modules/sql-engine/src/test/resources/mapping/correlated.test

N0
SELECT (SELECT count(*) FROM ct_n1) FROM t_n1
---
Fragment#4 root
  executionNodes: [N0]
  remoteFragments: [5]
  exchangeSourceNodes: {5=[N1]}
  tree:
    Receiver(sourceFragment=5, exchange=5, distribution=single)

Fragment#1
  targetNodes: [N1]
  executionNodes: [N1]
  tables: [T_N1]
  partitions: {N1=[0:1]}
  tree:
    Sender(targetFragment=5, exchange=1, distribution=single)
      TableScan(name=PUBLIC.T_N1, source=3, partitions=1, distribution=random)

Fragment#5
  targetNodes: [N0]
  executionNodes: [N1]
  remoteFragments: [1]
  exchangeSourceNodes: {1=[N1]}
  tables: [CT_N1]
  partitions: {N1=[0:1]}
  tree:
    Sender(targetFragment=4, exchange=5, distribution=single)
      Project
        NestedLoopJoin
          Receiver(sourceFragment=1, exchange=1, distribution=single)
          ColocatedHashAggregate
            TableScan(name=PUBLIC.CT_N1, source=2, partitions=1, distribution=single)
---

N1
SELECT (SELECT count(*) FROM ct_n1) FROM t_n1
---
Fragment#0 root
  executionNodes: [N1]
  remoteFragments: [1]
  exchangeSourceNodes: {1=[N1]}
  tables: [CT_N1]
  partitions: {N1=[0:1]}
  tree:
    Project
      NestedLoopJoin
        Receiver(sourceFragment=1, exchange=1, distribution=single)
        ColocatedHashAggregate
          TableScan(name=PUBLIC.CT_N1, source=2, partitions=1, distribution=single)

Fragment#1
  targetNodes: [N1]
  executionNodes: [N1]
  tables: [T_N1]
  partitions: {N1=[0:1]}
  tree:
    Sender(targetFragment=0, exchange=1, distribution=single)
      TableScan(name=PUBLIC.T_N1, source=3, partitions=1, distribution=random)
---

N0
SELECT (SELECT count(*) FROM ct_n1) FROM t_n2
---
Fragment#4 root
  executionNodes: [N0]
  remoteFragments: [5]
  exchangeSourceNodes: {5=[N1]}
  tree:
    Receiver(sourceFragment=5, exchange=5, distribution=single)

Fragment#1
  targetNodes: [N1]
  executionNodes: [N2]
  tables: [T_N2]
  partitions: {N2=[0:1]}
  tree:
    Sender(targetFragment=5, exchange=1, distribution=single)
      TableScan(name=PUBLIC.T_N2, source=3, partitions=1, distribution=random)

Fragment#5
  targetNodes: [N0]
  executionNodes: [N1]
  remoteFragments: [1]
  exchangeSourceNodes: {1=[N2]}
  tables: [CT_N1]
  partitions: {N1=[0:1]}
  tree:
    Sender(targetFragment=4, exchange=5, distribution=single)
      Project
        NestedLoopJoin
          Receiver(sourceFragment=1, exchange=1, distribution=single)
          ColocatedHashAggregate
            TableScan(name=PUBLIC.CT_N1, source=2, partitions=1, distribution=single)
---

N1
SELECT (SELECT count(*) FROM ct_n1) FROM t_n2
---
Fragment#0 root
  executionNodes: [N1]
  remoteFragments: [1]
  exchangeSourceNodes: {1=[N2]}
  tables: [CT_N1]
  partitions: {N1=[0:1]}
  tree:
    Project
      NestedLoopJoin
        Receiver(sourceFragment=1, exchange=1, distribution=single)
        ColocatedHashAggregate
          TableScan(name=PUBLIC.CT_N1, source=2, partitions=1, distribution=single)

Fragment#1
  targetNodes: [N1]
  executionNodes: [N2]
  tables: [T_N2]
  partitions: {N2=[0:1]}
  tree:
    Sender(targetFragment=0, exchange=1, distribution=single)
      TableScan(name=PUBLIC.T_N2, source=3, partitions=1, distribution=random)
---

N0
SELECT t.c1 FROM t_n1 t JOIN table(system_range(1, 50)) as r ON t.id = r.x WHERE mod(r.x, 10) = 0
---
Fragment#0 root
  executionNodes: [N0]
  remoteFragments: [1]
  exchangeSourceNodes: {1=[N1]}
  tree:
    Project
      HashJoin
        Receiver(sourceFragment=1, exchange=1, distribution=single)
        Filter
          TableFunctionScan(source=2, distribution=single)

