github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/sql/logictest/testdata/logic_test/collatedstring_index1 (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  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  x  5
    36  ü  5
    37  ü  6
    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  x
    49  5  ü
    50  6  ü
    51  
    52  query I
    53  SELECT COUNT (a) FROM t WHERE a = ('a' COLLATE da)
    54  ----
    55  2
    56  
    57  query I
    58  SELECT COUNT (a) FROM t WHERE a = ('y' COLLATE da)
    59  ----
    60  0
    61  
    62  query I
    63  SELECT COUNT (a) FROM t WHERE a > ('a' COLLATE da) AND a < ('c' COLLATE da)
    64  ----
    65  4
    66  
    67  # Create an index and try again.
    68  
    69  statement ok
    70  CREATE INDEX ON t (b, a) 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  x  5
    82  ü  5
    83  ü  6
    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  x
    95  5  ü
    96  6  ü
    97  
    98  query I
    99  SELECT COUNT (a) FROM t WHERE a = ('a' COLLATE da)
   100  ----
   101  2
   102  
   103  query I
   104  SELECT COUNT (a) FROM t WHERE a = ('y' COLLATE da)
   105  ----
   106  0
   107  
   108  query I
   109  SELECT COUNT (a) FROM t WHERE a > ('a' COLLATE da) AND a < ('c' COLLATE da)
   110  ----
   111  4
   112  
   113  # Update and try again.
   114  
   115  statement ok
   116  UPDATE t SET a = (a :: STRING || a :: STRING) COLLATE da
   117  
   118  query TI
   119  SELECT a, b FROM t ORDER BY a, b
   120  ----
   121  bb  4
   122  BB  3
   123  xx  5
   124  üü  5
   125  üü  6
   126  aa  2
   127  aa  3
   128  AA  1
   129  AA  2
   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  BB
   138  3  aa
   139  4  bb
   140  5  xx
   141  5  üü
   142  6  üü
   143  
   144  # Delete and try again
   145  
   146  statement ok
   147  DELETE FROM t WHERE a > ('a' COLLATE da) AND a < ('c' COLLATE da)
   148  
   149  query TI
   150  SELECT a, b FROM t ORDER BY a, b
   151  ----
   152  xx  5
   153  üü  5
   154  üü  6
   155  aa  2
   156  aa  3
   157  AA  1
   158  AA  2
   159  
   160  query IT
   161  SELECT b, a FROM t ORDER BY b, a
   162  ----
   163  1  AA
   164  2  aa
   165  2  AA
   166  3  aa
   167  5  xx
   168  5  üü
   169  6  üü