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