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 1041drop table t1 cascade; 1042NOTICE: drop cascades to table t1c 1043drop table t2; 1044drop table t3; 1045-- 1046-- Test combinations of DISTINCT and/or ORDER BY 1047-- 1048select array_agg(a order by b) 1049 from (values (1,4),(2,3),(3,1),(4,2)) v(a,b); 1050 array_agg 1051----------- 1052 {3,4,2,1} 1053(1 row) 1054 1055select array_agg(a order by a) 1056 from (values (1,4),(2,3),(3,1),(4,2)) v(a,b); 1057 array_agg 1058----------- 1059 {1,2,3,4} 1060(1 row) 1061 1062select array_agg(a order by a desc) 1063 from (values (1,4),(2,3),(3,1),(4,2)) v(a,b); 1064 array_agg 1065----------- 1066 {4,3,2,1} 1067(1 row) 1068 1069select array_agg(b order by a desc) 1070 from (values (1,4),(2,3),(3,1),(4,2)) v(a,b); 1071 array_agg 1072----------- 1073 {2,1,3,4} 1074(1 row) 1075 1076select array_agg(distinct a) 1077 from (values (1),(2),(1),(3),(null),(2)) v(a); 1078 array_agg 1079-------------- 1080 {1,2,3,NULL} 1081(1 row) 1082 1083select array_agg(distinct a order by a) 1084 from (values (1),(2),(1),(3),(null),(2)) v(a); 1085 array_agg 1086-------------- 1087 {1,2,3,NULL} 1088(1 row) 1089 1090select array_agg(distinct a order by a desc) 1091 from (values (1),(2),(1),(3),(null),(2)) v(a); 1092 array_agg 1093-------------- 1094 {NULL,3,2,1} 1095(1 row) 1096 1097select array_agg(distinct a order by a desc nulls last) 1098 from (values (1),(2),(1),(3),(null),(2)) v(a); 1099 array_agg 1100-------------- 1101 {3,2,1,NULL} 1102(1 row) 1103 1104-- multi-arg aggs, strict/nonstrict, distinct/order by 1105select aggfstr(a,b,c) 1106 from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c); 1107 aggfstr 1108--------------------------------------- 1109 {"(1,3,foo)","(2,2,bar)","(3,1,baz)"} 1110(1 row) 1111 1112select aggfns(a,b,c) 1113 from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c); 1114 aggfns 1115----------------------------------------------- 1116 {"(1,3,foo)","(0,,)","(2,2,bar)","(3,1,baz)"} 1117(1 row) 1118 1119select aggfstr(distinct a,b,c) 1120 from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c), 1121 generate_series(1,3) i; 1122 aggfstr 1123--------------------------------------- 1124 {"(1,3,foo)","(2,2,bar)","(3,1,baz)"} 1125(1 row) 1126 1127select aggfns(distinct a,b,c) 1128 from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c), 1129 generate_series(1,3) i; 1130 aggfns 1131----------------------------------------------- 1132 {"(0,,)","(1,3,foo)","(2,2,bar)","(3,1,baz)"} 1133(1 row) 1134 1135select aggfstr(distinct a,b,c order by b) 1136 from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c), 1137 generate_series(1,3) i; 1138 aggfstr 1139--------------------------------------- 1140 {"(3,1,baz)","(2,2,bar)","(1,3,foo)"} 1141(1 row) 1142 1143select aggfns(distinct a,b,c order by b) 1144 from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c), 1145 generate_series(1,3) i; 1146 aggfns 1147----------------------------------------------- 1148 {"(3,1,baz)","(2,2,bar)","(1,3,foo)","(0,,)"} 1149(1 row) 1150 1151-- test specific code paths 1152select aggfns(distinct a,a,c order by c using ~<~,a) 1153 from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c), 1154 generate_series(1,2) i; 1155 aggfns 1156------------------------------------------------ 1157 {"(2,2,bar)","(3,3,baz)","(1,1,foo)","(0,0,)"} 1158(1 row) 1159 1160select aggfns(distinct a,a,c order by c using ~<~) 1161 from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c), 1162 generate_series(1,2) i; 1163 aggfns 1164------------------------------------------------ 1165 {"(2,2,bar)","(3,3,baz)","(1,1,foo)","(0,0,)"} 1166(1 row) 1167 1168select aggfns(distinct a,a,c order by a) 1169 from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c), 1170 generate_series(1,2) i; 1171 aggfns 1172------------------------------------------------ 1173 {"(0,0,)","(1,1,foo)","(2,2,bar)","(3,3,baz)"} 1174(1 row) 1175 1176select aggfns(distinct a,b,c order by a,c using ~<~,b) 1177 from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c), 1178 generate_series(1,2) i; 1179 aggfns 1180----------------------------------------------- 1181 {"(0,,)","(1,3,foo)","(2,2,bar)","(3,1,baz)"} 1182(1 row) 1183 1184-- check node I/O via view creation and usage, also deparsing logic 1185create view agg_view1 as 1186 select aggfns(a,b,c) 1187 from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c); 1188select * from agg_view1; 1189 aggfns 1190----------------------------------------------- 1191 {"(1,3,foo)","(0,,)","(2,2,bar)","(3,1,baz)"} 1192(1 row) 1193 1194select pg_get_viewdef('agg_view1'::regclass); 1195 pg_get_viewdef 1196--------------------------------------------------------------------------------------------------------------------- 1197 SELECT aggfns(v.a, v.b, v.c) AS aggfns + 1198 FROM ( VALUES (1,3,'foo'::text), (0,NULL::integer,NULL::text), (2,2,'bar'::text), (3,1,'baz'::text)) v(a, b, c); 1199(1 row) 1200 1201create or replace view agg_view1 as 1202 select aggfns(distinct a,b,c) 1203 from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c), 1204 generate_series(1,3) i; 1205select * from agg_view1; 1206 aggfns 1207----------------------------------------------- 1208 {"(0,,)","(1,3,foo)","(2,2,bar)","(3,1,baz)"} 1209(1 row) 1210 1211select pg_get_viewdef('agg_view1'::regclass); 1212 pg_get_viewdef 1213--------------------------------------------------------------------------------------------------------------------- 1214 SELECT aggfns(DISTINCT v.a, v.b, v.c) AS aggfns + 1215 FROM ( VALUES (1,3,'foo'::text), (0,NULL::integer,NULL::text), (2,2,'bar'::text), (3,1,'baz'::text)) v(a, b, c),+ 1216 generate_series(1, 3) i(i); 1217(1 row) 1218 1219create or replace view agg_view1 as 1220 select aggfns(distinct a,b,c order by b) 1221 from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c), 1222 generate_series(1,3) i; 1223select * from agg_view1; 1224 aggfns 1225----------------------------------------------- 1226 {"(3,1,baz)","(2,2,bar)","(1,3,foo)","(0,,)"} 1227(1 row) 1228 1229select pg_get_viewdef('agg_view1'::regclass); 1230 pg_get_viewdef 1231--------------------------------------------------------------------------------------------------------------------- 1232 SELECT aggfns(DISTINCT v.a, v.b, v.c ORDER BY v.b) AS aggfns + 1233 FROM ( VALUES (1,3,'foo'::text), (0,NULL::integer,NULL::text), (2,2,'bar'::text), (3,1,'baz'::text)) v(a, b, c),+ 1234 generate_series(1, 3) i(i); 1235(1 row) 1236 1237create or replace view agg_view1 as 1238 select aggfns(a,b,c order by b+1) 1239 from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c); 1240select * from agg_view1; 1241 aggfns 1242----------------------------------------------- 1243 {"(3,1,baz)","(2,2,bar)","(1,3,foo)","(0,,)"} 1244(1 row) 1245 1246select pg_get_viewdef('agg_view1'::regclass); 1247 pg_get_viewdef 1248--------------------------------------------------------------------------------------------------------------------- 1249 SELECT aggfns(v.a, v.b, v.c ORDER BY (v.b + 1)) AS aggfns + 1250 FROM ( VALUES (1,3,'foo'::text), (0,NULL::integer,NULL::text), (2,2,'bar'::text), (3,1,'baz'::text)) v(a, b, c); 1251(1 row) 1252 1253create or replace view agg_view1 as 1254 select aggfns(a,a,c order by b) 1255 from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c); 1256select * from agg_view1; 1257 aggfns 1258------------------------------------------------ 1259 {"(3,3,baz)","(2,2,bar)","(1,1,foo)","(0,0,)"} 1260(1 row) 1261 1262select pg_get_viewdef('agg_view1'::regclass); 1263 pg_get_viewdef 1264--------------------------------------------------------------------------------------------------------------------- 1265 SELECT aggfns(v.a, v.a, v.c ORDER BY v.b) AS aggfns + 1266 FROM ( VALUES (1,3,'foo'::text), (0,NULL::integer,NULL::text), (2,2,'bar'::text), (3,1,'baz'::text)) v(a, b, c); 1267(1 row) 1268 1269create or replace view agg_view1 as 1270 select aggfns(a,b,c order by c using ~<~) 1271 from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c); 1272select * from agg_view1; 1273 aggfns 1274----------------------------------------------- 1275 {"(2,2,bar)","(3,1,baz)","(1,3,foo)","(0,,)"} 1276(1 row) 1277 1278select pg_get_viewdef('agg_view1'::regclass); 1279 pg_get_viewdef 1280--------------------------------------------------------------------------------------------------------------------- 1281 SELECT aggfns(v.a, v.b, v.c ORDER BY v.c USING ~<~ NULLS LAST) AS aggfns + 1282 FROM ( VALUES (1,3,'foo'::text), (0,NULL::integer,NULL::text), (2,2,'bar'::text), (3,1,'baz'::text)) v(a, b, c); 1283(1 row) 1284 1285create or replace view agg_view1 as 1286 select aggfns(distinct a,b,c order by a,c using ~<~,b) 1287 from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c), 1288 generate_series(1,2) i; 1289select * from agg_view1; 1290 aggfns 1291----------------------------------------------- 1292 {"(0,,)","(1,3,foo)","(2,2,bar)","(3,1,baz)"} 1293(1 row) 1294 1295select pg_get_viewdef('agg_view1'::regclass); 1296 pg_get_viewdef 1297--------------------------------------------------------------------------------------------------------------------- 1298 SELECT aggfns(DISTINCT v.a, v.b, v.c ORDER BY v.a, v.c USING ~<~ NULLS LAST, v.b) AS aggfns + 1299 FROM ( VALUES (1,3,'foo'::text), (0,NULL::integer,NULL::text), (2,2,'bar'::text), (3,1,'baz'::text)) v(a, b, c),+ 1300 generate_series(1, 2) i(i); 1301(1 row) 1302 1303drop view agg_view1; 1304-- incorrect DISTINCT usage errors 1305select aggfns(distinct a,b,c order by i) 1306 from (values (1,1,'foo')) v(a,b,c), generate_series(1,2) i; 1307ERROR: in an aggregate with DISTINCT, ORDER BY expressions must appear in argument list 1308LINE 1: select aggfns(distinct a,b,c order by i) 1309 ^ 1310select aggfns(distinct a,b,c order by a,b+1) 1311 from (values (1,1,'foo')) v(a,b,c), generate_series(1,2) i; 1312ERROR: in an aggregate with DISTINCT, ORDER BY expressions must appear in argument list 1313LINE 1: select aggfns(distinct a,b,c order by a,b+1) 1314 ^ 1315select aggfns(distinct a,b,c order by a,b,i,c) 1316 from (values (1,1,'foo')) v(a,b,c), generate_series(1,2) i; 1317ERROR: in an aggregate with DISTINCT, ORDER BY expressions must appear in argument list 1318LINE 1: select aggfns(distinct a,b,c order by a,b,i,c) 1319 ^ 1320select aggfns(distinct a,a,c order by a,b) 1321 from (values (1,1,'foo')) v(a,b,c), generate_series(1,2) i; 1322ERROR: in an aggregate with DISTINCT, ORDER BY expressions must appear in argument list 1323LINE 1: select aggfns(distinct a,a,c order by a,b) 1324 ^ 1325-- string_agg tests 1326select string_agg(a,',') from (values('aaaa'),('bbbb'),('cccc')) g(a); 1327 string_agg 1328---------------- 1329 aaaa,bbbb,cccc 1330(1 row) 1331 1332select string_agg(a,',') from (values('aaaa'),(null),('bbbb'),('cccc')) g(a); 1333 string_agg 1334---------------- 1335 aaaa,bbbb,cccc 1336(1 row) 1337 1338select string_agg(a,'AB') from (values(null),(null),('bbbb'),('cccc')) g(a); 1339 string_agg 1340------------ 1341 bbbbABcccc 1342(1 row) 1343 1344select string_agg(a,',') from (values(null),(null)) g(a); 1345 string_agg 1346------------ 1347 1348(1 row) 1349 1350-- check some implicit casting cases, as per bug #5564 1351select string_agg(distinct f1, ',' order by f1) from varchar_tbl; -- ok 1352 string_agg 1353------------ 1354 a,ab,abcd 1355(1 row) 1356 1357select string_agg(distinct f1::text, ',' order by f1) from varchar_tbl; -- not ok 1358ERROR: in an aggregate with DISTINCT, ORDER BY expressions must appear in argument list 1359LINE 1: select string_agg(distinct f1::text, ',' order by f1) from v... 1360 ^ 1361select string_agg(distinct f1, ',' order by f1::text) from varchar_tbl; -- not ok 1362ERROR: in an aggregate with DISTINCT, ORDER BY expressions must appear in argument list 1363LINE 1: select string_agg(distinct f1, ',' order by f1::text) from v... 1364 ^ 1365select string_agg(distinct f1::text, ',' order by f1::text) from varchar_tbl; -- ok 1366 string_agg 1367------------ 1368 a,ab,abcd 1369(1 row) 1370 1371-- string_agg bytea tests 1372create table bytea_test_table(v bytea); 1373select string_agg(v, '') from bytea_test_table; 1374 string_agg 1375------------ 1376 1377(1 row) 1378 1379insert into bytea_test_table values(decode('ff','hex')); 1380select string_agg(v, '') from bytea_test_table; 1381 string_agg 1382------------ 1383 \xff 1384(1 row) 1385 1386insert into bytea_test_table values(decode('aa','hex')); 1387select string_agg(v, '') from bytea_test_table; 1388 string_agg 1389------------ 1390 \xffaa 1391(1 row) 1392 1393select string_agg(v, NULL) from bytea_test_table; 1394 string_agg 1395------------ 1396 \xffaa 1397(1 row) 1398 1399select string_agg(v, decode('ee', 'hex')) from bytea_test_table; 1400 string_agg 1401------------ 1402 \xffeeaa 1403(1 row) 1404 1405drop table bytea_test_table; 1406-- FILTER tests 1407select min(unique1) filter (where unique1 > 100) from tenk1; 1408 min 1409----- 1410 101 1411(1 row) 1412 1413select sum(1/ten) filter (where ten > 0) from tenk1; 1414 sum 1415------ 1416 1000 1417(1 row) 1418 1419select ten, sum(distinct four) filter (where four::text ~ '123') from onek a 1420group by ten; 1421 ten | sum 1422-----+----- 1423 0 | 1424 1 | 1425 2 | 1426 3 | 1427 4 | 1428 5 | 1429 6 | 1430 7 | 1431 8 | 1432 9 | 1433(10 rows) 1434 1435select ten, sum(distinct four) filter (where four > 10) from onek a 1436group by ten 1437having exists (select 1 from onek b where sum(distinct a.four) = b.four); 1438 ten | sum 1439-----+----- 1440 0 | 1441 2 | 1442 4 | 1443 6 | 1444 8 | 1445(5 rows) 1446 1447select max(foo COLLATE "C") filter (where (bar collate "POSIX") > '0') 1448from (values ('a', 'b')) AS v(foo,bar); 1449 max 1450----- 1451 a 1452(1 row) 1453 1454-- outer reference in FILTER (PostgreSQL extension) 1455select (select count(*) 1456 from (values (1)) t0(inner_c)) 1457from (values (2),(3)) t1(outer_c); -- inner query is aggregation query 1458 count 1459------- 1460 1 1461 1 1462(2 rows) 1463 1464select (select count(*) filter (where outer_c <> 0) 1465 from (values (1)) t0(inner_c)) 1466from (values (2),(3)) t1(outer_c); -- outer query is aggregation query 1467 count 1468------- 1469 2 1470(1 row) 1471 1472select (select count(inner_c) filter (where outer_c <> 0) 1473 from (values (1)) t0(inner_c)) 1474from (values (2),(3)) t1(outer_c); -- inner query is aggregation query 1475 count 1476------- 1477 1 1478 1 1479(2 rows) 1480 1481select 1482 (select max((select i.unique2 from tenk1 i where i.unique1 = o.unique1)) 1483 filter (where o.unique1 < 10)) 1484from tenk1 o; -- outer query is aggregation query 1485 max 1486------ 1487 9998 1488(1 row) 1489 1490-- subquery in FILTER clause (PostgreSQL extension) 1491select sum(unique1) FILTER (WHERE 1492 unique1 IN (SELECT unique1 FROM onek where unique1 < 100)) FROM tenk1; 1493 sum 1494------ 1495 4950 1496(1 row) 1497 1498-- exercise lots of aggregate parts with FILTER 1499select aggfns(distinct a,b,c order by a,c using ~<~,b) filter (where a > 1) 1500 from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c), 1501 generate_series(1,2) i; 1502 aggfns 1503--------------------------- 1504 {"(2,2,bar)","(3,1,baz)"} 1505(1 row) 1506 1507-- check handling of bare boolean Var in FILTER 1508select max(0) filter (where b1) from bool_test; 1509 max 1510----- 1511 0 1512(1 row) 1513 1514select (select max(0) filter (where b1)) from bool_test; 1515 max 1516----- 1517 0 1518(1 row) 1519 1520-- check for correct detection of nested-aggregate errors in FILTER 1521select max(unique1) filter (where sum(ten) > 0) from tenk1; 1522ERROR: aggregate functions are not allowed in FILTER 1523LINE 1: select max(unique1) filter (where sum(ten) > 0) from tenk1; 1524 ^ 1525select (select max(unique1) filter (where sum(ten) > 0) from int8_tbl) from tenk1; 1526ERROR: aggregate function calls cannot be nested 1527LINE 1: select (select max(unique1) filter (where sum(ten) > 0) from... 1528 ^ 1529select max(unique1) filter (where bool_or(ten > 0)) from tenk1; 1530ERROR: aggregate functions are not allowed in FILTER 1531LINE 1: select max(unique1) filter (where bool_or(ten > 0)) from ten... 1532 ^ 1533select (select max(unique1) filter (where bool_or(ten > 0)) from int8_tbl) from tenk1; 1534ERROR: aggregate function calls cannot be nested 1535LINE 1: select (select max(unique1) filter (where bool_or(ten > 0)) ... 1536 ^ 1537-- ordered-set aggregates 1538select p, percentile_cont(p) within group (order by x::float8) 1539from generate_series(1,5) x, 1540 (values (0::float8),(0.1),(0.25),(0.4),(0.5),(0.6),(0.75),(0.9),(1)) v(p) 1541group by p order by p; 1542 p | percentile_cont 1543------+----------------- 1544 0 | 1 1545 0.1 | 1.4 1546 0.25 | 2 1547 0.4 | 2.6 1548 0.5 | 3 1549 0.6 | 3.4 1550 0.75 | 4 1551 0.9 | 4.6 1552 1 | 5 1553(9 rows) 1554 1555select p, percentile_cont(p order by p) within group (order by x) -- error 1556from generate_series(1,5) x, 1557 (values (0::float8),(0.1),(0.25),(0.4),(0.5),(0.6),(0.75),(0.9),(1)) v(p) 1558group by p order by p; 1559ERROR: cannot use multiple ORDER BY clauses with WITHIN GROUP 1560LINE 1: select p, percentile_cont(p order by p) within group (order ... 1561 ^ 1562select p, sum() within group (order by x::float8) -- error 1563from generate_series(1,5) x, 1564 (values (0::float8),(0.1),(0.25),(0.4),(0.5),(0.6),(0.75),(0.9),(1)) v(p) 1565group by p order by p; 1566ERROR: sum is not an ordered-set aggregate, so it cannot have WITHIN GROUP 1567LINE 1: select p, sum() within group (order by x::float8) 1568 ^ 1569select p, percentile_cont(p,p) -- error 1570from generate_series(1,5) x, 1571 (values (0::float8),(0.1),(0.25),(0.4),(0.5),(0.6),(0.75),(0.9),(1)) v(p) 1572group by p order by p; 1573ERROR: WITHIN GROUP is required for ordered-set aggregate percentile_cont 1574LINE 1: select p, percentile_cont(p,p) 1575 ^ 1576select percentile_cont(0.5) within group (order by b) from aggtest; 1577 percentile_cont 1578------------------ 1579 53.4485001564026 1580(1 row) 1581 1582select percentile_cont(0.5) within group (order by b), sum(b) from aggtest; 1583 percentile_cont | sum 1584------------------+--------- 1585 53.4485001564026 | 431.773 1586(1 row) 1587 1588select percentile_cont(0.5) within group (order by thousand) from tenk1; 1589 percentile_cont 1590----------------- 1591 499.5 1592(1 row) 1593 1594select percentile_disc(0.5) within group (order by thousand) from tenk1; 1595 percentile_disc 1596----------------- 1597 499 1598(1 row) 1599 1600select rank(3) within group (order by x) 1601from (values (1),(1),(2),(2),(3),(3),(4)) v(x); 1602 rank 1603------ 1604 5 1605(1 row) 1606 1607select cume_dist(3) within group (order by x) 1608from (values (1),(1),(2),(2),(3),(3),(4)) v(x); 1609 cume_dist 1610----------- 1611 0.875 1612(1 row) 1613 1614select percent_rank(3) within group (order by x) 1615from (values (1),(1),(2),(2),(3),(3),(4),(5)) v(x); 1616 percent_rank 1617-------------- 1618 0.5 1619(1 row) 1620 1621select dense_rank(3) within group (order by x) 1622from (values (1),(1),(2),(2),(3),(3),(4)) v(x); 1623 dense_rank 1624------------ 1625 3 1626(1 row) 1627 1628select percentile_disc(array[0,0.1,0.25,0.5,0.75,0.9,1]) within group (order by thousand) 1629from tenk1; 1630 percentile_disc 1631---------------------------- 1632 {0,99,249,499,749,899,999} 1633(1 row) 1634 1635select percentile_cont(array[0,0.25,0.5,0.75,1]) within group (order by thousand) 1636from tenk1; 1637 percentile_cont 1638----------------------------- 1639 {0,249.75,499.5,749.25,999} 1640(1 row) 1641 1642select percentile_disc(array[[null,1,0.5],[0.75,0.25,null]]) within group (order by thousand) 1643from tenk1; 1644 percentile_disc 1645--------------------------------- 1646 {{NULL,999,499},{749,249,NULL}} 1647(1 row) 1648 1649select 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) 1650from generate_series(1,6) x; 1651 percentile_cont 1652------------------------------------------ 1653 {1,6,2.25,4.75,3.5,6,2.5,2.6,2.75,2.9,3} 1654(1 row) 1655 1656select ten, mode() within group (order by string4) from tenk1 group by ten; 1657 ten | mode 1658-----+-------- 1659 0 | HHHHxx 1660 1 | OOOOxx 1661 2 | VVVVxx 1662 3 | OOOOxx 1663 4 | HHHHxx 1664 5 | HHHHxx 1665 6 | OOOOxx 1666 7 | AAAAxx 1667 8 | VVVVxx 1668 9 | VVVVxx 1669(10 rows) 1670 1671select percentile_disc(array[0.25,0.5,0.75]) within group (order by x) 1672from unnest('{fred,jim,fred,jack,jill,fred,jill,jim,jim,sheila,jim,sheila}'::text[]) u(x); 1673 percentile_disc 1674----------------- 1675 {fred,jill,jim} 1676(1 row) 1677 1678-- check collation propagates up in suitable cases: 1679select pg_collation_for(percentile_disc(1) within group (order by x collate "POSIX")) 1680 from (values ('fred'),('jim')) v(x); 1681 pg_collation_for 1682------------------ 1683 "POSIX" 1684(1 row) 1685 1686-- ordered-set aggs created with CREATE AGGREGATE 1687select test_rank(3) within group (order by x) 1688from (values (1),(1),(2),(2),(3),(3),(4)) v(x); 1689 test_rank 1690----------- 1691 5 1692(1 row) 1693 1694select test_percentile_disc(0.5) within group (order by thousand) from tenk1; 1695 test_percentile_disc 1696---------------------- 1697 499 1698(1 row) 1699 1700-- ordered-set aggs can't use ungrouped vars in direct args: 1701select rank(x) within group (order by x) from generate_series(1,5) x; 1702ERROR: column "x.x" must appear in the GROUP BY clause or be used in an aggregate function 1703LINE 1: select rank(x) within group (order by x) from generate_serie... 1704 ^ 1705DETAIL: Direct arguments of an ordered-set aggregate must use only grouped columns. 1706-- outer-level agg can't use a grouped arg of a lower level, either: 1707select array(select percentile_disc(a) within group (order by x) 1708 from (values (0.3),(0.7)) v(a) group by a) 1709 from generate_series(1,5) g(x); 1710ERROR: outer-level aggregate cannot contain a lower-level variable in its direct arguments 1711LINE 1: select array(select percentile_disc(a) within group (order b... 1712 ^ 1713-- agg in the direct args is a grouping violation, too: 1714select rank(sum(x)) within group (order by x) from generate_series(1,5) x; 1715ERROR: aggregate function calls cannot be nested 1716LINE 1: select rank(sum(x)) within group (order by x) from generate_... 1717 ^ 1718-- hypothetical-set type unification and argument-count failures: 1719select rank(3) within group (order by x) from (values ('fred'),('jim')) v(x); 1720ERROR: WITHIN GROUP types text and integer cannot be matched 1721LINE 1: select rank(3) within group (order by x) from (values ('fred... 1722 ^ 1723select rank(3) within group (order by stringu1,stringu2) from tenk1; 1724ERROR: function rank(integer, name, name) does not exist 1725LINE 1: select rank(3) within group (order by stringu1,stringu2) fro... 1726 ^ 1727HINT: To use the hypothetical-set aggregate rank, the number of hypothetical direct arguments (here 1) must match the number of ordering columns (here 2). 1728select rank('fred') within group (order by x) from generate_series(1,5) x; 1729ERROR: invalid input syntax for integer: "fred" 1730LINE 1: select rank('fred') within group (order by x) from generate_... 1731 ^ 1732select rank('adam'::text collate "C") within group (order by x collate "POSIX") 1733 from (values ('fred'),('jim')) v(x); 1734ERROR: collation mismatch between explicit collations "C" and "POSIX" 1735LINE 1: ...adam'::text collate "C") within group (order by x collate "P... 1736 ^ 1737-- hypothetical-set type unification successes: 1738select rank('adam'::varchar) within group (order by x) from (values ('fred'),('jim')) v(x); 1739 rank 1740------ 1741 1 1742(1 row) 1743 1744select rank('3') within group (order by x) from generate_series(1,5) x; 1745 rank 1746------ 1747 3 1748(1 row) 1749 1750-- divide by zero check 1751select percent_rank(0) within group (order by x) from generate_series(1,0) x; 1752 percent_rank 1753-------------- 1754 0 1755(1 row) 1756 1757-- deparse and multiple features: 1758create view aggordview1 as 1759select ten, 1760 percentile_disc(0.5) within group (order by thousand) as p50, 1761 percentile_disc(0.5) within group (order by thousand) filter (where hundred=1) as px, 1762 rank(5,'AZZZZ',50) within group (order by hundred, string4 desc, hundred) 1763 from tenk1 1764 group by ten order by ten; 1765select pg_get_viewdef('aggordview1'); 1766 pg_get_viewdef 1767------------------------------------------------------------------------------------------------------------------------------- 1768 SELECT tenk1.ten, + 1769 percentile_disc((0.5)::double precision) WITHIN GROUP (ORDER BY tenk1.thousand) AS p50, + 1770 percentile_disc((0.5)::double precision) WITHIN GROUP (ORDER BY tenk1.thousand) FILTER (WHERE (tenk1.hundred = 1)) AS px,+ 1771 rank(5, 'AZZZZ'::name, 50) WITHIN GROUP (ORDER BY tenk1.hundred, tenk1.string4 DESC, tenk1.hundred) AS rank + 1772 FROM tenk1 + 1773 GROUP BY tenk1.ten + 1774 ORDER BY tenk1.ten; 1775(1 row) 1776 1777select * from aggordview1 order by ten; 1778 ten | p50 | px | rank 1779-----+-----+-----+------ 1780 0 | 490 | | 101 1781 1 | 491 | 401 | 101 1782 2 | 492 | | 101 1783 3 | 493 | | 101 1784 4 | 494 | | 101 1785 5 | 495 | | 67 1786 6 | 496 | | 1 1787 7 | 497 | | 1 1788 8 | 498 | | 1 1789 9 | 499 | | 1 1790(10 rows) 1791 1792drop view aggordview1; 1793-- variadic aggregates 1794select least_agg(q1,q2) from int8_tbl; 1795 least_agg 1796------------------- 1797 -4567890123456789 1798(1 row) 1799 1800select least_agg(variadic array[q1,q2]) from int8_tbl; 1801 least_agg 1802------------------- 1803 -4567890123456789 1804(1 row) 1805 1806-- test aggregates with common transition functions share the same states 1807begin work; 1808create type avg_state as (total bigint, count bigint); 1809create or replace function avg_transfn(state avg_state, n int) returns avg_state as 1810$$ 1811declare new_state avg_state; 1812begin 1813 raise notice 'avg_transfn called with %', n; 1814 if state is null then 1815 if n is not null then 1816 new_state.total := n; 1817 new_state.count := 1; 1818 return new_state; 1819 end if; 1820 return null; 1821 elsif n is not null then 1822 state.total := state.total + n; 1823 state.count := state.count + 1; 1824 return state; 1825 end if; 1826 1827 return null; 1828end 1829$$ language plpgsql; 1830create function avg_finalfn(state avg_state) returns int4 as 1831$$ 1832begin 1833 if state is null then 1834 return NULL; 1835 else 1836 return state.total / state.count; 1837 end if; 1838end 1839$$ language plpgsql; 1840create function sum_finalfn(state avg_state) returns int4 as 1841$$ 1842begin 1843 if state is null then 1844 return NULL; 1845 else 1846 return state.total; 1847 end if; 1848end 1849$$ language plpgsql; 1850create aggregate my_avg(int4) 1851( 1852 stype = avg_state, 1853 sfunc = avg_transfn, 1854 finalfunc = avg_finalfn 1855); 1856create aggregate my_sum(int4) 1857( 1858 stype = avg_state, 1859 sfunc = avg_transfn, 1860 finalfunc = sum_finalfn 1861); 1862-- aggregate state should be shared as aggs are the same. 1863select my_avg(one),my_avg(one) from (values(1),(3)) t(one); 1864NOTICE: avg_transfn called with 1 1865NOTICE: avg_transfn called with 3 1866 my_avg | my_avg 1867--------+-------- 1868 2 | 2 1869(1 row) 1870 1871-- aggregate state should be shared as transfn is the same for both aggs. 1872select my_avg(one),my_sum(one) from (values(1),(3)) t(one); 1873NOTICE: avg_transfn called with 1 1874NOTICE: avg_transfn called with 3 1875 my_avg | my_sum 1876--------+-------- 1877 2 | 4 1878(1 row) 1879 1880-- same as previous one, but with DISTINCT, which requires sorting the input. 1881select my_avg(distinct one),my_sum(distinct one) from (values(1),(3),(1)) t(one); 1882NOTICE: avg_transfn called with 1 1883NOTICE: avg_transfn called with 3 1884 my_avg | my_sum 1885--------+-------- 1886 2 | 4 1887(1 row) 1888 1889-- shouldn't share states due to the distinctness not matching. 1890select my_avg(distinct one),my_sum(one) from (values(1),(3)) t(one); 1891NOTICE: avg_transfn called with 1 1892NOTICE: avg_transfn called with 3 1893NOTICE: avg_transfn called with 1 1894NOTICE: avg_transfn called with 3 1895 my_avg | my_sum 1896--------+-------- 1897 2 | 4 1898(1 row) 1899 1900-- shouldn't share states due to the filter clause not matching. 1901select my_avg(one) filter (where one > 1),my_sum(one) from (values(1),(3)) t(one); 1902NOTICE: avg_transfn called with 1 1903NOTICE: avg_transfn called with 3 1904NOTICE: avg_transfn called with 3 1905 my_avg | my_sum 1906--------+-------- 1907 3 | 4 1908(1 row) 1909 1910-- this should not share the state due to different input columns. 1911select my_avg(one),my_sum(two) from (values(1,2),(3,4)) t(one,two); 1912NOTICE: avg_transfn called with 2 1913NOTICE: avg_transfn called with 1 1914NOTICE: avg_transfn called with 4 1915NOTICE: avg_transfn called with 3 1916 my_avg | my_sum 1917--------+-------- 1918 2 | 6 1919(1 row) 1920 1921-- ideally these would share state, but we have to fix the OSAs first. 1922select 1923 percentile_cont(0.5) within group (order by a), 1924 percentile_disc(0.5) within group (order by a) 1925from (values(1::float8),(3),(5),(7)) t(a); 1926 percentile_cont | percentile_disc 1927-----------------+----------------- 1928 4 | 3 1929(1 row) 1930 1931select 1932 rank(4) within group (order by a), 1933 dense_rank(4) within group (order by a) 1934from (values(1),(3),(5),(7)) t(a); 1935 rank | dense_rank 1936------+------------ 1937 3 | 3 1938(1 row) 1939 1940-- test that aggs with the same sfunc and initcond share the same agg state 1941create aggregate my_sum_init(int4) 1942( 1943 stype = avg_state, 1944 sfunc = avg_transfn, 1945 finalfunc = sum_finalfn, 1946 initcond = '(10,0)' 1947); 1948create aggregate my_avg_init(int4) 1949( 1950 stype = avg_state, 1951 sfunc = avg_transfn, 1952 finalfunc = avg_finalfn, 1953 initcond = '(10,0)' 1954); 1955create aggregate my_avg_init2(int4) 1956( 1957 stype = avg_state, 1958 sfunc = avg_transfn, 1959 finalfunc = avg_finalfn, 1960 initcond = '(4,0)' 1961); 1962-- state should be shared if INITCONDs are matching 1963select my_sum_init(one),my_avg_init(one) from (values(1),(3)) t(one); 1964NOTICE: avg_transfn called with 1 1965NOTICE: avg_transfn called with 3 1966 my_sum_init | my_avg_init 1967-------------+------------- 1968 14 | 7 1969(1 row) 1970 1971-- Varying INITCONDs should cause the states not to be shared. 1972select my_sum_init(one),my_avg_init2(one) from (values(1),(3)) t(one); 1973NOTICE: avg_transfn called with 1 1974NOTICE: avg_transfn called with 1 1975NOTICE: avg_transfn called with 3 1976NOTICE: avg_transfn called with 3 1977 my_sum_init | my_avg_init2 1978-------------+-------------- 1979 14 | 4 1980(1 row) 1981 1982rollback; 1983-- test aggregate state sharing to ensure it works if one aggregate has a 1984-- finalfn and the other one has none. 1985begin work; 1986create or replace function sum_transfn(state int4, n int4) returns int4 as 1987$$ 1988declare new_state int4; 1989begin 1990 raise notice 'sum_transfn called with %', n; 1991 if state is null then 1992 if n is not null then 1993 new_state := n; 1994 return new_state; 1995 end if; 1996 return null; 1997 elsif n is not null then 1998 state := state + n; 1999 return state; 2000 end if; 2001 2002 return null; 2003end 2004$$ language plpgsql; 2005create function halfsum_finalfn(state int4) returns int4 as 2006$$ 2007begin 2008 if state is null then 2009 return NULL; 2010 else 2011 return state / 2; 2012 end if; 2013end 2014$$ language plpgsql; 2015create aggregate my_sum(int4) 2016( 2017 stype = int4, 2018 sfunc = sum_transfn 2019); 2020create aggregate my_half_sum(int4) 2021( 2022 stype = int4, 2023 sfunc = sum_transfn, 2024 finalfunc = halfsum_finalfn 2025); 2026-- Agg state should be shared even though my_sum has no finalfn 2027select my_sum(one),my_half_sum(one) from (values(1),(2),(3),(4)) t(one); 2028NOTICE: sum_transfn called with 1 2029NOTICE: sum_transfn called with 2 2030NOTICE: sum_transfn called with 3 2031NOTICE: sum_transfn called with 4 2032 my_sum | my_half_sum 2033--------+------------- 2034 10 | 5 2035(1 row) 2036 2037rollback; 2038-- test that the aggregate transition logic correctly handles 2039-- transition / combine functions returning NULL 2040-- First test the case of a normal transition function returning NULL 2041BEGIN; 2042CREATE FUNCTION balkifnull(int8, int4) 2043RETURNS int8 2044STRICT 2045LANGUAGE plpgsql AS $$ 2046BEGIN 2047 IF $1 IS NULL THEN 2048 RAISE 'erroneously called with NULL argument'; 2049 END IF; 2050 RETURN NULL; 2051END$$; 2052CREATE AGGREGATE balk( 2053 BASETYPE = int4, 2054 SFUNC = balkifnull(int8, int4), 2055 STYPE = int8, 2056 "PARALLEL" = SAFE, 2057 INITCOND = '0'); 2058SELECT balk(hundred) FROM tenk1; 2059 balk 2060------ 2061 2062(1 row) 2063 2064ROLLBACK; 2065-- Secondly test the case of a parallel aggregate combiner function 2066-- returning NULL. For that use normal transition function, but a 2067-- combiner function returning NULL. 2068BEGIN ISOLATION LEVEL REPEATABLE READ; 2069CREATE FUNCTION balkifnull(int8, int8) 2070RETURNS int8 2071PARALLEL SAFE 2072STRICT 2073LANGUAGE plpgsql AS $$ 2074BEGIN 2075 IF $1 IS NULL THEN 2076 RAISE 'erroneously called with NULL argument'; 2077 END IF; 2078 RETURN NULL; 2079END$$; 2080CREATE AGGREGATE balk( 2081 BASETYPE = int4, 2082 SFUNC = int4_sum(int8, int4), 2083 STYPE = int8, 2084 COMBINEFUNC = balkifnull(int8, int8), 2085 "PARALLEL" = SAFE, 2086 INITCOND = '0' 2087); 2088-- force use of parallelism 2089ALTER TABLE tenk1 set (parallel_workers = 4); 2090SET LOCAL parallel_setup_cost=0; 2091SET LOCAL max_parallel_workers_per_gather=4; 2092EXPLAIN (COSTS OFF) SELECT balk(hundred) FROM tenk1; 2093 QUERY PLAN 2094------------------------------------------------------------------------- 2095 Finalize Aggregate 2096 -> Gather 2097 Workers Planned: 4 2098 -> Partial Aggregate 2099 -> Parallel Index Only Scan using tenk1_hundred on tenk1 2100(5 rows) 2101 2102SELECT balk(hundred) FROM tenk1; 2103 balk 2104------ 2105 2106(1 row) 2107 2108ROLLBACK; 2109-- test coverage for aggregate combine/serial/deserial functions 2110BEGIN ISOLATION LEVEL REPEATABLE READ; 2111SET parallel_setup_cost = 0; 2112SET parallel_tuple_cost = 0; 2113SET min_parallel_table_scan_size = 0; 2114SET max_parallel_workers_per_gather = 4; 2115SET enable_indexonlyscan = off; 2116-- variance(int4) covers numeric_poly_combine 2117-- sum(int8) covers int8_avg_combine 2118-- regr_count(float8, float8) covers int8inc_float8_float8 and aggregates with > 1 arg 2119EXPLAIN (COSTS OFF, VERBOSE) 2120SELECT variance(unique1::int4), sum(unique1::int8), regr_count(unique1::float8, unique1::float8) 2121FROM (SELECT * FROM tenk1 2122 UNION ALL SELECT * FROM tenk1 2123 UNION ALL SELECT * FROM tenk1 2124 UNION ALL SELECT * FROM tenk1) u; 2125 QUERY PLAN 2126--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 2127 Finalize Aggregate 2128 Output: variance(tenk1.unique1), sum((tenk1.unique1)::bigint), regr_count((tenk1.unique1)::double precision, (tenk1.unique1)::double precision) 2129 -> Gather 2130 Output: (PARTIAL variance(tenk1.unique1)), (PARTIAL sum((tenk1.unique1)::bigint)), (PARTIAL regr_count((tenk1.unique1)::double precision, (tenk1.unique1)::double precision)) 2131 Workers Planned: 4 2132 -> Partial Aggregate 2133 Output: PARTIAL variance(tenk1.unique1), PARTIAL sum((tenk1.unique1)::bigint), PARTIAL regr_count((tenk1.unique1)::double precision, (tenk1.unique1)::double precision) 2134 -> Append 2135 -> Parallel Seq Scan on public.tenk1 2136 Output: tenk1.unique1 2137 -> Parallel Seq Scan on public.tenk1 tenk1_1 2138 Output: tenk1_1.unique1 2139 -> Parallel Seq Scan on public.tenk1 tenk1_2 2140 Output: tenk1_2.unique1 2141 -> Parallel Seq Scan on public.tenk1 tenk1_3 2142 Output: tenk1_3.unique1 2143(16 rows) 2144 2145SELECT variance(unique1::int4), sum(unique1::int8), regr_count(unique1::float8, unique1::float8) 2146FROM (SELECT * FROM tenk1 2147 UNION ALL SELECT * FROM tenk1 2148 UNION ALL SELECT * FROM tenk1 2149 UNION ALL SELECT * FROM tenk1) u; 2150 variance | sum | regr_count 2151----------------------+-----------+------------ 2152 8333541.588539713493 | 199980000 | 40000 2153(1 row) 2154 2155-- variance(int8) covers numeric_combine 2156-- avg(numeric) covers numeric_avg_combine 2157EXPLAIN (COSTS OFF, VERBOSE) 2158SELECT variance(unique1::int8), avg(unique1::numeric) 2159FROM (SELECT * FROM tenk1 2160 UNION ALL SELECT * FROM tenk1 2161 UNION ALL SELECT * FROM tenk1 2162 UNION ALL SELECT * FROM tenk1) u; 2163 QUERY PLAN 2164-------------------------------------------------------------------------------------------------------- 2165 Finalize Aggregate 2166 Output: variance((tenk1.unique1)::bigint), avg((tenk1.unique1)::numeric) 2167 -> Gather 2168 Output: (PARTIAL variance((tenk1.unique1)::bigint)), (PARTIAL avg((tenk1.unique1)::numeric)) 2169 Workers Planned: 4 2170 -> Partial Aggregate 2171 Output: PARTIAL variance((tenk1.unique1)::bigint), PARTIAL avg((tenk1.unique1)::numeric) 2172 -> Append 2173 -> Parallel Seq Scan on public.tenk1 2174 Output: tenk1.unique1 2175 -> Parallel Seq Scan on public.tenk1 tenk1_1 2176 Output: tenk1_1.unique1 2177 -> Parallel Seq Scan on public.tenk1 tenk1_2 2178 Output: tenk1_2.unique1 2179 -> Parallel Seq Scan on public.tenk1 tenk1_3 2180 Output: tenk1_3.unique1 2181(16 rows) 2182 2183SELECT variance(unique1::int8), avg(unique1::numeric) 2184FROM (SELECT * FROM tenk1 2185 UNION ALL SELECT * FROM tenk1 2186 UNION ALL SELECT * FROM tenk1 2187 UNION ALL SELECT * FROM tenk1) u; 2188 variance | avg 2189----------------------+----------------------- 2190 8333541.588539713493 | 4999.5000000000000000 2191(1 row) 2192 2193ROLLBACK; 2194-- check collation-sensitive matching between grouping expressions 2195select v||'a', case v||'a' when 'aa' then 1 else 0 end, count(*) 2196 from unnest(array['a','b']) u(v) 2197 group by v||'a' order by 1; 2198 ?column? | case | count 2199----------+------+------- 2200 aa | 1 | 1 2201 ba | 0 | 1 2202(2 rows) 2203 2204select v||'a', case when v||'a' = 'aa' then 1 else 0 end, count(*) 2205 from unnest(array['a','b']) u(v) 2206 group by v||'a' order by 1; 2207 ?column? | case | count 2208----------+------+------- 2209 aa | 1 | 1 2210 ba | 0 | 1 2211(2 rows) 2212 2213-- Make sure that generation of HashAggregate for uniqification purposes 2214-- does not lead to array overflow due to unexpected duplicate hash keys 2215-- see CAFeeJoKKu0u+A_A9R9316djW-YW3-+Gtgvy3ju655qRHR3jtdA@mail.gmail.com 2216explain (costs off) 2217 select 1 from tenk1 2218 where (hundred, thousand) in (select twothousand, twothousand from onek); 2219 QUERY PLAN 2220------------------------------------------------------------- 2221 Hash Join 2222 Hash Cond: (tenk1.hundred = onek.twothousand) 2223 -> Seq Scan on tenk1 2224 Filter: (hundred = thousand) 2225 -> Hash 2226 -> HashAggregate 2227 Group Key: onek.twothousand, onek.twothousand 2228 -> Seq Scan on onek 2229(8 rows) 2230 2231