This is a known issue. It seems not easy to fix. In my opinion, the alias of the measure should not have the same name as the column that already exists in the table.
On Mon, Jan 29, 2024 at 5:43 PM Zhang, Zhenning <zhennzh...@ebay.com.invalid> wrote: > 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 > > > > > > Thanks, > > Zhenning Zhang > > > > > > > > >
