[
https://issues.apache.org/jira/browse/IGNITE-27555?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=18051706#comment-18051706
]
Konstantin Orlov commented on IGNITE-27555:
-------------------------------------------
Plan before decorrelation looks like this:
{code}
LogicalSort(sort0=[$0], dir0=[ASC])
LogicalProject(I=[$1], EXPR$1=[$5])
LogicalCorrelate(correlation=[$cor1], joinType=[left],
requiredColumns=[{1}])
LogicalTableScan(table=[[PUBLIC, INTEGERS]], tableId=[18])
LogicalAggregate(group=[{}], agg#0=[SINGLE_VALUE($0)])
LogicalProject(EXPR$0=[$1])
LogicalAggregate(group=[{0}], EXPR$0=[COUNT()])
LogicalProject($f0=[$cor1.I])
LogicalFilter(condition=[>($1, $cor1.I)])
LogicalTableScan(table=[[PUBLIC, INTEGERS]], tableId=[18])
{code}
And here is plan after:
{code}
LogicalSort(sort0=[$0], dir0=[ASC])
LogicalProject(I=[$1], EXPR$1=[$6])
LogicalJoin(condition=[IS NOT DISTINCT FROM($1, $5)], joinType=[left])
LogicalTableScan(table=[[PUBLIC, INTEGERS]], tableId=[18])
LogicalAggregate(group=[{0}], agg#0=[SINGLE_VALUE($1)])
LogicalProject(I1=[$1], EXPR$0=[$2])
LogicalAggregate(group=[{0, 1}], EXPR$0=[COUNT()])
LogicalProject($f0=[$6], I1=[$6])
LogicalJoin(condition=[true], joinType=[inner])
LogicalJoin(condition=[>($1, $5)], joinType=[inner])
LogicalTableScan(table=[[PUBLIC, INTEGERS]], tableId=[18])
LogicalAggregate(group=[{0}])
LogicalProject(I=[$1])
LogicalTableScan(table=[[PUBLIC, INTEGERS]], tableId=[18])
LogicalAggregate(group=[{0}])
LogicalProject(I=[$1])
LogicalTableScan(table=[[PUBLIC, INTEGERS]], tableId=[18])
{code}
I think, the problem is this extra join:
{code}
LogicalJoin(condition=[true], joinType=[inner]) <<-- this one
<...>
LogicalAggregate(group=[{0}])
LogicalProject(I=[$1])
LogicalTableScan(table=[[PUBLIC, INTEGERS]], tableId=[18])
{code}
> Sql. Wrong plan generated by decorrelator when correlation id is used in
> multiple operators
> -------------------------------------------------------------------------------------------
>
> Key: IGNITE-27555
> URL: https://issues.apache.org/jira/browse/IGNITE-27555
> Project: Ignite
> Issue Type: Bug
> Components: sql ai3
> Reporter: Konstantin Orlov
> Priority: Major
> Labels: ignite-3
>
> This problem was discovered while implementing IGNITE-22092. To reproduce it
> may be needed to remove validation in
> {{org.apache.ignite.internal.sql.engine.prepare.PlannerHelper#tryDecorrelate}}
> (search for usages of {{CorrelationUsedOnlyInSingleRelValidator}}).
> Take a look at this test cases:
> {code:java}
> CREATE TABLE integers(i INTEGER)
> INSERT INTO integers VALUES (1), (2), (3), (NULL)
> SELECT i, (SELECT COUNT(*) FROM integers i2 WHERE i2.i>i1.i GROUP BY i1.i)
> FROM integers i1 ORDER BY i;
> ----
> 1 2
> 2 1
> 3 NULL
> NULL NULL
> {code}
> If we disable aforementioned validation, then test will fail with {{Invalid
> results: [[1, 3], [2, 3], [3, 3], [null, 3]]}}.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
