github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/sql/logictest/testdata/logic_test/aggregate (about) 1 # LogicTest: !3node-tenant 2 subtest other 3 4 statement ok 5 CREATE TABLE kv ( 6 k INT PRIMARY KEY, 7 v INT, 8 w INT, 9 s STRING, 10 i INTERVAL 11 ) 12 13 # Aggregate functions return NULL if there are no rows. 14 query IIIIRRRRRRBBTII 15 SELECT min(1), max(1), count(1), sum_int(1), avg(1), sum(1), stddev(1), stddev_samp(1), var_samp(1), variance(1), bool_and(true), bool_and(false), xor_agg(b'\x01'), bit_and(1), bit_or(1) FROM kv 16 ---- 17 NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 18 19 # Regression test for #29695 20 query T 21 SELECT min(NULL) 22 ---- 23 NULL 24 25 # Aggregate functions return NULL if there are no rows. 26 query T 27 SELECT array_agg(1) FROM kv 28 ---- 29 NULL 30 31 query T 32 SELECT json_agg(1) FROM kv 33 ---- 34 NULL 35 36 query T 37 SELECT jsonb_agg(1) FROM kv 38 ---- 39 NULL 40 41 query TTTT 42 SELECT min(i), avg(i), max(i), sum(i) FROM kv 43 ---- 44 NULL NULL NULL NULL 45 46 query IIIIRRRRBBT 47 SELECT min(v), max(v), count(v), sum_int(1), avg(v), sum(v), stddev(v), variance(v), bool_and(v = 1), bool_and(v = 1), xor_agg(s::bytes) FROM kv 48 ---- 49 NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL NULL 50 51 query T 52 SELECT array_agg(v) FROM kv 53 ---- 54 NULL 55 56 query T 57 SELECT json_agg(v) FROM kv 58 ---- 59 NULL 60 61 query T 62 SELECT jsonb_agg(v) FROM kv 63 ---- 64 NULL 65 66 # Aggregate functions triggers aggregation and computation when there is no source. 67 query IIIIRRRRBBT 68 SELECT min(1), count(1), max(1), sum_int(1), avg(1)::float, sum(1), stddev_samp(1), variance(1), bool_and(true), bool_or(true), to_hex(xor_agg(b'\x01')) 69 ---- 70 1 1 1 1 1 1 NULL NULL true true 01 71 72 # Aggregate functions triggers aggregation and computation when there is no source. 73 query T 74 SELECT array_agg(1) 75 ---- 76 {1} 77 78 query T 79 SELECT json_agg(1) 80 ---- 81 [1] 82 83 query T 84 SELECT jsonb_agg(1) 85 ---- 86 [1] 87 88 # Some aggregate functions are not normalized to NULL when given a NULL 89 # argument. 90 query I 91 SELECT count(NULL) 92 ---- 93 0 94 95 query T 96 SELECT json_agg(NULL) 97 ---- 98 [null] 99 100 query T 101 SELECT jsonb_agg(NULL) 102 ---- 103 [null] 104 105 # This should ideally return {NULL}, but this is a pathological case, and 106 # Postgres has the same behavior, so it's sufficient for now. 107 statement error ambiguous call 108 SELECT array_agg(NULL) 109 110 # With an explicit cast, this works as expected. 111 query T 112 SELECT array_agg(NULL::TEXT) 113 ---- 114 {NULL} 115 116 # Regression test for #25724 (problem with typed NULLs and distsql planning). 117 # The previous query doesn't run under distsql. 118 query T 119 SELECT array_agg(NULL::TEXT) FROM (VALUES (1)) AS t(x) 120 ---- 121 {NULL} 122 123 # Check that COALESCE using aggregate results over an empty table 124 # work properly. 125 query I 126 SELECT COALESCE(max(1), 0) FROM generate_series(1,0) 127 ---- 128 0 129 130 query I 131 SELECT count_rows() FROM generate_series(1,100) 132 ---- 133 100 134 135 # Same, using arithmetic on COUNT. 136 query I 137 SELECT 1 + count(*) FROM generate_series(1,0) 138 ---- 139 1 140 141 # Same, using an empty table. 142 # The following test *must* occur before the first INSERT to the tables, 143 # so that it can observe an empty table. 144 query II 145 SELECT count(*), COALESCE(max(k), 1) FROM kv 146 ---- 147 0 1 148 149 # Same, using a subquery. (#12705) 150 query I 151 SELECT (SELECT COALESCE(max(1), 0) FROM generate_series(1,0)) 152 ---- 153 0 154 155 statement OK 156 INSERT INTO kv VALUES 157 (1, 2, 3, 'a', '1min'), 158 (3, 4, 5, 'a', '2sec'), 159 (5, NULL, 5, NULL, NULL), 160 (6, 2, 3, 'b', '1ms'), 161 (7, 2, 2, 'b', '4 days'), 162 (8, 4, 2, 'A', '3 years') 163 164 # Aggregate functions triggers aggregation and computation for every row even when applied to a constant. 165 # NB: The XOR result is 00 because \x01 is XOR'd an even number of times. 166 query IIIIRRRRBBT 167 SELECT min(1), count(1), max(1), sum_int(1), avg(1)::float, sum(1), stddev(1), variance(1)::float, bool_and(true), bool_or(true), to_hex(xor_agg(b'\x01')) FROM kv 168 ---- 169 1 6 1 6 1 6 0 0 true true 00 170 171 # Aggregate functions triggers aggregation and computation for every row even when applied to a constant. 172 query T 173 SELECT array_agg(1) FROM kv 174 ---- 175 {1,1,1,1,1,1} 176 177 query T 178 SELECT json_agg(1) FROM kv 179 ---- 180 [1, 1, 1, 1, 1, 1] 181 182 query T 183 SELECT jsonb_agg(1) FROM kv 184 ---- 185 [1, 1, 1, 1, 1, 1] 186 187 # Even with no aggregate functions, grouping occurs in the presence of GROUP BY. 188 query I rowsort 189 SELECT 1 FROM kv GROUP BY v 190 ---- 191 1 192 1 193 1 194 195 # Presence of HAVING triggers aggregation, reducing results to one row (even without GROUP BY). 196 query I rowsort 197 SELECT 3 FROM kv HAVING TRUE 198 ---- 199 3 200 201 query error pgcode 42803 column "k" must appear in the GROUP BY clause or be used in an aggregate function 202 SELECT count(*), k FROM kv 203 204 query error unsupported comparison operator: <string> < <int> 205 SELECT count(*) FROM kv GROUP BY s < 5 206 207 query II rowsort 208 SELECT count(*), k FROM kv GROUP BY k 209 ---- 210 1 1 211 1 3 212 1 5 213 1 6 214 1 7 215 1 8 216 217 # GROUP BY specified using column index works. 218 query II rowsort 219 SELECT count(*), k FROM kv GROUP BY 2 220 ---- 221 1 1 222 1 3 223 1 5 224 1 6 225 1 7 226 1 8 227 228 query error aggregate functions are not allowed in GROUP BY 229 SELECT * FROM kv GROUP BY v, count(DISTINCT w) 230 231 query error aggregate functions are not allowed in GROUP BY 232 SELECT count(DISTINCT w) FROM kv GROUP BY 1 233 234 query error aggregate functions are not allowed in RETURNING 235 INSERT INTO kv (k, v) VALUES (99, 100) RETURNING sum(v) 236 237 query error column "v" does not exist 238 SELECT sum(v) FROM kv GROUP BY k LIMIT sum(v) 239 240 query error column "v" does not exist 241 SELECT sum(v) FROM kv GROUP BY k LIMIT 1 OFFSET sum(v) 242 243 query error aggregate functions are not allowed in VALUES 244 INSERT INTO kv (k, v) VALUES (99, count(1)) 245 246 query error pgcode 42P10 GROUP BY position 5 is not in select list 247 SELECT count(*), k FROM kv GROUP BY 5 248 249 query error pgcode 42P10 GROUP BY position 0 is not in select list 250 SELECT count(*), k FROM kv GROUP BY 0 251 252 query error pgcode 42601 non-integer constant in GROUP BY 253 SELECT 1 GROUP BY 'a' 254 255 # Qualifying a name in the SELECT, the GROUP BY, both or neither should not affect validation. 256 query IT rowsort 257 SELECT count(*), kv.s FROM kv GROUP BY s 258 ---- 259 1 A 260 1 NULL 261 2 a 262 2 b 263 264 query IT rowsort 265 SELECT count(*), s FROM kv GROUP BY kv.s 266 ---- 267 1 A 268 1 NULL 269 2 a 270 2 b 271 272 query IT rowsort 273 SELECT count(*), kv.s FROM kv GROUP BY kv.s 274 ---- 275 1 A 276 1 NULL 277 2 a 278 2 b 279 280 query IT rowsort 281 SELECT count(*), s FROM kv GROUP BY s 282 ---- 283 1 A 284 1 NULL 285 2 a 286 2 b 287 288 # Grouping by more than one column works. 289 query III rowsort 290 SELECT v, count(*), w FROM kv GROUP BY v, w 291 ---- 292 2 1 2 293 2 2 3 294 4 1 2 295 4 1 5 296 NULL 1 5 297 298 # Grouping by more than one column using column numbers works. 299 query III rowsort 300 SELECT v, count(*), w FROM kv GROUP BY 1, 3 301 ---- 302 2 1 2 303 2 2 3 304 4 1 2 305 4 1 5 306 NULL 1 5 307 308 # Selecting and grouping on a function expression works. 309 query IT rowsort 310 SELECT count(*), upper(s) FROM kv GROUP BY upper(s) 311 ---- 312 1 NULL 313 2 B 314 3 A 315 316 # Selecting and grouping on a constant works. 317 query I 318 SELECT count(*) FROM kv GROUP BY 1+2 319 ---- 320 6 321 322 query I 323 SELECT count(*) FROM kv GROUP BY length('abc') 324 ---- 325 6 326 327 # Selecting a function of something which is grouped works. 328 query IT rowsort 329 SELECT count(*), upper(s) FROM kv GROUP BY s 330 ---- 331 1 A 332 1 NULL 333 2 A 334 2 B 335 336 # Selecting a value that is not grouped, even if a function of it it, does not work. 337 query error column "s" must appear in the GROUP BY clause or be used in an aggregate function 338 SELECT count(*), s FROM kv GROUP BY upper(s) 339 340 # Selecting and grouping on a more complex expression works. 341 query II rowsort 342 SELECT count(*), k+v FROM kv GROUP BY k+v 343 ---- 344 1 12 345 1 3 346 1 7 347 1 8 348 1 9 349 1 NULL 350 351 352 # Selecting a more complex expression, made up of things which are each grouped, works. 353 query II rowsort 354 SELECT count(*), k+v FROM kv GROUP BY k, v 355 ---- 356 1 12 357 1 3 358 1 7 359 1 8 360 1 9 361 1 NULL 362 363 query II rowsort 364 SELECT count(*), k+v FROM kv GROUP BY k 365 ---- 366 1 3 367 1 7 368 1 NULL 369 1 8 370 1 9 371 1 12 372 373 query error column "k" must appear in the GROUP BY clause or be used in an aggregate function 374 SELECT count(*), k+v FROM kv GROUP BY v 375 376 query error column "v" must appear in the GROUP BY clause or be used in an aggregate function 377 SELECT count(*), v/(k+v) FROM kv GROUP BY k+v 378 379 query error aggregate functions are not allowed in WHERE 380 SELECT k FROM kv WHERE avg(k) > 1 381 382 query error aggregate function calls cannot be nested 383 SELECT max(avg(k)) FROM kv 384 385 # Test case from #2761. 386 query II rowsort 387 SELECT count(kv.k) AS count_1, kv.v + kv.w AS lx FROM kv GROUP BY kv.v + kv.w 388 ---- 389 1 4 390 1 6 391 1 9 392 1 NULL 393 2 5 394 395 query TI rowsort 396 SELECT s, count(*) FROM kv GROUP BY s HAVING count(*) > 1 397 ---- 398 a 2 399 b 2 400 401 query TII rowsort 402 SELECT upper(s), count(DISTINCT s), count(DISTINCT upper(s)) FROM kv GROUP BY upper(s) HAVING count(DISTINCT s) > 1 403 ---- 404 A 2 1 405 406 query II rowsort 407 SELECT max(k), min(v) FROM kv HAVING min(v) > 2 408 ---- 409 410 query II rowsort 411 SELECT max(k), min(v) FROM kv HAVING max(v) > 2 412 ---- 413 8 2 414 415 query error pgcode 42803 aggregate function calls cannot be nested 416 SELECT max(k), min(v) FROM kv HAVING max(min(v)) > 2 417 418 query error argument of HAVING must be type bool, not type int 419 SELECT max(k), min(v) FROM kv HAVING k 420 421 # Expressions listed in the HAVING clause must conform to same validation as the SELECT clause (grouped or aggregated). 422 query error column "k" must appear in the GROUP BY clause or be used in an aggregate function 423 SELECT 3 FROM kv GROUP BY v HAVING k > 5 424 425 # Special case for grouping on primary key. 426 query I 427 SELECT 3 FROM kv GROUP BY k HAVING v > 2 428 ---- 429 3 430 3 431 432 query error column "k" must appear in the GROUP BY clause or be used in an aggregate function 433 SELECT k FROM kv HAVING k > 7 434 435 query error at or near ",": syntax error 436 SELECT count(*, 1) FROM kv 437 438 query I 439 SELECT count(*) 440 ---- 441 1 442 443 query I 444 SELECT count(k) from kv 445 ---- 446 6 447 448 query I 449 SELECT count(1) 450 ---- 451 1 452 453 query I 454 SELECT count(1) from kv 455 ---- 456 6 457 458 query error unknown signature: count\(int, int\) 459 SELECT count(k, v) FROM kv 460 461 query II 462 SELECT v, count(k) FROM kv GROUP BY v ORDER BY v 463 ---- 464 NULL 1 465 2 3 466 4 2 467 468 query II 469 SELECT v, count(k) FROM kv GROUP BY v ORDER BY v DESC 470 ---- 471 4 2 472 2 3 473 NULL 1 474 475 query II 476 SELECT v, count(k) FROM kv GROUP BY v ORDER BY count(k) DESC 477 ---- 478 2 3 479 4 2 480 NULL 1 481 482 query II 483 SELECT v, count(k) FROM kv GROUP BY v ORDER BY v-count(k) 484 ---- 485 NULL 1 486 2 3 487 4 2 488 489 query II 490 SELECT v, count(k) FROM kv GROUP BY v ORDER BY 1 DESC 491 ---- 492 4 2 493 2 3 494 NULL 1 495 496 query III colnames 497 SELECT count(*), count(k), count(kv.v) FROM kv 498 ---- 499 count count count 500 6 6 5 501 502 query I 503 SELECT count(kv.*) FROM kv 504 ---- 505 6 506 507 query III 508 SELECT count(DISTINCT k), count(DISTINCT v), count(DISTINCT (v)) FROM kv 509 ---- 510 6 2 2 511 512 query TIII rowsort 513 SELECT upper(s), count(DISTINCT k), count(DISTINCT v), count(DISTINCT (v)) FROM kv GROUP BY upper(s) 514 ---- 515 A 3 2 2 516 B 2 1 1 517 NULL 1 0 0 518 519 520 query I 521 SELECT count((k, v)) FROM kv 522 ---- 523 6 524 525 query I 526 SELECT count(DISTINCT (k, v)) FROM kv 527 ---- 528 6 529 530 query I 531 SELECT count(DISTINCT (k, (v))) FROM kv 532 ---- 533 6 534 535 query I 536 SELECT count(*) FROM kv a, kv b 537 ---- 538 36 539 540 query I 541 SELECT count(DISTINCT a.*) FROM kv a, kv b 542 ---- 543 6 544 545 query I 546 SELECT count((k, v)) FROM kv LIMIT 1 547 ---- 548 6 549 550 query I 551 SELECT count((k, v)) FROM kv OFFSET 1 552 ---- 553 554 query I 555 SELECT count(k)+count(kv.v) FROM kv 556 ---- 557 11 558 559 query II 560 SELECT count(NULL::int), count((NULL, NULL)) 561 ---- 562 0 1 563 564 query IIII 565 SELECT min(k), max(k), min(v), max(v) FROM kv 566 ---- 567 1 8 2 4 568 569 # Even if no input rows match, we expect a row (of nulls). 570 query IIII 571 SELECT min(k), max(k), min(v), max(v) FROM kv WHERE k > 8 572 ---- 573 NULL NULL NULL NULL 574 575 query TT 576 SELECT array_agg(k), array_agg(s) FROM (SELECT k, s FROM kv ORDER BY k) 577 ---- 578 {1,3,5,6,7,8} {a,a,NULL,b,b,A} 579 580 query T 581 SELECT array_agg(k) || 1 FROM (SELECT k FROM kv ORDER BY k) 582 ---- 583 {1,3,5,6,7,8,1} 584 585 query T 586 SELECT array_agg(s) FROM kv WHERE s IS NULL 587 ---- 588 {NULL} 589 590 query T 591 SELECT json_agg(s) FROM kv WHERE s IS NULL 592 ---- 593 [null] 594 595 query T 596 SELECT jsonb_agg(s) FROM kv WHERE s IS NULL 597 ---- 598 [null] 599 600 query RRRR 601 SELECT avg(k), avg(v), sum(k), sum(v) FROM kv 602 ---- 603 5 2.8 30 14 604 605 query TTTT 606 SELECT min(i), avg(i), max(i), sum(i) FROM kv 607 ---- 608 00:00:00.001 7 mons 6 days 19:12:12.4002 3 years 3 years 4 days 00:01:02.001 609 610 query RRRR 611 SELECT avg(k::decimal), avg(v::decimal), sum(k::decimal), sum(v::decimal) FROM kv 612 ---- 613 5 2.8 30 14 614 615 query RRRR 616 SELECT avg(DISTINCT k), avg(DISTINCT v), sum(DISTINCT k), sum(DISTINCT v) FROM kv 617 ---- 618 5 3 30 6 619 620 query R 621 SELECT avg(k) * 2.0 + max(v)::DECIMAL FROM kv 622 ---- 623 14.0 624 625 # Verify things work with distsql when some of the nodes emit no results in the 626 # local stage. 627 query R 628 SELECT avg(k) * 2.0 + max(v)::DECIMAL FROM kv WHERE w*2 = k 629 ---- 630 14.0 631 632 # Grouping columns can be eliminated, but should still return zero rows (i.e. 633 # shouldn't use scalar GroupBy). 634 query I 635 SELECT max(v) FROM kv GROUP BY k HAVING k=100 636 ---- 637 638 # Same query as above, but using scalar GroupBy (should return default row). 639 query I 640 SELECT max(v) FROM kv WHERE k=100 641 ---- 642 NULL 643 644 statement ok 645 CREATE TABLE abc ( 646 a VARCHAR PRIMARY KEY, 647 b FLOAT, 648 c BOOLEAN, 649 d DECIMAL 650 ) 651 652 statement ok 653 INSERT INTO abc VALUES ('one', 1.5, true, 5::decimal), ('two', 2.0, false, 1.1::decimal) 654 655 query TRBR 656 SELECT min(a), min(b), min(c), min(d) FROM abc 657 ---- 658 one 1.5 false 1.1 659 660 query TRBR 661 SELECT max(a), max(b), max(c), max(d) FROM abc 662 ---- 663 two 2 true 5 664 665 query RRRR 666 SELECT avg(b), sum(b), avg(d), sum(d) FROM abc 667 ---- 668 1.75 3.5 3.05 6.1 669 670 # Verify summing of intervals 671 statement ok 672 CREATE TABLE intervals ( 673 a INTERVAL PRIMARY KEY 674 ) 675 676 statement ok 677 INSERT INTO intervals VALUES (INTERVAL '1 year 2 months 3 days 4 seconds'), (INTERVAL '2 year 3 months 4 days 5 seconds'), (INTERVAL '10000ms') 678 679 query T 680 SELECT sum(a) FROM intervals 681 ---- 682 3 years 5 mons 7 days 00:00:19 683 684 685 query error unknown signature: avg\(varchar\) 686 SELECT avg(a) FROM abc 687 688 query error unknown signature: avg\(bool\) 689 SELECT avg(c) FROM abc 690 691 query error unknown signature: avg\(tuple{varchar, bool}\) 692 SELECT avg((a,c)) FROM abc 693 694 query error unknown signature: sum\(varchar\) 695 SELECT sum(a) FROM abc 696 697 query error unknown signature: sum\(bool\) 698 SELECT sum(c) FROM abc 699 700 query error unknown signature: sum\(tuple{varchar, bool}\) 701 SELECT sum((a,c)) FROM abc 702 703 statement ok 704 CREATE TABLE xyz ( 705 x INT PRIMARY KEY, 706 y INT, 707 z FLOAT, 708 w INT, 709 INDEX xy (x, y), 710 INDEX zyx (z, y, x), 711 INDEX w (w), 712 FAMILY (x), 713 FAMILY (y), 714 FAMILY (z) 715 ) 716 717 statement ok 718 INSERT INTO xyz VALUES (1, 2, 3.0, NULL), (4, 5, 6.0, 2), (7, NULL, 8.0, 3) 719 720 query I 721 SELECT min(x) FROM xyz 722 ---- 723 1 724 725 query I 726 SELECT min(y) FROM xyz 727 ---- 728 2 729 730 query I 731 SELECT min(w) FROM xyz 732 ---- 733 2 734 735 query I 736 SELECT min(x) FROM xyz WHERE x in (0, 4, 7) 737 ---- 738 4 739 740 query I 741 SELECT max(x) FROM xyz 742 ---- 743 7 744 745 query I 746 SELECT min(y) FROM xyz WHERE x = 1 747 ---- 748 2 749 750 query I 751 SELECT max(y) FROM xyz WHERE x = 1 752 ---- 753 2 754 755 query I 756 SELECT min(y) FROM xyz WHERE x = 7 757 ---- 758 NULL 759 760 query I 761 SELECT max(y) FROM xyz WHERE x = 7 762 ---- 763 NULL 764 765 query I 766 SELECT min(x) FROM xyz WHERE (y, z) = (2, 3.0) 767 ---- 768 1 769 770 query I 771 SELECT max(x) FROM xyz WHERE (z, y) = (3.0, 2) 772 ---- 773 1 774 775 # VARIANCE/STDDEV 776 777 query RRR 778 SELECT var_samp(x), variance(y::decimal), round(var_samp(z), 14) FROM xyz 779 ---- 780 9 4.5 6.33333333333333 781 782 query R 783 SELECT variance(x) FROM xyz WHERE x = 10 784 ---- 785 NULL 786 787 query R 788 SELECT variance(x) FROM xyz WHERE x = 1 789 ---- 790 NULL 791 792 query RRR 793 SELECT stddev_samp(x), stddev(y::decimal), round(stddev_samp(z), 14) FROM xyz 794 ---- 795 3 2.1213203435596425732 2.51661147842358 796 797 query R 798 SELECT stddev(x) FROM xyz WHERE x = 1 799 ---- 800 NULL 801 802 # Ensure subqueries don't trigger aggregation. 803 query B 804 SELECT x > (SELECT avg(0)) FROM xyz LIMIT 1 805 ---- 806 true 807 808 statement ok 809 DROP TABLE xyz 810 811 # Numerical stability test for VARIANCE/STDDEV. 812 # See https://www.johndcook.com/blog/2008/09/28/theoretical-explanation-for-numerical-results. 813 # Avoid using random() since we do not have the deterministic option to specify a pseudo-random seed yet. 814 # Note under distsql, this is non-deterministic since the running variance/stddev algorithms depend on 815 # the local sum of squared difference values which depend on how the data is distributed across the distsql nodes. 816 statement ok 817 CREATE TABLE mnop ( 818 m INT PRIMARY KEY, 819 n FLOAT, 820 o DECIMAL, 821 p BIGINT 822 ) 823 824 statement ok 825 INSERT INTO mnop (m, n) SELECT i, (1e9 + i/2e4)::float FROM 826 generate_series(1, 2e4) AS i(i) 827 828 statement ok 829 UPDATE mnop SET o = n::decimal, p = (n * 10)::bigint 830 831 query RRR 832 SELECT round(variance(n), 2), round(variance(n), 2), round(variance(p)) FROM mnop 833 ---- 834 0.08 0.08 8 835 836 837 query RRR 838 SELECT round(stddev_samp(n), 2), round(stddev(n), 2), round(stddev_samp(p)) FROM mnop 839 ---- 840 0.29 0.29 3 841 842 query RRR 843 SELECT avg(1::int)::float, avg(2::float)::float, avg(3::decimal)::float 844 ---- 845 1 2 3 846 847 query III 848 SELECT count(2::int), count(3::float), count(4::decimal) 849 ---- 850 1 1 1 851 852 query RRR 853 SELECT sum(1::int), sum(2::float), sum(3::decimal) 854 ---- 855 1 2 3 856 857 query RRR 858 SELECT variance(1::int), variance(1::float), variance(1::decimal) 859 ---- 860 NULL NULL NULL 861 862 query RRR 863 SELECT stddev(1::int), stddev_samp(1::float), stddev(1::decimal) 864 ---- 865 NULL NULL NULL 866 867 statement ok 868 CREATE TABLE bits (b INT) 869 870 query II 871 SELECT bit_and(b), bit_or(b) FROM bits 872 ---- 873 NULL NULL 874 875 statement ok 876 INSERT INTO bits VALUES (12), (25) 877 878 query II 879 SELECT bit_and(b), bit_or(b) FROM bits 880 ---- 881 8 29 882 883 statement ok 884 INSERT INTO bits VALUES(105) 885 886 query II 887 SELECT bit_and(b), bit_or(b) FROM bits 888 ---- 889 8 125 890 891 statement ok 892 INSERT INTO bits VALUES(NULL) 893 894 query II 895 SELECT bit_and(b), bit_or(b) FROM bits 896 ---- 897 8 125 898 899 statement ok 900 CREATE TABLE bools (b BOOL) 901 902 query BB 903 SELECT bool_and(b), bool_or(b) FROM bools 904 ---- 905 NULL NULL 906 907 statement OK 908 INSERT INTO bools VALUES (true), (true), (true) 909 910 query BB 911 SELECT bool_and(b), bool_or(b) FROM bools 912 ---- 913 true true 914 915 statement OK 916 INSERT INTO bools VALUES (false), (false) 917 918 query BB 919 SELECT bool_and(b), bool_or(b) FROM bools 920 ---- 921 false true 922 923 statement OK 924 DELETE FROM bools WHERE b 925 926 query BB 927 SELECT bool_and(b), bool_or(b) FROM bools 928 ---- 929 false false 930 931 query T 932 SELECT concat_agg(s) FROM (SELECT s FROM kv ORDER BY k) 933 ---- 934 aabbA 935 936 query T 937 SELECT json_agg(s) FROM (SELECT s FROM kv ORDER BY k) 938 ---- 939 ["a", "a", null, "b", "b", "A"] 940 941 query T 942 SELECT jsonb_agg(s) FROM (SELECT s FROM kv ORDER BY k) 943 ---- 944 ["a", "a", null, "b", "b", "A"] 945 946 # Verify that FILTER works. 947 948 statement ok 949 CREATE TABLE filter_test ( 950 k INT, 951 v INT, 952 mark BOOL 953 ) 954 955 statement OK 956 INSERT INTO filter_test VALUES 957 (1, 2, false), 958 (3, 4, true), 959 (5, NULL, true), 960 (6, 2, true), 961 (7, 2, true), 962 (8, 4, true), 963 (NULL, 4, true) 964 965 # FILTER should eliminate some results. 966 query II rowsort 967 SELECT v, count(*) FILTER (WHERE k > 5) FROM filter_test GROUP BY v 968 ---- 969 2 2 970 4 1 971 NULL 0 972 973 # Test multiple filters 974 query IBIII rowsort 975 SELECT v, mark, count(*) FILTER (WHERE k > 5), count(*), max(k) FILTER (WHERE k < 8) FROM filter_test GROUP BY v, mark 976 ---- 977 2 false 0 1 1 978 2 true 2 2 7 979 4 true 1 3 3 980 NULL true 0 1 5 981 982 query error FILTER specified but abs\(\) is not an aggregate function 983 SELECT k, abs(k) FILTER (WHERE k=1) FROM kv 984 985 query error at or near "filter": syntax error 986 SELECT k FILTER (WHERE k=1) FROM kv GROUP BY k 987 988 query error aggregate functions are not allowed in FILTER 989 SELECT v, count(*) FILTER (WHERE count(*) > 5) FROM filter_test GROUP BY v 990 991 # Tests with * inside GROUP BY. 992 query I 993 SELECT 1 FROM kv GROUP BY kv.* 994 ---- 995 1 996 1 997 1 998 1 999 1 1000 1 1001 1002 query R rowsort 1003 SELECT sum(abc.d) FROM kv JOIN abc ON kv.k >= abc.d GROUP BY kv.* 1004 ---- 1005 1.1 1006 6.1 1007 6.1 1008 6.1 1009 6.1 1010 1011 # opt_test is used for tests around the single-row optimization for MIN/MAX. 1012 statement ok 1013 CREATE TABLE opt_test (k INT PRIMARY KEY, v INT, INDEX v(v)) 1014 1015 statement ok 1016 INSERT INTO opt_test VALUES (1, NULL), (2, 10), (3, NULL), (4, 5) 1017 1018 # Verify that we correctly add the v IS NOT NULL constraint (which restricts the span). 1019 # Without the "v IS NOT NULL" constraint, this result would incorrectly be NULL. 1020 query I 1021 SELECT min(v) FROM opt_test 1022 ---- 1023 5 1024 1025 # Cross-check against a query without this optimization. 1026 query I 1027 SELECT min(v) FROM opt_test@primary 1028 ---- 1029 5 1030 1031 # Repeat test when there is an existing filter. 1032 query I 1033 SELECT min(v) FROM opt_test WHERE k <> 4 1034 ---- 1035 10 1036 1037 # Verify that we don't use the optimization if there is a GROUP BY. 1038 query I rowsort 1039 SELECT min(v) FROM opt_test GROUP BY k 1040 ---- 1041 NULL 1042 NULL 1043 5 1044 10 1045 1046 query I rowsort 1047 SELECT max(v) FROM opt_test GROUP BY k 1048 ---- 1049 NULL 1050 NULL 1051 5 1052 10 1053 1054 statement ok 1055 CREATE TABLE xor_bytes (a bytes, b int, c int) 1056 1057 statement ok 1058 INSERT INTO xor_bytes VALUES 1059 (b'\x01\x01', 1, 3), 1060 (b'\x02\x01', 1, 1), 1061 (b'\x04\x01', 2, -5), 1062 (b'\x08\x01', 2, -1), 1063 (b'\x10\x01', 2, 0) 1064 1065 query TI 1066 SELECT to_hex(xor_agg(a)), xor_agg(c) FROM xor_bytes 1067 ---- 1068 1f01 6 1069 1070 query TII 1071 SELECT to_hex(xor_agg(a)), b, xor_agg(c) FROM xor_bytes GROUP BY b ORDER BY b 1072 ---- 1073 0300 1 2 1074 1c01 2 4 1075 1076 statement error arguments to xor must all be the same length 1077 SELECT xor_agg(i) FROM (VALUES (b'\x01'), (b'\x01\x01')) AS a(i) 1078 1079 query BB 1080 SELECT max(true), min(true) 1081 ---- 1082 true 1083 true 1084 1085 # Grouping and rendering tuples. 1086 statement OK 1087 CREATE TABLE ab ( 1088 a INT PRIMARY KEY, 1089 b INT, 1090 FAMILY (a), 1091 FAMILY (b) 1092 ) 1093 1094 statement ok 1095 INSERT INTO ab(a,b) VALUES (1,2), (3,4); 1096 CREATE TABLE xy(x STRING, y STRING); 1097 INSERT INTO xy(x, y) VALUES ('a', 'b'), ('c', 'd') 1098 1099 # Grouping and rendering tuples. 1100 query T rowsort 1101 SELECT (b, a) FROM ab GROUP BY (b, a) 1102 ---- 1103 (2,1) 1104 (4,3) 1105 1106 query TT rowsort 1107 SELECT min(y), (b, a) 1108 FROM ab, xy GROUP BY (x, (a, b)) 1109 ---- 1110 b (2,1) 1111 d (2,1) 1112 b (4,3) 1113 d (4,3) 1114 1115 # Test that ordering on GROUP BY columns is maintained. 1116 statement ok 1117 CREATE TABLE group_ord ( 1118 x INT PRIMARY KEY, 1119 y INT, 1120 z INT, 1121 INDEX foo(z) 1122 ) 1123 1124 statement ok 1125 INSERT INTO group_ord VALUES 1126 (1, 2, 3), 1127 (3, 4, 5), 1128 (5, NULL, 5), 1129 (6, 2, 3), 1130 (7, 2, 2), 1131 (8, 4, 2) 1132 1133 # The ordering is on all the GROUP BY columns, and isn't preserved after the 1134 # aggregation. 1135 query II rowsort 1136 SELECT x, max(y) FROM group_ord GROUP BY x 1137 ---- 1138 1 2 1139 3 4 1140 5 NULL 1141 6 2 1142 7 2 1143 8 4 1144 1145 # The ordering is on all the GROUP BY columns, and is preserved after the 1146 # aggregation. 1147 query II 1148 SELECT x, max(y) FROM group_ord GROUP BY x ORDER BY x 1149 ---- 1150 1 2 1151 3 4 1152 5 NULL 1153 6 2 1154 7 2 1155 8 4 1156 1157 # The ordering is on some of the GROUP BY columns, and isn't preserved after 1158 # the aggregation. 1159 query III rowsort 1160 SELECT z, x, max(y) FROM group_ord GROUP BY x, z 1161 ---- 1162 5 3 4 1163 3 6 2 1164 3 1 2 1165 5 5 NULL 1166 2 7 2 1167 2 8 4 1168 1169 # The ordering is on some of the GROUP BY columns, and is preserved after 1170 # the aggregation. 1171 query III 1172 SELECT z, x, max(y) FROM group_ord GROUP BY x, z ORDER BY x 1173 ---- 1174 3 1 2 1175 5 3 4 1176 5 5 NULL 1177 3 6 2 1178 2 7 2 1179 2 8 4 1180 1181 # If the underlying ordering isn't from the primary index, it needs to be hinted 1182 # for now. 1183 query II rowsort 1184 SELECT z, max(y) FROM group_ord@foo GROUP BY z 1185 ---- 1186 5 4 1187 2 4 1188 3 2 1189 1190 # Test that a merge join is used on two aggregate subqueries with orderings on 1191 # the GROUP BY columns. Note that an ORDER BY is not necessary on the 1192 # subqueries. 1193 query IIII rowsort 1194 SELECT * FROM (SELECT x, max(y) FROM group_ord GROUP BY x) JOIN (SELECT z, min(y) FROM group_ord@foo GROUP BY z) ON x = z 1195 ---- 1196 5 NULL 5 4 1197 3 4 3 2 1198 1199 # Regression test for #23798 until #10495 is fixed. 1200 statement error function reserved for internal use 1201 SELECT final_variance(1.2, 1.2, 123) FROM kv 1202 1203 # Regression test for #25533 (crash when propagating filter through GROUP BY). 1204 query I 1205 SELECT 1 FROM kv GROUP BY v, w::DECIMAL HAVING w::DECIMAL > 1 1206 ---- 1207 1 1208 1 1209 1 1210 1 1211 1 1212 1213 # Regression test for distsql aggregator crash when using hash aggregation. 1214 query IT rowsort 1215 SELECT v, array_agg('a') FROM kv GROUP BY v 1216 ---- 1217 2 {a,a,a} 1218 4 {a,a} 1219 NULL {a} 1220 1221 # Regression test for #26419 1222 query I 1223 SELECT 123 FROM kv ORDER BY max(v) 1224 ---- 1225 123 1226 1227 subtest statistics 1228 1229 statement OK 1230 CREATE TABLE statistics_agg_test ( 1231 y float, 1232 x float, 1233 int_y int, 1234 int_x int 1235 ) 1236 1237 statement OK 1238 INSERT INTO statistics_agg_test (y, x, int_y, int_x) VALUES 1239 (1.0, 10.0, 1, 10), 1240 (2.0, 25.0, 2, 25), 1241 (2.0, 25.0, 2, 25), 1242 (3.0, 40.0, 3, 40), 1243 (3.0, 40.0, 3, 40), 1244 (3.0, 40.0, 3, 40), 1245 (4.0, 100.0, 4, 100), 1246 (4.0, 100.0, 4, 100), 1247 (4.0, 100.0, 4, 100), 1248 (4.0, 100.0, 4, 100), 1249 (NULL, NULL, NULL, NULL) 1250 1251 query RRRR 1252 SELECT corr(y, x)::decimal, corr(int_y, int_x)::decimal, corr(y, int_x)::decimal, corr(int_y, x)::decimal FROM statistics_agg_test 1253 ---- 1254 0.933007822647968 0.933007822647968 0.933007822647968 0.933007822647968 1255 1256 query R 1257 SELECT corr(DISTINCT y, x)::decimal FROM statistics_agg_test 1258 ---- 1259 0.9326733179802503 1260 1261 query R 1262 SELECT CAST(corr(DISTINCT y, x) FILTER (WHERE x > 3 AND y < 30) AS decimal) FROM statistics_agg_test 1263 ---- 1264 0.9326733179802503 1265 1266 query error pq: unknown signature: corr\(string, string\) 1267 SELECT corr(y::string, x::string) FROM statistics_agg_test 1268 1269 statement OK 1270 INSERT INTO statistics_agg_test (y, x, int_y, int_x) VALUES 1271 (1.797693134862315708145274237317043567981e+308, 0, 0, 0) 1272 1273 query error float out of range 1274 SELECT corr(y, x)::decimal, corr(int_y, int_x)::decimal FROM statistics_agg_test 1275 1276 statement OK 1277 TRUNCATE statistics_agg_test 1278 1279 statement OK 1280 INSERT INTO statistics_agg_test (y, x, int_y, int_x) VALUES 1281 (1.0, 10.0, 1, 10), 1282 (2.0, 20.0, 2, 20) 1283 1284 query RR 1285 SELECT corr(y, x)::decimal, corr(int_y, int_x)::decimal FROM statistics_agg_test 1286 ---- 1287 1 1 1288 1289 statement OK 1290 TRUNCATE statistics_agg_test 1291 1292 statement OK 1293 INSERT INTO statistics_agg_test (y, x, int_y, int_x) VALUES 1294 (1.0, 10.0, 1, 10), 1295 (2.0, -20.0, 2, -20) 1296 1297 query RR 1298 SELECT corr(y, x)::decimal, corr(int_y, int_x)::decimal FROM statistics_agg_test 1299 ---- 1300 -1 -1 1301 1302 statement OK 1303 TRUNCATE statistics_agg_test 1304 1305 statement OK 1306 INSERT INTO statistics_agg_test (y, x, int_y, int_x) VALUES 1307 (1.0, -1.0, 1, -1), 1308 (1.0, 1.0, 1, 1) 1309 1310 query RR 1311 SELECT corr(y, x)::decimal, corr(int_y, int_x)::decimal FROM statistics_agg_test 1312 ---- 1313 NULL NULL 1314 1315 subtest string_agg 1316 1317 statement OK 1318 CREATE TABLE string_agg_test ( 1319 id INT PRIMARY KEY, 1320 company_id INT, 1321 employee STRING 1322 ) 1323 1324 query IT colnames 1325 SELECT company_id, string_agg(employee, ',') 1326 FROM string_agg_test 1327 GROUP BY company_id 1328 ORDER BY company_id; 1329 ---- 1330 company_id string_agg 1331 1332 query IT colnames 1333 SELECT company_id, string_agg(employee::BYTES, b',') 1334 FROM string_agg_test 1335 GROUP BY company_id 1336 ORDER BY company_id; 1337 ---- 1338 company_id string_agg 1339 1340 query IT colnames 1341 SELECT company_id, string_agg(employee, NULL) 1342 FROM string_agg_test 1343 GROUP BY company_id 1344 ORDER BY company_id; 1345 ---- 1346 company_id string_agg 1347 1348 query IT colnames 1349 SELECT company_id, string_agg(employee::BYTES, NULL) 1350 FROM string_agg_test 1351 GROUP BY company_id 1352 ORDER BY company_id; 1353 ---- 1354 company_id string_agg 1355 1356 statement OK 1357 INSERT INTO string_agg_test VALUES 1358 (1, 1, 'A'), 1359 (2, 2, 'B'), 1360 (3, 3, 'C'), 1361 (4, 4, 'D'), 1362 (5, 3, 'C'), 1363 (6, 4, 'D'), 1364 (7, 4, 'D'), 1365 (8, 4, 'D'), 1366 (9, 3, 'C'), 1367 (10, 2, 'B') 1368 1369 # This is a bit strange but the same behavior as PostgreSQL. 1370 query IT rowsort 1371 SELECT company_id, string_agg(employee, employee) 1372 FROM string_agg_test 1373 GROUP BY company_id; 1374 ---- 1375 1 A 1376 2 BBB 1377 3 CCCCC 1378 4 DDDDDDD 1379 1380 query IT colnames 1381 SELECT company_id, string_agg(employee, ',') 1382 FROM string_agg_test 1383 GROUP BY company_id 1384 ORDER BY company_id; 1385 ---- 1386 company_id string_agg 1387 1 A 1388 2 B,B 1389 3 C,C,C 1390 4 D,D,D,D 1391 1392 query IT colnames 1393 SELECT company_id, string_agg(DISTINCT employee, ',') 1394 FROM string_agg_test 1395 GROUP BY company_id 1396 ORDER BY company_id; 1397 ---- 1398 company_id string_agg 1399 1 A 1400 2 B 1401 3 C 1402 4 D 1403 1404 query IT colnames 1405 SELECT company_id, string_agg(employee::BYTES, b',') 1406 FROM string_agg_test 1407 GROUP BY company_id 1408 ORDER BY company_id; 1409 ---- 1410 company_id string_agg 1411 1 A 1412 2 B,B 1413 3 C,C,C 1414 4 D,D,D,D 1415 1416 query IT colnames 1417 SELECT company_id, string_agg(employee, '') 1418 FROM string_agg_test 1419 GROUP BY company_id 1420 ORDER BY company_id; 1421 ---- 1422 company_id string_agg 1423 1 A 1424 2 BB 1425 3 CCC 1426 4 DDDD 1427 1428 query IT colnames 1429 SELECT company_id, string_agg(employee::BYTES, b'') 1430 FROM string_agg_test 1431 GROUP BY company_id 1432 ORDER BY company_id; 1433 ---- 1434 company_id string_agg 1435 1 A 1436 2 BB 1437 3 CCC 1438 4 DDDD 1439 1440 query IT colnames 1441 SELECT company_id, string_agg(employee, NULL) 1442 FROM string_agg_test 1443 GROUP BY company_id 1444 ORDER BY company_id; 1445 ---- 1446 company_id string_agg 1447 1 A 1448 2 BB 1449 3 CCC 1450 4 DDDD 1451 1452 query IT colnames 1453 SELECT company_id, string_agg(employee::BYTES, NULL) 1454 FROM string_agg_test 1455 GROUP BY company_id 1456 ORDER BY company_id; 1457 ---- 1458 company_id string_agg 1459 1 A 1460 2 BB 1461 3 CCC 1462 4 DDDD 1463 1464 query IT colnames 1465 SELECT company_id, string_agg(NULL::STRING, ',') 1466 FROM string_agg_test 1467 GROUP BY company_id 1468 ORDER BY company_id; 1469 ---- 1470 company_id string_agg 1471 1 NULL 1472 2 NULL 1473 3 NULL 1474 4 NULL 1475 1476 query IT colnames 1477 SELECT company_id, string_agg(NULL::BYTES, b',') 1478 FROM string_agg_test 1479 GROUP BY company_id 1480 ORDER BY company_id; 1481 ---- 1482 company_id string_agg 1483 1 NULL 1484 2 NULL 1485 3 NULL 1486 4 NULL 1487 1488 query IT colnames 1489 SELECT company_id, string_agg(NULL::STRING, NULL) 1490 FROM string_agg_test 1491 GROUP BY company_id 1492 ORDER BY company_id; 1493 ---- 1494 company_id string_agg 1495 1 NULL 1496 2 NULL 1497 3 NULL 1498 4 NULL 1499 1500 query IT colnames 1501 SELECT company_id, string_agg(NULL::BYTES, NULL) 1502 FROM string_agg_test 1503 GROUP BY company_id 1504 ORDER BY company_id; 1505 ---- 1506 company_id string_agg 1507 1 NULL 1508 2 NULL 1509 3 NULL 1510 4 NULL 1511 1512 query error pq: ambiguous call: string_agg\(unknown, unknown\) 1513 SELECT company_id, string_agg(NULL, NULL) 1514 FROM string_agg_test 1515 GROUP BY company_id 1516 ORDER BY company_id; 1517 1518 # Now test the window function version of string_agg. 1519 1520 query IT colnames 1521 SELECT company_id, string_agg(employee, ',') 1522 OVER (PARTITION BY company_id ORDER BY id) 1523 FROM string_agg_test 1524 ORDER BY company_id, id; 1525 ---- 1526 company_id string_agg 1527 1 A 1528 2 B 1529 2 B,B 1530 3 C 1531 3 C,C 1532 3 C,C,C 1533 4 D 1534 4 D,D 1535 4 D,D,D 1536 4 D,D,D,D 1537 1538 query IT colnames 1539 SELECT company_id, string_agg(employee::BYTES, b',') 1540 OVER (PARTITION BY company_id ORDER BY id) 1541 FROM string_agg_test 1542 ORDER BY company_id, id; 1543 ---- 1544 company_id string_agg 1545 1 A 1546 2 B 1547 2 B,B 1548 3 C 1549 3 C,C 1550 3 C,C,C 1551 4 D 1552 4 D,D 1553 4 D,D,D 1554 4 D,D,D,D 1555 1556 query IT colnames 1557 SELECT company_id, string_agg(employee, '') 1558 OVER (PARTITION BY company_id ORDER BY id) 1559 FROM string_agg_test 1560 ORDER BY company_id, id; 1561 ---- 1562 company_id string_agg 1563 1 A 1564 2 B 1565 2 BB 1566 3 C 1567 3 CC 1568 3 CCC 1569 4 D 1570 4 DD 1571 4 DDD 1572 4 DDDD 1573 1574 query IT colnames 1575 SELECT company_id, string_agg(employee::BYTES, b'') 1576 OVER (PARTITION BY company_id ORDER BY id) 1577 FROM string_agg_test 1578 ORDER BY company_id, id; 1579 ---- 1580 company_id string_agg 1581 1 A 1582 2 B 1583 2 BB 1584 3 C 1585 3 CC 1586 3 CCC 1587 4 D 1588 4 DD 1589 4 DDD 1590 4 DDDD 1591 1592 query IT colnames 1593 SELECT company_id, string_agg(employee, NULL) 1594 OVER (PARTITION BY company_id ORDER BY id) 1595 FROM string_agg_test 1596 ORDER BY company_id, id; 1597 ---- 1598 company_id string_agg 1599 1 A 1600 2 B 1601 2 BB 1602 3 C 1603 3 CC 1604 3 CCC 1605 4 D 1606 4 DD 1607 4 DDD 1608 4 DDDD 1609 1610 query IT colnames 1611 SELECT company_id, string_agg(employee::BYTES, NULL) 1612 OVER (PARTITION BY company_id ORDER BY id) 1613 FROM string_agg_test 1614 ORDER BY company_id, id; 1615 ---- 1616 company_id string_agg 1617 1 A 1618 2 B 1619 2 BB 1620 3 C 1621 3 CC 1622 3 CCC 1623 4 D 1624 4 DD 1625 4 DDD 1626 4 DDDD 1627 1628 query IT colnames 1629 SELECT company_id, string_agg(NULL::STRING, employee) 1630 OVER (PARTITION BY company_id ORDER BY id) 1631 FROM string_agg_test 1632 ORDER BY company_id, id; 1633 ---- 1634 company_id string_agg 1635 1 NULL 1636 2 NULL 1637 2 NULL 1638 3 NULL 1639 3 NULL 1640 3 NULL 1641 4 NULL 1642 4 NULL 1643 4 NULL 1644 4 NULL 1645 1646 query IT colnames 1647 SELECT company_id, string_agg(NULL::BYTES, employee::BYTES) 1648 OVER (PARTITION BY company_id ORDER BY id) 1649 FROM string_agg_test 1650 ORDER BY company_id, id; 1651 ---- 1652 company_id string_agg 1653 1 NULL 1654 2 NULL 1655 2 NULL 1656 3 NULL 1657 3 NULL 1658 3 NULL 1659 4 NULL 1660 4 NULL 1661 4 NULL 1662 4 NULL 1663 1664 query IT colnames 1665 SELECT company_id, string_agg(NULL::STRING, NULL) 1666 OVER (PARTITION BY company_id ORDER BY id) 1667 FROM string_agg_test 1668 ORDER BY company_id, id; 1669 ---- 1670 company_id string_agg 1671 1 NULL 1672 2 NULL 1673 2 NULL 1674 3 NULL 1675 3 NULL 1676 3 NULL 1677 4 NULL 1678 4 NULL 1679 4 NULL 1680 4 NULL 1681 1682 query IT colnames 1683 SELECT company_id, string_agg(NULL::BYTES, NULL) 1684 OVER (PARTITION BY company_id ORDER BY id) 1685 FROM string_agg_test 1686 ORDER BY company_id, id; 1687 ---- 1688 company_id string_agg 1689 1 NULL 1690 2 NULL 1691 2 NULL 1692 3 NULL 1693 3 NULL 1694 3 NULL 1695 4 NULL 1696 4 NULL 1697 4 NULL 1698 4 NULL 1699 1700 query IT colnames 1701 SELECT company_id, string_agg(NULL, NULL::STRING) 1702 OVER (PARTITION BY company_id ORDER BY id) 1703 FROM string_agg_test 1704 ORDER BY company_id, id; 1705 ---- 1706 company_id string_agg 1707 1 NULL 1708 2 NULL 1709 2 NULL 1710 3 NULL 1711 3 NULL 1712 3 NULL 1713 4 NULL 1714 4 NULL 1715 4 NULL 1716 4 NULL 1717 1718 query IT colnames 1719 SELECT company_id, string_agg(NULL, NULL::BYTES) 1720 OVER (PARTITION BY company_id ORDER BY id) 1721 FROM string_agg_test 1722 ORDER BY company_id, id; 1723 ---- 1724 company_id string_agg 1725 1 NULL 1726 2 NULL 1727 2 NULL 1728 3 NULL 1729 3 NULL 1730 3 NULL 1731 4 NULL 1732 4 NULL 1733 4 NULL 1734 4 NULL 1735 1736 query error pq: ambiguous call: string_agg\(unknown, unknown\) 1737 SELECT company_id, string_agg(NULL, NULL) 1738 OVER (PARTITION BY company_id ORDER BY id) 1739 FROM string_agg_test 1740 ORDER BY company_id, id; 1741 1742 query IT colnames 1743 SELECT company_id, string_agg(employee, lower(employee)) 1744 OVER (PARTITION BY company_id) 1745 FROM string_agg_test 1746 ORDER BY company_id, id; 1747 ---- 1748 company_id string_agg 1749 1 A 1750 2 BbB 1751 2 BbB 1752 3 CcCcC 1753 3 CcCcC 1754 3 CcCcC 1755 4 DdDdDdD 1756 4 DdDdDdD 1757 4 DdDdDdD 1758 4 DdDdDdD 1759 1760 query IT colnames 1761 SELECT company_id, string_agg(lower(employee), employee) 1762 OVER (PARTITION BY company_id) 1763 FROM string_agg_test 1764 ORDER BY company_id, id; 1765 ---- 1766 company_id string_agg 1767 1 a 1768 2 bBb 1769 2 bBb 1770 3 cCcCc 1771 3 cCcCc 1772 3 cCcCc 1773 4 dDdDdDd 1774 4 dDdDdDd 1775 4 dDdDdDd 1776 4 dDdDdDd 1777 1778 statement error pq: unknown signature: string_agg\(string, string, string\) 1779 SELECT company_id, string_agg(employee, employee, employee) 1780 OVER (PARTITION BY company_id) 1781 FROM string_agg_test 1782 ORDER BY company_id, id; 1783 1784 query error pq: unknown signature: string_agg\(string\) 1785 SELECT company_id, string_agg(employee) 1786 OVER (PARTITION BY company_id) 1787 FROM string_agg_test 1788 ORDER BY company_id, id; 1789 1790 query error pq: unknown signature: string_agg\(string, string, string, string\) 1791 SELECT company_id, string_agg(employee, 'foo', employee, 'bar') 1792 OVER (PARTITION BY company_id) 1793 FROM string_agg_test 1794 ORDER BY company_id, id; 1795 1796 statement OK 1797 TRUNCATE string_agg_test 1798 1799 statement OK 1800 INSERT INTO string_agg_test VALUES 1801 (1, 1, 'A'), 1802 (2, 1, 'B'), 1803 (3, 1, 'C'), 1804 (4, 1, 'D') 1805 1806 query IT colnames 1807 SELECT e.company_id, string_agg(e.employee, ', ') 1808 FROM ( 1809 SELECT employee, company_id 1810 FROM string_agg_test 1811 ORDER BY employee 1812 ) AS e 1813 GROUP BY e.company_id 1814 ORDER BY e.company_id; 1815 ---- 1816 company_id string_agg 1817 1 A, B, C, D 1818 1819 query IT colnames 1820 SELECT e.company_id, string_agg(e.employee, b', ') 1821 FROM ( 1822 SELECT employee::BYTES, company_id 1823 FROM string_agg_test 1824 ORDER BY employee 1825 ) AS e 1826 GROUP BY e.company_id 1827 ORDER BY e.company_id; 1828 ---- 1829 company_id string_agg 1830 1 A, B, C, D 1831 1832 query IT colnames 1833 SELECT e.company_id, string_agg(e.employee, ', ') 1834 FROM ( 1835 SELECT employee, company_id 1836 FROM string_agg_test 1837 ORDER BY employee DESC 1838 ) AS e 1839 GROUP BY e.company_id 1840 ORDER BY e.company_id; 1841 ---- 1842 company_id string_agg 1843 1 D, C, B, A 1844 1845 query IT colnames 1846 SELECT e.company_id, string_agg(e.employee, b', ') 1847 FROM ( 1848 SELECT employee::BYTES, company_id 1849 FROM string_agg_test 1850 ORDER BY employee DESC 1851 ) AS e 1852 GROUP BY e.company_id 1853 ORDER BY e.company_id; 1854 ---- 1855 company_id string_agg 1856 1 D, C, B, A 1857 1858 query IT colnames 1859 SELECT e.company_id, string_agg(e.employee, NULL) 1860 FROM ( 1861 SELECT employee, company_id 1862 FROM string_agg_test 1863 ORDER BY employee DESC 1864 ) AS e 1865 GROUP BY e.company_id 1866 ORDER BY e.company_id; 1867 ---- 1868 company_id string_agg 1869 1 DCBA 1870 1871 query IT colnames 1872 SELECT e.company_id, string_agg(e.employee, NULL) 1873 FROM ( 1874 SELECT employee::BYTES, company_id 1875 FROM string_agg_test 1876 ORDER BY employee DESC 1877 ) AS e 1878 GROUP BY e.company_id 1879 ORDER BY e.company_id; 1880 ---- 1881 company_id string_agg 1882 1 DCBA 1883 1884 statement OK 1885 DROP TABLE string_agg_test 1886 1887 # Regression test for #28836. 1888 1889 query T 1890 SELECT string_agg('foo', CAST ((SELECT NULL) AS BYTES)) OVER (); 1891 ---- 1892 foo 1893 1894 # Regression test for #30166. 1895 query T 1896 SELECT array_agg(generate_series(1, 2)) 1897 ---- 1898 {1,2} 1899 1900 # Regression test for #31882. 1901 1902 statement ok 1903 CREATE TABLE uvw (u INT, v INT, w INT, INDEX uvw(u, v, w)) 1904 1905 statement ok 1906 INSERT INTO uvw VALUES (1, 2, 3), (1, 2, 3), (3, 2, 1), (3, 2, 3) 1907 1908 query IIT rowsort 1909 SELECT u, v, array_agg(w) AS s FROM (SELECT * FROM uvw ORDER BY w) GROUP BY u, v 1910 ---- 1911 3 2 {1,3} 1912 1 2 {3,3} 1913 1914 # Regression test for #36433: don't panic with count_agg if a post-render produces an error. 1915 1916 query error lpad 1917 SELECT count(*)::TEXT||lpad('foo', 23984729388383834723984) FROM (VALUES(1)); 1918 1919 statement ok 1920 CREATE TABLE tab ( 1921 col1 INT PRIMARY KEY, 1922 col2 INT, 1923 col3 STRING 1924 ) 1925 1926 # Ordered aggregations when there are no rows. 1927 query I 1928 SELECT array_agg(col1 ORDER BY col2) FROM TAB 1929 ---- 1930 NULL 1931 1932 statement ok 1933 INSERT INTO tab VALUES (-3, 7, 'a'), (-2, 6, 'a'), (-1, 5, 'a'), (0, 7, 'b'), (1, 5, 'b'), (2, 6, 'b') 1934 1935 query T colnames 1936 SELECT array_agg(col1 ORDER BY col1) FROM tab 1937 ---- 1938 array_agg 1939 {-3,-2,-1,0,1,2} 1940 1941 query T colnames 1942 SELECT array_agg(col1 ORDER BY col2*100+col1) FROM tab 1943 ---- 1944 array_agg 1945 {-1,1,-2,2,-3,0} 1946 1947 query T colnames 1948 SELECT json_agg(col1 ORDER BY col1) FROM tab 1949 ---- 1950 json_agg 1951 [-3, -2, -1, 0, 1, 2] 1952 1953 query T colnames 1954 SELECT jsonb_agg(col1 ORDER BY col1) FROM tab 1955 ---- 1956 jsonb_agg 1957 [-3, -2, -1, 0, 1, 2] 1958 1959 query T colnames 1960 SELECT jsonb_agg(col1 ORDER BY col2, col1) FROM tab 1961 ---- 1962 jsonb_agg 1963 [-1, 1, -2, 2, -3, 0] 1964 1965 query T colnames 1966 SELECT concat_agg(col3 ORDER BY col1) FROM tab 1967 ---- 1968 concat_agg 1969 aaabbb 1970 1971 query T colnames 1972 SELECT concat_agg(col3 ORDER BY col1 DESC) FROM tab 1973 ---- 1974 concat_agg 1975 bbbaaa 1976 1977 query T colnames 1978 SELECT string_agg(col3, ', ' ORDER BY col3) FROM tab 1979 ---- 1980 string_agg 1981 a, a, a, b, b, b 1982 1983 query T colnames 1984 SELECT string_agg(col3, ', ' ORDER BY col3 DESC) FROM tab 1985 ---- 1986 string_agg 1987 b, b, b, a, a, a 1988 1989 query TTT colnames 1990 SELECT array_agg(col1 ORDER BY col1), array_agg(col1 ORDER BY col2, col1), array_agg(col1 ORDER BY col3, col1) FROM tab 1991 ---- 1992 array_agg array_agg array_agg 1993 {-3,-2,-1,0,1,2} {-1,1,-2,2,-3,0} {-3,-2,-1,0,1,2} 1994 1995 query TTT colnames 1996 SELECT array_agg(col1 ORDER BY col1), array_agg(col1 ORDER BY col2, col1), col3 FROM tab GROUP BY col3 ORDER BY col3 1997 ---- 1998 array_agg array_agg col3 1999 {-3,-2,-1} {-1,-2,-3} a 2000 {0,1,2} {1,2,0} b 2001 2002 query TTII colnames 2003 SELECT array_agg(col1 ORDER BY col1), array_agg(col1 ORDER BY col2, col1), count(col3), count(*) FROM tab 2004 ---- 2005 array_agg array_agg count count 2006 {-3,-2,-1,0,1,2} {-1,1,-2,2,-3,0} 6 6 2007 2008 query TT colnames 2009 SELECT array_agg(col1 ORDER BY col1), array_agg(col1 ORDER BY col1) FILTER (WHERE col1 < 0) FROM tab 2010 ---- 2011 array_agg array_agg 2012 {-3,-2,-1,0,1,2} {-3,-2,-1} 2013 2014 query TT colnames 2015 SELECT array_agg(col1 ORDER BY col3, col1) FILTER (WHERE col1 < 0), array_agg(col1 ORDER BY col3, col1) FROM tab 2016 ---- 2017 array_agg array_agg 2018 {-3,-2,-1} {-3,-2,-1,0,1,2} 2019 2020 query IT 2021 SELECT count(1), concat_agg(col3 ORDER BY col1) from tab 2022 ---- 2023 6 aaabbb 2024 2025 # Testing pre-projections. Tests when the GroupBy clause has a projection. 2026 query IIIT colnames 2027 SELECT 2028 * 2029 FROM 2030 ( 2031 SELECT 2032 count(1) AS count_1, 2033 count(lower(col3)) AS count_lower, 2034 count(upper(col3)) AS count_upper, 2035 concat_agg(col3 ORDER BY col1) AS concat 2036 FROM 2037 tab 2038 GROUP BY 2039 upper(col3) 2040 ) 2041 ORDER BY 2042 concat 2043 ---- 2044 count_1 count_lower count_upper concat 2045 3 3 3 aaa 2046 3 3 3 bbb 2047 2048 # Tests for selecting any columns when grouping by the PK. 2049 statement ok 2050 DELETE FROM ab WHERE true; 2051 INSERT INTO ab VALUES (1,1), (2,1), (3,3), (4, 7) 2052 2053 query I rowsort 2054 SELECT b FROM ab GROUP BY a 2055 ---- 2056 1 2057 1 2058 3 2059 7 2060 2061 query II rowsort 2062 SELECT a+b, count(*) FROM ab JOIN tab ON b=col2 GROUP BY a 2063 ---- 2064 11 2 2065 2066 query IIII rowsort 2067 SELECT a, col1, b+col2, count(*) FROM ab JOIN tab ON b=col2 GROUP BY a, col1 2068 ---- 2069 4 -3 14 1 2070 4 0 14 1 2071 2072 query IIII rowsort 2073 SELECT a, b, count(*), count(col2) FROM ab LEFT JOIN tab ON b=col2 GROUP BY a 2074 ---- 2075 1 1 1 0 2076 2 1 1 0 2077 3 3 1 0 2078 4 7 2 2 2079 2080 query III rowsort 2081 SELECT a, b, count(*) FROM ab RIGHT JOIN tab ON b=col2 GROUP BY a 2082 ---- 2083 NULL NULL 4 2084 4 7 2 2085 2086 # Additional tests for MIN/MAX aggregates with indexes. 2087 statement ok 2088 CREATE TABLE xyz ( 2089 x INT PRIMARY KEY, 2090 y INT, 2091 z INT, 2092 INDEX yz (y, z) 2093 ) 2094 2095 statement ok 2096 INSERT INTO xyz VALUES (1, 2, 3), (2, 2, 7), (3, 2, 1), (4, 2, NULL), (5, 3, -1) 2097 2098 query I 2099 SELECT min(z) FROM xyz WHERE y = 2 GROUP BY y 2100 ---- 2101 1 2102 2103 query I 2104 SELECT min(z) FROM xyz WHERE y = 2 AND z IS NOT NULL GROUP BY y 2105 ---- 2106 1 2107 2108 query I 2109 SELECT min(z) FROM xyz WHERE y = 2 AND z IS NULL GROUP BY y 2110 ---- 2111 NULL 2112 2113 query I 2114 SELECT min(z) FROM xyz WHERE y = 100 AND z IS NULL GROUP BY y 2115 ---- 2116 2117 query I 2118 SELECT max(z) FROM xyz WHERE y = 2 GROUP BY y 2119 ---- 2120 7 2121 2122 query I 2123 SELECT max(z) FROM xyz WHERE y = 2 AND z IS NOT NULL GROUP BY y 2124 ---- 2125 7 2126 2127 query I 2128 SELECT max(z) FROM xyz WHERE y = 2 AND z IS NULL GROUP BY y 2129 ---- 2130 NULL 2131 2132 query I 2133 SELECT max(z) FROM xyz WHERE y = 100 GROUP BY y 2134 ---- 2135 2136 statement ok 2137 DROP TABLE xyz 2138 2139 # Regression test for #44469 (DistinctOn needs to remap the provided ordering). 2140 statement ok 2141 CREATE TABLE t44469_a (a INT, INDEX (a)) 2142 2143 statement ok 2144 CREATE TABLE t44469_b (b INT, INDEX (b)) 2145 2146 statement ok 2147 CREATE TABLE t44469_cd (c INT, d INT, INDEX (c, d)); 2148 2149 statement ok 2150 SELECT DISTINCT ON (b) b 2151 FROM t44469_a INNER LOOKUP JOIN t44469_b ON a = b INNER LOOKUP JOIN t44469_cd ON c = 1 AND d = a 2152 ORDER BY b 2153 2154 statement ok 2155 DROP TABLE IF EXISTS t; 2156 CREATE TABLE t (x JSONB, y INT); 2157 INSERT INTO t VALUES 2158 ('{"foo": "bar"}', 5), 2159 ('{"foo": "bar"}', 10), 2160 ('[1, 2]', 5), 2161 ('[1, 2]', 20), 2162 ('{"foo": "bar", "bar": "baz"}', 5), 2163 ('{"foo": "bar", "bar": "baz"}', 30), 2164 ('{"foo": {"bar" : "baz"}}', 5), 2165 ('{"foo": {"bar" : "baz"}}', 40) 2166 2167 query TT 2168 SELECT x, SUM (y) FROM t GROUP BY (x) ORDER BY SUM (y) 2169 ---- 2170 {"foo": "bar"} 15 2171 [1, 2] 25 2172 {"bar": "baz", "foo": "bar"} 35 2173 {"foo": {"bar": "baz"}} 45 2174 2175 # Tests for the 'every' aggregate function. 2176 subtest every 2177 2178 statement ok 2179 CREATE TABLE t_every (x BOOL) 2180 2181 query B 2182 SELECT every (x) FROM t_every 2183 ---- 2184 NULL 2185 2186 statement ok 2187 INSERT INTO t_every VALUES (true), (true) 2188 2189 query B 2190 SELECT every (x) FROM t_every 2191 ---- 2192 true 2193 2194 statement ok 2195 INSERT INTO t_every VALUES (NULL), (true) 2196 2197 query B 2198 SELECT every (x) FROM t_every 2199 ---- 2200 true 2201 2202 statement ok 2203 INSERT INTO t_every VALUES (false), (NULL) 2204 2205 query B 2206 SELECT every (x) FROM t_every 2207 ---- 2208 false 2209 2210 statement ok 2211 TRUNCATE t_every; 2212 INSERT INTO t_every VALUES (false) 2213 2214 query B 2215 SELECT every (x) FROM t_every 2216 ---- 2217 false 2218 2219 statement ok 2220 TRUNCATE t_every; 2221 INSERT INTO t_every VALUES (NULL), (NULL), (NULL) 2222 2223 query B 2224 SELECT every (x) FROM t_every 2225 ---- 2226 NULL 2227 2228 # Regression test for #46423: this query should return no rows. 2229 statement ok 2230 CREATE TABLE t46423(c0 INT); 2231 INSERT INTO t46423(c0) VALUES(0) 2232 2233 query T 2234 SELECT c0 FROM t46423 GROUP BY c0 HAVING NOT (variance(0) IS NULL); 2235 ---- 2236 2237 # Regression test for #45453 - make sure that we don't incorrectly treat the 2238 # aggregation as scalar. 2239 statement ok 2240 CREATE TABLE t45453(c INT) 2241 2242 query I 2243 SELECT count(*) FROM t45453 GROUP BY 0 + 0 2244 ---- 2245 2246 # Tests for the bit_and and bit_or aggregate functions. 2247 2248 subtest bit_aggregates 2249 2250 statement ok 2251 DROP TABLE IF EXISTS vals 2252 2253 statement ok 2254 CREATE TABLE vals ( 2255 v VARBIT, 2256 b BIT(8) 2257 ) 2258 2259 # Testing that bit aggregate functions return NULL if there are no rows. 2260 2261 query T 2262 SELECT bit_and(v) FROM vals 2263 ---- 2264 NULL 2265 2266 query T 2267 SELECT bit_or(v) FROM vals 2268 ---- 2269 NULL 2270 2271 # Testing that bit aggregate functions do not trigger aggregation on a constant 2272 # with a source that has no rows. 2273 2274 query T 2275 SELECT bit_and('1000'::varbit) FROM vals 2276 ---- 2277 NULL 2278 2279 query T 2280 SELECT bit_or('1000'::varbit) FROM vals 2281 ---- 2282 NULL 2283 2284 # Testing that bit aggregate functions trigger aggregation and computation on a 2285 # constant with no source. 2286 2287 query TTT 2288 SELECT bit_and('1'::varbit), bit_and('1000'::bit(4)), bit_and('1010'::varbit) 2289 ---- 2290 1 1000 1010 2291 2292 query TTT 2293 SELECT bit_or('1'::varbit), bit_or('1000'::bit(4)), bit_or('1010'::varbit) 2294 ---- 2295 1 1000 1010 2296 2297 # Testing that bit aggregate functions return null given a null. 2298 2299 query T 2300 SELECT bit_and(NULL::varbit) 2301 ---- 2302 NULL 2303 2304 query T 2305 SELECT bit_or(NULL::varbit) 2306 ---- 2307 NULL 2308 2309 # Testing successful bitwise aggregation over a sequence of non-nulls. 2310 2311 statement ok 2312 INSERT INTO vals VALUES 2313 ('11111110'::varbit, '11111110'::bit(8)), 2314 ('01111111'::varbit, '01111110'::bit(8)), 2315 ('10111111'::varbit, '10111110'::bit(8)), 2316 ('11011111'::varbit, '11011110'::bit(8)), 2317 ('11101111'::varbit, '11101110'::bit(8)) 2318 2319 query TT 2320 SELECT bit_and(v), bit_and(b) FROM vals 2321 ---- 2322 00001110 00001110 2323 2324 query TT 2325 SELECT bit_or(v), bit_or(b) FROM vals 2326 ---- 2327 11111111 11111110 2328 2329 # Testing bit aggregate functions over a sequence with nulls and non-nulls. 2330 2331 statement ok 2332 INSERT INTO vals VALUES 2333 (NULL::varbit, NULL::bit), 2334 (NULL::varbit, NULL::bit) 2335 2336 query TT 2337 SELECT bit_and(v), bit_and(b) FROM vals 2338 ---- 2339 00001110 00001110 2340 2341 query TT 2342 SELECT bit_or(v), bit_or(b) FROM vals 2343 ---- 2344 11111111 11111110 2345 2346 # Testing bit aggregate functions over a sequence with all nulls. 2347 2348 statement ok 2349 DELETE FROM vals 2350 2351 statement ok 2352 INSERT INTO vals VALUES 2353 (NULL::varbit), 2354 (NULL::varbit), 2355 (NULL::varbit), 2356 (NULL::varbit) 2357 2358 query T 2359 SELECT bit_and(v) FROM vals 2360 ---- 2361 NULL 2362 2363 query T 2364 SELECT bit_or(v) FROM vals 2365 ---- 2366 NULL 2367 2368 # Testing that bit aggregate functions return an error when given an uncasted null. 2369 2370 statement error ambiguous call: bit_and\(unknown\), candidates are 2371 SELECT bit_and(NULL) 2372 2373 statement error ambiguous call: bit_or\(unknown\), candidates are 2374 SELECT bit_or(NULL) 2375 2376 # Testing that an error is returned when bit aggregate functions are called on bit 2377 # arrays of different sizes. 2378 2379 statement error cannot AND bit strings of different sizes 2380 SELECT bit_and(x::varbit) FROM (VALUES ('1'), ('11')) t(x) 2381 2382 statement error cannot AND bit strings of different sizes 2383 SELECT bit_and(x) FROM (VALUES ('100'::bit(3)), ('101010111'::varbit)) t(x) 2384 2385 statement error cannot AND bit strings of different sizes 2386 SELECT bit_and(x) FROM (VALUES (''::varbit), ('1'::varbit)) t(x) 2387 2388 statement error cannot OR bit strings of different sizes 2389 SELECT bit_or(x::varbit) FROM (VALUES ('1'), ('11')) t(x) 2390 2391 statement error cannot OR bit strings of different sizes 2392 SELECT bit_or(x) FROM (VALUES ('100'::bit(3)), ('101010111'::varbit)) t(x) 2393 2394 statement error cannot OR bit strings of different sizes 2395 SELECT bit_or(x) FROM (VALUES (''::varbit), ('1'::varbit)) t(x) 2396 2397 # Regression test for #46981 (not propagating an error which occurs when 2398 # rendering the single output row of countRows aggregate). 2399 statement ok 2400 CREATE TABLE t46981_0(c0 INT); 2401 CREATE VIEW v46981_0(c0) AS SELECT count_rows() FROM t46981_0 2402 2403 statement error parsing regexp: missing argument to repetition operator: `\+` 2404 SELECT * FROM v46981_0 WHERE '' !~ '+' 2405 2406 # Testing ordered-set aggregations. 2407 subtest ordered_set_aggregates 2408 2409 statement ok 2410 DROP TABLE IF EXISTS osagg 2411 2412 statement ok 2413 CREATE TABLE osagg ( 2414 f FLOAT, 2415 s STRING, 2416 i INTERVAL 2417 ) 2418 2419 statement ok 2420 INSERT INTO osagg VALUES 2421 (NULL, NULL, NULL), 2422 (0.00, NULL, '1 months'), 2423 (0.05, NULL, '1 months'), 2424 (1.0, 'v1', '1 year 1 months'), 2425 (3.0, 'v3', '1 year 3 months'), 2426 (5.0, 'v5', '1 year 5 months'), 2427 (2.0, 'v2', '1 year 2 months'), 2428 (4.0, 'v4', '1 year 4 months'), 2429 (6.0, 'v6', '1 year 6 months') 2430 2431 # Test basic functionality. 2432 query R 2433 SELECT 2434 percentile_disc(0.95) WITHIN GROUP (ORDER BY f) 2435 FROM osagg 2436 ---- 2437 6 2438 2439 query RT 2440 SELECT 2441 percentile_disc(0.95) WITHIN GROUP (ORDER BY f), 2442 percentile_disc(0.95) WITHIN GROUP (ORDER BY s) 2443 FROM osagg 2444 ---- 2445 6 v6 2446 2447 query RRT 2448 SELECT 2449 percentile_cont(0.95) WITHIN GROUP (ORDER BY f), 2450 percentile_cont(0.95) WITHIN GROUP (ORDER BY f DESC), 2451 percentile_cont(0.95) WITHIN GROUP (ORDER BY i) 2452 FROM osagg 2453 ---- 2454 5.65 0.0175 1 year 5 mons 24 days 18:00:00 2455 2456 # Test with null values. 2457 query TRR 2458 SELECT 2459 percentile_disc(0.00) WITHIN GROUP (ORDER BY s), 2460 percentile_disc(0.1) WITHIN GROUP (ORDER BY f), 2461 percentile_disc(0.15) WITHIN GROUP (ORDER BY f) 2462 FROM osagg 2463 ---- 2464 v1 0 0.05 2465 2466 query RRTT 2467 SELECT 2468 percentile_cont(0.05) WITHIN GROUP (ORDER BY f), 2469 percentile_cont(0.05) WITHIN GROUP (ORDER BY f DESC), 2470 percentile_cont(0.05) WITHIN GROUP (ORDER BY i), 2471 percentile_cont(0.05) WITHIN GROUP (ORDER BY i DESC) 2472 FROM osagg 2473 ---- 2474 0.0175 5.65 1 mon 1 year 5 mons 24 days 18:00:00 2475 2476 # Test with different percent values. 2477 query RRR 2478 SELECT 2479 percentile_disc(0.25) WITHIN GROUP (ORDER BY f), 2480 percentile_disc(0.5) WITHIN GROUP (ORDER BY f), 2481 percentile_disc(0.75) WITHIN GROUP (ORDER BY f) 2482 FROM osagg 2483 ---- 2484 0.05 2 4 2485 2486 query RRR 2487 SELECT 2488 percentile_cont(0.25) WITHIN GROUP (ORDER BY f), 2489 percentile_cont(0.5) WITHIN GROUP (ORDER BY f), 2490 percentile_cont(0.75) WITHIN GROUP (ORDER BY f) 2491 FROM osagg 2492 ---- 2493 0.7625 2.5 4.25 2494 2495 # Test with arrays. 2496 query T 2497 SELECT 2498 percentile_disc(ARRAY[0.25]::float[]) WITHIN GROUP (ORDER BY f) 2499 FROM osagg 2500 ---- 2501 {0.05} 2502 2503 query T 2504 SELECT 2505 percentile_disc(ARRAY[0.25, 0.5, 0.75]::float[]) WITHIN GROUP (ORDER BY f) 2506 FROM osagg 2507 ---- 2508 {0.05,2.0,4.0} 2509 2510 query T 2511 SELECT 2512 percentile_cont(ARRAY[0.25, 0.5, 0.75]::float[]) WITHIN GROUP (ORDER BY f) 2513 FROM osagg 2514 ---- 2515 {0.7625,2.5,4.25} 2516 2517 query T 2518 SELECT 2519 percentile_disc(ARRAY[0.25, 0.5, 0.75]::float[]) WITHIN GROUP (ORDER BY i) 2520 FROM osagg 2521 ---- 2522 {"1 mon","1 year 2 mons","1 year 4 mons"} 2523 2524 statement error percentile value 1.250000 is not between 0 and 1 2525 SELECT 2526 percentile_disc(ARRAY[1.25]::float[]) WITHIN GROUP (ORDER BY f) 2527 FROM osagg 2528 2529 statement error percentile value 1.250000 is not between 0 and 1 2530 SELECT 2531 percentile_disc(ARRAY[0.25, 0.50, 1.25]::float[]) WITHIN GROUP (ORDER BY f) 2532 FROM osagg 2533 2534 query T 2535 SELECT 2536 percentile_cont(ARRAY[0.25, 0.5, 0.75]::float[]) WITHIN GROUP (ORDER BY i) 2537 FROM osagg 2538 ---- 2539 {"10 mons 3 days 22:30:00","1 year 2 mons 20 days 06:00:00","1 year 4 mons 12 days 18:00:00"} 2540 2541 # Test that the view query is not broken by the overriding logic in the optbuilder. 2542 statement ok 2543 CREATE VIEW osagg_view (disc, cont) AS 2544 SELECT percentile_disc(0.50) WITHIN GROUP (ORDER BY f), 2545 percentile_cont(0.50) WITHIN GROUP (ORDER BY f DESC) FROM osagg 2546 2547 query TT 2548 SHOW CREATE osagg_view 2549 ---- 2550 osagg_view CREATE VIEW osagg_view (disc, cont) AS SELECT percentile_disc(0.50) WITHIN GROUP (ORDER BY f), percentile_cont(0.50) WITHIN GROUP (ORDER BY f DESC) FROM test.public.osagg 2551 2552 # Test malformed ordered-set aggregation. 2553 statement error ordered-set aggregations must have a WITHIN GROUP clause containing one ORDER BY column 2554 SELECT percentile_disc(0.50) FROM osagg 2555 2556 statement error ordered-set aggregations must have a WITHIN GROUP clause containing one ORDER BY column 2557 SELECT percentile_cont(0.50) FROM osagg 2558 2559 # Tests for min/max on collated strings. 2560 statement ok 2561 CREATE TABLE t_collate (x STRING COLLATE en_us); 2562 INSERT INTO t_collate VALUES ('hi' COLLATE en_us), ('hello' COLLATE en_us), ('howdy' COLLATE en_us) 2563 2564 query TT 2565 SELECT min(x), max(x) FROM t_collate 2566 ---- 2567 hello howdy 2568 2569 query TT 2570 SELECT min(NULL::STRING COLLATE en_us), max(NULL::STRING COLLATE en_us) 2571 ---- 2572 NULL NULL