You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@calcite.apache.org by jh...@apache.org on 2023/05/04 22:41:41 UTC
[calcite] 01/02: [CALCITE-5653] SELECT DISTINCT aggregate function with ORDER BY gives invalid validation error
This is an automated email from the ASF dual-hosted git repository.
jhyde pushed a commit to branch main
in repository https://gitbox.apache.org/repos/asf/calcite.git
commit 788a4404a8c3fe72983f865f2d5af51dce04aa72
Author: Itiel Sadeh <it...@sqreamtech.com>
AuthorDate: Tue Apr 18 10:09:29 2023 +0300
[CALCITE-5653] SELECT DISTINCT aggregate function with ORDER BY gives invalid validation error
For example, the query
SELECT distinct sum(deptno + '1') FROM dept ORDER BY 1
throws
Expression 'SUM(`DEPT`.`DEPTNO` + CAST('1' AS INTEGER))'
is not in the select clause
The fix is for AggChecker to look in the expanded select list.
Close apache/calcite#3160
---
.../org/apache/calcite/sql/validate/AggChecker.java | 20 +++++++++++---------
.../org/apache/calcite/test/SqlValidatorTest.java | 6 ++++++
core/src/test/resources/sql/agg.iq | 11 +++++++++++
3 files changed, 28 insertions(+), 9 deletions(-)
diff --git a/core/src/main/java/org/apache/calcite/sql/validate/AggChecker.java b/core/src/main/java/org/apache/calcite/sql/validate/AggChecker.java
index 170411eafe..9a93718c38 100644
--- a/core/src/main/java/org/apache/calcite/sql/validate/AggChecker.java
+++ b/core/src/main/java/org/apache/calcite/sql/validate/AggChecker.java
@@ -20,7 +20,6 @@ import org.apache.calcite.sql.SqlCall;
import org.apache.calcite.sql.SqlIdentifier;
import org.apache.calcite.sql.SqlKind;
import org.apache.calcite.sql.SqlNode;
-import org.apache.calcite.sql.SqlNodeList;
import org.apache.calcite.sql.SqlSelect;
import org.apache.calcite.sql.SqlWindow;
import org.apache.calcite.sql.fun.SqlStdOperatorTable;
@@ -33,7 +32,6 @@ import java.util.ArrayDeque;
import java.util.Deque;
import java.util.List;
-import static org.apache.calcite.sql.validate.SqlNonNullableAccessors.getSelectList;
import static org.apache.calcite.util.Static.RESOURCE;
import static java.util.Objects.requireNonNull;
@@ -142,12 +140,18 @@ class AggChecker extends SqlBasicVisitor<Void> {
}
@Override public Void visit(SqlCall call) {
- final SqlValidatorScope scope = scopes.peek();
+ final SqlValidatorScope scope =
+ requireNonNull(scopes.peek(), () -> "scope for " + call);
if (call.getOperator().isAggregator()) {
if (distinct) {
if (scope instanceof AggregatingSelectScope) {
- SqlNodeList selectList =
- getSelectList((SqlSelect) scope.getNode());
+ final SqlSelect select = (SqlSelect) scope.getNode();
+ SelectScope selectScope =
+ requireNonNull(validator.getRawSelectScope(select),
+ () -> "rawSelectScope for " + scope.getNode());
+ List<SqlNode> selectList =
+ requireNonNull(selectScope.getExpandedSelectList(),
+ () -> "expandedSelectList for " + selectScope);
// Check if this aggregation function is just an element in the select
for (SqlNode sqlNode : selectList) {
@@ -196,8 +200,7 @@ class AggChecker extends SqlBasicVisitor<Void> {
} else if (over instanceof SqlIdentifier) {
// Check the corresponding SqlWindow in WINDOW clause
final SqlWindow window =
- requireNonNull(scope, () -> "scope for " + call)
- .lookupWindow(((SqlIdentifier) over).getSimple());
+ scope.lookupWindow(((SqlIdentifier) over).getSimple());
requireNonNull(window, () -> "window for " + call);
window.getPartitionList().accept(this);
window.getOrderList().accept(this);
@@ -234,8 +237,7 @@ class AggChecker extends SqlBasicVisitor<Void> {
}
// Switch to new scope.
- SqlValidatorScope newScope = requireNonNull(scope, () -> "scope for " + call)
- .getOperandScope(call);
+ SqlValidatorScope newScope = scope.getOperandScope(call);
scopes.push(newScope);
// Visit the operands (only expressions).
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 f84a1b1b79..f0f7395e7b 100644
--- a/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java
+++ b/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java
@@ -5903,6 +5903,12 @@ public class SqlValidatorTest extends SqlValidatorTestCase {
+ " order by upper(^eno^)")
.failsIf(!conformance.isSortByAlias(),
"Column 'ENO' not found in any table");
+
+ // Test case for [CALCITE-5653], order by on aggregate will fail when there is an implicit cast
+ // and IdentifierExpansion is off
+ sql("select distinct sum(deptno + '1') from dept order by 1")
+ .withValidatorIdentifierExpansion(false)
+ .ok();
}
/**
diff --git a/core/src/test/resources/sql/agg.iq b/core/src/test/resources/sql/agg.iq
index 1210727959..f359d06de6 100644
--- a/core/src/test/resources/sql/agg.iq
+++ b/core/src/test/resources/sql/agg.iq
@@ -3585,5 +3585,16 @@ from emp;
+----+----+-------+
(1 row)
+!ok
+
+# DISTINCT query with ORDER BY on aggregate when there is an implicit cast
+select distinct sum(deptno + '1') as deptsum from dept order by 1;
++---------+
+| DEPTSUM |
++---------+
+| 104 |
++---------+
+(1 row)
+
!ok
# End agg.iq