Hi Teams,
Issue:
For a sql case, when a result column name is set as an alias of a measure
contains this column, like "select sum(a) as a".
If this sql contains filter like 'having a = xxx', currently kylin5 calcite
(calcite-core-1.116.0-kylin-4.x-r021) will take this ‘a' as original table
column 'a' rather than the alias of measure sum(a), and finally got a
SqlValidatorException: Expression 'a' is not being grouped
Is there any fix for this issue?
Thanks.
Sql case:
select LO_ORDERKEY, sum(LO_ORDTOTALPRICE) as LO_ORDTOTALPRICE from
SSB.LINEORDEr where LO_ORDERKEY > 1 group by LO_ORDERKEY having 1=1 and
(LO_ORDTOTALPRICE = 5509067)
Error message:
Caused by: org.apache.calcite.sql.validate.SqlValidatorException: Expression
'LO_ORDTOTALPRICE' is not being grouped
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
~[?:1.8.0_292]
at
sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
~[?:1.8.0_292]
at
sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
~[?:1.8.0_292]
at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
~[?:1.8.0_292]
at
org.apache.calcite.runtime.Resources$ExInstWithCause.ex(Resources.java:463)
~[calcite-core-1.116.0-kylin-4.x-r021.jar:1.116.0-kylin-4.x-r021]
at org.apache.calcite.runtime.Resources$ExInst.ex(Resources.java:572)
~[calcite-core-1.116.0-kylin-4.x-r021.jar:1.116.0-kylin-4.x-r021]
at org.apache.calcite.sql.SqlUtil.newContextException(SqlUtil.java:834)
~[calcite-core-1.116.0-kylin-4.x-r021.jar:1.116.0-kylin-4.x-r021]
at org.apache.calcite.sql.SqlUtil.newContextException(SqlUtil.java:819)
~[calcite-core-1.116.0-kylin-4.x-r021.jar:1.116.0-kylin-4.x-r021]
at
org.apache.calcite.sql.validate.SqlValidatorImpl.newValidationError(SqlValidatorImpl.java:4746)
~[calcite-core-1.116.0-kylin-4.x-r021.jar:1.116.0-kylin-4.x-r021]
at org.apache.calcite.sql.validate.AggChecker.visit(AggChecker.java:117)
~[calcite-core-1.116.0-kylin-4.x-r021.jar:1.116.0-kylin-4.x-r021]
at org.apache.calcite.sql.validate.AggChecker.visit(AggChecker.java:41)
~[calcite-core-1.116.0-kylin-4.x-r021.jar:1.116.0-kylin-4.x-r021]
at org.apache.calcite.sql.SqlIdentifier.accept(SqlIdentifier.java:366)
~[calcite-core-1.116.0-kylin-4.x-r021.jar:1.116.0-kylin-4.x-r021]
at
org.apache.calcite.sql.util.SqlBasicVisitor$ArgHandlerImpl.visitChild(SqlBasicVisitor.java:123)
~[calcite-core-1.116.0-kylin-4.x-r021.jar:1.116.0-kylin-4.x-r021]
at org.apache.calcite.sql.SqlOperator.acceptCall(SqlOperator.java:859)
~[calcite-core-1.116.0-kylin-4.x-r021.jar:1.116.0-kylin-4.x-r021]
at org.apache.calcite.sql.validate.AggChecker.visit(AggChecker.java:212)
~[calcite-core-1.116.0-kylin-4.x-r021.jar:1.116.0-kylin-4.x-r021]
at org.apache.calcite.sql.validate.AggChecker.visit(AggChecker.java:41)
~[calcite-core-1.116.0-kylin-4.x-r021.jar:1.116.0-kylin-4.x-r021]
at org.apache.calcite.sql.SqlCall.accept(SqlCall.java:138)
~[calcite-core-1.116.0-kylin-4.x-r021.jar:1.116.0-kylin-4.x-r021]
at
org.apache.calcite.sql.util.SqlBasicVisitor$ArgHandlerImpl.visitChild(SqlBasicVisitor.java:123)
~[calcite-core-1.116.0-kylin-4.x-r021.jar:1.116.0-kylin-4.x-r021]
at org.apache.calcite.sql.SqlOperator.acceptCall(SqlOperator.java:859)
~[calcite-core-1.116.0-kylin-4.x-r021.jar:1.116.0-kylin-4.x-r021]
at org.apache.calcite.sql.validate.AggChecker.visit(AggChecker.java:212)
~[calcite-core-1.116.0-kylin-4.x-r021.jar:1.116.0-kylin-4.x-r021]
at org.apache.calcite.sql.validate.AggChecker.visit(AggChecker.java:41)
~[calcite-core-1.116.0-kylin-4.x-r021.jar:1.116.0-kylin-4.x-r021]
at org.apache.calcite.sql.SqlCall.accept(SqlCall.java:138)
~[calcite-core-1.116.0-kylin-4.x-r021.jar:1.116.0-kylin-4.x-r021]
at
org.apache.calcite.sql.validate.AggregatingSelectScope.checkAggregateExpr(AggregatingSelectScope.java:231)
~[calcite-core-1.116.0-kylin-4.x-r021.jar:1.116.0-kylin-4.x-r021]
at
org.apache.calcite.sql.validate.SqlValidatorImpl.validateHavingClause(SqlValidatorImpl.java:3974)
~[calcite-core-1.116.0-kylin-4.x-r021.jar:1.116.0-kylin-4.x-r021]
at
org.apache.calcite.sql.validate.SqlValidatorImpl.validateSelect(SqlValidatorImpl.java:3264)
~[calcite-core-1.116.0-kylin-4.x-r021.jar:1.116.0-kylin-4.x-r021]
at
org.apache.calcite.sql.validate.SelectNamespace.validateImpl(SelectNamespace.java:60)
~[calcite-core-1.116.0-kylin-4.x-r021.jar:1.116.0-kylin-4.x-r021]
at
org.apache.calcite.sql.validate.AbstractNamespace.validate(AbstractNamespace.java:84)
~[calcite-core-1.116.0-kylin-4.x-r021.jar:1.116.0-kylin-4.x-r021]
at
org.apache.calcite.sql.validate.SqlValidatorImpl.validateNamespace(SqlValidatorImpl.java:987)
~[calcite-core-1.116.0-kylin-4.x-r021.jar:1.116.0-kylin-4.x-r021]
at
org.apache.calcite.sql.validate.SqlValidatorImpl.validateQuery(SqlValidatorImpl.java:968)
~[calcite-core-1.116.0-kylin-4.x-r021.jar:1.116.0-kylin-4.x-r021]
at org.apache.calcite.sql.SqlSelect.validate(SqlSelect.java:226)
~[calcite-core-1.116.0-kylin-4.x-r021.jar:1.116.0-kylin-4.x-r021]
at
org.apache.calcite.sql.validate.SqlValidatorImpl.validateScopedExpression(SqlValidatorImpl.java:943)
~[calcite-core-1.116.0-kylin-4.x-r021.jar:1.116.0-kylin-4.x-r021]
at
org.apache.calcite.sql.validate.SqlValidatorImpl.validate(SqlValidatorImpl.java:653)
~[calcite-core-1.116.0-kylin-4.x-r021.jar:1.116.0-kylin-4.x-r021]
at
org.apache.calcite.sql2rel.SqlToRelConverter.convertQuery(SqlToRelConverter.java:571)
~[calcite-core-1.116.0-kylin-4.x-r021.jar:1.116.0-kylin-4.x-r021]
at
org.apache.kylin.query.engine.SQLConverter.convertToRelNode(SQLConverter.java:180)
~[classes/:?]
at
org.apache.kylin.query.engine.SQLConverter.convertSqlToRelNode(SQLConverter.java:108)
~[classes/:?]
at org.apache.kylin.query.engine.QueryExec.executeQuery(QueryExec.java:188)
~[classes/:?]
... 215 more
[cid:image001.png@01DA52D9.79A24730]
Thanks,
Zhenning Zhang
