modules/sql-engine/src/integrationTest/sql/group1/explain/join.test

# description: Tests for printout of various join operators.
#     Plan around these operators may change in whatever way,
#     the only thing that must be held is an invariant described
#     in comments to every test case.
# group: [explain]

# hash join
# inner
explain plan
WITH
  t1(x) AS (SELECT * FROM system_range(1, 10)),
  t2(y) AS (SELECT * FROM system_range(1, 5))
SELECT /*+ ENFORCE_JOIN_ORDER, DISABLE_RULE('MergeJoinConverterRule', 'NestedLoopJoinConverterRule') */
  * FROM t1 JOIN t2 ON t1.x = t2.y
----
HashJoin
    predicate: =(X, X$0)
    fieldNames: [X, X$0]
    type: inner
    est: (rows=1500)
  TableFunctionScan
      fieldNames: [X]
      invocation: SYSTEM_RANGE(1, 10)
      est: (rows=100)
  TableFunctionScan
      fieldNames: [X]
      invocation: SYSTEM_RANGE(1, 5)
      est: (rows=100)

# left
explain plan
WITH
  t1(x) AS (SELECT * FROM system_range(1, 10)),
  t2(y) AS (SELECT * FROM system_range(1, 5))
SELECT /*+ ENFORCE_JOIN_ORDER, DISABLE_RULE('MergeJoinConverterRule', 'NestedLoopJoinConverterRule') */
  * FROM t1 LEFT JOIN t2 ON t1.x = t2.y
----
HashJoin
    predicate: =(X, X$0)
    fieldNames: [X, X$0]
    type: left
    est: (rows=1585)
  TableFunctionScan
      fieldNames: [X]
      invocation: SYSTEM_RANGE(1, 10)
      est: (rows=100)
  TableFunctionScan
      fieldNames: [X]
      invocation: SYSTEM_RANGE(1, 5)
      est: (rows=100)

# right
explain plan
WITH
  t1(x) AS (SELECT * FROM system_range(1, 10)),
  t2(y) AS (SELECT * FROM system_range(1, 5))
SELECT /*+ ENFORCE_JOIN_ORDER, DISABLE_RULE('MergeJoinConverterRule', 'NestedLoopJoinConverterRule') */
  * FROM t1 RIGHT JOIN t2 ON t1.x = t2.y
----
HashJoin
    predicate: =(X, X$0)
    fieldNames: [X, X$0]
    type: right
    est: (rows=1585)
  TableFunctionScan
      fieldNames: [X]
      invocation: SYSTEM_RANGE(1, 10)
      est: (rows=100)
  TableFunctionScan
      fieldNames: [X]
      invocation: SYSTEM_RANGE(1, 5)
      est: (rows=100)

# full
explain plan
WITH
  t1(x) AS (SELECT * FROM system_range(1, 10)),
  t2(y) AS (SELECT * FROM system_range(1, 5))
SELECT /*+ ENFORCE_JOIN_ORDER, DISABLE_RULE('MergeJoinConverterRule', 'NestedLoopJoinConverterRule') */
  * FROM t1 FULL JOIN t2 ON t1.x = t2.y
----
HashJoin
    predicate: =(X, X$0)
    fieldNames: [X, X$0]
    type: full
    est: (rows=1670)
  TableFunctionScan
      fieldNames: [X]
      invocation: SYSTEM_RANGE(1, 10)
      est: (rows=100)
  TableFunctionScan
      fieldNames: [X]
      invocation: SYSTEM_RANGE(1, 5)
      est: (rows=100)

# sort merge join
# inner
explain plan
WITH
  t1(x) AS (SELECT * FROM system_range(1, 15)),
  t2(y) AS (SELECT * FROM system_range(1, 25))
SELECT /*+ ENFORCE_JOIN_ORDER, DISABLE_RULE('HashJoinConverter', 'NestedLoopJoinConverter') */
  * FROM t1 JOIN t2 ON t1.x = t2.y ORDER BY t1.x
----
MergeJoin
    predicate: =(X, X$0)
    fieldNames: [X, X$0]
    type: inner
    est: (rows=1500)
  Sort
      collation: [X ASC]
      est: (rows=100)
    TableFunctionScan
        fieldNames: [X]
        invocation: SYSTEM_RANGE(1, 15)
        est: (rows=100)
  Sort
      collation: [X ASC]
      est: (rows=100)
    TableFunctionScan
        fieldNames: [X]
        invocation: SYSTEM_RANGE(1, 25)
        est: (rows=100)

# left
explain plan
WITH
  t1(x) AS (SELECT * FROM system_range(1, 15)),
  t2(y) AS (SELECT * FROM system_range(1, 25))
SELECT /*+ ENFORCE_JOIN_ORDER, DISABLE_RULE('HashJoinConverter', 'NestedLoopJoinConverter') */
  * FROM t1 LEFT JOIN t2 ON t1.x = t2.y ORDER BY t1.x
----
MergeJoin
    predicate: =(X, X$0)
    fieldNames: [X, X$0]
    type: left
    est: (rows=1585)
  Sort
      collation: [X ASC]
      est: (rows=100)
    TableFunctionScan
        fieldNames: [X]
        invocation: SYSTEM_RANGE(1, 15)
        est: (rows=100)
  Sort
      collation: [X ASC]
      est: (rows=100)
    TableFunctionScan
        fieldNames: [X]
        invocation: SYSTEM_RANGE(1, 25)
        est: (rows=100)

# right
explain plan
WITH
  t1(x) AS (SELECT * FROM system_range(1, 15)),
  t2(y) AS (SELECT * FROM system_range(1, 25))
