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

     1  # Basic tables, no nulls
     2  
     3  statement ok
     4  CREATE TABLE t1 (k INT PRIMARY KEY, v INT)
     5  
     6  statement ok
     7  INSERT INTO t1 VALUES (-1, -1), (0, 4), (2, 1), (3, 4), (5, 4)
     8  
     9  statement ok
    10  CREATE TABLE t2 (x INT, y INT, INDEX x (x))
    11  
    12  statement ok
    13  INSERT INTO t2 VALUES (0, 5), (1, 3), (1, 4), (3, 2), (3, 3), (4, 6)
    14  
    15  query IIII rowsort
    16  SELECT k, v, x, y FROM t1 INNER MERGE JOIN t2 ON k = x
    17  ----
    18  0 4 0 5
    19  3 4 3 2
    20  3 4 3 3
    21  
    22  statement ok
    23  DROP TABLE t1
    24  
    25  statement ok
    26  DROP TABLE t2
    27  
    28  # Basic tables with nulls
    29  
    30  statement ok
    31  CREATE TABLE t1 (k INT, INDEX k(k))
    32  
    33  statement ok
    34  INSERT INTO t1 VALUES (0), (null)
    35  
    36  statement ok
    37  CREATE TABLE t2 (x INT, INDEX x (x))
    38  
    39  statement ok
    40  INSERT INTO t2 VALUES (0), (null)
    41  
    42  query II
    43  SELECT k, x FROM t1 INNER MERGE JOIN t2 ON k = x
    44  ----
    45  0 0
    46  
    47  # Regression test for the inputs that have comparable but different types (see
    48  # issue #44798).
    49  statement ok
    50  CREATE TABLE t44798_0(c0 INT4 PRIMARY KEY); CREATE TABLE t44798_1(c0 INT8 PRIMARY KEY)
    51  
    52  statement ok
    53  INSERT INTO t44798_0(c0) VALUES(0), (1), (2); INSERT INTO t44798_1(c0) VALUES(0), (2), (4)
    54  
    55  # Note that integers of different width are still considered equal.
    56  query I rowsort
    57  SELECT * FROM t44798_0 NATURAL JOIN t44798_1
    58  ----
    59  0
    60  2
    61  
    62  # Regression test for batch type schema prefix mismatch after LEFT ANTI join
    63  # (48622).
    64  statement ok
    65  CREATE TABLE l (l INT PRIMARY KEY); INSERT INTO l VALUES (1), (2);
    66  CREATE TABLE r (r INT PRIMARY KEY); INSERT INTO r VALUES (1)
    67  
    68  query IB
    69  SELECT *, true FROM (SELECT l FROM l WHERE l NOT IN (SELECT r FROM r))
    70  ----
    71  2 true