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 72963da2e7 [CALCITE-6632] Wrong optimization because window missing
constants in digest
72963da2e7 is described below
commit 72963da2e76e7961bec5aa19aa9bdfdd99c33a05
Author: Jiajun Xie <[email protected]>
AuthorDate: Thu Oct 17 14:41:57 2024 +0800
[CALCITE-6632] Wrong optimization because window missing constants in digest
---
.../java/org/apache/calcite/rel/core/Window.java | 3 ++
.../java/org/apache/calcite/test/JdbcTest.java | 2 +-
.../org/apache/calcite/test/RelOptRulesTest.java | 14 +++++++
.../org/apache/calcite/test/RelOptRulesTest.xml | 48 ++++++++++++++++++----
core/src/test/resources/sql/winagg.iq | 2 +-
5 files changed, 59 insertions(+), 10 deletions(-)
diff --git a/core/src/main/java/org/apache/calcite/rel/core/Window.java
b/core/src/main/java/org/apache/calcite/rel/core/Window.java
index 7c07bb6f8d..ab3a5e515c 100644
--- a/core/src/main/java/org/apache/calcite/rel/core/Window.java
+++ b/core/src/main/java/org/apache/calcite/rel/core/Window.java
@@ -163,6 +163,9 @@ public abstract class Window extends SingleRel implements
Hintable {
for (Ord<Group> window : Ord.zip(groups)) {
pw.item("window#" + window.i, window.e.toString());
}
+ if (this.constants != null && this.constants.size() > 0) {
+ pw.item("constants", constants);
+ }
return pw;
}
diff --git a/core/src/test/java/org/apache/calcite/test/JdbcTest.java
b/core/src/test/java/org/apache/calcite/test/JdbcTest.java
index ec681ef601..7ec62b28fb 100644
--- a/core/src/test/java/org/apache/calcite/test/JdbcTest.java
+++ b/core/src/test/java/org/apache/calcite/test/JdbcTest.java
@@ -4123,7 +4123,7 @@ public class JdbcTest {
"[deptno INTEGER NOT NULL, empid INTEGER NOT NULL, S REAL, FIVE
INTEGER NOT NULL, M REAL, C BIGINT NOT NULL]")
.explainContains(""
+ "EnumerableCalc(expr#0..7=[{inputs}], expr#8=[0:BIGINT],
expr#9=[>($t4, $t8)], expr#10=[null:JavaType(class java.lang.Float)],
expr#11=[CASE($t9, $t5, $t10)], expr#12=[5], deptno=[$t1], empid=[$t0],
S=[$t11], FIVE=[$t12], M=[$t6], C=[$t7])\n"
- + " EnumerableWindow(window#0=[window(partition {1} order by [0]
rows between $4 PRECEDING and CURRENT ROW aggs [COUNT($3), $SUM0($3), MIN($2),
COUNT()])])\n"
+ + " EnumerableWindow(window#0=[window(partition {1} order by [0]
rows between $4 PRECEDING and CURRENT ROW aggs [COUNT($3), $SUM0($3), MIN($2),
COUNT()])], constants=[[1]])\n"
+ " EnumerableCalc(expr#0..4=[{inputs}], expr#5=[+($t3, $t0)],
proj#0..1=[{exprs}], salary=[$t3], $3=[$t5])\n"
+ " EnumerableTableScan(table=[[hr, emps]])\n")
.returnsUnordered(
diff --git a/core/src/test/java/org/apache/calcite/test/RelOptRulesTest.java
b/core/src/test/java/org/apache/calcite/test/RelOptRulesTest.java
index 4862a89039..904643cdf2 100644
--- a/core/src/test/java/org/apache/calcite/test/RelOptRulesTest.java
+++ b/core/src/test/java/org/apache/calcite/test/RelOptRulesTest.java
@@ -5892,6 +5892,20 @@ class RelOptRulesTest extends RelOptTestBase {
.check();
}
+ /** Test case for
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-6632";>[CALCITE-6632]
+ * Wrong optimization because window missing constants in digest</a>. */
+ @Test void testWindowMissingConstantInDigest() {
+ final String sql = "select sum(100) over (partition by deptno order by
sal) as s\n"
+ + "from emp\n"
+ + "union all\n"
+ + "select sum(1000) over(partition by deptno order by sal) as s\n"
+ + "from emp\n";
+ sql(sql)
+ .withRule(CoreRules.PROJECT_TO_LOGICAL_PROJECT_AND_WINDOW)
+ .check();
+ }
+
/** While it's probably valid relational algebra for a Project to contain
* a RexOver inside a RexOver, ProjectMergeRule should not bring it about. */
@Test void testProjectMergeShouldIgnoreOver() {
diff --git
a/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml
b/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml
index babaae6201..a0070f5922 100644
--- a/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml
+++ b/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml
@@ -4441,7 +4441,7 @@ ROWS BETWEEN 5 + 5 PRECEDING AND 1 PRECEDING) AS w_count
from emp
<Resource name="planBefore">
<![CDATA[
LogicalProject($0=[$1])
- LogicalWindow(window#0=[window(order by [0] rows between $1 PRECEDING and $2
PRECEDING aggs [COUNT()])])
+ LogicalWindow(window#0=[window(order by [0] rows between $1 PRECEDING and $2
PRECEDING aggs [COUNT()])], constants=[[10, 1]])
LogicalProject(EMPNO=[$0])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
]]>
@@ -5328,7 +5328,7 @@ LogicalProject(EMPNO=[$0], DEPTNO=[$1], W_COUNT=[$2])
<![CDATA[
LogicalProject(EMPNO=[$0], DEPTNO=[$1], W_COUNT=[$2])
LogicalFilter(condition=[IS NULL($2)])
- LogicalWindow(window#0=[window(rows between $2 PRECEDING and $3 PRECEDING
aggs [COUNT($0)])])
+ LogicalWindow(window#0=[window(rows between $2 PRECEDING and $3 PRECEDING
aggs [COUNT($0)])], constants=[[10, 1]])
LogicalProject(EMPNO=[$0], DEPTNO=[$7])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
]]>
@@ -8409,7 +8409,7 @@ LogicalProject(EXPR$0=[CAST(/(CASE(>(COUNT($5) OVER
(ORDER BY $0 ROWS 3 PRECEDIN
<Resource name="planAfter">
<![CDATA[
LogicalProject(EXPR$0=[CAST(/(CASE(>($2, 0), $3, null:INTEGER), $2)):INTEGER])
- LogicalWindow(window#0=[window(order by [0] rows between $2 PRECEDING and
CURRENT ROW aggs [COUNT($1), $SUM0($1)])])
+ LogicalWindow(window#0=[window(order by [0] rows between $2 PRECEDING and
CURRENT ROW aggs [COUNT($1), $SUM0($1)])], constants=[[3]])
LogicalProject(EMPNO=[$0], SAL=[$5])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
]]>
@@ -8509,7 +8509,7 @@ from (
<Resource name="planAfter">
<![CDATA[
LogicalProject($0=[$2], $1=[$3])
- LogicalWindow(window#0=[window(partition {1} order by [0] aggs [SUM($2),
SUM($3)])])
+ LogicalWindow(window#0=[window(partition {1} order by [0] aggs [SUM($2),
SUM($3)])], constants=[[100, 1000]])
LogicalProject(SAL=[$5], DEPTNO=[$7])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
]]>
@@ -10603,7 +10603,7 @@ where DEPTNO = 1]]>
LogicalProject(NAME=[$0], DEPTNO=[$1], CNT=[$2], ALL_SUM=[$3])
LogicalFilter(condition=[=($1, 1)])
LogicalProject(NAME=[$1], DEPTNO=[$0], CNT=[$2], ALL_SUM=[$3])
- LogicalWindow(window#0=[window(partition {0, 1} aggs [COUNT()])],
window#1=[window(partition {0} aggs [SUM($2)])])
+ LogicalWindow(window#0=[window(partition {0, 1} aggs [COUNT()])],
window#1=[window(partition {0} aggs [SUM($2)])], constants=[[1]])
LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
]]>
</Resource>
@@ -10611,7 +10611,7 @@ LogicalProject(NAME=[$0], DEPTNO=[$1], CNT=[$2],
ALL_SUM=[$3])
<![CDATA[
LogicalProject(NAME=[$1], DEPTNO=[$0], CNT=[$2], ALL_SUM=[$3])
LogicalFilter(condition=[=($0, 1)])
- LogicalWindow(window#0=[window(partition {0, 1} aggs [COUNT()])],
window#1=[window(partition {0} aggs [SUM($2)])])
+ LogicalWindow(window#0=[window(partition {0, 1} aggs [COUNT()])],
window#1=[window(partition {0} aggs [SUM($2)])], constants=[[1]])
LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
]]>
</Resource>
@@ -13055,7 +13055,7 @@ from (
<Resource name="planAfter">
<![CDATA[
LogicalProject($0=[$2], $1=[$3], $2=[$4])
- LogicalWindow(window#0=[window(partition {1} range between UNBOUNDED
PRECEDING and CURRENT ROW aggs [SUM($2)])], window#1=[window(order by [1] aggs
[SUM($2)])], window#2=[window(partition {1} range between UNBOUNDED PRECEDING
and CURRENT ROW aggs [SUM(5000)])])
+ LogicalWindow(window#0=[window(partition {1} range between UNBOUNDED
PRECEDING and CURRENT ROW aggs [SUM($2)])], window#1=[window(order by [1] aggs
[SUM($2)])], window#2=[window(partition {1} range between UNBOUNDED PRECEDING
and CURRENT ROW aggs [SUM(5000)])], constants=[[100]])
LogicalProject(SAL=[$5], DEPTNO=[$7])
LogicalFilter(condition=[=($5, 5000)])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
@@ -13088,7 +13088,7 @@ LogicalProject(COL1=[SUM(100) OVER (ORDER BY $7, $0
RANGE BETWEEN CURRENT ROW AN
<Resource name="planAfter">
<![CDATA[
LogicalProject($0=[$3], $1=[$4])
- LogicalWindow(window#0=[window(order by [2, 0] range between CURRENT ROW and
UNBOUNDED FOLLOWING aggs [SUM($3)])], window#1=[window(partition {2} order by
[2, 0] range between UNBOUNDED PRECEDING and UNBOUNDED FOLLOWING aggs
[SUM($3)])])
+ LogicalWindow(window#0=[window(order by [2, 0] range between CURRENT ROW and
UNBOUNDED FOLLOWING aggs [SUM($3)])], window#1=[window(partition {2} order by
[2, 0] range between UNBOUNDED PRECEDING and UNBOUNDED FOLLOWING aggs
[SUM($3)])], constants=[[100]])
LogicalProject(EMPNO=[$0], SAL=[$5], DEPTNO=[$7])
LogicalFilter(condition=[=($5, 5000)])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
@@ -17232,6 +17232,38 @@ LogicalProject(EXPR$0=[$9], EXPR$1=[$9])
<![CDATA[
LogicalProject(EXPR$0=[COUNT() OVER (PARTITION BY $0 ORDER BY $0)],
EXPR$1=[COUNT() OVER (PARTITION BY $0 ORDER BY $0)])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testWindowMissingConstantInDigest">
+ <Resource name="sql">
+ <![CDATA[select sum(100) over (partition by deptno order by sal) as s
+from emp
+union all
+select sum(1000) over(partition by deptno order by sal) as s
+from emp
+]]>
+ </Resource>
+ <Resource name="planBefore">
+ <![CDATA[
+LogicalUnion(all=[true])
+ LogicalProject(S=[SUM(100) OVER (PARTITION BY $7 ORDER BY $5)])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+ LogicalProject(S=[SUM(1000) OVER (PARTITION BY $7 ORDER BY $5)])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ <Resource name="planAfter">
+ <![CDATA[
+LogicalUnion(all=[true])
+ LogicalProject($0=[$2])
+ LogicalWindow(window#0=[window(partition {1} order by [0] aggs
[SUM($2)])], constants=[[100]])
+ LogicalProject(SAL=[$5], DEPTNO=[$7])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+ LogicalProject($0=[$2])
+ LogicalWindow(window#0=[window(partition {1} order by [0] aggs
[SUM($2)])], constants=[[1000]])
+ LogicalProject(SAL=[$5], DEPTNO=[$7])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
]]>
</Resource>
</TestCase>
diff --git a/core/src/test/resources/sql/winagg.iq
b/core/src/test/resources/sql/winagg.iq
index 762e8a351e..feb4dc6951 100644
--- a/core/src/test/resources/sql/winagg.iq
+++ b/core/src/test/resources/sql/winagg.iq
@@ -760,7 +760,7 @@ select emp."ENAME", emp."DEPTNO",
nth_value(emp."DEPTNO", 10) over() as "tenth_value"
from emp order by emp."ENAME";
EnumerableSort(sort0=[$0], dir0=[ASC])
- EnumerableWindow(window#0=[window(aggs [NTH_VALUE($1, $2), NTH_VALUE($1,
$3), NTH_VALUE($1, $4), NTH_VALUE($1, $5), NTH_VALUE($1, $6)])])
+ EnumerableWindow(window#0=[window(aggs [NTH_VALUE($1, $2), NTH_VALUE($1,
$3), NTH_VALUE($1, $4), NTH_VALUE($1, $5), NTH_VALUE($1, $6)])], constants=[[1,
2, 5, 8, 10]])
EnumerableValues(tuples=[[{ 'Jane ', 10 }, { 'Bob ', 10 }, { 'Eric ', 20
}, { 'Susan', 30 }, { 'Alice', 30 }, { 'Adam ', 50 }, { 'Eve ', 50 }, {
'Grace', 60 }, { 'Wilma', null }]])
!plan
+-------+--------+-------------+--------------+-------------+--------------+-------------+
