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

N0
SELECT /*+ ENFORCE_JOIN_ORDER, DISABLE_RULE('JoinCommuteRule', 'MergeJoinConverter', 'NestedLoopJoinConverter', 'CorrelatedNestedLoopJoin') */ * FROM t1_n1 JOIN t2_n1 USING (id)
---
Fragment#0 root
  executionNodes: [N0]
  remoteFragments: [1]
  exchangeSourceNodes: {1=[N1]}
  tree:
    Receiver(sourceFragment=1, exchange=1, distribution=single)

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

N0
SELECT /*+ ENFORCE_JOIN_ORDER, DISABLE_RULE('JoinCommuteRule', 'MergeJoinConverter', 'NestedLoopJoinConverter', 'CorrelatedNestedLoopJoin') */ * FROM t1_n1 t1 JOIN t1_n1 t2 ON t1.id = t2.id
---
Fragment#0 root
  executionNodes: [N0]
  remoteFragments: [1]
  exchangeSourceNodes: {1=[N1]}
  tree:
    Receiver(sourceFragment=1, exchange=1, distribution=single)

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

N0
SELECT /*+ ENFORCE_JOIN_ORDER, DISABLE_RULE('JoinCommuteRule', 'MergeJoinConverter', 'NestedLoopJoinConverter', 'CorrelatedNestedLoopJoin') */ * FROM t1_n1 JOIN t2_n2 USING (id)
---
Fragment#0 root
  executionNodes: [N0]
  remoteFragments: [1]
  exchangeSourceNodes: {1=[N1]}
  tree:
    Receiver(sourceFragment=1, exchange=1, distribution=single)

Fragment#4
  targetNodes: [N0]
  executionNodes: [N1]
  remoteFragments: [5]
  exchangeSourceNodes: {5=[N2]}
  tables: [T1_N1, T2_N2]
  partitions: {N1=[0:1]}
  tree:
    Sender(targetFragment=0, exchange=1, distribution=single)
      Project
        HashJoin
          TableScan(name=PUBLIC.T1_N1, source=2, partitions=1, distribution=affinity[table: T1_N1, columns: [ID]])
          Receiver(sourceFragment=5, exchange=5, distribution=affinity[table: T2_N2, columns: [ID]])

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

N0
SELECT /*+ ENFORCE_JOIN_ORDER, DISABLE_RULE('JoinCommuteRule', 'MergeJoinConverter', 'NestedLoopJoinConverter', 'CorrelatedNestedLoopJoin') */ * FROM t1_n1
JOIN TABLE(SYSTEM_RANGE(0, 10)) r ON t1_n1.id = r.x
---
Fragment#0 root
  executionNodes: [N0]
  remoteFragments: [1]
  exchangeSourceNodes: {1=[N1]}
  tree:
    Project
      HashJoin
        Receiver(sourceFragment=1, exchange=1, distribution=single)
        TableFunctionScan(source=2, distribution=single)

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