Cannot compare BIGINT with FromDateTime() in v2 query engine

[shaobo-stripe]

Hi, we are trying out v2 query engine. This simplified query works well in v1 (without set useMultistageEngine=true;):

set useMultistageEngine=true;

select
  *
from
  <table>
where
  created_ms >= FromDateTime('2023-09-22 11:30:00', 'yyyy-MM-dd HH:mm:ss')
  and created_ms < FromDateTime('2023-09-22 12:00:00', 'yyyy-MM-dd HH:mm:ss')
limit 1

but we see this error with v2 turned on

Caused by: org.apache.calcite.runtime.CalciteContextException: From line 8, column 3 to line 8, column 81: Cannot apply '>=' to arguments of type '<BIGINT> >= <TIMESTAMP(0)>'. Supported form(s): '<COMPARABLE_TYPE> >= <COMPARABLE_TYPE>'
    at java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    at java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
    at java.base/jdk.internal.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
    at java.base/java.lang.reflect.Constructor.newInstance(Constructor.java:490)
...
Caused by: org.apache.calcite.sql.validate.SqlValidatorException: Cannot apply '>=' to arguments of type '<BIGINT> >= <TIMESTAMP(0)>'. Supported form(s): '<COMPARABLE_TYPE> >= <COMPARABLE_TYPE>'
    at java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    at java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
    at java.base/jdk.internal.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
    at java.base/java.lang.reflect.Constructor.newInstance(Constructor.java:490))

Could use help figuring out how to craft the time comparison logic in v2 engine

[jadami10]

cc @walterddr @xiangfu0, i think you saw something similar in #11668

[walterddr]

The 2 issues are not exactly the same be related. The fundamental issue is that the function signature in calcite is strongly typed where as in v1 it is more adaptive and thus it is executing the implicit conversion between timestamp and bigint.

(Note the problem is with the >= operator not the fromdatetime function itself, unlike the other issue)

A proper fix would require to consolidate the function registries and unifies the behavior

At this time, to make query work it require a CAST fromdatetime... AS BIGINT)

[walterddr]

i wouldn't call this a bug though TBH. if you run the same query with timestamp type in postgres, it gives similar error:

SELECT * FROM tbl WHRE ts > 1600000000;

error looks like:

ERROR: operator does not exist: timestamp without time zone > integer Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts.

but granted that the error message from postgres is MUCH MORE readable :-)

[shaobo-stripe]

thanks for the quick response and all the context. CAST to BigInt nailed and it's helpful to know the error comes from v2 being more strongly typed. Closing this issue then