[jira] [Created] (CALCITE-4452) The query column is allowed not in the group by in MySQL

["Quan Chao (Jira)" <ji...@apache.org>]

Quan Chao created CALCITE-4452:
----------------------------------

             Summary: 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: Bug
          Components: core
    Affects Versions: 1.26.0
            Reporter: Quan Chao
             Fix For: next


In MySQL， query column can be not contained in group by columns，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.3.4#803005)