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

     1  ##
     2  # Test a primary key with a collated string in first position (can get a key range).
     3  #
     4  # Danish collation chart: http://www.unicode.org/cldr/charts/30/collation/da.html
     5  
     6  statement ok
     7  CREATE TABLE t (
     8    a STRING COLLATE da,
     9    b INT,
    10    c BOOL,
    11    PRIMARY KEY (a, b)
    12  )
    13  
    14  statement ok
    15  INSERT INTO t VALUES
    16    ('A' COLLATE da, 1, TRUE),
    17    ('A' COLLATE da, 2, NULL),
    18    ('a' COLLATE da, 2, FALSE),
    19    ('a' COLLATE da, 3, TRUE),
    20    ('B' COLLATE da, 3, NULL),
    21    ('b' COLLATE da, 4, FALSE),
    22    ('ü' COLLATE da, 6, TRUE),
    23    ('ü' COLLATE da, 5, NULL),
    24    ('x' COLLATE da, 5, FALSE)
    25  
    26  statement ok
    27  CREATE UNIQUE INDEX ON t (b, a)
    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  x  5
    39  ü  5
    40  ü  6
    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  x
    52  5  ü
    53  6  ü
    54  
    55  query I
    56  SELECT COUNT (a) FROM t WHERE a = ('a' COLLATE da)
    57  ----
    58  2
    59  
    60  query I
    61  SELECT COUNT (a) FROM t WHERE a = ('y' COLLATE da)
    62  ----
    63  0
    64  
    65  query I
    66  SELECT COUNT (a) FROM t WHERE a > ('a' COLLATE da) AND a < ('c' COLLATE da)
    67  ----
    68  4
    69  
    70  # Update and try again.
    71  
    72  statement ok
    73  UPDATE t SET a = (a :: STRING || a :: STRING) COLLATE da
    74  
    75  query TI
    76  SELECT a, b FROM t ORDER BY a, b
    77  ----
    78  bb  4
    79  BB  3
    80  xx  5
    81  üü  5
    82  üü  6
    83  aa  2
    84  aa  3
    85  AA  1
    86  AA  2
    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  BB
    95  3  aa
    96  4  bb
    97  5  xx
    98  5  üü
    99  6  üü
   100  
   101  # Delete and try again
   102  
   103  statement ok
   104  DELETE FROM t WHERE a > ('a' COLLATE da) AND a < ('c' COLLATE da)
   105  
   106  query TI
   107  SELECT a, b FROM t ORDER BY a, b
   108  ----
   109  xx  5
   110  üü  5
   111  üü  6
   112  aa  2
   113  aa  3
   114  AA  1
   115  AA  2
   116  
   117  query IT
   118  SELECT b, a FROM t ORDER BY b, a
   119  ----
   120  1  AA
   121  2  aa
   122  2  AA
   123  3  aa
   124  5  xx
   125  5  üü
   126  6  üü