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

     1  statement ok
     2  CREATE TABLE kv (
     3    k INT PRIMARY KEY,
     4    v INT
     5  )
     6  
     7  statement ok
     8  INSERT INTO kv VALUES (1, 2), (3, 4), (5, 6), (7, 8)
     9  
    10  statement ok
    11  CREATE TABLE kvString (
    12    k STRING PRIMARY KEY,
    13    v STRING
    14  )
    15  
    16  statement ok
    17  INSERT INTO kvString VALUES ('like1', 'hell%'), ('like2', 'worl%')
    18  
    19  query II rowsort
    20  SELECT * FROM kv WHERE True
    21  ----
    22  1 2
    23  3 4
    24  5 6
    25  7 8
    26  
    27  query II
    28  SELECT * FROM kv WHERE False
    29  ----
    30  
    31  query II rowsort
    32  SELECT * FROM kv WHERE k IN (1, 3)
    33  ----
    34  1 2
    35  3 4
    36  
    37  query II
    38  SELECT * FROM kv WHERE v IN (6)
    39  ----
    40  5 6
    41  
    42  query II rowsort
    43  SELECT * FROM kv WHERE k IN (SELECT k FROM kv)
    44  ----
    45  1 2
    46  3 4
    47  5 6
    48  7 8
    49  
    50  query II rowsort
    51  SELECT * FROM kv WHERE (k,v) IN (SELECT * FROM kv)
    52  ----
    53  1 2
    54  3 4
    55  5 6
    56  7 8
    57  
    58  query error column "nonexistent" does not exist
    59  SELECT * FROM kv WHERE nonexistent = 1
    60  
    61  query B
    62  SELECT 'hello' LIKE v FROM kvString WHERE k LIKE 'like%' ORDER BY k
    63  ----
    64  true
    65  false
    66  
    67  query B
    68  SELECT 'hello' SIMILAR TO v FROM kvString WHERE k SIMILAR TO 'like[1-2]' ORDER BY k
    69  ----
    70  true
    71  false
    72  
    73  query B
    74  SELECT 'hello' ~ replace(v, '%', '.*') FROM kvString WHERE k ~ 'like[1-2]' ORDER BY k
    75  ----
    76  true
    77  false
    78  
    79  # Test mixed type tuple comparison.
    80  
    81  query II rowsort
    82  SELECT * FROM kv WHERE k IN (1, 5.0, 9)
    83  ----
    84  1 2
    85  5 6
    86  
    87  # Regression tests for #22670.
    88  statement ok
    89  CREATE TABLE ab (a INT, b INT)
    90  
    91  statement ok
    92  INSERT INTO ab VALUES (1, 10), (2, 20), (3, 30), (4, NULL), (NULL, 50), (NULL, NULL)
    93  
    94  query II rowsort
    95  SELECT * FROM ab WHERE a IN (1, 3, 4)
    96  ----
    97  1  10
    98  3  30
    99  4  NULL
   100  
   101  query II rowsort
   102  SELECT * FROM ab WHERE a IN (1, 3, 4, NULL)
   103  ----
   104  1  10
   105  3  30
   106  4  NULL
   107  
   108  query II rowsort
   109  SELECT * FROM ab WHERE (a, b) IN ((1, 10), (3, 30), (4, 40))
   110  ----
   111  1  10
   112  3  30
   113  
   114  query II rowsort
   115  SELECT * FROM ab WHERE (a, b) IN ((1, 10), (4, NULL), (NULL, 50))
   116  ----
   117  1  10