SQL: floats precision and scale corrections

[bpintea]

This issue is proposing to change the reported MAXIMUM_SCALE, MINIMUM_SCALE and potentially PRECISION values in SYS TYPES answer.

• MINIMUM_SCALE
  The floats are not variable precision in ES. Furthermore, according to ODBC's SQLGetTypeInfo, If a data type has a fixed scale, the MINIMUM_SCALE and MAXIMUM_SCALE columns both contain this value. So I believe MINIMUM_SCALE should contain the same values as MAXIMUM_SCALE.

• MAXIMUM_SCALE

  • HALF_FLOAT
    Currently its value is 16. However, it's precision is half of that of a FLOAT. So it should be adjusted down accordingly. (Also, see example[1] below.)
  • SCALED_FLOAT
    Currently 19, probably suggested by it being backed up by a signed long, whose precision is 19. However, this is handled as a floating point (probably double?), so it's scale should be adjusted accordingly. (Also, see example[2] below.)

• PRECISION
  This column has currently the same values as the MAXIMUM_SCALE. Its value however is that of the maximum number of digits (whole and fractional plus dot and sign or exponential), so it should be in any case greater than that. Current implementation of operations - like addition, for instance - is consistent with the currently reported value, in that it truncates the result. But this truncation seems incorrect, as it's effectively reducing the type's scale. (Also, see example[3] below.). IMO, both the reported precision and operations should be corrected. (There might be Java readily-available calculations for the maximum precision of floats, like for C/C++.)

Example:

DELETE floats
PUT floats
{
  "mappings" : {
    "properties": {
      "scaled_float": {
        "type": "scaled_float"
        , "scaling_factor": 1000.0
      },
      "half_float": {
        "type": "half_float"
      },
      "float": {
        "type": "float"
      },
      "double": {
        "type": "double"
      }
    }
  }
}

PUT floats/_doc/1
{
  "scaled_float": 9223372036854775807,
  "half_float": 1.98765432100123456789,
  "float": 1.98765432100123456789,
  "double": 1.98765432100123456789
}

sql> select * from floats;

      double      |      float       |  half_float   |    scaled_float
------------------+------------------+---------------+--------------------
1.9876543210012345|1.9876543283462524|1.9873046875   |9.223372036854776E15

Above one can see that:

• the double will correctly retain 16 decimals, in accordance to Java's double implementation;
• the float is also consistent with float implementation
• [1] the half_float is down to 3 (vs 16 reported now);
• [2] the scaled_float won't report with higher scale than a double's (vs 19 reported).

For the PRECISION point[3]:

sql> select 1000000+double from floats;
 1000000+double
-----------------
1000001.987654321

In this case, the PRECISION seems kept, but the scale (and thus float data type's precision, in non-SQL talk) is diminished, the type's reported scale then no longer being correct.

An alternative implementation:

MariaDB [(none)]> select 1000000+1.98765432100123456789;
+--------------------------------+
| 1000000+1.98765432100123456789 |
+--------------------------------+
|   1000001.98765432100123456789 |
+--------------------------------+
1 row in set (0.00 sec)

[elasticmachine]

Pinging @elastic/es-search

[bpintea]

Rectification: for PRECISION, the ODBC documents the values to use for the floating types as column size (as 3rd column in the type information API) as follows:

• SQL_DOUBLE (ES DOUBLE): 15
• SQL_REAL (ES FLOAT): 7
  and thus correspondingly, for or half precision, 16bit representation, lg(2¹¹):
• HALF_FLOAT: 3