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 4ae896d4a5 [CALCITE-7369] ProjectToWindowRule loses column alias when
optimizing OVER window queries
4ae896d4a5 is described below
commit 4ae896d4a5992fe271fa763e3f3d2bddc8b74954
Author: lincoln.lil <[email protected]>
AuthorDate: Mon Jan 12 14:33:22 2026 +0800
[CALCITE-7369] ProjectToWindowRule loses column alias when optimizing OVER
window queries
---
.../apache/calcite/rel/logical/LogicalWindow.java | 3 +-
.../apache/calcite/rel/rules/CalcRelSplitter.java | 63 ++++++++++++++++----
.../rel/logical/ToLogicalConverterTest.java | 8 +--
.../calcite/rel/rel2sql/RelToSqlConverterTest.java | 32 +++++------
.../org/apache/calcite/test/RelOptRulesTest.java | 33 +++++++++++
.../org/apache/calcite/test/RelOptRulesTest.xml | 67 ++++++++++++++++++----
core/src/test/resources/sql/sub-query.iq | 4 +-
core/src/test/resources/sql/winagg.iq | 2 +-
.../java/org/apache/calcite/test/PigRelOpTest.java | 4 +-
9 files changed, 170 insertions(+), 46 deletions(-)
diff --git
a/core/src/main/java/org/apache/calcite/rel/logical/LogicalWindow.java
b/core/src/main/java/org/apache/calcite/rel/logical/LogicalWindow.java
index 815b8f8ca5..16b247974b 100644
--- a/core/src/main/java/org/apache/calcite/rel/logical/LogicalWindow.java
+++ b/core/src/main/java/org/apache/calcite/rel/logical/LogicalWindow.java
@@ -244,11 +244,12 @@ public static RelNode create(RelOptCluster cluster,
}
}
+ int callIndex = 0;
for (Ord<Group> window : Ord.zip(groups)) {
for (Ord<RexWinAggCall> over : Ord.zip(window.e.aggCalls)) {
// Add the k-th over expression of
// the i-th window to the output of the program.
- String name = fieldNames.get(over.i);
+ String name = fieldNames.get(callIndex++);
if (name == null || name.startsWith("$")) {
name = "w" + window.i + "$o" + over.i;
}
diff --git
a/core/src/main/java/org/apache/calcite/rel/rules/CalcRelSplitter.java
b/core/src/main/java/org/apache/calcite/rel/rules/CalcRelSplitter.java
index 67bbbae246..17e6333dc2 100644
--- a/core/src/main/java/org/apache/calcite/rel/rules/CalcRelSplitter.java
+++ b/core/src/main/java/org/apache/calcite/rel/rules/CalcRelSplitter.java
@@ -605,7 +605,7 @@ private RexProgram createProgramForLevel(
projectRefs.add(new RexLocalRef(index, expr.getType()));
// Inherit meaningful field name if possible.
- fieldNames.add(deriveFieldName(expr, i));
+ fieldNames.add(deriveFieldName(expr, projectExprOrdinal, i));
}
RexLocalRef conditionRef;
if (conditionExprOrdinal >= 0) {
@@ -627,18 +627,61 @@ private RexProgram createProgramForLevel(
outputRowType);
}
- private String deriveFieldName(RexNode expr, int ordinal) {
+ /**
+ * Derives a field name for a projected expression.
+ *
+ * <p>If {@code expr} is a {@link RexInputRef}, returns the corresponding
+ * input field name. Otherwise, attempts to retrieve the name from the
+ * original program's projections. If no meaningful name is found, or if
+ * the name looks like an auto-generated name such as {@code $n} (but not
+ * starting with {@code $EXPR}), returns a synthesized name {@code "$" +
ordinal}.
+ *
+ * @param expr Expression to derive the name for
+ * @param exprIndex Index of the expression in the program's expression list
+ * @param ordinal Position in the projection (used to generate a fallback
name)
+ * @return Derived or synthesized field name
+ */
+ private String deriveFieldName(RexNode expr, int exprIndex, int ordinal) {
+ String fieldName = null;
if (expr instanceof RexInputRef) {
- int inputIndex = ((RexInputRef) expr).getIndex();
- String fieldName =
- child.getRowType().getFieldList().get(inputIndex).getName();
- // Don't inherit field names like '$3' from child: that's
- // confusing.
- if (!fieldName.startsWith("$") || fieldName.startsWith("$EXPR")) {
- return fieldName;
+ fieldName = getInputRefName((RexInputRef) expr);
+ } else {
+ fieldName = findProjectedFieldName(exprIndex);
+ }
+ return normalizeFieldName(fieldName, ordinal);
+ }
+
+ private String getInputRefName(RexInputRef ref) {
+ int inputIndex = ref.getIndex();
+ return child.getRowType().getFieldList().get(inputIndex).getName();
+ }
+
+ /**
+ * Return the output field name corresponding to the given expression index
{@code exprIndex},
+ * or {@code null} if the expression is not part of the program's projection.
+ *
+ * @param exprIndex Index of the expression in the program's expression list
+ * @return the output field name for the given expression index, or {@code
null} if not projected
+ */
+ private @Nullable String findProjectedFieldName(int exprIndex) {
+ List<RexLocalRef> projects = program.getProjectList();
+ List<String> fieldNames = program.getOutputRowType().getFieldNames();
+ for (int i = 0; i < projects.size(); i++) {
+ // If the project entry refers to the given expression, return its name.
+ if (projects.get(i).getIndex() == exprIndex) {
+ return fieldNames.get(i);
}
}
- return "$" + ordinal;
+ return null;
+ }
+
+ private String normalizeFieldName(@Nullable String fieldName, int ordinal) {
+ // Don't inherit field names like '$3' from child: that's confusing.
+ if (fieldName == null
+ || (fieldName.startsWith("$") && !fieldName.startsWith("$EXPR"))) {
+ return "$" + ordinal;
+ }
+ return fieldName;
}
/**
diff --git
a/core/src/test/java/org/apache/calcite/rel/logical/ToLogicalConverterTest.java
b/core/src/test/java/org/apache/calcite/rel/logical/ToLogicalConverterTest.java
index 062bea6d50..fb7deb5b8a 100644
---
a/core/src/test/java/org/apache/calcite/rel/logical/ToLogicalConverterTest.java
+++
b/core/src/test/java/org/apache/calcite/rel/logical/ToLogicalConverterTest.java
@@ -477,12 +477,12 @@ private void verify(RelNode rel, String expectedPhysical,
String expectedLogical
@Test void testWindow() {
String sql = "SELECT rank() over (order by \"hire_date\") FROM
\"employee\"";
String expectedPhysical = ""
- + "EnumerableProject($0=[$17])\n"
+ + "EnumerableProject(EXPR$0=[$17])\n"
+ " EnumerableWindow(window#0=[window(order by [9] aggs [RANK()])])\n"
+ " JdbcToEnumerableConverter\n"
+ " JdbcTableScan(table=[[foodmart, employee]])\n";
String expectedLogical = ""
- + "LogicalProject($0=[$17])\n"
+ + "LogicalProject(EXPR$0=[$17])\n"
+ " LogicalWindow(window#0=[window(order by [9] aggs [RANK()])])\n"
+ " LogicalTableScan(table=[[foodmart, employee]])\n";
verify(rel(sql), expectedPhysical, expectedLogical);
@@ -493,13 +493,13 @@ void testWindowExcludeImp(String excludeClause, String
expectedExcludeString) {
String sql = String.format(Locale.ROOT, "SELECT sum(\"salary\") over
(order by \"hire_date\" "
+ "rows between unbounded preceding and current row %s) FROM
\"employee\"", excludeClause);
String expectedPhysical =
- String.format(Locale.ROOT, "EnumerableProject($0=[$17])\n"
+ String.format(Locale.ROOT, "EnumerableProject(EXPR$0=[$17])\n"
+ " EnumerableWindow(window#0=[window(order by [9] rows between"
+ " UNBOUNDED PRECEDING and CURRENT ROW %saggs [SUM($11)])])\n"
+ " JdbcToEnumerableConverter\n"
+ " JdbcTableScan(table=[[foodmart, employee]])\n",
expectedExcludeString);
String expectedLogical =
- String.format(Locale.ROOT, "LogicalProject($0=[$17])\n"
+ String.format(Locale.ROOT, "LogicalProject(EXPR$0=[$17])\n"
+ " LogicalWindow(window#0=[window(order by [9] rows between"
+ " UNBOUNDED PRECEDING and CURRENT ROW %saggs [SUM($11)])])\n"
+ " LogicalTableScan(table=[[foodmart, employee]])\n",
expectedExcludeString);
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 5a9b278c35..a44d05ce64 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
@@ -5566,11 +5566,11 @@ private void checkLiteral2(String expression, String
expected) {
* Support Window in RelToSqlConverter</a>. */
@Test void testConvertWindowToSql() {
String query0 = "SELECT row_number() over (order by \"hire_date\") FROM
\"employee\"";
- String expected0 = "SELECT ROW_NUMBER() OVER (ORDER BY \"hire_date\") AS
\"$0\"\n"
+ String expected0 = "SELECT ROW_NUMBER() OVER (ORDER BY \"hire_date\")\n"
+ "FROM \"foodmart\".\"employee\"";
String query1 = "SELECT rank() over (order by \"hire_date\") FROM
\"employee\"";
- String expected1 = "SELECT RANK() OVER (ORDER BY \"hire_date\") AS
\"$0\"\n"
+ String expected1 = "SELECT RANK() OVER (ORDER BY \"hire_date\")\n"
+ "FROM \"foodmart\".\"employee\"";
String query2 = "SELECT lead(\"employee_id\",1,'NA') over "
@@ -5578,14 +5578,14 @@ private void checkLiteral2(String expression, String
expected) {
+ "FROM \"employee\"";
String expected2 = "SELECT LEAD(\"employee_id\", 1, 'NA') OVER "
+ "(PARTITION BY \"hire_date\" "
- + "ORDER BY \"employee_id\") AS \"$0\"\n"
+ + "ORDER BY \"employee_id\")\n"
+ "FROM \"foodmart\".\"employee\"";
String query3 = "SELECT lag(\"employee_id\",1,'NA') over "
+ "(partition by \"hire_date\" order by \"employee_id\")\n"
+ "FROM \"employee\"";
String expected3 = "SELECT LAG(\"employee_id\", 1, 'NA') OVER "
- + "(PARTITION BY \"hire_date\" ORDER BY \"employee_id\") AS
\"$0\"\n"
+ + "(PARTITION BY \"hire_date\" ORDER BY \"employee_id\")\n"
+ "FROM \"foodmart\".\"employee\"";
String query4 = "SELECT lag(\"employee_id\",1,'NA') "
@@ -5596,13 +5596,13 @@ private void checkLiteral2(String expression, String
expected) {
+ "count(*) over (partition by \"birth_date\" order by
\"employee_id\") as count2\n"
+ "FROM \"employee\"";
String expected4 = "SELECT LAG(\"employee_id\", 1, 'NA') OVER "
- + "(PARTITION BY \"hire_date\" ORDER BY \"employee_id\") AS
\"$0\", "
+ + "(PARTITION BY \"hire_date\" ORDER BY \"employee_id\") AS
\"LAG1\", "
+ "LAG(\"employee_id\", 1, 'NA') OVER "
- + "(PARTITION BY \"birth_date\" ORDER BY \"employee_id\") AS
\"$1\", "
+ + "(PARTITION BY \"birth_date\" ORDER BY \"employee_id\") AS
\"LAG2\", "
+ "COUNT(*) OVER (PARTITION BY \"hire_date\" ORDER BY
\"employee_id\" "
- + "RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS \"$2\", "
+ + "RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS
\"COUNT1\", "
+ "COUNT(*) OVER (PARTITION BY \"birth_date\" ORDER BY
\"employee_id\" "
- + "RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS \"$3\"\n"
+ + "RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS
\"COUNT2\"\n"
+ "FROM \"foodmart\".\"employee\"";
String query5 = "SELECT lag(\"employee_id\",1,'NA') "
@@ -5613,13 +5613,13 @@ private void checkLiteral2(String expression, String
expected) {
+ "max(sum(\"employee_id\")) over (partition by \"birth_date\"
order by \"employee_id\") as count2\n"
+ "FROM \"employee\" group by \"employee_id\", \"hire_date\",
\"birth_date\"";
String expected5 = "SELECT LAG(\"employee_id\", 1, 'NA') OVER "
- + "(PARTITION BY \"hire_date\" ORDER BY \"employee_id\") AS
\"$0\", "
+ + "(PARTITION BY \"hire_date\" ORDER BY \"employee_id\") AS
\"LAG1\", "
+ "LAG(\"employee_id\", 1, 'NA') OVER "
- + "(PARTITION BY \"birth_date\" ORDER BY \"employee_id\") AS
\"$1\", "
+ + "(PARTITION BY \"birth_date\" ORDER BY \"employee_id\") AS
\"LAG2\", "
+ "MAX(SUM(\"employee_id\")) OVER (PARTITION BY \"hire_date\"
ORDER BY \"employee_id\" "
- + "RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS \"$2\", "
+ + "RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS
\"COUNT1\", "
+ "MAX(SUM(\"employee_id\")) OVER (PARTITION BY \"birth_date\"
ORDER BY \"employee_id\" "
- + "RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS \"$3\"\n"
+ + "RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS
\"COUNT2\"\n"
+ "FROM \"foodmart\".\"employee\"\n"
+ "GROUP BY \"employee_id\", \"hire_date\", \"birth_date\"";
@@ -5635,7 +5635,7 @@ private void checkLiteral2(String expression, String
expected) {
+ "count(distinct \"employee_id\") over (order by \"hire_date\") FROM
\"employee\"";
String expected7 = "SELECT "
+ "COUNT(DISTINCT \"employee_id\") OVER (ORDER BY \"hire_date\""
- + " RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS \"$0\"\n"
+ + " RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)\n"
+ "FROM \"foodmart\".\"employee\"";
String query8 = "SELECT "
@@ -5823,9 +5823,9 @@ private void checkLiteral2(String expression, String
expected) {
+ "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"
+ String expected00 = "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
\"$1\"\n"
+ + "RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS
\"daily_sales\"\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\""
@@ -5840,7 +5840,7 @@ private void checkLiteral2(String expression, String
expected) {
+ "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"
+ + " RANK() OVER (ORDER BY \"product_name\" DESC) AS \"rank1\"\n"
+ "FROM \"foodmart\".\"product\"";
String expected11 = "SELECT \"product_id\","
+ " RANK() OVER (ORDER BY \"product_name\" DESC) AS \"rank1\"\n"
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 a839054349..7de683e9ac 100644
--- a/core/src/test/java/org/apache/calcite/test/RelOptRulesTest.java
+++ b/core/src/test/java/org/apache/calcite/test/RelOptRulesTest.java
@@ -12059,4 +12059,37 @@ private void
checkLoptOptimizeJoinRule(LoptOptimizeJoinRule rule) {
.check();
}
+ /** Test case of
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-7369";>[CALCITE-7369]
+ * ProjectToWindowRule loses column alias when optimizing OVER window
queries</a>. */
+ @Test void testProjectToWindowRuleForNestedOver() {
+ HepProgramBuilder builder = new HepProgramBuilder();
+ builder.addRuleClass(ProjectToWindowRule.class);
+ HepPlanner hepPlanner = new HepPlanner(builder.build());
+ hepPlanner.addRule(CoreRules.PROJECT_TO_LOGICAL_PROJECT_AND_WINDOW);
+
+ final String sql =
+ "select deptno, f1, f2 from (select *, last_value(deptno) over
(order by empno) f2\n"
+ + "from (select *, first_value(deptno) over (order by empno) f1
from emp))\n";
+ sql(sql).withPlanner(hepPlanner)
+ .check();
+ }
+
+ /** Test case of
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-7369";>[CALCITE-7369]
+ * ProjectToWindowRule loses column alias when optimizing OVER window
queries</a>. */
+ @Test void testProjectToWindowRuleForMultiOver() {
+ HepProgramBuilder builder = new HepProgramBuilder();
+ builder.addRuleClass(ProjectToWindowRule.class);
+ HepPlanner hepPlanner = new HepPlanner(builder.build());
+ hepPlanner.addRule(CoreRules.PROJECT_TO_LOGICAL_PROJECT_AND_WINDOW);
+
+ final String sql = "select * from ("
+ + "select empno, deptno, last_value(deptno) over (order by empno)
f1,\n"
+ + "first_value(deptno) over (order by empno desc) f3,\n"
+ + "count(deptno) over (order by empno) f2\n"
+ + "from emp) where f2 > 10";
+ sql(sql).withPlanner(hepPlanner)
+ .check();
+ }
}
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 465ce0e3e1..7c56417de6 100644
--- a/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml
+++ b/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml
@@ -5439,7 +5439,7 @@ from emp]]>
</Resource>
<Resource name="planAfter">
<![CDATA[
-LogicalProject($0=[$3], $1=[$4])
+LogicalProject(SUM1=[$3], SUM2=[$4])
LogicalWindow(window#0=[window(partition {1} order by [0] aggs [SUM($1),
SUM($2)])])
LogicalProject(SAL=[$5], DEPTNO=[$7], $2=[+($7, $5)])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
@@ -5461,7 +5461,7 @@ ROWS BETWEEN 5 + 5 PRECEDING AND 1 PRECEDING) AS w_count
from emp
</Resource>
<Resource name="planBefore">
<![CDATA[
-LogicalProject($0=[$1])
+LogicalProject(W_COUNT=[$1])
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]])
@@ -7382,7 +7382,7 @@ LogicalProject(EMPNO=[$0], DEPTNO=[$1], W_COUNT=[$2])
<![CDATA[
LogicalProject(EMPNO=[$0], DEPTNO=[$1], W_COUNT=[$2])
LogicalFilter(condition=[IS NULL($2)])
- LogicalProject(EMPNO=[$0], DEPTNO=[$7], $2=[$9])
+ LogicalProject(EMPNO=[$0], DEPTNO=[$7], W_COUNT=[$9])
LogicalWindow(window#0=[window(partition {7} order by [0] aggs
[COUNT($0)])])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
]]>
@@ -11833,6 +11833,31 @@ LogicalProject(DNAME=[$1])
LogicalAggregate(group=[{0}])
LogicalProject(DEPTNO=[$7])
LogicalTableScan(table=[[scott, EMP]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testProjectToWindowRuleForMultiOver">
+ <Resource name="sql">
+ <![CDATA[select * from (select empno, deptno, last_value(deptno) over
(order by empno) f1,
+first_value(deptno) over (order by empno desc) f3,
+count(deptno) over (order by empno) f2
+from emp) where f2 > 10]]>
+ </Resource>
+ <Resource name="planBefore">
+ <![CDATA[
+LogicalProject(EMPNO=[$0], DEPTNO=[$1], F1=[$2], F3=[$3], F2=[$4])
+ LogicalFilter(condition=[>($4, 10)])
+ LogicalProject(EMPNO=[$0], DEPTNO=[$7], F1=[LAST_VALUE($7) OVER (ORDER BY
$0)], F3=[FIRST_VALUE($7) OVER (ORDER BY $0 DESC)], F2=[COUNT($7) OVER (ORDER
BY $0)])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ <Resource name="planAfter">
+ <![CDATA[
+LogicalProject(EMPNO=[$0], DEPTNO=[$1], F1=[$2], F3=[$3], F2=[$4])
+ LogicalFilter(condition=[>($4, 10)])
+ LogicalProject(EMPNO=[$0], DEPTNO=[$7], F1=[$9], F3=[$11], F2=[$10])
+ LogicalWindow(window#0=[window(order by [0] aggs [LAST_VALUE($7),
COUNT($7)])], window#1=[window(order by [0 DESC] aggs [FIRST_VALUE($7)])])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
]]>
</Resource>
</TestCase>
@@ -11847,7 +11872,7 @@ from emp]]>
</Resource>
<Resource name="planAfter">
<![CDATA[
-LogicalProject($0=[$9], $1=[$11], $2=[$10], $3=[$12])
+LogicalProject(COUNT1=[$9], COUNT2=[$11], SUM1=[$10], SUM2=[$12])
LogicalWindow(window#0=[window(partition {0} order by [5] aggs [COUNT(),
SUM($7)])], window#1=[window(partition {7} order by [5] aggs [COUNT(),
SUM($7)])])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
]]>
@@ -11856,6 +11881,28 @@ LogicalProject($0=[$9], $1=[$11], $2=[$10], $3=[$12])
<![CDATA[
LogicalProject(COUNT1=[COUNT() OVER (PARTITION BY $0 ORDER BY $5)],
COUNT2=[COUNT() OVER (PARTITION BY $7 ORDER BY $5)], SUM1=[SUM($7) OVER
(PARTITION BY $0 ORDER BY $5)], SUM2=[SUM($7) OVER (PARTITION BY $7 ORDER BY
$5)])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testProjectToWindowRuleForNestedOver">
+ <Resource name="sql">
+ <![CDATA[select deptno, f1, f2 from (select *, last_value(deptno) over
(order by empno) f2
+from (select *, first_value(deptno) over (order by empno) f1 from emp))
+]]>
+ </Resource>
+ <Resource name="planBefore">
+ <![CDATA[
+LogicalProject(DEPTNO=[$7], F1=[$9], F2=[LAST_VALUE($7) OVER (ORDER BY $0)])
+ LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4],
SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8], F1=[FIRST_VALUE($7) OVER (ORDER
BY $0)])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ <Resource name="planAfter">
+ <![CDATA[
+LogicalProject(DEPTNO=[$7], F1=[$9], F2=[$10])
+ LogicalWindow(window#0=[window(order by [0] aggs [LAST_VALUE($7)])])
+ LogicalWindow(window#0=[window(order by [0] aggs [FIRST_VALUE($7)])])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
]]>
</Resource>
</TestCase>
@@ -11889,7 +11936,7 @@ from (
</Resource>
<Resource name="planAfter">
<![CDATA[
-LogicalProject($0=[$2], $1=[$3])
+LogicalProject(COL1=[$2], COL2=[$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]])
@@ -16459,7 +16506,7 @@ from (
</Resource>
<Resource name="planAfter">
<![CDATA[
-LogicalProject($0=[$2], $1=[$3], $2=[$4])
+LogicalProject(COL1=[$2], COL2=[$3], COL3=[$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)])], constants=[[100]])
LogicalProject(SAL=[$5], DEPTNO=[$7])
LogicalFilter(condition=[=($5, 5000)])
@@ -16492,7 +16539,7 @@ LogicalProject(COL1=[SUM(100) OVER (ORDER BY $7, $0
RANGE BETWEEN CURRENT ROW AN
</Resource>
<Resource name="planAfter">
<![CDATA[
-LogicalProject($0=[$3], $1=[$4])
+LogicalProject(COL1=[$3], COL2=[$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)])], constants=[[100]])
LogicalProject(EMPNO=[$0], SAL=[$5], DEPTNO=[$7])
LogicalFilter(condition=[=($5, 5000)])
@@ -22115,7 +22162,7 @@ LogicalProject(EXPR$0=[$1], EXPR$1=[$2],
EXPR$2=[SUM($2) OVER (PARTITION BY $1 O
</Resource>
<Resource name="planAfter">
<![CDATA[
-LogicalProject(EXPR$0=[$1], EXPR$1=[$2], $2=[$3])
+LogicalProject(EXPR$0=[$1], EXPR$1=[$2], EXPR$2=[$3])
LogicalWindow(window#0=[window(partition {1} order by [2] aggs [SUM($2)])])
LogicalAggregate(group=[{0}], EXPR$0=[MIN($1)], EXPR$1=[SUM($2)])
LogicalProject(DEPTNO=[$7], EMPNO=[$0], SAL=[$5])
@@ -22164,11 +22211,11 @@ LogicalUnion(all=[true])
<Resource name="planAfter">
<![CDATA[
LogicalUnion(all=[true])
- LogicalProject($0=[$2])
+ LogicalProject(S=[$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])
+ LogicalProject(S=[$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]])
diff --git a/core/src/test/resources/sql/sub-query.iq
b/core/src/test/resources/sql/sub-query.iq
index 971e8c0065..404520f0dc 100644
--- a/core/src/test/resources/sql/sub-query.iq
+++ b/core/src/test/resources/sql/sub-query.iq
@@ -724,7 +724,7 @@ EnumerableCalc(expr#0..4=[{inputs}], DNAME=[$t1],
EXPR$1=[$t2])
EnumerableHashJoin(condition=[=($0, $3)], joinType=[left])
EnumerableCalc(expr#0..2=[{inputs}], proj#0..1=[{exprs}])
EnumerableTableScan(table=[[scott, DEPT]])
- EnumerableCalc(expr#0..8=[{inputs}], expr#9=[1], expr#10=[<=($t8, $t9)],
COMM=[$t6], DEPTNO=[$t7], $2=[$t8], $condition=[$t10])
+ EnumerableCalc(expr#0..8=[{inputs}], expr#9=[1], expr#10=[<=($t8, $t9)],
COMM=[$t6], DEPTNO=[$t7], rn=[$t8], $condition=[$t10])
EnumerableWindow(window#0=[window(partition {7} order by [6 DESC] rows
between UNBOUNDED PRECEDING and CURRENT ROW aggs [ROW_NUMBER()])])
EnumerableCalc(expr#0..7=[{inputs}], expr#8=[IS NOT NULL($t7)],
proj#0..7=[{exprs}], $condition=[$t8])
EnumerableTableScan(table=[[scott, EMP]])
@@ -2435,7 +2435,7 @@ EnumerableCalc(expr#0..5=[{inputs}], expr#6=[NOT($t3)],
expr#7=[IS NOT NULL($t3)
EnumerableSort(sort0=[$1], dir0=[ASC])
EnumerableCalc(expr#0..2=[{inputs}], cs=[$t1], DEPTNO=[$t0], rn=[$t2])
EnumerableWindow(window#0=[window(partition {0} order by [1 DESC]
rows between UNBOUNDED PRECEDING and CURRENT ROW aggs [ROW_NUMBER()])])
- EnumerableCalc(expr#0..2=[{inputs}], expr#3=[CAST($t0):INTEGER NOT
NULL], expr#4=[0], expr#5=[>($t3, $t4)], expr#6=[null:TINYINT],
expr#7=[CASE($t5, $t0, $t6)], expr#8=[IS NOT NULL($t7)],
expr#9=[CAST($t7):INTEGER], expr#10=[Sarg[10; NULL AS TRUE]],
expr#11=[SEARCH($t9, $t10)], DEPTNO=[$t0], $1=[$t8], $condition=[$t11])
+ EnumerableCalc(expr#0..2=[{inputs}], expr#3=[CAST($t0):INTEGER NOT
NULL], expr#4=[0], expr#5=[>($t3, $t4)], expr#6=[null:TINYINT],
expr#7=[CASE($t5, $t0, $t6)], expr#8=[IS NOT NULL($t7)],
expr#9=[CAST($t7):INTEGER], expr#10=[Sarg[10; NULL AS TRUE]],
expr#11=[SEARCH($t9, $t10)], DEPTNO=[$t0], cs=[$t8], $condition=[$t11])
EnumerableTableScan(table=[[scott, DEPT]])
!plan
diff --git a/core/src/test/resources/sql/winagg.iq
b/core/src/test/resources/sql/winagg.iq
index feb4dc6951..beb90d9fd4 100644
--- a/core/src/test/resources/sql/winagg.iq
+++ b/core/src/test/resources/sql/winagg.iq
@@ -1003,7 +1003,7 @@ select gender, count(*) over(partition by gender order by
ename) as count1 from
# Get the plan and result which push filter past window
select gender, count(*) over(partition by gender order by ename) as count1
from emp where gender = 'F';
-EnumerableCalc(expr#0..3=[{inputs}], GENDER=[$t2], $1=[$t3])
+EnumerableCalc(expr#0..3=[{inputs}], GENDER=[$t2], COUNT1=[$t3])
EnumerableWindow(window#0=[window(partition {2} order by [0] aggs
[COUNT()])])
EnumerableCalc(expr#0..2=[{inputs}], expr#3=['F'], expr#4=[=($t2, $t3)],
proj#0..2=[{exprs}], $condition=[$t4])
EnumerableValues(tuples=[[{ 'Jane ', 10, 'F' }, { 'Bob ', 10, 'M' }, {
'Eric ', 20, 'M' }, { 'Susan', 30, 'F' }, { 'Alice', 30, 'F' }, { 'Adam ', 50,
'M' }, { 'Eve ', 50, 'F' }, { 'Grace', 60, 'F' }, { 'Wilma', null, 'F' }]])
diff --git a/piglet/src/test/java/org/apache/calcite/test/PigRelOpTest.java
b/piglet/src/test/java/org/apache/calcite/test/PigRelOpTest.java
index 5067503b89..e1c60c10cb 100644
--- a/piglet/src/test/java/org/apache/calcite/test/PigRelOpTest.java
+++ b/piglet/src/test/java/org/apache/calcite/test/PigRelOpTest.java
@@ -1665,10 +1665,10 @@ private Fluent pig(String script) {
+ " name=[$1], age=[$2], city=[$3])\n"
+ " LogicalTableScan(table=[[emp1]])\n";
- final String sql = "SELECT w0$o0 AS rank_A, id, name, age, city\n"
+ final String sql = "SELECT rank_A, id, name, age, city\n"
+ "FROM (SELECT id, name, age, city, RANK() OVER ()\n"
+ " FROM emp1) AS t\n"
- + "WHERE w0$o0 > 1";
+ + "WHERE rank_A > 1";
pig(script).assertRel(hasTree(plan))
.assertSql(is(sql));
}
