SQL: Perform type verification for comparison operators

[costin]

Currently BinaryOperators only check whether both types are resolved yet it doesn't check whether the types are actually compatible.
This means 123 > 'foo' is allowed despite being a numeric vs string comparison which is incorrect.

[elasticmachine]

Pinging @elastic/es-search (:Search/SQL)

[matriv]

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)

[bpintea]

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?

[bpintea]

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:

  • SELECT some.string FROM test WHERE date = 5
  • SELECT some.string FROM test WHERE date > 1969-05-13
  • SELECT int FROM test WHERE DATE_DIFF('week',date, date) > '2018-09-04'::date
  • SELECT int FROM test WHERE DATE_PART('month', date) > '2018-09-04'::date

• datetime - time comparison:

  • SELECT some.string FROM test WHERE date = CAST('12:34:56' AS TIME)

[matriv]

@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)

[matriv]

@elastic/es-ql

[elasticsearchmachine]

Pinging @elastic/es-analytical-engine (Team:Analytics)