[
https://issues.apache.org/jira/browse/CALCITE-876?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Julian Hyde updated CALCITE-876:
--------------------------------
Description:
ProjectWindowTransposeRule would produce a LogicalWindow which mis-refers
constant fields. A query like:
{code}
select col1, col2 \n"
from (
select empno, sum(100) over (partition by deptno order by sal) as col1,
sum(1000) over(partition by deptno order by sal) as col2
from emp
{code}
where the inner query generates columns which are never used in the main query.
If we use the three rules in the hep planner:
{code}
ProjectToWindowRule
ProjectMergeRule
ProjectWindowTransposeRule
{code}
we will get a plan:
{code}
LogicalProject(COL1=[$2], COL2=[$3])
LogicalWindow(window#0=[window(partition {1} order by [0] range between
UNBOUNDED PRECEDING and CURRENT ROW aggs [SUM($2), SUM($2)])])
LogicalProject(SAL=[$1], DEPTNO=[$2])
LogicalProject(EMPNO=[$0], SAL=[$5], DEPTNO=[$7])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
{code}
Note that the second SUM() in the LogicalWindow should have pointed to the
second constant (i.e., $3).
was:
A query like:
{code}
select col1, col2 \n"
from (
select empno, sum(100) over (partition by deptno order by sal) as col1,
sum(1000) over(partition by deptno order by sal) as col2
from emp
{code}
where the inner query generates columns which are never used in the main query.
If we use the three rules in the hep planner:
{code}
ProjectToWindowRule
ProjectMergeRule
ProjectWindowTransposeRule
{code}
, we will get a plan:
{code}
LogicalProject(COL1=[$2], COL2=[$3])
LogicalWindow(window#0=[window(partition {1} order by [0] range between
UNBOUNDED PRECEDING and CURRENT ROW aggs [SUM($2), SUM($2)])])
LogicalProject(SAL=[$1], DEPTNO=[$2])
LogicalProject(EMPNO=[$0], SAL=[$5], DEPTNO=[$7])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
{code}
Note that the second SUM() in the LogicalWindow should have pointed to the
second constant (i.e., $3).
> After pushing Project past Window, adjust references to constants properly
> --------------------------------------------------------------------------
>
> Key: CALCITE-876
> URL: https://issues.apache.org/jira/browse/CALCITE-876
> Project: Calcite
> Issue Type: Bug
> Components: core
> Reporter: Sean Hsuan-Yi Chu
> Assignee: Julian Hyde
> Fix For: 1.5.0-incubating
>
>
> ProjectWindowTransposeRule would produce a LogicalWindow which mis-refers
> constant fields. A query like:
> {code}
> select col1, col2 \n"
> from (
> select empno, sum(100) over (partition by deptno order by sal) as col1,
> sum(1000) over(partition by deptno order by sal) as col2
> from emp
> {code}
> where the inner query generates columns which are never used in the main
> query. If we use the three rules in the hep planner:
> {code}
> ProjectToWindowRule
> ProjectMergeRule
> ProjectWindowTransposeRule
> {code}
> we will get a plan:
> {code}
> LogicalProject(COL1=[$2], COL2=[$3])
> LogicalWindow(window#0=[window(partition {1} order by [0] range between
> UNBOUNDED PRECEDING and CURRENT ROW aggs [SUM($2), SUM($2)])])
> LogicalProject(SAL=[$1], DEPTNO=[$2])
> LogicalProject(EMPNO=[$0], SAL=[$5], DEPTNO=[$7])
> LogicalTableScan(table=[[CATALOG, SALES, EMP]])
> {code}
> Note that the second SUM() in the LogicalWindow should have pointed to the
> second constant (i.e., $3).
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
