github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/sql/logictest/testdata/logic_test/merge_join_dist (about)

     1  # LogicTest: 5node-default-configs
     2  
     3  # Regression test for #39317.
     4  
     5  statement ok
     6  CREATE TABLE l (a INT PRIMARY KEY, b INT, FAMILY (a, b))
     7  
     8  statement ok
     9  CREATE TABLE r (a INT PRIMARY KEY, b INT, FAMILY (a, b))
    10  
    11  statement ok
    12  INSERT INTO l VALUES (1, 10), (2, 20), (3, 30)
    13  
    14  statement ok
    15  INSERT INTO r VALUES (2, 200), (3, 300), (4, 400)
    16  
    17  statement ok
    18  ALTER TABLE l SPLIT AT VALUES (2), (3)
    19  
    20  statement ok
    21  ALTER TABLE r SPLIT AT VALUES (2), (3)
    22  
    23  statement ok
    24  ALTER TABLE l EXPERIMENTAL_RELOCATE VALUES (ARRAY[1], 1), (ARRAY[2], 2), (ARRAY[3], 3)
    25  
    26  statement ok
    27  ALTER TABLE r EXPERIMENTAL_RELOCATE VALUES (ARRAY[1], 2), (ARRAY[2], 3), (ARRAY[3], 1)
    28  
    29  query TTTI colnames
    30  SELECT start_key, end_key, replicas, lease_holder from [SHOW EXPERIMENTAL_RANGES FROM TABLE l] ORDER BY lease_holder
    31  ----
    32  start_key  end_key  replicas  lease_holder
    33  NULL       /2       {1}       1
    34  /2         /3       {2}       2
    35  /3         NULL     {3}       3
    36  
    37  query TTTI colnames
    38  SELECT start_key, end_key, replicas, lease_holder from [SHOW EXPERIMENTAL_RANGES FROM TABLE r] ORDER BY lease_holder
    39  ----
    40  start_key  end_key  replicas  lease_holder
    41  /2         /3       {1}       1
    42  /3         NULL     {2}       2
    43  NULL       /2       {3}       3
    44  
    45  query III
    46  SELECT * FROM l LEFT OUTER JOIN r USING(a) WHERE a = 2
    47  ----
    48  2 20 200
    49  
    50  # Test that LEFT SEMI merge join outputs batches only with the columns from the
    51  # left side.
    52  query II rowsort
    53  SELECT * FROM l WHERE EXISTS(SELECT * FROM r WHERE r.a=l.a)
    54  ----
    55  2  20
    56  3  30