[jira] [Updated] (IGNITE-25235) Calcite engine. Wrong query results for join with broadcast
[
https://issues.apache.org/jira/browse/IGNITE-25235?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Aleksey Plekhanov updated IGNITE-25235:
---
Description:
Reproducer:
{code:java}
@Test
public void testJoinWithBroadcast() {
sql("CREATE TABLE t(id INT, val VARCHAR, PRIMARY KEY(id)) WITH " +
atomicity());
for (int i = 0; i < 1000; i++)
sql("INSERT INTO t VALUES (?, ?)", i, "val" + i);
assertQuery("SELECT * FROM t WHERE id IN (SELECT * FROM (VALUES (?),
(?), (?)))")
.withParams(10, 20, 30)
.returns(10, "val10").returns(20, "val20").returns(30, "val30")
.check();
}
{code}
The plan for the query:
{noformat}
IgniteExchange(distribution=[single]): rowcount = 1.0, cumulative cost =
IgniteCost [rowCount=2019.5, cpu=5028.5, memory=13.0, io=1.0, network=15.0], id
= 4663
IgniteProject(ID=[$0], VAL=[$1]): rowcount = 1.0, cumulative cost =
IgniteCost [rowCount=2018.5, cpu=5027.5, memory=13.0, io=1.0, network=7.0], id
= 4662
IgniteMergeJoin(condition=[=($0, $2)], joinType=[inner], variablesSet=[[]],
leftCollation=[[0 ASC-nulls-first]], rightCollation=[[0 ASC-nulls-first]]):
rowcount = 1.0, cumulative cost = IgniteCost [rowCount=2017.5, cpu=5026.5,
memory=13.0, io=1.0, network=7.0], id = 4661
IgniteIndexScan(table=[[PUBLIC, T]], index=[_key_PK_proxy],
requiredColumns=[{2, 3}], inlineScan=[false], collation=[[2 ASC-nulls-first]]):
rowcount = 1000.0, cumulative cost = IgniteCost [rowCount=1001.0, cpu=1001.0,
memory=1.0, io=1.0, network=1.0], id = 414
IgniteExchange(distribution=[affinity[identity=AffinityIdentity
[affFuncCls=class
org.apache.ignite.cache.affinity.rendezvous.RendezvousAffinityFunction,
backups=0, partsCnt=1024, filterCls=class
org.apache.ignite.configuration.CacheConfiguration$IgniteAllNodesPredicate,
hash=1953727900], cacheId=-1578586257][0]]): rowcount = 1.5, cumulative cost =
IgniteCost [rowCount=15.0, cpu=19.5, memory=12.0, io=0.0, network=6.0], id =
4660
IgniteSort(sort0=[$0], dir0=[ASC-nulls-first]): rowcount = 1.5,
cumulative cost = IgniteCost [rowCount=13.5, cpu=18.0, memory=12.0, io=0.0,
network=0.0], id = 4659
IgniteColocatedHashAggregate(group=[{0}]): rowcount = 1.5, cumulative
cost = IgniteCost [rowCount=12.0, cpu=12.0, memory=6.0, io=0.0, network=0.0],
id = 4658
IgniteUnionAll(all=[true]): rowcount = 3.0, cumulative cost =
IgniteCost [rowCount=9.0, cpu=9.0, memory=0.0, io=0.0, network=0.0], id = 4657
IgniteProject(EXPR$0=[?0]): rowcount = 1.0, cumulative cost =
IgniteCost [rowCount=2.0, cpu=2.0, memory=0.0, io=0.0, network=0.0], id = 4654
IgniteValues(tuples=[[{ 0 }]]): rowcount = 1.0, cumulative cost
= IgniteCost [rowCount=1.0, cpu=1.0, memory=0.0, io=0.0, network=0.0], id = 200
IgniteProject(EXPR$0=[?1]): rowcount = 1.0, cumulative cost =
IgniteCost [rowCount=2.0, cpu=2.0, memory=0.0, io=0.0, network=0.0], id = 4655
IgniteValues(tuples=[[{ 0 }]]): rowcount = 1.0, cumulative cost
= IgniteCost [rowCount=1.0, cpu=1.0, memory=0.0, io=0.0, network=0.0], id = 200
IgniteProject(EXPR$0=[?2]): rowcount = 1.0, cumulative cost =
IgniteCost [rowCount=2.0, cpu=2.0, memory=0.0, io=0.0, network=0.0], id = 4656
IgniteValues(tuples=[[{ 0 }]]): rowcount = 1.0, cumulative cost
= IgniteCost [rowCount=1.0, cpu=1.0, memory=0.0, io=0.0, network=0.0], id = 200
{noformat}
There is at least one problem on planning: for some reason {{IgniteValues}}
nodes have {{single}} distribution instead of {{broadcast}}, and
{{IgniteExchange}} to affinity distribution is produced instead of
{{IgniteTrimExchange}}.
But even with {{IgniteExchange}} results should be correct. There is some
another problem on mapping: fragment with {{IgniteValues}} is not sent to any
node, so the result is empty.
was:
Reproducer:
{code:java}
@Test
public void testCorrelatedNestedLoopJoinWithBroadcast() {
sql("CREATE TABLE t(id INT, val VARCHAR, PRIMARY KEY(id)) WITH " +
atomicity());
for (int i = 0; i < 1000; i++)
sql("INSERT INTO t VALUES (?, ?)", i, "val" + i);
assertQuery("SELECT * FROM t WHERE id IN (SELECT * FROM (VALUES (?),
(?), (?)))")
.withParams(10, 20, 30)
.returns(10, "val10").returns(20, "val20").returns(30, "val30")
.check();
}
{code}
The plan for the query:
{noformat}
IgniteExchange(distribution=[single]): rowcount = 1.0, cumulative cost =
IgniteCost [rowCount=2019.5, cpu=5028.5, memory=13.0, io=1.0, network=15.0], id
= 4663
IgniteProject(ID=[$0], VAL=[$1]): rowcount = 1.0, cumulative cost =
IgniteCost [rowCount=2018.5, cpu=5027.5, memory=13.0, io=1.0, network=7.0], id
= 4662
IgniteMergeJoin(condition=[=($0, $2)], joinType=[inner], variablesSet=[[]],
leftCollation=[[0 ASC-nulls-first]], rightCollation=[[0 A
[jira] [Updated] (IGNITE-25235) Calcite engine. Wrong query results for join with broadcast
[
https://issues.apache.org/jira/browse/IGNITE-25235?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Aleksey Plekhanov updated IGNITE-25235:
---
Summary: Calcite engine. Wrong query results for join with broadcast (was:
Calcite engine. Wrong query results for CNLJ with broadcast)
> Calcite engine. Wrong query results for join with broadcast
> ---
>
> Key: IGNITE-25235
> URL: https://issues.apache.org/jira/browse/IGNITE-25235
> Project: Ignite
> Issue Type: Bug
>Reporter: Aleksey Plekhanov
>Assignee: Aleksey Plekhanov
>Priority: Major
> Labels: calcite, ise
>
> Reproducer:
> {code:java}
> @Test
> public void testCorrelatedNestedLoopJoinWithBroadcast() {
> sql("CREATE TABLE t(id INT, val VARCHAR, PRIMARY KEY(id)) WITH " +
> atomicity());
> for (int i = 0; i < 1000; i++)
> sql("INSERT INTO t VALUES (?, ?)", i, "val" + i);
> assertQuery("SELECT * FROM t WHERE id IN (SELECT * FROM (VALUES (?),
> (?), (?)))")
> .withParams(10, 20, 30)
> .returns(10, "val10").returns(20, "val20").returns(30, "val30")
> .check();
> }
> {code}
> The plan for the query:
> {noformat}
> IgniteExchange(distribution=[single]): rowcount = 1.0, cumulative cost =
> IgniteCost [rowCount=2019.5, cpu=5028.5, memory=13.0, io=1.0, network=15.0],
> id = 4663
> IgniteProject(ID=[$0], VAL=[$1]): rowcount = 1.0, cumulative cost =
> IgniteCost [rowCount=2018.5, cpu=5027.5, memory=13.0, io=1.0, network=7.0],
> id = 4662
> IgniteMergeJoin(condition=[=($0, $2)], joinType=[inner],
> variablesSet=[[]], leftCollation=[[0 ASC-nulls-first]], rightCollation=[[0
> ASC-nulls-first]]): rowcount = 1.0, cumulative cost = IgniteCost
> [rowCount=2017.5, cpu=5026.5, memory=13.0, io=1.0, network=7.0], id = 4661
> IgniteIndexScan(table=[[PUBLIC, T]], index=[_key_PK_proxy],
> requiredColumns=[{2, 3}], inlineScan=[false], collation=[[2
> ASC-nulls-first]]): rowcount = 1000.0, cumulative cost = IgniteCost
> [rowCount=1001.0, cpu=1001.0, memory=1.0, io=1.0, network=1.0], id = 414
> IgniteExchange(distribution=[affinity[identity=AffinityIdentity
> [affFuncCls=class
> org.apache.ignite.cache.affinity.rendezvous.RendezvousAffinityFunction,
> backups=0, partsCnt=1024, filterCls=class
> org.apache.ignite.configuration.CacheConfiguration$IgniteAllNodesPredicate,
> hash=1953727900], cacheId=-1578586257][0]]): rowcount = 1.5, cumulative cost
> = IgniteCost [rowCount=15.0, cpu=19.5, memory=12.0, io=0.0, network=6.0], id
> = 4660
> IgniteSort(sort0=[$0], dir0=[ASC-nulls-first]): rowcount = 1.5,
> cumulative cost = IgniteCost [rowCount=13.5, cpu=18.0, memory=12.0, io=0.0,
> network=0.0], id = 4659
> IgniteColocatedHashAggregate(group=[{0}]): rowcount = 1.5,
> cumulative cost = IgniteCost [rowCount=12.0, cpu=12.0, memory=6.0, io=0.0,
> network=0.0], id = 4658
> IgniteUnionAll(all=[true]): rowcount = 3.0, cumulative cost =
> IgniteCost [rowCount=9.0, cpu=9.0, memory=0.0, io=0.0, network=0.0], id = 4657
> IgniteProject(EXPR$0=[?0]): rowcount = 1.0, cumulative cost =
> IgniteCost [rowCount=2.0, cpu=2.0, memory=0.0, io=0.0, network=0.0], id = 4654
> IgniteValues(tuples=[[{ 0 }]]): rowcount = 1.0, cumulative
> cost = IgniteCost [rowCount=1.0, cpu=1.0, memory=0.0, io=0.0, network=0.0],
> id = 200
> IgniteProject(EXPR$0=[?1]): rowcount = 1.0, cumulative cost =
> IgniteCost [rowCount=2.0, cpu=2.0, memory=0.0, io=0.0, network=0.0], id = 4655
> IgniteValues(tuples=[[{ 0 }]]): rowcount = 1.0, cumulative
> cost = IgniteCost [rowCount=1.0, cpu=1.0, memory=0.0, io=0.0, network=0.0],
> id = 200
> IgniteProject(EXPR$0=[?2]): rowcount = 1.0, cumulative cost =
> IgniteCost [rowCount=2.0, cpu=2.0, memory=0.0, io=0.0, network=0.0], id = 4656
> IgniteValues(tuples=[[{ 0 }]]): rowcount = 1.0, cumulative
> cost = IgniteCost [rowCount=1.0, cpu=1.0, memory=0.0, io=0.0, network=0.0],
> id = 200
> {noformat}
> There is at least one problem on planning: for some reason {{IgniteValues}}
> nodes have {{single}} distribution instead of {{broadcast}}, and
> {{IgniteExchange}} to affinity distribution is produced instead of
> {{IgniteTrimExchange}}.
> But even with {{IgniteExchange}} results should be correct. There is some
> another problem on mapping: fragment with {{IgniteValues}} is not sent to any
> node, so the result is empty.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
