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

     1  statement ok
     2  CREATE TABLE abcd (
     3    a INT PRIMARY KEY,
     4    b INT,
     5    c INT,
     6    d INT,
     7    INDEX b (b),
     8    INDEX cd (c,d),
     9    UNIQUE INDEX bcd (b,c,d)
    10  )
    11  
    12  statement ok
    13  INSERT INTO abcd VALUES (10, 11, 12, 13), (20, 21, 22, 23), (30, 31, 32, 33), (40, 41, 42, 43)
    14  
    15  # No hint
    16  query IIII rowsort
    17  SELECT * FROM abcd WHERE a >= 20 AND a <= 30
    18  ----
    19  20 21 22 23
    20  30 31 32 33
    21  
    22  # Force primary
    23  query IIII rowsort
    24  SELECT * FROM abcd@primary WHERE a >= 20 AND a <= 30
    25  ----
    26  20 21 22 23
    27  30 31 32 33
    28  
    29  # Force primary, reverse scan.
    30  query IIII rowsort
    31  SELECT * FROM abcd@{FORCE_INDEX=primary,DESC} WHERE a >= 20 AND a <= 30
    32  ----
    33  20 21 22 23
    34  30 31 32 33
    35  
    36  # Force index b
    37  query IIII rowsort
    38  SELECT * FROM abcd@b WHERE a >= 20 AND a <= 30
    39  ----
    40  20 21 22 23
    41  30 31 32 33
    42  
    43  # Force index b, reverse scan.
    44  query IIII rowsort
    45  SELECT * FROM abcd@{FORCE_INDEX=b,DESC} WHERE a >= 20 AND a <= 30
    46  ----
    47  20 21 22 23
    48  30 31 32 33
    49  
    50  # Force index cd
    51  query IIII rowsort
    52  SELECT * FROM abcd@cd WHERE a >= 20 AND a <= 30
    53  ----
    54  20 21 22 23
    55  30 31 32 33
    56  
    57  # Force index bcd
    58  query IIII rowsort
    59  SELECT * FROM abcd@bcd WHERE a >= 20 AND a <= 30
    60  ----
    61  20 21 22 23
    62  30 31 32 33
    63  
    64  # Force index b (covering)
    65  query I rowsort
    66  SELECT b FROM abcd@b WHERE a >= 20 AND a <= 30
    67  ----
    68  21
    69  31
    70  
    71  # Force index b (non-covering due to WHERE clause)
    72  query I rowsort
    73  SELECT b FROM abcd@b WHERE c >= 20 AND c <= 30
    74  ----
    75  21
    76  
    77  # No hint, should be using index cd
    78  query II rowsort
    79  SELECT c, d FROM abcd WHERE c >= 20 AND c < 40
    80  ----
    81  22 23
    82  32 33
    83  
    84  # Force primary index
    85  query II rowsort
    86  SELECT c, d FROM abcd@primary WHERE c >= 20 AND c < 40
    87  ----
    88  22 23
    89  32 33
    90  
    91  # Force index b
    92  query II rowsort
    93  SELECT c, d FROM abcd@b WHERE c >= 20 AND c < 40
    94  ----
    95  22 23
    96  32 33
    97  
    98  query error index \"badidx\" not found
    99  SELECT * FROM abcd@badidx
   100  
   101  query error index \"badidx\" not found
   102  SELECT * FROM abcd@{FORCE_INDEX=badidx}