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

     1  ##
     2  # Test a primary key with a collated string in second position (cannot get a key range).
     3  #
     4  # German collation chart: http://www.unicode.org/cldr/charts/30/collation/de.html
     5  
     6  statement ok
     7  CREATE TABLE t (
     8    a STRING COLLATE de,
     9    b INT,
    10    c BOOL,
    11    PRIMARY KEY (b, a)
    12  )
    13  
    14  statement ok
    15  INSERT INTO t VALUES
    16    ('A' COLLATE de, 1, TRUE),
    17    ('A' COLLATE de, 2, NULL),
    18    ('a' COLLATE de, 2, FALSE),
    19    ('a' COLLATE de, 3, TRUE),
    20    ('B' COLLATE de, 3, NULL),
    21    ('b' COLLATE de, 4, FALSE),
    22    ('ü' COLLATE de, 6, TRUE),
    23    ('ü' COLLATE de, 5, NULL),
    24    ('x' COLLATE de, 5, FALSE)
    25  
    26  query TI
    27  SELECT a, b FROM t ORDER BY a, b
    28  ----
    29  a  2
    30  a  3
    31  A  1
    32  A  2
    33  b  4
    34  B  3
    35  ü  5
    36  ü  6
    37  x  5
    38  
    39  query IT
    40  SELECT b, a FROM t ORDER BY b, a
    41  ----
    42  1  A
    43  2  a
    44  2  A
    45  3  a
    46  3  B
    47  4  b
    48  5  ü
    49  5  x
    50  6  ü
    51  
    52  query I
    53  SELECT COUNT (a) FROM t WHERE a = ('a' COLLATE de)
    54  ----
    55  2
    56  
    57  query I
    58  SELECT COUNT (a) FROM t WHERE a = ('y' COLLATE de)
    59  ----
    60  0
    61  
    62  query I
    63  SELECT COUNT (a) FROM t WHERE a > ('a' COLLATE de) AND a < ('c' COLLATE de)
    64  ----
    65  4
    66  
    67  # Create an index and try again.
    68  
    69  statement ok
    70  CREATE INDEX ON t (a, b) STORING (c)
    71  
    72  query TI
    73  SELECT a, b FROM t ORDER BY a, b
    74  ----
    75  a  2
    76  a  3
    77  A  1
    78  A  2
    79  b  4
    80  B  3
    81  ü  5
    82  ü  6
    83  x  5
    84  
    85  query IT
    86  SELECT b, a FROM t ORDER BY b, a
    87  ----
    88  1  A
    89  2  a
    90  2  A
    91  3  a
    92  3  B
    93  4  b
    94  5  ü
    95  5  x
    96  6  ü
    97  
    98  query I
    99  SELECT COUNT (a) FROM t WHERE a = ('a' COLLATE de)
   100  ----
   101  2
   102  
   103  query I
   104  SELECT COUNT (a) FROM t WHERE a = ('y' COLLATE de)
   105  ----
   106  0
   107  
   108  query I
   109  SELECT COUNT (a) FROM t WHERE a > ('a' COLLATE de) AND a < ('c' COLLATE de)
   110  ----
   111  4
   112  
   113  # Update and try again.
   114  
   115  statement ok
   116  UPDATE t SET a = (a :: STRING || a :: STRING) COLLATE de
   117  
   118  query TI
   119  SELECT a, b FROM t ORDER BY a, b
   120  ----
   121  aa  2
   122  aa  3
   123  AA  1
   124  AA  2
   125  bb  4
   126  BB  3
   127  üü  5
   128  üü  6
   129  xx  5
   130  
   131  query IT
   132  SELECT b, a FROM t ORDER BY b, a
   133  ----
   134  1  AA
   135  2  aa
   136  2  AA
   137  3  aa
   138  3  BB
   139  4  bb
   140  5  üü
   141  5  xx
   142  6  üü
   143  
   144  # Delete and try again
   145  
   146  statement ok
   147  DELETE FROM t WHERE a > ('a' COLLATE de) AND a < ('c' COLLATE de)
   148  
   149  query TI
   150  SELECT a, b FROM t ORDER BY a, b
   151  ----
   152  üü  5
   153  üü  6
   154  xx  5
   155  
   156  query IT
   157  SELECT b, a FROM t ORDER BY b, a
   158  ----
   159  5  üü
   160  5  xx
   161  6  üü