[jira] [Created] (IGNITE-28580) Sql. Merge Operator. Constants are not pushed-down into scan predicates when NOT MATCHED clause is present

[Maksim Zhuravkov (Jira)]

Maksim Zhuravkov created IGNITE-28580:
-----------------------------------------

             Summary: Sql. Merge Operator. Constants are not pushed-down into 
scan predicates when NOT MATCHED clause is present
                 Key: IGNITE-28580
                 URL: https://issues.apache.org/jira/browse/IGNITE-28580
             Project: Ignite
          Issue Type: Bug
          Components: sql ai3
    Affects Versions: 3.1
            Reporter: Maksim Zhuravkov


Constant values for columns used in a top level condition of MERGE clause are 
not propagated into conditions pushed-down into scan nodes, when there is a NOT 
MATCH clause in MERGE operator. 
The same happens when both NOT MATCHED and MATCHED are present.

*Expected behaviour* 

Constant values are pushed into scan and their values are present in predicates 
pushed down into scan nodes.

*Examples*

{noformat}
MERGE INTO T2 dst USING t1 src ON src.key = 42
WHEN NOT MATCHED THEN INSERT (key, val) VALUES (src.key, 101)
{noformat}

Plan
{noformat}
Project
    fieldNames: [ROWCOUNT]
    projection: [CAST($f0):BIGINT NOT NULL]
    est: (rows=1)
  ColocatedHashAggregate
      fieldNames: [$f0]
      group: []
      aggregation: [$SUM0(ROWCOUNT)]
      est: (rows=1)
    Exchange
        distribution: single
        est: (rows=1)
      TableModify
          table: PUBLIC.T2
          fieldNames: [ROWCOUNT]
          type: MERGE
          est: (rows=1)
        Project
            fieldNames: [KEY, $f1]
            projection: [KEY, 101]
            est: (rows=1)
          Exchange
              distribution: table PUBLIC.T2 in zone "Default" by [KEY]
              est: (rows=1)
            NestedLoopJoin
                predicate: =(KEY, 42)
                fieldNames: [KEY]
                type: anti
                est: (rows=1)
              Exchange
                  distribution: single
                  est: (rows=1)
                TableScan
                    table: PUBLIC.T1
                    fieldNames: [KEY] < Predicate is missing
                    est: (rows=1)
              Exchange
                  distribution: single
                  est: (rows=1)
                TableScan
                    table: PUBLIC.T2
                    fieldNames: [DUMMY]
                    projection: [0]
                    est: (rows=1)
{noformat}
There is the same issue, when both NOT MATCHED and MATCHED are present.

But for a statement without NOT MATCHED clause values are propagated correctly:

{noformat}
MERGE INTO T2 dst USING t1 src ON src.key = 42
WHEN MATCHED THEN UPDATE SET val = 99 
{noformat}

Plan
{noformat}
Project
    fieldNames: [ROWCOUNT]
    projection: [CAST($f0):BIGINT NOT NULL]
    est: (rows=1)
  ColocatedHashAggregate
      fieldNames: [$f0]
      group: []
      aggregation: [$SUM0(ROWCOUNT)]
      est: (rows=1)
    Exchange
        distribution: single
        est: (rows=1)
      TableModify
          table: PUBLIC.T2
          fieldNames: [ROWCOUNT]
          type: MERGE
          est: (rows=1)
        Project
            fieldNames: [KEY0, VAL0, $f2]
            projection: [KEY, VAL, 99]
            est: (rows=1)
          Exchange
              distribution: table PUBLIC.T2 in zone "Default" by [KEY]
              est: (rows=1)
            NestedLoopJoin
                predicate: true
                fieldNames: [KEY, VAL, KEY$0]
                type: inner
                est: (rows=1)
              Exchange
                  distribution: single
                  est: (rows=1)
                TableScan
                    table: PUBLIC.T2
                    fieldNames: [KEY, VAL]
                    est: (rows=1)
              Exchange
                  distribution: single
                  est: (rows=1)
                TableScan
                    table: PUBLIC.T1
                    predicate: =(KEY, 42)  < Predicate is here
                    fieldNames: [KEY]
                    est: (rows=1)
{noformat}

Reproducer:


{code:java}
import org.junit.jupiter.api.Test;

public class MergeOpTest extends BaseSqlMultiStatementTest {

    @Override
    protected int initialNodes() {
        return 1;
    }

    @Test
    public void test() {
        sql("CREATE TABLE t1 (key INT, val INT, PRIMARY KEY (key))");
        sql("CREATE TABLE t2 (key INT, val INT, PRIMARY KEY (key))");

        {
            String sql = "EXPLAIN PLAN FOR MERGE INTO T2 dst USING t1 src ON 
src.key = 42 "
                    + "WHEN MATCHED THEN UPDATE SET val = 99 ";
            System.err.println(sql(sql));
        }

        System.err.println("---");

        {
            String sql = "EXPLAIN PLAN FOR MERGE INTO T2 dst USING t1 src ON 
src.key = 42 "
                    + "WHEN MATCHED THEN UPDATE SET val = 99 "
                    + "WHEN NOT MATCHED THEN INSERT (key, val) VALUES (src.key, 
101)";
            System.err.println(sql(sql));
        }

        System.err.println("---");

        {
            String sql = "EXPLAIN PLAN FOR MERGE INTO T2 dst USING t1 src ON 
src.key = 42 "
                    + "WHEN NOT MATCHED THEN INSERT (key, val) VALUES (src.key, 
101)";
            System.err.println(sql(sql));
        }
    }
}
{code}





--
This message was sent by Atlassian Jira
(v8.20.10#820010)