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

     1  # LogicTest: 5node-default-configs
     2  
     3  statement ok
     4  CREATE TABLE t (k INT, v INT)
     5  
     6  statement ok
     7  INSERT INTO t VALUES (1, 10), (2, 20), (3, 30)
     8  
     9  statement ok
    10  ALTER TABLE t EXPERIMENTAL_RELOCATE VALUES (ARRAY[3], 1)
    11  
    12  statement ok
    13  CREATE TABLE xy (x INT PRIMARY KEY, y INT)
    14  
    15  statement ok
    16  INSERT INTO xy VALUES (2, 200), (3, 300), (4, 400)
    17  
    18  statement ok
    19  ALTER TABLE t SPLIT AT VALUES (3), (4)
    20  
    21  statement ok
    22  ALTER TABLE t EXPERIMENTAL_RELOCATE VALUES (ARRAY[1], 2), (ARRAY[2], 3), (ARRAY[3], 4)
    23  
    24  # Test that LEFT SEMI hash join outputs batches only with the columns from the
    25  # left side.
    26  query II rowsort
    27  SELECT * FROM t WHERE EXISTS(SELECT * FROM xy WHERE x=t.k)
    28  ----
    29  2  20
    30  3  30
    31  
    32  # Regression test for #39303.
    33  statement ok
    34  CREATE TABLE small (a INT PRIMARY KEY, b INT)
    35  
    36  statement ok
    37  CREATE TABLE large (c INT, d INT)
    38  
    39  statement ok
    40  INSERT INTO small SELECT x, 3*x FROM
    41    generate_series(1, 10) AS a(x)
    42  
    43  statement ok
    44  INSERT INTO large SELECT 2*x, 4*x FROM
    45    generate_series(1, 10) AS a(x)
    46  
    47  statement ok
    48  ALTER TABLE small SPLIT AT SELECT a FROM small
    49  
    50  statement ok
    51  ALTER TABLE small EXPERIMENTAL_RELOCATE SELECT ARRAY[mod(i, 3) + 1], i FROM generate_series(1, 10) AS g(i)
    52  
    53  statement ok
    54  ALTER TABLE large SPLIT AT SELECT 2*i FROM generate_series(1, 10) AS g(i)
    55  
    56  statement ok
    57  ALTER TABLE large EXPERIMENTAL_RELOCATE SELECT ARRAY[mod(i, 3) + 1], 2*i FROM generate_series(1, 10) as g(i)
    58  
    59  # Test that RIGHT OUTER hash join correctly sets probeRowUnmatched on
    60  # subsequent batches.
    61  query II rowsort
    62  SELECT small.b, large.d FROM large RIGHT HASH JOIN small ON small.b = large.c AND large.d < 30 ORDER BY 1 LIMIT 5
    63  ----
    64  3   NULL
    65  6   12
    66  9   NULL
    67  12  24
    68  15  NULL