(calcite) branch main updated: [CALCITE-7596] TopDownGeneralDecorrelator omits rewriting the ORDER BY clause in window function within correlated subquery

[zwh]

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 2f77d83ed1 [CALCITE-7596] TopDownGeneralDecorrelator omits rewriting 
the ORDER BY clause in window function within correlated subquery
2f77d83ed1 is described below

commit 2f77d83ed1fc734ab0e50ec1e495168ac2933461
Author: Silun Dong <silund...@outlook.com>
AuthorDate: Thu Jun 11 15:42:25 2026 +0800

    [CALCITE-7596] TopDownGeneralDecorrelator omits rewriting the ORDER BY 
clause in window function within correlated subquery
---
 .../sql2rel/TopDownGeneralDecorrelator.java        |  2 +-
 core/src/test/resources/sql/new-decorr.iq          | 25 ++++++++++++++++++++++
 core/src/test/resources/sql/sub-query.iq           |  4 +---
 3 files changed, 27 insertions(+), 4 deletions(-)

diff --git 
a/core/src/main/java/org/apache/calcite/sql2rel/TopDownGeneralDecorrelator.java 
b/core/src/main/java/org/apache/calcite/sql2rel/TopDownGeneralDecorrelator.java
index c3d2bd9249..291eb619d0 100644
--- 
a/core/src/main/java/org/apache/calcite/sql2rel/TopDownGeneralDecorrelator.java
+++ 
b/core/src/main/java/org/apache/calcite/sql2rel/TopDownGeneralDecorrelator.java
@@ -975,7 +975,7 @@ static List<RexNode> rewrite(
       }
       return unnestedQuery.r.getCluster().getRexBuilder().makeWindow(
           newPartitionKeys,
-          window.orderKeys,
+          shiftedWindow.orderKeys,
           window.getLowerBound(),
           window.getUpperBound(),
           window.isRows(),
diff --git a/core/src/test/resources/sql/new-decorr.iq 
b/core/src/test/resources/sql/new-decorr.iq
index 987bd22299..3aef6e5276 100644
--- a/core/src/test/resources/sql/new-decorr.iq
+++ b/core/src/test/resources/sql/new-decorr.iq
@@ -471,4 +471,29 @@ FROM (SELECT ARRAY[1,2,3] as x) s;
 
 !ok
 
+# [CALCITE-7596] TopDownGeneralDecorrelator omits rewriting the ORDER BY 
clause in window function within correlated subquery
+SELECT empno, (SELECT row_number() OVER (PARTITION BY dname ORDER BY emp.sal) 
FROM dept WHERE dept.deptno = emp.deptno) as rn FROM emp;
+!if (use_new_decorr) {
++-------+----+
+| EMPNO | RN |
++-------+----+
+|  7369 |  1 |
+|  7499 |  1 |
+|  7521 |  1 |
+|  7566 |  1 |
+|  7654 |  1 |
+|  7698 |  1 |
+|  7782 |  1 |
+|  7788 |  1 |
+|  7839 |  1 |
+|  7844 |  1 |
+|  7876 |  1 |
+|  7900 |  1 |
+|  7902 |  1 |
+|  7934 |  1 |
++-------+----+
+(14 rows)
+
+!ok
+!}
 # End new-decorr.iq
diff --git a/core/src/test/resources/sql/sub-query.iq 
b/core/src/test/resources/sql/sub-query.iq
index 3a1fa64784..db69258df2 100644
--- a/core/src/test/resources/sql/sub-query.iq
+++ b/core/src/test/resources/sql/sub-query.iq
@@ -8216,7 +8216,6 @@ JOIN LATERAL (
   WHERE e.deptno = d.deptno
 ) d ON TRUE
 ORDER BY e.empno;
-!if (use_old_decorr) {
 +--------+--------+----+
 | ENAME  | DEPTNO | RN |
 +--------+--------+----+
@@ -8238,7 +8237,6 @@ ORDER BY e.empno;
 (14 rows)
 
 !ok
-!}
 
 # Multiple equality-derived correlation keys must remain available
 # when a window expression also needs an additional correlation key.
@@ -8593,7 +8591,6 @@ FROM calcite_7584_t1 t1
 WHERE t1b = (SELECT MAX(tmp.s) FROM (
              SELECT SUM(t2c) OVER (PARTITION BY t2c ORDER BY t1.t1d + t2d) AS s
                FROM calcite_7584_t2 t2) AS tmp);
-!if (use_old_decorr) {
 +--------+
 | EXPR$0 |
 +--------+
@@ -8602,6 +8599,7 @@ WHERE t1b = (SELECT MAX(tmp.s) FROM (
 
 !ok
 
+!if (use_old_decorr) {
 EnumerableCalc(expr#0..1=[{inputs}], expr#2=[1], EXPR$0=[$t2])
   EnumerableHashJoin(condition=[AND(=($1, $2), =(CAST($0):INTEGER, $3))], 
joinType=[semi])
     EnumerableValues(tuples=[[{ 6, 10 }, { 8, 19 }, { 16, 21 }, { 16, 10 }, { 
8, 19 }, { null, 22 }, { null, 19 }, { 10, 25 }, { 10, 19 }, { 10, 12 }, { 6, 
10 }, { 10, 19 }]])