github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/sql/opt/exec/execbuilder/testdata/aggregate (about) 1 # LogicTest: local 2 3 statement ok 4 CREATE TABLE kv ( 5 k INT PRIMARY KEY, 6 v INT, 7 w INT, 8 s STRING 9 ) 10 11 query TTTTT 12 EXPLAIN (TYPES) SELECT min(1), max(1), count(NULL), sum_int(1), avg(1), sum(1), stddev(1), variance(1), bool_and(true), bool_or(false), xor_agg(b'\x01') FROM kv 13 ---- 14 · distributed false · · 15 · vectorized true · · 16 group · · (min int, max int, count int, sum_int int, avg decimal, sum decimal, stddev decimal, variance decimal, bool_and bool, bool_or bool, xor_agg bytes) · 17 │ aggregate 0 min(column5) · · 18 │ aggregate 1 max(column5) · · 19 │ aggregate 2 count(column8) · · 20 │ aggregate 3 sum_int(column5) · · 21 │ aggregate 4 avg(column5) · · 22 │ aggregate 5 sum(column5) · · 23 │ aggregate 6 stddev(column5) · · 24 │ aggregate 7 variance(column5) · · 25 │ aggregate 8 bool_and(column15) · · 26 │ aggregate 9 bool_or(column17) · · 27 │ aggregate 10 xor_agg(column19) · · 28 │ scalar · · · 29 └── render · · (column5 int, column8 unknown, column15 bool, column17 bool, column19 bytes) · 30 │ render 0 (1)[int] · · 31 │ render 1 (NULL)[unknown] · · 32 │ render 2 (true)[bool] · · 33 │ render 3 (false)[bool] · · 34 │ render 4 ('\x01')[bytes] · · 35 └── scan · · () · 36 · table kv@primary · · 37 · spans FULL SCAN · · 38 39 query TTTTT 40 EXPLAIN (TYPES) SELECT min(v), max(v), count(v), sum_int(1), avg(v), sum(v), stddev(v), variance(v), bool_and(v = 1), bool_and(v = 1), xor_agg(s::bytes) FROM kv 41 ---- 42 · distributed false · · 43 · vectorized true · · 44 render · · (min int, max int, count int, sum_int int, avg decimal, sum decimal, stddev decimal, variance decimal, bool_and bool, bool_and bool, xor_agg bytes) · 45 │ render 0 (min)[int] · · 46 │ render 1 (max)[int] · · 47 │ render 2 (count)[int] · · 48 │ render 3 (sum_int)[int] · · 49 │ render 4 (avg)[decimal] · · 50 │ render 5 (sum)[decimal] · · 51 │ render 6 (stddev)[decimal] · · 52 │ render 7 (variance)[decimal] · · 53 │ render 8 (bool_and)[bool] · · 54 │ render 9 (bool_and)[bool] · · 55 │ render 10 (xor_agg)[bytes] · · 56 └── group · · (min int, max int, count int, sum_int int, avg decimal, sum decimal, stddev decimal, variance decimal, bool_and bool, xor_agg bytes) · 57 │ aggregate 0 min(v) · · 58 │ aggregate 1 max(v) · · 59 │ aggregate 2 count(v) · · 60 │ aggregate 3 sum_int(column8) · · 61 │ aggregate 4 avg(v) · · 62 │ aggregate 5 sum(v) · · 63 │ aggregate 6 stddev(v) · · 64 │ aggregate 7 variance(v) · · 65 │ aggregate 8 bool_and(column14) · · 66 │ aggregate 9 xor_agg(column16) · · 67 │ scalar · · · 68 └── render · · (column8 int, column14 bool, column16 bytes, v int) · 69 │ render 0 (1)[int] · · 70 │ render 1 ((v)[int] = (1)[int])[bool] · · 71 │ render 2 ((s)[string]::BYTES)[bytes] · · 72 │ render 3 (v)[int] · · 73 └── scan · · (v int, s string) · 74 · table kv@primary · · 75 · spans FULL SCAN · · 76 77 # Aggregate functions trigger aggregation and computation when there is no source. 78 query TTTTT 79 EXPLAIN (TYPES) SELECT min(1), count(NULL), max(1), sum_int(1), avg(1)::float, sum(1), stddev(1), variance(1), bool_and(true), bool_or(true), to_hex(xor_agg(b'\x01')) 80 ---- 81 · distributed false · · 82 · vectorized false · · 83 render · · (min int, count int, max int, sum_int int, avg float, sum decimal, stddev decimal, variance decimal, bool_and bool, bool_or bool, to_hex string) · 84 │ render 0 (min)[int] · · 85 │ render 1 (count)[int] · · 86 │ render 2 (max)[int] · · 87 │ render 3 (sum_int)[int] · · 88 │ render 4 ((avg)[decimal]::FLOAT8)[float] · · 89 │ render 5 (sum)[decimal] · · 90 │ render 6 (stddev)[decimal] · · 91 │ render 7 (variance)[decimal] · · 92 │ render 8 (bool_and)[bool] · · 93 │ render 9 (bool_or)[bool] · · 94 │ render 10 (to_hex((xor_agg)[bytes]))[string] · · 95 └── group · · (min int, count int, max int, sum_int int, avg decimal, sum decimal, stddev decimal, variance decimal, bool_and bool, bool_or bool, xor_agg bytes) · 96 │ aggregate 0 min(column1) · · 97 │ aggregate 1 count(column3) · · 98 │ aggregate 2 max(column1) · · 99 │ aggregate 3 sum_int(column1) · · 100 │ aggregate 4 avg(column1) · · 101 │ aggregate 5 sum(column1) · · 102 │ aggregate 6 stddev(column1) · · 103 │ aggregate 7 variance(column1) · · 104 │ aggregate 8 bool_and(column11) · · 105 │ aggregate 9 bool_or(column11) · · 106 │ aggregate 10 xor_agg(column14) · · 107 │ scalar · · · 108 └── values · · (column1 int, column3 unknown, column11 bool, column14 bytes) · 109 · size 4 columns, 1 row · · 110 · row 0, expr 0 (1)[int] · · 111 · row 0, expr 1 (NULL)[unknown] · · 112 · row 0, expr 2 (true)[bool] · · 113 · row 0, expr 3 ('\x01')[bytes] · · 114 115 query TTTTT 116 EXPLAIN (TYPES) SELECT count(*), k FROM kv GROUP BY 2 117 ---- 118 · distributed false · · 119 · vectorized true · · 120 render · · (count int, k int) · 121 │ render 0 (count_rows)[int] · · 122 │ render 1 (k)[int] · · 123 └── group · · (k int, count_rows int) · 124 │ aggregate 0 k · · 125 │ aggregate 1 count_rows() · · 126 │ group by k · · 127 │ ordered +k · · 128 └── scan · · (k int) +k 129 · table kv@primary · · 130 · spans FULL SCAN · · 131 132 # Selecting and grouping on a more complex expression works. 133 query TTTTT 134 EXPLAIN (TYPES) SELECT count(*), k+v AS r FROM kv GROUP BY k+v 135 ---- 136 · distributed false · · 137 · vectorized true · · 138 render · · (count int, r int) · 139 │ render 0 (count_rows)[int] · · 140 │ render 1 (column6)[int] · · 141 └── group · · (column6 int, count_rows int) · 142 │ aggregate 0 column6 · · 143 │ aggregate 1 count_rows() · · 144 │ group by column6 · · 145 └── render · · (column6 int) · 146 │ render 0 ((k)[int] + (v)[int])[int] · · 147 └── scan · · (k int, v int) · 148 · table kv@primary · · 149 · spans FULL SCAN · · 150 151 # Selecting a more complex expression, made up of things which are each grouped, works. 152 query TTTTT 153 EXPLAIN (TYPES) SELECT count(*), k+v AS r FROM kv GROUP BY k, v 154 ---- 155 · distributed false · · 156 · vectorized true · · 157 render · · (count int, r int) · 158 │ render 0 (count_rows)[int] · · 159 │ render 1 ((k)[int] + (any_not_null)[int])[int] · · 160 └── group · · (k int, count_rows int, any_not_null int) · 161 │ aggregate 0 k · · 162 │ aggregate 1 count_rows() · · 163 │ aggregate 2 any_not_null(v) · · 164 │ group by k · · 165 │ ordered +k · · 166 └── scan · · (k int, v int) +k 167 · table kv@primary · · 168 · spans FULL SCAN · · 169 170 query TTTTT 171 EXPLAIN (TYPES) SELECT count(k) FROM kv 172 ---- 173 · distributed false · · 174 · vectorized true · · 175 group · · (count int) · 176 │ aggregate 0 count_rows() · · 177 │ scalar · · · 178 └── scan · · () · 179 · table kv@primary · · 180 · spans FULL SCAN · · 181 182 query TTTTT 183 EXPLAIN (TYPES) SELECT count(k), sum(k), max(k) FROM kv 184 ---- 185 · distributed false · · 186 · vectorized true · · 187 group · · (count int, sum decimal, max int) · 188 │ aggregate 0 count_rows() · · 189 │ aggregate 1 sum(k) · · 190 │ aggregate 2 max(k) · · 191 │ scalar · · · 192 └── scan · · (k int) · 193 · table kv@primary · · 194 · spans FULL SCAN · · 195 196 query TTTTT 197 EXPLAIN (VERBOSE) SELECT count(v), count(DISTINCT v), sum(v), sum(DISTINCT v), min(v), min(DISTINCT v) FROM kv 198 ---- 199 · distributed false · · 200 · vectorized true · · 201 group · · (count, count, sum, sum, min, min) · 202 │ aggregate 0 count(v) · · 203 │ aggregate 1 count(DISTINCT v) · · 204 │ aggregate 2 sum(v) · · 205 │ aggregate 3 sum(DISTINCT v) · · 206 │ aggregate 4 min(v) · · 207 │ aggregate 5 min(v) · · 208 │ scalar · · · 209 └── scan · · (v) · 210 · table kv@primary · · 211 · spans FULL SCAN · · 212 213 query TTTTT 214 EXPLAIN (VERBOSE) SELECT count(DISTINCT a.*) FROM kv a, kv b 215 ---- 216 · distributed false · · 217 · vectorized true · · 218 group · · (count) · 219 │ aggregate 0 count(column9) · · 220 │ scalar · · · 221 └── distinct · · (column9) · 222 │ distinct on column9 · · 223 └── render · · (column9) · 224 │ render 0 ((k, v, w, s) AS k, v, w, s) · · 225 └── cross-join · · (k, v, w, s) · 226 │ type cross · · 227 ├── scan · · (k, v, w, s) · 228 │ table kv@primary · · 229 │ spans FULL SCAN · · 230 └── scan · · () · 231 · table kv@primary · · 232 · spans FULL SCAN · · 233 234 query TTT 235 SELECT tree, field, description FROM [ 236 EXPLAIN (VERBOSE) SELECT min(b.k) FROM kv a, kv b GROUP BY a.* 237 ] 238 ---- 239 · distributed false 240 · vectorized true 241 render · · 242 │ render 0 min 243 └── group · · 244 │ aggregate 0 k 245 │ aggregate 1 min(k) 246 │ group by k 247 └── cross-join · · 248 │ type cross 249 ├── scan · · 250 │ table kv@primary 251 │ spans FULL SCAN 252 └── scan · · 253 · table kv@primary 254 · spans FULL SCAN 255 256 query TTT 257 SELECT tree, field, description FROM [ 258 EXPLAIN (VERBOSE) SELECT min(b.k) FROM kv a, kv b GROUP BY (1, (a.*)) 259 ] 260 ---- 261 · distributed false 262 · vectorized true 263 render · · 264 │ render 0 min 265 └── group · · 266 │ aggregate 0 k 267 │ aggregate 1 min(k) 268 │ group by k 269 └── cross-join · · 270 │ type cross 271 ├── scan · · 272 │ table kv@primary 273 │ spans FULL SCAN 274 └── scan · · 275 · table kv@primary 276 · spans FULL SCAN 277 278 # A useful optimization: naked tuple expansion in GROUP BY clause. 279 query TTT 280 SELECT tree, field, description FROM [ 281 EXPLAIN (VERBOSE) SELECT min(b.k) FROM kv a, kv b GROUP BY (a.*) 282 ] 283 ---- 284 · distributed false 285 · vectorized true 286 render · · 287 │ render 0 min 288 └── group · · 289 │ aggregate 0 k 290 │ aggregate 1 min(k) 291 │ group by k 292 └── cross-join · · 293 │ type cross 294 ├── scan · · 295 │ table kv@primary 296 │ spans FULL SCAN 297 └── scan · · 298 · table kv@primary 299 · spans FULL SCAN 300 301 # Show reuse of renders expression inside an expansion. 302 query TTT 303 SELECT tree, field, description FROM [ 304 EXPLAIN (VERBOSE) SELECT a.v FROM kv a, kv b GROUP BY a.v, a.w, a.s 305 ] 306 ---- 307 · distributed false 308 · vectorized true 309 render · · 310 │ render 0 v 311 └── distinct · · 312 │ distinct on v, w, s 313 └── cross-join · · 314 │ type cross 315 ├── scan · · 316 │ table kv@primary 317 │ spans FULL SCAN 318 └── scan · · 319 · table kv@primary 320 · spans FULL SCAN 321 322 statement ok 323 CREATE TABLE abc ( 324 a CHAR PRIMARY KEY, 325 b FLOAT, 326 c BOOLEAN, 327 d DECIMAL 328 ) 329 330 query TTTTT 331 EXPLAIN (TYPES) SELECT min(a) FROM abc 332 ---- 333 · distributed false · · 334 · vectorized true · · 335 group · · (min char) · 336 │ aggregate 0 any_not_null(a) · · 337 │ scalar · · · 338 └── scan · · (a char) · 339 · table abc@primary · · 340 · spans LIMITED SCAN · · 341 · limit 1 · · 342 343 statement ok 344 CREATE TABLE xyz ( 345 x INT PRIMARY KEY, 346 y INT, 347 z FLOAT, 348 INDEX xy (x, y), 349 INDEX zyx (z, y, x), 350 FAMILY (x), 351 FAMILY (y), 352 FAMILY (z) 353 ) 354 355 statement ok 356 INSERT INTO xyz VALUES (1, 2, 3.0), (4, 5, 6.0), (7, NULL, 8.0) 357 358 query TTTTT 359 EXPLAIN (TYPES) SELECT min(x) FROM xyz 360 ---- 361 · distributed false · · 362 · vectorized true · · 363 group · · (min int) · 364 │ aggregate 0 any_not_null(x) · · 365 │ scalar · · · 366 └── scan · · (x int) · 367 · table xyz@xy · · 368 · spans LIMITED SCAN · · 369 · limit 1 · · 370 371 query TTTTT 372 EXPLAIN (TYPES) SELECT min(x) FROM xyz WHERE x in (0, 4, 7) 373 ---- 374 · distributed false · · 375 · vectorized true · · 376 group · · (min int) · 377 │ aggregate 0 any_not_null(x) · · 378 │ scalar · · · 379 └── scan · · (x int) · 380 · table xyz@xy · · 381 · spans /0-/1 /4-/5 /7-/8 · · 382 · limit 1 · · 383 384 query TTTTT 385 EXPLAIN (TYPES) SELECT max(x) FROM xyz 386 ---- 387 · distributed false · · 388 · vectorized true · · 389 group · · (max int) · 390 │ aggregate 0 any_not_null(x) · · 391 │ scalar · · · 392 └── revscan · · (x int) · 393 · table xyz@xy · · 394 · spans LIMITED SCAN · · 395 · limit 1 · · 396 397 query TTTTT 398 EXPLAIN (TYPES) SELECT min(y) FROM xyz WHERE x = 1 399 ---- 400 · distributed false · · 401 · vectorized true · · 402 group · · (min int) · 403 │ aggregate 0 any_not_null(y) · · 404 │ scalar · · · 405 └── render · · (y int) · 406 │ render 0 (y)[int] · · 407 └── scan · · (x int, y int) · 408 · table xyz@xy · · 409 · spans /1/!NULL-/2 · · 410 411 query TTTTT 412 EXPLAIN (TYPES) SELECT max(y) FROM xyz WHERE x = 1 413 ---- 414 · distributed false · · 415 · vectorized true · · 416 group · · (max int) · 417 │ aggregate 0 any_not_null(y) · · 418 │ scalar · · · 419 └── render · · (y int) · 420 │ render 0 (y)[int] · · 421 └── scan · · (x int, y int) · 422 · table xyz@xy · · 423 · spans /1/!NULL-/2 · · 424 425 query TTTTT 426 EXPLAIN (TYPES) SELECT min(y) FROM xyz WHERE z = 7 427 ---- 428 · distributed false · · 429 · vectorized true · · 430 group · · (min int) · 431 │ aggregate 0 any_not_null(y) · · 432 │ scalar · · · 433 └── render · · (y int) · 434 │ render 0 (y)[int] · · 435 └── scan · · (y int, z float) · 436 · table xyz@zyx · · 437 · spans /7/!NULL-/7.000000000000001 · · 438 · limit 1 · · 439 440 query TTTTT 441 EXPLAIN (TYPES) SELECT max(y) FROM xyz WHERE z = 7 442 ---- 443 · distributed false · · 444 · vectorized true · · 445 group · · (max int) · 446 │ aggregate 0 any_not_null(y) · · 447 │ scalar · · · 448 └── render · · (y int) · 449 │ render 0 (y)[int] · · 450 └── revscan · · (y int, z float) · 451 · table xyz@zyx · · 452 · spans /7/!NULL-/7.000000000000001 · · 453 · limit 1 · · 454 455 query TTTTT 456 EXPLAIN (TYPES) SELECT min(x) FROM xyz WHERE (y, z) = (2, 3.0) 457 ---- 458 · distributed false · · 459 · vectorized true · · 460 group · · (min int) · 461 │ aggregate 0 min(x) · · 462 │ scalar · · · 463 └── render · · (x int) · 464 │ render 0 (x)[int] · · 465 └── scan · · (x int, y int, z float) · 466 · table xyz@zyx · · 467 · spans /3/2-/3/3 · · 468 469 statement ok 470 SET tracing = on,kv,results; SELECT min(x) FROM xyz WHERE (y, z) = (2, 3.0); SET tracing = off 471 472 query T 473 SELECT message FROM [SHOW KV TRACE FOR SESSION] WITH ORDINALITY 474 WHERE message LIKE 'fetched:%' OR message LIKE 'output row%' 475 ORDER BY message LIKE 'fetched:%' DESC, ordinality ASC 476 ---- 477 fetched: /xyz/zyx/3.0/2/1 -> NULL 478 output row: [1] 479 480 query TTTTT 481 EXPLAIN (TYPES) SELECT max(x) FROM xyz WHERE (z, y) = (3.0, 2) 482 ---- 483 · distributed false · · 484 · vectorized true · · 485 group · · (max int) · 486 │ aggregate 0 max(x) · · 487 │ scalar · · · 488 └── render · · (x int) · 489 │ render 0 (x)[int] · · 490 └── scan · · (x int, y int, z float) · 491 · table xyz@zyx · · 492 · spans /3/2-/3/3 · · 493 494 # VARIANCE/STDDEV 495 496 statement ok 497 SET tracing = on,kv,results; SELECT variance(x), variance(y::decimal), round(variance(z), 14) FROM xyz; SET tracing = off 498 499 query T 500 SELECT message FROM [SHOW KV TRACE FOR SESSION] WITH ORDINALITY 501 WHERE message LIKE 'fetched:%' OR message LIKE 'output row%' 502 ORDER BY message LIKE 'fetched:%' DESC, ordinality ASC 503 ---- 504 fetched: /xyz/primary/1 -> NULL 505 fetched: /xyz/primary/1/y -> 2 506 fetched: /xyz/primary/1/z -> 3.0 507 fetched: /xyz/primary/4 -> NULL 508 fetched: /xyz/primary/4/y -> 5 509 fetched: /xyz/primary/4/z -> 6.0 510 fetched: /xyz/primary/7 -> NULL 511 fetched: /xyz/primary/7/z -> 8.0 512 output row: [9 4.5 6.33333333333333] 513 514 query TTTTT 515 EXPLAIN (TYPES) SELECT variance(x) FROM xyz WHERE x = 1 516 ---- 517 · distributed false · · 518 · vectorized true · · 519 group · · (variance decimal) · 520 │ aggregate 0 variance(x) · · 521 │ scalar · · · 522 └── scan · · (x int) · 523 · table xyz@xy · · 524 · spans /1-/2 · · 525 526 ## Tests for the single-row optimization. 527 statement ok 528 CREATE TABLE ab ( 529 a INT PRIMARY KEY, 530 b INT, 531 FAMILY (a), 532 FAMILY (b) 533 ) 534 535 statement ok 536 INSERT INTO ab VALUES 537 (1, 10), 538 (2, 20), 539 (3, 30), 540 (4, 40), 541 (5, 50) 542 543 #exec nodist 544 #EXPLAIN (EXPRS) SELECT min(a) FROM abc 545 #---- 546 #group · · 547 # │ aggregate 0 min(a) 548 # └── render · · 549 # │ render 0 a 550 # └── scan · · 551 #· table abc@primary 552 #· spans ALL 553 #· limit 1 554 # 555 ## Verify we only buffer one row. 556 #exec 557 #SELECT message FROM [SHOW KV TRACE FOR SELECT min(a) FROM ab] 558 # WHERE message LIKE 'fetched:%' OR message LIKE 'output row%' 559 #---- 560 #fetched: /ab/primary/1 -> NULL 561 #fetched: /ab/primary/1/b -> 10 562 #output row: [1] 563 # 564 #exec nodist 565 #EXPLAIN (EXPRS) SELECT max(a) FROM abc 566 #---- 567 #group · · 568 # │ aggregate 0 max(a) 569 # └── render · · 570 # │ render 0 a 571 # └── revscan · · 572 #· table abc@primary 573 #· spans ALL 574 #· limit 1 575 # 576 ## Verify we only buffer one row. 577 #exec 578 #SELECT message FROM [SHOW KV TRACE FOR SELECT max(a) FROM ab] 579 # WHERE message LIKE 'fetched:%' OR message LIKE 'output row%' 580 #---- 581 #fetched: /ab/primary/5/b -> 50 582 #fetched: /ab/primary/5 -> NULL 583 #output row: [5] 584 585 query TTTTT 586 EXPLAIN (TYPES) SELECT v, count(k) FROM kv GROUP BY v ORDER BY count(k) 587 ---- 588 · distributed false · · 589 · vectorized true · · 590 sort · · (v int, count int) +count 591 │ order +count · · 592 └── group · · (v int, count int) · 593 │ aggregate 0 v · · 594 │ aggregate 1 count_rows() · · 595 │ group by v · · 596 └── scan · · (v int) · 597 · table kv@primary · · 598 · spans FULL SCAN · · 599 600 query TTTTT 601 EXPLAIN (TYPES) SELECT v, count(*) FROM kv GROUP BY v ORDER BY count(*) 602 ---- 603 · distributed false · · 604 · vectorized true · · 605 sort · · (v int, count int) +count 606 │ order +count · · 607 └── group · · (v int, count int) · 608 │ aggregate 0 v · · 609 │ aggregate 1 count_rows() · · 610 │ group by v · · 611 └── scan · · (v int) · 612 · table kv@primary · · 613 · spans FULL SCAN · · 614 615 query TTTTT 616 EXPLAIN (TYPES) SELECT v, count(NULL) FROM kv GROUP BY v ORDER BY count(1) 617 ---- 618 · distributed false · · 619 · vectorized true · · 620 render · · (v int, count int) · 621 │ render 0 (v)[int] · · 622 │ render 1 (count)[int] · · 623 └── sort · · (v int, count int, count_rows int) +count_rows 624 │ order +count_rows · · 625 └── group · · (v int, count int, count_rows int) · 626 │ aggregate 0 v · · 627 │ aggregate 1 count(column5) · · 628 │ aggregate 2 count_rows() · · 629 │ group by v · · 630 └── render · · (column5 unknown, v int) · 631 │ render 0 (NULL)[unknown] · · 632 │ render 1 (v)[int] · · 633 └── scan · · (v int) · 634 · table kv@primary · · 635 · spans FULL SCAN · · 636 637 # Check that filters propagate through no-op aggregation. 638 query TTTTT 639 EXPLAIN (VERBOSE) SELECT * FROM (SELECT v, count(NULL) FROM kv GROUP BY v) WHERE v > 10 640 ---- 641 · distributed false · · 642 · vectorized true · · 643 group · · (v, count) · 644 │ aggregate 0 v · · 645 │ aggregate 1 count(column5) · · 646 │ group by v · · 647 └── render · · (column5, v) · 648 │ render 0 NULL · · 649 │ render 1 v · · 650 └── scan · · (v) · 651 · table kv@primary · · 652 · spans FULL SCAN · · 653 · filter v > 10 · · 654 655 # Verify that FILTER works. 656 657 statement ok 658 CREATE TABLE filter_test ( 659 k INT, 660 v INT, 661 mark BOOL 662 ) 663 664 # Check that filter expressions are only rendered once. 665 query TTTTT 666 EXPLAIN (VERBOSE) SELECT count(*) FILTER (WHERE k>5), max(k>5) FILTER(WHERE k>5) FROM filter_test GROUP BY v 667 ---- 668 · distributed false · · 669 · vectorized true · · 670 render · · (count, max) · 671 │ render 0 count · · 672 │ render 1 max · · 673 └── group · · (v, count, max) · 674 │ aggregate 0 v · · 675 │ aggregate 1 count(column5) FILTER (WHERE column6) · · 676 │ aggregate 2 max(column6) FILTER (WHERE column6) · · 677 │ group by v · · 678 └── render · · (column5, column6, v) · 679 │ render 0 true · · 680 │ render 1 k > 5 · · 681 │ render 2 v · · 682 └── scan · · (k, v) · 683 · table filter_test@primary · · 684 · spans FULL SCAN · · 685 686 query TTTTT 687 EXPLAIN (VERBOSE) SELECT count(*) FILTER (WHERE k > 5) FROM filter_test GROUP BY v 688 ---- 689 · distributed false · · 690 · vectorized true · · 691 render · · (count) · 692 │ render 0 count · · 693 └── group · · (v, count) · 694 │ aggregate 0 v · · 695 │ aggregate 1 count(column5) FILTER (WHERE column6) · · 696 │ group by v · · 697 └── render · · (column5, column6, v) · 698 │ render 0 true · · 699 │ render 1 k > 5 · · 700 │ render 2 v · · 701 └── scan · · (k, v) · 702 · table filter_test@primary · · 703 · spans FULL SCAN · · 704 705 # Tests with * inside GROUP BY. 706 query TTTTT 707 EXPLAIN (TYPES) SELECT 1 a FROM kv GROUP BY kv.*; 708 ---- 709 · distributed false · · 710 · vectorized true · · 711 render · · (a int) · 712 │ render 0 (1)[int] · · 713 └── scan · · () · 714 · table kv@primary · · 715 · spans FULL SCAN · · 716 717 query TTTTT 718 EXPLAIN (TYPES) SELECT sum(abc.d) FROM kv JOIN abc ON kv.k >= abc.d GROUP BY kv.*; 719 ---- 720 · distributed false · · 721 · vectorized true · · 722 render · · (sum decimal) · 723 │ render 0 (sum)[decimal] · · 724 └── group · · (k int, sum decimal) · 725 │ aggregate 0 k · · 726 │ aggregate 1 sum(d) · · 727 │ group by k · · 728 └── cross-join · · (k int, d decimal) · 729 │ type inner · · 730 │ pred ((k)[int] >= (d)[decimal])[bool] · · 731 ├── scan · · (k int) · 732 │ table kv@primary · · 733 │ spans FULL SCAN · · 734 └── scan · · (d decimal) · 735 · table abc@primary · · 736 · spans FULL SCAN · · 737 738 # opt_test is used for tests around the single-row optimization for MIN/MAX. 739 statement ok 740 CREATE TABLE opt_test (k INT PRIMARY KEY, v INT, INDEX v(v)) 741 742 # Verify that we correctly add the v IS NOT NULL constraint (which restricts the span). 743 query TTTTT 744 EXPLAIN (TYPES) SELECT min(v) FROM opt_test 745 ---- 746 · distributed false · · 747 · vectorized true · · 748 group · · (min int) · 749 │ aggregate 0 any_not_null(v) · · 750 │ scalar · · · 751 └── scan · · (v int) · 752 · table opt_test@v · · 753 · spans /!NULL- · · 754 · limit 1 · · 755 756 # Repeat test when there is an existing filter. 757 # TODO(radu): the best plan for this would be to use index v; in this case the scan 758 # will end early but that is not reflected by the cost. 759 query TTTTT 760 EXPLAIN (TYPES) SELECT min(v) FROM opt_test WHERE k <> 4 761 ---- 762 · distributed false · · 763 · vectorized true · · 764 group · · (min int) · 765 │ aggregate 0 any_not_null(v) · · 766 │ scalar · · · 767 └── render · · (v int) · 768 │ render 0 (v)[int] · · 769 └── limit · · (k int, v int) +v 770 │ count (1)[int] · · 771 └── scan · · (k int, v int) +v 772 · table opt_test@v · · 773 · spans /!NULL- · · 774 · filter ((k)[int] != (4)[int])[bool] · · 775 776 # Check that the optimization doesn't work when the argument is non-trivial (we 777 # can't in general guarantee an ordering on a synthesized column). 778 query TTTTT 779 EXPLAIN (TYPES) SELECT min(v+1) FROM opt_test 780 ---- 781 · distributed false · · 782 · vectorized true · · 783 group · · (min int) · 784 │ aggregate 0 min(column3) · · 785 │ scalar · · · 786 └── render · · (column3 int) · 787 │ render 0 ((v)[int] + (1)[int])[int] · · 788 └── scan · · (v int) · 789 · table opt_test@primary · · 790 · spans FULL SCAN · · 791 792 # Verify that we don't use the optimization if there is a GROUP BY. 793 query TTTTT 794 EXPLAIN (TYPES) SELECT min(v) FROM opt_test GROUP BY k 795 ---- 796 · distributed false · · 797 · vectorized true · · 798 render · · (min int) · 799 │ render 0 (min)[int] · · 800 └── group · · (k int, min int) · 801 │ aggregate 0 k · · 802 │ aggregate 1 min(v) · · 803 │ group by k · · 804 │ ordered +k · · 805 └── scan · · (k int, v int) +k 806 · table opt_test@primary · · 807 · spans FULL SCAN · · 808 809 statement ok 810 CREATE TABLE xy(x STRING, y STRING); 811 812 query TTTTT 813 EXPLAIN (TYPES) SELECT (b, a) r FROM ab GROUP BY (b, a) 814 ---- 815 · distributed false · · 816 · vectorized true · · 817 render · · (r tuple{int, int}) · 818 │ render 0 (((b)[int], (a)[int]))[tuple{int, int}] · · 819 └── scan · · (a int, b int) · 820 · table ab@primary · · 821 · spans FULL SCAN · · 822 823 query TTTTT 824 EXPLAIN (TYPES) SELECT min(y), (b, a) r FROM ab, xy GROUP BY (x, (a, b)) 825 ---- 826 · distributed false · · 827 · vectorized true · · 828 render · · (min string, r tuple{int, int}) · 829 │ render 0 (min)[string] · · 830 │ render 1 (((any_not_null)[int], (a)[int]))[tuple{int, int}] · · 831 └── group · · (a int, x string, min string, any_not_null int) · 832 │ aggregate 0 a · · 833 │ aggregate 1 x · · 834 │ aggregate 2 min(y) · · 835 │ aggregate 3 any_not_null(b) · · 836 │ group by a, x · · 837 └── cross-join · · (a int, b int, x string, y string) · 838 │ type cross · · 839 ├── scan · · (a int, b int) · 840 │ table ab@primary · · 841 │ spans FULL SCAN · · 842 └── scan · · (x string, y string) · 843 · table xy@primary · · 844 · spans FULL SCAN · · 845 846 # Test that ordering on GROUP BY columns is maintained. 847 # TODO(radu): Derive GROUP BY ordering in physicalPropsBuilder. 848 #exec-raw 849 #CREATE TABLE group_ord ( 850 # x INT PRIMARY KEY, 851 # y INT, 852 # z INT, 853 # INDEX foo(z) 854 #) 855 #---- 856 # 857 ## The ordering is on all the GROUP BY columns, and isn't preserved after the 858 ## aggregation. 859 #exec hide-colnames nodist 860 #EXPLAIN (TYPES) SELECT x, max(y) FROM group_ord GROUP BY x 861 #---- 862 #group · · (x, max) · 863 # │ aggregate 0 x · · 864 # │ aggregate 1 max(y) · · 865 # │ group by @1 · · 866 # └── scan · · (x, y) · 867 #· table group_ord@primary · · 868 #· spans ALL · · 869 # 870 ## The ordering is on all the GROUP BY columns, and is preserved after the 871 ## aggregation. 872 #exec hide-colnames nodist 873 #EXPLAIN (TYPES) SELECT x, max(y) FROM group_ord GROUP BY x ORDER BY x 874 #---- 875 #sort · · (x, max) +x 876 # │ order +x · · 877 # └── group · · (x, max) · 878 # │ aggregate 0 x · · 879 # │ aggregate 1 max(y) · · 880 # │ group by @1 · · 881 # └── scan · · (x, y) · 882 #· table group_ord@primary · · 883 #· spans ALL · · 884 # 885 ## The ordering is on some of the GROUP BY columns, and isn't preserved after 886 ## the aggregation. 887 #exec hide-colnames nodist 888 #EXPLAIN (TYPES) SELECT z, x, max(y) FROM group_ord GROUP BY x, z 889 #---- 890 #render · · (z, x, max) · 891 # │ render 0 z · · 892 # │ render 1 x · · 893 # │ render 2 agg0 · · 894 # └── group · · (x, z, agg0) · 895 # │ aggregate 0 x · · 896 # │ aggregate 1 z · · 897 # │ aggregate 2 max(y) · · 898 # │ group by @1,@3 · · 899 # └── scan · · (x, y, z) · 900 #· table group_ord@primary · · 901 #· spans ALL · · 902 # 903 ## The ordering is on some of the GROUP BY columns, and is preserved after 904 ## the aggregation. 905 #exec hide-colnames nodist 906 #EXPLAIN (TYPES) SELECT z, x, max(y) FROM group_ord GROUP BY x, z ORDER BY x 907 #---- 908 #render · · (z, x, max) · 909 # │ render 0 z · · 910 # │ render 1 x · · 911 # │ render 2 agg0 · · 912 # └── sort · · (x, z, agg0) +x 913 # │ order +x · · 914 # └── group · · (x, z, agg0) · 915 # │ aggregate 0 x · · 916 # │ aggregate 1 z · · 917 # │ aggregate 2 max(y) · · 918 # │ group by @1,@3 · · 919 # └── scan · · (x, y, z) · 920 #· table group_ord@primary · · 921 #· spans ALL · · 922 # 923 ## If the underlying ordering isn't from the primary index, it needs to be hinted 924 ## for now. 925 #exec hide-colnames nodist 926 #EXPLAIN (TYPES) SELECT z, max(y) FROM group_ord@foo GROUP BY z 927 #---- 928 #group · · (z, max) · 929 # │ aggregate 0 z · · 930 # │ aggregate 1 max(y) · · 931 # │ group by @2 · · 932 # └── scan · · (y, z) · 933 #· table group_ord@primary · · 934 #· spans ALL · · 935 # 936 ## Test that a merge join is used on two aggregate subqueries with orderings on 937 ## the GROUP BY columns. Note that an ORDER BY is not necessary on the 938 ## subqueries. 939 #exec hide-colnames nodist 940 #EXPLAIN (TYPES) SELECT * FROM (SELECT x, max(y) FROM group_ord GROUP BY x) JOIN (SELECT z, min(y) FROM group_ord@foo GROUP BY z) ON x = z 941 #---- 942 #join · · (x, max, z, min) · 943 # │ type inner · · 944 # │ equality (x) = (z) · · 945 # ├── group · · (x, agg0) · 946 # │ │ aggregate 0 x · · 947 # │ │ aggregate 1 max(y) · · 948 # │ │ group by @1 · · 949 # │ └── scan · · (x, y) · 950 # │ table group_ord@primary · · 951 # │ spans ALL · · 952 # └── group · · (z, agg0) · 953 # │ aggregate 0 z · · 954 # │ aggregate 1 min(y) · · 955 # │ group by @2 · · 956 # └── scan · · (y, z) · 957 #· table group_ord@primary · · 958 #· spans ALL · · 959 960 # Regression test for #25533 (crash when propagating filter through GROUP BY). 961 query TTTTT 962 EXPLAIN (TYPES) SELECT 1 a FROM kv GROUP BY v, w::DECIMAL HAVING w::DECIMAL > 1; 963 ---- 964 · distributed false · · 965 · vectorized true · · 966 render · · (a int) · 967 │ render 0 (1)[int] · · 968 └── distinct · · (column5 decimal, v int) · 969 │ distinct on column5, v · · 970 └── filter · · (column5 decimal, v int) · 971 │ filter ((column5)[decimal] > (1)[decimal])[bool] · · 972 └── render · · (column5 decimal, v int) · 973 │ render 0 ((w)[int]::DECIMAL)[decimal] · · 974 │ render 1 (v)[int] · · 975 └── scan · · (v int, w int) · 976 · table kv@primary · · 977 · spans FULL SCAN · · 978 979 statement ok 980 CREATE TABLE foo(a INT, b CHAR) 981 982 # Check that GROUP BY picks up column ordinals. 983 query TTTTT 984 EXPLAIN (VERBOSE) SELECT min(a) AS m FROM foo GROUP BY @1 985 ---- 986 · distributed false · · 987 · vectorized true · · 988 render · · (m) · 989 │ render 0 min · · 990 └── group · · (column5, min) · 991 │ aggregate 0 column5 · · 992 │ aggregate 1 min(a) · · 993 │ group by column5 · · 994 └── render · · (column5, a) · 995 │ render 0 a · · 996 │ render 1 a · · 997 └── scan · · (a) · 998 · table foo@primary · · 999 · spans FULL SCAN · · 1000 1001 query TTTTT 1002 EXPLAIN (VERBOSE) SELECT min(a) AS m FROM foo GROUP BY @2 1003 ---- 1004 · distributed false · · 1005 · vectorized true · · 1006 render · · (m) · 1007 │ render 0 min · · 1008 └── group · · (column5, min) · 1009 │ aggregate 0 column5 · · 1010 │ aggregate 1 min(a) · · 1011 │ group by column5 · · 1012 └── render · · (column5, a) · 1013 │ render 0 b · · 1014 │ render 1 a · · 1015 └── scan · · (a, b) · 1016 · table foo@primary · · 1017 · spans FULL SCAN · · 1018 1019 query TTTTT 1020 EXPLAIN (VERBOSE) SELECT array_agg(v) FROM (SELECT * FROM kv ORDER BY v) 1021 ---- 1022 · distributed false · · 1023 · vectorized true · · 1024 group · · (array_agg) · 1025 │ aggregate 0 array_agg(v) · · 1026 │ scalar · · · 1027 └── sort · · (v) +v 1028 │ order +v · · 1029 └── scan · · (v) · 1030 · table kv@primary · · 1031 · spans FULL SCAN · · 1032 1033 query TTTTT 1034 EXPLAIN (VERBOSE) SELECT k FROM kv ORDER BY s 1035 ---- 1036 · distributed false · · 1037 · vectorized true · · 1038 render · · (k) · 1039 │ render 0 k · · 1040 └── sort · · (k, s) +s 1041 │ order +s · · 1042 └── scan · · (k, s) · 1043 · table kv@primary · · 1044 · spans FULL SCAN · · 1045 1046 query TTTTT 1047 EXPLAIN (VERBOSE) SELECT concat_agg(s) FROM (SELECT s FROM kv ORDER BY k) 1048 ---- 1049 · distributed false · · 1050 · vectorized true · · 1051 group · · (concat_agg) · 1052 │ aggregate 0 concat_agg(s) · · 1053 │ scalar · · · 1054 └── scan · · (k, s) +k 1055 · table kv@primary · · 1056 · spans FULL SCAN · · 1057 1058 query TTTTT 1059 EXPLAIN (VERBOSE) SELECT array_agg(k) FROM (SELECT k FROM kv ORDER BY s) 1060 ---- 1061 · distributed false · · 1062 · vectorized true · · 1063 group · · (array_agg) · 1064 │ aggregate 0 array_agg(k) · · 1065 │ scalar · · · 1066 └── sort · · (k, s) +s 1067 │ order +s · · 1068 └── scan · · (k, s) · 1069 · table kv@primary · · 1070 · spans FULL SCAN · · 1071 1072 query TTTTT 1073 EXPLAIN (VERBOSE) SELECT string_agg(s, ',') FROM (SELECT s FROM kv ORDER BY k) 1074 ---- 1075 · distributed false · · 1076 · vectorized true · · 1077 group · · (string_agg) · 1078 │ aggregate 0 string_agg(s, column5) · · 1079 │ scalar · · · 1080 └── render · · (column5, k, s) +k 1081 │ render 0 ',' · · 1082 │ render 1 k · · 1083 │ render 2 s · · 1084 └── scan · · (k, s) +k 1085 · table kv@primary · · 1086 · spans FULL SCAN · · 1087 1088 # Verify that we project away all input columns for count(*). 1089 query TTTTT 1090 EXPLAIN (VERBOSE) SELECT count(*) FROM xyz JOIN kv ON y=v 1091 ---- 1092 · distributed false · · 1093 · vectorized true · · 1094 group · · (count) · 1095 │ aggregate 0 count_rows() · · 1096 │ scalar · · · 1097 └── render · · () · 1098 └── hash-join · · (y, v) · 1099 │ type inner · · 1100 │ equality (y) = (v) · · 1101 ├── scan · · (y) · 1102 │ table xyz@xy · · 1103 │ spans FULL SCAN · · 1104 └── scan · · (v) · 1105 · table kv@primary · · 1106 · spans FULL SCAN · · 1107 1108 1109 # Regression test for #31882: make sure we don't incorrectly advertise an 1110 # ordering of +w at the scan node. 1111 statement ok 1112 CREATE TABLE uvw (u INT, v INT, w INT, INDEX uvw(u, v, w)) 1113 1114 query TTTTT 1115 EXPLAIN (VERBOSE) SELECT u, v, array_agg(w) AS s FROM (SELECT * FROM uvw ORDER BY w) GROUP BY u, v 1116 ---- 1117 · distributed false · · 1118 · vectorized true · · 1119 group · · (u, v, s) · 1120 │ aggregate 0 u · · 1121 │ aggregate 1 v · · 1122 │ aggregate 2 array_agg(w) · · 1123 │ group by u, v · · 1124 │ ordered +u,+v · · 1125 └── scan · · (u, v, w) +u,+v,+w 1126 · table uvw@uvw · · 1127 · spans FULL SCAN · · 1128 1129 query TTTTT 1130 EXPLAIN (VERBOSE) SELECT string_agg(s, ', ') FROM kv 1131 ---- 1132 · distributed false · · 1133 · vectorized true · · 1134 group · · (string_agg) · 1135 │ aggregate 0 string_agg(s, column5) · · 1136 │ scalar · · · 1137 └── render · · (column5, s) · 1138 │ render 0 ', ' · · 1139 │ render 1 s · · 1140 └── scan · · (s) · 1141 · table kv@primary · · 1142 · spans FULL SCAN · · 1143 1144 statement ok 1145 CREATE TABLE string_agg_test ( 1146 id INT PRIMARY KEY, 1147 company_id INT, 1148 employee STRING 1149 ) 1150 1151 query TTTTT 1152 EXPLAIN (VERBOSE) 1153 SELECT 1154 company_id, string_agg(employee, ',') 1155 FROM 1156 string_agg_test 1157 GROUP BY 1158 company_id 1159 ORDER BY 1160 company_id 1161 ---- 1162 · distributed false · · 1163 · vectorized true · · 1164 sort · · (company_id, string_agg) +company_id 1165 │ order +company_id · · 1166 └── group · · (company_id, string_agg) · 1167 │ aggregate 0 company_id · · 1168 │ aggregate 1 string_agg(employee, column4) · · 1169 │ group by company_id · · 1170 └── render · · (column4, company_id, employee) · 1171 │ render 0 ',' · · 1172 │ render 1 company_id · · 1173 │ render 2 employee · · 1174 └── scan · · (company_id, employee) · 1175 · table string_agg_test@primary · · 1176 · spans FULL SCAN · · 1177 1178 query TTTTT 1179 EXPLAIN (VERBOSE) 1180 SELECT 1181 company_id, string_agg(employee::BYTES, b',') 1182 FROM 1183 string_agg_test 1184 GROUP BY 1185 company_id 1186 ORDER BY 1187 company_id 1188 ---- 1189 · distributed false · · 1190 · vectorized true · · 1191 sort · · (company_id, string_agg) +company_id 1192 │ order +company_id · · 1193 └── group · · (company_id, string_agg) · 1194 │ aggregate 0 company_id · · 1195 │ aggregate 1 string_agg(column4, column5) · · 1196 │ group by company_id · · 1197 └── render · · (column4, column5, company_id) · 1198 │ render 0 employee::BYTES · · 1199 │ render 1 '\x2c' · · 1200 │ render 2 company_id · · 1201 └── scan · · (company_id, employee) · 1202 · table string_agg_test@primary · · 1203 · spans FULL SCAN · · 1204 1205 query TTTTT 1206 EXPLAIN (VERBOSE) 1207 SELECT 1208 company_id, string_agg(employee, NULL) 1209 FROM 1210 string_agg_test 1211 GROUP BY 1212 company_id 1213 ORDER BY 1214 company_id 1215 ---- 1216 · distributed false · · 1217 · vectorized true · · 1218 sort · · (company_id, string_agg) +company_id 1219 │ order +company_id · · 1220 └── group · · (company_id, string_agg) · 1221 │ aggregate 0 company_id · · 1222 │ aggregate 1 string_agg(employee, column4) · · 1223 │ group by company_id · · 1224 └── render · · (column4, company_id, employee) · 1225 │ render 0 NULL · · 1226 │ render 1 company_id · · 1227 │ render 2 employee · · 1228 └── scan · · (company_id, employee) · 1229 · table string_agg_test@primary · · 1230 · spans FULL SCAN · · 1231 1232 query TTTTT 1233 EXPLAIN (VERBOSE) 1234 SELECT 1235 company_id, string_agg(employee::BYTES, NULL) 1236 FROM 1237 string_agg_test 1238 GROUP BY 1239 company_id 1240 ORDER BY 1241 company_id 1242 ---- 1243 · distributed false · · 1244 · vectorized true · · 1245 sort · · (company_id, string_agg) +company_id 1246 │ order +company_id · · 1247 └── group · · (company_id, string_agg) · 1248 │ aggregate 0 company_id · · 1249 │ aggregate 1 string_agg(column4, column5) · · 1250 │ group by company_id · · 1251 └── render · · (column4, column5, company_id) · 1252 │ render 0 employee::BYTES · · 1253 │ render 1 NULL · · 1254 │ render 2 company_id · · 1255 └── scan · · (company_id, employee) · 1256 · table string_agg_test@primary · · 1257 · spans FULL SCAN · ·