[BUG] Rounding functions don't work well with values > INT_MAX

[Yury-Fridlyand]

What is the bug?

ROUND, FLOOR and CEILING functions don't work well with values > INT_MAX or < -INT_MAX

How can one reproduce the bug?

opensearchsql> select ROUND(9223372036854775807), FLOOR(9223372036854775807), CEILING(9223372036854775807);
fetched rows / total rows = 1/1
+------------------------------+------------------------------+--------------------------------+
| ROUND(9223372036854775807)   | FLOOR(9223372036854775807)   | CEILING(9223372036854775807)   |
|------------------------------+------------------------------+--------------------------------|
| 2147483647                   | 2147483647                   | 2147483647                     |
+------------------------------+------------------------------+--------------------------------+

9223372036854775807 == LONG_MAX
Those functions return INTEGER instead of LONG.

What is the expected behavior?

Functions should return LONG.

What is your host/environment?

2.x @ 21373b9

Do you have any additional context?

Reported on behalf of @kylepbit

[MitchellGale]

mysql> select ROUND(9223372036854775807), FLOOR(9223372036854775807), CEILING(92233720368547555575456454654654444444444444444444465555555555555555555555.55555555555554444444444444444444777777777777777777778888888888888666);
+----------------------------+----------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------+
| ROUND(9223372036854775807) | FLOOR(9223372036854775807) | CEILING(92233720368547555575456454654654444444444444444444465555555555555555555555.55555555555554444444444444444444777777777777777777778888888888888666) |
+----------------------------+----------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------+
|        9223372036854775807 |        9223372036854775807 |                                                                               92233720368547555575456454654654444444444444444444465555555555555555555555 |
+----------------------------+----------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Seems like MySQL has no limit on length.

Should we use Long Double type and limit the length the length for opensearch?

[acarbonetto]

We currently support double, but not long, and anything larger than a double gets truncated without an error/warning.

Option a) Limit the values to the size of a double or error/warn about loss of precision (not recommended).
Option b) Submit a ticket to OpenSearch to error/warn or support long numbers.
Option c) For numbers not supported by double, we can support larger numbers (long?) in-memory.

Regardless, we should fail with an error for any number larger than what is supported by OpenSearch (https://opensearch.org/docs/2.0/opensearch/supported-field-types/numeric).

[MitchellGale]

When converting to support long data type, there are issues. The IEEE 754 which is the floating point format of listed in the OpenSearch documentation for the double type has precision issues within the scope of this ticket. IEEE 754 has 52 bits for the fraction (for double type), if the input takes up more than that space, such as with 282474973688888.022, the decimal places may be sacrificed.

MySQL Issue:

MySQL also has a similar issue, albeit with a larger number. Both of the below queries should end in a 9, however the 2nd one ends in an 8.

mysql> select ceil(219988888888888888888888888888888888888888888888888888888888888888888888.9);
+----------------------------------------------------------------------------------+
| ceil(219988888888888888888888888888888888888888888888888888888888888888888888.9) |
+----------------------------------------------------------------------------------+
|         219988888888888888888888888888888888888888888888888888888888888888888889 |
+----------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select ceil(3219988888888888888888888888888888888888888888888888888888888888888888888.9);
+-----------------------------------------------------------------------------------+
| ceil(3219988888888888888888888888888888888888888888888888888888888888888888888.9) |
+-----------------------------------------------------------------------------------+
|         3219988888888888888888888888888888888888888888888888888888888888888888888 |
+-----------------------------------------------------------------------------------+
1 row in set (0.01 sec)

OpenSearchSQL Fix Issue:

Issue observed after fix for OpenSearchSQL ceil function. Both queries should go up to the next whole number, however because of floating point precision issues we lose some decimals in same situations. Issue in the positive domain for ceil/ceiling functions and negative domain for floor function.

opensearchsql> select ceil(282474973688888.022);                                                                                                                                                                                                            
fetched rows / total rows = 1/1
+-----------------------------+
| ceil(282474973688888.022)   |
|-----------------------------|
| 282474973688888             |
+-----------------------------+
opensearchsql> select ceil(282474973688888.122);                                                                                                                                                                                                            
fetched rows / total rows = 1/1
+-----------------------------+
| ceil(282474973688888.122)   |
|-----------------------------|
| 282474973688889             |
+-----------------------------+

OpenSearch Issue:

This issue exists in OpenSearch, we can see when the number 282474973688888.022 is stored it is value 2.82474973688888E14 losing the decimal places. Example below. If there are no decimal places stored, we can't know if the ceil function should change the value in the return.

Bits-MacBook-Pro:resources mitchellg$ curl -s -H 'Content-Type: application/json' -XPOST "http://localhost:9200/double_test/_doc?pretty" -d '{"number": 282474973688888.022 }'
{
  "_index" : "double_test",
  "_id" : "zhNk1IQBZbRcIIYW2I6q",
  "_version" : 1,
  "result" : "created",
  "_shards" : {
    "total" : 2,
    "successful" : 1,
    "failed" : 0
  },
  "_seq_no" : 0,
  "_primary_term" : 1
}
Bits-MacBook-Pro:resources mitchellg$ curl -s -XGET "http://localhost:9200/double_test/_search?pretty"
{
  "took" : 2,
  "timed_out" : false,
  "_shards" : {
    "total" : 1,
    "successful" : 1,
    "skipped" : 0,
    "failed" : 0
  },
  "hits" : {
    "total" : {
      "value" : 1,
      "relation" : "eq"
    },
    "max_score" : 1.0,
    "hits" : [
      {
        "_index" : "double_test",
        "_id" : "zhNk1IQBZbRcIIYW2I6q",
        "_score" : 1.0,
        "_source" : {
          "number" : 2.82474973688888E14
        }
      }
    ]
  }
}

Summary:

Allowing for return values larger than INT_MAX can be implemented to work as well as OpenSearch does for handling doubles. Unless a user is using OpenSearchSQL as a ceil/floor calculator, most of the values will be stored at some point in OpenSearch, we will lose.

I propose the fix for this issue is to have a return type of long from the ceil/ceiling/floor functions in java/org/opensearch/sql/expression/operator/arthmetic/MathematicalFunction.java. This will support better support for large numbers, but may produce incorrect data if OpenSearch loses the decimal places from the input double.

@kylepbit @acarbonetto @Yury-Fridlyand @dai-chen @penghuo

[acarbonetto]

This issue can be closed

[Yury-Fridlyand]

Closed by #1205 and #1255.