Fragment#1
  targetNodes: [N0]
  executionNodes: [N1]
  tables: [T_N1]
  partitions: {N1=[0:1]}
  tree:
    Sender(targetFragment=0, exchange=1, distribution=single)
      TableScan(name=PUBLIC.T_N1, source=3, partitions=1, distribution=random)
---

N0
SELECT t.c1 FROM ct_n1 t WHERE t.c1 < 5 AND
EXISTS (SELECT x FROM table(system_range(t.c1, t.c2)) WHERE mod(x, 2) = 0)
---
Fragment#3 root
  executionNodes: [N0]
  remoteFragments: [4]
  exchangeSourceNodes: {4=[N1]}
  tree:
    Receiver(sourceFragment=4, exchange=4, distribution=single)

Fragment#4
  targetNodes: [N0]
  executionNodes: [N1]
  tables: [CT_N1]
  partitions: {N1=[0:1]}
  tree:
    Sender(targetFragment=3, exchange=4, distribution=single)
      Project
        CorrelatedNestedLoopJoin
          TableScan(name=PUBLIC.CT_N1, source=1, partitions=1, distribution=single)
          ColocatedHashAggregate
            Project
              Filter
                TableFunctionScan(source=2, distribution=single)
---

N0
SELECT t.c1 FROM t_n1 t WHERE t.c1 < 5 AND
EXISTS (SELECT x FROM table(system_range(t.c1, t.c2)) WHERE mod(x, 2) = 0)
---
Fragment#0 root
  executionNodes: [N0]
  remoteFragments: [1]
  exchangeSourceNodes: {1=[N1]}
  tree:
    Project
      CorrelatedNestedLoopJoin
        Receiver(sourceFragment=1, exchange=1, distribution=single)
        ColocatedHashAggregate
          Project
            Filter
              TableFunctionScan(source=2, distribution=single)

Fragment#1
  targetNodes: [N0]
  executionNodes: [N1]
  tables: [T_N1]
  partitions: {N1=[0:1]}
  tree:
    Sender(targetFragment=0, exchange=1, distribution=single)
      TableScan(name=PUBLIC.T_N1, source=3, partitions=1, distribution=affinity[table: T_N1, columns: [ID]])
---
# Pass partition pruning metadata for correlated joins.
N0
SELECT * FROM t1_n0n1n2 as cor WHERE EXISTS (SELECT 1 FROM t2_n0n1n2 as t2 WHERE t2.id = cor.id)
---
Fragment#0 root
  executionNodes: [N0]
  remoteFragments: [1, 2]
  exchangeSourceNodes: {1=[N0, N1, N2], 2=[N0, N1, N2]}
  tree:
    Project
      CorrelatedNestedLoopJoin
        Receiver(sourceFragment=1, exchange=1, distribution=single)
        ReduceHashAggregate
          Receiver(sourceFragment=2, exchange=2, distribution=single)

Fragment#2 correlated
  targetNodes: [N0]
  executionNodes: [N0, N1, N2]
  tables: [T2_N0N1N2]
  partitions: {N0=[0:3], N1=[1:3], N2=[2:3]}
  pruningMetadata: [3=[{0=$cor0.ID}]]
  tree:
    Sender(targetFragment=0, exchange=2, distribution=single)
      MapHashAggregate
        TableScan(name=PUBLIC.T2_N0N1N2, source=3, partitions=3, distribution=random)

Fragment#1
  targetNodes: [N0]
  executionNodes: [N0, N1, N2]
  tables: [T1_N0N1N2]
  partitions: {N0=[0:3], N1=[1:3], N2=[2:3]}
  tree:
    Sender(targetFragment=0, exchange=1, distribution=single)
      TableScan(name=PUBLIC.T1_N0N1N2, source=4, partitions=3, distribution=affinity[table: T1_N0N1N2, columns: [ID]])
---
# Pass partition pruning metadata to correlated joins.
N0
SELECT * FROM t3_n0n1n2 AS out
WHERE EXISTS (SELECT * FROM t1_n0n1n2 as cor WHERE out.id = cor.id AND EXISTS (SELECT 1 FROM t2_n0n1n2 as t2 WHERE t2.id = cor.id))
---
Fragment#0 root
  executionNodes: [N0]
  remoteFragments: [1, 2, 3]
  exchangeSourceNodes: {1=[N0, N1, N2], 2=[N0, N1, N2], 3=[N0, N1, N2]}
  tree:
    Project
      CorrelatedNestedLoopJoin
        Receiver(sourceFragment=1, exchange=1, distribution=single)
        ColocatedHashAggregate
          Project
            CorrelatedNestedLoopJoin
              Receiver(sourceFragment=2, exchange=2, distribution=single)
              ReduceHashAggregate
                Receiver(sourceFragment=3, exchange=3, distribution=single)

