Zine eddine Zidane created CALCITE-5718: -------------------------------------------
Summary: Referencing column alias in WHERE clause Key: CALCITE-5718 URL: https://issues.apache.org/jira/browse/CALCITE-5718 Project: Calcite Issue Type: Bug Components: core Affects Versions: 1.34.0 Reporter: Zine eddine Zidane Does calcite support referencing column aliases in WHERE clause? I know SQL doesn't allow this but it is supported out of the box in databases such as redshift, bigquery etc.. The following query (for the sake of the example) fails with the error message below: Query: {code:java} SELECT (CASE WHEN deptno = 20 THEN deptno ELSE NULL END) AS deptno_20 FROM emp WHERE deptno_20 = 20;{code} Error: {code:java} org.apache.calcite.sql.validate.SqlValidatorException: Column 'W_20' not found in any table 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) at org.apache.calcite.runtime.Resources$ExInstWithCause.ex(Resources.java:505) at org.apache.calcite.runtime.Resources$ExInst.ex(Resources.java:599) at org.apache.calcite.sql.SqlUtil.newContextException(SqlUtil.java:945) at org.apache.calcite.sql.SqlUtil.newContextException(SqlUtil.java:930) at org.apache.calcite.sql.validate.SqlValidatorImpl.newValidationError(SqlValidatorImpl.java:5408) at org.apache.calcite.sql.validate.DelegatingScope.fullyQualify(DelegatingScope.java:286) at org.apache.calcite.sql.validate.SqlValidatorImpl$Expander.visit(SqlValidatorImpl.java:6573) at org.apache.calcite.sql.validate.SqlValidatorImpl$Expander.visit(SqlValidatorImpl.java:6553) at org.apache.calcite.sql.SqlIdentifier.accept(SqlIdentifier.java:324) at org.apache.calcite.sql.util.SqlShuttle$CallCopyingArgHandler.visitChild(SqlShuttle.java:134) at org.apache.calcite.sql.util.SqlShuttle$CallCopyingArgHandler.visitChild(SqlShuttle.java:101) at org.apache.calcite.sql.SqlOperator.acceptCall(SqlOperator.java:954) at org.apache.calcite.sql.validate.SqlValidatorImpl$Expander.visitScoped(SqlValidatorImpl.java:6593) at org.apache.calcite.sql.validate.SqlScopedShuttle.visit(SqlScopedShuttle.java:54) at org.apache.calcite.sql.validate.SqlScopedShuttle.visit(SqlScopedShuttle.java:37) at org.apache.calcite.sql.SqlCall.accept(SqlCall.java:161) at org.apache.calcite.sql.validate.SqlValidatorImpl$Expander.go(SqlValidatorImpl.java:6562) at org.apache.calcite.sql.validate.SqlValidatorImpl.expand(SqlValidatorImpl.java:6144) at org.apache.calcite.sql.validate.SqlValidatorImpl.validateWhereClause(SqlValidatorImpl.java:4450) at org.apache.calcite.sql.validate.SqlValidatorImpl.validateSelect(SqlValidatorImpl.java:3737) at org.apache.calcite.sql.validate.SelectNamespace.validateImpl(SelectNamespace.java:61) at org.apache.calcite.sql.validate.AbstractNamespace.validate(AbstractNamespace.java:88) at org.apache.calcite.sql.validate.SqlValidatorImpl.validateNamespace(SqlValidatorImpl.java:1114) at org.apache.calcite.sql.validate.SqlValidatorImpl.validateQuery(SqlValidatorImpl.java:1085) at org.apache.calcite.sql.SqlSelect.validate(SqlSelect.java:248) at org.apache.calcite.sql.validate.SqlValidatorImpl.validateScopedExpression(SqlValidatorImpl.java:1060) at org.apache.calcite.sql.validate.SqlValidatorImpl.validate(SqlValidatorImpl.java:766) {code} -- This message was sent by Atlassian Jira (v8.20.10#820010)