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

     1  # LogicTest: 5node
     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  CREATE TABLE abc (
    13    a STRING,
    14    b STRING,
    15    c STRING,
    16    PRIMARY KEY (a, b, c)
    17  )
    18  
    19  statement ok
    20  ALTER TABLE xyz SPLIT AT VALUES (2), (4), (6), (7)
    21  
    22  statement ok
    23  ALTER TABLE xyz EXPERIMENTAL_RELOCATE VALUES
    24    (ARRAY[1], 0),
    25    (ARRAY[2], 2),
    26    (ARRAY[3], 4),
    27    (ARRAY[4], 6),
    28    (ARRAY[5], 7)
    29  
    30  statement ok
    31  ALTER TABLE abc SPLIT AT VALUES
    32    (NULL, NULL, NULL),
    33    ('1', '1', '2'),
    34    ('1', '2', '2'),
    35    ('2', '3', '4'),
    36    ('3', '4', '5')
    37  
    38  statement ok
    39  ALTER TABLE abc EXPERIMENTAL_RELOCATE VALUES
    40    (ARRAY[1], NULL, NULL, NULL),
    41    (ARRAY[2], '1', '1', '2'),
    42    (ARRAY[3], '1', '2', '2'),
    43    (ARRAY[4], '2', '3', '4'),
    44    (ARRAY[5], '3', '4', '5')
    45  
    46  query TTTI colnames
    47  SELECT start_key, end_key, replicas, lease_holder from [SHOW RANGES FROM TABLE xyz]
    48  ----
    49  start_key  end_key  replicas  lease_holder
    50  NULL       /2       {1}       1
    51  /2         /4       {2}       2
    52  /4         /6       {3}       3
    53  /6         /7       {4}       4
    54  /7         NULL     {5}       5
    55  
    56  query TTTI colnames,rowsort
    57  SELECT start_key, end_key, replicas, lease_holder from [SHOW RANGES FROM TABLE abc]
    58  ----
    59  start_key        end_key          replicas  lease_holder
    60  NULL             /NULL/NULL/NULL  {5}       5
    61  /NULL/NULL/NULL  /"1"/"1"/"2"     {1}       1
    62  /"1"/"1"/"2"     /"1"/"2"/"2"     {2}       2
    63  /"1"/"2"/"2"     /"2"/"3"/"4"     {3}       3
    64  /"2"/"3"/"4"     /"3"/"4"/"5"     {4}       4
    65  /"3"/"4"/"5"     NULL             {5}       5
    66  
    67  query TTTTT
    68  EXPLAIN (VERBOSE) SELECT DISTINCT ON (x,y,z) x, y, z FROM xyz
    69  ----
    70  ·          distributed  true         ·          ·
    71  ·          vectorized   true         ·          ·
    72  distinct   ·            ·            (x, y, z)  ·
    73   │         distinct on  x, y, z      ·          ·
    74   └── scan  ·            ·            (x, y, z)  ·
    75  ·          table        xyz@primary  ·          ·
    76  ·          spans        FULL SCAN    ·          ·
    77  
    78  query T
    79  SELECT url FROM [EXPLAIN (DISTSQL) SELECT DISTINCT ON (x,y,z) x, y, z FROM xyz]
    80  ----
    81  https://cockroachdb.github.io/distsqlplan/decode.html#eJyslF1vmzAUhu_3K6xz1UpGYCAf5YqpzSSkLOmSXEyquGBwVCFRzGwj5UP57xNQpUvUGC_sLgZeP-_jWOcA8ncBAaxn89njhtSiIN9Wy-_kZfbzef41WpC7p2i9Wf-Y35P3T5p1tHjckOWC3G0p2VGyvyfvP7rwdrePgULJM1wkbygheAEGFFyg4AEFHyiMIKZQCZ6ilFw0nxzaQJRtIXAo5GVVq-ZxTCHlAiE4gMpVgRDAJvlV4AqTDIXtAIUMVZIXLaYS-VsiduF2twcK6yopZUAsuyEvaxWQ0KWhR0Mf4iMFXqsPiFTJK0LAjtS8yFMuVV6myh6dtwgZbUFXKe5VysfmdclFhgKzs73jo64Hc_6xiHdWhJmfOzM4d9u1bP_Gk--pcjIeDzp511zYNRH2rbbQLcI9VU7Ck0HCnrmwZyI8ttpCtwj3VDkJTwcJ--bCvonwxLpRt6fISffhv42STygrlBUvJV6MlM93dppRg9krdnNJ8lqk-Cx42mK65bLNtQ8ylKp7y7pFVHavmoJ_h5k27J6F2WXY1ZN70J427evD_pDeI214rCePh5An2vBUT54OIT_o_yun55roL9klOz5--RMAAP__gO7S5g==
    82  
    83  # Ensure that ordering propagates past local DISTINCT processors.
    84  query TTTTT
    85  EXPLAIN (VERBOSE) SELECT DISTINCT ON (x,y,z) x, y, z FROM xyz ORDER BY x
    86  ----
    87  ·               distributed  true         ·          ·
    88  ·               vectorized   true         ·          ·
    89  distinct        ·            ·            (x, y, z)  +x
    90   │              distinct on  x, y, z      ·          ·
    91   │              order key    x            ·          ·
    92   └── sort       ·            ·            (x, y, z)  +x
    93        │         order        +x           ·          ·
    94        └── scan  ·            ·            (x, y, z)  ·
    95  ·               table        xyz@primary  ·          ·
    96  ·               spans        FULL SCAN    ·          ·
    97  
    98  query T
    99  SELECT url FROM [EXPLAIN (DISTSQL) SELECT DISTINCT ON (x,y,z) x, y, z FROM xyz ORDER BY x]
   100  ----
   101  https://cockroachdb.github.io/distsqlplan/decode.html#eJy0lU2P2jwUhffvr7DuakavUXBivrJKO1AJicIUWLQasUgTaxQpE6e2IwUQ_73Kh5RCBzsT6C5Ocu4597lX8hHkrxhc2MwWs6ctykSMvqxXX9HL7Pvz4tN8iR6m8812823xiOpfivN8-bRFqyV6yDHaY3R4RPVDJc73B7RaT2dr9PkHyneAIeEhW_pvTIL7AgQw2IDBAQwUMAxghyEVPGBSclH8ciwF8zAHt48hStJMFa93GAIuGLhHUJGKGbiw9X_GbM38kAmrDxhCpvwoLm1SEb35Yu_l-wNg2KR-Il3UswrnVaZc5NnYc7BHYXfCwDPVmEjlvzJwyQm3D7LhQjFhDc4zeOT_q-Xtj5SfRlJFSaAs0r90wGUjRVciZIKFLvLIVVPnqmnjxas673WiTfZX7x9KRs-SkfZjJy3Gbtk9i3YcvCFKPfhh18Ebyjd4yR0Hb7fHa7fBS3slgC54DVFqvKOueA3lG7z2HfE67fE6bfAOeyWALngNUWq84654DeUbvM4d8dL2eGkbvKNeR7iGIDXcSVe4hvINXPqP7oR3TNdMpjyR7MzxWuV-cWGw8JVVF4zkmQjYs-BBaVMdV6WufBEyqaqvpDrMk-pTEfBPMdGKbb3Y1oqdMzG5FDv62EO9NdWqB3rxQCs2OA9vaXqkFY_1zmOteKIXT26JTQw7Zloy_ZYRw5qRm_aMGBaNGsz1m0YMq0b0u3aZfXf673cAAAD__1cN74Y=
   102  
   103  # Ensure that even with more ordering columns, ordering propagates past local
   104  # DISTINCT processors.
   105  query TTTTT
   106  EXPLAIN (VERBOSE) SELECT DISTINCT ON (y) x, y FROM xyz ORDER BY y, x
   107  ----
   108  ·               distributed  true         ·       ·
   109  ·               vectorized   true         ·       ·
   110  distinct        ·            ·            (x, y)  +y
   111   │              distinct on  y            ·       ·
   112   │              order key    y            ·       ·
   113   └── sort       ·            ·            (x, y)  +y,+x
   114        │         order        +y,+x        ·       ·
   115        └── scan  ·            ·            (x, y)  ·
   116  ·               table        xyz@primary  ·       ·
   117  ·               spans        FULL SCAN    ·       ·
   118  
   119  query T
   120  SELECT url FROM [EXPLAIN (DISTSQL) SELECT DISTINCT ON (y) x, y FROM xyz ORDER BY y, x]
   121  ----
   122  https://cockroachdb.github.io/distsqlplan/decode.html#eJyslV9v2jAUxd_3Kaz71KpGwU6gkCe2wiQkBh3wsKniISNWFYnGme1ISRHffcofKYMttml4dJKTc-7vHiVHkL8P4MNmtpg9bVEqDujrevUNvcx-PC8-z5fobjrfbDffF_eofqQ4z5dPW7Raorv8HmUY5ZUmy9_Raj2drdGXnyjHKNsBhpiHbBm8MQn-CxDAQAGDCxg8wDCAHYZE8D2TkovikWMpmIcZ-H0MUZykqri8w7DngoF_BBWpAwMftsGvA1uzIGTC6QOGkKkgOpQ2iYjeApFPsvwdMGySIJY-6jmF8ypVPppQPHFhd8LAU9U4SBW8MvDJCdun2HChmHAG5wEm9AFPyEOrBb3GYhpJFcV75ZD-pUsxkAiZYGExVKud22rXuPDqPW1zaHP9M71lLu8sF7HfN7HYt0N7jveRjRty1Bsfdtm4waIhS26ycWpPltqQ9Xrl-FeTNeSoyT52IWuwaMjSm5B17cm6NmSHvXL8q8kactRkR13IGiwasu5NyHr2ZD0bso-9j3A1pKi5jrtwNVg0XL2bf_3_Y7dmMuGxZGdebW_uF78FFr6y6lcieSr27FnwfWlTHVelrrwQMqmqu6Q6zOPqVhHwbzHRiqleTLVi90xMLsWuPvZQb-1p1QO9eKAVG5yHXYZ-1IpHeueRVjzWi8ddYhNDx0wl07eMGGpGOvWMGIrmGcz1TSOGqhF91y6z706f_gQAAP__dR3oLw==
   123  
   124  # Distinct processors elided becaue of strong key.
   125  query TTTTT
   126  EXPLAIN (VERBOSE) SELECT DISTINCT ON (a,b,c) a, b, c FROM abc
   127  ----
   128  ·     distributed  true         ·          ·
   129  ·     vectorized   true         ·          ·
   130  scan  ·            ·            (a, b, c)  ·
   131  ·     table        abc@primary  ·          ·
   132  ·     spans        FULL SCAN    ·          ·
   133  
   134  query T
   135  SELECT url FROM [EXPLAIN (DISTSQL) SELECT DISTINCT ON (a,b,c) a, b, c FROM abc]
   136  ----
   137  https://cockroachdb.github.io/distsqlplan/decode.html#eJykk0-L2zAQxe_9FOKddkHG8Z_04FPLNgWD62yTFAobHxRr2Aa8livJ0BLy3YvlpfnTtCjNZfCT5vn9GEY7mO8NMixnxexhxXrdsI-L-Sf2NPv6WLzPS3b3IV-ulp-Le_baMui8fFixecnuBGcbzup79voxmsWmrsDRKkmleCGD7AkROGJwJOBIwTFFxdFpVZMxSg8tO2fI5Q9kE45t2_V2OK44aqUJ2Q52axtChpXYNLQgIUmHw38lWbFtXEynty9C_3wnNjU4lp1oTcbC8ktRHEoQrhGt8bvGa6Dac6jeHjKNFc-ELNrzv3AdcPpWaUma5AlKtb9AXqpAdeH0rPFydHwSHfmPJPYZyR8jCI7FVVOJ_dGSa9DiY7RRJK6m3miJP1rqh3bGERyLcI2pN1rqjzbxQAvOlpyJVrKIKfuN9H9s9wWiBZlOtYa8lncybD_JZxqfilG9rulRq9rFjHLufO5AkrHjbTSKvHVXDvDYHP3T_PbEPDk3x7ckJ7eY01vM06vM1f7NrwAAAP__f9DXkQ==
   138  
   139  query TTTTT
   140  EXPLAIN (VERBOSE) SELECT DISTINCT ON (a, b) a, b FROM abc ORDER BY a, b, c
   141  ----
   142  ·               distributed  true         ·          ·
   143  ·               vectorized   true         ·          ·
   144  render          ·            ·            (a, b)     +a,+b
   145   │              render 0     a            ·          ·
   146   │              render 1     b            ·          ·
   147   └── distinct   ·            ·            (a, b, c)  +a,+b
   148        │         distinct on  a, b         ·          ·
   149        │         order key    a, b         ·          ·
   150        └── scan  ·            ·            (a, b, c)  +a,+b,+c
   151  ·               table        abc@primary  ·          ·
   152  ·               spans        FULL SCAN    ·          ·
   153  
   154  query T
   155  SELECT url FROM [EXPLAIN (DISTSQL) SELECT DISTINCT ON (a, b) a, b FROM abc ORDER BY a, b, c]
   156  ----
   157  https://cockroachdb.github.io/distsqlplan/decode.html#eJy0lFFvmzAUhd_3K6z71KqOEgNJU56yNZkUiUFHMmlTw4MDVodEMbONtCnKf5-AiSRsMa6SvVi6F47P4bvo7kD-yMCF1cJbPK5RKTL0MQw-oefF1yfv_dJHN_Plar367N2iP69U9dJ_XKPARzcUo-0tqs5GRrcxCsL5IkQfvtVtjOIIMOQ8YT59ZRLcZyCAwQIMNmBwAMMYIgyF4DGTkovqlV0tWCY_wR1hSPOiVFU7whBzwcDdgUpVxsCFNd1mLGQ0YWJY3ZswRdOstilE-krFrxndxoBhVdBcumjof_G8wzEYboBsoD2tDUC0x8BLdfCUir4wcMkem-eap1KleayGk9NQM4Jn1bcHImGCJS5qGuc8rbOeByveXNX1ucMz6w7P7DuI9rqAZGSUEENQqr649klcYj46y2R0f41qcFy8aXo90Vo491ecnmWOw34LDusYR1PY9ekY4-iJ1uKYXhGHbY7DMcPR-fbBcTHcwNgYR0-0FsfDFXE45jhGBjgGnUWHaJ4ggrj6zoQhhZ5ELYXxf9pw__AMmSx4LtmJ47mbR9XaY8kLa9al5KWI2ZPgcW3TlEGtqxsJk6p5SppimTePqoDHYqIVWydi0hVbeucea1urdvRi55LcY614oneeXOJ8rxVP9c7TS5wf9LMa9fwm-p-s6x3t3_0OAAD__zbeA6s=