1-- 2-- AGGREGATES 3-- 4SELECT avg(four) AS avg_1 FROM onek; 5 avg_1 6-------------------- 7 1.5000000000000000 8(1 row) 9 10SELECT avg(a) AS avg_32 FROM aggtest WHERE a < 100; 11 avg_32 12--------------------- 13 32.6666666666666667 14(1 row) 15 16-- In 7.1, avg(float4) is computed using float8 arithmetic. 17-- Round the result to 3 digits to avoid platform-specific results. 18SELECT avg(b)::numeric(10,3) AS avg_107_943 FROM aggtest; 19 avg_107_943 20------------- 21 107.943 22(1 row) 23 24SELECT avg(gpa) AS avg_3_4 FROM ONLY student; 25 avg_3_4 26--------- 27 3.4 28(1 row) 29 30SELECT sum(four) AS sum_1500 FROM onek; 31 sum_1500 32---------- 33 1500 34(1 row) 35 36SELECT sum(a) AS sum_198 FROM aggtest; 37 sum_198 38--------- 39 198 40(1 row) 41 42SELECT sum(b) AS avg_431_773 FROM aggtest; 43 avg_431_773 44------------- 45 431.773 46(1 row) 47 48SELECT sum(gpa) AS avg_6_8 FROM ONLY student; 49 avg_6_8 50--------- 51 6.8 52(1 row) 53 54SELECT max(four) AS max_3 FROM onek; 55 max_3 56------- 57 3 58(1 row) 59 60SELECT max(a) AS max_100 FROM aggtest; 61 max_100 62--------- 63 100 64(1 row) 65 66SELECT max(aggtest.b) AS max_324_78 FROM aggtest; 67 max_324_78 68------------ 69 324.78 70(1 row) 71 72SELECT max(student.gpa) AS max_3_7 FROM student; 73 max_3_7 74--------- 75 3.7 76(1 row) 77 78SELECT stddev_pop(b) FROM aggtest; 79 stddev_pop 80----------------- 81 131.10703231895 82(1 row) 83 84SELECT stddev_samp(b) FROM aggtest; 85 stddev_samp 86------------------ 87 151.389360803998 88(1 row) 89 90SELECT var_pop(b) FROM aggtest; 91 var_pop 92------------------ 93 17189.0539234823 94(1 row) 95 96SELECT var_samp(b) FROM aggtest; 97 var_samp 98------------------ 99 22918.7385646431 100(1 row) 101 102SELECT stddev_pop(b::numeric) FROM aggtest; 103 stddev_pop 104------------------ 105 131.107032862199 106(1 row) 107 108SELECT stddev_samp(b::numeric) FROM aggtest; 109 stddev_samp 110------------------ 111 151.389361431288 112(1 row) 113 114SELECT var_pop(b::numeric) FROM aggtest; 115 var_pop 116-------------------- 117 17189.054065929769 118(1 row) 119 120SELECT var_samp(b::numeric) FROM aggtest; 121 var_samp 122-------------------- 123 22918.738754573025 124(1 row) 125 126-- population variance is defined for a single tuple, sample variance 127-- is not 128SELECT var_pop(1.0), var_samp(2.0); 129 var_pop | var_samp 130---------+---------- 131 0 | 132(1 row) 133 134SELECT stddev_pop(3.0::numeric), stddev_samp(4.0::numeric); 135 stddev_pop | stddev_samp 136------------+------------- 137 0 | 138(1 row) 139 140-- verify correct results for null and NaN inputs 141select sum(null::int4) from generate_series(1,3); 142 sum 143----- 144 145(1 row) 146 147select sum(null::int8) from generate_series(1,3); 148 sum 149----- 150 151(1 row) 152 153select sum(null::numeric) from generate_series(1,3); 154 sum 155----- 156 157(1 row) 158 159select sum(null::float8) from generate_series(1,3); 160 sum 161----- 162 163(1 row) 164 165select avg(null::int4) from generate_series(1,3); 166 avg 167----- 168 169(1 row) 170 171select avg(null::int8) from generate_series(1,3); 172 avg 173----- 174 175(1 row) 176 177select avg(null::numeric) from generate_series(1,3); 178 avg 179----- 180 181(1 row) 182 183select avg(null::float8) from generate_series(1,3); 184 avg 185----- 186 187(1 row) 188 189select sum('NaN'::numeric) from generate_series(1,3); 190 sum 191----- 192 NaN 193(1 row) 194 195select avg('NaN'::numeric) from generate_series(1,3); 196 avg 197----- 198 NaN 199(1 row) 200 201-- SQL2003 binary aggregates 202SELECT regr_count(b, a) FROM aggtest; 203 regr_count 204------------ 205 4 206(1 row) 207 208SELECT regr_sxx(b, a) FROM aggtest; 209 regr_sxx 210---------- 211 5099 212(1 row) 213 214SELECT regr_syy(b, a) FROM aggtest; 215 regr_syy 216------------------ 217 68756.2156939293 218(1 row) 219 220SELECT regr_sxy(b, a) FROM aggtest; 221 regr_sxy 222------------------ 223 2614.51582155004 224(1 row) 225 226SELECT regr_avgx(b, a), regr_avgy(b, a) FROM aggtest; 227 regr_avgx | regr_avgy 228-----------+------------------ 229 49.5 | 107.943152273074 230(1 row) 231 232SELECT regr_r2(b, a) FROM aggtest; 233 regr_r2 234-------------------- 235 0.0194977982031803 236(1 row) 237 238SELECT regr_slope(b, a), regr_intercept(b, a) FROM aggtest; 239 regr_slope | regr_intercept 240-------------------+------------------ 241 0.512750700441271 | 82.5619926012309 242(1 row) 243 244SELECT covar_pop(b, a), covar_samp(b, a) FROM aggtest; 245 covar_pop | covar_samp 246-----------------+------------------ 247 653.62895538751 | 871.505273850014 248(1 row) 249 250SELECT corr(b, a) FROM aggtest; 251 corr 252------------------- 253 0.139634516517873 254(1 row) 255 256SELECT count(four) AS cnt_1000 FROM onek; 257 cnt_1000 258---------- 259 1000 260(1 row) 261 262SELECT count(DISTINCT four) AS cnt_4 FROM onek; 263 cnt_4 264------- 265 4 266(1 row) 267 268select ten, count(*), sum(four) from onek 269group by ten order by ten; 270 ten | count | sum 271-----+-------+----- 272 0 | 100 | 100 273 1 | 100 | 200 274 2 | 100 | 100 275 3 | 100 | 200 276 4 | 100 | 100 277 5 | 100 | 200 278 6 | 100 | 100 279 7 | 100 | 200 280 8 | 100 | 100 281 9 | 100 | 200 282(10 rows) 283 284select ten, count(four), sum(DISTINCT four) from onek 285group by ten order by ten; 286 ten | count | sum 287-----+-------+----- 288 0 | 100 | 2 289 1 | 100 | 4 290 2 | 100 | 2 291 3 | 100 | 4 292 4 | 100 | 2 293 5 | 100 | 4 294 6 | 100 | 2 295 7 | 100 | 4 296 8 | 100 | 2 297 9 | 100 | 4 298(10 rows) 299 300-- user-defined aggregates 301SELECT newavg(four) AS avg_1 FROM onek; 302 avg_1 303-------------------- 304 1.5000000000000000 305(1 row) 306 307SELECT newsum(four) AS sum_1500 FROM onek; 308 sum_1500 309---------- 310 1500 311(1 row) 312 313SELECT newcnt(four) AS cnt_1000 FROM onek; 314 cnt_1000 315---------- 316 1000 317(1 row) 318 319SELECT newcnt(*) AS cnt_1000 FROM onek; 320 cnt_1000 321---------- 322 1000 323(1 row) 324 325SELECT oldcnt(*) AS cnt_1000 FROM onek; 326 cnt_1000 327---------- 328 1000 329(1 row) 330 331SELECT sum2(q1,q2) FROM int8_tbl; 332 sum2 333------------------- 334 18271560493827981 335(1 row) 336 337-- test for outer-level aggregates 338-- this should work 339select ten, sum(distinct four) from onek a 340group by ten 341having exists (select 1 from onek b where sum(distinct a.four) = b.four); 342 ten | sum 343-----+----- 344 0 | 2 345 2 | 2 346 4 | 2 347 6 | 2 348 8 | 2 349(5 rows) 350 351-- this should fail because subquery has an agg of its own in WHERE 352select ten, sum(distinct four) from onek a 353group by ten 354having exists (select 1 from onek b 355 where sum(distinct a.four + b.four) = b.four); 356ERROR: aggregate functions are not allowed in WHERE 357LINE 4: where sum(distinct a.four + b.four) = b.four)... 358 ^ 359-- Test handling of sublinks within outer-level aggregates. 360-- Per bug report from Daniel Grace. 361select 362 (select max((select i.unique2 from tenk1 i where i.unique1 = o.unique1))) 363from tenk1 o; 364 max 365------ 366 9999 367(1 row) 368 369-- Test handling of Params within aggregate arguments in hashed aggregation. 370-- Per bug report from Jeevan Chalke. 371explain (verbose, costs off) 372select s1, s2, sm 373from generate_series(1, 3) s1, 374 lateral (select s2, sum(s1 + s2) sm 375 from generate_series(1, 3) s2 group by s2) ss 376order by 1, 2; 377 QUERY PLAN 378------------------------------------------------------------------ 379 Sort 380 Output: s1.s1, s2.s2, (sum((s1.s1 + s2.s2))) 381 Sort Key: s1.s1, s2.s2 382 -> Nested Loop 383 Output: s1.s1, s2.s2, (sum((s1.s1 + s2.s2))) 384 -> Function Scan on pg_catalog.generate_series s1 385 Output: s1.s1 386 Function Call: generate_series(1, 3) 387 -> HashAggregate 388 Output: s2.s2, sum((s1.s1 + s2.s2)) 389 Group Key: s2.s2 390 -> Function Scan on pg_catalog.generate_series s2 391 Output: s2.s2 392 Function Call: generate_series(1, 3) 393(14 rows) 394 395select s1, s2, sm 396from generate_series(1, 3) s1, 397 lateral (select s2, sum(s1 + s2) sm 398 from generate_series(1, 3) s2 group by s2) ss 399order by 1, 2; 400 s1 | s2 | sm 401----+----+---- 402 1 | 1 | 2 403 1 | 2 | 3 404 1 | 3 | 4 405 2 | 1 | 3 406 2 | 2 | 4 407 2 | 3 | 5 408 3 | 1 | 4 409 3 | 2 | 5 410 3 | 3 | 6 411(9 rows) 412 413explain (verbose, costs off) 414select array(select sum(x+y) s 415 from generate_series(1,3) y group by y order by s) 416 from generate_series(1,3) x; 417 QUERY PLAN 418------------------------------------------------------------------- 419 Function Scan on pg_catalog.generate_series x 420 Output: (SubPlan 1) 421 Function Call: generate_series(1, 3) 422 SubPlan 1 423 -> Sort 424 Output: (sum((x.x + y.y))), y.y 425 Sort Key: (sum((x.x + y.y))) 426 -> HashAggregate 427 Output: sum((x.x + y.y)), y.y 428 Group Key: y.y 429 -> Function Scan on pg_catalog.generate_series y 430 Output: y.y 431 Function Call: generate_series(1, 3) 432(13 rows) 433 434select array(select sum(x+y) s 435 from generate_series(1,3) y group by y order by s) 436 from generate_series(1,3) x; 437 array 438--------- 439 {2,3,4} 440 {3,4,5} 441 {4,5,6} 442(3 rows) 443 444-- 445-- test for bitwise integer aggregates 446-- 447CREATE TEMPORARY TABLE bitwise_test( 448 i2 INT2, 449 i4 INT4, 450 i8 INT8, 451 i INTEGER, 452 x INT2, 453 y BIT(4) 454); 455-- empty case 456SELECT 457 BIT_AND(i2) AS "?", 458 BIT_OR(i4) AS "?" 459FROM bitwise_test; 460 ? | ? 461---+--- 462 | 463(1 row) 464 465COPY bitwise_test FROM STDIN NULL 'null'; 466SELECT 467 BIT_AND(i2) AS "1", 468 BIT_AND(i4) AS "1", 469 BIT_AND(i8) AS "1", 470 BIT_AND(i) AS "?", 471 BIT_AND(x) AS "0", 472 BIT_AND(y) AS "0100", 473 BIT_OR(i2) AS "7", 474 BIT_OR(i4) AS "7", 475 BIT_OR(i8) AS "7", 476 BIT_OR(i) AS "?", 477 BIT_OR(x) AS "7", 478 BIT_OR(y) AS "1101" 479FROM bitwise_test; 480 1 | 1 | 1 | ? | 0 | 0100 | 7 | 7 | 7 | ? | 7 | 1101 481---+---+---+---+---+------+---+---+---+---+---+------ 482 1 | 1 | 1 | 1 | 0 | 0100 | 7 | 7 | 7 | 3 | 7 | 1101 483(1 row) 484 485-- 486-- test boolean aggregates 487-- 488-- first test all possible transition and final states 489SELECT 490 -- boolean and transitions 491 -- null because strict 492 booland_statefunc(NULL, NULL) IS NULL AS "t", 493 booland_statefunc(TRUE, NULL) IS NULL AS "t", 494 booland_statefunc(FALSE, NULL) IS NULL AS "t", 495 booland_statefunc(NULL, TRUE) IS NULL AS "t", 496 booland_statefunc(NULL, FALSE) IS NULL AS "t", 497 -- and actual computations 498 booland_statefunc(TRUE, TRUE) AS "t", 499 NOT booland_statefunc(TRUE, FALSE) AS "t", 500 NOT booland_statefunc(FALSE, TRUE) AS "t", 501 NOT booland_statefunc(FALSE, FALSE) AS "t"; 502 t | t | t | t | t | t | t | t | t 503---+---+---+---+---+---+---+---+--- 504 t | t | t | t | t | t | t | t | t 505(1 row) 506 507SELECT 508 -- boolean or transitions 509 -- null because strict 510 boolor_statefunc(NULL, NULL) IS NULL AS "t", 511 boolor_statefunc(TRUE, NULL) IS NULL AS "t", 512 boolor_statefunc(FALSE, NULL) IS NULL AS "t", 513 boolor_statefunc(NULL, TRUE) IS NULL AS "t", 514 boolor_statefunc(NULL, FALSE) IS NULL AS "t", 515 -- actual computations 516 boolor_statefunc(TRUE, TRUE) AS "t", 517 boolor_statefunc(TRUE, FALSE) AS "t", 518 boolor_statefunc(FALSE, TRUE) AS "t", 519 NOT boolor_statefunc(FALSE, FALSE) AS "t"; 520 t | t | t | t | t | t | t | t | t 521---+---+---+---+---+---+---+---+--- 522 t | t | t | t | t | t | t | t | t 523(1 row) 524 525CREATE TEMPORARY TABLE bool_test( 526 b1 BOOL, 527 b2 BOOL, 528 b3 BOOL, 529 b4 BOOL); 530-- empty case 531SELECT 532 BOOL_AND(b1) AS "n", 533 BOOL_OR(b3) AS "n" 534FROM bool_test; 535 n | n 536---+--- 537 | 538(1 row) 539 540COPY bool_test FROM STDIN NULL 'null'; 541SELECT 542 BOOL_AND(b1) AS "f", 543 BOOL_AND(b2) AS "t", 544 BOOL_AND(b3) AS "f", 545 BOOL_AND(b4) AS "n", 546 BOOL_AND(NOT b2) AS "f", 547 BOOL_AND(NOT b3) AS "t" 548FROM bool_test; 549 f | t | f | n | f | t 550---+---+---+---+---+--- 551 f | t | f | | f | t 552(1 row) 553 554SELECT 555 EVERY(b1) AS "f", 556 EVERY(b2) AS "t", 557 EVERY(b3) AS "f", 558 EVERY(b4) AS "n", 559 EVERY(NOT b2) AS "f", 560 EVERY(NOT b3) AS "t" 561FROM bool_test; 562 f | t | f | n | f | t 563---+---+---+---+---+--- 564 f | t | f | | f | t 565(1 row) 566 567SELECT 568 BOOL_OR(b1) AS "t", 569 BOOL_OR(b2) AS "t", 570 BOOL_OR(b3) AS "f", 571 BOOL_OR(b4) AS "n", 572 BOOL_OR(NOT b2) AS "f", 573 BOOL_OR(NOT b3) AS "t" 574FROM bool_test; 575 t | t | f | n | f | t 576---+---+---+---+---+--- 577 t | t | f | | f | t 578(1 row) 579 580-- 581-- Test cases that should be optimized into indexscans instead of 582-- the generic aggregate implementation. 583-- 584-- Basic cases 585explain (costs off) 586 select min(unique1) from tenk1; 587 QUERY PLAN 588------------------------------------------------------------ 589 Result 590 InitPlan 1 (returns $0) 591 -> Limit 592 -> Index Only Scan using tenk1_unique1 on tenk1 593 Index Cond: (unique1 IS NOT NULL) 594(5 rows) 595 596select min(unique1) from tenk1; 597 min 598----- 599 0 600(1 row) 601 602explain (costs off) 603 select max(unique1) from tenk1; 604 QUERY PLAN 605--------------------------------------------------------------------- 606 Result 607 InitPlan 1 (returns $0) 608 -> Limit 609 -> Index Only Scan Backward using tenk1_unique1 on tenk1 610 Index Cond: (unique1 IS NOT NULL) 611(5 rows) 612 613select max(unique1) from tenk1; 614 max 615------ 616 9999 617(1 row) 618 619explain (costs off) 620 select max(unique1) from tenk1 where unique1 < 42; 621 QUERY PLAN 622------------------------------------------------------------------------ 623 Result 624 InitPlan 1 (returns $0) 625 -> Limit 626 -> Index Only Scan Backward using tenk1_unique1 on tenk1 627 Index Cond: ((unique1 IS NOT NULL) AND (unique1 < 42)) 628(5 rows) 629 630select max(unique1) from tenk1 where unique1 < 42; 631 max 632----- 633 41 634(1 row) 635 636explain (costs off) 637 select max(unique1) from tenk1 where unique1 > 42; 638 QUERY PLAN 639------------------------------------------------------------------------ 640 Result 641 InitPlan 1 (returns $0) 642 -> Limit 643 -> Index Only Scan Backward using tenk1_unique1 on tenk1 644 Index Cond: ((unique1 IS NOT NULL) AND (unique1 > 42)) 645(5 rows) 646 647select max(unique1) from tenk1 where unique1 > 42; 648 max 649------ 650 9999 651(1 row) 652 653-- the planner may choose a generic aggregate here if parallel query is 654-- enabled, since that plan will be parallel safe and the "optimized" 655-- plan, which has almost identical cost, will not be. we want to test 656-- the optimized plan, so temporarily disable parallel query. 657begin; 658set local max_parallel_workers_per_gather = 0; 659explain (costs off) 660 select max(unique1) from tenk1 where unique1 > 42000; 661 QUERY PLAN 662--------------------------------------------------------------------------- 663 Result 664 InitPlan 1 (returns $0) 665 -> Limit 666 -> Index Only Scan Backward using tenk1_unique1 on tenk1 667 Index Cond: ((unique1 IS NOT NULL) AND (unique1 > 42000)) 668(5 rows) 669 670select max(unique1) from tenk1 where unique1 > 42000; 671 max 672----- 673 674(1 row) 675 676rollback; 677-- multi-column index (uses tenk1_thous_tenthous) 678explain (costs off) 679 select max(tenthous) from tenk1 where thousand = 33; 680 QUERY PLAN 681---------------------------------------------------------------------------- 682 Result 683 InitPlan 1 (returns $0) 684 -> Limit 685 -> Index Only Scan Backward using tenk1_thous_tenthous on tenk1 686 Index Cond: ((thousand = 33) AND (tenthous IS NOT NULL)) 687(5 rows) 688 689select max(tenthous) from tenk1 where thousand = 33; 690 max 691------ 692 9033 693(1 row) 694 695explain (costs off) 696 select min(tenthous) from tenk1 where thousand = 33; 697 QUERY PLAN 698-------------------------------------------------------------------------- 699 Result 700 InitPlan 1 (returns $0) 701 -> Limit 702 -> Index Only Scan using tenk1_thous_tenthous on tenk1 703 Index Cond: ((thousand = 33) AND (tenthous IS NOT NULL)) 704(5 rows) 705 706select min(tenthous) from tenk1 where thousand = 33; 707 min 708----- 709 33 710(1 row) 711 712-- check parameter propagation into an indexscan subquery 713explain (costs off) 714 select f1, (select min(unique1) from tenk1 where unique1 > f1) AS gt 715 from int4_tbl; 716 QUERY PLAN 717----------------------------------------------------------------------------------------- 718 Seq Scan on int4_tbl 719 SubPlan 2 720 -> Result 721 InitPlan 1 (returns $1) 722 -> Limit 723 -> Index Only Scan using tenk1_unique1 on tenk1 724 Index Cond: ((unique1 IS NOT NULL) AND (unique1 > int4_tbl.f1)) 725(7 rows) 726 727select f1, (select min(unique1) from tenk1 where unique1 > f1) AS gt 728 from int4_tbl; 729 f1 | gt 730-------------+---- 731 0 | 1 732 123456 | 733 -123456 | 0 734 2147483647 | 735 -2147483647 | 0 736(5 rows) 737 738-- check some cases that were handled incorrectly in 8.3.0 739explain (costs off) 740 select distinct max(unique2) from tenk1; 741 QUERY PLAN 742--------------------------------------------------------------------- 743 HashAggregate 744 Group Key: $0 745 InitPlan 1 (returns $0) 746 -> Limit 747 -> Index Only Scan Backward using tenk1_unique2 on tenk1 748 Index Cond: (unique2 IS NOT NULL) 749 -> Result 750(7 rows) 751 752select distinct max(unique2) from tenk1; 753 max 754------ 755 9999 756(1 row) 757 758explain (costs off) 759 select max(unique2) from tenk1 order by 1; 760 QUERY PLAN 761--------------------------------------------------------------------- 762 Sort 763 Sort Key: ($0) 764 InitPlan 1 (returns $0) 765 -> Limit 766 -> Index Only Scan Backward using tenk1_unique2 on tenk1 767 Index Cond: (unique2 IS NOT NULL) 768 -> Result 769(7 rows) 770 771select max(unique2) from tenk1 order by 1; 772 max 773------ 774 9999 775(1 row) 776 777explain (costs off) 778 select max(unique2) from tenk1 order by max(unique2); 779 QUERY PLAN 780--------------------------------------------------------------------- 781 Sort 782 Sort Key: ($0) 783 InitPlan 1 (returns $0) 784 -> Limit 785 -> Index Only Scan Backward using tenk1_unique2 on tenk1 786 Index Cond: (unique2 IS NOT NULL) 787 -> Result 788(7 rows) 789 790select max(unique2) from tenk1 order by max(unique2); 791 max 792------ 793 9999 794(1 row) 795 796explain (costs off) 797 select max(unique2) from tenk1 order by max(unique2)+1; 798 QUERY PLAN 799--------------------------------------------------------------------- 800 Sort 801 Sort Key: (($0 + 1)) 802 InitPlan 1 (returns $0) 803 -> Limit 804 -> Index Only Scan Backward using tenk1_unique2 on tenk1 805 Index Cond: (unique2 IS NOT NULL) 806 -> Result 807(7 rows) 808 809select max(unique2) from tenk1 order by max(unique2)+1; 810 max 811------ 812 9999 813(1 row) 814 815explain (costs off) 816 select max(unique2), generate_series(1,3) as g from tenk1 order by g desc; 817 QUERY PLAN 818--------------------------------------------------------------------- 819 Sort 820 Sort Key: (generate_series(1, 3)) DESC 821 InitPlan 1 (returns $0) 822 -> Limit 823 -> Index Only Scan Backward using tenk1_unique2 on tenk1 824 Index Cond: (unique2 IS NOT NULL) 825 -> ProjectSet 826 -> Result 827(8 rows) 828 829select max(unique2), generate_series(1,3) as g from tenk1 order by g desc; 830 max | g 831------+--- 832 9999 | 3 833 9999 | 2 834 9999 | 1 835(3 rows) 836 837-- interesting corner case: constant gets optimized into a seqscan 838explain (costs off) 839 select max(100) from tenk1; 840 QUERY PLAN 841---------------------------------------------------- 842 Result 843 InitPlan 1 (returns $0) 844 -> Limit 845 -> Result 846 One-Time Filter: (100 IS NOT NULL) 847 -> Seq Scan on tenk1 848(6 rows) 849 850select max(100) from tenk1; 851 max 852----- 853 100 854(1 row) 855 856-- try it on an inheritance tree 857create table minmaxtest(f1 int); 858create table minmaxtest1() inherits (minmaxtest); 859create table minmaxtest2() inherits (minmaxtest); 860create table minmaxtest3() inherits (minmaxtest); 861create index minmaxtesti on minmaxtest(f1); 862create index minmaxtest1i on minmaxtest1(f1); 863create index minmaxtest2i on minmaxtest2(f1 desc); 864create index minmaxtest3i on minmaxtest3(f1) where f1 is not null; 865insert into minmaxtest values(11), (12); 866insert into minmaxtest1 values(13), (14); 867insert into minmaxtest2 values(15), (16); 868insert into minmaxtest3 values(17), (18); 869explain (costs off) 870 select min(f1), max(f1) from minmaxtest; 871 QUERY PLAN 872---------------------------------------------------------------------------------------------- 873 Result 874 InitPlan 1 (returns $0) 875 -> Limit 876 -> Merge Append 877 Sort Key: minmaxtest.f1 878 -> Index Only Scan using minmaxtesti on minmaxtest 879 Index Cond: (f1 IS NOT NULL) 880 -> Index Only Scan using minmaxtest1i on minmaxtest1 881 Index Cond: (f1 IS NOT NULL) 882 -> Index Only Scan Backward using minmaxtest2i on minmaxtest2 883 Index Cond: (f1 IS NOT NULL) 884 -> Index Only Scan using minmaxtest3i on minmaxtest3 885 InitPlan 2 (returns $1) 886 -> Limit 887 -> Merge Append 888 Sort Key: minmaxtest_1.f1 DESC 889 -> Index Only Scan Backward using minmaxtesti on minmaxtest minmaxtest_1 890 Index Cond: (f1 IS NOT NULL) 891 -> Index Only Scan Backward using minmaxtest1i on minmaxtest1 minmaxtest1_1 892 Index Cond: (f1 IS NOT NULL) 893 -> Index Only Scan using minmaxtest2i on minmaxtest2 minmaxtest2_1 894 Index Cond: (f1 IS NOT NULL) 895 -> Index Only Scan Backward using minmaxtest3i on minmaxtest3 minmaxtest3_1 896(23 rows) 897 898select min(f1), max(f1) from minmaxtest; 899 min | max 900-----+----- 901 11 | 18 902(1 row) 903 904-- DISTINCT doesn't do anything useful here, but it shouldn't fail 905explain (costs off) 906 select distinct min(f1), max(f1) from minmaxtest; 907 QUERY PLAN 908---------------------------------------------------------------------------------------------- 909 Unique 910 InitPlan 1 (returns $0) 911 -> Limit 912 -> Merge Append 913 Sort Key: minmaxtest.f1 914 -> Index Only Scan using minmaxtesti on minmaxtest 915 Index Cond: (f1 IS NOT NULL) 916 -> Index Only Scan using minmaxtest1i on minmaxtest1 917 Index Cond: (f1 IS NOT NULL) 918 -> Index Only Scan Backward using minmaxtest2i on minmaxtest2 919 Index Cond: (f1 IS NOT NULL) 920 -> Index Only Scan using minmaxtest3i on minmaxtest3 921 InitPlan 2 (returns $1) 922 -> Limit 923 -> Merge Append 924 Sort Key: minmaxtest_1.f1 DESC 925 -> Index Only Scan Backward using minmaxtesti on minmaxtest minmaxtest_1 926 Index Cond: (f1 IS NOT NULL) 927 -> Index Only Scan Backward using minmaxtest1i on minmaxtest1 minmaxtest1_1 928 Index Cond: (f1 IS NOT NULL) 929 -> Index Only Scan using minmaxtest2i on minmaxtest2 minmaxtest2_1 930 Index Cond: (f1 IS NOT NULL) 931 -> Index Only Scan Backward using minmaxtest3i on minmaxtest3 minmaxtest3_1 932 -> Sort 933 Sort Key: ($0), ($1) 934 -> Result 935(26 rows) 936 937select distinct min(f1), max(f1) from minmaxtest; 938 min | max 939-----+----- 940 11 | 18 941(1 row) 942 943drop table minmaxtest cascade; 944NOTICE: drop cascades to 3 other objects 945DETAIL: drop cascades to table minmaxtest1 946drop cascades to table minmaxtest2 947drop cascades to table minmaxtest3 948-- check for correct detection of nested-aggregate errors 949select max(min(unique1)) from tenk1; 950ERROR: aggregate function calls cannot be nested 951LINE 1: select max(min(unique1)) from tenk1; 952 ^ 953select (select max(min(unique1)) from int8_tbl) from tenk1; 954ERROR: aggregate function calls cannot be nested 955LINE 1: select (select max(min(unique1)) from int8_tbl) from tenk1; 956 ^ 957-- 958-- Test removal of redundant GROUP BY columns 959-- 960create temp table t1 (a int, b int, c int, d int, primary key (a, b)); 961create temp table t2 (x int, y int, z int, primary key (x, y)); 962create temp table t3 (a int, b int, c int, primary key(a, b) deferrable); 963-- Non-primary-key columns can be removed from GROUP BY 964explain (costs off) select * from t1 group by a,b,c,d; 965 QUERY PLAN 966---------------------- 967 HashAggregate 968 Group Key: a, b 969 -> Seq Scan on t1 970(3 rows) 971 972-- No removal can happen if the complete PK is not present in GROUP BY 973explain (costs off) select a,c from t1 group by a,c,d; 974 QUERY PLAN 975---------------------- 976 HashAggregate 977 Group Key: a, c, d 978 -> Seq Scan on t1 979(3 rows) 980 981-- Test removal across multiple relations 982explain (costs off) select * 983from t1 inner join t2 on t1.a = t2.x and t1.b = t2.y 984group by t1.a,t1.b,t1.c,t1.d,t2.x,t2.y,t2.z; 985 QUERY PLAN 986------------------------------------------------------ 987 HashAggregate 988 Group Key: t1.a, t1.b, t2.x, t2.y 989 -> Hash Join 990 Hash Cond: ((t2.x = t1.a) AND (t2.y = t1.b)) 991 -> Seq Scan on t2 992 -> Hash 993 -> Seq Scan on t1 994(7 rows) 995 996-- Test case where t1 can be optimized but not t2 997explain (costs off) select t1.*,t2.x,t2.z 998from t1 inner join t2 on t1.a = t2.x and t1.b = t2.y 999group by t1.a,t1.b,t1.c,t1.d,t2.x,t2.z; 1000 QUERY PLAN 1001------------------------------------------------------ 1002 HashAggregate 1003 Group Key: t1.a, t1.b, t2.x, t2.z 1004 -> Hash Join 1005 Hash Cond: ((t2.x = t1.a) AND (t2.y = t1.b)) 1006 -> Seq Scan on t2 1007 -> Hash 1008 -> Seq Scan on t1 1009(7 rows) 1010 1011-- Cannot optimize when PK is deferrable 1012explain (costs off) select * from t3 group by a,b,c; 1013 QUERY PLAN 1014---------------------- 1015 HashAggregate 1016 Group Key: a, b, c 1017 -> Seq Scan on t3 1018(3 rows) 1019 1020create temp table t1c () inherits (t1); 1021-- Ensure we don't remove any columns when t1 has a child table 1022explain (costs off) select * from t1 group by a,b,c,d; 1023 QUERY PLAN 1024------------------------------------- 1025 HashAggregate 1026 Group Key: t1.a, t1.b, t1.c, t1.d 1027 -> Append 1028 -> Seq Scan on t1 1029 -> Seq Scan on t1c 1030(5 rows) 1031 1032-- Okay to remove columns if we're only querying the parent. 1033explain (costs off) select * from only t1 group by a,b,c,d; 1034 QUERY PLAN 1035---------------------- 1036 HashAggregate 1037 Group Key: a, b 1038 -> Seq Scan on t1 1039(3 rows) 1040 1041create temp table p_t1 ( 1042 a int, 1043 b int, 1044 c int, 1045 d int, 1046 primary key(a,b) 1047) partition by list(a); 1048create temp table p_t1_1 partition of p_t1 for values in(1); 1049create temp table p_t1_2 partition of p_t1 for values in(2); 1050-- Ensure we can remove non-PK columns for partitioned tables. 1051explain (costs off) select * from p_t1 group by a,b,c,d; 1052 QUERY PLAN 1053--------------------------------- 1054 HashAggregate 1055 Group Key: p_t1_1.a, p_t1_1.b 1056 -> Append 1057 -> Seq Scan on p_t1_1 1058 -> Seq Scan on p_t1_2 1059(5 rows) 1060 1061drop table t1 cascade; 1062NOTICE: drop cascades to table t1c 1063drop table t2; 1064drop table t3; 1065drop table p_t1; 1066-- 1067-- Test combinations of DISTINCT and/or ORDER BY 1068-- 1069select array_agg(a order by b) 1070 from (values (1,4),(2,3),(3,1),(4,2)) v(a,b); 1071 array_agg 1072----------- 1073 {3,4,2,1} 1074(1 row) 1075 1076select array_agg(a order by a) 1077 from (values (1,4),(2,3),(3,1),(4,2)) v(a,b); 1078 array_agg 1079----------- 1080 {1,2,3,4} 1081(1 row) 1082 1083select array_agg(a order by a desc) 1084 from (values (1,4),(2,3),(3,1),(4,2)) v(a,b); 1085 array_agg 1086----------- 1087 {4,3,2,1} 1088(1 row) 1089 1090select array_agg(b order by a desc) 1091 from (values (1,4),(2,3),(3,1),(4,2)) v(a,b); 1092 array_agg 1093----------- 1094 {2,1,3,4} 1095(1 row) 1096 1097select array_agg(distinct a) 1098 from (values (1),(2),(1),(3),(null),(2)) v(a); 1099 array_agg 1100-------------- 1101 {1,2,3,NULL} 1102(1 row) 1103 1104select array_agg(distinct a order by a) 1105 from (values (1),(2),(1),(3),(null),(2)) v(a); 1106 array_agg 1107-------------- 1108 {1,2,3,NULL} 1109(1 row) 1110 1111select array_agg(distinct a order by a desc) 1112 from (values (1),(2),(1),(3),(null),(2)) v(a); 1113 array_agg 1114-------------- 1115 {NULL,3,2,1} 1116(1 row) 1117 1118select array_agg(distinct a order by a desc nulls last) 1119 from (values (1),(2),(1),(3),(null),(2)) v(a); 1120 array_agg 1121-------------- 1122 {3,2,1,NULL} 1123(1 row) 1124 1125-- multi-arg aggs, strict/nonstrict, distinct/order by 1126select aggfstr(a,b,c) 1127 from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c); 1128 aggfstr 1129--------------------------------------- 1130 {"(1,3,foo)","(2,2,bar)","(3,1,baz)"} 1131(1 row) 1132 1133select aggfns(a,b,c) 1134 from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c); 1135 aggfns 1136----------------------------------------------- 1137 {"(1,3,foo)","(0,,)","(2,2,bar)","(3,1,baz)"} 1138(1 row) 1139 1140select aggfstr(distinct a,b,c) 1141 from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c), 1142 generate_series(1,3) i; 1143 aggfstr 1144--------------------------------------- 1145 {"(1,3,foo)","(2,2,bar)","(3,1,baz)"} 1146(1 row) 1147 1148select aggfns(distinct a,b,c) 1149 from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c), 1150 generate_series(1,3) i; 1151 aggfns 1152----------------------------------------------- 1153 {"(0,,)","(1,3,foo)","(2,2,bar)","(3,1,baz)"} 1154(1 row) 1155 1156select aggfstr(distinct a,b,c order by b) 1157 from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c), 1158 generate_series(1,3) i; 1159 aggfstr 1160--------------------------------------- 1161 {"(3,1,baz)","(2,2,bar)","(1,3,foo)"} 1162(1 row) 1163 1164select aggfns(distinct a,b,c order by b) 1165 from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c), 1166 generate_series(1,3) i; 1167 aggfns 1168----------------------------------------------- 1169 {"(3,1,baz)","(2,2,bar)","(1,3,foo)","(0,,)"} 1170(1 row) 1171 1172-- test specific code paths 1173select aggfns(distinct a,a,c order by c using ~<~,a) 1174 from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c), 1175 generate_series(1,2) i; 1176 aggfns 1177------------------------------------------------ 1178 {"(2,2,bar)","(3,3,baz)","(1,1,foo)","(0,0,)"} 1179(1 row) 1180 1181select aggfns(distinct a,a,c order by c using ~<~) 1182 from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c), 1183 generate_series(1,2) i; 1184 aggfns 1185------------------------------------------------ 1186 {"(2,2,bar)","(3,3,baz)","(1,1,foo)","(0,0,)"} 1187(1 row) 1188 1189select aggfns(distinct a,a,c order by a) 1190 from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c), 1191 generate_series(1,2) i; 1192 aggfns 1193------------------------------------------------ 1194 {"(0,0,)","(1,1,foo)","(2,2,bar)","(3,3,baz)"} 1195(1 row) 1196 1197select aggfns(distinct a,b,c order by a,c using ~<~,b) 1198 from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c), 1199 generate_series(1,2) i; 1200 aggfns 1201----------------------------------------------- 1202 {"(0,,)","(1,3,foo)","(2,2,bar)","(3,1,baz)"} 1203(1 row) 1204 1205-- check node I/O via view creation and usage, also deparsing logic 1206create view agg_view1 as 1207 select aggfns(a,b,c) 1208 from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c); 1209select * from agg_view1; 1210 aggfns 1211----------------------------------------------- 1212 {"(1,3,foo)","(0,,)","(2,2,bar)","(3,1,baz)"} 1213(1 row) 1214 1215select pg_get_viewdef('agg_view1'::regclass); 1216 pg_get_viewdef 1217--------------------------------------------------------------------------------------------------------------------- 1218 SELECT aggfns(v.a, v.b, v.c) AS aggfns + 1219 FROM ( VALUES (1,3,'foo'::text), (0,NULL::integer,NULL::text), (2,2,'bar'::text), (3,1,'baz'::text)) v(a, b, c); 1220(1 row) 1221 1222create or replace view agg_view1 as 1223 select aggfns(distinct a,b,c) 1224 from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c), 1225 generate_series(1,3) i; 1226select * from agg_view1; 1227 aggfns 1228----------------------------------------------- 1229 {"(0,,)","(1,3,foo)","(2,2,bar)","(3,1,baz)"} 1230(1 row) 1231 1232select pg_get_viewdef('agg_view1'::regclass); 1233 pg_get_viewdef 1234--------------------------------------------------------------------------------------------------------------------- 1235 SELECT aggfns(DISTINCT v.a, v.b, v.c) AS aggfns + 1236 FROM ( VALUES (1,3,'foo'::text), (0,NULL::integer,NULL::text), (2,2,'bar'::text), (3,1,'baz'::text)) v(a, b, c),+ 1237 generate_series(1, 3) i(i); 1238(1 row) 1239 1240create or replace view agg_view1 as 1241 select aggfns(distinct a,b,c order by b) 1242 from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c), 1243 generate_series(1,3) i; 1244select * from agg_view1; 1245 aggfns 1246----------------------------------------------- 1247 {"(3,1,baz)","(2,2,bar)","(1,3,foo)","(0,,)"} 1248(1 row) 1249 1250select pg_get_viewdef('agg_view1'::regclass); 1251 pg_get_viewdef 1252--------------------------------------------------------------------------------------------------------------------- 1253 SELECT aggfns(DISTINCT v.a, v.b, v.c ORDER BY v.b) AS aggfns + 1254 FROM ( VALUES (1,3,'foo'::text), (0,NULL::integer,NULL::text), (2,2,'bar'::text), (3,1,'baz'::text)) v(a, b, c),+ 1255 generate_series(1, 3) i(i); 1256(1 row) 1257 1258create or replace view agg_view1 as 1259 select aggfns(a,b,c order by b+1) 1260 from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c); 1261select * from agg_view1; 1262 aggfns 1263----------------------------------------------- 1264 {"(3,1,baz)","(2,2,bar)","(1,3,foo)","(0,,)"} 1265(1 row) 1266 1267select pg_get_viewdef('agg_view1'::regclass); 1268 pg_get_viewdef 1269--------------------------------------------------------------------------------------------------------------------- 1270 SELECT aggfns(v.a, v.b, v.c ORDER BY (v.b + 1)) AS aggfns + 1271 FROM ( VALUES (1,3,'foo'::text), (0,NULL::integer,NULL::text), (2,2,'bar'::text), (3,1,'baz'::text)) v(a, b, c); 1272(1 row) 1273 1274create or replace view agg_view1 as 1275 select aggfns(a,a,c order by b) 1276 from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c); 1277select * from agg_view1; 1278 aggfns 1279------------------------------------------------ 1280 {"(3,3,baz)","(2,2,bar)","(1,1,foo)","(0,0,)"} 1281(1 row) 1282 1283select pg_get_viewdef('agg_view1'::regclass); 1284 pg_get_viewdef 1285--------------------------------------------------------------------------------------------------------------------- 1286 SELECT aggfns(v.a, v.a, v.c ORDER BY v.b) AS aggfns + 1287 FROM ( VALUES (1,3,'foo'::text), (0,NULL::integer,NULL::text), (2,2,'bar'::text), (3,1,'baz'::text)) v(a, b, c); 1288(1 row) 1289 1290create or replace view agg_view1 as 1291 select aggfns(a,b,c order by c using ~<~) 1292 from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c); 1293select * from agg_view1; 1294 aggfns 1295----------------------------------------------- 1296 {"(2,2,bar)","(3,1,baz)","(1,3,foo)","(0,,)"} 1297(1 row) 1298 1299select pg_get_viewdef('agg_view1'::regclass); 1300 pg_get_viewdef 1301--------------------------------------------------------------------------------------------------------------------- 1302 SELECT aggfns(v.a, v.b, v.c ORDER BY v.c USING ~<~ NULLS LAST) AS aggfns + 1303 FROM ( VALUES (1,3,'foo'::text), (0,NULL::integer,NULL::text), (2,2,'bar'::text), (3,1,'baz'::text)) v(a, b, c); 1304(1 row) 1305 1306create or replace view agg_view1 as 1307 select aggfns(distinct a,b,c order by a,c using ~<~,b) 1308 from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c), 1309 generate_series(1,2) i; 1310select * from agg_view1; 1311 aggfns 1312----------------------------------------------- 1313 {"(0,,)","(1,3,foo)","(2,2,bar)","(3,1,baz)"} 1314(1 row) 1315 1316select pg_get_viewdef('agg_view1'::regclass); 1317 pg_get_viewdef 1318--------------------------------------------------------------------------------------------------------------------- 1319 SELECT aggfns(DISTINCT v.a, v.b, v.c ORDER BY v.a, v.c USING ~<~ NULLS LAST, v.b) AS aggfns + 1320 FROM ( VALUES (1,3,'foo'::text), (0,NULL::integer,NULL::text), (2,2,'bar'::text), (3,1,'baz'::text)) v(a, b, c),+ 1321 generate_series(1, 2) i(i); 1322(1 row) 1323 1324drop view agg_view1; 1325-- incorrect DISTINCT usage errors 1326select aggfns(distinct a,b,c order by i) 1327 from (values (1,1,'foo')) v(a,b,c), generate_series(1,2) i; 1328ERROR: in an aggregate with DISTINCT, ORDER BY expressions must appear in argument list 1329LINE 1: select aggfns(distinct a,b,c order by i) 1330 ^ 1331select aggfns(distinct a,b,c order by a,b+1) 1332 from (values (1,1,'foo')) v(a,b,c), generate_series(1,2) i; 1333ERROR: in an aggregate with DISTINCT, ORDER BY expressions must appear in argument list 1334LINE 1: select aggfns(distinct a,b,c order by a,b+1) 1335 ^ 1336select aggfns(distinct a,b,c order by a,b,i,c) 1337 from (values (1,1,'foo')) v(a,b,c), generate_series(1,2) i; 1338ERROR: in an aggregate with DISTINCT, ORDER BY expressions must appear in argument list 1339LINE 1: select aggfns(distinct a,b,c order by a,b,i,c) 1340 ^ 1341select aggfns(distinct a,a,c order by a,b) 1342 from (values (1,1,'foo')) v(a,b,c), generate_series(1,2) i; 1343ERROR: in an aggregate with DISTINCT, ORDER BY expressions must appear in argument list 1344LINE 1: select aggfns(distinct a,a,c order by a,b) 1345 ^ 1346-- string_agg tests 1347select string_agg(a,',') from (values('aaaa'),('bbbb'),('cccc')) g(a); 1348 string_agg 1349---------------- 1350 aaaa,bbbb,cccc 1351(1 row) 1352 1353select string_agg(a,',') from (values('aaaa'),(null),('bbbb'),('cccc')) g(a); 1354 string_agg 1355---------------- 1356 aaaa,bbbb,cccc 1357(1 row) 1358 1359select string_agg(a,'AB') from (values(null),(null),('bbbb'),('cccc')) g(a); 1360 string_agg 1361------------ 1362 bbbbABcccc 1363(1 row) 1364 1365select string_agg(a,',') from (values(null),(null)) g(a); 1366 string_agg 1367------------ 1368 1369(1 row) 1370 1371-- check some implicit casting cases, as per bug #5564 1372select string_agg(distinct f1, ',' order by f1) from varchar_tbl; -- ok 1373 string_agg 1374------------ 1375 a,ab,abcd 1376(1 row) 1377 1378select string_agg(distinct f1::text, ',' order by f1) from varchar_tbl; -- not ok 1379ERROR: in an aggregate with DISTINCT, ORDER BY expressions must appear in argument list 1380LINE 1: select string_agg(distinct f1::text, ',' order by f1) from v... 1381 ^ 1382select string_agg(distinct f1, ',' order by f1::text) from varchar_tbl; -- not ok 1383ERROR: in an aggregate with DISTINCT, ORDER BY expressions must appear in argument list 1384LINE 1: select string_agg(distinct f1, ',' order by f1::text) from v... 1385 ^ 1386select string_agg(distinct f1::text, ',' order by f1::text) from varchar_tbl; -- ok 1387 string_agg 1388------------ 1389 a,ab,abcd 1390(1 row) 1391 1392-- string_agg bytea tests 1393create table bytea_test_table(v bytea); 1394select string_agg(v, '') from bytea_test_table; 1395 string_agg 1396------------ 1397 1398(1 row) 1399 1400insert into bytea_test_table values(decode('ff','hex')); 1401select string_agg(v, '') from bytea_test_table; 1402 string_agg 1403------------ 1404 \xff 1405(1 row) 1406 1407insert into bytea_test_table values(decode('aa','hex')); 1408select string_agg(v, '') from bytea_test_table; 1409 string_agg 1410------------ 1411 \xffaa 1412(1 row) 1413 1414select string_agg(v, NULL) from bytea_test_table; 1415 string_agg 1416------------ 1417 \xffaa 1418(1 row) 1419 1420select string_agg(v, decode('ee', 'hex')) from bytea_test_table; 1421 string_agg 1422------------ 1423 \xffeeaa 1424(1 row) 1425 1426drop table bytea_test_table; 1427-- FILTER tests 1428select min(unique1) filter (where unique1 > 100) from tenk1; 1429 min 1430----- 1431 101 1432(1 row) 1433 1434select sum(1/ten) filter (where ten > 0) from tenk1; 1435 sum 1436------ 1437 1000 1438(1 row) 1439 1440select ten, sum(distinct four) filter (where four::text ~ '123') from onek a 1441group by ten; 1442 ten | sum 1443-----+----- 1444 0 | 1445 1 | 1446 2 | 1447 3 | 1448 4 | 1449 5 | 1450 6 | 1451 7 | 1452 8 | 1453 9 | 1454(10 rows) 1455 1456select ten, sum(distinct four) filter (where four > 10) from onek a 1457group by ten 1458having exists (select 1 from onek b where sum(distinct a.four) = b.four); 1459 ten | sum 1460-----+----- 1461 0 | 1462 2 | 1463 4 | 1464 6 | 1465 8 | 1466(5 rows) 1467 1468select max(foo COLLATE "C") filter (where (bar collate "POSIX") > '0') 1469from (values ('a', 'b')) AS v(foo,bar); 1470 max 1471----- 1472 a 1473(1 row) 1474 1475-- outer reference in FILTER (PostgreSQL extension) 1476select (select count(*) 1477 from (values (1)) t0(inner_c)) 1478from (values (2),(3)) t1(outer_c); -- inner query is aggregation query 1479 count 1480------- 1481 1 1482 1 1483(2 rows) 1484 1485select (select count(*) filter (where outer_c <> 0) 1486 from (values (1)) t0(inner_c)) 1487from (values (2),(3)) t1(outer_c); -- outer query is aggregation query 1488 count 1489------- 1490 2 1491(1 row) 1492 1493select (select count(inner_c) filter (where outer_c <> 0) 1494 from (values (1)) t0(inner_c)) 1495from (values (2),(3)) t1(outer_c); -- inner query is aggregation query 1496 count 1497------- 1498 1 1499 1 1500(2 rows) 1501 1502select 1503 (select max((select i.unique2 from tenk1 i where i.unique1 = o.unique1)) 1504 filter (where o.unique1 < 10)) 1505from tenk1 o; -- outer query is aggregation query 1506 max 1507------ 1508 9998 1509(1 row) 1510 1511-- subquery in FILTER clause (PostgreSQL extension) 1512select sum(unique1) FILTER (WHERE 1513 unique1 IN (SELECT unique1 FROM onek where unique1 < 100)) FROM tenk1; 1514 sum 1515------ 1516 4950 1517(1 row) 1518 1519-- exercise lots of aggregate parts with FILTER 1520select aggfns(distinct a,b,c order by a,c using ~<~,b) filter (where a > 1) 1521 from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c), 1522 generate_series(1,2) i; 1523 aggfns 1524--------------------------- 1525 {"(2,2,bar)","(3,1,baz)"} 1526(1 row) 1527 1528-- check handling of bare boolean Var in FILTER 1529select max(0) filter (where b1) from bool_test; 1530 max 1531----- 1532 0 1533(1 row) 1534 1535select (select max(0) filter (where b1)) from bool_test; 1536 max 1537----- 1538 0 1539(1 row) 1540 1541-- check for correct detection of nested-aggregate errors in FILTER 1542select max(unique1) filter (where sum(ten) > 0) from tenk1; 1543ERROR: aggregate functions are not allowed in FILTER 1544LINE 1: select max(unique1) filter (where sum(ten) > 0) from tenk1; 1545 ^ 1546select (select max(unique1) filter (where sum(ten) > 0) from int8_tbl) from tenk1; 1547ERROR: aggregate function calls cannot be nested 1548LINE 1: select (select max(unique1) filter (where sum(ten) > 0) from... 1549 ^ 1550select max(unique1) filter (where bool_or(ten > 0)) from tenk1; 1551ERROR: aggregate functions are not allowed in FILTER 1552LINE 1: select max(unique1) filter (where bool_or(ten > 0)) from ten... 1553 ^ 1554select (select max(unique1) filter (where bool_or(ten > 0)) from int8_tbl) from tenk1; 1555ERROR: aggregate function calls cannot be nested 1556LINE 1: select (select max(unique1) filter (where bool_or(ten > 0)) ... 1557 ^ 1558-- ordered-set aggregates 1559select p, percentile_cont(p) within group (order by x::float8) 1560from generate_series(1,5) x, 1561 (values (0::float8),(0.1),(0.25),(0.4),(0.5),(0.6),(0.75),(0.9),(1)) v(p) 1562group by p order by p; 1563 p | percentile_cont 1564------+----------------- 1565 0 | 1 1566 0.1 | 1.4 1567 0.25 | 2 1568 0.4 | 2.6 1569 0.5 | 3 1570 0.6 | 3.4 1571 0.75 | 4 1572 0.9 | 4.6 1573 1 | 5 1574(9 rows) 1575 1576select p, percentile_cont(p order by p) within group (order by x) -- error 1577from generate_series(1,5) x, 1578 (values (0::float8),(0.1),(0.25),(0.4),(0.5),(0.6),(0.75),(0.9),(1)) v(p) 1579group by p order by p; 1580ERROR: cannot use multiple ORDER BY clauses with WITHIN GROUP 1581LINE 1: select p, percentile_cont(p order by p) within group (order ... 1582 ^ 1583select p, sum() within group (order by x::float8) -- error 1584from generate_series(1,5) x, 1585 (values (0::float8),(0.1),(0.25),(0.4),(0.5),(0.6),(0.75),(0.9),(1)) v(p) 1586group by p order by p; 1587ERROR: sum is not an ordered-set aggregate, so it cannot have WITHIN GROUP 1588LINE 1: select p, sum() within group (order by x::float8) 1589 ^ 1590select p, percentile_cont(p,p) -- error 1591from generate_series(1,5) x, 1592 (values (0::float8),(0.1),(0.25),(0.4),(0.5),(0.6),(0.75),(0.9),(1)) v(p) 1593group by p order by p; 1594ERROR: WITHIN GROUP is required for ordered-set aggregate percentile_cont 1595LINE 1: select p, percentile_cont(p,p) 1596 ^ 1597select percentile_cont(0.5) within group (order by b) from aggtest; 1598 percentile_cont 1599------------------ 1600 53.4485001564026 1601(1 row) 1602 1603select percentile_cont(0.5) within group (order by b), sum(b) from aggtest; 1604 percentile_cont | sum 1605------------------+--------- 1606 53.4485001564026 | 431.773 1607(1 row) 1608 1609select percentile_cont(0.5) within group (order by thousand) from tenk1; 1610 percentile_cont 1611----------------- 1612 499.5 1613(1 row) 1614 1615select percentile_disc(0.5) within group (order by thousand) from tenk1; 1616 percentile_disc 1617----------------- 1618 499 1619(1 row) 1620 1621select rank(3) within group (order by x) 1622from (values (1),(1),(2),(2),(3),(3),(4)) v(x); 1623 rank 1624------ 1625 5 1626(1 row) 1627 1628select cume_dist(3) within group (order by x) 1629from (values (1),(1),(2),(2),(3),(3),(4)) v(x); 1630 cume_dist 1631----------- 1632 0.875 1633(1 row) 1634 1635select percent_rank(3) within group (order by x) 1636from (values (1),(1),(2),(2),(3),(3),(4),(5)) v(x); 1637 percent_rank 1638-------------- 1639 0.5 1640(1 row) 1641 1642select dense_rank(3) within group (order by x) 1643from (values (1),(1),(2),(2),(3),(3),(4)) v(x); 1644 dense_rank 1645------------ 1646 3 1647(1 row) 1648 1649select percentile_disc(array[0,0.1,0.25,0.5,0.75,0.9,1]) within group (order by thousand) 1650from tenk1; 1651 percentile_disc 1652---------------------------- 1653 {0,99,249,499,749,899,999} 1654(1 row) 1655 1656select percentile_cont(array[0,0.25,0.5,0.75,1]) within group (order by thousand) 1657from tenk1; 1658 percentile_cont 1659----------------------------- 1660 {0,249.75,499.5,749.25,999} 1661(1 row) 1662 1663select percentile_disc(array[[null,1,0.5],[0.75,0.25,null]]) within group (order by thousand) 1664from tenk1; 1665 percentile_disc 1666--------------------------------- 1667 {{NULL,999,499},{749,249,NULL}} 1668(1 row) 1669 1670select percentile_cont(array[0,1,0.25,0.75,0.5,1,0.3,0.32,0.35,0.38,0.4]) within group (order by x) 1671from generate_series(1,6) x; 1672 percentile_cont 1673------------------------------------------ 1674 {1,6,2.25,4.75,3.5,6,2.5,2.6,2.75,2.9,3} 1675(1 row) 1676 1677select ten, mode() within group (order by string4) from tenk1 group by ten; 1678 ten | mode 1679-----+-------- 1680 0 | HHHHxx 1681 1 | OOOOxx 1682 2 | VVVVxx 1683 3 | OOOOxx 1684 4 | HHHHxx 1685 5 | HHHHxx 1686 6 | OOOOxx 1687 7 | AAAAxx 1688 8 | VVVVxx 1689 9 | VVVVxx 1690(10 rows) 1691 1692select percentile_disc(array[0.25,0.5,0.75]) within group (order by x) 1693from unnest('{fred,jim,fred,jack,jill,fred,jill,jim,jim,sheila,jim,sheila}'::text[]) u(x); 1694 percentile_disc 1695----------------- 1696 {fred,jill,jim} 1697(1 row) 1698 1699-- check collation propagates up in suitable cases: 1700select pg_collation_for(percentile_disc(1) within group (order by x collate "POSIX")) 1701 from (values ('fred'),('jim')) v(x); 1702 pg_collation_for 1703------------------ 1704 "POSIX" 1705(1 row) 1706 1707-- ordered-set aggs created with CREATE AGGREGATE 1708select test_rank(3) within group (order by x) 1709from (values (1),(1),(2),(2),(3),(3),(4)) v(x); 1710 test_rank 1711----------- 1712 5 1713(1 row) 1714 1715select test_percentile_disc(0.5) within group (order by thousand) from tenk1; 1716 test_percentile_disc 1717---------------------- 1718 499 1719(1 row) 1720 1721-- ordered-set aggs can't use ungrouped vars in direct args: 1722select rank(x) within group (order by x) from generate_series(1,5) x; 1723ERROR: column "x.x" must appear in the GROUP BY clause or be used in an aggregate function 1724LINE 1: select rank(x) within group (order by x) from generate_serie... 1725 ^ 1726DETAIL: Direct arguments of an ordered-set aggregate must use only grouped columns. 1727-- outer-level agg can't use a grouped arg of a lower level, either: 1728select array(select percentile_disc(a) within group (order by x) 1729 from (values (0.3),(0.7)) v(a) group by a) 1730 from generate_series(1,5) g(x); 1731ERROR: outer-level aggregate cannot contain a lower-level variable in its direct arguments 1732LINE 1: select array(select percentile_disc(a) within group (order b... 1733 ^ 1734-- agg in the direct args is a grouping violation, too: 1735select rank(sum(x)) within group (order by x) from generate_series(1,5) x; 1736ERROR: aggregate function calls cannot be nested 1737LINE 1: select rank(sum(x)) within group (order by x) from generate_... 1738 ^ 1739-- hypothetical-set type unification and argument-count failures: 1740select rank(3) within group (order by x) from (values ('fred'),('jim')) v(x); 1741ERROR: WITHIN GROUP types text and integer cannot be matched 1742LINE 1: select rank(3) within group (order by x) from (values ('fred... 1743 ^ 1744select rank(3) within group (order by stringu1,stringu2) from tenk1; 1745ERROR: function rank(integer, name, name) does not exist 1746LINE 1: select rank(3) within group (order by stringu1,stringu2) fro... 1747 ^ 1748HINT: To use the hypothetical-set aggregate rank, the number of hypothetical direct arguments (here 1) must match the number of ordering columns (here 2). 1749select rank('fred') within group (order by x) from generate_series(1,5) x; 1750ERROR: invalid input syntax for integer: "fred" 1751LINE 1: select rank('fred') within group (order by x) from generate_... 1752 ^ 1753select rank('adam'::text collate "C") within group (order by x collate "POSIX") 1754 from (values ('fred'),('jim')) v(x); 1755ERROR: collation mismatch between explicit collations "C" and "POSIX" 1756LINE 1: ...adam'::text collate "C") within group (order by x collate "P... 1757 ^ 1758-- hypothetical-set type unification successes: 1759select rank('adam'::varchar) within group (order by x) from (values ('fred'),('jim')) v(x); 1760 rank 1761------ 1762 1 1763(1 row) 1764 1765select rank('3') within group (order by x) from generate_series(1,5) x; 1766 rank 1767------ 1768 3 1769(1 row) 1770 1771-- divide by zero check 1772select percent_rank(0) within group (order by x) from generate_series(1,0) x; 1773 percent_rank 1774-------------- 1775 0 1776(1 row) 1777 1778-- deparse and multiple features: 1779create view aggordview1 as 1780select ten, 1781 percentile_disc(0.5) within group (order by thousand) as p50, 1782 percentile_disc(0.5) within group (order by thousand) filter (where hundred=1) as px, 1783 rank(5,'AZZZZ',50) within group (order by hundred, string4 desc, hundred) 1784 from tenk1 1785 group by ten order by ten; 1786select pg_get_viewdef('aggordview1'); 1787 pg_get_viewdef 1788------------------------------------------------------------------------------------------------------------------------------- 1789 SELECT tenk1.ten, + 1790 percentile_disc((0.5)::double precision) WITHIN GROUP (ORDER BY tenk1.thousand) AS p50, + 1791 percentile_disc((0.5)::double precision) WITHIN GROUP (ORDER BY tenk1.thousand) FILTER (WHERE (tenk1.hundred = 1)) AS px,+ 1792 rank(5, 'AZZZZ'::name, 50) WITHIN GROUP (ORDER BY tenk1.hundred, tenk1.string4 DESC, tenk1.hundred) AS rank + 1793 FROM tenk1 + 1794 GROUP BY tenk1.ten + 1795 ORDER BY tenk1.ten; 1796(1 row) 1797 1798select * from aggordview1 order by ten; 1799 ten | p50 | px | rank 1800-----+-----+-----+------ 1801 0 | 490 | | 101 1802 1 | 491 | 401 | 101 1803 2 | 492 | | 101 1804 3 | 493 | | 101 1805 4 | 494 | | 101 1806 5 | 495 | | 67 1807 6 | 496 | | 1 1808 7 | 497 | | 1 1809 8 | 498 | | 1 1810 9 | 499 | | 1 1811(10 rows) 1812 1813drop view aggordview1; 1814-- variadic aggregates 1815select least_agg(q1,q2) from int8_tbl; 1816 least_agg 1817------------------- 1818 -4567890123456789 1819(1 row) 1820 1821select least_agg(variadic array[q1,q2]) from int8_tbl; 1822 least_agg 1823------------------- 1824 -4567890123456789 1825(1 row) 1826 1827-- test aggregates with common transition functions share the same states 1828begin work; 1829create type avg_state as (total bigint, count bigint); 1830create or replace function avg_transfn(state avg_state, n int) returns avg_state as 1831$$ 1832declare new_state avg_state; 1833begin 1834 raise notice 'avg_transfn called with %', n; 1835 if state is null then 1836 if n is not null then 1837 new_state.total := n; 1838 new_state.count := 1; 1839 return new_state; 1840 end if; 1841 return null; 1842 elsif n is not null then 1843 state.total := state.total + n; 1844 state.count := state.count + 1; 1845 return state; 1846 end if; 1847 1848 return null; 1849end 1850$$ language plpgsql; 1851create function avg_finalfn(state avg_state) returns int4 as 1852$$ 1853begin 1854 if state is null then 1855 return NULL; 1856 else 1857 return state.total / state.count; 1858 end if; 1859end 1860$$ language plpgsql; 1861create function sum_finalfn(state avg_state) returns int4 as 1862$$ 1863begin 1864 if state is null then 1865 return NULL; 1866 else 1867 return state.total; 1868 end if; 1869end 1870$$ language plpgsql; 1871create aggregate my_avg(int4) 1872( 1873 stype = avg_state, 1874 sfunc = avg_transfn, 1875 finalfunc = avg_finalfn 1876); 1877create aggregate my_sum(int4) 1878( 1879 stype = avg_state, 1880 sfunc = avg_transfn, 1881 finalfunc = sum_finalfn 1882); 1883-- aggregate state should be shared as aggs are the same. 1884select my_avg(one),my_avg(one) from (values(1),(3)) t(one); 1885NOTICE: avg_transfn called with 1 1886NOTICE: avg_transfn called with 3 1887 my_avg | my_avg 1888--------+-------- 1889 2 | 2 1890(1 row) 1891 1892-- aggregate state should be shared as transfn is the same for both aggs. 1893select my_avg(one),my_sum(one) from (values(1),(3)) t(one); 1894NOTICE: avg_transfn called with 1 1895NOTICE: avg_transfn called with 3 1896 my_avg | my_sum 1897--------+-------- 1898 2 | 4 1899(1 row) 1900 1901-- same as previous one, but with DISTINCT, which requires sorting the input. 1902select my_avg(distinct one),my_sum(distinct one) from (values(1),(3),(1)) t(one); 1903NOTICE: avg_transfn called with 1 1904NOTICE: avg_transfn called with 3 1905 my_avg | my_sum 1906--------+-------- 1907 2 | 4 1908(1 row) 1909 1910-- shouldn't share states due to the distinctness not matching. 1911select my_avg(distinct one),my_sum(one) from (values(1),(3)) t(one); 1912NOTICE: avg_transfn called with 1 1913NOTICE: avg_transfn called with 3 1914NOTICE: avg_transfn called with 1 1915NOTICE: avg_transfn called with 3 1916 my_avg | my_sum 1917--------+-------- 1918 2 | 4 1919(1 row) 1920 1921-- shouldn't share states due to the filter clause not matching. 1922select my_avg(one) filter (where one > 1),my_sum(one) from (values(1),(3)) t(one); 1923NOTICE: avg_transfn called with 1 1924NOTICE: avg_transfn called with 3 1925NOTICE: avg_transfn called with 3 1926 my_avg | my_sum 1927--------+-------- 1928 3 | 4 1929(1 row) 1930 1931-- this should not share the state due to different input columns. 1932select my_avg(one),my_sum(two) from (values(1,2),(3,4)) t(one,two); 1933NOTICE: avg_transfn called with 2 1934NOTICE: avg_transfn called with 1 1935NOTICE: avg_transfn called with 4 1936NOTICE: avg_transfn called with 3 1937 my_avg | my_sum 1938--------+-------- 1939 2 | 6 1940(1 row) 1941 1942-- exercise cases where OSAs share state 1943select 1944 percentile_cont(0.5) within group (order by a), 1945 percentile_disc(0.5) within group (order by a) 1946from (values(1::float8),(3),(5),(7)) t(a); 1947 percentile_cont | percentile_disc 1948-----------------+----------------- 1949 4 | 3 1950(1 row) 1951 1952select 1953 percentile_cont(0.25) within group (order by a), 1954 percentile_disc(0.5) within group (order by a) 1955from (values(1::float8),(3),(5),(7)) t(a); 1956 percentile_cont | percentile_disc 1957-----------------+----------------- 1958 2.5 | 3 1959(1 row) 1960 1961-- these can't share state currently 1962select 1963 rank(4) within group (order by a), 1964 dense_rank(4) within group (order by a) 1965from (values(1),(3),(5),(7)) t(a); 1966 rank | dense_rank 1967------+------------ 1968 3 | 3 1969(1 row) 1970 1971-- test that aggs with the same sfunc and initcond share the same agg state 1972create aggregate my_sum_init(int4) 1973( 1974 stype = avg_state, 1975 sfunc = avg_transfn, 1976 finalfunc = sum_finalfn, 1977 initcond = '(10,0)' 1978); 1979create aggregate my_avg_init(int4) 1980( 1981 stype = avg_state, 1982 sfunc = avg_transfn, 1983 finalfunc = avg_finalfn, 1984 initcond = '(10,0)' 1985); 1986create aggregate my_avg_init2(int4) 1987( 1988 stype = avg_state, 1989 sfunc = avg_transfn, 1990 finalfunc = avg_finalfn, 1991 initcond = '(4,0)' 1992); 1993-- state should be shared if INITCONDs are matching 1994select my_sum_init(one),my_avg_init(one) from (values(1),(3)) t(one); 1995NOTICE: avg_transfn called with 1 1996NOTICE: avg_transfn called with 3 1997 my_sum_init | my_avg_init 1998-------------+------------- 1999 14 | 7 2000(1 row) 2001 2002-- Varying INITCONDs should cause the states not to be shared. 2003select my_sum_init(one),my_avg_init2(one) from (values(1),(3)) t(one); 2004NOTICE: avg_transfn called with 1 2005NOTICE: avg_transfn called with 1 2006NOTICE: avg_transfn called with 3 2007NOTICE: avg_transfn called with 3 2008 my_sum_init | my_avg_init2 2009-------------+-------------- 2010 14 | 4 2011(1 row) 2012 2013rollback; 2014-- test aggregate state sharing to ensure it works if one aggregate has a 2015-- finalfn and the other one has none. 2016begin work; 2017create or replace function sum_transfn(state int4, n int4) returns int4 as 2018$$ 2019declare new_state int4; 2020begin 2021 raise notice 'sum_transfn called with %', n; 2022 if state is null then 2023 if n is not null then 2024 new_state := n; 2025 return new_state; 2026 end if; 2027 return null; 2028 elsif n is not null then 2029 state := state + n; 2030 return state; 2031 end if; 2032 2033 return null; 2034end 2035$$ language plpgsql; 2036create function halfsum_finalfn(state int4) returns int4 as 2037$$ 2038begin 2039 if state is null then 2040 return NULL; 2041 else 2042 return state / 2; 2043 end if; 2044end 2045$$ language plpgsql; 2046create aggregate my_sum(int4) 2047( 2048 stype = int4, 2049 sfunc = sum_transfn 2050); 2051create aggregate my_half_sum(int4) 2052( 2053 stype = int4, 2054 sfunc = sum_transfn, 2055 finalfunc = halfsum_finalfn 2056); 2057-- Agg state should be shared even though my_sum has no finalfn 2058select my_sum(one),my_half_sum(one) from (values(1),(2),(3),(4)) t(one); 2059NOTICE: sum_transfn called with 1 2060NOTICE: sum_transfn called with 2 2061NOTICE: sum_transfn called with 3 2062NOTICE: sum_transfn called with 4 2063 my_sum | my_half_sum 2064--------+------------- 2065 10 | 5 2066(1 row) 2067 2068rollback; 2069-- test that the aggregate transition logic correctly handles 2070-- transition / combine functions returning NULL 2071-- First test the case of a normal transition function returning NULL 2072BEGIN; 2073CREATE FUNCTION balkifnull(int8, int4) 2074RETURNS int8 2075STRICT 2076LANGUAGE plpgsql AS $$ 2077BEGIN 2078 IF $1 IS NULL THEN 2079 RAISE 'erroneously called with NULL argument'; 2080 END IF; 2081 RETURN NULL; 2082END$$; 2083CREATE AGGREGATE balk(int4) 2084( 2085 SFUNC = balkifnull(int8, int4), 2086 STYPE = int8, 2087 PARALLEL = SAFE, 2088 INITCOND = '0' 2089); 2090SELECT balk(hundred) FROM tenk1; 2091 balk 2092------ 2093 2094(1 row) 2095 2096ROLLBACK; 2097-- Secondly test the case of a parallel aggregate combiner function 2098-- returning NULL. For that use normal transition function, but a 2099-- combiner function returning NULL. 2100BEGIN ISOLATION LEVEL REPEATABLE READ; 2101CREATE FUNCTION balkifnull(int8, int8) 2102RETURNS int8 2103PARALLEL SAFE 2104STRICT 2105LANGUAGE plpgsql AS $$ 2106BEGIN 2107 IF $1 IS NULL THEN 2108 RAISE 'erroneously called with NULL argument'; 2109 END IF; 2110 RETURN NULL; 2111END$$; 2112CREATE AGGREGATE balk(int4) 2113( 2114 SFUNC = int4_sum(int8, int4), 2115 STYPE = int8, 2116 COMBINEFUNC = balkifnull(int8, int8), 2117 PARALLEL = SAFE, 2118 INITCOND = '0' 2119); 2120-- force use of parallelism 2121ALTER TABLE tenk1 set (parallel_workers = 4); 2122SET LOCAL parallel_setup_cost=0; 2123SET LOCAL max_parallel_workers_per_gather=4; 2124EXPLAIN (COSTS OFF) SELECT balk(hundred) FROM tenk1; 2125 QUERY PLAN 2126------------------------------------------------------------------------- 2127 Finalize Aggregate 2128 -> Gather 2129 Workers Planned: 4 2130 -> Partial Aggregate 2131 -> Parallel Index Only Scan using tenk1_hundred on tenk1 2132(5 rows) 2133 2134SELECT balk(hundred) FROM tenk1; 2135 balk 2136------ 2137 2138(1 row) 2139 2140ROLLBACK; 2141-- test coverage for aggregate combine/serial/deserial functions 2142BEGIN ISOLATION LEVEL REPEATABLE READ; 2143SET parallel_setup_cost = 0; 2144SET parallel_tuple_cost = 0; 2145SET min_parallel_table_scan_size = 0; 2146SET max_parallel_workers_per_gather = 4; 2147SET parallel_leader_participation = off; 2148SET enable_indexonlyscan = off; 2149-- variance(int4) covers numeric_poly_combine 2150-- sum(int8) covers int8_avg_combine 2151-- regr_count(float8, float8) covers int8inc_float8_float8 and aggregates with > 1 arg 2152EXPLAIN (COSTS OFF, VERBOSE) 2153SELECT variance(unique1::int4), sum(unique1::int8), regr_count(unique1::float8, unique1::float8) 2154FROM (SELECT * FROM tenk1 2155 UNION ALL SELECT * FROM tenk1 2156 UNION ALL SELECT * FROM tenk1 2157 UNION ALL SELECT * FROM tenk1) u; 2158 QUERY PLAN 2159--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 2160 Finalize Aggregate 2161 Output: variance(tenk1.unique1), sum((tenk1.unique1)::bigint), regr_count((tenk1.unique1)::double precision, (tenk1.unique1)::double precision) 2162 -> Gather 2163 Output: (PARTIAL variance(tenk1.unique1)), (PARTIAL sum((tenk1.unique1)::bigint)), (PARTIAL regr_count((tenk1.unique1)::double precision, (tenk1.unique1)::double precision)) 2164 Workers Planned: 4 2165 -> Partial Aggregate 2166 Output: PARTIAL variance(tenk1.unique1), PARTIAL sum((tenk1.unique1)::bigint), PARTIAL regr_count((tenk1.unique1)::double precision, (tenk1.unique1)::double precision) 2167 -> Parallel Append 2168 -> Parallel Seq Scan on public.tenk1 2169 Output: tenk1.unique1 2170 -> Parallel Seq Scan on public.tenk1 tenk1_1 2171 Output: tenk1_1.unique1 2172 -> Parallel Seq Scan on public.tenk1 tenk1_2 2173 Output: tenk1_2.unique1 2174 -> Parallel Seq Scan on public.tenk1 tenk1_3 2175 Output: tenk1_3.unique1 2176(16 rows) 2177 2178SELECT variance(unique1::int4), sum(unique1::int8), regr_count(unique1::float8, unique1::float8) 2179FROM (SELECT * FROM tenk1 2180 UNION ALL SELECT * FROM tenk1 2181 UNION ALL SELECT * FROM tenk1 2182 UNION ALL SELECT * FROM tenk1) u; 2183 variance | sum | regr_count 2184----------------------+-----------+------------ 2185 8333541.588539713493 | 199980000 | 40000 2186(1 row) 2187 2188-- variance(int8) covers numeric_combine 2189-- avg(numeric) covers numeric_avg_combine 2190EXPLAIN (COSTS OFF, VERBOSE) 2191SELECT variance(unique1::int8), avg(unique1::numeric) 2192FROM (SELECT * FROM tenk1 2193 UNION ALL SELECT * FROM tenk1 2194 UNION ALL SELECT * FROM tenk1 2195 UNION ALL SELECT * FROM tenk1) u; 2196 QUERY PLAN 2197-------------------------------------------------------------------------------------------------------- 2198 Finalize Aggregate 2199 Output: variance((tenk1.unique1)::bigint), avg((tenk1.unique1)::numeric) 2200 -> Gather 2201 Output: (PARTIAL variance((tenk1.unique1)::bigint)), (PARTIAL avg((tenk1.unique1)::numeric)) 2202 Workers Planned: 4 2203 -> Partial Aggregate 2204 Output: PARTIAL variance((tenk1.unique1)::bigint), PARTIAL avg((tenk1.unique1)::numeric) 2205 -> Parallel Append 2206 -> Parallel Seq Scan on public.tenk1 2207 Output: tenk1.unique1 2208 -> Parallel Seq Scan on public.tenk1 tenk1_1 2209 Output: tenk1_1.unique1 2210 -> Parallel Seq Scan on public.tenk1 tenk1_2 2211 Output: tenk1_2.unique1 2212 -> Parallel Seq Scan on public.tenk1 tenk1_3 2213 Output: tenk1_3.unique1 2214(16 rows) 2215 2216SELECT variance(unique1::int8), avg(unique1::numeric) 2217FROM (SELECT * FROM tenk1 2218 UNION ALL SELECT * FROM tenk1 2219 UNION ALL SELECT * FROM tenk1 2220 UNION ALL SELECT * FROM tenk1) u; 2221 variance | avg 2222----------------------+----------------------- 2223 8333541.588539713493 | 4999.5000000000000000 2224(1 row) 2225 2226ROLLBACK; 2227-- test coverage for dense_rank 2228SELECT dense_rank(x) WITHIN GROUP (ORDER BY x) FROM (VALUES (1),(1),(2),(2),(3),(3)) v(x) GROUP BY (x) ORDER BY 1; 2229 dense_rank 2230------------ 2231 1 2232 1 2233 1 2234(3 rows) 2235 2236-- Ensure that the STRICT checks for aggregates does not take NULLness 2237-- of ORDER BY columns into account. See bug report around 2238-- 2a505161-2727-2473-7c46-591ed108ac52@email.cz 2239SELECT min(x ORDER BY y) FROM (VALUES(1, NULL)) AS d(x,y); 2240 min 2241----- 2242 1 2243(1 row) 2244 2245SELECT min(x ORDER BY y) FROM (VALUES(1, 2)) AS d(x,y); 2246 min 2247----- 2248 1 2249(1 row) 2250 2251-- check collation-sensitive matching between grouping expressions 2252select v||'a', case v||'a' when 'aa' then 1 else 0 end, count(*) 2253 from unnest(array['a','b']) u(v) 2254 group by v||'a' order by 1; 2255 ?column? | case | count 2256----------+------+------- 2257 aa | 1 | 1 2258 ba | 0 | 1 2259(2 rows) 2260 2261select v||'a', case when v||'a' = 'aa' then 1 else 0 end, count(*) 2262 from unnest(array['a','b']) u(v) 2263 group by v||'a' order by 1; 2264 ?column? | case | count 2265----------+------+------- 2266 aa | 1 | 1 2267 ba | 0 | 1 2268(2 rows) 2269 2270-- Make sure that generation of HashAggregate for uniqification purposes 2271-- does not lead to array overflow due to unexpected duplicate hash keys 2272-- see CAFeeJoKKu0u+A_A9R9316djW-YW3-+Gtgvy3ju655qRHR3jtdA@mail.gmail.com 2273explain (costs off) 2274 select 1 from tenk1 2275 where (hundred, thousand) in (select twothousand, twothousand from onek); 2276 QUERY PLAN 2277------------------------------------------------------------- 2278 Hash Join 2279 Hash Cond: (tenk1.hundred = onek.twothousand) 2280 -> Seq Scan on tenk1 2281 Filter: (hundred = thousand) 2282 -> Hash 2283 -> HashAggregate 2284 Group Key: onek.twothousand, onek.twothousand 2285 -> Seq Scan on onek 2286(8 rows) 2287 2288