SQL: Comparison beetween datetime field and string behaves inconsistently when used in filter vs. projection, grouping and ordering

[Luegg]

All kinds of comparisons between a datetime field and a string behave as expected in WHERE clauses but produce null in SELECT, GROUP BY and ORDER BY clauses.

A simple example is:

SELECT birth_date = '1955-10-04T00:00:00.000Z' a FROM test_emp WHERE a

       a
---------------
null

The query correctly produces one record but the a field should contain true.

Another example with GROUP BY and <:

SELECT COUNT(*) FROM test_emp GROUP BY birth_date < '1955-10-04T00:00:00.000Z'

   count(*)
---------------
100

Expected result contains two groups.

[elasticmachine]

Pinging @elastic/es-ql (Team:QL)

[costin]

This is a good example for making date/math explicit, even if it's not exposed to the user.
Currently we delegate to ES however as we've discovered this can lead to some issues when things are being rewritten. In this issue, the problem lies with the evaluation (outside of ES) which fails.
One could argue that comparing a date time to a string should not be supported due to type compatibility. I tend to agree with that and I think it will expose the underlying behavior - that is promote the string into a date/time (or in case of a failure to promote into a detailed exception):

SELECT birth_date = '1955-10-04T00:00:00.000Z'  FROM ... // becomes
SELECT birthdate == CAST('1955-10-04T00:00:00.000Z' AS birthdate.type)  // OR
SELECT birth_date = '1955-10-04T00:00:00.000Z' :: DATETIME

Not easy to do since we need to take into account the date/math syntax however I'm hopeful we can reuse that code so we don't end up with duplicated work.

[elasticsearchmachine]

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