elasticsearch
elasticsearch copied to clipboard
elastic
SQL: Make automatic type conversion consistent for comparisons
Problem to solve
Since SQL heavily relies on Search API, it also inherits its behavior regarding type conversion.
Eg. the following queries are equivalent, when id is a numeric field:
SELECT * FROM someIndex WHERE id = 1 ;
SELECT * FROM someIndex WHERE id = '1';
In the second query, the string '1' is automatically converted to the numeric 1.
A slightly different version of this query, though, will return an unexpected result:
SELECT id, id = '1' as x FROM someIndex WHERE id = '1';
| id | x |
+------+--------+
| 1 | null |
The same filter that is true as a condition, is null as a projection.
This is due to the fact that the projection is executed locally (ie. not pushed to Search).
Scope of the PR
This PR extends automatic type conversion to all the local comparison operations (equalities, inequalities, BETWEEN, IN), when the conversion is possible at execution planning time (ie. when the expression to convert is constant, can be folded and converted during the query optimization phase).
As an additional point, String to Date conversion is particularly complicated, due to date math.
This PR also addresses this problem, enhancing the date parsing and preserving the logic of comparison with date math expressions (eg. x = '2020-01-01||/y' is true for any x in the year 2020).
This is enough to cover all the common use cases and to make the usability experience more natural (ie. uniform and consistent behavior regarding data conversion in comparisons)
Out of scope
Runtime conversion
This PR only covers data conversion for constant (ie. foldable) expressions. Some conversions can only be performed at execution time, eg. the following:
SELECT aDateField = aStringField FROM index
needs to convert each value of aStringField after it's returned by the search phase (assuming that type conversion is the desired behavior in this case).
Mathematic operations
Data conversion can also be attempted in mathematic operation like 1 + '1'. This PR does not address this case
Fixes #87500 Fixes #77055
