[
https://issues.apache.org/jira/browse/IGNITE-25275?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Konstantin Orlov updated IGNITE-25275:
--------------------------------------
Description:
This problem is similar to one described in IGNITE-25253, but now correlated
subquery references the right side of join:
{code}
sqlScript(""
+ "CREATE TABLE t0 ("
+ " id INT PRIMARY KEY,"
+ " val INT);"
+ "CREATE TABLE t1 (id INT PRIMARY KEY, val INT);");
sql("insert into t0 values(1, 10), (2, 20);");
sql("insert into t1 values(1, 10), (2, 20);");
assertQuery("SELECT t1.ID FROM t0 JOIN t1 ON "
+ "(t0.id = (SELECT inner_t0.id FROM t0 AS inner_t0 WHERE
inner_t0.val = t1.val)) ORDER BY 1")
.matches(containsSubPlan("CorrelatedNestedLoopJoin"))
.returns(1)
.returns(2)
.check();
{code}
was:
This problem is similar to one described in IGNITE-25253, but now correlated
subquery references the right side of join:
```
sqlScript(""
+ "CREATE TABLE t0 ("
+ " id INT PRIMARY KEY,"
+ " val INT);"
+ "CREATE TABLE t1 (id INT PRIMARY KEY, val INT);");
sql("insert into t0 values(1, 10), (2, 20);");
sql("insert into t1 values(1, 10), (2, 20);");
assertQuery("SELECT t1.ID FROM t0 JOIN t1 ON "
+ "(t0.id = (SELECT inner_t0.id FROM t0 AS inner_t0 WHERE
inner_t0.val = t1.val)) ORDER BY 1")
.matches(containsSubPlan("CorrelatedNestedLoopJoin"))
.returns(1)
.returns(2)
.check();
```
> Sql. Correlated sub-query in ON classes of join cannot be rewritten
> -------------------------------------------------------------------
>
> Key: IGNITE-25275
> URL: https://issues.apache.org/jira/browse/IGNITE-25275
> Project: Ignite
> Issue Type: Bug
> Components: sql ai3
> Reporter: Konstantin Orlov
> Assignee: Konstantin Orlov
> Priority: Major
> Labels: ignite-3
>
> This problem is similar to one described in IGNITE-25253, but now correlated
> subquery references the right side of join:
> {code}
> sqlScript(""
> + "CREATE TABLE t0 ("
> + " id INT PRIMARY KEY,"
> + " val INT);"
> + "CREATE TABLE t1 (id INT PRIMARY KEY, val INT);");
> sql("insert into t0 values(1, 10), (2, 20);");
> sql("insert into t1 values(1, 10), (2, 20);");
> assertQuery("SELECT t1.ID FROM t0 JOIN t1 ON "
> + "(t0.id = (SELECT inner_t0.id FROM t0 AS inner_t0 WHERE
> inner_t0.val = t1.val)) ORDER BY 1")
> .matches(containsSubPlan("CorrelatedNestedLoopJoin"))
> .returns(1)
> .returns(2)
> .check();
> {code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
