[CALCITE-5653] Validation error when using aggregation from the select list in ORDER BY with DISTINCT query

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Fix Version/s: 1.35.0
• Labels: pull-request-available
• Assignee: Unassigned
• Reporter: Itiel Sadeh
• Created: 16/Apr/23 20:06
• Updated: 27/Jul/23 10:22
• Resolved: 04/May/23 22:43

Description

When using aggregation expression from the SELECT list in the  ORDER BY clause in a DISTINCT query, there is a validation error of "expression is not in the select clause".
 
The issue happens when casts are added the aggregation expression in SqlValidator, as they are not part of the select.
For example, the following query:

SELECT distinct sum(deptno + '1') FROM dept ORDER BY 1

will lead to:

From line 1, column 21 to line 1, column 54: Expression 'SUM(`DEPT`.`DEPTNO` + CAST('1' AS INTEGER))' is not in the select clause
org.apache.calcite.runtime.CalciteContextException: From line 1, column 21 to line 1, column 54: Expression 'SUM(`DEPT`.`DEPTNO` + CAST('1' AS INTEGER))' is not in the select clause
    at java.base@17.0.5/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    at java.base@17.0.5/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:77)
    at java.base@17.0.5/jdk.internal.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
    at java.base@17.0.5/java.lang.reflect.Constructor.newInstanceWithCaller(Constructor.java:499)
    at java.base@17.0.5/java.lang.reflect.Constructor.newInstance(Constructor.java:480)
    at app//org.apache.calcite.runtime.Resources$ExInstWithCause.ex(Resources.java:505)
    at app//org.apache.calcite.sql.SqlUtil.newContextException(SqlUtil.java:945)
    at app//org.apache.calcite.sql.SqlUtil.newContextException(SqlUtil.java:930)
    at app//org.apache.calcite.sql.validate.SqlValidatorImpl.newValidationError(SqlValidatorImpl.java:5464)
    at app//org.apache.calcite.sql.validate.AggChecker.visit(AggChecker.java:168)
    at app//org.apache.calcite.sql.validate.AggChecker.visit(AggChecker.java:45)
    at app//org.apache.calcite.sql.SqlCall.accept(SqlCall.java:161)
    at app//org.apache.calcite.sql.validate.AggregatingSelectScope.checkAggregateExpr(AggregatingSelectScope.java:233)
    at app//org.apache.calcite.sql.validate.AggregatingSelectScope.validateExpr(AggregatingSelectScope.java:242)
    at app//org.apache.calcite.sql.validate.OrderByScope.validateExpr(OrderByScope.java:128)
    at app//org.apache.calcite.sql.validate.SqlValidatorImpl.validateExpr(SqlValidatorImpl.java:4676)
    at app//org.apache.calcite.sql.validate.SqlValidatorImpl.validateOrderItem(SqlValidatorImpl.java:4398)
    at app//org.apache.calcite.sql.validate.SqlValidatorImpl.validateOrderList(SqlValidatorImpl.java:4341)
    at app//org.apache.calcite.sql.validate.SqlValidatorImpl.validateSelect(SqlValidatorImpl.java:3786)
    at app//org.apache.calcite.sql.validate.SelectNamespace.validateImpl(SelectNamespace.java:61)
    at app//org.apache.calcite.sql.validate.AbstractNamespace.validate(AbstractNamespace.java:88)
    at app//org.apache.calcite.sql.validate.SqlValidatorImpl.validateNamespace(SqlValidatorImpl.java:1135)
    at app//org.apache.calcite.sql.validate.SqlValidatorImpl.validateQuery(SqlValidatorImpl.java:1106)
    at app//org.apache.calcite.sql.SqlSelect.validate(SqlSelect.java:282)
    at app//org.apache.calcite.sql.validate.SqlValidatorImpl.validateScopedExpression(SqlValidatorImpl.java:1081)
    at app//org.apache.calcite.sql.validate.SqlValidatorImpl.validate(SqlValidatorImpl.java:787) 

The issue is that the code in{{ AggChecker}} will search for the expanded node (with the CAST) in the select list, but the select list contains only the unexpanded node.

This can be solved through setting{{ identifierExpansion }}to true, but I don't think it should be related, as it's not an identifier expansion here.

Issue Links

• links to: GitHub Pull Request #3160