[jira] [Created] (CALCITE-5718) Referencing column alias in WHERE clause

["Zine eddine Zidane (Jira)" <ji...@apache.org>]

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)