[
https://issues.apache.org/jira/browse/CALCITE-4452?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Zhen Chen resolved CALCITE-4452.
--------------------------------
Resolution: Fixed
> The query column is allowed not in the group by in MySQL
> ---------------------------------------------------------
>
> Key: CALCITE-4452
> URL: https://issues.apache.org/jira/browse/CALCITE-4452
> Project: Calcite
> Issue Type: Improvement
> Components: core
> Reporter: super486
> Priority: Major
>
> Support behaves the same as MySQL with ONLY_FULL_GROUP_BY disabled which is
> default behaves of MySQL
> In MySQL， query column can be not contained in group by columns when sql_mode
> is not ONLY_FULL_GROUP_BY, the final column value is first column value in
> group.
> CREATE TABLE `a` (
> `id` int(11) DEFAULT NULL,
> `a` int(11) DEFAULT NULL,
> `c` varchar(10) DEFAULT NULL
> ) ENGINE=InnoDB DEFAULT CHARSET=utf8；
> CREATE TABLE `b` (
> `id` int(11) DEFAULT NULL,
> `a` int(11) DEFAULT NULL
> ) ENGINE=InnoDB DEFAULT CHARSET=utf8
> select concat(a.a,b.a) from a join b on a.a=b.a group by a.c;
> Caused by: org.apache.calcite.sql.validate.SqlValidatorException: Expression
> 'a.a' is not being groupedCaused by:
> org.apache.calcite.sql.validate.SqlValidatorException: Expression 'a.a' is
> not being grouped at
> sun.reflect.GeneratedConstructorAccessor45.newInstance(Unknown Source) ~[?:?]
> at
> sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
> ~[?:1.8.0_252] at
> java.lang.reflect.Constructor.newInstance(Constructor.java:423)
> ~[?:1.8.0_252] at
> org.apache.calcite.runtime.Resources$ExInstWithCause.ex(Resources.java:467)
> ~[calcite-core-1.26.0.jar:1.26.0] at
> org.apache.calcite.runtime.Resources$ExInst.ex(Resources.java:560)
> ~[calcite-core-1.26.0.jar:1.26.0] at
> org.apache.calcite.sql.SqlUtil.newContextException(SqlUtil.java:883)
> ~[calcite-core-1.26.0.jar:1.26.0] at
> org.apache.calcite.sql.SqlUtil.newContextException(SqlUtil.java:868)
> ~[calcite-core-1.26.0.jar:1.26.0] at
> org.apache.calcite.sql.validate.SqlValidatorImpl.newValidationError(SqlValidatorImpl.java:5043)
> ~[calcite-core-1.26.0.jar:1.26.0] at
> org.apache.calcite.sql.validate.AggChecker.visit(AggChecker.java:113)
> ~[calcite-core-1.26.0.jar:1.26.0] at
> org.apache.calcite.sql.validate.AggChecker.visit(AggChecker.java:40)
> ~[calcite-core-1.26.0.jar:1.26.0] at
> org.apache.calcite.sql.SqlIdentifier.accept(SqlIdentifier.java:320)
> ~[calcite-core-1.26.0.jar:1.26.0] at
> org.apache.calcite.sql.util.SqlBasicVisitor$ArgHandlerImpl.visitChild(SqlBasicVisitor.java:123)
> ~[calcite-core-1.26.0.jar:1.26.0] at
> org.apache.calcite.sql.SqlOperator.acceptCall(SqlOperator.java:879)
> ~[calcite-core-1.26.0.jar:1.26.0] at
> org.apache.calcite.sql.validate.AggChecker.visit(AggChecker.java:212)
> ~[calcite-core-1.26.0.jar:1.26.0] at
> org.apache.calcite.sql.validate.AggChecker.visit(AggChecker.java:40)
> ~[calcite-core-1.26.0.jar:1.26.0] at
> org.apache.calcite.sql.SqlCall.accept(SqlCall.java:139)
> ~[calcite-core-1.26.0.jar:1.26.0] at
> org.apache.calcite.sql.util.SqlBasicVisitor$ArgHandlerImpl.visitChild(SqlBasicVisitor.java:123)
> ~[calcite-core-1.26.0.jar:1.26.0] at
> org.apache.calcite.sql.SqlAsOperator.acceptCall(SqlAsOperator.java:121)
> ~[calcite-core-1.26.0.jar:1.26.0] at
> org.apache.calcite.sql.validate.AggChecker.visit(AggChecker.java:212)
> ~[calcite-core-1.26.0.jar:1.26.0] at
> org.apache.calcite.sql.validate.AggChecker.visit(AggChecker.java:40)
> ~[calcite-core-1.26.0.jar:1.26.0] at
> org.apache.calcite.sql.SqlCall.accept(SqlCall.java:139)
> ~[calcite-core-1.26.0.jar:1.26.0] at
> org.apache.calcite.sql.validate.AggregatingSelectScope.checkAggregateExpr(AggregatingSelectScope.java:209)
> ~[calcite-core-1.26.0.jar:1.26.0] at
> org.apache.calcite.sql.validate.AggregatingSelectScope.validateExpr(AggregatingSelectScope.java:218)
> ~[calcite-core-1.26.0.jar:1.26.0] at
> org.apache.calcite.sql.validate.SqlValidatorImpl.validateExpr(SqlValidatorImpl.java:4263)
> ~[calcite-core-1.26.0.jar:1.26.0] at
> org.apache.calcite.sql.validate.SqlValidatorImpl.validateSelectList(SqlValidatorImpl.java:4234)
> ~[calcite-core-1.26.0.jar:1.26.0] at
> org.apache.calcite.sql.validate.SqlValidatorImpl.validateSelect(SqlValidatorImpl.java:3474)
> ~[calcite-core-1.26.0.jar:1.26.0] at
> org.apache.calcite.sql.validate.SelectNamespace.validateImpl(SelectNamespace.java:60)
> ~[calcite-core-1.26.0.jar:1.26.0] at
> org.apache.calcite.sql.validate.AbstractNamespace.validate(AbstractNamespace.java:84)
> ~[calcite-core-1.26.0.jar:1.26.0] at
> org.apache.calcite.sql.validate.SqlValidatorImpl.validateNamespace(SqlValidatorImpl.java:1067)
> ~[calcite-core-1.26.0.jar:1.26.0] at
> org.apache.calcite.sql.validate.SqlValidatorImpl.validateQuery(SqlValidatorImpl.java:1041)
> ~[calcite-core-1.26.0.jar:1.26.0] at
> org.apache.calcite.sql.SqlSelect.validate(SqlSelect.java:232)
> ~[calcite-core-1.26.0.jar:1.26.0] at
> org.apache.calcite.sql.validate.SqlValidatorImpl.validateScopedExpression(SqlValidatorImpl.java:1016)
> ~[calcite-core-1.26.0.jar:1.26.0] at
> org.apache.calcite.sql.validate.SqlValidatorImpl.validate(SqlValidatorImpl.java:724)
> ~[calcite-core-1.26.0.jar:1.26.0] at
> org.apache.calcite.prepare.PlannerImpl.validate(PlannerImpl.java:215)
> ~[calcite-core-1.26.0.jar:1.26.0] ... 12 more
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