Fragment#3 correlated
  targetNodes: [N0]
  executionNodes: [N0, N1, N2]
  tables: [T2_N0N1N2]
  partitions: {N0=[0:3], N1=[1:3], N2=[2:3]}
  pruningMetadata: [4=[{0=$cor1.ID}]]
  tree:
    Sender(targetFragment=0, exchange=3, distribution=single)
      MapHashAggregate
        TableScan(name=PUBLIC.T2_N0N1N2, source=4, partitions=3, distribution=random)

Fragment#2 correlated
  targetNodes: [N0]
  executionNodes: [N0, N1, N2]
  tables: [T1_N0N1N2]
  partitions: {N0=[0:3], N1=[1:3], N2=[2:3]}
  pruningMetadata: [5=[{0=$cor0.ID}]]
  tree:
    Sender(targetFragment=0, exchange=2, distribution=single)
      TableScan(name=PUBLIC.T1_N0N1N2, source=5, partitions=3, distribution=affinity[table: T1_N0N1N2, columns: [ID]])

Fragment#1
  targetNodes: [N0]
  executionNodes: [N0, N1, N2]
  tables: [T3_N0N1N2]
  partitions: {N0=[0:3], N1=[1:3], N2=[2:3]}
  tree:
    Sender(targetFragment=0, exchange=1, distribution=single)
      TableScan(name=PUBLIC.T3_N0N1N2, source=6, partitions=3, distribution=affinity[table: T3_N0N1N2, columns: [ID]])
---
# Pass partition pruning metadata to correlated joins one layer deep.
N0
SELECT * FROM t3_n0n1n2 AS out
WHERE EXISTS (
  SELECT * FROM t1_n0n1n2 as cor
  WHERE out.id = cor.id AND EXISTS (SELECT 1 FROM t2_n0n1n2 as t2 WHERE t2.id = out.id or t2.id=cor.id)
)
---
Fragment#0 root
  executionNodes: [N0]
  remoteFragments: [1, 2, 3]
  exchangeSourceNodes: {1=[N0, N1, N2], 2=[N0, N1, N2], 3=[N0, N1, N2]}
  tree:
    Project
      CorrelatedNestedLoopJoin
        Receiver(sourceFragment=1, exchange=1, distribution=single)
        ColocatedHashAggregate
          Project
            CorrelatedNestedLoopJoin
              Receiver(sourceFragment=2, exchange=2, distribution=single)
              ReduceHashAggregate
                Receiver(sourceFragment=3, exchange=3, distribution=single)

Fragment#3 correlated
  targetNodes: [N0]
  executionNodes: [N0, N1, N2]
  tables: [T2_N0N1N2]
  partitions: {N0=[0:3], N1=[1:3], N2=[2:3]}
  pruningMetadata: [4=[{0=$cor0.ID}, {0=$cor2.ID}]]
  tree:
    Sender(targetFragment=0, exchange=3, distribution=single)
      MapHashAggregate
        TableScan(name=PUBLIC.T2_N0N1N2, source=4, partitions=3, distribution=random)

Fragment#2 correlated
  targetNodes: [N0]
  executionNodes: [N0, N1, N2]
  tables: [T1_N0N1N2]
  partitions: {N0=[0:3], N1=[1:3], N2=[2:3]}
  pruningMetadata: [5=[{0=$cor0.ID}]]
  tree:
    Sender(targetFragment=0, exchange=2, distribution=single)
      TableScan(name=PUBLIC.T1_N0N1N2, source=5, partitions=3, distribution=affinity[table: T1_N0N1N2, columns: [ID]])

Fragment#1
  targetNodes: [N0]
  executionNodes: [N0, N1, N2]
  tables: [T3_N0N1N2]
  partitions: {N0=[0:3], N1=[1:3], N2=[2:3]}
  tree:
    Sender(targetFragment=0, exchange=1, distribution=single)
      TableScan(name=PUBLIC.T3_N0N1N2, source=6, partitions=3, distribution=affinity[table: T3_N0N1N2, columns: [ID]])
---