search

apache / pinot

Apache Pinot - A realtime distributed OLAP datastore
https://pinot.apache.org/
Apache License 2.0
5.27k stars 1.23k forks source link

Lookup function using alias is giving error #12865

Open vineethvp opened 3 months ago

vineethvp commented 3 months ago

Trying to run below query with an inner join and lookup in the query.

The column used for lookup is present in both tables. While using the alias for the column inside lookup it is giving error.

SELECT  e.sub_site_id,
  lookup(
    'account_summary',
    'account_name',
    'sub_site_id',
     e.sub_site_id
  )
FROM kpi_events as e INNER JOIN  device_count_summary as d on e.customer_id=d.account_id
where  upload_ts_millis > ago('PT3000M')

ProcessingException(errorCode:150, message:SQLParsingError: java.lang.Exception: Unable to find table for this query at org.apache.pinot.controller.api.resources.PinotQueryResource.getMultiStageQueryResponse(PinotQueryResource.java:214) at org.apache.pinot.controller.api.resources.PinotQueryResource.executeSqlQuery(PinotQueryResource.java:177) at org.apache.pinot.controller.api.resources.PinotQueryResource.handlePostSql(PinotQueryResource.java:125) at jdk.internal.reflect.GeneratedMethodAccessor386.invoke(Unknown Source) ... Caused by: java.lang.RuntimeException: Error composing query plan for: SELECT lookup( 'account_summary', 'account_name', 'sub_site_id', ... Caused by: org.apache.calcite.runtime.CalciteContextException: From line 2, column 3 to line 7, column 3: No match found for function signature lookup(<CHARACTER>, <CHARACTER>, <CHARACTER>, <CHARACTER>) 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: No match found for function signature lookup(<CHARACTER>, <CHARACTER>, <CHARACTER>, <CHARACTER>) 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))

Jackie-Jiang commented 3 months ago

Seems like we have skipped the column rewrite for lookup in #7670. @xiangfu0 Do you recall the reason behind it? IMO we should not skip it since the reference to the dimension table has to be literal.

vineethvp commented 2 months ago

@Jackie-Jiang @xiangfu0 Any update on this?