[jira] [Created] (IGNITE-25235) Calcite engine. Wrong query results for CNLJ with broadcast

["Aleksey Plekhanov (Jira)" <ji...@apache.org>]

Aleksey Plekhanov created IGNITE-25235:
------------------------------------------

             Summary: Calcite engine. Wrong query results for CNLJ 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


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)