lincoln lee created CALCITE-7375:
------------------------------------
Summary: ProjectWindowTransposeRule does not correctly adjust
column indices in window bounds
Key: CALCITE-7375
URL: https://issues.apache.org/jira/browse/CALCITE-7375
Project: Calcite
Issue Type: Bug
Components: core
Affects Versions: 1.41.0
Reporter: lincoln lee
Assignee: lincoln lee
Fix For: 1.42.0
In CALCITE-6746, the fix for updating column indices during
ProjectWindowTransposeRule project pushdown only synchronized the input field
requirements, but did not update the column indices referenced by the window
bounds. The issue was masked in the added test case because the indexed column
was not affected by the pushdown.
We can construct a nested constant window case to product the error:
{code}
@Test void testNestedConstantWindow() {
final String sql =
"select deptno, f1, f2 from (\n"
+ "select *, last_value(deptno) over"
+ " (order by empno ROWS BETWEEN 2 PRECEDING AND 1 FOLLOWING) f2\n"
+ "from (\n"
+ " select *, first_value(deptno) over "
+ " (order by empno ROWS BETWEEN 2 PRECEDING AND 1 FOLLOWING) f1
from emp))\n";;
sql(sql)
.withPreRule(CoreRules.PROJECT_TO_LOGICAL_PROJECT_AND_WINDOW)
.withRule(CoreRules.PROJECT_WINDOW_TRANSPOSE)
.check();
}
{code}
the incorrect plan:
{code}
LogicalProject(DEPTNO=[$1], F1=[$2], F2=[$3])
LogicalWindow(window#0=[window(order by [0] rows between $3 PRECEDING and $4
FOLLOWING aggs [LAST_VALUE($1)])], constants=[[2, 1]])
LogicalWindow(window#0=[window(order by [0] rows between $9 PRECEDING and
$10 FOLLOWING aggs [FIRST_VALUE($1)])], constants=[[2, 1]])
LogicalProject(EMPNO=[$0], DEPTNO=[$7])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
{code}
correct plan should be:
{code}
LogicalProject(DEPTNO=[$1], F1=[$2], F2=[$3])
LogicalWindow(window#0=[window(order by [0] rows between $3 PRECEDING and $4
FOLLOWING aggs [LAST_VALUE($1)])], constants=[[2, 1]])
LogicalWindow(window#0=[window(order by [0] rows between $2 PRECEDING and
$3 FOLLOWING aggs [FIRST_VALUE($1)])], constants=[[2, 1]])
LogicalProject(EMPNO=[$0], DEPTNO=[$7])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
{code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
