search

DataSQRL / sqrl

Compiler for streaming data pipelines and data microservices with configurable engines.
https://www.datasqrl.com/
74 stars 10 forks source link

Column name casing in DISTINCT ON statements can throw error #474

Open mbroecheler opened 5 months ago

mbroecheler commented 5 months ago

When I write the following DISTINCT ON statement:

Merchant := DISTINCT Merchant ON merchantid ORDER BY updatedTime DESC;

The compiler throws an exception:

Caused by: org.apache.calcite.runtime.CalciteContextException: At line 0, column 0: Column 'merchantid' is ambiguous
    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.newInstanceWithCaller(Constructor.java:500)
    at java.base/java.lang.reflect.Constructor.newInstance(Constructor.java:481)
    at org.apache.calcite.runtime.Resources$ExInstWithCause.ex(Resources.java:467)
    at org.apache.calcite.sql.SqlUtil.newContextException(SqlUtil.java:915)
    at org.apache.calcite.sql.SqlUtil.newContextException(SqlUtil.java:900)
    at org.apache.calcite.sql.validate.SqlValidatorImpl.newValidationError(SqlValidatorImpl.java:4881)
    at org.apache.calcite.sql.validate.DelegatingScope.fullyQualify(DelegatingScope.java:496)
    at org.apache.calcite.sql.validate.SqlValidatorImpl.findTableColumnPair(SqlValidatorImpl.java:3492)
    at org.apache.calcite.sql.validate.SqlValidatorImpl.isRolledUpColumn(SqlValidatorImpl.java:3524)
    at org.apache.calcite.sql.validate.SqlValidatorImpl.expandStar(SqlValidatorImpl.java:597)
    at org.apache.calcite.sql.validate.SqlValidatorImpl.expandSelectItem(SqlValidatorImpl.java:392)
    at org.apache.calcite.sql.validate.SqlValidatorImpl.validateSelectList(SqlValidatorImpl.java:4075)
    at org.apache.calcite.sql.validate.SqlValidatorImpl.validateSelect(SqlValidatorImpl.java:3361)
    at org.apache.calcite.sql.validate.SelectNamespace.validateImpl(SelectNamespace.java:60)
    at org.apache.calcite.sql.validate.AbstractNamespace.validate(AbstractNamespace.java:84)
    at org.apache.calcite.sql.validate.SqlValidatorImpl.validateNamespace(SqlValidatorImpl.java:998)
    at org.apache.calcite.sql.validate.SqlValidatorImpl.validateQuery(SqlValidatorImpl.java:976)
    at org.apache.calcite.sql.SqlSelect.validate(SqlSelect.java:232)
    at org.apache.calcite.sql.validate.SqlValidatorImpl.validateScopedExpression(SqlValidatorImpl.java:953)
    at org.apache.calcite.sql.validate.SqlValidatorImpl.validate(SqlValidatorImpl.java:705)
    at com.datasqrl.calcite.QueryPlanner.planRoot(QueryPlanner.java:179)
    at com.datasqrl.calcite.QueryPlanner.planCalcite(QueryPlanner.java:175)
    at com.datasqrl.calcite.QueryPlanner.planCalcite(QueryPlanner.java:169)
    at com.datasqrl.calcite.QueryPlanner.plan(QueryPlanner.java:156)
    at com.datasqrl.plan.validate.ScriptPlanner.postvisit(ScriptPlanner.java:372)
    at com.datasqrl.plan.validate.ScriptPlanner.visit(ScriptPlanner.java:868)
    at com.datasqrl.plan.validate.ScriptPlanner.visit(ScriptPlanner.java:108)
    at com.datasqrl.calcite.visitor.SqlNodeVisitor.accept(SqlNodeVisitor.java:48)
    at com.datasqrl.plan.validate.ScriptPlanner.validateStatement(ScriptPlanner.java:999)
    at com.datasqrl.plan.ScriptPlanner.plan(ScriptPlanner.java:56)
    ... 85 more
Caused by: org.apache.calcite.sql.validate.SqlValidatorException: Column 'merchantid' is ambiguous
    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.newInstanceWithCaller(Constructor.java:500)
    at java.base/java.lang.reflect.Constructor.newInstance(Constructor.java:481)
    at org.apache.calcite.runtime.Resources$ExInstWithCause.ex(Resources.java:467)
    at org.apache.calcite.runtime.Resources$ExInst.ex(Resources.java:560)
    ... 112 mor

If I change the name of the column to merchantId (which is the name in the schema) then it works.

henneberger commented 5 months ago

The issue is during SqrlToSql translation when we have to rebuild the select list for the distinct statement. We should continue to honor the user's casing and remove the other references to that column.

mbroecheler commented 5 months ago

Why is it case sensitive in this particular instance?

henneberger commented 5 months ago

It's throwing an error because it is case insensitive. There are two columns named 'merchantid' with different casing and that is invalid for case insensitive column ambiguity rules. The solution is to exclude the second 'merchantid' with the original casing.