github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/sql/logictest/testdata/logic_test/collatedstring_uniqueindex2 (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  statement ok
    27  CREATE UNIQUE INDEX ON t (a, b)
    28  
    29  query TI
    30  SELECT a, b FROM t ORDER BY a, b
    31  ----
    32  a  2
    33  a  3
    34  A  1
    35  A  2
    36  b  4
    37  B  3
    38  ü  5
    39  ü  6
    40  x  5
    41  
    42  query IT
    43  SELECT b, a FROM t ORDER BY b, a
    44  ----
    45  1  A
    46  2  a
    47  2  A
    48  3  a
    49  3  B
    50  4  b
    51  5  ü
    52  5  x
    53  6  ü
    54  
    55  query I
    56  SELECT COUNT (a) FROM t WHERE a = ('a' COLLATE de)
    57  ----
    58  2
    59  
    60  query I
    61  SELECT COUNT (a) FROM t WHERE a = ('y' COLLATE de)
    62  ----
    63  0
    64  
    65  query I
    66  SELECT COUNT (a) FROM t WHERE a > ('a' COLLATE de) AND a < ('c' COLLATE de)
    67  ----
    68  4
    69  
    70  # Update and try again.
    71  
    72  statement ok
    73  UPDATE t SET a = (a :: STRING || a :: STRING) COLLATE de
    74  
    75  query TI
    76  SELECT a, b FROM t ORDER BY a, b
    77  ----
    78  aa  2
    79  aa  3
    80  AA  1
    81  AA  2
    82  bb  4
    83  BB  3
    84  üü  5
    85  üü  6
    86  xx  5
    87  
    88  query IT
    89  SELECT b, a FROM t ORDER BY b, a
    90  ----
    91  1  AA
    92  2  aa
    93  2  AA
    94  3  aa
    95  3  BB
    96  4  bb
    97  5  üü
    98  5  xx
    99  6  üü
   100  
   101  # Delete and try again
   102  
   103  statement ok
   104  DELETE FROM t WHERE a > ('a' COLLATE de) AND a < ('c' COLLATE de)
   105  
   106  query TI
   107  SELECT a, b FROM t ORDER BY a, b
   108  ----
   109  üü  5
   110  üü  6
   111  xx  5
   112  
   113  query IT
   114  SELECT b, a FROM t ORDER BY b, a
   115  ----
   116  5  üü
   117  5  xx
   118  6  üü