SELECT /*+ ENFORCE_JOIN_ORDER, DISABLE_RULE('HashJoinConverter', 'NestedLoopJoinConverter') */
  * FROM t1 RIGHT JOIN t2 ON t1.x = t2.y ORDER BY t1.x
----
Sort
    collation: [X ASC]
    est: (rows=1585)
  MergeJoin
      predicate: =(X, X$0)
      fieldNames: [X, X$0]
      type: right
      est: (rows=1585)
    Sort
        collation: [X ASC]
        est: (rows=100)
      TableFunctionScan
          fieldNames: [X]
          invocation: SYSTEM_RANGE(1, 15)
          est: (rows=100)
    Sort
        collation: [X ASC]
        est: (rows=100)
      TableFunctionScan
          fieldNames: [X]
          invocation: SYSTEM_RANGE(1, 25)
          est: (rows=100)

# full
explain plan
WITH
  t1(x) AS (SELECT * FROM system_range(1, 15)),
  t2(y) AS (SELECT * FROM system_range(1, 25))
SELECT /*+ ENFORCE_JOIN_ORDER, DISABLE_RULE('HashJoinConverter', 'NestedLoopJoinConverter') */
  * FROM t1 FULL JOIN t2 ON t1.x = t2.y ORDER BY t1.x
----
Sort
    collation: [X ASC]
    est: (rows=1670)
  MergeJoin
      predicate: =(X, X$0)
      fieldNames: [X, X$0]
      type: full
      est: (rows=1670)
    Sort
        collation: [X ASC]
        est: (rows=100)
      TableFunctionScan
          fieldNames: [X]
          invocation: SYSTEM_RANGE(1, 15)
          est: (rows=100)
    Sort
        collation: [X ASC]
        est: (rows=100)
      TableFunctionScan
          fieldNames: [X]
          invocation: SYSTEM_RANGE(1, 25)
          est: (rows=100)

# correlated nested loop join
explain plan
WITH
  t1(x) AS (SELECT * FROM system_range(1, 20)),
  t2(y) AS (SELECT * FROM system_range(1, 15))
SELECT  /*+ disable_decorrelation , ENFORCE_JOIN_ORDER, DISABLE_RULE('MergeJoinConverterRule', 'HashJoinConverter', 'NestedLoopJoinConverter') */
  * FROM t1 WHERE EXISTS(SELECT * FROM t2 WHERE t1.x = t2.y)
----
CorrelatedNestedLoopJoin
    predicate: true
    type: inner
    correlates: [$cor1]
    est: (rows=100)
  TableFunctionScan
      fieldNames: [X]
      invocation: SYSTEM_RANGE(1, 20)
      est: (rows=100)
  Project
      fieldNames: []
      projection: []
      est: (rows=1)
    ColocatedHashAggregate
        group: [i]
        aggregation: []
        est: (rows=1)
      Project
          fieldNames: [i]
          projection: [true]
          est: (rows=15)
        Filter
            predicate: =($cor1.X, X)
            est: (rows=15)
          TableFunctionScan
              fieldNames: [X]
              invocation: SYSTEM_RANGE(1, 15)
              est: (rows=100)


# nested loop join

explain plan
WITH
  t1(x) AS (SELECT * FROM system_range(1, 20)),
  t2(y) AS (SELECT * FROM system_range(1, 15))
SELECT  /*+ ENFORCE_JOIN_ORDER */ * FROM t1, t2
----
NestedLoopJoin
    predicate: true
    fieldNames: [X, X$0]
    type: inner
    est: (rows=10000)
  TableFunctionScan
      fieldNames: [X]
      invocation: SYSTEM_RANGE(1, 20)
      est: (rows=100)
  TableFunctionScan
      fieldNames: [X]
      invocation: SYSTEM_RANGE(1, 15)
      est: (rows=100)

# cross join

explain plan
WITH
  t1(x) AS (SELECT * FROM system_range(1, 25)),
  t2(y) AS (SELECT * FROM system_range(1, 35))
SELECT  /*+ ENFORCE_JOIN_ORDER */ * FROM t1 CROSS JOIN t2
----
NestedLoopJoin
    predicate: true
    fieldNames: [X, X$0]
    type: inner
    est: (rows=10000)
  TableFunctionScan
      fieldNames: [X]
      invocation: SYSTEM_RANGE(1, 25)
      est: (rows=100)
  TableFunctionScan
      fieldNames: [X]
      invocation: SYSTEM_RANGE(1, 35)
      est: (rows=100)

# anti join

statement ok
CREATE TABLE dst (id INT PRIMARY KEY, val INT);

explain plan
MERGE INTO dst 
USING (SELECT * FROM system_range(1, 25)) AS src
   ON dst.id = src.x
 WHEN NOT MATCHED THEN INSERT VALUES (x, x)
----
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.DST
          fieldNames: [ROWCOUNT]
          type: MERGE
          est: (rows=1)
        Exchange
            distribution: table PUBLIC.DST in zone "Default" by [X]
            est: (rows=1)
          Project
              fieldNames: [X, X0]
              projection: [CAST(X):INTEGER NOT NULL, CAST(X):INTEGER NOT NULL]
              est: (rows=1)
            HashJoin
                predicate: =(ID0, X)
                fieldNames: [X]
                type: anti
                est: (rows=1)
              TableFunctionScan
                  fieldNames: [X]
                  invocation: SYSTEM_RANGE(1, 25)
                  est: (rows=100)
              Exchange
                  distribution: single
                  est: (rows=1)
                TableScan
                    table: PUBLIC.DST
                    fieldNames: [ID0]
                    projection: [CAST(ID):BIGINT NOT NULL]
                    est: (rows=1)