This is an automated email from the ASF dual-hosted git repository.
mbudiu pushed a commit to branch main
in repository https://gitbox.apache.org/repos/asf/calcite.git
The following commit(s) were added to refs/heads/main by this push:
new 225933d485 [CALCITE-7052] When conformance specifies isGroupbyAlias =
true the validator rejects legal queries
225933d485 is described below
commit 225933d485130a65f1846f0d0c69b048195bbb56
Author: Mihai Budiu <[email protected]>
AuthorDate: Mon Jun 16 01:41:03 2025 +0200
[CALCITE-7052] When conformance specifies isGroupbyAlias = true the
validator rejects legal queries
Signed-off-by: Mihai Budiu <[email protected]>
---
.../calcite/sql/validate/SqlValidatorImpl.java | 118 ++++--
.../apache/calcite/test/SqlToRelConverterTest.java | 29 ++
.../org/apache/calcite/test/SqlValidatorTest.java | 40 +-
.../apache/calcite/test/SqlToRelConverterTest.xml | 37 +-
core/src/test/resources/sql/aliasing.iq | 465 +++++++++++++++++++++
core/src/test/resources/sql/misc.iq | 18 -
6 files changed, 633 insertions(+), 74 deletions(-)
diff --git
a/core/src/main/java/org/apache/calcite/sql/validate/SqlValidatorImpl.java
b/core/src/main/java/org/apache/calcite/sql/validate/SqlValidatorImpl.java
index dae940982a..69b2baf2d4 100644
--- a/core/src/main/java/org/apache/calcite/sql/validate/SqlValidatorImpl.java
+++ b/core/src/main/java/org/apache/calcite/sql/validate/SqlValidatorImpl.java
@@ -7236,6 +7236,30 @@ public SqlNode go(SqlNode root) {
() -> this + " returned null for " + root);
}
+ /** True if the exception ex indicates that name lookup has failed. */
+ public static boolean isNotFoundException(Exception ex) {
+ if (!(ex instanceof CalciteContextException)) {
+ return false;
+ }
+ String message = ex.getMessage();
+ if (message == null || !message.contains("not found")) {
+ return false;
+ }
+ return true;
+ }
+
+ /** True if the exception ex indicates that a column is ambiguous. */
+ public static boolean isAmbiguousException(Exception ex) {
+ if (!(ex instanceof CalciteContextException)) {
+ return false;
+ }
+ String message = ex.getMessage();
+ if (message == null || !message.contains("is ambiguous")) {
+ return false;
+ }
+ return true;
+ }
+
@Override public @Nullable SqlNode visit(SqlIdentifier id) {
// First check for builtin functions which don't have
// parentheses, like "LOCALTIME".
@@ -7452,11 +7476,7 @@ private SelectExpander(SelectExpander expander) {
try {
return super.visit(id);
} catch (Exception ex) {
- if (!(ex instanceof CalciteContextException)) {
- throw ex;
- }
- String message = ex.getMessage();
- if (message != null && !message.contains("not found")) {
+ if (!isNotFoundException(ex)) {
throw ex;
}
// This point is reached only if the name lookup failed using
standard rules
@@ -7540,7 +7560,10 @@ static class ExtendedExpander extends Expander {
final SqlSelect select;
final SqlNode root;
final Clause clause;
- // Retain only expandable aliases or ordinals to prevent their expansion
in a SQL call expr.
+ // This contains the ordinal nodes in a GROUP BY that may need to be
expanded
+ // into column names when using a conformance that allows ordinals in
group bys
+ // E.g., For GROUP BY 1, 1 will be in this list
+ // For GROUP BY CUBE(1), 1 will also be in this list
final Set<SqlNode> aliasOrdinalExpandSet = Sets.newIdentityHashSet();
ExtendedExpander(SqlValidatorImpl validator, SqlValidatorScope scope,
@@ -7550,7 +7573,7 @@ static class ExtendedExpander extends Expander {
this.root = root;
this.clause = clause;
if (clause == Clause.GROUP_BY) {
- addExpandableExpressions();
+ addExpandableOrdinals();
}
}
@@ -7561,18 +7584,54 @@ static class ExtendedExpander extends Expander {
final SelectScope selectScope =
validator.getRawSelectScopeNonNull(select);
final boolean replaceAliases =
clause.shouldReplaceAliases(validator.config);
- if (!replaceAliases || (clause == Clause.GROUP_BY &&
!aliasOrdinalExpandSet.contains(id))) {
- SqlNode node = expandCommonColumn(select, id, selectScope, validator);
- if (node != id) {
- return node;
+ try {
+ // First try a standard expansion
+ if (clause == Clause.GROUP_BY) {
+ SqlNode node = expandCommonColumn(select, id, selectScope,
validator);
+ if (node != id) {
+ return node;
+ }
+ return super.visit(id);
}
- return super.visit(id);
+ } catch (Exception ex) {
+ // This behavior is from MySQL:
+ // - if there is no column in the FROM with the name used in the GROUP
BY
+ // then look for a column alias in the SELECT
+ // - if there are multiple columns in the FROM with the name used in
the GROUP BY,
+ // then also look for a column alias in the SELECT
+ if (!Expander.isNotFoundException(ex) &&
!Expander.isAmbiguousException(ex)) {
+ throw ex;
+ }
+ if (!replaceAliases) {
+ throw ex;
+ }
+ // Continue execution, trying to replace alias
}
- String name = id.getSimple();
- SqlNode expr = null;
final SqlNameMatcher nameMatcher =
validator.catalogReader.nameMatcher();
+
+ if (clause == Clause.HAVING) {
+ if (!replaceAliases) {
+ return id;
+ }
+
+ // Do not expand aliases in HAVING if they are being grouped on
+ SqlNodeList list = select.getGroup();
+ if (list != null) {
+ // HAVING can be used without GROUP BY
+ for (SqlNode node : list) {
+ if (node instanceof SqlIdentifier) {
+ SqlIdentifier grouped = (SqlIdentifier) node;
+ if (nameMatcher.matches(id.getSimple(),
Util.last(grouped.names))) {
+ return id;
+ }
+ }
+ }
+ }
+ }
+ String name = id.getSimple();
+ SqlNode expr = null;
int n = 0;
for (SqlNode s : SqlNonNullableAccessors.getSelectList(select)) {
final @Nullable String alias = SqlValidatorUtil.alias(s);
@@ -7598,8 +7657,10 @@ static class ExtendedExpander extends Expander {
// expr cannot be null; in that case n = 0 would have returned
requireNonNull(expr, "expr");
- if (validator.getConformance().isSelectAlias()
- != SqlConformance.SelectAliasLookup.UNSUPPORTED) {
+ if ((clause == Clause.SELECT
+ && (validator.getConformance().isSelectAlias()
+ != SqlConformance.SelectAliasLookup.UNSUPPORTED))
+ || clause == Clause.GROUP_BY) {
Map<String, SqlNode> expansions = new HashMap<>();
final Expander expander = new SelectExpander(validator, selectScope,
select, expansions);
expr = expander.go(expr);
@@ -7641,18 +7702,15 @@ static class ExtendedExpander extends Expander {
break;
}
}
-
return super.visit(literal);
}
/**
- * Add all possible expandable 'group by' expression to set, which is
- * used to check whether expr could be expanded as alias or ordinal.
+ * Add all possible expandable 'group by' ordinals to {@link
aliasOrdinalExpandSet}.
*/
@RequiresNonNull({"root"})
- private void addExpandableExpressions(@UnknownInitialization
ExtendedExpander this) {
+ private void addExpandableOrdinals(@UnknownInitialization ExtendedExpander
this) {
switch (root.getKind()) {
- case IDENTIFIER:
case LITERAL:
aliasOrdinalExpandSet.add(root);
break;
@@ -7662,7 +7720,7 @@ private void
addExpandableExpressions(@UnknownInitialization ExtendedExpander th
if (root instanceof SqlBasicCall) {
List<SqlNode> operandList = ((SqlBasicCall) root).getOperandList();
for (SqlNode sqlNode : operandList) {
- addIdentifierOrdinal2ExpandSet(sqlNode);
+ addOrdinal2ExpandSet(sqlNode);
}
}
break;
@@ -7672,18 +7730,18 @@ private void
addExpandableExpressions(@UnknownInitialization ExtendedExpander th
}
/**
- * Identifier or literal in grouping sets, rollup, cube will be eligible
for alias.
+ * Literal in grouping sets, rollup, cube will be expanded.
*
* @param sqlNode expression within grouping sets, rollup, cube
*/
- private void addIdentifierOrdinal2ExpandSet(@UnknownInitialization
ExtendedExpander this,
+ private void addOrdinal2ExpandSet(@UnknownInitialization ExtendedExpander
this,
SqlNode sqlNode) {
if (sqlNode.getKind() == SqlKind.ROW) {
List<SqlNode> rowOperandList = ((SqlCall) sqlNode).getOperandList();
for (SqlNode node : rowOperandList) {
- addIdentifierOrdinal2ExpandSet(node);
+ addOrdinal2ExpandSet(node);
}
- } else if (sqlNode.getKind() == SqlKind.IDENTIFIER || sqlNode.getKind()
== SqlKind.LITERAL) {
+ } else if (sqlNode.getKind() == SqlKind.LITERAL) {
aliasOrdinalExpandSet.add(sqlNode);
}
}
@@ -8218,14 +8276,10 @@ private enum Clause {
boolean shouldReplaceAliases(Config config) {
switch (this) {
case GROUP_BY:
- return config.conformance().isGroupByAlias()
- || (config.conformance().isSelectAlias()
- != SqlConformance.SelectAliasLookup.UNSUPPORTED);
+ return config.conformance().isGroupByAlias();
case HAVING:
- return config.conformance().isHavingAlias()
- || (config.conformance().isSelectAlias()
- != SqlConformance.SelectAliasLookup.UNSUPPORTED);
+ return config.conformance().isHavingAlias();
case QUALIFY:
return true;
diff --git
a/core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java
b/core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java
index 02afa42041..761433d147 100644
--- a/core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java
+++ b/core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java
@@ -461,7 +461,12 @@ public static void checkActualAndReferenceFiles() {
}
@Test void testGroupByAliasEqualToColumnName() {
+ // If the alias (deptno) matches an existing column, it is not used in the
GROUP BY
sql("select empno, ename as deptno from emp group by empno, deptno")
+ .withConformance(SqlConformanceEnum.LENIENT)
+ .throws_("Expression 'ENAME' is not being grouped");
+ // If the alias is a new one, it is used in the GROUP BY
+ sql("select empno, ename as x from emp group by empno, x")
.withConformance(SqlConformanceEnum.LENIENT).ok();
}
@@ -508,6 +513,30 @@ public static void checkActualAndReferenceFiles() {
.ok();
}
+ /**
+ * Test case for
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-4512";>[CALCITE-4512]
+ * GROUP BY expression with argument name same with SELECT field and alias
causes
+ * validation error</a>.
+ */
+ @Test void testGroupByExprArgFieldSameWithAlias3() {
+ // Same as the test above, but different conformance.
+ // Must produce the exact same plan.
+ final String sql = "SELECT deptno / 2 AS deptno, deptno / 2 as empno,
sum(sal)\n"
+ + "FROM emp\n"
+ + "GROUP BY GROUPING SETS "
+ + "((deptno), (empno, deptno / 2), (2, 1), ((1, 2), (deptno, deptno /
2)))";
+ sql(sql)
+ .withConformance(
+ // This ensures that numbers in grouping sets are interpreted as
column numbers
+ new SqlDelegatingConformance(SqlConformanceEnum.DEFAULT) {
+ @Override public boolean isGroupByOrdinal() {
+ return true;
+ }
+ })
+ .ok();
+ }
+
@Test void testAliasInHaving() {
sql("select count(empno) as e from emp having e > 1")
.withConformance(SqlConformanceEnum.LENIENT).ok();
diff --git a/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java
b/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java
index e7a5699b8e..958be01b8b 100644
--- a/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java
+++ b/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java
@@ -293,6 +293,26 @@ static SqlOperatorTable operatorTableFor(SqlLibrary
library) {
.fails("(?s).*Illegal TIMESTAMP literal.*");
}
+ /**
+ * Test case for <a
href="https://issues.apache.org/jira/browse/CALCITE-7052";>[CALCITE-7052]
+ * When conformance specifies isGroupbyAlias = true the validator rejects
legal queries</a>.
+ */
+ @Test void testHavingTableAlias() {
+ sql("select ename as deptno from emp as e join dept as d on "
+ + "e.deptno = d.deptno group by ^deptno^")
+ .fails("Column 'DEPTNO' is ambiguous");
+ sql("select ename as deptno from emp as e join dept as d on "
+ + "e.deptno = d.deptno group by ^deptno^")
+ .withConformance(SqlConformanceEnum.LENIENT)
+ .ok();
+ sql("SELECT ALL - cor0.empno AS empno "
+ + "FROM emp AS cor0 GROUP BY empno HAVING NOT ( cor0.empno ) IS NULL")
+ .withConformance(SqlConformanceEnum.LENIENT)
+ .ok();
+ sql("select floor(empno/2) as empno from emp group by empno")
+ .withConformance(SqlConformanceEnum.LENIENT).ok();
+ }
+
/** PostgreSQL and Redshift allow TIMESTAMP literals that contain only a
* date part. */
@Test void testShortTimestampLiteral() {
@@ -6556,6 +6576,7 @@ public boolean isBangEqualAllowed() {
.withConformance(lenient).ok();
sql("select ename as deptno from emp as e join dept as d on "
+ "e.deptno = d.deptno group by ^deptno^")
+ .withConformance(strict).fails("Column 'DEPTNO' is ambiguous")
.withConformance(lenient).ok();
sql("select t.e, count(*) from (select empno as e from emp) t group by e")
.withConformance(strict).ok()
@@ -6589,12 +6610,14 @@ public boolean isBangEqualAllowed() {
sql("select deptno + empno as d, deptno + empno + mgr from emp"
+ " group by d,mgr")
.withConformance(lenient).ok();
- // When alias is equal to one or more columns in the query then giving
- // priority to alias. But Postgres may throw ambiguous column error or give
- // priority to column name.
+ // Alias is not used since there is a single column in the SELECT already
matching
sql("select count(*) from (\n"
- + " select ename AS deptno FROM emp GROUP BY deptno) t")
- .withConformance(lenient).ok();
+ + " select ^ename^ AS deptno FROM emp GROUP BY deptno) t")
+ .withConformance(strict)
+ .fails("Expression 'ENAME' is not being grouped")
+ .withConformance(lenient)
+ .fails("Expression 'ENAME' is not being grouped");
+ // When alias matches multiple columns in the SELECT, alias takes
precedence
sql("select count(*) from "
+ "(select ename AS deptno FROM emp, dept GROUP BY deptno) t")
.withConformance(lenient).ok();
@@ -6806,9 +6829,10 @@ public boolean isBangEqualAllowed() {
sql("select ^e.empno^ from emp as e group by 1 having e.empno > 10")
.withConformance(strict).fails("Expression 'E.EMPNO' is not being
grouped")
.withConformance(lenient).ok();
- // When alias is equal to one or more columns in the query then giving
- // priority to alias, but PostgreSQL throws ambiguous column error or gives
- // priority to column name.
+ // When alias matches multiple columns in the query, alias has priority.
+ sql("select ename AS deptno FROM emp, dept GROUP BY ^deptno^ HAVING deptno
= 2")
+ .withConformance(strict).fails("Column 'DEPTNO' is ambiguous")
+ .withConformance(lenient).ok();
sql("select count(empno) as deptno from emp having ^deptno^ > 10")
.withConformance(strict).fails("Expression 'DEPTNO' is not being
grouped")
.withConformance(lenient).ok();
diff --git
a/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml
b/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml
index 105ba0f9e4..cde9a7b197 100644
--- a/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml
+++ b/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml
@@ -2302,12 +2302,12 @@ LogicalAggregate(group=[{0}])
<Resource name="plan">
<![CDATA[
LogicalAggregate(group=[{0, 1}])
- LogicalProject(EMPNO=[$0], DEPTNO=[$1])
+ LogicalProject(EMPNO=[$0], X=[$1])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
]]>
</Resource>
<Resource name="sql">
- <![CDATA[select empno, ename as deptno from emp group by empno, deptno]]>
+ <![CDATA[select empno, ename as x from emp group by empno, x]]>
</Resource>
</TestCase>
<TestCase name="testGroupByAliasOfSubExpressionsInProject">
@@ -2476,20 +2476,25 @@ GROUP BY GROUPING SETS ((deptno), (empno, deptno / 2),
(2, 1), ((1, 2), (deptno,
</Resource>
<Resource name="plan">
<![CDATA[
-LogicalProject(DEPTNO=[$0], EMPNO=[$0], EXPR$2=[$1])
- LogicalUnion(all=[true])
- LogicalAggregate(group=[{0}], EXPR$2=[SUM($1)])
- LogicalProject(EMPNO=[/($7, 2)], SAL=[$5])
- LogicalTableScan(table=[[CATALOG, SALES, EMP]])
- LogicalAggregate(group=[{0}], EXPR$2=[SUM($1)])
- LogicalProject(EMPNO=[/($7, 2)], SAL=[$5])
- LogicalTableScan(table=[[CATALOG, SALES, EMP]])
- LogicalAggregate(group=[{0}], EXPR$2=[SUM($1)])
- LogicalProject(EMPNO=[/($7, 2)], SAL=[$5])
- LogicalTableScan(table=[[CATALOG, SALES, EMP]])
- LogicalAggregate(group=[{0}], EXPR$2=[SUM($1)])
- LogicalProject(EMPNO=[/($7, 2)], SAL=[$5])
- LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+LogicalProject(DEPTNO=[$2], EMPNO=[$2], EXPR$2=[$3])
+ LogicalAggregate(group=[{0, 1, 2}], groups=[[{0, 2}, {0}, {1, 2}, {2}]],
EXPR$2=[SUM($3)])
+ LogicalProject(DEPTNO=[$7], EMPNO=[$0], $f2=[/($7, 2)], SAL=[$5])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testGroupByExprArgFieldSameWithAlias3">
+ <Resource name="sql">
+ <![CDATA[SELECT deptno / 2 AS deptno, deptno / 2 as empno, sum(sal)
+FROM emp
+GROUP BY GROUPING SETS ((deptno), (empno, deptno / 2), (2, 1), ((1, 2),
(deptno, deptno / 2)))]]>
+ </Resource>
+ <Resource name="plan">
+ <![CDATA[
+LogicalProject(DEPTNO=[$2], EMPNO=[$2], EXPR$2=[$3])
+ LogicalAggregate(group=[{0, 1, 2}], groups=[[{0, 2}, {0}, {1, 2}, {2}]],
EXPR$2=[SUM($3)])
+ LogicalProject(DEPTNO=[$7], EMPNO=[$0], $f2=[/($7, 2)], SAL=[$5])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
]]>
</Resource>
</TestCase>
diff --git a/core/src/test/resources/sql/aliasing.iq
b/core/src/test/resources/sql/aliasing.iq
new file mode 100644
index 0000000000..2d36e2b4ae
--- /dev/null
+++ b/core/src/test/resources/sql/aliasing.iq
@@ -0,0 +1,465 @@
+# aliasing.iq - Test for column aliases in GROUP BY, HAVING, SELECT
+#
+# Licensed to the Apache Software Foundation (ASF) under one or more
+# contributor license agreements. See the NOTICE file distributed with
+# this work for additional information regarding copyright ownership.
+# The ASF licenses this file to you under the Apache License, Version 2.0
+# (the "License"); you may not use this file except in compliance with
+# the License. You may obtain a copy of the License at
+#
+# http://www.apache.org/licenses/LICENSE-2.0
+#
+# Unless required by applicable law or agreed to in writing, software
+# distributed under the License is distributed on an "AS IS" BASIS,
+# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+# See the License for the specific language governing permissions and
+# limitations under the License.
+#
+
+# They exercise various aliasing options for GROUP BY, HAVING, and SELECT.
+
+!use scott
+!set outputformat mysql
+
+# [CALCITE-4512] GROUP BY expression with argument name same with SELECT field
and alias causes validation error
+SELECT floor(empno/2) as empno
+FROM emp
+GROUP BY floor(empno/2);
+
+SELECT FLOOR("EMP"."EMPNO" / 2) AS "EMPNO"
+FROM "scott"."EMP" AS "EMP"
+GROUP BY FLOOR("EMP"."EMPNO" / 2)
+!explain-validated-on calcite
+#
++-------+
+| EMPNO |
++-------+
+| 3684 |
+| 3749 |
+| 3760 |
+| 3783 |
+| 3827 |
+| 3849 |
+| 3891 |
+| 3894 |
+| 3919 |
+| 3922 |
+| 3938 |
+| 3950 |
+| 3951 |
+| 3967 |
++-------+
+(14 rows)
+
+!ok
+
+!use scott-lenient
+
+# Result should be the same as above
+# Validated using MySQL
+SELECT floor(empno/2) as empno
+FROM emp
+GROUP BY floor(empno/2);
+
+SELECT FLOOR("EMP"."EMPNO" / 2) AS "EMPNO"
+FROM "scott"."EMP" AS "EMP"
+GROUP BY FLOOR("EMP"."EMPNO" / 2)
+!explain-validated-on calcite
+#
++-------+
+| EMPNO |
++-------+
+| 3684 |
+| 3749 |
+| 3760 |
+| 3783 |
+| 3827 |
+| 3849 |
+| 3891 |
+| 3894 |
+| 3919 |
+| 3922 |
+| 3938 |
+| 3950 |
+| 3951 |
+| 3967 |
++-------+
+(14 rows)
+
+!ok
+
+# Test cases for CALCITE-7052
+# When conformance specifies isGroupbyAlias = true the validator rejects legal
queries
+# Result validated using MySQL
+SELECT floor(empno/2) as empno
+FROM emp
+GROUP BY empno;
+
+SELECT FLOOR("EMP"."EMPNO" / 2) AS "EMPNO"
+FROM "scott"."EMP" AS "EMP"
+GROUP BY "EMP"."EMPNO"
+!explain-validated-on calcite
+#
++-------+
+| EMPNO |
++-------+
+| 3684 |
+| 3749 |
+| 3760 |
+| 3783 |
+| 3827 |
+| 3849 |
+| 3891 |
+| 3894 |
+| 3919 |
+| 3922 |
+| 3938 |
+| 3950 |
+| 3951 |
+| 3967 |
++-------+
+(14 rows)
+
+!ok
+
+# Result validated using MySQL
+SELECT floor(empno/100) as empno
+FROM emp
+GROUP BY empno;
+
+SELECT FLOOR("EMP"."EMPNO" / 100) AS "EMPNO"
+FROM "scott"."EMP" AS "EMP"
+GROUP BY "EMP"."EMPNO"
+!explain-validated-on calcite
+#
++-------+
+| EMPNO |
++-------+
+| 73 |
+| 74 |
+| 75 |
+| 75 |
+| 76 |
+| 76 |
+| 77 |
+| 77 |
+| 78 |
+| 78 |
+| 78 |
+| 79 |
+| 79 |
+| 79 |
++-------+
+(14 rows)
+
+!ok
+
+# Result validated using MySQL
+SELECT floor(empno/100) as empno
+FROM emp
+GROUP BY floor(empno/100);
+
+SELECT FLOOR("EMP"."EMPNO" / 100) AS "EMPNO"
+FROM "scott"."EMP" AS "EMP"
+GROUP BY FLOOR("EMP"."EMPNO" / 100)
+!explain-validated-on calcite
+#
++-------+
+| EMPNO |
++-------+
+| 73 |
+| 74 |
+| 75 |
+| 76 |
+| 77 |
+| 78 |
+| 79 |
++-------+
+(7 rows)
+
+!ok
+
+# Result validated using MySQL
+SELECT floor(empno/100) as X
+FROM emp
+GROUP BY X;
+
+SELECT FLOOR("EMP"."EMPNO" / 100) AS "X"
+FROM "scott"."EMP" AS "EMP"
+GROUP BY FLOOR("EMP"."EMPNO" / 100)
+!explain-validated-on calcite
+#
++----+
+| X |
++----+
+| 73 |
+| 74 |
+| 75 |
+| 76 |
+| 77 |
+| 78 |
+| 79 |
++----+
+(7 rows)
+
+!ok
+
+# MySQL also gives an error, but a different error message
+SELECT sum(empno) as X
+FROM emp
+GROUP BY X;
+Aggregate expression is illegal in GROUP BY clause
+!error
+
+# Validated on MySQL, by replacing / with DIV, which does integer division in
MySQL
+SELECT sum(empno / 100) as empno
+FROM EMP
+GROUP BY empno / 100;
+
+SELECT SUM("EMP"."EMPNO" / 100) AS "EMPNO"
+FROM "scott"."EMP" AS "EMP"
+GROUP BY "EMP"."EMPNO" / 100
+!explain-validated-on calcite
+#
++-------+
+| EMPNO |
++-------+
+| 73 |
+| 74 |
+| 150 |
+| 152 |
+| 154 |
+| 234 |
+| 237 |
++-------+
+(7 rows)
+
+!ok
+
+# Validated on MySQL, by replacing / with DIV
+SELECT sum(empno / 100), EMPNO / 100 as X
+FROM EMP
+GROUP BY X
+ORDER BY X;
+
+SELECT SUM("EMP"."EMPNO" / 100), "EMP"."EMPNO" / 100 AS "X"
+FROM "scott"."EMP" AS "EMP"
+GROUP BY "EMP"."EMPNO" / 100
+ORDER BY "X"
+!explain-validated-on calcite
+#
++--------+----+
+| EXPR$0 | X |
++--------+----+
+| 73 | 73 |
+| 74 | 74 |
+| 150 | 75 |
+| 152 | 76 |
+| 154 | 77 |
+| 234 | 78 |
+| 237 | 79 |
++--------+----+
+(7 rows)
+
+!ok
+
+# Validated on MySQL, by replacing / with DIV
+SELECT sum(empno / 100), EMPNO / 100 as X
+FROM EMP
+GROUP BY X
+HAVING X > 75;
+
+SELECT SUM("EMP"."EMPNO" / 100), "EMP"."EMPNO" / 100 AS "X"
+FROM "scott"."EMP" AS "EMP"
+GROUP BY "EMP"."EMPNO" / 100
+HAVING "EMP"."EMPNO" / 100 > 75
+!explain-validated-on calcite
+#
++--------+----+
+| EXPR$0 | X |
++--------+----+
+| 152 | 76 |
+| 154 | 77 |
+| 234 | 78 |
+| 237 | 79 |
++--------+----+
+(4 rows)
+
+!ok
+
+# Validated on MySQL, by replacing / with DIV
+SELECT sum(empno / 100), EMPNO / 100 as empno
+FROM EMP
+GROUP BY empno
+HAVING empno > 75;
+
+SELECT SUM("EMP"."EMPNO" / 100), "EMP"."EMPNO" / 100 AS "EMPNO"
+FROM "scott"."EMP" AS "EMP"
+GROUP BY "EMP"."EMPNO"
+HAVING CAST("EMP"."EMPNO" AS INTEGER) > 75
+!explain-validated-on calcite
+#
++--------+-------+
+| EXPR$0 | EMPNO |
++--------+-------+
+| 73 | 73 |
+| 74 | 74 |
+| 75 | 75 |
+| 75 | 75 |
+| 76 | 76 |
+| 76 | 76 |
+| 77 | 77 |
+| 77 | 77 |
+| 78 | 78 |
+| 78 | 78 |
+| 78 | 78 |
+| 79 | 79 |
+| 79 | 79 |
+| 79 | 79 |
++--------+-------+
+(14 rows)
+
+!ok
+
+# Validated on MySQL
+SELECT empno AS X
+FROM EMP, DEPT
+GROUP BY X
+HAVING empno > 75;
+
+SELECT "EMP"."EMPNO" AS "X"
+FROM "scott"."EMP" AS "EMP",
+ "scott"."DEPT" AS "DEPT"
+GROUP BY "EMP"."EMPNO"
+HAVING CAST("EMP"."EMPNO" AS INTEGER) > 75
+!explain-validated-on calcite
+#
++------+
+| X |
++------+
+| 7369 |
+| 7499 |
+| 7521 |
+| 7566 |
+| 7654 |
+| 7698 |
+| 7782 |
+| 7788 |
+| 7839 |
+| 7844 |
+| 7876 |
+| 7900 |
+| 7902 |
+| 7934 |
++------+
+(14 rows)
+
+!ok
+
+# Validated on MySQL by replacing / with DIV
+SELECT empno AS X
+FROM EMP, DEPT
+GROUP BY empno / 100
+HAVING empno > 75;
+Expression 'EMPNO' is not being grouped
+!error
+
+# Validated on MySQL by replacing / with DIV
+SELECT empno / 100 AS X
+FROM EMP, DEPT
+GROUP BY empno / 100
+HAVING empno > 75;
+Expression 'EMPNO' is not being grouped
+!error
+
+# Validated on MySQL by changing MOD to %
+SELECT MAX(empno)
+FROM EMP
+GROUP BY deptno
+HAVING MOD(MAX(empno), 2) = 0;
+
+SELECT MAX("EMP"."EMPNO")
+FROM "scott"."EMP" AS "EMP"
+GROUP BY "EMP"."DEPTNO"
+HAVING MOD(MAX("EMP"."EMPNO"), 2) = 0
+!explain-validated-on calcite
+#
++--------+
+| EXPR$0 |
++--------+
+| 7900 |
+| 7902 |
+| 7934 |
++--------+
+(3 rows)
+
+!ok
+
+# Validated on MySQL by changing MOD to %
+SELECT MAX(empno) AS X
+FROM EMP
+GROUP BY deptno
+HAVING MOD(X, 2) = 0;
+
+SELECT MAX("EMP"."EMPNO") AS "X"
+FROM "scott"."EMP" AS "EMP"
+GROUP BY "EMP"."DEPTNO"
+HAVING MOD(MAX("EMP"."EMPNO"), 2) = 0
+!explain-validated-on calcite
+#
++------+
+| X |
++------+
+| 7900 |
+| 7902 |
+| 7934 |
++------+
+(3 rows)
+
+!ok
+
+# Validated on MySQL by changing MOD to %
+# The alias 'deptno' is substituted in HAVING
+SELECT MAX(empno) AS deptno
+FROM EMP
+HAVING MOD(deptno, 2) = 0;
+
+SELECT MAX("EMP"."EMPNO") AS "DEPTNO"
+FROM "scott"."EMP" AS "EMP"
+HAVING MOD(MAX("EMP"."EMPNO"), 2) = 0
+!explain-validated-on calcite
+#
++--------+
+| DEPTNO |
++--------+
+| 7934 |
++--------+
+(1 row)
+
+!ok
+
+# Validated on MySQL by changing MOD to %
+# The alias 'deptno' if not substituted in HAVING because it is grouped on
+SELECT MAX(empno) AS deptno
+FROM EMP
+GROUP BY deptno
+HAVING MOD(deptno, 2) = 0;
+
+SELECT MAX("EMP"."EMPNO") AS "DEPTNO"
+FROM "scott"."EMP" AS "EMP"
+GROUP BY "EMP"."DEPTNO"
+HAVING MOD("EMP"."DEPTNO", 2) = 0
+!explain-validated-on calcite
+#
++--------+
+| DEPTNO |
++--------+
+| 7900 |
+| 7902 |
+| 7934 |
++--------+
+(3 rows)
+
+!ok
+
+# End aliasing.iq
diff --git a/core/src/test/resources/sql/misc.iq
b/core/src/test/resources/sql/misc.iq
index 4879f1eab8..6db6e0bd45 100644
--- a/core/src/test/resources/sql/misc.iq
+++ b/core/src/test/resources/sql/misc.iq
@@ -2467,24 +2467,6 @@ FROM "hr"."emps";
!ok
-!use post
-
-# [CALCITE-4512] GROUP BY expression with argument name same with SELECT field
and alias causes validation error
-SELECT floor(empno/2) as empno
-FROM emps
-GROUP BY floor(empno/2);
-+-------+
-| EMPNO |
-+-------+
-| 50 |
-| 55 |
-| 60 |
-| 65 |
-+-------+
-(4 rows)
-
-!ok
-
# [CALCITE-6566] JDBC adapter should generate PI function with parentheses in
most dialects
!use scott-mysql
