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

     1  # LogicTest: 5node-default-configs
     2  
     3  statement ok
     4  CREATE TABLE xyz (
     5    id INT PRIMARY KEY,
     6    x INT,
     7    y INT,
     8    z INT
     9  )
    10  
    11  statement ok
    12  INSERT INTO xyz VALUES
    13    (1, 1, 1, NULL),
    14    (2, 1, 1, 2),
    15    (3, 1, 1, 2),
    16    (4, 1, 2, 1),
    17    (5, 2, 2, 3),
    18    (6, 4, 5, 6),
    19    (7, 4, 1, 6)
    20  
    21  statement ok
    22  CREATE TABLE abc (
    23    a STRING,
    24    b STRING,
    25    c STRING,
    26    PRIMARY KEY (a, b, c)
    27  )
    28  
    29  statement ok
    30  INSERT INTO abc VALUES
    31    ('1', '1', '1'),
    32    ('1', '1', '2'),
    33    ('1', '2', '2'),
    34    ('2', '3', '4'),
    35    ('3', '4', '5')
    36  
    37  statement ok
    38  ALTER TABLE xyz SPLIT AT VALUES (2), (4), (6), (7)
    39  
    40  statement ok
    41  ALTER TABLE xyz EXPERIMENTAL_RELOCATE VALUES
    42    (ARRAY[1], 0),
    43    (ARRAY[2], 2),
    44    (ARRAY[3], 4),
    45    (ARRAY[4], 6),
    46    (ARRAY[5], 7)
    47  
    48  statement ok
    49  ALTER TABLE abc SPLIT AT VALUES
    50    (NULL, NULL, NULL),
    51    ('1', '1', '2'),
    52    ('1', '2', '2'),
    53    ('2', '3', '4'),
    54    ('3', '4', '5')
    55  
    56  statement ok
    57  ALTER TABLE abc EXPERIMENTAL_RELOCATE VALUES
    58    (ARRAY[1], NULL, NULL, NULL),
    59    (ARRAY[2], '1', '1', '2'),
    60    (ARRAY[3], '1', '2', '2'),
    61    (ARRAY[4], '2', '3', '4'),
    62    (ARRAY[5], '3', '4', '5')
    63  
    64  query TTTI colnames,rowsort
    65  SELECT start_key, end_key, replicas, lease_holder from [SHOW RANGES FROM TABLE xyz]
    66  ----
    67  start_key  end_key  replicas  lease_holder
    68  NULL       /2       {1}       1
    69  /2         /4       {2}       2
    70  /4         /6       {3}       3
    71  /6         /7       {4}       4
    72  /7         NULL     {5}       5
    73  
    74  query TTTI colnames,rowsort
    75  SELECT start_key, end_key, replicas, lease_holder from [SHOW RANGES FROM TABLE abc]
    76  ----
    77  start_key        end_key          replicas  lease_holder
    78  NULL             /NULL/NULL/NULL  {5}       5
    79  /NULL/NULL/NULL  /"1"/"1"/"2"     {1}       1
    80  /"1"/"1"/"2"     /"1"/"2"/"2"     {2}       2
    81  /"1"/"2"/"2"     /"2"/"3"/"4"     {3}       3
    82  /"2"/"3"/"4"     /"3"/"4"/"5"     {4}       4
    83  /"3"/"4"/"5"     NULL             {5}       5
    84  
    85  query III rowsort
    86  SELECT DISTINCT ON (x,y,z) x, y, z FROM xyz
    87  ----
    88  1 1 NULL
    89  1 1 2
    90  1 2 1
    91  2 2 3
    92  4 5 6
    93  4 1 6
    94  
    95  query III partialsort(1)
    96  SELECT DISTINCT ON (x,y,z) x, y, z FROM xyz ORDER BY x
    97  ----
    98  1 1 NULL
    99  1 1 2
   100  1 2 1
   101  2 2 3
   102  4 5 6
   103  4 1 6
   104  
   105  query II
   106  SELECT DISTINCT ON (y) x, y FROM xyz ORDER BY y, x
   107  ----
   108  1 1
   109  1 2
   110  4 5
   111  
   112  query TTT rowsort
   113  SELECT DISTINCT ON (a,b,c) a, b, c FROM abc
   114  ----
   115  1  1  1
   116  1  1  2
   117  1  2  2
   118  2  3  4
   119  3  4  5
   120  
   121  query TT
   122  SELECT DISTINCT ON (a, b) a, b FROM abc ORDER BY a, b, c
   123  ----
   124  1 1
   125  1 2
   126  2 3
   127  3 4