This is an automated email from the ASF dual-hosted git repository.
iwanttobepowerful 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 2df193e496 [CALCITE-7887] SubQueryRemoveRule fails for a sub-query
inside a window function
2df193e496 is described below
commit 2df193e496c7eecaf774b678ef2f4a63d087c444
Author: Weihua Zhang <[email protected]>
AuthorDate: Sun Oct 25 12:36:33 2026 +0800
[CALCITE-7887] SubQueryRemoveRule fails for a sub-query inside a window
function
---
.../java/org/apache/calcite/rex/LogicVisitor.java | 15 ++
core/src/test/resources/sql/sub-query.iq | 165 +++++++++++++++++++++
2 files changed, 180 insertions(+)
diff --git a/core/src/main/java/org/apache/calcite/rex/LogicVisitor.java
b/core/src/main/java/org/apache/calcite/rex/LogicVisitor.java
index b18613b104..d9e467a28e 100644
--- a/core/src/main/java/org/apache/calcite/rex/LogicVisitor.java
+++ b/core/src/main/java/org/apache/calcite/rex/LogicVisitor.java
@@ -150,6 +150,21 @@ public static void collect(RexNode node, RexNode seek,
Logic logic,
}
@Override public @Nullable Logic visitOver(RexOver over, @Nullable Logic
arg) {
+ // The value, not just the truth, of the operands and of the window
+ // matters, so visit them with TRUE_FALSE_UNKNOWN.
+ final Logic logic = Logic.TRUE_FALSE_UNKNOWN;
+ for (RexNode operand : over.getOperands()) {
+ operand.accept(this, logic);
+ }
+ final RexWindow window = over.getWindow();
+ for (RexNode partitionKey : window.partitionKeys) {
+ partitionKey.accept(this, logic);
+ }
+ for (RexFieldCollation orderKey : window.orderKeys) {
+ orderKey.left.accept(this, logic);
+ }
+ window.getLowerBound().accept(this, logic);
+ window.getUpperBound().accept(this, logic);
return end(over, arg);
}
diff --git a/core/src/test/resources/sql/sub-query.iq
b/core/src/test/resources/sql/sub-query.iq
index 6ea6492808..5df47b92f3 100644
--- a/core/src/test/resources/sql/sub-query.iq
+++ b/core/src/test/resources/sql/sub-query.iq
@@ -11003,3 +11003,168 @@ from t;
(2 rows)
!ok
+
+# [CALCITE-7887] SubQueryRemoveRule fails for a sub-query inside a window
function
+# IN sub-query inside the argument of a windowed aggregate.
+# The result of this query was validated using Postgres.
+SELECT empno,
+ SUM(CASE WHEN deptno IN (SELECT deptno FROM dept WHERE deptno < 30) THEN 1
ELSE 0 END)
+ OVER (ORDER BY empno) AS s
+FROM emp
+WHERE empno < 7600;
++-------+---+
+| EMPNO | S |
++-------+---+
+| 7369 | 1 |
+| 7499 | 1 |
+| 7521 | 1 |
+| 7566 | 2 |
++-------+---+
+(4 rows)
+
+!ok
+
+# Correlated EXISTS sub-query inside the argument of a windowed aggregate that
has a PARTITION BY.
+SELECT empno, deptno,
+ COUNT(CASE WHEN EXISTS (SELECT 1 FROM emp AS e2 WHERE e2.mgr = e.empno) THEN
1 END)
+ OVER (PARTITION BY deptno) AS c
+FROM emp AS e
+ORDER BY empno;
++-------+--------+---+
+| EMPNO | DEPTNO | C |
++-------+--------+---+
+| 7369 | 20 | 3 |
+| 7499 | 30 | 1 |
+| 7521 | 30 | 1 |
+| 7566 | 20 | 3 |
+| 7654 | 30 | 1 |
+| 7698 | 30 | 1 |
+| 7782 | 10 | 2 |
+| 7788 | 20 | 3 |
+| 7839 | 10 | 2 |
+| 7844 | 30 | 1 |
+| 7876 | 20 | 3 |
+| 7900 | 30 | 1 |
+| 7902 | 20 | 3 |
+| 7934 | 10 | 2 |
++-------+--------+---+
+(14 rows)
+
+!ok
+
+# Correlated scalar sub-query as the argument of a windowed aggregate.
+SELECT empno,
+ SUM((SELECT COUNT(*) FROM emp AS e2 WHERE e2.mgr = e.empno))
+ OVER (ORDER BY empno) AS s
+FROM emp AS e
+WHERE deptno = 20
+ORDER BY empno;
++-------+---+
+| EMPNO | S |
++-------+---+
+| 7369 | 0 |
+| 7566 | 2 |
+| 7788 | 3 |
+| 7876 | 3 |
+| 7902 | 4 |
++-------+---+
+(5 rows)
+
+!ok
+
+# IN sub-query in the PARTITION BY clause of a window.
+SELECT empno, deptno,
+ COUNT(*) OVER (PARTITION BY deptno IN (SELECT deptno FROM dept WHERE dname =
'SALES')) AS c
+FROM emp
+ORDER BY empno;
++-------+--------+---+
+| EMPNO | DEPTNO | C |
++-------+--------+---+
+| 7369 | 20 | 8 |
+| 7499 | 30 | 6 |
+| 7521 | 30 | 6 |
+| 7566 | 20 | 8 |
+| 7654 | 30 | 6 |
+| 7698 | 30 | 6 |
+| 7782 | 10 | 8 |
+| 7788 | 20 | 8 |
+| 7839 | 10 | 8 |
+| 7844 | 30 | 6 |
+| 7876 | 20 | 8 |
+| 7900 | 30 | 6 |
+| 7902 | 20 | 8 |
+| 7934 | 10 | 8 |
++-------+--------+---+
+(14 rows)
+
+!ok
+
+# Correlated EXISTS sub-query in the ORDER BY clause of a window.
+SELECT empno,
+ ROW_NUMBER() OVER (ORDER BY EXISTS (SELECT 1 FROM emp AS e2 WHERE e2.mgr =
e.empno) DESC,
+ empno) AS rn
+FROM emp AS e
+WHERE deptno = 20
+ORDER BY empno;
++-------+----+
+| EMPNO | RN |
++-------+----+
+| 7369 | 4 |
+| 7566 | 1 |
+| 7788 | 2 |
+| 7876 | 5 |
+| 7902 | 3 |
++-------+----+
+(5 rows)
+
+!ok
+
+# NOT IN sub-query whose result is UNKNOWN (the sub-query
+# returns a NULL) inside a windowed COUNT. COUNT ignores UNKNOWN values,
+# so the sub-query must be expanded using three-valued logic.
+SELECT empno,
+ COUNT(deptno NOT IN (SELECT mgr FROM emp)) OVER () AS c1,
+ COUNT(*) OVER () AS c2
+FROM emp
+WHERE deptno = 10
+ORDER BY empno;
++-------+----+----+
+| EMPNO | C1 | C2 |
++-------+----+----+
+| 7782 | 0 | 3 |
+| 7839 | 0 | 3 |
+| 7934 | 0 | 3 |
++-------+----+----+
+(3 rows)
+
+!ok
+
+# Two sub-queries in the same window: one in the argument of
+# the aggregate, one in the PARTITION BY clause.
+SELECT empno,
+ SUM(CASE WHEN EXISTS (SELECT 1 FROM emp AS e2 WHERE e2.mgr = e.empno) THEN 1
ELSE 0 END)
+ OVER (PARTITION BY deptno IN (SELECT deptno FROM dept WHERE loc = 'DALLAS')
+ ORDER BY empno) AS s
+FROM emp AS e
+ORDER BY empno;
++-------+---+
+| EMPNO | S |
++-------+---+
+| 7369 | 0 |
+| 7499 | 0 |
+| 7521 | 0 |
+| 7566 | 1 |
+| 7654 | 0 |
+| 7698 | 1 |
+| 7782 | 2 |
+| 7788 | 2 |
+| 7839 | 3 |
+| 7844 | 3 |
+| 7876 | 2 |
+| 7900 | 3 |
+| 7902 | 3 |
+| 7934 | 3 |
++-------+---+
+(14 rows)
+
+!ok
