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

     1  # LogicTest: 5node-default-configs
     2  
     3  # Test that the distSQL MergeJoiner follows SQL NULL semantics for ON predicate
     4  # equivalence. The use of sorts here force the planning of merge join.
     5  
     6  statement ok
     7  CREATE TABLE distsql_mj_test (k INT, v INT)
     8  
     9  statement ok
    10  INSERT INTO distsql_mj_test VALUES (0, NULL), (0, 1), (2, 4), (NULL, 4)
    11  
    12  # If SQL NULL semantics are not followed, NULL = NULL is truthy. This makes the rows with NULL also appear in the inner join.
    13  
    14  query IIII rowsort
    15  SELECT l.k, l.v, r.k, r.v FROM (SELECT * FROM distsql_mj_test ORDER BY k, v) l INNER JOIN (SELECT * FROM distsql_mj_test ORDER BY k, v) r ON l.k = r.k AND l.v = r.v
    16  ----
    17  0  1  0  1
    18  2  4  2  4
    19  
    20  statement ok
    21  DELETE FROM distsql_mj_test WHERE TRUE;
    22  
    23  statement ok
    24  INSERT INTO distsql_mj_test VALUES (0, NULL), (1, NULL), (2, NULL)
    25  
    26  # We should not have any results for values with NULLs
    27  query IIII rowsort
    28  SELECT l.k, l.v, r.k, r.v FROM (SELECT * FROM distsql_mj_test ORDER BY k, v) l INNER JOIN (SELECT * FROM distsql_mj_test ORDER BY k, v) r ON l.k = r.k AND l.v = r.v
    29  ----
    30  
    31  statement ok
    32  DELETE FROM distsql_mj_test WHERE TRUE;
    33  
    34  statement ok
    35  INSERT INTO distsql_mj_test VALUES (NULL)
    36  
    37  # We shouldn't expect a row of (NULL, NULL), otherwise NULL = NULL was joined.
    38  query II rowsort
    39  SELECT l.k, r.k FROM (SELECT * FROM distsql_mj_test ORDER BY k) l INNER JOIN (SELECT * FROM distsql_mj_test ORDER BY k) r ON l.k = r.k
    40  ----
    41  
    42  # Regression test for #23001.
    43  
    44  statement ok
    45  CREATE TABLE tab0(pk INTEGER PRIMARY KEY, a INTEGER, b INTEGER);
    46  
    47  statement ok
    48  INSERT INTO tab0 VALUES(0,1,2);
    49  
    50  statement ok
    51  CREATE INDEX on tab0 (a);
    52  
    53  query III
    54  SELECT pk, a, b FROM tab0 WHERE a < 10 AND b = 2 ORDER BY a DESC, pk;
    55  ----
    56  0 1 2
    57  
    58  query T
    59  SELECT feature_name FROM crdb_internal.feature_usage WHERE feature_name='sql.exec.query.is-distributed' AND usage_count > 0
    60  ----
    61  sql.exec.query.is-distributed