Open costin opened 4 years ago
Pinging @elastic/es-search (:Search/SQL)
Maybe as a bonus check if the Postgres behaviour could be followed:
postgres=# select 123 > 'foo';
ERROR: invalid input syntax for integer: "foo"
LINE 1: select 123 > 'foo';
^
postgres=# select 123 > '222';
?column?
----------
f
(1 row)
postgres=# select 123 > '111';
?column?
----------
t
(1 row)
postgres=# select 123 > '222';
@matriv Does that work in where clauses too, like select ... where int_column > '222'
?
On the flip side, ES accepts integers in range queries on text/keyword fields (i.e. select ... from test_emp where first_name > 1
), but not sure if that makes sense for SQL?
We probably still want to support queries like these:
SELECT ... FROM ... WHERE date > '1969-05-13'
,
so if the literal can be converted to the other operand's type, the comparison should be allowed (as suggested above).
But the following tests, currently asserting success, would need to be changed/dropped:
date/time - int comparison:
datetime - time comparison:
@matriv Does that work in where clauses too, like
select ... where int_column > '222'
? On the flip side, ES accepts integers in range queries on text/keyword fields (i.e.select ... from test_emp where first_name > 1
), but not sure if that makes sense for SQL?
postgres=# create table t(a int);
CREATE TABLE
postgres=# insert into t values(1),(2),(3),(4),(111),(222);
INSERT 0 6
postgres=# select * from t where a > '111';
a
-----
222
(1 row)
postgres=# select * from t where a > '3';
a
-----
4
111
222
(3 rows)
@elastic/es-ql
Pinging @elastic/es-analytical-engine (Team:Analytics)
Currently
BinaryOperators
only check whether both types are resolved yet it doesn't check whether the types are actually compatible. This means123 > 'foo'
is allowed despite being a numeric vs string comparison which is incorrect.