github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/sql/opt/optbuilder/testdata/aggregate (about) 1 # tests adapted from logictest -- aggregate 2 3 exec-ddl 4 CREATE TABLE kv ( 5 k INT PRIMARY KEY, 6 v INT, 7 w INT, 8 s STRING 9 ) 10 ---- 11 12 exec-ddl 13 CREATE TABLE abxy ( 14 a INT, 15 b INT, 16 x INT, 17 y INT, 18 PRIMARY KEY(a,b) 19 ) 20 ---- 21 22 build 23 SELECT min(1), max(1), count(1), sum_int(1), avg(1), sum(1), stddev(1), 24 variance(1), bool_and(true), bool_or(false), xor_agg(b'\x01') FROM kv 25 ---- 26 scalar-group-by 27 ├── columns: min:6 max:7 count:8!null sum_int:9 avg:10 sum:11 stddev:12 variance:13 bool_and:15 bool_or:17 xor_agg:19 28 ├── project 29 │ ├── columns: column5:5!null column14:14!null column16:16!null column18:18!null 30 │ ├── scan kv 31 │ │ └── columns: k:1!null v:2 w:3 s:4 32 │ └── projections 33 │ ├── 1 [as=column5:5] 34 │ ├── true [as=column14:14] 35 │ ├── false [as=column16:16] 36 │ └── '\x01' [as=column18:18] 37 └── aggregations 38 ├── min [as=min:6] 39 │ └── column5:5 40 ├── max [as=max:7] 41 │ └── column5:5 42 ├── count [as=count:8] 43 │ └── column5:5 44 ├── sum-int [as=sum_int:9] 45 │ └── column5:5 46 ├── avg [as=avg:10] 47 │ └── column5:5 48 ├── sum [as=sum:11] 49 │ └── column5:5 50 ├── std-dev [as=stddev:12] 51 │ └── column5:5 52 ├── variance [as=variance:13] 53 │ └── column5:5 54 ├── bool-and [as=bool_and:15] 55 │ └── column14:14 56 ├── bool-or [as=bool_or:17] 57 │ └── column16:16 58 └── xor-agg [as=xor_agg:19] 59 └── column18:18 60 61 build 62 SELECT min(v), max(v), count(v), sum_int(1), avg(v), sum(v), stddev(v), 63 variance(v), bool_and(v = 1), bool_and(v = 1), xor_agg(s::bytes) FROM kv 64 ---- 65 scalar-group-by 66 ├── columns: min:5 max:6 count:7!null sum_int:9 avg:10 sum:11 stddev:12 variance:13 bool_and:15 bool_and:15 xor_agg:17 67 ├── project 68 │ ├── columns: column8:8!null column14:14 column16:16 v:2 69 │ ├── scan kv 70 │ │ └── columns: k:1!null v:2 w:3 s:4 71 │ └── projections 72 │ ├── 1 [as=column8:8] 73 │ ├── v:2 = 1 [as=column14:14] 74 │ └── s:4::BYTES [as=column16:16] 75 └── aggregations 76 ├── min [as=min:5] 77 │ └── v:2 78 ├── max [as=max:6] 79 │ └── v:2 80 ├── count [as=count:7] 81 │ └── v:2 82 ├── sum-int [as=sum_int:9] 83 │ └── column8:8 84 ├── avg [as=avg:10] 85 │ └── v:2 86 ├── sum [as=sum:11] 87 │ └── v:2 88 ├── std-dev [as=stddev:12] 89 │ └── v:2 90 ├── variance [as=variance:13] 91 │ └── v:2 92 ├── bool-and [as=bool_and:15] 93 │ └── column14:14 94 └── xor-agg [as=xor_agg:17] 95 └── column16:16 96 97 build 98 SELECT min(1, 2) 99 ---- 100 error (42883): unknown signature: min(int, int) 101 102 build 103 SELECT min(1), count(1), max(1), sum_int(1), avg(1)::float, sum(1), stddev(1), 104 variance(1)::float, bool_and(true), bool_or(true), to_hex(xor_agg(b'\x01')) 105 ---- 106 project 107 ├── columns: min:2 count:3!null max:4 sum_int:5 avg:15 sum:7 stddev:8 variance:16 bool_and:11 bool_or:12 to_hex:17 108 ├── scalar-group-by 109 │ ├── columns: min:2 count:3!null max:4 sum_int:5 avg:6 sum:7 stddev:8 variance:9 bool_and:11 bool_or:12 xor_agg:14 110 │ ├── project 111 │ │ ├── columns: column1:1!null column10:10!null column13:13!null 112 │ │ ├── values 113 │ │ │ └── () 114 │ │ └── projections 115 │ │ ├── 1 [as=column1:1] 116 │ │ ├── true [as=column10:10] 117 │ │ └── '\x01' [as=column13:13] 118 │ └── aggregations 119 │ ├── min [as=min:2] 120 │ │ └── column1:1 121 │ ├── count [as=count:3] 122 │ │ └── column1:1 123 │ ├── max [as=max:4] 124 │ │ └── column1:1 125 │ ├── sum-int [as=sum_int:5] 126 │ │ └── column1:1 127 │ ├── avg [as=avg:6] 128 │ │ └── column1:1 129 │ ├── sum [as=sum:7] 130 │ │ └── column1:1 131 │ ├── std-dev [as=stddev:8] 132 │ │ └── column1:1 133 │ ├── variance [as=variance:9] 134 │ │ └── column1:1 135 │ ├── bool-and [as=bool_and:11] 136 │ │ └── column10:10 137 │ ├── bool-or [as=bool_or:12] 138 │ │ └── column10:10 139 │ └── xor-agg [as=xor_agg:14] 140 │ └── column13:13 141 └── projections 142 ├── avg:6::FLOAT8 [as=avg:15] 143 ├── variance:9::FLOAT8 [as=variance:16] 144 └── to_hex(xor_agg:14) [as=to_hex:17] 145 146 build 147 SELECT array_agg(1) FROM kv 148 ---- 149 scalar-group-by 150 ├── columns: array_agg:6 151 ├── project 152 │ ├── columns: column5:5!null 153 │ ├── scan kv 154 │ │ └── columns: k:1!null v:2 w:3 s:4 155 │ └── projections 156 │ └── 1 [as=column5:5] 157 └── aggregations 158 └── array-agg [as=array_agg:6] 159 └── column5:5 160 161 build 162 SELECT json_agg(v) FROM kv 163 ---- 164 scalar-group-by 165 ├── columns: json_agg:5 166 ├── project 167 │ ├── columns: v:2 168 │ └── scan kv 169 │ └── columns: k:1!null v:2 w:3 s:4 170 └── aggregations 171 └── json-agg [as=json_agg:5] 172 └── v:2 173 174 build 175 SELECT jsonb_agg(1) 176 ---- 177 scalar-group-by 178 ├── columns: jsonb_agg:2 179 ├── project 180 │ ├── columns: column1:1!null 181 │ ├── values 182 │ │ └── () 183 │ └── projections 184 │ └── 1 [as=column1:1] 185 └── aggregations 186 └── jsonb-agg [as=jsonb_agg:2] 187 └── column1:1 188 189 # Even with no aggregate functions, grouping occurs in the presence of GROUP BY. 190 build 191 SELECT 1 r FROM kv GROUP BY v 192 ---- 193 project 194 ├── columns: r:5!null 195 ├── group-by 196 │ ├── columns: v:2 197 │ ├── grouping columns: v:2 198 │ └── project 199 │ ├── columns: v:2 200 │ └── scan kv 201 │ └── columns: k:1!null v:2 w:3 s:4 202 └── projections 203 └── 1 [as=r:5] 204 205 # This should ideally return {NULL}, but this is a pathological case, and 206 # Postgres has the same behavior, so it's sufficient for now. 207 build 208 SELECT array_agg(NULL) 209 ---- 210 error (42725): ambiguous call: array_agg(unknown), candidates are: 211 array_agg(int) -> int[] 212 array_agg(float) -> float[] 213 array_agg(decimal) -> decimal[] 214 array_agg(date) -> date[] 215 array_agg(timestamp) -> timestamp[] 216 array_agg(interval) -> interval[] 217 array_agg(geography) -> geography[] 218 array_agg(geometry) -> geometry[] 219 array_agg(string) -> string[] 220 array_agg(bytes) -> bytes[] 221 array_agg(timestamptz) -> timestamptz[] 222 array_agg(oid) -> oid[] 223 array_agg(uuid) -> uuid[] 224 array_agg(inet) -> inet[] 225 array_agg(time) -> time[] 226 array_agg(timetz) -> timetz[] 227 array_agg(varbit) -> varbit[] 228 array_agg(bool) -> bool[] 229 230 # With an explicit cast, this works as expected. 231 build 232 SELECT array_agg(NULL::TEXT) 233 ---- 234 scalar-group-by 235 ├── columns: array_agg:2 236 ├── project 237 │ ├── columns: column1:1 238 │ ├── values 239 │ │ └── () 240 │ └── projections 241 │ └── NULL::STRING [as=column1:1] 242 └── aggregations 243 └── array-agg [as=array_agg:2] 244 └── column1:1 245 246 build 247 SELECT (SELECT COALESCE(max(1), 0) FROM kv) 248 ---- 249 project 250 ├── columns: coalesce:8 251 ├── values 252 │ └── () 253 └── projections 254 └── subquery [as=coalesce:8] 255 └── max1-row 256 ├── columns: coalesce:7 257 └── project 258 ├── columns: coalesce:7 259 ├── scalar-group-by 260 │ ├── columns: max:6 261 │ ├── project 262 │ │ ├── columns: column5:5!null 263 │ │ ├── scan kv 264 │ │ │ └── columns: k:1!null v:2 w:3 s:4 265 │ │ └── projections 266 │ │ └── 1 [as=column5:5] 267 │ └── aggregations 268 │ └── max [as=max:6] 269 │ └── column5:5 270 └── projections 271 └── COALESCE(max:6, 0) [as=coalesce:7] 272 273 build 274 SELECT count(*), k FROM kv 275 ---- 276 error (42803): column "k" must appear in the GROUP BY clause or be used in an aggregate function 277 278 build 279 SELECT count(*) FROM kv GROUP BY s < 5 280 ---- 281 error (22023): unsupported comparison operator: <string> < <int> 282 283 build 284 SELECT count(*), k FROM kv GROUP BY k 285 ---- 286 group-by 287 ├── columns: count:5!null k:1!null 288 ├── grouping columns: k:1!null 289 ├── project 290 │ ├── columns: k:1!null 291 │ └── scan kv 292 │ └── columns: k:1!null v:2 w:3 s:4 293 └── aggregations 294 └── count-rows [as=count_rows:5] 295 296 # GROUP BY specified using column index works. 297 build 298 SELECT count(*), k FROM kv GROUP BY 2 299 ---- 300 group-by 301 ├── columns: count:5!null k:1!null 302 ├── grouping columns: k:1!null 303 ├── project 304 │ ├── columns: k:1!null 305 │ └── scan kv 306 │ └── columns: k:1!null v:2 w:3 s:4 307 └── aggregations 308 └── count-rows [as=count_rows:5] 309 310 build 311 SELECT * FROM kv GROUP BY v, count(w) 312 ---- 313 error (42803): count(): aggregate functions are not allowed in GROUP BY 314 315 build 316 SELECT count(w) FROM kv GROUP BY 1 317 ---- 318 error (42803): count(): aggregate functions are not allowed in GROUP BY 319 320 build 321 SELECT sum(v) FROM kv GROUP BY k LIMIT sum(v) 322 ---- 323 error (42703): column "v" does not exist 324 325 build 326 SELECT sum(v) FROM kv GROUP BY k LIMIT 1 OFFSET sum(v) 327 ---- 328 error (42703): column "v" does not exist 329 330 build 331 VALUES (99, count(1)) 332 ---- 333 error (42803): count(): aggregate functions are not allowed in VALUES 334 335 build 336 SELECT count(*), k FROM kv GROUP BY 5 337 ---- 338 error (42P10): GROUP BY position 5 is not in select list 339 340 build 341 SELECT count(*), k FROM kv GROUP BY 0 342 ---- 343 error (42P10): GROUP BY position 0 is not in select list 344 345 build 346 SELECT 1 GROUP BY 'a' 347 ---- 348 error (42601): non-integer constant in GROUP BY: 'a' 349 350 # Qualifying a name in the SELECT, the GROUP BY, both or neither should not affect validation. 351 build 352 SELECT count(*), kv.s FROM kv GROUP BY s 353 ---- 354 group-by 355 ├── columns: count:5!null s:4 356 ├── grouping columns: s:4 357 ├── project 358 │ ├── columns: s:4 359 │ └── scan kv 360 │ └── columns: k:1!null v:2 w:3 s:4 361 └── aggregations 362 └── count-rows [as=count_rows:5] 363 364 build 365 SELECT count(*), s FROM kv GROUP BY kv.s 366 ---- 367 group-by 368 ├── columns: count:5!null s:4 369 ├── grouping columns: s:4 370 ├── project 371 │ ├── columns: s:4 372 │ └── scan kv 373 │ └── columns: k:1!null v:2 w:3 s:4 374 └── aggregations 375 └── count-rows [as=count_rows:5] 376 377 build 378 SELECT count(*), kv.s FROM kv GROUP BY kv.s 379 ---- 380 group-by 381 ├── columns: count:5!null s:4 382 ├── grouping columns: s:4 383 ├── project 384 │ ├── columns: s:4 385 │ └── scan kv 386 │ └── columns: k:1!null v:2 w:3 s:4 387 └── aggregations 388 └── count-rows [as=count_rows:5] 389 390 build 391 SELECT count(*), s FROM kv GROUP BY s 392 ---- 393 group-by 394 ├── columns: count:5!null s:4 395 ├── grouping columns: s:4 396 ├── project 397 │ ├── columns: s:4 398 │ └── scan kv 399 │ └── columns: k:1!null v:2 w:3 s:4 400 └── aggregations 401 └── count-rows [as=count_rows:5] 402 403 # Grouping by more than one column works. 404 build 405 SELECT v, count(*), w FROM kv GROUP BY v, w 406 ---- 407 group-by 408 ├── columns: v:2 count:5!null w:3 409 ├── grouping columns: v:2 w:3 410 ├── project 411 │ ├── columns: v:2 w:3 412 │ └── scan kv 413 │ └── columns: k:1!null v:2 w:3 s:4 414 └── aggregations 415 └── count-rows [as=count_rows:5] 416 417 # Grouping by more than one column using column numbers works. 418 build 419 SELECT v, count(*), w FROM kv GROUP BY 1, 3 420 ---- 421 group-by 422 ├── columns: v:2 count:5!null w:3 423 ├── grouping columns: v:2 w:3 424 ├── project 425 │ ├── columns: v:2 w:3 426 │ └── scan kv 427 │ └── columns: k:1!null v:2 w:3 s:4 428 └── aggregations 429 └── count-rows [as=count_rows:5] 430 431 # Selecting and grouping on a function expression works. 432 build 433 SELECT count(*), upper(s) FROM kv GROUP BY upper(s) 434 ---- 435 group-by 436 ├── columns: count:5!null upper:6 437 ├── grouping columns: column6:6 438 ├── project 439 │ ├── columns: column6:6 440 │ ├── scan kv 441 │ │ └── columns: k:1!null v:2 w:3 s:4 442 │ └── projections 443 │ └── upper(s:4) [as=column6:6] 444 └── aggregations 445 └── count-rows [as=count_rows:5] 446 447 # Selecting and grouping on a constant works. 448 build 449 SELECT count(*) FROM kv GROUP BY 1+2 450 ---- 451 project 452 ├── columns: count:5!null 453 └── group-by 454 ├── columns: count_rows:5!null column6:6!null 455 ├── grouping columns: column6:6!null 456 ├── project 457 │ ├── columns: column6:6!null 458 │ ├── scan kv 459 │ │ └── columns: k:1!null v:2 w:3 s:4 460 │ └── projections 461 │ └── 3 [as=column6:6] 462 └── aggregations 463 └── count-rows [as=count_rows:5] 464 465 build 466 SELECT count(*) FROM kv GROUP BY length('abc') 467 ---- 468 project 469 ├── columns: count:5!null 470 └── group-by 471 ├── columns: count_rows:5!null column6:6 472 ├── grouping columns: column6:6 473 ├── project 474 │ ├── columns: column6:6 475 │ ├── scan kv 476 │ │ └── columns: k:1!null v:2 w:3 s:4 477 │ └── projections 478 │ └── length('abc') [as=column6:6] 479 └── aggregations 480 └── count-rows [as=count_rows:5] 481 482 # Selecting a function of something which is grouped works. 483 build 484 SELECT count(*), upper(s) FROM kv GROUP BY s 485 ---- 486 project 487 ├── columns: count:5!null upper:6 488 ├── group-by 489 │ ├── columns: s:4 count_rows:5!null 490 │ ├── grouping columns: s:4 491 │ ├── project 492 │ │ ├── columns: s:4 493 │ │ └── scan kv 494 │ │ └── columns: k:1!null v:2 w:3 s:4 495 │ └── aggregations 496 │ └── count-rows [as=count_rows:5] 497 └── projections 498 └── upper(s:4) [as=upper:6] 499 500 # Selecting a value that is not grouped, even if a function of it it, does not work. 501 build 502 SELECT count(*), s FROM kv GROUP BY upper(s) 503 ---- 504 error (42803): column "s" must appear in the GROUP BY clause or be used in an aggregate function 505 506 # Selecting and grouping on a more complex expression works. 507 build 508 SELECT count(*), k+v AS r FROM kv GROUP BY k+v 509 ---- 510 group-by 511 ├── columns: count:5!null r:6 512 ├── grouping columns: column6:6 513 ├── project 514 │ ├── columns: column6:6 515 │ ├── scan kv 516 │ │ └── columns: k:1!null v:2 w:3 s:4 517 │ └── projections 518 │ └── k:1 + v:2 [as=column6:6] 519 └── aggregations 520 └── count-rows [as=count_rows:5] 521 522 523 # Selecting a more complex expression, made up of things which are each grouped, works. 524 build 525 SELECT count(*), k+v AS r FROM kv GROUP BY k, v 526 ---- 527 project 528 ├── columns: count:5!null r:6 529 ├── group-by 530 │ ├── columns: k:1!null v:2 count_rows:5!null 531 │ ├── grouping columns: k:1!null v:2 532 │ ├── project 533 │ │ ├── columns: k:1!null v:2 534 │ │ └── scan kv 535 │ │ └── columns: k:1!null v:2 w:3 s:4 536 │ └── aggregations 537 │ └── count-rows [as=count_rows:5] 538 └── projections 539 └── k:1 + v:2 [as=r:6] 540 541 build 542 SELECT count(*), k+v FROM kv GROUP BY v 543 ---- 544 error (42803): column "k" must appear in the GROUP BY clause or be used in an aggregate function 545 546 build 547 SELECT count(*), v/(k+v) FROM kv GROUP BY k+v 548 ---- 549 error (42803): column "v" must appear in the GROUP BY clause or be used in an aggregate function 550 551 build 552 SELECT k FROM kv WHERE avg(k) > 1 553 ---- 554 error (42803): aggregate functions are not allowed in WHERE 555 556 build 557 SELECT max(avg(k)) FROM kv 558 ---- 559 error (42803): max(): avg(): aggregate function calls cannot be nested 560 561 # Test case from #2761. 562 build 563 SELECT count(kv.k) AS count_1, kv.v + kv.w AS lx FROM kv GROUP BY kv.v + kv.w 564 ---- 565 group-by 566 ├── columns: count_1:5!null lx:6 567 ├── grouping columns: column6:6 568 ├── project 569 │ ├── columns: column6:6 k:1!null 570 │ ├── scan kv 571 │ │ └── columns: k:1!null v:2 w:3 s:4 572 │ └── projections 573 │ └── v:2 + w:3 [as=column6:6] 574 └── aggregations 575 └── count [as=count:5] 576 └── k:1 577 578 build 579 SELECT count(*) 580 ---- 581 scalar-group-by 582 ├── columns: count:1!null 583 ├── values 584 │ └── () 585 └── aggregations 586 └── count-rows [as=count_rows:1] 587 588 build 589 SELECT count(k) from kv 590 ---- 591 scalar-group-by 592 ├── columns: count:5!null 593 ├── project 594 │ ├── columns: k:1!null 595 │ └── scan kv 596 │ └── columns: k:1!null v:2 w:3 s:4 597 └── aggregations 598 └── count [as=count:5] 599 └── k:1 600 601 build 602 SELECT count(1) 603 ---- 604 scalar-group-by 605 ├── columns: count:2!null 606 ├── project 607 │ ├── columns: column1:1!null 608 │ ├── values 609 │ │ └── () 610 │ └── projections 611 │ └── 1 [as=column1:1] 612 └── aggregations 613 └── count [as=count:2] 614 └── column1:1 615 616 build 617 SELECT count(1) from kv 618 ---- 619 scalar-group-by 620 ├── columns: count:6!null 621 ├── project 622 │ ├── columns: column5:5!null 623 │ ├── scan kv 624 │ │ └── columns: k:1!null v:2 w:3 s:4 625 │ └── projections 626 │ └── 1 [as=column5:5] 627 └── aggregations 628 └── count [as=count:6] 629 └── column5:5 630 631 build 632 SELECT count(k, v) FROM kv 633 ---- 634 error (42883): unknown signature: count(int, int) 635 636 build 637 SELECT v, count(k) FROM kv GROUP BY v ORDER BY v 638 ---- 639 sort 640 ├── columns: v:2 count:5!null 641 ├── ordering: +2 642 └── group-by 643 ├── columns: v:2 count:5!null 644 ├── grouping columns: v:2 645 ├── project 646 │ ├── columns: k:1!null v:2 647 │ └── scan kv 648 │ └── columns: k:1!null v:2 w:3 s:4 649 └── aggregations 650 └── count [as=count:5] 651 └── k:1 652 653 build 654 SELECT v, count(k) FROM kv GROUP BY v ORDER BY v DESC 655 ---- 656 sort 657 ├── columns: v:2 count:5!null 658 ├── ordering: -2 659 └── group-by 660 ├── columns: v:2 count:5!null 661 ├── grouping columns: v:2 662 ├── project 663 │ ├── columns: k:1!null v:2 664 │ └── scan kv 665 │ └── columns: k:1!null v:2 w:3 s:4 666 └── aggregations 667 └── count [as=count:5] 668 └── k:1 669 670 build 671 SELECT v, count(k) FROM kv GROUP BY v ORDER BY count(k) DESC 672 ---- 673 sort 674 ├── columns: v:2 count:5!null 675 ├── ordering: -5 676 └── group-by 677 ├── columns: v:2 count:5!null 678 ├── grouping columns: v:2 679 ├── project 680 │ ├── columns: k:1!null v:2 681 │ └── scan kv 682 │ └── columns: k:1!null v:2 w:3 s:4 683 └── aggregations 684 └── count [as=count:5] 685 └── k:1 686 687 build 688 SELECT v, count(k) FROM kv GROUP BY v ORDER BY v-count(k) 689 ---- 690 sort 691 ├── columns: v:2 count:5!null [hidden: column6:6] 692 ├── ordering: +6 693 └── project 694 ├── columns: column6:6 v:2 count:5!null 695 ├── group-by 696 │ ├── columns: v:2 count:5!null 697 │ ├── grouping columns: v:2 698 │ ├── project 699 │ │ ├── columns: k:1!null v:2 700 │ │ └── scan kv 701 │ │ └── columns: k:1!null v:2 w:3 s:4 702 │ └── aggregations 703 │ └── count [as=count:5] 704 │ └── k:1 705 └── projections 706 └── v:2 - count:5 [as=column6:6] 707 708 build 709 SELECT v FROM kv GROUP BY v ORDER BY sum(k) 710 ---- 711 sort 712 ├── columns: v:2 [hidden: sum:5!null] 713 ├── ordering: +5 714 └── group-by 715 ├── columns: v:2 sum:5!null 716 ├── grouping columns: v:2 717 ├── project 718 │ ├── columns: k:1!null v:2 719 │ └── scan kv 720 │ └── columns: k:1!null v:2 w:3 s:4 721 └── aggregations 722 └── sum [as=sum:5] 723 └── k:1 724 725 build 726 SELECT v, count(k) FROM kv GROUP BY v ORDER BY 1 DESC 727 ---- 728 sort 729 ├── columns: v:2 count:5!null 730 ├── ordering: -2 731 └── group-by 732 ├── columns: v:2 count:5!null 733 ├── grouping columns: v:2 734 ├── project 735 │ ├── columns: k:1!null v:2 736 │ └── scan kv 737 │ └── columns: k:1!null v:2 w:3 s:4 738 └── aggregations 739 └── count [as=count:5] 740 └── k:1 741 742 build 743 SELECT count(*), count(k), count(kv.v) FROM kv 744 ---- 745 scalar-group-by 746 ├── columns: count:5!null count:6!null count:7!null 747 ├── project 748 │ ├── columns: k:1!null v:2 749 │ └── scan kv 750 │ └── columns: k:1!null v:2 w:3 s:4 751 └── aggregations 752 ├── count-rows [as=count_rows:5] 753 ├── count [as=count:6] 754 │ └── k:1 755 └── count [as=count:7] 756 └── v:2 757 758 build 759 SELECT count(kv.*) FROM kv 760 ---- 761 scalar-group-by 762 ├── columns: count:6!null 763 ├── project 764 │ ├── columns: column5:5 765 │ ├── scan kv 766 │ │ └── columns: k:1!null v:2 w:3 s:4 767 │ └── projections 768 │ └── ((k:1, v:2, w:3, s:4) AS k, v, w, s) [as=column5:5] 769 └── aggregations 770 └── count [as=count:6] 771 └── column5:5 772 773 build 774 SELECT count(DISTINCT k), count(DISTINCT v), count(DISTINCT (v)) FROM kv 775 ---- 776 scalar-group-by 777 ├── columns: count:5!null count:6!null count:6!null 778 ├── project 779 │ ├── columns: k:1!null v:2 780 │ └── scan kv 781 │ └── columns: k:1!null v:2 w:3 s:4 782 └── aggregations 783 ├── agg-distinct [as=count:5] 784 │ └── count 785 │ └── k:1 786 └── agg-distinct [as=count:6] 787 └── count 788 └── v:2 789 790 build 791 SELECT upper(s), count(DISTINCT k), count(DISTINCT v), count(DISTINCT (v)) FROM kv GROUP BY upper(s) 792 ---- 793 group-by 794 ├── columns: upper:7 count:5!null count:6!null count:6!null 795 ├── grouping columns: column7:7 796 ├── project 797 │ ├── columns: column7:7 k:1!null v:2 798 │ ├── scan kv 799 │ │ └── columns: k:1!null v:2 w:3 s:4 800 │ └── projections 801 │ └── upper(s:4) [as=column7:7] 802 └── aggregations 803 ├── agg-distinct [as=count:5] 804 │ └── count 805 │ └── k:1 806 └── agg-distinct [as=count:6] 807 └── count 808 └── v:2 809 810 build 811 SELECT count((k, v)) FROM kv 812 ---- 813 scalar-group-by 814 ├── columns: count:6!null 815 ├── project 816 │ ├── columns: column5:5 817 │ ├── scan kv 818 │ │ └── columns: k:1!null v:2 w:3 s:4 819 │ └── projections 820 │ └── (k:1, v:2) [as=column5:5] 821 └── aggregations 822 └── count [as=count:6] 823 └── column5:5 824 825 build 826 SELECT count(DISTINCT (k, v)) FROM kv 827 ---- 828 scalar-group-by 829 ├── columns: count:6!null 830 ├── project 831 │ ├── columns: column5:5 832 │ ├── scan kv 833 │ │ └── columns: k:1!null v:2 w:3 s:4 834 │ └── projections 835 │ └── (k:1, v:2) [as=column5:5] 836 └── aggregations 837 └── agg-distinct [as=count:6] 838 └── count 839 └── column5:5 840 841 build 842 SELECT count(DISTINCT (k, (v))) FROM kv 843 ---- 844 scalar-group-by 845 ├── columns: count:6!null 846 ├── project 847 │ ├── columns: column5:5 848 │ ├── scan kv 849 │ │ └── columns: k:1!null v:2 w:3 s:4 850 │ └── projections 851 │ └── (k:1, v:2) [as=column5:5] 852 └── aggregations 853 └── agg-distinct [as=count:6] 854 └── count 855 └── column5:5 856 857 build 858 SELECT count(*) FROM kv a, kv b 859 ---- 860 scalar-group-by 861 ├── columns: count:9!null 862 ├── project 863 │ └── inner-join (cross) 864 │ ├── columns: a.k:1!null a.v:2 a.w:3 a.s:4 b.k:5!null b.v:6 b.w:7 b.s:8 865 │ ├── scan a 866 │ │ └── columns: a.k:1!null a.v:2 a.w:3 a.s:4 867 │ ├── scan b 868 │ │ └── columns: b.k:5!null b.v:6 b.w:7 b.s:8 869 │ └── filters (true) 870 └── aggregations 871 └── count-rows [as=count_rows:9] 872 873 build 874 SELECT count((k, v)) FROM kv LIMIT 1 875 ---- 876 limit 877 ├── columns: count:6!null 878 ├── scalar-group-by 879 │ ├── columns: count:6!null 880 │ ├── limit hint: 1.00 881 │ ├── project 882 │ │ ├── columns: column5:5 883 │ │ ├── scan kv 884 │ │ │ └── columns: k:1!null v:2 w:3 s:4 885 │ │ └── projections 886 │ │ └── (k:1, v:2) [as=column5:5] 887 │ └── aggregations 888 │ └── count [as=count:6] 889 │ └── column5:5 890 └── 1 891 892 build 893 SELECT count((k, v)) FROM kv OFFSET 1 894 ---- 895 offset 896 ├── columns: count:6!null 897 ├── scalar-group-by 898 │ ├── columns: count:6!null 899 │ ├── project 900 │ │ ├── columns: column5:5 901 │ │ ├── scan kv 902 │ │ │ └── columns: k:1!null v:2 w:3 s:4 903 │ │ └── projections 904 │ │ └── (k:1, v:2) [as=column5:5] 905 │ └── aggregations 906 │ └── count [as=count:6] 907 │ └── column5:5 908 └── 1 909 910 build 911 SELECT count(k)+count(kv.v) AS r FROM kv 912 ---- 913 project 914 ├── columns: r:7!null 915 ├── scalar-group-by 916 │ ├── columns: count:5!null count:6!null 917 │ ├── project 918 │ │ ├── columns: k:1!null v:2 919 │ │ └── scan kv 920 │ │ └── columns: k:1!null v:2 w:3 s:4 921 │ └── aggregations 922 │ ├── count [as=count:5] 923 │ │ └── k:1 924 │ └── count [as=count:6] 925 │ └── v:2 926 └── projections 927 └── count:5 + count:6 [as=r:7] 928 929 build 930 SELECT count(NULL::int), count((NULL, NULL)) 931 ---- 932 scalar-group-by 933 ├── columns: count:2!null count:4!null 934 ├── project 935 │ ├── columns: column1:1 column3:3 936 │ ├── values 937 │ │ └── () 938 │ └── projections 939 │ ├── NULL::INT8 [as=column1:1] 940 │ └── (NULL, NULL) [as=column3:3] 941 └── aggregations 942 ├── count [as=count:2] 943 │ └── column1:1 944 └── count [as=count:4] 945 └── column3:3 946 947 build 948 SELECT min(k), max(k), min(v), max(v) FROM kv 949 ---- 950 scalar-group-by 951 ├── columns: min:5 max:6 min:7 max:8 952 ├── project 953 │ ├── columns: k:1!null v:2 954 │ └── scan kv 955 │ └── columns: k:1!null v:2 w:3 s:4 956 └── aggregations 957 ├── min [as=min:5] 958 │ └── k:1 959 ├── max [as=max:6] 960 │ └── k:1 961 ├── min [as=min:7] 962 │ └── v:2 963 └── max [as=max:8] 964 └── v:2 965 966 build 967 SELECT min(k), max(k), min(v), max(v) FROM kv WHERE k > 8 968 ---- 969 scalar-group-by 970 ├── columns: min:5 max:6 min:7 max:8 971 ├── project 972 │ ├── columns: k:1!null v:2 973 │ └── select 974 │ ├── columns: k:1!null v:2 w:3 s:4 975 │ ├── scan kv 976 │ │ └── columns: k:1!null v:2 w:3 s:4 977 │ └── filters 978 │ └── k:1 > 8 979 └── aggregations 980 ├── min [as=min:5] 981 │ └── k:1 982 ├── max [as=max:6] 983 │ └── k:1 984 ├── min [as=min:7] 985 │ └── v:2 986 └── max [as=max:8] 987 └── v:2 988 989 build 990 SELECT array_agg(k), array_agg(s) FROM (SELECT k, s FROM kv ORDER BY k) 991 ---- 992 scalar-group-by 993 ├── columns: array_agg:5 array_agg:6 994 ├── internal-ordering: +1 995 ├── project 996 │ ├── columns: k:1!null s:4 997 │ ├── ordering: +1 998 │ └── scan kv 999 │ ├── columns: k:1!null v:2 w:3 s:4 1000 │ └── ordering: +1 1001 └── aggregations 1002 ├── array-agg [as=array_agg:5] 1003 │ └── k:1 1004 └── array-agg [as=array_agg:6] 1005 └── s:4 1006 1007 build 1008 SELECT array_agg(k) FROM (SELECT k FROM kv ORDER BY s) 1009 ---- 1010 scalar-group-by 1011 ├── columns: array_agg:5 1012 ├── internal-ordering: +4 1013 ├── sort 1014 │ ├── columns: k:1!null s:4 1015 │ ├── ordering: +4 1016 │ └── project 1017 │ ├── columns: k:1!null s:4 1018 │ └── scan kv 1019 │ └── columns: k:1!null v:2 w:3 s:4 1020 └── aggregations 1021 └── array-agg [as=array_agg:5] 1022 └── k:1 1023 1024 build 1025 SELECT max(k) FROM (SELECT k FROM kv ORDER BY s) 1026 ---- 1027 scalar-group-by 1028 ├── columns: max:5 1029 ├── project 1030 │ ├── columns: k:1!null 1031 │ └── project 1032 │ ├── columns: k:1!null s:4 1033 │ └── scan kv 1034 │ └── columns: k:1!null v:2 w:3 s:4 1035 └── aggregations 1036 └── max [as=max:5] 1037 └── k:1 1038 1039 1040 build 1041 SELECT array_agg(k) || 1 FROM (SELECT k FROM kv ORDER BY s) 1042 ---- 1043 project 1044 ├── columns: "?column?":6 1045 ├── scalar-group-by 1046 │ ├── columns: array_agg:5 1047 │ ├── internal-ordering: +4 1048 │ ├── sort 1049 │ │ ├── columns: k:1!null s:4 1050 │ │ ├── ordering: +4 1051 │ │ └── project 1052 │ │ ├── columns: k:1!null s:4 1053 │ │ └── scan kv 1054 │ │ └── columns: k:1!null v:2 w:3 s:4 1055 │ └── aggregations 1056 │ └── array-agg [as=array_agg:5] 1057 │ └── k:1 1058 └── projections 1059 └── array_agg:5 || 1 [as="?column?":6] 1060 1061 build 1062 SELECT array_agg(s) FROM kv WHERE s IS NULL 1063 ---- 1064 scalar-group-by 1065 ├── columns: array_agg:5 1066 ├── project 1067 │ ├── columns: s:4 1068 │ └── select 1069 │ ├── columns: k:1!null v:2 w:3 s:4 1070 │ ├── scan kv 1071 │ │ └── columns: k:1!null v:2 w:3 s:4 1072 │ └── filters 1073 │ └── s:4 IS NULL 1074 └── aggregations 1075 └── array-agg [as=array_agg:5] 1076 └── s:4 1077 1078 build 1079 SELECT avg(k), avg(v), sum(k), sum(v) FROM kv 1080 ---- 1081 scalar-group-by 1082 ├── columns: avg:5 avg:6 sum:7 sum:8 1083 ├── project 1084 │ ├── columns: k:1!null v:2 1085 │ └── scan kv 1086 │ └── columns: k:1!null v:2 w:3 s:4 1087 └── aggregations 1088 ├── avg [as=avg:5] 1089 │ └── k:1 1090 ├── avg [as=avg:6] 1091 │ └── v:2 1092 ├── sum [as=sum:7] 1093 │ └── k:1 1094 └── sum [as=sum:8] 1095 └── v:2 1096 1097 build 1098 SELECT avg(k::decimal), avg(v::decimal), sum(k::decimal), sum(v::decimal) FROM kv 1099 ---- 1100 scalar-group-by 1101 ├── columns: avg:6 avg:8 sum:9 sum:10 1102 ├── project 1103 │ ├── columns: column5:5!null column7:7 1104 │ ├── scan kv 1105 │ │ └── columns: k:1!null v:2 w:3 s:4 1106 │ └── projections 1107 │ ├── k:1::DECIMAL [as=column5:5] 1108 │ └── v:2::DECIMAL [as=column7:7] 1109 └── aggregations 1110 ├── avg [as=avg:6] 1111 │ └── column5:5 1112 ├── avg [as=avg:8] 1113 │ └── column7:7 1114 ├── sum [as=sum:9] 1115 │ └── column5:5 1116 └── sum [as=sum:10] 1117 └── column7:7 1118 1119 build 1120 SELECT avg(DISTINCT k), avg(DISTINCT v), sum(DISTINCT k), sum(DISTINCT v) FROM kv 1121 ---- 1122 scalar-group-by 1123 ├── columns: avg:5 avg:6 sum:7 sum:8 1124 ├── project 1125 │ ├── columns: k:1!null v:2 1126 │ └── scan kv 1127 │ └── columns: k:1!null v:2 w:3 s:4 1128 └── aggregations 1129 ├── agg-distinct [as=avg:5] 1130 │ └── avg 1131 │ └── k:1 1132 ├── agg-distinct [as=avg:6] 1133 │ └── avg 1134 │ └── v:2 1135 ├── agg-distinct [as=sum:7] 1136 │ └── sum 1137 │ └── k:1 1138 └── agg-distinct [as=sum:8] 1139 └── sum 1140 └── v:2 1141 1142 build 1143 SELECT avg(k) * 2.0 + max(v)::DECIMAL AS r FROM kv 1144 ---- 1145 project 1146 ├── columns: r:7 1147 ├── scalar-group-by 1148 │ ├── columns: avg:5 max:6 1149 │ ├── project 1150 │ │ ├── columns: k:1!null v:2 1151 │ │ └── scan kv 1152 │ │ └── columns: k:1!null v:2 w:3 s:4 1153 │ └── aggregations 1154 │ ├── avg [as=avg:5] 1155 │ │ └── k:1 1156 │ └── max [as=max:6] 1157 │ └── v:2 1158 └── projections 1159 └── (avg:5 * 2.0) + max:6::DECIMAL [as=r:7] 1160 1161 build 1162 SELECT avg(k) * 2.0 + max(v)::DECIMAL AS r FROM kv WHERE w*2 = k 1163 ---- 1164 project 1165 ├── columns: r:7 1166 ├── scalar-group-by 1167 │ ├── columns: avg:5 max:6 1168 │ ├── project 1169 │ │ ├── columns: k:1!null v:2 1170 │ │ └── select 1171 │ │ ├── columns: k:1!null v:2 w:3 s:4 1172 │ │ ├── scan kv 1173 │ │ │ └── columns: k:1!null v:2 w:3 s:4 1174 │ │ └── filters 1175 │ │ └── (w:3 * 2) = k:1 1176 │ └── aggregations 1177 │ ├── avg [as=avg:5] 1178 │ │ └── k:1 1179 │ └── max [as=max:6] 1180 │ └── v:2 1181 └── projections 1182 └── (avg:5 * 2.0) + max:6::DECIMAL [as=r:7] 1183 1184 exec-ddl 1185 CREATE TABLE abc ( 1186 a CHAR PRIMARY KEY, 1187 b FLOAT, 1188 c BOOLEAN, 1189 d DECIMAL 1190 ) 1191 ---- 1192 1193 build 1194 SELECT min(a), min(b), min(c), min(d) FROM abc 1195 ---- 1196 scalar-group-by 1197 ├── columns: min:5 min:6 min:7 min:8 1198 ├── scan abc 1199 │ └── columns: a:1!null b:2 c:3 d:4 1200 └── aggregations 1201 ├── min [as=min:5] 1202 │ └── a:1 1203 ├── min [as=min:6] 1204 │ └── b:2 1205 ├── min [as=min:7] 1206 │ └── c:3 1207 └── min [as=min:8] 1208 └── d:4 1209 1210 build 1211 SELECT max(a), max(b), max(c), max(d) FROM abc 1212 ---- 1213 scalar-group-by 1214 ├── columns: max:5 max:6 max:7 max:8 1215 ├── scan abc 1216 │ └── columns: a:1!null b:2 c:3 d:4 1217 └── aggregations 1218 ├── max [as=max:5] 1219 │ └── a:1 1220 ├── max [as=max:6] 1221 │ └── b:2 1222 ├── max [as=max:7] 1223 │ └── c:3 1224 └── max [as=max:8] 1225 └── d:4 1226 1227 build 1228 SELECT avg(b), sum(b), avg(d), sum(d) FROM abc 1229 ---- 1230 scalar-group-by 1231 ├── columns: avg:5 sum:6 avg:7 sum:8 1232 ├── project 1233 │ ├── columns: b:2 d:4 1234 │ └── scan abc 1235 │ └── columns: a:1!null b:2 c:3 d:4 1236 └── aggregations 1237 ├── avg [as=avg:5] 1238 │ └── b:2 1239 ├── sum [as=sum:6] 1240 │ └── b:2 1241 ├── avg [as=avg:7] 1242 │ └── d:4 1243 └── sum [as=sum:8] 1244 └── d:4 1245 1246 # Verify summing of intervals 1247 exec-ddl 1248 CREATE TABLE intervals ( 1249 a INTERVAL PRIMARY KEY 1250 ) 1251 ---- 1252 1253 build 1254 SELECT sum(a) FROM intervals 1255 ---- 1256 scalar-group-by 1257 ├── columns: sum:2 1258 ├── scan intervals 1259 │ └── columns: a:1!null 1260 └── aggregations 1261 └── sum [as=sum:2] 1262 └── a:1 1263 1264 build 1265 SELECT avg(a) FROM abc 1266 ---- 1267 error (42883): unknown signature: avg(char) 1268 1269 build 1270 SELECT avg(c) FROM abc 1271 ---- 1272 error (42883): unknown signature: avg(bool) 1273 1274 build 1275 SELECT avg((a,c)) FROM abc 1276 ---- 1277 error (42883): unknown signature: avg(tuple{char, bool}) 1278 1279 build 1280 SELECT sum(a) FROM abc 1281 ---- 1282 error (42883): unknown signature: sum(char) 1283 1284 build 1285 SELECT sum(c) FROM abc 1286 ---- 1287 error (42883): unknown signature: sum(bool) 1288 1289 build 1290 SELECT sum((a,c)) FROM abc 1291 ---- 1292 error (42883): unknown signature: sum(tuple{char, bool}) 1293 1294 exec-ddl 1295 CREATE TABLE xyz ( 1296 x INT PRIMARY KEY, 1297 y INT, 1298 z FLOAT, 1299 INDEX xy (x, y), 1300 INDEX zyx (z, y, x), 1301 FAMILY (x), 1302 FAMILY (y), 1303 FAMILY (z) 1304 ) 1305 ---- 1306 1307 build 1308 SELECT min(x) FROM xyz 1309 ---- 1310 scalar-group-by 1311 ├── columns: min:4 1312 ├── project 1313 │ ├── columns: x:1!null 1314 │ └── scan xyz 1315 │ └── columns: x:1!null y:2 z:3 1316 └── aggregations 1317 └── min [as=min:4] 1318 └── x:1 1319 1320 build 1321 SELECT min(x) FROM xyz WHERE x in (0, 4, 7) 1322 ---- 1323 scalar-group-by 1324 ├── columns: min:4 1325 ├── project 1326 │ ├── columns: x:1!null 1327 │ └── select 1328 │ ├── columns: x:1!null y:2 z:3 1329 │ ├── scan xyz 1330 │ │ └── columns: x:1!null y:2 z:3 1331 │ └── filters 1332 │ └── x:1 IN (0, 4, 7) 1333 └── aggregations 1334 └── min [as=min:4] 1335 └── x:1 1336 1337 build 1338 SELECT max(x) FROM xyz 1339 ---- 1340 scalar-group-by 1341 ├── columns: max:4 1342 ├── project 1343 │ ├── columns: x:1!null 1344 │ └── scan xyz 1345 │ └── columns: x:1!null y:2 z:3 1346 └── aggregations 1347 └── max [as=max:4] 1348 └── x:1 1349 1350 build 1351 SELECT max(y) FROM xyz WHERE x = 1 1352 ---- 1353 scalar-group-by 1354 ├── columns: max:4 1355 ├── project 1356 │ ├── columns: y:2 1357 │ └── select 1358 │ ├── columns: x:1!null y:2 z:3 1359 │ ├── scan xyz 1360 │ │ └── columns: x:1!null y:2 z:3 1361 │ └── filters 1362 │ └── x:1 = 1 1363 └── aggregations 1364 └── max [as=max:4] 1365 └── y:2 1366 1367 build 1368 SELECT min(y) FROM xyz WHERE x = 7 1369 ---- 1370 scalar-group-by 1371 ├── columns: min:4 1372 ├── project 1373 │ ├── columns: y:2 1374 │ └── select 1375 │ ├── columns: x:1!null y:2 z:3 1376 │ ├── scan xyz 1377 │ │ └── columns: x:1!null y:2 z:3 1378 │ └── filters 1379 │ └── x:1 = 7 1380 └── aggregations 1381 └── min [as=min:4] 1382 └── y:2 1383 1384 build 1385 SELECT min(x) FROM xyz WHERE (y, z) = (2, 3.0) 1386 ---- 1387 scalar-group-by 1388 ├── columns: min:4 1389 ├── project 1390 │ ├── columns: x:1!null 1391 │ └── select 1392 │ ├── columns: x:1!null y:2 z:3 1393 │ ├── scan xyz 1394 │ │ └── columns: x:1!null y:2 z:3 1395 │ └── filters 1396 │ └── (y:2, z:3) = (2, 3.0) 1397 └── aggregations 1398 └── min [as=min:4] 1399 └── x:1 1400 1401 build 1402 SELECT max(x) FROM xyz WHERE (z, y) = (3.0, 2) 1403 ---- 1404 scalar-group-by 1405 ├── columns: max:4 1406 ├── project 1407 │ ├── columns: x:1!null 1408 │ └── select 1409 │ ├── columns: x:1!null y:2 z:3 1410 │ ├── scan xyz 1411 │ │ └── columns: x:1!null y:2 z:3 1412 │ └── filters 1413 │ └── (z:3, y:2) = (3.0, 2) 1414 └── aggregations 1415 └── max [as=max:4] 1416 └── x:1 1417 1418 1419 # VARIANCE/STDDEV 1420 1421 build 1422 SELECT variance(x), variance(y::decimal), round(variance(z), 14) FROM xyz 1423 ---- 1424 project 1425 ├── columns: variance:4 variance:6 round:8 1426 ├── scalar-group-by 1427 │ ├── columns: variance:4 variance:6 variance:7 1428 │ ├── project 1429 │ │ ├── columns: column5:5 x:1!null z:3 1430 │ │ ├── scan xyz 1431 │ │ │ └── columns: x:1!null y:2 z:3 1432 │ │ └── projections 1433 │ │ └── y:2::DECIMAL [as=column5:5] 1434 │ └── aggregations 1435 │ ├── variance [as=variance:4] 1436 │ │ └── x:1 1437 │ ├── variance [as=variance:6] 1438 │ │ └── column5:5 1439 │ └── variance [as=variance:7] 1440 │ └── z:3 1441 └── projections 1442 └── round(variance:7, 14) [as=round:8] 1443 1444 build 1445 SELECT variance(x) FROM xyz WHERE x = 10 1446 ---- 1447 scalar-group-by 1448 ├── columns: variance:4 1449 ├── project 1450 │ ├── columns: x:1!null 1451 │ └── select 1452 │ ├── columns: x:1!null y:2 z:3 1453 │ ├── scan xyz 1454 │ │ └── columns: x:1!null y:2 z:3 1455 │ └── filters 1456 │ └── x:1 = 10 1457 └── aggregations 1458 └── variance [as=variance:4] 1459 └── x:1 1460 1461 build 1462 SELECT stddev(x), stddev(y::decimal), round(stddev(z), 14) FROM xyz 1463 ---- 1464 project 1465 ├── columns: stddev:4 stddev:6 round:8 1466 ├── scalar-group-by 1467 │ ├── columns: stddev:4 stddev:6 stddev:7 1468 │ ├── project 1469 │ │ ├── columns: column5:5 x:1!null z:3 1470 │ │ ├── scan xyz 1471 │ │ │ └── columns: x:1!null y:2 z:3 1472 │ │ └── projections 1473 │ │ └── y:2::DECIMAL [as=column5:5] 1474 │ └── aggregations 1475 │ ├── std-dev [as=stddev:4] 1476 │ │ └── x:1 1477 │ ├── std-dev [as=stddev:6] 1478 │ │ └── column5:5 1479 │ └── std-dev [as=stddev:7] 1480 │ └── z:3 1481 └── projections 1482 └── round(stddev:7, 14) [as=round:8] 1483 1484 build 1485 SELECT stddev(x) FROM xyz WHERE x = 1 1486 ---- 1487 scalar-group-by 1488 ├── columns: stddev:4 1489 ├── project 1490 │ ├── columns: x:1!null 1491 │ └── select 1492 │ ├── columns: x:1!null y:2 z:3 1493 │ ├── scan xyz 1494 │ │ └── columns: x:1!null y:2 z:3 1495 │ └── filters 1496 │ └── x:1 = 1 1497 └── aggregations 1498 └── std-dev [as=stddev:4] 1499 └── x:1 1500 1501 build 1502 SELECT avg(1::int)::float, avg(2::float)::float, avg(3::decimal)::float 1503 ---- 1504 project 1505 ├── columns: avg:7 avg:8 avg:9 1506 ├── scalar-group-by 1507 │ ├── columns: avg:2 avg:4 avg:6 1508 │ ├── project 1509 │ │ ├── columns: column1:1!null column3:3!null column5:5!null 1510 │ │ ├── values 1511 │ │ │ └── () 1512 │ │ └── projections 1513 │ │ ├── 1::INT8 [as=column1:1] 1514 │ │ ├── 2.0::FLOAT8 [as=column3:3] 1515 │ │ └── 3::DECIMAL [as=column5:5] 1516 │ └── aggregations 1517 │ ├── avg [as=avg:2] 1518 │ │ └── column1:1 1519 │ ├── avg [as=avg:4] 1520 │ │ └── column3:3 1521 │ └── avg [as=avg:6] 1522 │ └── column5:5 1523 └── projections 1524 ├── avg:2::FLOAT8 [as=avg:7] 1525 ├── avg:4::FLOAT8 [as=avg:8] 1526 └── avg:6::FLOAT8 [as=avg:9] 1527 1528 build 1529 SELECT count(2::int), count(3::float), count(4::decimal) 1530 ---- 1531 scalar-group-by 1532 ├── columns: count:2!null count:4!null count:6!null 1533 ├── project 1534 │ ├── columns: column1:1!null column3:3!null column5:5!null 1535 │ ├── values 1536 │ │ └── () 1537 │ └── projections 1538 │ ├── 2::INT8 [as=column1:1] 1539 │ ├── 3.0::FLOAT8 [as=column3:3] 1540 │ └── 4::DECIMAL [as=column5:5] 1541 └── aggregations 1542 ├── count [as=count:2] 1543 │ └── column1:1 1544 ├── count [as=count:4] 1545 │ └── column3:3 1546 └── count [as=count:6] 1547 └── column5:5 1548 1549 build 1550 SELECT sum(1::int), sum(2::float), sum(3::decimal) 1551 ---- 1552 scalar-group-by 1553 ├── columns: sum:2 sum:4 sum:6 1554 ├── project 1555 │ ├── columns: column1:1!null column3:3!null column5:5!null 1556 │ ├── values 1557 │ │ └── () 1558 │ └── projections 1559 │ ├── 1::INT8 [as=column1:1] 1560 │ ├── 2.0::FLOAT8 [as=column3:3] 1561 │ └── 3::DECIMAL [as=column5:5] 1562 └── aggregations 1563 ├── sum [as=sum:2] 1564 │ └── column1:1 1565 ├── sum [as=sum:4] 1566 │ └── column3:3 1567 └── sum [as=sum:6] 1568 └── column5:5 1569 1570 build 1571 SELECT variance(1::int), variance(1::float), variance(1::decimal) 1572 ---- 1573 scalar-group-by 1574 ├── columns: variance:2 variance:4 variance:6 1575 ├── project 1576 │ ├── columns: column1:1!null column3:3!null column5:5!null 1577 │ ├── values 1578 │ │ └── () 1579 │ └── projections 1580 │ ├── 1::INT8 [as=column1:1] 1581 │ ├── 1.0::FLOAT8 [as=column3:3] 1582 │ └── 1::DECIMAL [as=column5:5] 1583 └── aggregations 1584 ├── variance [as=variance:2] 1585 │ └── column1:1 1586 ├── variance [as=variance:4] 1587 │ └── column3:3 1588 └── variance [as=variance:6] 1589 └── column5:5 1590 1591 build 1592 SELECT stddev(1::int), stddev(1::float), stddev(1::decimal) 1593 ---- 1594 scalar-group-by 1595 ├── columns: stddev:2 stddev:4 stddev:6 1596 ├── project 1597 │ ├── columns: column1:1!null column3:3!null column5:5!null 1598 │ ├── values 1599 │ │ └── () 1600 │ └── projections 1601 │ ├── 1::INT8 [as=column1:1] 1602 │ ├── 1.0::FLOAT8 [as=column3:3] 1603 │ └── 1::DECIMAL [as=column5:5] 1604 └── aggregations 1605 ├── std-dev [as=stddev:2] 1606 │ └── column1:1 1607 ├── std-dev [as=stddev:4] 1608 │ └── column3:3 1609 └── std-dev [as=stddev:6] 1610 └── column5:5 1611 1612 # Ensure subqueries don't trigger aggregation. 1613 build 1614 SELECT x > (SELECT avg(0)) AS r FROM xyz LIMIT 1 1615 ---- 1616 limit 1617 ├── columns: r:6 1618 ├── project 1619 │ ├── columns: r:6 1620 │ ├── limit hint: 1.00 1621 │ ├── scan xyz 1622 │ │ ├── columns: x:1!null y:2 z:3 1623 │ │ └── limit hint: 1.00 1624 │ └── projections 1625 │ └── gt [as=r:6] 1626 │ ├── x:1 1627 │ └── subquery 1628 │ └── max1-row 1629 │ ├── columns: avg:5 1630 │ └── scalar-group-by 1631 │ ├── columns: avg:5 1632 │ ├── project 1633 │ │ ├── columns: column4:4!null 1634 │ │ ├── values 1635 │ │ │ └── () 1636 │ │ └── projections 1637 │ │ └── 0 [as=column4:4] 1638 │ └── aggregations 1639 │ └── avg [as=avg:5] 1640 │ └── column4:4 1641 └── 1 1642 1643 build 1644 SELECT x > (SELECT avg(y) FROM xyz) AS r FROM xyz LIMIT 1 1645 ---- 1646 limit 1647 ├── columns: r:8 1648 ├── project 1649 │ ├── columns: r:8 1650 │ ├── limit hint: 1.00 1651 │ ├── scan xyz 1652 │ │ ├── columns: x:1!null y:2 z:3 1653 │ │ └── limit hint: 1.00 1654 │ └── projections 1655 │ └── gt [as=r:8] 1656 │ ├── x:1 1657 │ └── subquery 1658 │ └── max1-row 1659 │ ├── columns: avg:7 1660 │ └── scalar-group-by 1661 │ ├── columns: avg:7 1662 │ ├── project 1663 │ │ ├── columns: y:5 1664 │ │ └── scan xyz 1665 │ │ └── columns: x:4!null y:5 z:6 1666 │ └── aggregations 1667 │ └── avg [as=avg:7] 1668 │ └── y:5 1669 └── 1 1670 1671 exec-ddl 1672 CREATE TABLE bools (b BOOL) 1673 ---- 1674 1675 build 1676 SELECT bool_and(b), bool_or(b) FROM bools 1677 ---- 1678 scalar-group-by 1679 ├── columns: bool_and:3 bool_or:4 1680 ├── project 1681 │ ├── columns: b:1 1682 │ └── scan bools 1683 │ └── columns: b:1 rowid:2!null 1684 └── aggregations 1685 ├── bool-and [as=bool_and:3] 1686 │ └── b:1 1687 └── bool-or [as=bool_or:4] 1688 └── b:1 1689 1690 1691 # Tests with * inside GROUP BY. 1692 build 1693 SELECT 1 r FROM kv GROUP BY kv.*; 1694 ---- 1695 project 1696 ├── columns: r:5!null 1697 ├── group-by 1698 │ ├── columns: k:1!null v:2 w:3 s:4 1699 │ ├── grouping columns: k:1!null v:2 w:3 s:4 1700 │ └── scan kv 1701 │ └── columns: k:1!null v:2 w:3 s:4 1702 └── projections 1703 └── 1 [as=r:5] 1704 1705 exec-ddl 1706 CREATE TABLE xor_bytes (a bytes, b int, c int) 1707 ---- 1708 1709 build 1710 SELECT to_hex(xor_agg(a)), xor_agg(c) FROM xor_bytes 1711 ---- 1712 project 1713 ├── columns: to_hex:7 xor_agg:6 1714 ├── scalar-group-by 1715 │ ├── columns: xor_agg:5 xor_agg:6 1716 │ ├── project 1717 │ │ ├── columns: a:1 c:3 1718 │ │ └── scan xor_bytes 1719 │ │ └── columns: a:1 b:2 c:3 rowid:4!null 1720 │ └── aggregations 1721 │ ├── xor-agg [as=xor_agg:5] 1722 │ │ └── a:1 1723 │ └── xor-agg [as=xor_agg:6] 1724 │ └── c:3 1725 └── projections 1726 └── to_hex(xor_agg:5) [as=to_hex:7] 1727 1728 build 1729 SELECT to_hex(xor_agg(a)), b, xor_agg(c) FROM xor_bytes GROUP BY b ORDER BY b 1730 ---- 1731 sort 1732 ├── columns: to_hex:7 b:2 xor_agg:6 1733 ├── ordering: +2 1734 └── project 1735 ├── columns: to_hex:7 b:2 xor_agg:6 1736 ├── group-by 1737 │ ├── columns: b:2 xor_agg:5 xor_agg:6 1738 │ ├── grouping columns: b:2 1739 │ ├── project 1740 │ │ ├── columns: a:1 b:2 c:3 1741 │ │ └── scan xor_bytes 1742 │ │ └── columns: a:1 b:2 c:3 rowid:4!null 1743 │ └── aggregations 1744 │ ├── xor-agg [as=xor_agg:5] 1745 │ │ └── a:1 1746 │ └── xor-agg [as=xor_agg:6] 1747 │ └── c:3 1748 └── projections 1749 └── to_hex(xor_agg:5) [as=to_hex:7] 1750 1751 # At execution time, this query will cause the error: 1752 # "arguments to xor must all be the same length" 1753 build 1754 SELECT xor_agg(i) FROM (VALUES (b'\x01'), (b'\x01\x01')) AS a(i) 1755 ---- 1756 scalar-group-by 1757 ├── columns: xor_agg:2 1758 ├── values 1759 │ ├── columns: column1:1!null 1760 │ ├── ('\x01',) 1761 │ └── ('\x0101',) 1762 └── aggregations 1763 └── xor-agg [as=xor_agg:2] 1764 └── column1:1 1765 1766 build 1767 SELECT max(true), min(true) 1768 ---- 1769 scalar-group-by 1770 ├── columns: max:2 min:3 1771 ├── project 1772 │ ├── columns: column1:1!null 1773 │ ├── values 1774 │ │ └── () 1775 │ └── projections 1776 │ └── true [as=column1:1] 1777 └── aggregations 1778 ├── max [as=max:2] 1779 │ └── column1:1 1780 └── min [as=min:3] 1781 └── column1:1 1782 1783 build 1784 SELECT concat_agg(s) FROM (SELECT s FROM kv ORDER BY k) 1785 ---- 1786 scalar-group-by 1787 ├── columns: concat_agg:5 1788 ├── internal-ordering: +1 1789 ├── project 1790 │ ├── columns: k:1!null s:4 1791 │ ├── ordering: +1 1792 │ └── scan kv 1793 │ ├── columns: k:1!null v:2 w:3 s:4 1794 │ └── ordering: +1 1795 └── aggregations 1796 └── concat-agg [as=concat_agg:5] 1797 └── s:4 1798 1799 build 1800 SELECT json_agg(s) FROM (SELECT s FROM kv ORDER BY k) 1801 ---- 1802 scalar-group-by 1803 ├── columns: json_agg:5 1804 ├── internal-ordering: +1 1805 ├── project 1806 │ ├── columns: k:1!null s:4 1807 │ ├── ordering: +1 1808 │ └── scan kv 1809 │ ├── columns: k:1!null v:2 w:3 s:4 1810 │ └── ordering: +1 1811 └── aggregations 1812 └── json-agg [as=json_agg:5] 1813 └── s:4 1814 1815 build 1816 SELECT jsonb_agg(s) FROM (SELECT s FROM kv ORDER BY k) 1817 ---- 1818 scalar-group-by 1819 ├── columns: jsonb_agg:5 1820 ├── internal-ordering: +1 1821 ├── project 1822 │ ├── columns: k:1!null s:4 1823 │ ├── ordering: +1 1824 │ └── scan kv 1825 │ ├── columns: k:1!null v:2 w:3 s:4 1826 │ └── ordering: +1 1827 └── aggregations 1828 └── jsonb-agg [as=jsonb_agg:5] 1829 └── s:4 1830 1831 exec-ddl 1832 CREATE TABLE ab ( 1833 a INT PRIMARY KEY, 1834 b INT, 1835 FAMILY (a), 1836 FAMILY (b) 1837 ) 1838 ---- 1839 1840 exec-ddl 1841 CREATE TABLE xy(x STRING, y STRING); 1842 ---- 1843 1844 # Grouping and rendering tuples. 1845 build 1846 SELECT (b, a) AS r FROM ab GROUP BY (b, a) 1847 ---- 1848 project 1849 ├── columns: r:3 1850 ├── group-by 1851 │ ├── columns: a:1!null b:2 1852 │ ├── grouping columns: a:1!null b:2 1853 │ └── scan ab 1854 │ └── columns: a:1!null b:2 1855 └── projections 1856 └── (b:2, a:1) [as=r:3] 1857 1858 build 1859 SELECT min(y), (b, a) AS r 1860 FROM ab, xy GROUP BY (x, (a, b)) 1861 ---- 1862 project 1863 ├── columns: min:6 r:7 1864 ├── group-by 1865 │ ├── columns: a:1!null b:2 x:3 min:6 1866 │ ├── grouping columns: a:1!null b:2 x:3 1867 │ ├── project 1868 │ │ ├── columns: a:1!null b:2 x:3 y:4 1869 │ │ └── inner-join (cross) 1870 │ │ ├── columns: a:1!null b:2 x:3 y:4 rowid:5!null 1871 │ │ ├── scan ab 1872 │ │ │ └── columns: a:1!null b:2 1873 │ │ ├── scan xy 1874 │ │ │ └── columns: x:3 y:4 rowid:5!null 1875 │ │ └── filters (true) 1876 │ └── aggregations 1877 │ └── min [as=min:6] 1878 │ └── y:4 1879 └── projections 1880 └── (b:2, a:1) [as=r:7] 1881 1882 build 1883 SELECT v, count(k) FROM kv GROUP BY v ORDER BY count(k) 1884 ---- 1885 sort 1886 ├── columns: v:2 count:5!null 1887 ├── ordering: +5 1888 └── group-by 1889 ├── columns: v:2 count:5!null 1890 ├── grouping columns: v:2 1891 ├── project 1892 │ ├── columns: k:1!null v:2 1893 │ └── scan kv 1894 │ └── columns: k:1!null v:2 w:3 s:4 1895 └── aggregations 1896 └── count [as=count:5] 1897 └── k:1 1898 1899 build 1900 SELECT v, count(*) FROM kv GROUP BY v ORDER BY count(*) 1901 ---- 1902 sort 1903 ├── columns: v:2 count:5!null 1904 ├── ordering: +5 1905 └── group-by 1906 ├── columns: v:2 count_rows:5!null 1907 ├── grouping columns: v:2 1908 ├── project 1909 │ ├── columns: v:2 1910 │ └── scan kv 1911 │ └── columns: k:1!null v:2 w:3 s:4 1912 └── aggregations 1913 └── count-rows [as=count_rows:5] 1914 1915 build 1916 SELECT v, count(1) FROM kv GROUP BY v ORDER BY count(1) 1917 ---- 1918 sort 1919 ├── columns: v:2 count:6!null 1920 ├── ordering: +6 1921 └── group-by 1922 ├── columns: v:2 count:6!null 1923 ├── grouping columns: v:2 1924 ├── project 1925 │ ├── columns: column5:5!null v:2 1926 │ ├── scan kv 1927 │ │ └── columns: k:1!null v:2 w:3 s:4 1928 │ └── projections 1929 │ └── 1 [as=column5:5] 1930 └── aggregations 1931 └── count [as=count:6] 1932 └── column5:5 1933 1934 build 1935 SELECT (k+v)/(v+w) AS r FROM kv GROUP BY k+v, v+w; 1936 ---- 1937 project 1938 ├── columns: r:7 1939 ├── group-by 1940 │ ├── columns: column5:5 column6:6 1941 │ ├── grouping columns: column5:5 column6:6 1942 │ └── project 1943 │ ├── columns: column5:5 column6:6 1944 │ ├── scan kv 1945 │ │ └── columns: k:1!null v:2 w:3 s:4 1946 │ └── projections 1947 │ ├── k:1 + v:2 [as=column5:5] 1948 │ └── v:2 + w:3 [as=column6:6] 1949 └── projections 1950 └── column5:5 / column6:6 [as=r:7] 1951 1952 # Check that everything still works with differently qualified names 1953 build fully-qualify-names 1954 SELECT sum(t.kv.w), t.kv.v FROM t.kv GROUP BY v, kv.k * w 1955 ---- 1956 project 1957 ├── columns: sum:5 v:2 1958 └── group-by 1959 ├── columns: t.public.kv.v:2 sum:5 column6:6 1960 ├── grouping columns: t.public.kv.v:2 column6:6 1961 ├── project 1962 │ ├── columns: column6:6 t.public.kv.v:2 t.public.kv.w:3 1963 │ ├── scan t.public.kv 1964 │ │ └── columns: t.public.kv.k:1!null t.public.kv.v:2 t.public.kv.w:3 t.public.kv.s:4 1965 │ └── projections 1966 │ └── t.public.kv.k:1 * t.public.kv.w:3 [as=column6:6] 1967 └── aggregations 1968 └── sum [as=sum:5] 1969 └── t.public.kv.w:3 1970 1971 build fully-qualify-names 1972 SELECT sum(t.kv.w), lower(s), t.kv.v + k * t.kv.w AS r, t.kv.v FROM t.kv GROUP BY v, lower(kv.s), kv.k * w 1973 ---- 1974 project 1975 ├── columns: sum:5 lower:6 r:8 v:2 1976 ├── group-by 1977 │ ├── columns: t.public.kv.v:2 sum:5 column6:6 column7:7 1978 │ ├── grouping columns: t.public.kv.v:2 column6:6 column7:7 1979 │ ├── project 1980 │ │ ├── columns: column6:6 column7:7 t.public.kv.v:2 t.public.kv.w:3 1981 │ │ ├── scan t.public.kv 1982 │ │ │ └── columns: t.public.kv.k:1!null t.public.kv.v:2 t.public.kv.w:3 t.public.kv.s:4 1983 │ │ └── projections 1984 │ │ ├── lower(t.public.kv.s:4) [as=column6:6] 1985 │ │ └── t.public.kv.k:1 * t.public.kv.w:3 [as=column7:7] 1986 │ └── aggregations 1987 │ └── sum [as=sum:5] 1988 │ └── t.public.kv.w:3 1989 └── projections 1990 └── t.public.kv.v:2 + column7:7 [as=r:8] 1991 1992 # Check all the different types of scalar expressions as group by columns 1993 build 1994 SELECT b1.b AND abc.c AND b2.b AS r FROM bools b1, bools b2, abc GROUP BY b1.b AND abc.c, b2.b 1995 ---- 1996 project 1997 ├── columns: r:10 1998 ├── group-by 1999 │ ├── columns: b2.b:3 column9:9 2000 │ ├── grouping columns: b2.b:3 column9:9 2001 │ └── project 2002 │ ├── columns: column9:9 b2.b:3 2003 │ ├── inner-join (cross) 2004 │ │ ├── columns: b1.b:1 b1.rowid:2!null b2.b:3 b2.rowid:4!null a:5!null abc.b:6 c:7 d:8 2005 │ │ ├── scan b1 2006 │ │ │ └── columns: b1.b:1 b1.rowid:2!null 2007 │ │ ├── inner-join (cross) 2008 │ │ │ ├── columns: b2.b:3 b2.rowid:4!null a:5!null abc.b:6 c:7 d:8 2009 │ │ │ ├── scan b2 2010 │ │ │ │ └── columns: b2.b:3 b2.rowid:4!null 2011 │ │ │ ├── scan abc 2012 │ │ │ │ └── columns: a:5!null abc.b:6 c:7 d:8 2013 │ │ │ └── filters (true) 2014 │ │ └── filters (true) 2015 │ └── projections 2016 │ └── b1.b:1 AND c:7 [as=column9:9] 2017 └── projections 2018 └── column9:9 AND b2.b:3 [as=r:10] 2019 2020 build 2021 SELECT b1.b AND abc.c AND abc.c FROM bools b1, bools b2, abc GROUP BY b1.b AND abc.c, b2.b 2022 ---- 2023 error (42803): column "c" must appear in the GROUP BY clause or be used in an aggregate function 2024 2025 build 2026 SELECT b1.b OR abc.c OR b2.b AS r FROM bools b1, bools b2, abc GROUP BY b1.b OR abc.c, b2.b 2027 ---- 2028 project 2029 ├── columns: r:10 2030 ├── group-by 2031 │ ├── columns: b2.b:3 column9:9 2032 │ ├── grouping columns: b2.b:3 column9:9 2033 │ └── project 2034 │ ├── columns: column9:9 b2.b:3 2035 │ ├── inner-join (cross) 2036 │ │ ├── columns: b1.b:1 b1.rowid:2!null b2.b:3 b2.rowid:4!null a:5!null abc.b:6 c:7 d:8 2037 │ │ ├── scan b1 2038 │ │ │ └── columns: b1.b:1 b1.rowid:2!null 2039 │ │ ├── inner-join (cross) 2040 │ │ │ ├── columns: b2.b:3 b2.rowid:4!null a:5!null abc.b:6 c:7 d:8 2041 │ │ │ ├── scan b2 2042 │ │ │ │ └── columns: b2.b:3 b2.rowid:4!null 2043 │ │ │ ├── scan abc 2044 │ │ │ │ └── columns: a:5!null abc.b:6 c:7 d:8 2045 │ │ │ └── filters (true) 2046 │ │ └── filters (true) 2047 │ └── projections 2048 │ └── b1.b:1 OR c:7 [as=column9:9] 2049 └── projections 2050 └── column9:9 OR b2.b:3 [as=r:10] 2051 2052 build 2053 SELECT b1.b OR abc.c OR abc.c FROM bools b1, bools b2, abc GROUP BY b1.b OR abc.c, b2.b 2054 ---- 2055 error (42803): column "c" must appear in the GROUP BY clause or be used in an aggregate function 2056 2057 build 2058 SELECT k % w % v AS r FROM kv GROUP BY k % w, v 2059 ---- 2060 project 2061 ├── columns: r:6 2062 ├── group-by 2063 │ ├── columns: v:2 column5:5 2064 │ ├── grouping columns: v:2 column5:5 2065 │ └── project 2066 │ ├── columns: column5:5 v:2 2067 │ ├── scan kv 2068 │ │ └── columns: k:1!null v:2 w:3 s:4 2069 │ └── projections 2070 │ └── k:1 % w:3 [as=column5:5] 2071 └── projections 2072 └── column5:5 % v:2 [as=r:6] 2073 2074 build 2075 SELECT concat(concat(s, a), a) FROM kv, abc GROUP BY concat(s, a), a 2076 ---- 2077 project 2078 ├── columns: concat:10 2079 ├── group-by 2080 │ ├── columns: a:5!null column9:9 2081 │ ├── grouping columns: a:5!null column9:9 2082 │ └── project 2083 │ ├── columns: column9:9 a:5!null 2084 │ ├── inner-join (cross) 2085 │ │ ├── columns: k:1!null v:2 w:3 s:4 a:5!null b:6 c:7 d:8 2086 │ │ ├── scan kv 2087 │ │ │ └── columns: k:1!null v:2 w:3 s:4 2088 │ │ ├── scan abc 2089 │ │ │ └── columns: a:5!null b:6 c:7 d:8 2090 │ │ └── filters (true) 2091 │ └── projections 2092 │ └── concat(s:4, a:5) [as=column9:9] 2093 └── projections 2094 └── concat(column9:9, a:5) [as=concat:10] 2095 2096 build 2097 SELECT concat(concat(s, a), s) FROM kv, abc GROUP BY concat(s, a), a 2098 ---- 2099 error (42803): column "s" must appear in the GROUP BY clause or be used in an aggregate function 2100 2101 build 2102 SELECT k < w AND v != 5 AS r FROM kv GROUP BY k < w, v 2103 ---- 2104 project 2105 ├── columns: r:6 2106 ├── group-by 2107 │ ├── columns: v:2 column5:5 2108 │ ├── grouping columns: v:2 column5:5 2109 │ └── project 2110 │ ├── columns: column5:5 v:2 2111 │ ├── scan kv 2112 │ │ └── columns: k:1!null v:2 w:3 s:4 2113 │ └── projections 2114 │ └── k:1 < w:3 [as=column5:5] 2115 └── projections 2116 └── column5:5 AND (v:2 != 5) [as=r:6] 2117 2118 build 2119 SELECT k < w AND k < v FROM kv GROUP BY k < w, v 2120 ---- 2121 error (42803): column "k" must appear in the GROUP BY clause or be used in an aggregate function 2122 2123 exec-ddl 2124 CREATE TABLE foo (bar JSON, baz JSON) 2125 ---- 2126 2127 build 2128 SELECT a.bar @> b.baz AND b.baz @> b.baz AS r FROM foo AS a, foo AS b GROUP BY a.bar @> b.baz, b.baz 2129 ---- 2130 project 2131 ├── columns: r:8 2132 ├── group-by 2133 │ ├── columns: b.baz:5 column7:7 2134 │ ├── grouping columns: b.baz:5 column7:7 2135 │ └── project 2136 │ ├── columns: column7:7 b.baz:5 2137 │ ├── inner-join (cross) 2138 │ │ ├── columns: a.bar:1 a.baz:2 a.rowid:3!null b.bar:4 b.baz:5 b.rowid:6!null 2139 │ │ ├── scan a 2140 │ │ │ └── columns: a.bar:1 a.baz:2 a.rowid:3!null 2141 │ │ ├── scan b 2142 │ │ │ └── columns: b.bar:4 b.baz:5 b.rowid:6!null 2143 │ │ └── filters (true) 2144 │ └── projections 2145 │ └── a.bar:1 @> b.baz:5 [as=column7:7] 2146 └── projections 2147 └── column7:7 AND (b.baz:5 @> b.baz:5) [as=r:8] 2148 2149 build 2150 SELECT a.bar @> b.baz AND b.baz @> b.baz FROM foo AS a, foo AS b GROUP BY b.baz <@ a.bar, b.baz 2151 ---- 2152 error (42803): column "bar" must appear in the GROUP BY clause or be used in an aggregate function 2153 2154 build 2155 SELECT b.baz <@ a.bar AND b.baz <@ b.baz AS r FROM foo AS a, foo AS b GROUP BY b.baz <@ a.bar, b.baz 2156 ---- 2157 project 2158 ├── columns: r:8 2159 ├── group-by 2160 │ ├── columns: b.baz:5 column7:7 2161 │ ├── grouping columns: b.baz:5 column7:7 2162 │ └── project 2163 │ ├── columns: column7:7 b.baz:5 2164 │ ├── inner-join (cross) 2165 │ │ ├── columns: a.bar:1 a.baz:2 a.rowid:3!null b.bar:4 b.baz:5 b.rowid:6!null 2166 │ │ ├── scan a 2167 │ │ │ └── columns: a.bar:1 a.baz:2 a.rowid:3!null 2168 │ │ ├── scan b 2169 │ │ │ └── columns: b.bar:4 b.baz:5 b.rowid:6!null 2170 │ │ └── filters (true) 2171 │ └── projections 2172 │ └── a.bar:1 @> b.baz:5 [as=column7:7] 2173 └── projections 2174 └── column7:7 AND (b.baz:5 @> b.baz:5) [as=r:8] 2175 2176 exec-ddl 2177 CREATE TABLE times (t time PRIMARY KEY) 2178 ---- 2179 2180 build 2181 SELECT date_trunc('second', a.t) - date_trunc('minute', b.t) AS r FROM times a, times b 2182 GROUP BY date_trunc('second', a.t), date_trunc('minute', b.t) 2183 ---- 2184 project 2185 ├── columns: r:5 2186 ├── group-by 2187 │ ├── columns: column3:3 column4:4 2188 │ ├── grouping columns: column3:3 column4:4 2189 │ └── project 2190 │ ├── columns: column3:3 column4:4 2191 │ ├── inner-join (cross) 2192 │ │ ├── columns: a.t:1!null b.t:2!null 2193 │ │ ├── scan a 2194 │ │ │ └── columns: a.t:1!null 2195 │ │ ├── scan b 2196 │ │ │ └── columns: b.t:2!null 2197 │ │ └── filters (true) 2198 │ └── projections 2199 │ ├── date_trunc('second', a.t:1) [as=column3:3] 2200 │ └── date_trunc('minute', b.t:2) [as=column4:4] 2201 └── projections 2202 └── column3:3 - column4:4 [as=r:5] 2203 2204 build 2205 SELECT date_trunc('second', a.t) - date_trunc('second', b.t) FROM times a, times b 2206 GROUP BY date_trunc('second', a.t), date_trunc('minute', b.t) 2207 ---- 2208 error (42803): column "t" must appear in the GROUP BY clause or be used in an aggregate function 2209 2210 build 2211 SELECT NOT b AS r FROM bools GROUP BY NOT b 2212 ---- 2213 group-by 2214 ├── columns: r:3 2215 ├── grouping columns: column3:3 2216 └── project 2217 ├── columns: column3:3 2218 ├── scan bools 2219 │ └── columns: b:1 rowid:2!null 2220 └── projections 2221 └── NOT b:1 [as=column3:3] 2222 2223 build 2224 SELECT b FROM bools GROUP BY NOT b 2225 ---- 2226 error (42803): column "b" must appear in the GROUP BY clause or be used in an aggregate function 2227 2228 build 2229 SELECT NOT b AS r FROM bools GROUP BY b 2230 ---- 2231 project 2232 ├── columns: r:3 2233 ├── group-by 2234 │ ├── columns: b:1 2235 │ ├── grouping columns: b:1 2236 │ └── project 2237 │ ├── columns: b:1 2238 │ └── scan bools 2239 │ └── columns: b:1 rowid:2!null 2240 └── projections 2241 └── NOT b:1 [as=r:3] 2242 2243 build 2244 SELECT +k * (-w) AS r FROM kv GROUP BY +k, -w 2245 ---- 2246 project 2247 ├── columns: r:6 2248 ├── group-by 2249 │ ├── columns: k:1!null column5:5 2250 │ ├── grouping columns: k:1!null column5:5 2251 │ └── project 2252 │ ├── columns: column5:5 k:1!null 2253 │ ├── scan kv 2254 │ │ └── columns: k:1!null v:2 w:3 s:4 2255 │ └── projections 2256 │ └── -w:3 [as=column5:5] 2257 └── projections 2258 └── k:1 * column5:5 [as=r:6] 2259 2260 build 2261 SELECT k * (-w) FROM kv GROUP BY +k, -w 2262 ---- 2263 project 2264 ├── columns: "?column?":6 2265 ├── group-by 2266 │ ├── columns: k:1!null column5:5 2267 │ ├── grouping columns: k:1!null column5:5 2268 │ └── project 2269 │ ├── columns: column5:5 k:1!null 2270 │ ├── scan kv 2271 │ │ └── columns: k:1!null v:2 w:3 s:4 2272 │ └── projections 2273 │ └── -w:3 [as=column5:5] 2274 └── projections 2275 └── k:1 * column5:5 [as="?column?":6] 2276 2277 build 2278 SELECT +k * (-w) AS r FROM kv GROUP BY k, w 2279 ---- 2280 project 2281 ├── columns: r:5 2282 ├── group-by 2283 │ ├── columns: k:1!null w:3 2284 │ ├── grouping columns: k:1!null w:3 2285 │ └── project 2286 │ ├── columns: k:1!null w:3 2287 │ └── scan kv 2288 │ └── columns: k:1!null v:2 w:3 s:4 2289 └── projections 2290 └── k:1 * (-w:3) [as=r:5] 2291 2292 build 2293 SELECT 1 + min(v*2) AS r FROM kv GROUP BY k+3 2294 ---- 2295 project 2296 ├── columns: r:8 2297 ├── group-by 2298 │ ├── columns: min:6 column7:7!null 2299 │ ├── grouping columns: column7:7!null 2300 │ ├── project 2301 │ │ ├── columns: column5:5 column7:7!null 2302 │ │ ├── scan kv 2303 │ │ │ └── columns: k:1!null v:2 w:3 s:4 2304 │ │ └── projections 2305 │ │ ├── v:2 * 2 [as=column5:5] 2306 │ │ └── k:1 + 3 [as=column7:7] 2307 │ └── aggregations 2308 │ └── min [as=min:6] 2309 │ └── column5:5 2310 └── projections 2311 └── 1 + min:6 [as=r:8] 2312 2313 build 2314 SELECT count(*) FROM kv GROUP BY k, k 2315 ---- 2316 project 2317 ├── columns: count:5!null 2318 └── group-by 2319 ├── columns: k:1!null count_rows:5!null 2320 ├── grouping columns: k:1!null 2321 ├── project 2322 │ ├── columns: k:1!null 2323 │ └── scan kv 2324 │ └── columns: k:1!null v:2 w:3 s:4 2325 └── aggregations 2326 └── count-rows [as=count_rows:5] 2327 2328 build 2329 SELECT count(upper(s)) FROM kv GROUP BY upper(s) 2330 ---- 2331 project 2332 ├── columns: count:6!null 2333 └── group-by 2334 ├── columns: column5:5 count:6!null 2335 ├── grouping columns: column5:5 2336 ├── project 2337 │ ├── columns: column5:5 2338 │ ├── scan kv 2339 │ │ └── columns: k:1!null v:2 w:3 s:4 2340 │ └── projections 2341 │ └── upper(s:4) [as=column5:5] 2342 └── aggregations 2343 └── count [as=count:6] 2344 └── column5:5 2345 2346 build 2347 SELECT sum(abc.d) FROM kv JOIN abc ON kv.k >= abc.d GROUP BY kv.* 2348 ---- 2349 project 2350 ├── columns: sum:9!null 2351 └── group-by 2352 ├── columns: k:1!null v:2 w:3 s:4 sum:9!null 2353 ├── grouping columns: k:1!null v:2 w:3 s:4 2354 ├── project 2355 │ ├── columns: k:1!null v:2 w:3 s:4 d:8!null 2356 │ └── inner-join (cross) 2357 │ ├── columns: k:1!null v:2 w:3 s:4 a:5!null b:6 c:7 d:8!null 2358 │ ├── scan kv 2359 │ │ └── columns: k:1!null v:2 w:3 s:4 2360 │ ├── scan abc 2361 │ │ └── columns: a:5!null b:6 c:7 d:8 2362 │ └── filters 2363 │ └── k:1 >= d:8 2364 └── aggregations 2365 └── sum [as=sum:9] 2366 └── d:8 2367 2368 build 2369 SELECT sum(DISTINCT abc.d) FROM abc 2370 ---- 2371 scalar-group-by 2372 ├── columns: sum:5 2373 ├── project 2374 │ ├── columns: d:4 2375 │ └── scan abc 2376 │ └── columns: a:1!null b:2 c:3 d:4 2377 └── aggregations 2378 └── agg-distinct [as=sum:5] 2379 └── sum 2380 └── d:4 2381 2382 # FILTER. 2383 2384 build 2385 SELECT sum(abc.d) FILTER (WHERE abc.d > 0) FROM abc 2386 ---- 2387 scalar-group-by 2388 ├── columns: sum:6 2389 ├── project 2390 │ ├── columns: column5:5 d:4 2391 │ ├── scan abc 2392 │ │ └── columns: a:1!null b:2 c:3 d:4 2393 │ └── projections 2394 │ └── d:4 > 0 [as=column5:5] 2395 └── aggregations 2396 └── agg-filter [as=sum:6] 2397 ├── sum 2398 │ └── d:4 2399 └── column5:5 2400 2401 # Ensure aggregates with FILTER coexist properly with non-FILTER aggregates. 2402 build 2403 SELECT 2404 sum(x) FILTER (WHERE y > 0), 2405 avg(DISTINCT z), 2406 avg(DISTINCT z) FILTER (WHERE y > 0) 2407 FROM xyz 2408 ---- 2409 scalar-group-by 2410 ├── columns: sum:5 avg:6 avg:7 2411 ├── project 2412 │ ├── columns: column4:4 x:1!null z:3 2413 │ ├── scan xyz 2414 │ │ └── columns: x:1!null y:2 z:3 2415 │ └── projections 2416 │ └── y:2 > 0 [as=column4:4] 2417 └── aggregations 2418 ├── agg-filter [as=sum:5] 2419 │ ├── sum 2420 │ │ └── x:1 2421 │ └── column4:4 2422 ├── agg-distinct [as=avg:6] 2423 │ └── avg 2424 │ └── z:3 2425 └── agg-filter [as=avg:7] 2426 ├── agg-distinct 2427 │ └── avg 2428 │ └── z:3 2429 └── column4:4 2430 2431 # Ensure aggregates involving FILTER are deduplicated. 2432 build 2433 SELECT 2434 avg(DISTINCT x), 2435 avg(DISTINCT x), 2436 avg(DISTINCT x) FILTER (WHERE y > 0), 2437 avg(DISTINCT x) FILTER (WHERE y > 0) 2438 FROM xyz 2439 ---- 2440 scalar-group-by 2441 ├── columns: avg:4 avg:4 avg:6 avg:6 2442 ├── project 2443 │ ├── columns: column5:5 x:1!null 2444 │ ├── scan xyz 2445 │ │ └── columns: x:1!null y:2 z:3 2446 │ └── projections 2447 │ └── y:2 > 0 [as=column5:5] 2448 └── aggregations 2449 ├── agg-distinct [as=avg:4] 2450 │ └── avg 2451 │ └── x:1 2452 └── agg-filter [as=avg:6] 2453 ├── agg-distinct 2454 │ └── avg 2455 │ └── x:1 2456 └── column5:5 2457 2458 build 2459 SELECT 2460 string_agg(x::string, 'foo') FILTER (WHERE y > 0) 2461 FROM xyz 2462 ---- 2463 scalar-group-by 2464 ├── columns: string_agg:7 2465 ├── project 2466 │ ├── columns: column4:4!null column5:5!null column6:6 2467 │ ├── scan xyz 2468 │ │ └── columns: x:1!null y:2 z:3 2469 │ └── projections 2470 │ ├── x:1::STRING [as=column4:4] 2471 │ ├── 'foo' [as=column5:5] 2472 │ └── y:2 > 0 [as=column6:6] 2473 └── aggregations 2474 └── agg-filter [as=string_agg:7] 2475 ├── string-agg 2476 │ ├── column4:4 2477 │ └── column5:5 2478 └── column6:6 2479 2480 build 2481 SELECT y, count(*) FILTER (WHERE x > 5) FROM xyz GROUP BY y 2482 ---- 2483 group-by 2484 ├── columns: y:2 count:6!null 2485 ├── grouping columns: y:2 2486 ├── project 2487 │ ├── columns: column4:4!null column5:5!null y:2 2488 │ ├── scan xyz 2489 │ │ └── columns: x:1!null y:2 z:3 2490 │ └── projections 2491 │ ├── true [as=column4:4] 2492 │ └── x:1 > 5 [as=column5:5] 2493 └── aggregations 2494 └── agg-filter [as=count:6] 2495 ├── count 2496 │ └── column4:4 2497 └── column5:5 2498 2499 build 2500 SELECT y, count(*) FILTER (WHERE count(*) > 5) FROM xyz GROUP BY y 2501 ---- 2502 error (42803): count_rows(): aggregate functions are not allowed in FILTER 2503 2504 2505 # Check that ordering by an alias of an aggregate works. 2506 build 2507 SELECT max(k) AS mk FROM kv GROUP BY v ORDER BY mk 2508 ---- 2509 sort 2510 ├── columns: mk:5!null 2511 ├── ordering: +5 2512 └── project 2513 ├── columns: max:5!null 2514 └── group-by 2515 ├── columns: v:2 max:5!null 2516 ├── grouping columns: v:2 2517 ├── project 2518 │ ├── columns: k:1!null v:2 2519 │ └── scan kv 2520 │ └── columns: k:1!null v:2 w:3 s:4 2521 └── aggregations 2522 └── max [as=max:5] 2523 └── k:1 2524 2525 build 2526 SELECT max(k) AS mk FROM kv GROUP BY v ORDER BY max(k) 2527 ---- 2528 sort 2529 ├── columns: mk:5!null 2530 ├── ordering: +5 2531 └── project 2532 ├── columns: max:5!null 2533 └── group-by 2534 ├── columns: v:2 max:5!null 2535 ├── grouping columns: v:2 2536 ├── project 2537 │ ├── columns: k:1!null v:2 2538 │ └── scan kv 2539 │ └── columns: k:1!null v:2 w:3 s:4 2540 └── aggregations 2541 └── max [as=max:5] 2542 └── k:1 2543 2544 build 2545 SELECT max(k) AS mk1, max(k) AS mk2 FROM kv GROUP BY v ORDER BY mk1 2546 ---- 2547 sort 2548 ├── columns: mk1:5!null mk2:5!null 2549 ├── ordering: +5 2550 └── project 2551 ├── columns: max:5!null 2552 └── group-by 2553 ├── columns: v:2 max:5!null 2554 ├── grouping columns: v:2 2555 ├── project 2556 │ ├── columns: k:1!null v:2 2557 │ └── scan kv 2558 │ └── columns: k:1!null v:2 w:3 s:4 2559 └── aggregations 2560 └── max [as=max:5] 2561 └── k:1 2562 2563 build 2564 SELECT max(k) AS mk1, max(k) AS mk2 FROM kv GROUP BY v ORDER BY mk2 2565 ---- 2566 sort 2567 ├── columns: mk1:5!null mk2:5!null 2568 ├── ordering: +5 2569 └── project 2570 ├── columns: max:5!null 2571 └── group-by 2572 ├── columns: v:2 max:5!null 2573 ├── grouping columns: v:2 2574 ├── project 2575 │ ├── columns: k:1!null v:2 2576 │ └── scan kv 2577 │ └── columns: k:1!null v:2 w:3 s:4 2578 └── aggregations 2579 └── max [as=max:5] 2580 └── k:1 2581 2582 build 2583 SELECT max(k) AS mk1, max(k)/5 AS mk2 FROM kv GROUP BY v ORDER BY mk2 2584 ---- 2585 sort 2586 ├── columns: mk1:5!null mk2:6!null 2587 ├── ordering: +6 2588 └── project 2589 ├── columns: mk2:6!null max:5!null 2590 ├── group-by 2591 │ ├── columns: v:2 max:5!null 2592 │ ├── grouping columns: v:2 2593 │ ├── project 2594 │ │ ├── columns: k:1!null v:2 2595 │ │ └── scan kv 2596 │ │ └── columns: k:1!null v:2 w:3 s:4 2597 │ └── aggregations 2598 │ └── max [as=max:5] 2599 │ └── k:1 2600 └── projections 2601 └── max:5 / 5 [as=mk2:6] 2602 2603 # Grouping columns cannot be reused inside an aggregate input expression 2604 # because the aggregate input expressions and grouping expressions are 2605 # built as part of the same projection. 2606 build 2607 SELECT max((k+v)/(k-v)) AS r, (k+v)*(k-v) AS s FROM kv GROUP BY k+v, k-v 2608 ---- 2609 project 2610 ├── columns: r:6 s:9 2611 ├── group-by 2612 │ ├── columns: max:6 column7:7 column8:8 2613 │ ├── grouping columns: column7:7 column8:8 2614 │ ├── project 2615 │ │ ├── columns: column5:5 column7:7 column8:8 2616 │ │ ├── scan kv 2617 │ │ │ └── columns: k:1!null v:2 w:3 kv.s:4 2618 │ │ └── projections 2619 │ │ ├── (k:1 + v:2) / (k:1 - v:2) [as=column5:5] 2620 │ │ ├── k:1 + v:2 [as=column7:7] 2621 │ │ └── k:1 - v:2 [as=column8:8] 2622 │ └── aggregations 2623 │ └── max [as=max:6] 2624 │ └── column5:5 2625 └── projections 2626 └── column7:7 * column8:8 [as=s:9] 2627 2628 build 2629 SELECT max((k+v)/(k-v)) AS r, (k+v)*(k-v) AS s FROM kv GROUP BY k+v, (k+v)/(k-v), (k+v)*(k-v) 2630 ---- 2631 project 2632 ├── columns: r:6 s:9 2633 └── group-by 2634 ├── columns: max:6 column7:7 column8:8 column9:9 2635 ├── grouping columns: column7:7 column8:8 column9:9 2636 ├── project 2637 │ ├── columns: column5:5 column7:7 column8:8 column9:9 2638 │ ├── scan kv 2639 │ │ └── columns: k:1!null v:2 w:3 s:4 2640 │ └── projections 2641 │ ├── (k:1 + v:2) / (k:1 - v:2) [as=column5:5] 2642 │ ├── k:1 + v:2 [as=column7:7] 2643 │ ├── (k:1 + v:2) / (k:1 - v:2) [as=column8:8] 2644 │ └── (k:1 + v:2) * (k:1 - v:2) [as=column9:9] 2645 └── aggregations 2646 └── max [as=max:6] 2647 └── column5:5 2648 2649 # Tests for corr. 2650 2651 build 2652 SELECT corr(k, v) FROM kv 2653 ---- 2654 scalar-group-by 2655 ├── columns: corr:5 2656 ├── project 2657 │ ├── columns: k:1!null v:2 2658 │ └── scan kv 2659 │ └── columns: k:1!null v:2 w:3 s:4 2660 └── aggregations 2661 └── corr [as=corr:5] 2662 ├── k:1 2663 └── v:2 2664 2665 build 2666 SELECT corr(k) FROM kv 2667 ---- 2668 error (42883): unknown signature: corr(int) 2669 2670 build 2671 SELECT corr(k, 'x', 'y') FROM kv 2672 ---- 2673 error (42883): unknown signature: corr(int, string, string) 2674 2675 build 2676 SELECT corr(DISTINCT k, v) FROM kv 2677 ---- 2678 scalar-group-by 2679 ├── columns: corr:5 2680 ├── project 2681 │ ├── columns: k:1!null v:2 2682 │ └── scan kv 2683 │ └── columns: k:1!null v:2 w:3 s:4 2684 └── aggregations 2685 └── agg-distinct [as=corr:5] 2686 └── corr 2687 ├── k:1 2688 └── v:2 2689 2690 build 2691 SELECT max(s), corr(k, v), min(s) FROM kv 2692 ---- 2693 scalar-group-by 2694 ├── columns: max:5 corr:6 min:7 2695 ├── project 2696 │ ├── columns: k:1!null v:2 s:4 2697 │ └── scan kv 2698 │ └── columns: k:1!null v:2 w:3 s:4 2699 └── aggregations 2700 ├── max [as=max:5] 2701 │ └── s:4 2702 ├── corr [as=corr:6] 2703 │ ├── k:1 2704 │ └── v:2 2705 └── min [as=min:7] 2706 └── s:4 2707 2708 build 2709 SELECT corr(k, NULL) FROM kv 2710 ---- 2711 error (42725): ambiguous call: corr(int, unknown), candidates are: 2712 corr(int, int) -> float 2713 corr(int, float) -> float 2714 2715 build 2716 SELECT corr('foo', v) FROM kv 2717 ---- 2718 error (22P02): corr(): could not parse "foo" as type int: strconv.ParseInt: parsing "foo": invalid syntax 2719 2720 # Tests for string_agg. 2721 2722 build 2723 SELECT string_agg(s, 'separator') FROM kv 2724 ---- 2725 scalar-group-by 2726 ├── columns: string_agg:6 2727 ├── project 2728 │ ├── columns: column5:5!null s:4 2729 │ ├── scan kv 2730 │ │ └── columns: k:1!null v:2 w:3 s:4 2731 │ └── projections 2732 │ └── 'separator' [as=column5:5] 2733 └── aggregations 2734 └── string-agg [as=string_agg:6] 2735 ├── s:4 2736 └── column5:5 2737 2738 build 2739 SELECT string_agg(s) FROM kv 2740 ---- 2741 error (42883): unknown signature: string_agg(string) 2742 2743 build 2744 SELECT string_agg(s, 'x', 'y') FROM kv 2745 ---- 2746 error (42883): unknown signature: string_agg(string, string, string) 2747 2748 build 2749 SELECT string_agg(DISTINCT s, 'separator') FROM kv 2750 ---- 2751 scalar-group-by 2752 ├── columns: string_agg:6 2753 ├── project 2754 │ ├── columns: column5:5!null s:4 2755 │ ├── scan kv 2756 │ │ └── columns: k:1!null v:2 w:3 s:4 2757 │ └── projections 2758 │ └── 'separator' [as=column5:5] 2759 └── aggregations 2760 └── agg-distinct [as=string_agg:6] 2761 └── string-agg 2762 ├── s:4 2763 └── column5:5 2764 2765 build 2766 SELECT max(s), string_agg(s, 'sep1'), string_agg(s, 'sep2'), min(s) FROM kv 2767 ---- 2768 scalar-group-by 2769 ├── columns: max:5 string_agg:7 string_agg:9 min:10 2770 ├── project 2771 │ ├── columns: column6:6!null column8:8!null s:4 2772 │ ├── scan kv 2773 │ │ └── columns: k:1!null v:2 w:3 s:4 2774 │ └── projections 2775 │ ├── 'sep1' [as=column6:6] 2776 │ └── 'sep2' [as=column8:8] 2777 └── aggregations 2778 ├── max [as=max:5] 2779 │ └── s:4 2780 ├── string-agg [as=string_agg:7] 2781 │ ├── s:4 2782 │ └── column6:6 2783 ├── string-agg [as=string_agg:9] 2784 │ ├── s:4 2785 │ └── column8:8 2786 └── min [as=min:10] 2787 └── s:4 2788 2789 # The separator must be constant, but it need not be a literal - any constant (as 2790 # determined by tree.IsConst) is valid. 2791 build 2792 SELECT string_agg(s, 'abc' || 'xyz') FROM kv 2793 ---- 2794 scalar-group-by 2795 ├── columns: string_agg:6 2796 ├── project 2797 │ ├── columns: column5:5!null s:4 2798 │ ├── scan kv 2799 │ │ └── columns: k:1!null v:2 w:3 s:4 2800 │ └── projections 2801 │ └── 'abcxyz' [as=column5:5] 2802 └── aggregations 2803 └── string-agg [as=string_agg:6] 2804 ├── s:4 2805 └── column5:5 2806 2807 build 2808 SELECT string_agg(s, NULL) FROM kv 2809 ---- 2810 scalar-group-by 2811 ├── columns: string_agg:6 2812 ├── project 2813 │ ├── columns: column5:5 s:4 2814 │ ├── scan kv 2815 │ │ └── columns: k:1!null v:2 w:3 s:4 2816 │ └── projections 2817 │ └── NULL [as=column5:5] 2818 └── aggregations 2819 └── string-agg [as=string_agg:6] 2820 ├── s:4 2821 └── column5:5 2822 2823 build 2824 SELECT string_agg(s, s) FROM kv 2825 ---- 2826 scalar-group-by 2827 ├── columns: string_agg:5 2828 ├── project 2829 │ ├── columns: s:4 2830 │ └── scan kv 2831 │ └── columns: k:1!null v:2 w:3 s:4 2832 └── aggregations 2833 └── string-agg [as=string_agg:5] 2834 ├── s:4 2835 └── s:4 2836 2837 # Regression test for #26419 2838 build 2839 SELECT 123 r FROM kv ORDER BY max(v) 2840 ---- 2841 project 2842 ├── columns: r:6!null [hidden: max:5] 2843 ├── ordering: +5 2844 ├── scalar-group-by 2845 │ ├── columns: max:5 2846 │ ├── project 2847 │ │ ├── columns: v:2 2848 │ │ └── scan kv 2849 │ │ └── columns: k:1!null v:2 w:3 s:4 2850 │ └── aggregations 2851 │ └── max [as=max:5] 2852 │ └── v:2 2853 └── projections 2854 └── 123 [as=r:6] 2855 2856 # Check that ordering columns are projected correctly. 2857 build 2858 SELECT array_agg(y) FROM (SELECT * FROM xyz ORDER BY x+y) 2859 ---- 2860 scalar-group-by 2861 ├── columns: array_agg:5 2862 ├── internal-ordering: +4 2863 ├── sort 2864 │ ├── columns: y:2 column4:4 2865 │ ├── ordering: +4 2866 │ └── project 2867 │ ├── columns: y:2 column4:4 2868 │ └── project 2869 │ ├── columns: column4:4 x:1!null y:2 z:3 2870 │ ├── scan xyz 2871 │ │ └── columns: x:1!null y:2 z:3 2872 │ └── projections 2873 │ └── x:1 + y:2 [as=column4:4] 2874 └── aggregations 2875 └── array-agg [as=array_agg:5] 2876 └── y:2 2877 2878 build 2879 SELECT array_agg(y) FROM (SELECT * FROM xyz ORDER BY x DESC) 2880 ---- 2881 scalar-group-by 2882 ├── columns: array_agg:4 2883 ├── internal-ordering: -1 2884 ├── project 2885 │ ├── columns: x:1!null y:2 2886 │ ├── ordering: -1 2887 │ └── scan xyz,rev 2888 │ ├── columns: x:1!null y:2 z:3 2889 │ └── ordering: -1 2890 └── aggregations 2891 └── array-agg [as=array_agg:4] 2892 └── y:2 2893 2894 # Regression test for #30166. 2895 build 2896 SELECT array_agg(generate_series(1, 2)) 2897 ---- 2898 scalar-group-by 2899 ├── columns: array_agg:2 2900 ├── project-set 2901 │ ├── columns: generate_series:1 2902 │ ├── values 2903 │ │ └── () 2904 │ └── zip 2905 │ └── generate_series(1, 2) 2906 └── aggregations 2907 └── array-agg [as=array_agg:2] 2908 └── generate_series:1 2909 2910 # Regression test for #37317: duplicate column in GROUP BY 2911 build format=show-all 2912 SELECT 2913 * 2914 FROM 2915 ( 2916 SELECT 2917 x AS firstCol, 2918 y AS secondCol, 2919 y AS thirdCol 2920 FROM xyz 2921 ) 2922 GROUP BY 2923 firstCol, secondCol, thirdCol; 2924 ---- 2925 group-by 2926 ├── columns: firstcol:1(int!null) secondcol:2(int) thirdcol:2(int) 2927 ├── grouping columns: t.public.xyz.x:1(int!null) t.public.xyz.y:2(int) 2928 ├── stats: [rows=1000, distinct(1,2)=1000, null(1,2)=0] 2929 ├── cost: 1110.05 2930 ├── key: (1) 2931 ├── fd: (1)-->(2) 2932 ├── interesting orderings: (+1,+2) 2933 └── project 2934 ├── columns: t.public.xyz.x:1(int!null) t.public.xyz.y:2(int) 2935 ├── stats: [rows=1000, distinct(1,2)=1000, null(1,2)=0] 2936 ├── cost: 1070.03 2937 ├── key: (1) 2938 ├── fd: (1)-->(2) 2939 ├── prune: (1,2) 2940 ├── interesting orderings: (+1,+2) 2941 └── scan t.public.xyz 2942 ├── columns: t.public.xyz.x:1(int!null) t.public.xyz.y:2(int) t.public.xyz.z:3(float) 2943 ├── stats: [rows=1000, distinct(1,2)=1000, null(1,2)=0] 2944 ├── cost: 1060.02 2945 ├── key: (1) 2946 ├── fd: (1)-->(2,3) 2947 ├── prune: (1-3) 2948 └── interesting orderings: (+1,+2) (+3,+2,+1) 2949 2950 # Testing ordered aggregations. 2951 exec-ddl 2952 CREATE TABLE tab (col1 int NOT NULL, col2 int NOT NULL, col3 string) 2953 ---- 2954 2955 build 2956 SELECT array_agg(col1 ORDER BY col1) FROM tab 2957 ---- 2958 scalar-group-by 2959 ├── columns: array_agg:5 2960 ├── window partition=() ordering=+1 2961 │ ├── columns: col1:1!null col2:2!null col3:3 rowid:4!null array_agg:5 2962 │ ├── scan tab 2963 │ │ └── columns: col1:1!null col2:2!null col3:3 rowid:4!null 2964 │ └── windows 2965 │ └── array-agg [as=array_agg:5, frame="range from unbounded to unbounded"] 2966 │ └── col1:1 2967 └── aggregations 2968 └── const-agg [as=array_agg:5] 2969 └── array_agg:5 2970 2971 # Ignore aggregate orderings for non commutative aggregates. 2972 build 2973 SELECT count(col1 ORDER BY col2) FROM tab 2974 ---- 2975 scalar-group-by 2976 ├── columns: count:5!null 2977 ├── project 2978 │ ├── columns: col1:1!null col2:2!null 2979 │ └── scan tab 2980 │ └── columns: col1:1!null col2:2!null col3:3 rowid:4!null 2981 └── aggregations 2982 └── count [as=count:5] 2983 └── col1:1 2984 2985 # Multiple ordered aggregations. 2986 build 2987 SELECT array_agg(col1 ORDER BY col1), array_agg(col1 ORDER BY col2) from tab 2988 ---- 2989 scalar-group-by 2990 ├── columns: array_agg:5 array_agg:6 2991 ├── window partition=() ordering=+2 2992 │ ├── columns: col1:1!null col2:2!null col3:3 rowid:4!null array_agg:5 array_agg:6 2993 │ ├── window partition=() ordering=+1 2994 │ │ ├── columns: col1:1!null col2:2!null col3:3 rowid:4!null array_agg:5 2995 │ │ ├── scan tab 2996 │ │ │ └── columns: col1:1!null col2:2!null col3:3 rowid:4!null 2997 │ │ └── windows 2998 │ │ └── array-agg [as=array_agg:5, frame="range from unbounded to unbounded"] 2999 │ │ └── col1:1 3000 │ └── windows 3001 │ └── array-agg [as=array_agg:6, frame="range from unbounded to unbounded"] 3002 │ └── col1:1 3003 └── aggregations 3004 ├── const-agg [as=array_agg:5] 3005 │ └── array_agg:5 3006 └── const-agg [as=array_agg:6] 3007 └── array_agg:6 3008 3009 build 3010 SELECT concat_agg(col3 ORDER BY col1), array_agg(col1) FROM tab 3011 ---- 3012 scalar-group-by 3013 ├── columns: concat_agg:5 array_agg:6 3014 ├── window partition=() 3015 │ ├── columns: col1:1!null col2:2!null col3:3 rowid:4!null concat_agg:5 array_agg:6 3016 │ ├── window partition=() ordering=+1 3017 │ │ ├── columns: col1:1!null col2:2!null col3:3 rowid:4!null concat_agg:5 3018 │ │ ├── scan tab 3019 │ │ │ └── columns: col1:1!null col2:2!null col3:3 rowid:4!null 3020 │ │ └── windows 3021 │ │ └── concat-agg [as=concat_agg:5, frame="range from unbounded to unbounded"] 3022 │ │ └── col3:3 3023 │ └── windows 3024 │ └── array-agg [as=array_agg:6, frame="range from unbounded to unbounded"] 3025 │ └── col1:1 3026 └── aggregations 3027 ├── const-agg [as=concat_agg:5] 3028 │ └── concat_agg:5 3029 └── const-agg [as=array_agg:6] 3030 └── array_agg:6 3031 3032 build 3033 SELECT concat_agg(col3 ORDER BY col1), sum(col1 ORDER BY col2) FROM tab 3034 ---- 3035 scalar-group-by 3036 ├── columns: concat_agg:5 sum:6 3037 ├── window partition=() 3038 │ ├── columns: col1:1!null col2:2!null col3:3 rowid:4!null concat_agg:5 sum:6 3039 │ ├── window partition=() ordering=+1 3040 │ │ ├── columns: col1:1!null col2:2!null col3:3 rowid:4!null concat_agg:5 3041 │ │ ├── scan tab 3042 │ │ │ └── columns: col1:1!null col2:2!null col3:3 rowid:4!null 3043 │ │ └── windows 3044 │ │ └── concat-agg [as=concat_agg:5, frame="range from unbounded to unbounded"] 3045 │ │ └── col3:3 3046 │ └── windows 3047 │ └── sum [as=sum:6, frame="range from unbounded to unbounded"] 3048 │ └── col1:1 3049 └── aggregations 3050 ├── const-agg [as=concat_agg:5] 3051 │ └── concat_agg:5 3052 └── const-agg [as=sum:6] 3053 └── sum:6 3054 3055 build 3056 SELECT array_agg(col1 ORDER BY col1) FROM tab GROUP BY col2 3057 ---- 3058 project 3059 ├── columns: array_agg:5 3060 └── group-by 3061 ├── columns: col2:2!null array_agg:5 3062 ├── grouping columns: col2:2!null 3063 ├── window partition=(2) ordering=+1 3064 │ ├── columns: col1:1!null col2:2!null col3:3 rowid:4!null array_agg:5 3065 │ ├── scan tab 3066 │ │ └── columns: col1:1!null col2:2!null col3:3 rowid:4!null 3067 │ └── windows 3068 │ └── array-agg [as=array_agg:5, frame="range from unbounded to unbounded"] 3069 │ └── col1:1 3070 └── aggregations 3071 └── const-agg [as=array_agg:5] 3072 └── array_agg:5 3073 3074 build 3075 SELECT array_agg(col1 ORDER BY col1), array_agg(col3 ORDER BY col1) FROM tab GROUP BY col2 3076 ---- 3077 project 3078 ├── columns: array_agg:5 array_agg:6 3079 └── group-by 3080 ├── columns: col2:2!null array_agg:5 array_agg:6 3081 ├── grouping columns: col2:2!null 3082 ├── window partition=(2) ordering=+1 3083 │ ├── columns: col1:1!null col2:2!null col3:3 rowid:4!null array_agg:5 array_agg:6 3084 │ ├── scan tab 3085 │ │ └── columns: col1:1!null col2:2!null col3:3 rowid:4!null 3086 │ └── windows 3087 │ ├── array-agg [as=array_agg:5, frame="range from unbounded to unbounded"] 3088 │ │ └── col1:1 3089 │ └── array-agg [as=array_agg:6, frame="range from unbounded to unbounded"] 3090 │ └── col3:3 3091 └── aggregations 3092 ├── const-agg [as=array_agg:5] 3093 │ └── array_agg:5 3094 └── const-agg [as=array_agg:6] 3095 └── array_agg:6 3096 3097 build 3098 SELECT array_agg(col1 ORDER BY col1), array_agg(col3 ORDER BY col1) FROM tab GROUP BY col2 HAVING col2 > 1 3099 ---- 3100 project 3101 ├── columns: array_agg:5 array_agg:6 3102 └── select 3103 ├── columns: col2:2!null array_agg:5 array_agg:6 3104 ├── group-by 3105 │ ├── columns: col2:2!null array_agg:5 array_agg:6 3106 │ ├── grouping columns: col2:2!null 3107 │ ├── window partition=(2) ordering=+1 3108 │ │ ├── columns: col1:1!null col2:2!null col3:3 rowid:4!null array_agg:5 array_agg:6 3109 │ │ ├── scan tab 3110 │ │ │ └── columns: col1:1!null col2:2!null col3:3 rowid:4!null 3111 │ │ └── windows 3112 │ │ ├── array-agg [as=array_agg:5, frame="range from unbounded to unbounded"] 3113 │ │ │ └── col1:1 3114 │ │ └── array-agg [as=array_agg:6, frame="range from unbounded to unbounded"] 3115 │ │ └── col3:3 3116 │ └── aggregations 3117 │ ├── const-agg [as=array_agg:5] 3118 │ │ └── array_agg:5 3119 │ └── const-agg [as=array_agg:6] 3120 │ └── array_agg:6 3121 └── filters 3122 └── col2:2 > 1 3123 3124 # Add projection on top to ensure the default NULL values are set correctly. 3125 build 3126 SELECT count(DISTINCT col1), count(*), array_agg(col1 ORDER BY col2) FROM tab 3127 ---- 3128 project 3129 ├── columns: count:5 count:6 array_agg:7 3130 └── project 3131 ├── columns: count:5 count_rows:6 col1:1 col2:2 col3:3 rowid:4 array_agg:7 3132 ├── scalar-group-by 3133 │ ├── columns: array_agg:7 count:8 count_rows:9 3134 │ ├── window partition=() ordering=+2 3135 │ │ ├── columns: col1:1!null col2:2!null col3:3 rowid:4!null count:5 count_rows:6 array_agg:7 3136 │ │ ├── window partition=() 3137 │ │ │ ├── columns: col1:1!null col2:2!null col3:3 rowid:4!null count:5 count_rows:6 3138 │ │ │ ├── scan tab 3139 │ │ │ │ └── columns: col1:1!null col2:2!null col3:3 rowid:4!null 3140 │ │ │ └── windows 3141 │ │ │ ├── count [as=count:5, frame="range from unbounded to unbounded"] 3142 │ │ │ │ └── col1:1 3143 │ │ │ └── count-rows [as=count_rows:6, frame="range from unbounded to unbounded"] 3144 │ │ └── windows 3145 │ │ └── array-agg [as=array_agg:7, frame="range from unbounded to unbounded"] 3146 │ │ └── col1:1 3147 │ └── aggregations 3148 │ ├── const-agg [as=count:8] 3149 │ │ └── count:5 3150 │ ├── const-agg [as=count_rows:9] 3151 │ │ └── count_rows:6 3152 │ └── const-agg [as=array_agg:7] 3153 │ └── array_agg:7 3154 └── projections 3155 ├── CASE WHEN count:8 IS NULL THEN 0 ELSE count:8 END [as=count:5] 3156 └── CASE WHEN count_rows:9 IS NULL THEN 0 ELSE count_rows:9 END [as=count_rows:6] 3157 3158 # Testing aggregations as window when group by has a projection. 3159 build 3160 SELECT array_agg(col1 ORDER BY col1) FROM tab GROUP BY upper(col3) 3161 ---- 3162 project 3163 ├── columns: array_agg:5 3164 └── group-by 3165 ├── columns: array_agg:5 column6:6 3166 ├── grouping columns: column6:6 3167 ├── window partition=(6) ordering=+1 3168 │ ├── columns: col1:1!null col2:2!null col3:3 rowid:4!null array_agg:5 column6:6 3169 │ ├── project 3170 │ │ ├── columns: column6:6 col1:1!null col2:2!null col3:3 rowid:4!null 3171 │ │ ├── scan tab 3172 │ │ │ └── columns: col1:1!null col2:2!null col3:3 rowid:4!null 3173 │ │ └── projections 3174 │ │ └── upper(col3:3) [as=column6:6] 3175 │ └── windows 3176 │ └── array-agg [as=array_agg:5, frame="range from unbounded to unbounded"] 3177 │ └── col1:1 3178 └── aggregations 3179 └── const-agg [as=array_agg:5] 3180 └── array_agg:5 3181 3182 build 3183 SELECT array_agg(col1 ORDER BY col1), upper(col3) FROM tab GROUP BY upper(col3) 3184 ---- 3185 group-by 3186 ├── columns: array_agg:5 upper:6 3187 ├── grouping columns: column6:6 3188 ├── window partition=(6) ordering=+1 3189 │ ├── columns: col1:1!null col2:2!null col3:3 rowid:4!null array_agg:5 column6:6 3190 │ ├── project 3191 │ │ ├── columns: column6:6 col1:1!null col2:2!null col3:3 rowid:4!null 3192 │ │ ├── scan tab 3193 │ │ │ └── columns: col1:1!null col2:2!null col3:3 rowid:4!null 3194 │ │ └── projections 3195 │ │ └── upper(col3:3) [as=column6:6] 3196 │ └── windows 3197 │ └── array-agg [as=array_agg:5, frame="range from unbounded to unbounded"] 3198 │ └── col1:1 3199 └── aggregations 3200 └── const-agg [as=array_agg:5] 3201 └── array_agg:5 3202 3203 build 3204 SELECT array_agg(lower(col3)) FROM tab GROUP BY upper(col3) 3205 ---- 3206 project 3207 ├── columns: array_agg:6 3208 └── group-by 3209 ├── columns: array_agg:6 column7:7 3210 ├── grouping columns: column7:7 3211 ├── project 3212 │ ├── columns: column5:5 column7:7 3213 │ ├── scan tab 3214 │ │ └── columns: col1:1!null col2:2!null col3:3 rowid:4!null 3215 │ └── projections 3216 │ ├── lower(col3:3) [as=column5:5] 3217 │ └── upper(col3:3) [as=column7:7] 3218 └── aggregations 3219 └── array-agg [as=array_agg:6] 3220 └── column5:5 3221 3222 build 3223 SELECT array_agg(v+w ORDER BY w) FROM kv 3224 ---- 3225 scalar-group-by 3226 ├── columns: array_agg:6 3227 ├── window partition=() ordering=+3 3228 │ ├── columns: k:1!null v:2 w:3 s:4 column5:5 array_agg:6 3229 │ ├── project 3230 │ │ ├── columns: column5:5 k:1!null v:2 w:3 s:4 3231 │ │ ├── scan kv 3232 │ │ │ └── columns: k:1!null v:2 w:3 s:4 3233 │ │ └── projections 3234 │ │ └── v:2 + w:3 [as=column5:5] 3235 │ └── windows 3236 │ └── array-agg [as=array_agg:6, frame="range from unbounded to unbounded"] 3237 │ └── column5:5 3238 └── aggregations 3239 └── const-agg [as=array_agg:6] 3240 └── array_agg:6 3241 3242 build 3243 SELECT array_agg(v ORDER BY v+w) FROM kv 3244 ---- 3245 scalar-group-by 3246 ├── columns: array_agg:6 3247 ├── window partition=() ordering=+5 3248 │ ├── columns: k:1!null v:2 w:3 s:4 column5:5 array_agg:6 3249 │ ├── project 3250 │ │ ├── columns: column5:5 k:1!null v:2 w:3 s:4 3251 │ │ ├── scan kv 3252 │ │ │ └── columns: k:1!null v:2 w:3 s:4 3253 │ │ └── projections 3254 │ │ └── v:2 + w:3 [as=column5:5] 3255 │ └── windows 3256 │ └── array-agg [as=array_agg:6, frame="range from unbounded to unbounded"] 3257 │ └── v:2 3258 └── aggregations 3259 └── const-agg [as=array_agg:6] 3260 └── array_agg:6 3261 3262 build 3263 SELECT array_agg(a ORDER BY b) FROM (SELECT 1 AS a, 2 AS b) 3264 ---- 3265 scalar-group-by 3266 ├── columns: array_agg:3 3267 ├── window partition=() ordering=+2 3268 │ ├── columns: a:1!null b:2!null array_agg:3 3269 │ ├── project 3270 │ │ ├── columns: a:1!null b:2!null 3271 │ │ ├── values 3272 │ │ │ └── () 3273 │ │ └── projections 3274 │ │ ├── 1 [as=a:1] 3275 │ │ └── 2 [as=b:2] 3276 │ └── windows 3277 │ └── array-agg [as=array_agg:3, frame="range from unbounded to unbounded"] 3278 │ └── a:1 3279 └── aggregations 3280 └── const-agg [as=array_agg:3] 3281 └── array_agg:3 3282 3283 # Regression test for #38551. 3284 build 3285 SELECT * FROM ROWS FROM (count(json_each('[]'))) 3286 ---- 3287 error (0A000): count(): json_each(): generator functions are not allowed in aggregate 3288 3289 # Tests for projecting non-grouping columns when we group by a PK. 3290 build 3291 SELECT v FROM kv GROUP BY k 3292 ---- 3293 project 3294 ├── columns: v:2 3295 └── group-by 3296 ├── columns: k:1!null v:2 3297 ├── grouping columns: k:1!null v:2 3298 └── project 3299 ├── columns: k:1!null v:2 3300 └── scan kv 3301 └── columns: k:1!null v:2 w:3 s:4 3302 3303 # This should be equivalent to the query above. 3304 build 3305 SELECT v FROM kv GROUP BY k, v 3306 ---- 3307 project 3308 ├── columns: v:2 3309 └── group-by 3310 ├── columns: k:1!null v:2 3311 ├── grouping columns: k:1!null v:2 3312 └── project 3313 ├── columns: k:1!null v:2 3314 └── scan kv 3315 └── columns: k:1!null v:2 w:3 s:4 3316 3317 build 3318 SELECT count(*), k+v FROM kv GROUP BY k 3319 ---- 3320 project 3321 ├── columns: count:5!null "?column?":6 3322 ├── group-by 3323 │ ├── columns: k:1!null v:2 count_rows:5!null 3324 │ ├── grouping columns: k:1!null v:2 3325 │ ├── project 3326 │ │ ├── columns: k:1!null v:2 3327 │ │ └── scan kv 3328 │ │ └── columns: k:1!null v:2 w:3 s:4 3329 │ └── aggregations 3330 │ └── count-rows [as=count_rows:5] 3331 └── projections 3332 └── k:1 + v:2 [as="?column?":6] 3333 3334 build 3335 SELECT count(*) FROM kv GROUP BY k HAVING v=1 3336 ---- 3337 project 3338 ├── columns: count:5!null 3339 └── select 3340 ├── columns: k:1!null v:2!null count_rows:5!null 3341 ├── group-by 3342 │ ├── columns: k:1!null v:2 count_rows:5!null 3343 │ ├── grouping columns: k:1!null v:2 3344 │ ├── project 3345 │ │ ├── columns: k:1!null v:2 3346 │ │ └── scan kv 3347 │ │ └── columns: k:1!null v:2 w:3 s:4 3348 │ └── aggregations 3349 │ └── count-rows [as=count_rows:5] 3350 └── filters 3351 └── v:2 = 1 3352 3353 build 3354 SELECT k, v, count(*) FROM kv JOIN ab ON a=k GROUP BY k 3355 ---- 3356 group-by 3357 ├── columns: k:1!null v:2 count:7!null 3358 ├── grouping columns: k:1!null v:2 3359 ├── project 3360 │ ├── columns: k:1!null v:2 3361 │ └── inner-join (hash) 3362 │ ├── columns: k:1!null v:2 w:3 s:4 a:5!null b:6 3363 │ ├── scan kv 3364 │ │ └── columns: k:1!null v:2 w:3 s:4 3365 │ ├── scan ab 3366 │ │ └── columns: a:5!null b:6 3367 │ └── filters 3368 │ └── a:5 = k:1 3369 └── aggregations 3370 └── count-rows [as=count_rows:7] 3371 3372 # Not allowed when grouping on a subset of the PK. 3373 build 3374 SELECT x, y FROM abxy GROUP BY a 3375 ---- 3376 error (42803): column "x" must appear in the GROUP BY clause or be used in an aggregate function 3377 3378 build 3379 SELECT x, y FROM abxy GROUP BY a, b 3380 ---- 3381 project 3382 ├── columns: x:3 y:4 3383 └── group-by 3384 ├── columns: a:1!null b:2!null x:3 y:4 3385 ├── grouping columns: a:1!null b:2!null x:3 y:4 3386 └── scan abxy 3387 └── columns: a:1!null b:2!null x:3 y:4 3388 3389 # The following two should be equivalent to the one above. 3390 build 3391 SELECT x, y FROM abxy GROUP BY x, a, b 3392 ---- 3393 project 3394 ├── columns: x:3 y:4 3395 └── group-by 3396 ├── columns: a:1!null b:2!null x:3 y:4 3397 ├── grouping columns: a:1!null b:2!null x:3 y:4 3398 └── scan abxy 3399 └── columns: a:1!null b:2!null x:3 y:4 3400 3401 build 3402 SELECT x, y FROM abxy GROUP BY x, y, a, b 3403 ---- 3404 project 3405 ├── columns: x:3 y:4 3406 └── group-by 3407 ├── columns: a:1!null b:2!null x:3 y:4 3408 ├── grouping columns: a:1!null b:2!null x:3 y:4 3409 └── scan abxy 3410 └── columns: a:1!null b:2!null x:3 y:4 3411 3412 build 3413 SELECT x, y FROM abxy NATURAL JOIN ab GROUP BY a, b 3414 ---- 3415 project 3416 ├── columns: x:3 y:4 3417 └── group-by 3418 ├── columns: abxy.a:1!null abxy.b:2!null x:3 y:4 3419 ├── grouping columns: abxy.a:1!null abxy.b:2!null x:3 y:4 3420 └── project 3421 ├── columns: abxy.a:1!null abxy.b:2!null x:3 y:4 3422 └── inner-join (hash) 3423 ├── columns: abxy.a:1!null abxy.b:2!null x:3 y:4 ab.a:5!null ab.b:6!null 3424 ├── scan abxy 3425 │ └── columns: abxy.a:1!null abxy.b:2!null x:3 y:4 3426 ├── scan ab 3427 │ └── columns: ab.a:5!null ab.b:6 3428 └── filters 3429 ├── abxy.a:1 = ab.a:5 3430 └── abxy.b:2 = ab.b:6 3431 3432 # Should be equivalent to the one above. 3433 build 3434 SELECT x, y FROM abxy NATURAL JOIN ab GROUP BY a, b, x 3435 ---- 3436 project 3437 ├── columns: x:3 y:4 3438 └── group-by 3439 ├── columns: abxy.a:1!null abxy.b:2!null x:3 y:4 3440 ├── grouping columns: abxy.a:1!null abxy.b:2!null x:3 y:4 3441 └── project 3442 ├── columns: abxy.a:1!null abxy.b:2!null x:3 y:4 3443 └── inner-join (hash) 3444 ├── columns: abxy.a:1!null abxy.b:2!null x:3 y:4 ab.a:5!null ab.b:6!null 3445 ├── scan abxy 3446 │ └── columns: abxy.a:1!null abxy.b:2!null x:3 y:4 3447 ├── scan ab 3448 │ └── columns: ab.a:5!null ab.b:6 3449 └── filters 3450 ├── abxy.a:1 = ab.a:5 3451 └── abxy.b:2 = ab.b:6 3452 3453 build 3454 SELECT abxy.*, ab.* FROM abxy, ab GROUP BY abxy.a, abxy.b, ab.a 3455 ---- 3456 group-by 3457 ├── columns: a:1!null b:2!null x:3 y:4 a:5!null b:6 3458 ├── grouping columns: abxy.a:1!null abxy.b:2!null x:3 y:4 ab.a:5!null ab.b:6 3459 └── inner-join (cross) 3460 ├── columns: abxy.a:1!null abxy.b:2!null x:3 y:4 ab.a:5!null ab.b:6 3461 ├── scan abxy 3462 │ └── columns: abxy.a:1!null abxy.b:2!null x:3 y:4 3463 ├── scan ab 3464 │ └── columns: ab.a:5!null ab.b:6 3465 └── filters (true) 3466 3467 # Not allowed with UNION. 3468 build 3469 SELECT x FROM (SELECT a, b, x FROM abxy UNION SELECT a, b, 1 FROM ab) GROUP BY a,b 3470 ---- 3471 error (42803): column "x" must appear in the GROUP BY clause or be used in an aggregate function 3472 3473 # Allowed with EXCEPT. 3474 build 3475 SELECT x FROM (SELECT a, b, x FROM abxy EXCEPT SELECT a, b, 1 FROM ab) GROUP BY a,b 3476 ---- 3477 project 3478 ├── columns: x:3 3479 └── group-by 3480 ├── columns: abxy.a:1!null abxy.b:2 x:3 3481 ├── grouping columns: abxy.a:1!null abxy.b:2 x:3 3482 └── except 3483 ├── columns: abxy.a:1!null abxy.b:2 x:3 3484 ├── left columns: abxy.a:1!null abxy.b:2 x:3 3485 ├── right columns: ab.a:5 ab.b:6 "?column?":7 3486 ├── project 3487 │ ├── columns: abxy.a:1!null abxy.b:2!null x:3 3488 │ └── scan abxy 3489 │ └── columns: abxy.a:1!null abxy.b:2!null x:3 y:4 3490 └── project 3491 ├── columns: "?column?":7!null ab.a:5!null ab.b:6 3492 ├── scan ab 3493 │ └── columns: ab.a:5!null ab.b:6 3494 └── projections 3495 └── 1 [as="?column?":7] 3496 3497 # Allowed even with outer joins. It's a little subtle why this is correct: the 3498 # PK columns are also non-nullable so any "outer" rows are never in the same 3499 # group with "non-outer" rows. 3500 build 3501 SELECT v, w FROM kv FULL JOIN ab ON k=a GROUP BY k 3502 ---- 3503 project 3504 ├── columns: v:2 w:3 3505 └── group-by 3506 ├── columns: k:1 v:2 w:3 3507 ├── grouping columns: k:1 v:2 w:3 3508 └── project 3509 ├── columns: k:1 v:2 w:3 3510 └── full-join (hash) 3511 ├── columns: k:1 v:2 w:3 s:4 a:5 b:6 3512 ├── scan kv 3513 │ └── columns: k:1!null v:2 w:3 s:4 3514 ├── scan ab 3515 │ └── columns: a:5!null b:6 3516 └── filters 3517 └── k:1 = a:5 3518 3519 # Verify that we handle tables with no primary index (#44659). 3520 build 3521 SELECT table_schema FROM information_schema.columns GROUP BY table_name 3522 ---- 3523 error (42803): column "table_schema" must appear in the GROUP BY clause or be used in an aggregate function 3524 3525 # Tests with aliases (see #28059). 3526 build 3527 SELECT x + 1 AS z FROM abxy GROUP BY z 3528 ---- 3529 group-by 3530 ├── columns: z:5 3531 ├── grouping columns: z:5 3532 └── project 3533 ├── columns: z:5 3534 ├── scan abxy 3535 │ └── columns: a:1!null b:2!null x:3 y:4 3536 └── projections 3537 └── x:3 + 1 [as=z:5] 3538 3539 # The FROM column has precedence, we should be grouping by abxy.x, not by x%10. 3540 build 3541 SELECT (x % 10) AS x FROM abxy GROUP BY x 3542 ---- 3543 project 3544 ├── columns: x:5 3545 ├── group-by 3546 │ ├── columns: abxy.x:3 3547 │ ├── grouping columns: abxy.x:3 3548 │ └── project 3549 │ ├── columns: abxy.x:3 3550 │ └── scan abxy 3551 │ └── columns: a:1!null b:2!null abxy.x:3 y:4 3552 └── projections 3553 └── abxy.x:3 % 10 [as=x:5] 3554 3555 # But aliases have precedence over columns from higher scopes. Here we are 3556 # grouping by v, not by the outer x. 3557 build 3558 SELECT x, (SELECT v AS x FROM kv GROUP BY x) FROM abxy 3559 ---- 3560 project 3561 ├── columns: x:3 x:9 3562 ├── scan abxy 3563 │ └── columns: a:1!null b:2!null abxy.x:3 y:4 3564 └── projections 3565 └── subquery [as=x:9] 3566 └── max1-row 3567 ├── columns: v:6 3568 └── group-by 3569 ├── columns: v:6 3570 ├── grouping columns: v:6 3571 └── project 3572 ├── columns: v:6 3573 └── scan kv 3574 └── columns: k:5!null v:6 w:7 s:8 3575 3576 build 3577 SELECT sum(x) AS u FROM abxy GROUP BY u 3578 ---- 3579 error (42803): sum(): aggregate functions are not allowed in GROUP BY 3580 3581 # Implicit aliases should work too. 3582 build 3583 SELECT x + 1 FROM abxy GROUP BY "?column?" 3584 ---- 3585 group-by 3586 ├── columns: "?column?":5 3587 ├── grouping columns: "?column?":5 3588 └── project 3589 ├── columns: "?column?":5 3590 ├── scan abxy 3591 │ └── columns: a:1!null b:2!null x:3 y:4 3592 └── projections 3593 └── x:3 + 1 [as="?column?":5] 3594 3595 build 3596 SELECT sum(x) FROM abxy GROUP BY sum 3597 ---- 3598 error (42803): sum(): aggregate functions are not allowed in GROUP BY 3599 3600 # Ambiguous aliases should error out. 3601 build 3602 SELECT (x + 1) AS u, (y + 1) AS u FROM abxy GROUP BY u 3603 ---- 3604 error (42702): GROUP BY "u" is ambiguous 3605 3606 # In this case we would have had an outer column if it wasn't for the aliases; 3607 # this should error out just the same. 3608 build 3609 SELECT x, (SELECT v AS x, w AS x FROM kv GROUP BY x) FROM abxy 3610 ---- 3611 error (42702): GROUP BY "x" is ambiguous 3612 3613 # Duplicate expressions with the same alias are not ambiguous. 3614 build 3615 SELECT (x + 1) AS u, (x + 1) AS u FROM abxy GROUP BY u 3616 ---- 3617 group-by 3618 ├── columns: u:5 u:5 3619 ├── grouping columns: u:5 3620 └── project 3621 ├── columns: u:5 3622 ├── scan abxy 3623 │ └── columns: a:1!null b:2!null x:3 y:4 3624 └── projections 3625 └── x:3 + 1 [as=u:5] 3626 3627 build 3628 SELECT (x + 1) AS u, (x + 1) AS u, (y + 1) AS u FROM abxy GROUP BY u 3629 ---- 3630 error (42702): GROUP BY "u" is ambiguous 3631 3632 # In this case, the FROM column has precedence. 3633 build 3634 SELECT sum(x + 1) AS x, sum(y + 1) AS x FROM abxy GROUP BY x 3635 ---- 3636 project 3637 ├── columns: x:6 x:8 3638 └── group-by 3639 ├── columns: x:3 sum:6 sum:8 3640 ├── grouping columns: x:3 3641 ├── project 3642 │ ├── columns: column5:5 column7:7 x:3 3643 │ ├── scan abxy 3644 │ │ └── columns: a:1!null b:2!null x:3 y:4 3645 │ └── projections 3646 │ ├── x:3 + 1 [as=column5:5] 3647 │ └── y:4 + 1 [as=column7:7] 3648 └── aggregations 3649 ├── sum [as=sum:6] 3650 │ └── column5:5 3651 └── sum [as=sum:8] 3652 └── column7:7 3653 3654 # Regression test for #44724. 3655 build 3656 SELECT * 3657 FROM (SELECT 1 AS one, v FROM kv) AS kv 3658 JOIN LATERAL ( 3659 SELECT b, sum(one) FROM abxy 3660 ) AS abxy ON kv.v = abxy.b 3661 ---- 3662 error (42803): aggregate functions are not allowed in FROM clause of their own query level 3663 3664 # Regression test for #45838. The aggregate should be allowed in the WHERE 3665 # clause since it's scoped at the outer level. 3666 build 3667 SELECT sum(x) 3668 FROM abxy AS t 3669 GROUP BY y 3670 HAVING EXISTS(SELECT 1 FROM abxy AS t2 WHERE sum(t.x) = 1) 3671 ---- 3672 project 3673 ├── columns: sum:5 3674 └── select 3675 ├── columns: t.y:4 sum:5 3676 ├── group-by 3677 │ ├── columns: t.y:4 sum:5 3678 │ ├── grouping columns: t.y:4 3679 │ ├── project 3680 │ │ ├── columns: t.x:3 t.y:4 3681 │ │ └── scan t 3682 │ │ └── columns: t.a:1!null t.b:2!null t.x:3 t.y:4 3683 │ └── aggregations 3684 │ └── sum [as=sum:5] 3685 │ └── t.x:3 3686 └── filters 3687 └── exists 3688 └── project 3689 ├── columns: "?column?":11!null 3690 ├── select 3691 │ ├── columns: t2.a:6!null t2.b:7!null t2.x:8 t2.y:9 3692 │ ├── scan t2 3693 │ │ └── columns: t2.a:6!null t2.b:7!null t2.x:8 t2.y:9 3694 │ └── filters 3695 │ └── sum:5 = 1 3696 └── projections 3697 └── 1 [as="?column?":11] 3698 3699 exec-ddl 3700 CREATE TABLE onek ( 3701 unique1 int, 3702 unique2 int, 3703 two int, 3704 four int, 3705 ten int, 3706 twenty int, 3707 hundred int, 3708 thousand int, 3709 twothousand int, 3710 fivethous int, 3711 tenthous int, 3712 odd int, 3713 even int, 3714 stringu1 string, 3715 stringu2 string, 3716 string4 string 3717 ) 3718 ---- 3719 3720 # Regression tests for #30652. 3721 build 3722 SELECT ten, sum(DISTINCT four) 3723 FROM onek AS a 3724 GROUP BY ten 3725 HAVING EXISTS( 3726 SELECT 1 FROM onek AS b WHERE sum(DISTINCT a.four) = b.four 3727 ) 3728 ---- 3729 select 3730 ├── columns: ten:5 sum:18 3731 ├── group-by 3732 │ ├── columns: a.ten:5 sum:18 3733 │ ├── grouping columns: a.ten:5 3734 │ ├── project 3735 │ │ ├── columns: a.four:4 a.ten:5 3736 │ │ └── scan a 3737 │ │ └── columns: a.unique1:1 a.unique2:2 a.two:3 a.four:4 a.ten:5 a.twenty:6 a.hundred:7 a.thousand:8 a.twothousand:9 a.fivethous:10 a.tenthous:11 a.odd:12 a.even:13 a.stringu1:14 a.stringu2:15 a.string4:16 a.rowid:17!null 3738 │ └── aggregations 3739 │ └── agg-distinct [as=sum:18] 3740 │ └── sum 3741 │ └── a.four:4 3742 └── filters 3743 └── exists 3744 └── project 3745 ├── columns: "?column?":37!null 3746 ├── select 3747 │ ├── columns: b.unique1:19 b.unique2:20 b.two:21 b.four:22!null b.ten:23 b.twenty:24 b.hundred:25 b.thousand:26 b.twothousand:27 b.fivethous:28 b.tenthous:29 b.odd:30 b.even:31 b.stringu1:32 b.stringu2:33 b.string4:34 b.rowid:35!null 3748 │ ├── scan b 3749 │ │ └── columns: b.unique1:19 b.unique2:20 b.two:21 b.four:22 b.ten:23 b.twenty:24 b.hundred:25 b.thousand:26 b.twothousand:27 b.fivethous:28 b.tenthous:29 b.odd:30 b.even:31 b.stringu1:32 b.stringu2:33 b.string4:34 b.rowid:35!null 3750 │ └── filters 3751 │ └── sum:18 = b.four:22 3752 └── projections 3753 └── 1 [as="?column?":37] 3754 3755 build 3756 SELECT ten, sum(DISTINCT four) 3757 FROM onek AS a 3758 GROUP BY ten 3759 HAVING EXISTS( 3760 SELECT 1 3761 FROM onek AS b 3762 WHERE sum(DISTINCT a.four + b.four) = b.four 3763 ) 3764 ---- 3765 error (42803): aggregate functions are not allowed in WHERE 3766 3767 build 3768 SELECT ( 3769 SELECT t2.a 3770 FROM abxy AS t2 JOIN abxy AS t3 ON sum(t1.x) = t3.x 3771 ) 3772 FROM abxy AS t1 3773 ---- 3774 project 3775 ├── columns: a:15 3776 ├── scalar-group-by 3777 │ ├── columns: sum:14 3778 │ ├── project 3779 │ │ ├── columns: x:13 3780 │ │ ├── scan t1 3781 │ │ │ └── columns: t1.a:1!null t1.b:2!null t1.x:3 t1.y:4 3782 │ │ └── projections 3783 │ │ └── t1.x:3 [as=x:13] 3784 │ └── aggregations 3785 │ └── sum [as=sum:14] 3786 │ └── x:13 3787 └── projections 3788 └── subquery [as=a:15] 3789 └── max1-row 3790 ├── columns: t2.a:5!null 3791 └── project 3792 ├── columns: t2.a:5!null 3793 └── inner-join (cross) 3794 ├── columns: t2.a:5!null t2.b:6!null t2.x:7 t2.y:8 t3.a:9!null t3.b:10!null t3.x:11!null t3.y:12 3795 ├── scan t2 3796 │ └── columns: t2.a:5!null t2.b:6!null t2.x:7 t2.y:8 3797 ├── scan t3 3798 │ └── columns: t3.a:9!null t3.b:10!null t3.x:11 t3.y:12 3799 └── filters 3800 └── sum:14 = t3.x:11 3801 3802 # Regression test for #45631. 3803 build 3804 SELECT 3805 (SELECT (max(b), unnest) FROM ab WHERE a = unnest) 3806 FROM 3807 ROWS FROM (unnest(ARRAY[1, 2])) 3808 ---- 3809 project 3810 ├── columns: "?column?":7 3811 ├── project-set 3812 │ ├── columns: unnest:1 3813 │ ├── values 3814 │ │ └── () 3815 │ └── zip 3816 │ └── unnest(ARRAY[1,2]) 3817 └── projections 3818 └── subquery [as="?column?":7] 3819 └── max1-row 3820 ├── columns: "?column?":6 3821 └── project 3822 ├── columns: "?column?":6 3823 ├── group-by 3824 │ ├── columns: max:4 unnest:5 3825 │ ├── grouping columns: unnest:5 3826 │ ├── project 3827 │ │ ├── columns: unnest:5 b:3 3828 │ │ ├── select 3829 │ │ │ ├── columns: a:2!null b:3 3830 │ │ │ ├── scan ab 3831 │ │ │ │ └── columns: a:2!null b:3 3832 │ │ │ └── filters 3833 │ │ │ └── a:2 = unnest:1 3834 │ │ └── projections 3835 │ │ └── unnest:1 [as=unnest:5] 3836 │ └── aggregations 3837 │ └── max [as=max:4] 3838 │ └── b:3 3839 └── projections 3840 └── (max:4, unnest:1) [as="?column?":6] 3841 3842 # Regression test for #46196. Don't eliminate the scalar group by, and 3843 # default to type string. 3844 build format=show-types 3845 SELECT max(t0.c0) FROM (VALUES (NULL), (NULL)) t0(c0); 3846 ---- 3847 scalar-group-by 3848 ├── columns: max:3(string) 3849 ├── project 3850 │ ├── columns: column2:2(string) 3851 │ ├── values 3852 │ │ ├── columns: column1:1(unknown) 3853 │ │ ├── (NULL,) [type=tuple{unknown}] 3854 │ │ └── (NULL,) [type=tuple{unknown}] 3855 │ └── projections 3856 │ └── column1:1::STRING [as=column2:2, type=string] 3857 └── aggregations 3858 └── max [as=max:3, type=string] 3859 └── column2:2 [type=string] 3860 3861 build 3862 SELECT percentile_disc(0.95) WITHIN GROUP (ORDER BY b) FROM abc; 3863 ---- 3864 scalar-group-by 3865 ├── columns: percentile_disc:6 3866 ├── window partition=() ordering=+2 3867 │ ├── columns: a:1!null b:2 c:3 d:4 column5:5!null percentile_disc:6 3868 │ ├── project 3869 │ │ ├── columns: column5:5!null a:1!null b:2 c:3 d:4 3870 │ │ ├── scan abc 3871 │ │ │ └── columns: a:1!null b:2 c:3 d:4 3872 │ │ └── projections 3873 │ │ └── 0.95 [as=column5:5] 3874 │ └── windows 3875 │ └── percentile-disc [as=percentile_disc:6, frame="range from unbounded to unbounded"] 3876 │ ├── column5:5 3877 │ └── b:2 3878 └── aggregations 3879 └── const-agg [as=percentile_disc:6] 3880 └── percentile_disc:6 3881 3882 build 3883 SELECT percentile_cont(0.95) WITHIN GROUP (ORDER BY b) FROM abc; 3884 ---- 3885 scalar-group-by 3886 ├── columns: percentile_cont:6 3887 ├── window partition=() ordering=+2 3888 │ ├── columns: a:1!null b:2 c:3 d:4 column5:5!null percentile_cont:6 3889 │ ├── project 3890 │ │ ├── columns: column5:5!null a:1!null b:2 c:3 d:4 3891 │ │ ├── scan abc 3892 │ │ │ └── columns: a:1!null b:2 c:3 d:4 3893 │ │ └── projections 3894 │ │ └── 0.95 [as=column5:5] 3895 │ └── windows 3896 │ └── percentile-cont [as=percentile_cont:6, frame="range from unbounded to unbounded"] 3897 │ ├── column5:5 3898 │ └── b:2 3899 └── aggregations 3900 └── const-agg [as=percentile_cont:6] 3901 └── percentile_cont:6 3902 3903 build 3904 SELECT percentile_disc(ARRAY[0.90, 0.95]::float[]) WITHIN GROUP (ORDER BY b) FROM abc; 3905 ---- 3906 scalar-group-by 3907 ├── columns: percentile_disc:6 3908 ├── window partition=() ordering=+2 3909 │ ├── columns: a:1!null b:2 c:3 d:4 column5:5!null percentile_disc:6 3910 │ ├── project 3911 │ │ ├── columns: column5:5!null a:1!null b:2 c:3 d:4 3912 │ │ ├── scan abc 3913 │ │ │ └── columns: a:1!null b:2 c:3 d:4 3914 │ │ └── projections 3915 │ │ └── ARRAY[0.90,0.95]::FLOAT8[] [as=column5:5] 3916 │ └── windows 3917 │ └── percentile-disc [as=percentile_disc:6, frame="range from unbounded to unbounded"] 3918 │ ├── column5:5 3919 │ └── b:2 3920 └── aggregations 3921 └── const-agg [as=percentile_disc:6] 3922 └── percentile_disc:6 3923 3924 build 3925 SELECT percentile_cont(ARRAY[0.90, 0.95]::float[]) WITHIN GROUP (ORDER BY b) FROM abc; 3926 ---- 3927 scalar-group-by 3928 ├── columns: percentile_cont:6 3929 ├── window partition=() ordering=+2 3930 │ ├── columns: a:1!null b:2 c:3 d:4 column5:5!null percentile_cont:6 3931 │ ├── project 3932 │ │ ├── columns: column5:5!null a:1!null b:2 c:3 d:4 3933 │ │ ├── scan abc 3934 │ │ │ └── columns: a:1!null b:2 c:3 d:4 3935 │ │ └── projections 3936 │ │ └── ARRAY[0.90,0.95]::FLOAT8[] [as=column5:5] 3937 │ └── windows 3938 │ └── percentile-cont [as=percentile_cont:6, frame="range from unbounded to unbounded"] 3939 │ ├── column5:5 3940 │ └── b:2 3941 └── aggregations 3942 └── const-agg [as=percentile_cont:6] 3943 └── percentile_cont:6