This is an automated email from the ASF dual-hosted git repository.
kgyrtkirk 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 b33dddeb3a [CALCITE-6473] HAVING clauses may not contain window
functions
b33dddeb3a is described below
commit b33dddeb3a79cf4da1ac3c72ae004a893945fc60
Author: Zoltan Haindrich <[email protected]>
AuthorDate: Tue Jul 16 11:48:02 2024 +0000
[CALCITE-6473] HAVING clauses may not contain window functions
---
.../org/apache/calcite/runtime/CalciteResource.java | 3 +++
.../main/java/org/apache/calcite/sql/SqlUtil.java | 2 +-
.../calcite/sql/validate/SqlValidatorImpl.java | 5 +++++
.../calcite/runtime/CalciteResource.properties | 1 +
.../org/apache/calcite/test/SqlValidatorTest.java | 20 ++++++++++++++++++++
5 files changed, 30 insertions(+), 1 deletion(-)
diff --git a/core/src/main/java/org/apache/calcite/runtime/CalciteResource.java
b/core/src/main/java/org/apache/calcite/runtime/CalciteResource.java
index d056af26e3..2e132c81b6 100644
--- a/core/src/main/java/org/apache/calcite/runtime/CalciteResource.java
+++ b/core/src/main/java/org/apache/calcite/runtime/CalciteResource.java
@@ -416,6 +416,9 @@ public interface CalciteResource {
@BaseMessage("HAVING clause must be a condition")
ExInst<SqlValidatorException> havingMustBeBoolean();
+ @BaseMessage("Window expressions are not permitted in the HAVING clause; use
the QUALIFY clause instead")
+ ExInst<SqlValidatorException> windowInHavingNotAllowed();
+
@BaseMessage("OVER must be applied to aggregate function")
ExInst<SqlValidatorException> overNonAggregate();
diff --git a/core/src/main/java/org/apache/calcite/sql/SqlUtil.java
b/core/src/main/java/org/apache/calcite/sql/SqlUtil.java
index 539c476a78..edb50aac38 100644
--- a/core/src/main/java/org/apache/calcite/sql/SqlUtil.java
+++ b/core/src/main/java/org/apache/calcite/sql/SqlUtil.java
@@ -1262,7 +1262,7 @@ public abstract class SqlUtil {
/** Returns whether an AST tree contains a call that matches a given
* predicate. */
- private static boolean containsCall(SqlNode node,
+ public static boolean containsCall(SqlNode node,
Predicate<SqlCall> callPredicate) {
try {
SqlVisitor<Void> visitor =
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 b9de1d48a5..9daadadec0 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
@@ -72,6 +72,7 @@ import org.apache.calcite.sql.SqlNodeList;
import org.apache.calcite.sql.SqlOperator;
import org.apache.calcite.sql.SqlOperatorTable;
import org.apache.calcite.sql.SqlOrderBy;
+import org.apache.calcite.sql.SqlOverOperator;
import org.apache.calcite.sql.SqlPivot;
import org.apache.calcite.sql.SqlSampleSpec;
import org.apache.calcite.sql.SqlSelect;
@@ -4800,6 +4801,7 @@ public class SqlValidatorImpl implements
SqlValidatorWithHints {
if (having == null) {
return;
}
+ SqlNode originalHaving = having;
final AggregatingScope havingScope =
(AggregatingScope) getSelectScope(select);
if (config.conformance().isHavingAlias()) {
@@ -4809,6 +4811,9 @@ public class SqlValidatorImpl implements
SqlValidatorWithHints {
select.setHaving(newExpr);
}
}
+ if (SqlUtil.containsCall(having, call -> call.getOperator() instanceof
SqlOverOperator)) {
+ throw newValidationError(originalHaving,
RESOURCE.windowInHavingNotAllowed());
+ }
havingScope.checkAggregateExpr(having, true);
inferUnknownTypes(booleanType, havingScope, having);
having.validate(this, havingScope);
diff --git
a/core/src/main/resources/org/apache/calcite/runtime/CalciteResource.properties
b/core/src/main/resources/org/apache/calcite/runtime/CalciteResource.properties
index e925aaf39e..773c3bc6b0 100644
---
a/core/src/main/resources/org/apache/calcite/runtime/CalciteResource.properties
+++
b/core/src/main/resources/org/apache/calcite/runtime/CalciteResource.properties
@@ -362,4 +362,5 @@ NoOperator=No operator for ''{0}'' with kind: ''{1}'',
syntax: ''{2}'' during JS
MustFilterFieldsMissing=SQL statement did not contain filters on the following
fields: {0}
IllegalNegativeBitGetPosition=BIT_GET/GETBIT error: negative position
{0,number} not allowed
IllegalBitGetPositionExceedsLimit=BIT_GET/GETBIT error: position {0,number}
exceeds the bit upper limit {1,number}
+WindowInHavingNotAllowed=Window expressions are not permitted in the HAVING
clause; use the QUALIFY clause instead
# End CalciteResource.properties
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 0e5b219075..3e7164765a 100644
--- a/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java
+++ b/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java
@@ -6821,6 +6821,26 @@ public class SqlValidatorTest extends
SqlValidatorTestCase {
.fails(ERR_NESTED_AGG);
}
+ /**
+ * Test case for <a
href="https://issues.apache.org/jira/browse/CALCITE-6473";>[CALCITE-6473]
+ * HAVING clauses may not contain window functions</a>.
+ */
+ @Test void testOverInHaving() {
+ final SqlConformanceEnum lenient = SqlConformanceEnum.LENIENT;
+ final SqlConformanceEnum strict = SqlConformanceEnum.STRICT_2003;
+
+ sql("select sum(1) over () as e from emp having ^e^ > 1")
+ .withConformance(strict)
+ .fails("Column 'E' not found in any table");
+ sql("select sum(1) over () as e from emp having ^e > 1^")
+ .withConformance(lenient)
+ .fails("Window expressions are not permitted in the HAVING clause;"
+ + " use the QUALIFY clause instead");
+ sql("select empno from emp having ^max(empno) OVER () > 1^")
+ .fails("Window expressions are not permitted in the HAVING clause;"
+ + " use the QUALIFY clause instead");
+ }
+
@Test void testAggregateInGroupByFails() {
sql("select count(*) from emp group by ^sum(empno)^")
.fails(ERR_AGG_IN_GROUP_BY);
