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

     1  # tests adapted from logictest -- where
     2  
     3  exec-ddl
     4  CREATE TABLE kv (
     5    k INT PRIMARY KEY,
     6    v INT
     7  )
     8  ----
     9  
    10  exec-ddl
    11  CREATE TABLE kvString (
    12    k STRING PRIMARY KEY,
    13    v STRING
    14  )
    15  ----
    16  
    17  build
    18  SELECT * FROM kv WHERE k IN (1, 3)
    19  ----
    20  select
    21   ├── columns: k:1!null v:2
    22   ├── scan kv
    23   │    └── columns: k:1!null v:2
    24   └── filters
    25        └── k:1 IN (1, 3)
    26  
    27  build
    28  SELECT * FROM kv WHERE v IN (6)
    29  ----
    30  select
    31   ├── columns: k:1!null v:2!null
    32   ├── scan kv
    33   │    └── columns: k:1!null v:2
    34   └── filters
    35        └── v:2 IN (6,)
    36  
    37  build
    38  SELECT * FROM kv WHERE k IN (SELECT k FROM kv)
    39  ----
    40  select
    41   ├── columns: k:1!null v:2
    42   ├── scan kv
    43   │    └── columns: k:1!null v:2
    44   └── filters
    45        └── any: eq
    46             ├── project
    47             │    ├── columns: k:3!null
    48             │    └── scan kv
    49             │         └── columns: k:3!null v:4
    50             └── k:1
    51  
    52  build
    53  SELECT * FROM kv WHERE (k,v) IN (SELECT * FROM kv)
    54  ----
    55  select
    56   ├── columns: k:1!null v:2
    57   ├── scan kv
    58   │    └── columns: k:1!null v:2
    59   └── filters
    60        └── any: eq
    61             ├── project
    62             │    ├── columns: column5:5
    63             │    ├── scan kv
    64             │    │    └── columns: k:3!null v:4
    65             │    └── projections
    66             │         └── (k:3, v:4) [as=column5:5]
    67             └── (k:1, v:2)
    68  
    69  build
    70  SELECT * FROM kv WHERE nonexistent = 1
    71  ----
    72  error (42703): column "nonexistent" does not exist
    73  
    74  build
    75  SELECT 'hello' LIKE v AS r FROM kvString WHERE k LIKE 'like%' ORDER BY k
    76  ----
    77  project
    78   ├── columns: r:3  [hidden: k:1!null]
    79   ├── ordering: +1
    80   ├── select
    81   │    ├── columns: k:1!null v:2
    82   │    ├── ordering: +1
    83   │    ├── scan kvstring
    84   │    │    ├── columns: k:1!null v:2
    85   │    │    └── ordering: +1
    86   │    └── filters
    87   │         └── k:1 LIKE 'like%'
    88   └── projections
    89        └── 'hello' LIKE v:2 [as=r:3]
    90  
    91  build
    92  SELECT 'hello' SIMILAR TO v AS r FROM kvString WHERE k SIMILAR TO 'like[1-2]' ORDER BY k
    93  ----
    94  project
    95   ├── columns: r:3  [hidden: k:1!null]
    96   ├── ordering: +1
    97   ├── select
    98   │    ├── columns: k:1!null v:2
    99   │    ├── ordering: +1
   100   │    ├── scan kvstring
   101   │    │    ├── columns: k:1!null v:2
   102   │    │    └── ordering: +1
   103   │    └── filters
   104   │         └── k:1 SIMILAR TO 'like[1-2]'
   105   └── projections
   106        └── 'hello' SIMILAR TO v:2 [as=r:3]
   107  
   108  build
   109  SELECT 'hello' ~ replace(v, '%', '.*') AS r FROM kvString WHERE k ~ 'like[1-2]' ORDER BY k
   110  ----
   111  project
   112   ├── columns: r:3  [hidden: k:1!null]
   113   ├── ordering: +1
   114   ├── select
   115   │    ├── columns: k:1!null v:2
   116   │    ├── ordering: +1
   117   │    ├── scan kvstring
   118   │    │    ├── columns: k:1!null v:2
   119   │    │    └── ordering: +1
   120   │    └── filters
   121   │         └── k:1 ~ 'like[1-2]'
   122   └── projections
   123        └── 'hello' ~ replace(v:2, '%', '.*') [as=r:3]
   124  
   125  # Test mixed type tuple comparison.
   126  
   127  build
   128  SELECT * FROM kv WHERE k IN (1, 5.0, 9)
   129  ----
   130  select
   131   ├── columns: k:1!null v:2
   132   ├── scan kv
   133   │    └── columns: k:1!null v:2
   134   └── filters
   135        └── k:1 IN (1, 5, 9)
   136  
   137  # Regression tests for #22670.
   138  exec-ddl
   139  CREATE TABLE ab (a INT, b INT)
   140  ----
   141  
   142  build
   143  SELECT * FROM ab WHERE a IN (1, 3, 4)
   144  ----
   145  project
   146   ├── columns: a:1!null b:2
   147   └── select
   148        ├── columns: a:1!null b:2 rowid:3!null
   149        ├── scan ab
   150        │    └── columns: a:1 b:2 rowid:3!null
   151        └── filters
   152             └── a:1 IN (1, 3, 4)
   153  
   154  build
   155  SELECT * FROM ab WHERE a IN (1, 3, 4, NULL)
   156  ----
   157  project
   158   ├── columns: a:1!null b:2
   159   └── select
   160        ├── columns: a:1!null b:2 rowid:3!null
   161        ├── scan ab
   162        │    └── columns: a:1 b:2 rowid:3!null
   163        └── filters
   164             └── a:1 IN (1, 3, 4, NULL)
   165  
   166  build
   167  SELECT * FROM ab WHERE (a, b) IN ((1, 10), (3, 30), (4, 40))
   168  ----
   169  project
   170   ├── columns: a:1!null b:2!null
   171   └── select
   172        ├── columns: a:1!null b:2!null rowid:3!null
   173        ├── scan ab
   174        │    └── columns: a:1 b:2 rowid:3!null
   175        └── filters
   176             └── (a:1, b:2) IN ((1, 10), (3, 30), (4, 40))
   177  
   178  build
   179  SELECT * FROM ab WHERE (a, b) IN ((1, 10), (4, NULL), (NULL, 50))
   180  ----
   181  project
   182   ├── columns: a:1!null b:2!null
   183   └── select
   184        ├── columns: a:1!null b:2!null rowid:3!null
   185        ├── scan ab
   186        │    └── columns: a:1 b:2 rowid:3!null
   187        └── filters
   188             └── (a:1, b:2) IN ((1, 10), (4, NULL), (NULL, 50))
   189  
   190  # Where clause must be type bool.
   191  build
   192  SELECT * FROM ab WHERE a
   193  ----
   194  error (42804): argument of WHERE must be type bool, not type int