Hi, I'm getting an error when running these queries

SELECT ToDateTime(1639137263000, 'yyyy-MM-dd') AS dateTimeString FROM ignoreMe

and

SELECT FromDateTime('2019-08-07', 'yyyy-MM-dd') AS epochMillis FROM ignoreMe

in Pinot's multi stage engine from the Pinot UI. Here's the error message: { "message": "SQLParsingError\njava.lang.RuntimeException Error composing query plan for: SELECT FromDateTime('2019-08-07', 'yyyy-MM-dd') AS epochMillis\nFROM ignoreMe\n\tat org.apache.pinot.query.QueryEnvironment.planQuery(QueryEnvironment.java:137)\n\tat org.apache.pinot.broker.requesthandler.MultiStageBrokerRequestHandler.handleRequest(MultiStageBrokerRequestHandler.java:153)\n\tat org.apache.pinot.broker.requesthandler.MultiStageBrokerRequestHandler.handleRequest(MultiStageBrokerRequestHandler.java:128)\n...\nCaused by: java.lang.IllegalArgumentException: Could not find schema for table: 'ignoreMe'. This is likely indicative of some kind of corruption and should not happen! If you are running this via the a test environment, check to make sure you're specifying the correct tables.\n\tat org.apache.pinot.query.catalog.PinotCatalog.getTable(PinotCatalog.java:68)\n\tat org.apache.calcite.jdbc.SimpleCalciteSchema.getImplicitTable(SimpleCalciteSchema.java:126)\n\tat org.apache.calcite.jdbc.CalciteSchema.getTable(CalciteSchema.java:295)\n\tat org.apache.calcite.sql.validate.EmptyScope.resolve_(EmptyScope.java:145)", "errorCode": 150 } Is this a known issue? Can you please investigate and confirm whether we need to create an issue here?

@Rong R ^^

were you trying to do a literal only query? this is not supported in v2 engine

@Rong R: I tried this

SELECT ToDateTime(some_date_column, 'yyyy-MM-dd') FROM tbl

and it failed as well in the v2 engine when input is a column but works in v1.

can you share the error msg?

No match found for function signature ToDateTime(<TIMESTAMP>, <CHARACTER>)'\n\tat org.apache.pinot.query.QueryEnvironment.planQuery(QueryEnvironment.java:135)\n\tat org.apache.pinot.broker.requesthandler.MultiStageBrokerRequestHandler.handleRequest(MultiStageBrokerRequestHandler.java:153)\n\tat org.apache.pinot.broker.requesthandler.MultiStageBrokerRequestHandler.handleRequest(MultiStageBrokerRequestHandler.java:128)\n\tat org.apache.pinot.broker.requesthandler.BrokerRequestHandler.handleRequest(BrokerRequestHandler.java:47)\n...\nCaused by: org.apache.calcite.runtime.CalciteContextException: From line 1, column 8 to line 1, column 42: No match found for function signature ToDateTime(<TIMESTAMP>, <CHARACTER>)\n\tat java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)\n\tat java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)\n\tat java.base/jdk.internal.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)\n\tat java.base/java.lang.reflect.Constructor.newInstance(Constructor.java:490)\n...\nCaused by: org.apache.calcite.sql.validate.SqlValidatorException: No match found for function signature ToDateTime(<TIMESTAMP>, <CHARACTER>)\n\tat java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)\n\tat java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)\n\tat java.base/jdk.internal.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)\n\tat java.base/java.lang.reflect.Constructor.newInstance(Constructor.java:490)",

yup. this seems to be an implicit type casting done in v1 that is not standard SQL so we didn't support it in v2. casting the timestamp column to long will solve the issue

I see, just tried casting but get another error for

select cast(some_date_comlum) from tbl

in v2 but works in v1. From line 1, column 26 to line 1, column 29: Unknown identifier 'LONG''\n\tat org.apache.pinot.query.QueryEnvironment.planQuery(QueryEnvironment.java:135)\n\tat org.apache.pinot.broker.requesthandler.MultiStageBrokerRequestHandler.handleRequest(MultiStageBrokerRequestHandler.java:153)\n\tat org.apache.pinot.broker.requesthandler.MultiStageBrokerRequestHandler.handleRequest(MultiStageBrokerRequestHandler.java:128)\n\tat org.apache.pinot.broker.requesthandler.BrokerRequestHandler.handleRequest(BrokerRequestHandler.java:47)\n...\nCaused by: org.apache.calcite.runtime.CalciteContextException: From line 1, column 26 to line 1, column 29: Unknown identifier 'LONG'\n\tat java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)\n\tat java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)\n\tat java.base/jdk.internal.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)\n\tat java.base/java.lang.reflect.Constructor.newInstance(Constructor.java:490)\n...\nCaused by: org.apache.calcite.sql.validate.SqlValidatorException: Unknown identifier 'LONG'\n\tat java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)\n\tat java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)\n\tat java.base/jdk.internal.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)\n\tat java.base/java.lang.reflect.Constructor.newInstance(Constructor.java:490)",

CAST col as BIGINT

--> LONG is the java type, SQL type name is BIGINT

got, it, thanks. I'm able to cast it but now it throws this error for `select ToDateTime(CAST col as BIGINT, 'yyyy-MM-dd') from tbl`: No match found for function signature ToDateTime(<NUMERIC>, <CHARACTER>)'\n\tat org.apache.pinot.query.QueryEnvironment.planQuery(QueryEnvironment.java:135)\n\tat org.apache.pinot.broker.requesthandler.MultiStageBrokerRequestHandler.handleRequest(MultiStageBrokerRequestHandler.java:153)\n\tat org.apache.pinot.broker.requesthandler.MultiStageBrokerRequestHandler.handleRequest(MultiStageBrokerRequestHandler.java:128)\n...\nCaused by: org.apache.calcite.runtime.CalciteContextException: From line 1, column 8 to line 1, column 58: No match found for function signature

interesting. let me take a look at this one

sure, I tried that but it throws the same error. No match found for function signature ToDateTime(<NUMERIC>, <CHARACTER>)'\n\tat org.apache.pinot.query.QueryEnvironment.planQuery(QueryEnvironment.java:135)\n\tat org.apache.pinot.broker.requesthandler.MultiStageBrokerRequestHandler.handleRequest(MultiStageBrokerRequestHandler.java:153)\n\tat

got it.

running this on dev and we're using the latest build: 0.12.0-ST.34.

thanks. will try to reproduce and create a fix

Thank you!

can you try

toDateTime

instead of

ToDateTime

confirming that works for both engines. thanks.

my mistake for not reading your query carefully, this is a known issue, see: https://github.com/apache/pinot/issues/9900

np, thanks for sharing this w/ the clarification.