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

     1  # LogicTest: local
     2  
     3  statement ok
     4  CREATE TABLE t (x INT[] PRIMARY KEY)
     5  
     6  # Test some scans of constrained spans on arrays.
     7  
     8  query TTT
     9  EXPLAIN SELECT x FROM t WHERE x = ARRAY[1,4,6]
    10  ----
    11  ·     distributed  false
    12  ·     vectorized   true
    13  scan  ·            ·
    14  ·     table        t@primary
    15  ·     spans        /ARRAY[1,4,6]-/ARRAY[1,4,6]/#
    16  
    17  query TTT
    18  EXPLAIN SELECT x FROM t WHERE x < ARRAY[1, 4, 3]
    19  ----
    20  ·     distributed  false
    21  ·     vectorized   true
    22  scan  ·            ·
    23  ·     table        t@primary
    24  ·     spans        -/ARRAY[1,4,3]
    25  
    26  query TTT
    27  EXPLAIN SELECT x FROM t WHERE x > ARRAY [1, NULL]
    28  ----
    29  ·     distributed  false
    30  ·     vectorized   true
    31  scan  ·            ·
    32  ·     table        t@primary
    33  ·     spans        /ARRAY[1,NULL,NULL]-
    34  
    35  query TTT
    36  EXPLAIN SELECT x FROM t WHERE x > ARRAY[1, 3] AND x < ARRAY[1, 4, 10] ORDER BY x
    37  ----
    38  ·     distributed  false
    39  ·     vectorized   true
    40  scan  ·            ·
    41  ·     table        t@primary
    42  ·     spans        /ARRAY[1,3,NULL]-/ARRAY[1,4,10]
    43  
    44  query TTT
    45  EXPLAIN SELECT x FROM t WHERE x > ARRAY[1, 3] AND x < ARRAY[1, 4, 10] ORDER BY x DESC
    46  ----
    47  ·        distributed  false
    48  ·        vectorized   true
    49  revscan  ·            ·
    50  ·        table        t@primary
    51  ·        spans        /ARRAY[1,3,NULL]-/ARRAY[1,4,10]
    52  
    53  statement ok
    54  DROP TABLE t
    55  
    56  statement ok
    57  CREATE TABLE t (x INT, y INT[], z INT, INDEX i (x, y, z))
    58  
    59  query TTT
    60  EXPLAIN SELECT x, y, z FROM t WHERE x = 2 AND y < ARRAY[10] ORDER BY y
    61  ----
    62  ·     distributed  false
    63  ·     vectorized   true
    64  scan  ·            ·
    65  ·     table        t@i
    66  ·     spans        /2/!NULL-/2/ARRAY[10]
    67  
    68  # Test span generation on interleaved tables.
    69  statement ok
    70  CREATE TABLE parent (x INT, y INT[], PRIMARY KEY (x, y), FAMILY (x, y));
    71  CREATE TABLE child (x INT, y INT[], z INT[], PRIMARY KEY (x, y, z), FAMILY (x, y, z)) INTERLEAVE IN PARENT parent (x, y)
    72  
    73  query TTT
    74  EXPLAIN SELECT x, y FROM parent WHERE x > 1 AND y > ARRAY[1, NULL]
    75  ----
    76  ·     distributed  false
    77  ·     vectorized   true
    78  scan  ·            ·
    79  ·     table        parent@primary
    80  ·     spans        /2/ARRAY[1,NULL,NULL]-
    81  ·     filter       y > ARRAY[1,NULL]
    82  
    83  query TTT
    84  EXPLAIN SELECT y FROM parent WHERE x = 1 AND y = ARRAY[NULL, NULL]
    85  ----
    86  ·          distributed  false
    87  ·          vectorized   true
    88  render     ·            ·
    89   └── scan  ·            ·
    90  ·          table        parent@primary
    91  ·          spans        /1/ARRAY[NULL,NULL]-/1/ARRAY[NULL,NULL]/#
    92  
    93  query TTT
    94  EXPLAIN SELECT z FROM child WHERE x = 1 AND y = ARRAY[NULL, NULL]
    95  ----
    96  ·          distributed  false
    97  ·          vectorized   true
    98  render     ·            ·
    99   └── scan  ·            ·
   100  ·          table        child@primary
   101  ·          spans        /1/ARRAY[NULL,NULL]/#/56/1-/1/ARRAY[NULL,NULL]/#/56/2