[
https://issues.apache.org/jira/browse/CALCITE-4913?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Konstantin Orlov updated CALCITE-4913:
--------------------------------------
Description:
Currently, the deduplication of correlated variables ain't done for correlates
in select list. Looks like the root cause here is same as for CALCITE-4673,
but, unfortunately, suggested patch has addressed only the particular problem
with a table function. It would be great to provide a more gereric fix.
Besides, the project rel doesn't expose any correlated variables that should be
set by this node.
The problematic query is follow:
{code:java}
select e.deptno,
(select count(*) from emp where e.deptno > 0) as c1,
(select count(*) from emp where e.deptno > 0 and e.deptno < 10) as c2
from emp e;
{code}
Currently after conversion to relational nodes it looks like:
{code:java}
LogicalProject(DEPTNO=[$7], EXPR$1=[$SCALAR_QUERY({
LogicalAggregate(group=[{}], EXPR$0=[COUNT()])
LogicalProject($f0=[0])
LogicalFilter(condition=[>($cor0.DEPTNO, 0)])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
})], EXPR$2=[$SCALAR_QUERY({
LogicalAggregate(group=[{}], EXPR$0=[COUNT()])
LogicalProject($f0=[0])
LogicalFilter(condition=[AND(>($cor1.DEPTNO, 0), <($cor2.DEPTNO, 10))])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
})])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
{code}
All the correlates are set by the very top project node and could be
represented by only one variable.
Moreover, I've tried to add the query above
was:
Currently, the deduplication of correlated variables ain't done for correlates
in select list. Looks like the root cause here is same as for CALCITE-4673,
but, unfortunately, suggested patch has addressed only the particular problem
with a table function. It would be great to provide a more gereric fix.
Besides, the project rel doesn't expose any correlated variables that should be
set by this node
> Correlated variables in a select list are not deduplicated
> ----------------------------------------------------------
>
> Key: CALCITE-4913
> URL: https://issues.apache.org/jira/browse/CALCITE-4913
> Project: Calcite
> Issue Type: Bug
> Components: core
> Affects Versions: 1.28.0
> Reporter: Konstantin Orlov
> Assignee: Konstantin Orlov
> Priority: Major
> Labels: pull-request-available
> Time Spent: 3h 50m
> Remaining Estimate: 0h
>
> Currently, the deduplication of correlated variables ain't done for
> correlates in select list. Looks like the root cause here is same as for
> CALCITE-4673, but, unfortunately, suggested patch has addressed only the
> particular problem with a table function. It would be great to provide a more
> gereric fix.
> Besides, the project rel doesn't expose any correlated variables that should
> be set by this node.
> The problematic query is follow:
> {code:java}
> select e.deptno,
> (select count(*) from emp where e.deptno > 0) as c1,
> (select count(*) from emp where e.deptno > 0 and e.deptno < 10) as c2
> from emp e;
> {code}
> Currently after conversion to relational nodes it looks like:
> {code:java}
> LogicalProject(DEPTNO=[$7], EXPR$1=[$SCALAR_QUERY({
> LogicalAggregate(group=[{}], EXPR$0=[COUNT()])
> LogicalProject($f0=[0])
> LogicalFilter(condition=[>($cor0.DEPTNO, 0)])
> LogicalTableScan(table=[[CATALOG, SALES, EMP]])
> })], EXPR$2=[$SCALAR_QUERY({
> LogicalAggregate(group=[{}], EXPR$0=[COUNT()])
> LogicalProject($f0=[0])
> LogicalFilter(condition=[AND(>($cor1.DEPTNO, 0), <($cor2.DEPTNO, 10))])
> LogicalTableScan(table=[[CATALOG, SALES, EMP]])
> })])
> LogicalTableScan(table=[[CATALOG, SALES, EMP]])
> {code}
> All the correlates are set by the very top project node and could be
> represented by only one variable.
> Moreover, I've tried to add the query above
--
This message was sent by Atlassian Jira
(v8.20.1#820001)
