github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/sql/opt/exec/execbuilder/testdata/distinct_on (about) 1 # LogicTest: local 2 3 statement ok 4 CREATE TABLE xyz ( 5 x INT, 6 y INT, 7 z INT, 8 pk1 INT, 9 pk2 INT, 10 PRIMARY KEY (pk1, pk2) 11 ) 12 13 statement ok 14 CREATE TABLE abc ( 15 a STRING, 16 b STRING, 17 c STRING, 18 PRIMARY KEY (a, b, c) 19 ) 20 21 ################## 22 # Simple queries # 23 ################## 24 25 # 3/3 columns 26 27 query TTTTT 28 EXPLAIN (VERBOSE) SELECT DISTINCT ON (x, y, z) x, y, z FROM xyz 29 ---- 30 · distributed false · · 31 · vectorized true · · 32 distinct · · (x, y, z) · 33 │ distinct on x, y, z · · 34 └── scan · · (x, y, z) · 35 · table xyz@primary · · 36 · spans FULL SCAN · · 37 38 query TTTTT 39 EXPLAIN (VERBOSE) SELECT DISTINCT ON (z, x, y) x FROM xyz 40 ---- 41 · distributed false · · 42 · vectorized true · · 43 render · · (x) · 44 │ render 0 x · · 45 └── distinct · · (x, y, z) · 46 │ distinct on x, y, z · · 47 └── scan · · (x, y, z) · 48 · table xyz@primary · · 49 · spans FULL SCAN · · 50 51 query TTTTT 52 EXPLAIN (VERBOSE) SELECT DISTINCT ON (b, c, a) a, c, b FROM abc 53 ---- 54 · distributed false · · 55 · vectorized true · · 56 render · · (a, c, b) · 57 │ render 0 a · · 58 │ render 1 c · · 59 │ render 2 b · · 60 └── scan · · (a, b, c) · 61 · table abc@primary · · 62 · spans FULL SCAN · · 63 64 # Distinct node should be elided since we have a strong key. 65 query TTTTT 66 EXPLAIN (VERBOSE) SELECT DISTINCT ON (b, c, a) a FROM abc 67 ---- 68 · distributed false · · 69 · vectorized true · · 70 scan · · (a) · 71 · table abc@primary · · 72 · spans FULL SCAN · · 73 74 # Distinct node should be elided since we have a strong key. 75 query TTTTT 76 EXPLAIN (VERBOSE) SELECT DISTINCT ON (c, a, b) b FROM abc ORDER BY b 77 ---- 78 · distributed false · · 79 · vectorized true · · 80 sort · · (b) +b 81 │ order +b · · 82 └── scan · · (b) · 83 · table abc@primary · · 84 · spans FULL SCAN · · 85 86 87 # 2/3 columns 88 89 query TTTTT 90 EXPLAIN (VERBOSE) SELECT DISTINCT ON (x, y) y, x FROM xyz 91 ---- 92 · distributed false · · 93 · vectorized true · · 94 render · · (y, x) · 95 │ render 0 y · · 96 │ render 1 x · · 97 └── distinct · · (x, y) · 98 │ distinct on x, y · · 99 └── scan · · (x, y) · 100 · table xyz@primary · · 101 · spans FULL SCAN · · 102 103 query TTTTT 104 EXPLAIN (VERBOSE) SELECT DISTINCT ON (y, x) x FROM xyz 105 ---- 106 · distributed false · · 107 · vectorized true · · 108 render · · (x) · 109 │ render 0 x · · 110 └── distinct · · (x, y) · 111 │ distinct on x, y · · 112 └── scan · · (x, y) · 113 · table xyz@primary · · 114 · spans FULL SCAN · · 115 116 query TTTTT 117 EXPLAIN (VERBOSE) SELECT DISTINCT ON (y, x, x, y, x) x, y FROM xyz 118 ---- 119 · distributed false · · 120 · vectorized true · · 121 distinct · · (x, y) · 122 │ distinct on x, y · · 123 └── scan · · (x, y) · 124 · table xyz@primary · · 125 · spans FULL SCAN · · 126 127 query TTTTT 128 EXPLAIN (VERBOSE) SELECT DISTINCT ON(pk1, x) pk1, x FROM xyz ORDER BY pk1 129 ---- 130 · distributed false · · 131 · vectorized true · · 132 render · · (pk1, x) · 133 │ render 0 pk1 · · 134 │ render 1 x · · 135 └── distinct · · (x, pk1) +pk1 136 │ distinct on x, pk1 · · 137 │ order key pk1 · · 138 └── scan · · (x, pk1) +pk1 139 · table xyz@primary · · 140 · spans FULL SCAN · · 141 142 query TTTTT 143 EXPLAIN (VERBOSE) SELECT DISTINCT ON (a, c) a, b FROM abc 144 ---- 145 · distributed false · · 146 · vectorized true · · 147 render · · (a, b) · 148 │ render 0 a · · 149 │ render 1 b · · 150 └── distinct · · (a, b, c) · 151 │ distinct on a, c · · 152 │ order key a · · 153 └── scan · · (a, b, c) +a 154 · table abc@primary · · 155 · spans FULL SCAN · · 156 157 query TTTTT 158 EXPLAIN (VERBOSE) SELECT DISTINCT ON (c, a) b, c, a FROM abc 159 ---- 160 · distributed false · · 161 · vectorized true · · 162 render · · (b, c, a) · 163 │ render 0 b · · 164 │ render 1 c · · 165 │ render 2 a · · 166 └── distinct · · (a, b, c) · 167 │ distinct on a, c · · 168 │ order key a · · 169 └── scan · · (a, b, c) +a 170 · table abc@primary · · 171 · spans FULL SCAN · · 172 173 174 # 1/3 columns 175 176 # Check that distinct propagates the smaller, tighter key (pk1) as opposed to 177 # the original key (pk1, pk2). 178 query TTTTT 179 EXPLAIN (VERBOSE) SELECT DISTINCT ON (pk1) pk1, pk2 FROM xyz 180 ---- 181 · distributed false · · 182 · vectorized true · · 183 distinct · · (pk1, pk2) · 184 │ distinct on pk1 · · 185 │ order key pk1 · · 186 └── scan · · (pk1, pk2) +pk1 187 · table xyz@primary · · 188 · spans FULL SCAN · · 189 190 # Ensure the distinctNode advertises an a+ ordering. 191 # TODO(radu): set the ordering in the render node to fix this. 192 query TTTTT 193 EXPLAIN (VERBOSE) SELECT DISTINCT ON (a) a, c FROM abc ORDER BY a, c DESC, b 194 ---- 195 · distributed false · · 196 · vectorized true · · 197 render · · (a, c) +a 198 │ render 0 a · · 199 │ render 1 c · · 200 └── distinct · · (a, b, c) +a 201 │ distinct on a · · 202 │ order key a · · 203 └── sort · · (a, b, c) +a,-c,+b 204 │ order +a,-c,+b · · 205 │ already ordered +a · · 206 └── scan · · (a, b, c) +a 207 · table abc@primary · · 208 · spans FULL SCAN · · 209 210 ################# 211 # With ORDER BY # 212 ################# 213 214 query TTTTT 215 EXPLAIN (VERBOSE) SELECT DISTINCT ON (x) x FROM xyz ORDER BY x DESC 216 ---- 217 · distributed false · · 218 · vectorized true · · 219 sort · · (x) -x 220 │ order -x · · 221 └── distinct · · (x) · 222 │ distinct on x · · 223 └── scan · · (x) · 224 · table xyz@primary · · 225 · spans FULL SCAN · · 226 227 query TTTTT 228 EXPLAIN (VERBOSE) SELECT DISTINCT ON (x, z) y, z, x FROM xyz ORDER BY z 229 ---- 230 · distributed false · · 231 · vectorized true · · 232 render · · (y, z, x) · 233 │ render 0 y · · 234 │ render 1 z · · 235 │ render 2 x · · 236 └── distinct · · (x, y, z) +z 237 │ distinct on x, z · · 238 │ order key z · · 239 └── sort · · (x, y, z) +z 240 │ order +z · · 241 └── scan · · (x, y, z) · 242 · table xyz@primary · · 243 · spans FULL SCAN · · 244 245 query TTTTT 246 EXPLAIN (VERBOSE) SELECT DISTINCT ON (x) y, z, x FROM xyz ORDER BY x ASC, z DESC, y DESC 247 ---- 248 · distributed false · · 249 · vectorized true · · 250 render · · (y, z, x) · 251 │ render 0 y · · 252 │ render 1 z · · 253 │ render 2 x · · 254 └── distinct · · (x, y, z) +x 255 │ distinct on x · · 256 │ order key x · · 257 └── sort · · (x, y, z) +x,-z,-y 258 │ order +x,-z,-y · · 259 └── scan · · (x, y, z) · 260 · table xyz@primary · · 261 · spans FULL SCAN · · 262 263 ##################### 264 # With aggregations # 265 ##################### 266 267 query TTTTT 268 EXPLAIN (VERBOSE) SELECT DISTINCT ON (max(y)) max(x) FROM xyz 269 ---- 270 · distributed false · · 271 · vectorized true · · 272 group · · (max) · 273 │ aggregate 0 max(x) · · 274 │ scalar · · · 275 └── scan · · (x) · 276 · table xyz@primary · · 277 · spans FULL SCAN · · 278 279 query TTTTT 280 EXPLAIN (VERBOSE) SELECT DISTINCT ON(min(a), max(b), min(c)) max(a) FROM abc 281 ---- 282 · distributed false · · 283 · vectorized true · · 284 group · · (max) · 285 │ aggregate 0 any_not_null(a) · · 286 │ scalar · · · 287 └── revscan · · (a) · 288 · table abc@primary · · 289 · spans LIMITED SCAN · · 290 · limit 1 · · 291 292 ################# 293 # With GROUP BY # 294 ################# 295 296 # We can elide the DISTINCT ON since its key is equivalent to the group key. 297 query TTTTT 298 EXPLAIN (VERBOSE) SELECT DISTINCT ON(y) min(x) FROM xyz GROUP BY y 299 ---- 300 · distributed false · · 301 · vectorized true · · 302 render · · (min) · 303 │ render 0 min · · 304 └── group · · (y, min) · 305 │ aggregate 0 y · · 306 │ aggregate 1 min(x) · · 307 │ group by y · · 308 └── scan · · (x, y) · 309 · table xyz@primary · · 310 · spans FULL SCAN · · 311 312 query TTTTT 313 EXPLAIN (VERBOSE) SELECT DISTINCT ON(min(x)) min(x) FROM xyz GROUP BY y HAVING min(x) = 1 314 ---- 315 · distributed false · · 316 · vectorized true · · 317 render · · (min) · 318 │ render 0 min · · 319 └── limit · · (y, min) · 320 │ count 1 · · 321 └── filter · · (y, min) · 322 │ filter min = 1 · · 323 └── group · · (y, min) · 324 │ aggregate 0 y · · 325 │ aggregate 1 min(x) · · 326 │ group by y · · 327 └── scan · · (x, y) · 328 · table xyz@primary · · 329 · spans FULL SCAN · · 330 331 ######################### 332 # With window functions # 333 ######################### 334 335 query TTTTT 336 EXPLAIN (VERBOSE) SELECT DISTINCT ON(row_number() OVER()) y FROM xyz 337 ---- 338 · distributed false · · 339 · vectorized true · · 340 render · · (y) · 341 │ render 0 y · · 342 └── distinct · · (y, row_number) · 343 │ distinct on row_number · · 344 └── window · · (y, row_number) · 345 │ window 0 row_number() OVER (RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) · · 346 │ render 1 row_number() OVER (RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) · · 347 └── scan · · (y) · 348 · table xyz@primary · · 349 · spans FULL SCAN · · 350 351 ########################### 352 # With ordinal references # 353 ########################### 354 355 query TTTTT 356 EXPLAIN (VERBOSE) SELECT DISTINCT ON (1) x, y, z FROM xyz 357 ---- 358 · distributed false · · 359 · vectorized true · · 360 distinct · · (x, y, z) · 361 │ distinct on x · · 362 └── scan · · (x, y, z) · 363 · table xyz@primary · · 364 · spans FULL SCAN · · 365 366 # Distinct node elided because of strong key. 367 query TTTTT 368 EXPLAIN (VERBOSE) SELECT DISTINCT ON (1,2,3) a, b, c FROM abc 369 ---- 370 · distributed false · · 371 · vectorized true · · 372 scan · · (a, b, c) · 373 · table abc@primary · · 374 · spans FULL SCAN · · 375 376 ######################### 377 # With alias references # 378 ######################### 379 380 # This should priortize alias (use 'x' as the key). 381 query TTTTT 382 EXPLAIN (VERBOSE) SELECT DISTINCT ON(y) x AS y, y AS x FROM xyz 383 ---- 384 · distributed false · · 385 · vectorized true · · 386 distinct · · (y, x) · 387 │ distinct on y · · 388 └── scan · · (y, x) · 389 · table xyz@primary · · 390 · spans FULL SCAN · · 391 392 # Ignores the alias. 393 query TTTTT 394 EXPLAIN (VERBOSE) SELECT DISTINCT ON(x) x AS y FROM xyz 395 ---- 396 · distributed false · · 397 · vectorized true · · 398 distinct · · (y) · 399 │ distinct on y · · 400 └── scan · · (y) · 401 · table xyz@primary · · 402 · spans FULL SCAN · · 403 404 ################################## 405 # With nested parentheses/tuples # 406 ################################## 407 408 query TTTTT 409 EXPLAIN (VERBOSE) SELECT DISTINCT ON(((x)), (x, y)) x, y FROM xyz 410 ---- 411 · distributed false · · 412 · vectorized true · · 413 distinct · · (x, y) · 414 │ distinct on x, y · · 415 └── scan · · (x, y) · 416 · table xyz@primary · · 417 · spans FULL SCAN · · 418 419 ################################ 420 # Hybrid PK and non-PK queries # 421 ################################ 422 423 # Distinct elided because of strong key presence. 424 query TTTTT 425 EXPLAIN (VERBOSE) SELECT DISTINCT ON(pk1, pk2, x, y) x, y, z FROM xyz ORDER BY x, y 426 ---- 427 · distributed false · · 428 · vectorized true · · 429 sort · · (x, y, z) +x,+y 430 │ order +x,+y · · 431 └── scan · · (x, y, z) · 432 · table xyz@primary · · 433 · spans FULL SCAN · · 434 435 query TTTTT 436 EXPLAIN (VERBOSE) SELECT DISTINCT ON (x, y, z) pk1 FROM xyz ORDER BY x 437 ---- 438 · distributed false · · 439 · vectorized true · · 440 render · · (pk1) · 441 │ render 0 pk1 · · 442 └── distinct · · (x, y, z, pk1) +x 443 │ distinct on x, y, z · · 444 │ order key x · · 445 └── sort · · (x, y, z, pk1) +x 446 │ order +x · · 447 └── scan · · (x, y, z, pk1) · 448 · table xyz@primary · · 449 · spans FULL SCAN · · 450 451 # Regression tests for #34112: distinct on constant column. 452 query TTTTT 453 EXPLAIN (VERBOSE) SELECT DISTINCT ON (x) x, y FROM xyz WHERE x = 1 ORDER BY x, y 454 ---- 455 · distributed false · · 456 · vectorized true · · 457 limit · · (x, y) +y 458 │ count 1 · · 459 └── sort · · (x, y) +y 460 │ order +y · · 461 └── scan · · (x, y) · 462 · table xyz@primary · · 463 · spans FULL SCAN · · 464 · filter x = 1 · · 465 466 query TTTTT 467 EXPLAIN (VERBOSE) SELECT count(*) FROM (SELECT DISTINCT ON (x) x, y FROM xyz WHERE x = 1 ORDER BY x, y) 468 ---- 469 · distributed false · · 470 · vectorized true · · 471 group · · (count) · 472 │ aggregate 0 count_rows() · · 473 │ scalar · · · 474 └── render · · () · 475 └── limit · · (x, y) +y 476 │ count 1 · · 477 └── sort · · (x, y) +y 478 │ order +y · · 479 └── scan · · (x, y) · 480 · table xyz@primary · · 481 · spans FULL SCAN · · 482 · filter x = 1 · ·