(calcite) branch main updated: [CALCITE-6563] RelToSqlConverter should not merge two window functions

[mbudiu]

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 c1b07276c3 [CALCITE-6563] RelToSqlConverter should not merge two 
window functions
c1b07276c3 is described below

commit c1b07276c3aa2b0525033b19f0a15303880d3973
Author: suibianwanwan <suibianwanwa...@foxmail.com>
AuthorDate: Thu Sep 5 01:21:22 2024 +0800

    [CALCITE-6563] RelToSqlConverter should not merge two window functions
---
 .../apache/calcite/rel/rel2sql/SqlImplementor.java | 35 ++++++++++++++++++++--
 .../calcite/rel/rel2sql/RelToSqlConverterTest.java | 35 ++++++++++++++++++++++
 2 files changed, 67 insertions(+), 3 deletions(-)

diff --git 
a/core/src/main/java/org/apache/calcite/rel/rel2sql/SqlImplementor.java 
b/core/src/main/java/org/apache/calcite/rel/rel2sql/SqlImplementor.java
index 6e52d841e4..225b1dc98e 100644
--- a/core/src/main/java/org/apache/calcite/rel/rel2sql/SqlImplementor.java
+++ b/core/src/main/java/org/apache/calcite/rel/rel2sql/SqlImplementor.java
@@ -1918,11 +1918,15 @@ public abstract class SqlImplementor {
         return true;
       }
 
+      // Cannot merge two window functions
+      boolean containsOver = containsOver(node);
       if (rel instanceof Project
           && ((Project) rel).containsOver()
-          && maxClause == Clause.SELECT) {
-        // Cannot merge a Project that contains windowed functions onto an
-        // underlying Project
+          && containsOver) {
+        return true;
+      }
+
+      if (rel instanceof Window && containsOver) {
         return true;
       }
 
@@ -2014,6 +2018,31 @@ public abstract class SqlImplementor {
       return false;
     }
 
+    private boolean containsOver(@UnknownInitialization Result this,
+        @Nullable SqlNode node) {
+      if (node == null) {
+        return false;
+      }
+      if (node.getKind() == SqlKind.WINDOW) {
+        return true;
+      }
+      if (node instanceof SqlSelect) {
+        final SqlNodeList selectList = ((SqlSelect) node).getSelectList();
+        for (SqlNode child : selectList) {
+          if (containsOver(child)) {
+            return true;
+          }
+        }
+      } else if (node instanceof SqlBasicCall) {
+        for (SqlNode operand : ((SqlBasicCall) node).getOperandList()) {
+          if (containsOver(operand)) {
+            return true;
+          }
+        }
+      }
+      return false;
+    }
+
     /** Returns whether an {@link Aggregate} contains nested operands that
      * match the predicate.
      *
diff --git 
a/core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java 
b/core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java
index 6eba6a16cc..74f4450df4 100644
--- 
a/core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java
+++ 
b/core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java
@@ -4879,6 +4879,41 @@ class RelToSqlConverterTest {
         .withPostgresql().ok(expected);
   }
 
+  /** Test case for
+   * <a 
href="https://issues.apache.org/jira/browse/CALCITE-6563";>[CALCITE-6563]
+   * RelToSqlConverter should not merge two window functions</a>. */
+  @Test void testConvertNestWindowToSql() {
+    String query0 = " SELECT "
+        + "RANK() OVER (ORDER BY \"daily_sales\" DESC) AS \"rank1\" "
+        + "FROM ( SELECT \"product_name\", "
+        + "SUM(\"product_id\") OVER (PARTITION BY \"product_name\") AS 
\"daily_sales\" "
+        + "FROM \"product\" ) subquery";
+    String expected00 = "SELECT RANK() OVER (ORDER BY \"$1\" DESC) AS \"$0\"\n"
+        + "FROM (SELECT \"product_name\", SUM(\"product_id\") OVER (PARTITION 
BY \"product_name\" "
+        + "RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS 
\"$1\"\n"
+        + "FROM \"foodmart\".\"product\") AS \"t0\"";
+    String expected01 = "SELECT RANK() OVER (ORDER BY \"daily_sales\" DESC) AS 
\"rank1\"\n"
+        + "FROM (SELECT \"product_name\", SUM(\"product_id\") OVER (PARTITION 
BY \"product_name\""
+        + " RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS 
\"daily_sales\"\n"
+        + "FROM \"foodmart\".\"product\") AS \"t\"";
+    RuleSet rules = 
RuleSets.ofList(CoreRules.PROJECT_TO_LOGICAL_PROJECT_AND_WINDOW);
+    // PROJECT_TO_LOGICAL_PROJECT_AND_WINDOW rule will remove alias
+    sql(query0).optimize(rules, null).ok(expected00);
+    sql(query0).ok(expected01);
+
+    String query1 = " SELECT \"product_id\","
+        + "RANK() OVER (ORDER BY \"product_name\" DESC) AS \"rank1\" "
+        + "FROM (SELECT \"product_id\", \"product_name\" FROM \"product\") a";
+    String expected10 = "SELECT \"product_id\","
+        + " RANK() OVER (ORDER BY \"product_name\" DESC) AS \"$1\"\n"
+        + "FROM \"foodmart\".\"product\"";
+    String expected11 = "SELECT \"product_id\","
+        + " RANK() OVER (ORDER BY \"product_name\" DESC) AS \"rank1\"\n"
+        + "FROM \"foodmart\".\"product\"";
+    sql(query1).optimize(rules, null).ok(expected10);
+    sql(query1).ok(expected11);
+  }
+
   /** Test case for
    * <a 
href="https://issues.apache.org/jira/browse/CALCITE-1798";>[CALCITE-1798]
    * Generate dialect-specific SQL for FLOOR operator</a>. */