1-- 2-- grouping sets 3-- 4-- test data sources 5create temp view gstest1(a,b,v) 6 as values (1,1,10),(1,1,11),(1,2,12),(1,2,13),(1,3,14), 7 (2,3,15), 8 (3,3,16),(3,4,17), 9 (4,1,18),(4,1,19); 10create temp table gstest2 (a integer, b integer, c integer, d integer, 11 e integer, f integer, g integer, h integer); 12copy gstest2 from stdin; 13create temp table gstest3 (a integer, b integer, c integer, d integer); 14copy gstest3 from stdin; 15alter table gstest3 add primary key (a); 16create temp table gstest4(id integer, v integer, 17 unhashable_col bit(4), unsortable_col xid); 18insert into gstest4 19values (1,1,b'0000','1'), (2,2,b'0001','1'), 20 (3,4,b'0010','2'), (4,8,b'0011','2'), 21 (5,16,b'0000','2'), (6,32,b'0001','2'), 22 (7,64,b'0010','1'), (8,128,b'0011','1'); 23create temp table gstest_empty (a integer, b integer, v integer); 24create function gstest_data(v integer, out a integer, out b integer) 25 returns setof record 26 as $f$ 27 begin 28 return query select v, i from generate_series(1,3) i; 29 end; 30 $f$ language plpgsql; 31-- basic functionality 32set enable_hashagg = false; -- test hashing explicitly later 33-- simple rollup with multiple plain aggregates, with and without ordering 34-- (and with ordering differing from grouping) 35select a, b, grouping(a,b), sum(v), count(*), max(v) 36 from gstest1 group by rollup (a,b); 37 a | b | grouping | sum | count | max 38---+---+----------+-----+-------+----- 39 1 | 1 | 0 | 21 | 2 | 11 40 1 | 2 | 0 | 25 | 2 | 13 41 1 | 3 | 0 | 14 | 1 | 14 42 1 | | 1 | 60 | 5 | 14 43 2 | 3 | 0 | 15 | 1 | 15 44 2 | | 1 | 15 | 1 | 15 45 3 | 3 | 0 | 16 | 1 | 16 46 3 | 4 | 0 | 17 | 1 | 17 47 3 | | 1 | 33 | 2 | 17 48 4 | 1 | 0 | 37 | 2 | 19 49 4 | | 1 | 37 | 2 | 19 50 | | 3 | 145 | 10 | 19 51(12 rows) 52 53select a, b, grouping(a,b), sum(v), count(*), max(v) 54 from gstest1 group by rollup (a,b) order by a,b; 55 a | b | grouping | sum | count | max 56---+---+----------+-----+-------+----- 57 1 | 1 | 0 | 21 | 2 | 11 58 1 | 2 | 0 | 25 | 2 | 13 59 1 | 3 | 0 | 14 | 1 | 14 60 1 | | 1 | 60 | 5 | 14 61 2 | 3 | 0 | 15 | 1 | 15 62 2 | | 1 | 15 | 1 | 15 63 3 | 3 | 0 | 16 | 1 | 16 64 3 | 4 | 0 | 17 | 1 | 17 65 3 | | 1 | 33 | 2 | 17 66 4 | 1 | 0 | 37 | 2 | 19 67 4 | | 1 | 37 | 2 | 19 68 | | 3 | 145 | 10 | 19 69(12 rows) 70 71select a, b, grouping(a,b), sum(v), count(*), max(v) 72 from gstest1 group by rollup (a,b) order by b desc, a; 73 a | b | grouping | sum | count | max 74---+---+----------+-----+-------+----- 75 1 | | 1 | 60 | 5 | 14 76 2 | | 1 | 15 | 1 | 15 77 3 | | 1 | 33 | 2 | 17 78 4 | | 1 | 37 | 2 | 19 79 | | 3 | 145 | 10 | 19 80 3 | 4 | 0 | 17 | 1 | 17 81 1 | 3 | 0 | 14 | 1 | 14 82 2 | 3 | 0 | 15 | 1 | 15 83 3 | 3 | 0 | 16 | 1 | 16 84 1 | 2 | 0 | 25 | 2 | 13 85 1 | 1 | 0 | 21 | 2 | 11 86 4 | 1 | 0 | 37 | 2 | 19 87(12 rows) 88 89select a, b, grouping(a,b), sum(v), count(*), max(v) 90 from gstest1 group by rollup (a,b) order by coalesce(a,0)+coalesce(b,0); 91 a | b | grouping | sum | count | max 92---+---+----------+-----+-------+----- 93 | | 3 | 145 | 10 | 19 94 1 | | 1 | 60 | 5 | 14 95 1 | 1 | 0 | 21 | 2 | 11 96 2 | | 1 | 15 | 1 | 15 97 3 | | 1 | 33 | 2 | 17 98 1 | 2 | 0 | 25 | 2 | 13 99 1 | 3 | 0 | 14 | 1 | 14 100 4 | | 1 | 37 | 2 | 19 101 4 | 1 | 0 | 37 | 2 | 19 102 2 | 3 | 0 | 15 | 1 | 15 103 3 | 3 | 0 | 16 | 1 | 16 104 3 | 4 | 0 | 17 | 1 | 17 105(12 rows) 106 107-- various types of ordered aggs 108select a, b, grouping(a,b), 109 array_agg(v order by v), 110 string_agg(v::text, ':' order by v desc), 111 percentile_disc(0.5) within group (order by v), 112 rank(1,2,12) within group (order by a,b,v) 113 from gstest1 group by rollup (a,b) order by a,b; 114 a | b | grouping | array_agg | string_agg | percentile_disc | rank 115---+---+----------+---------------------------------+-------------------------------+-----------------+------ 116 1 | 1 | 0 | {10,11} | 11:10 | 10 | 3 117 1 | 2 | 0 | {12,13} | 13:12 | 12 | 1 118 1 | 3 | 0 | {14} | 14 | 14 | 1 119 1 | | 1 | {10,11,12,13,14} | 14:13:12:11:10 | 12 | 3 120 2 | 3 | 0 | {15} | 15 | 15 | 1 121 2 | | 1 | {15} | 15 | 15 | 1 122 3 | 3 | 0 | {16} | 16 | 16 | 1 123 3 | 4 | 0 | {17} | 17 | 17 | 1 124 3 | | 1 | {16,17} | 17:16 | 16 | 1 125 4 | 1 | 0 | {18,19} | 19:18 | 18 | 1 126 4 | | 1 | {18,19} | 19:18 | 18 | 1 127 | | 3 | {10,11,12,13,14,15,16,17,18,19} | 19:18:17:16:15:14:13:12:11:10 | 14 | 3 128(12 rows) 129 130-- test usage of grouped columns in direct args of aggs 131select grouping(a), a, array_agg(b), 132 rank(a) within group (order by b nulls first), 133 rank(a) within group (order by b nulls last) 134 from (values (1,1),(1,4),(1,5),(3,1),(3,2)) v(a,b) 135 group by rollup (a) order by a; 136 grouping | a | array_agg | rank | rank 137----------+---+-------------+------+------ 138 0 | 1 | {1,4,5} | 1 | 1 139 0 | 3 | {1,2} | 3 | 3 140 1 | | {1,4,5,1,2} | 1 | 6 141(3 rows) 142 143-- nesting with window functions 144select a, b, sum(c), sum(sum(c)) over (order by a,b) as rsum 145 from gstest2 group by rollup (a,b) order by rsum, a, b; 146 a | b | sum | rsum 147---+---+-----+------ 148 1 | 1 | 8 | 8 149 1 | 2 | 2 | 10 150 1 | | 10 | 20 151 2 | 2 | 2 | 22 152 2 | | 2 | 24 153 | | 12 | 36 154(6 rows) 155 156-- nesting with grouping sets 157select sum(c) from gstest2 158 group by grouping sets((), grouping sets((), grouping sets(()))) 159 order by 1 desc; 160 sum 161----- 162 12 163 12 164 12 165(3 rows) 166 167select sum(c) from gstest2 168 group by grouping sets((), grouping sets((), grouping sets(((a, b))))) 169 order by 1 desc; 170 sum 171----- 172 12 173 12 174 8 175 2 176 2 177(5 rows) 178 179select sum(c) from gstest2 180 group by grouping sets(grouping sets(rollup(c), grouping sets(cube(c)))) 181 order by 1 desc; 182 sum 183----- 184 12 185 12 186 6 187 6 188 6 189 6 190(6 rows) 191 192select sum(c) from gstest2 193 group by grouping sets(a, grouping sets(a, cube(b))) 194 order by 1 desc; 195 sum 196----- 197 12 198 10 199 10 200 8 201 4 202 2 203 2 204(7 rows) 205 206select sum(c) from gstest2 207 group by grouping sets(grouping sets((a, (b)))) 208 order by 1 desc; 209 sum 210----- 211 8 212 2 213 2 214(3 rows) 215 216select sum(c) from gstest2 217 group by grouping sets(grouping sets((a, b))) 218 order by 1 desc; 219 sum 220----- 221 8 222 2 223 2 224(3 rows) 225 226select sum(c) from gstest2 227 group by grouping sets(grouping sets(a, grouping sets(a), a)) 228 order by 1 desc; 229 sum 230----- 231 10 232 10 233 10 234 2 235 2 236 2 237(6 rows) 238 239select sum(c) from gstest2 240 group by grouping sets(grouping sets(a, grouping sets(a, grouping sets(a), ((a)), a, grouping sets(a), (a)), a)) 241 order by 1 desc; 242 sum 243----- 244 10 245 10 246 10 247 10 248 10 249 10 250 10 251 10 252 2 253 2 254 2 255 2 256 2 257 2 258 2 259 2 260(16 rows) 261 262select sum(c) from gstest2 263 group by grouping sets((a,(a,b)), grouping sets((a,(a,b)),a)) 264 order by 1 desc; 265 sum 266----- 267 10 268 8 269 8 270 2 271 2 272 2 273 2 274 2 275(8 rows) 276 277-- empty input: first is 0 rows, second 1, third 3 etc. 278select a, b, sum(v), count(*) from gstest_empty group by grouping sets ((a,b),a); 279 a | b | sum | count 280---+---+-----+------- 281(0 rows) 282 283select a, b, sum(v), count(*) from gstest_empty group by grouping sets ((a,b),()); 284 a | b | sum | count 285---+---+-----+------- 286 | | | 0 287(1 row) 288 289select a, b, sum(v), count(*) from gstest_empty group by grouping sets ((a,b),(),(),()); 290 a | b | sum | count 291---+---+-----+------- 292 | | | 0 293 | | | 0 294 | | | 0 295(3 rows) 296 297select sum(v), count(*) from gstest_empty group by grouping sets ((),(),()); 298 sum | count 299-----+------- 300 | 0 301 | 0 302 | 0 303(3 rows) 304 305-- empty input with joins tests some important code paths 306select t1.a, t2.b, sum(t1.v), count(*) from gstest_empty t1, gstest_empty t2 307 group by grouping sets ((t1.a,t2.b),()); 308 a | b | sum | count 309---+---+-----+------- 310 | | | 0 311(1 row) 312 313-- simple joins, var resolution, GROUPING on join vars 314select t1.a, t2.b, grouping(t1.a, t2.b), sum(t1.v), max(t2.a) 315 from gstest1 t1, gstest2 t2 316 group by grouping sets ((t1.a, t2.b), ()); 317 a | b | grouping | sum | max 318---+---+----------+------+----- 319 1 | 1 | 0 | 420 | 1 320 1 | 2 | 0 | 120 | 2 321 2 | 1 | 0 | 105 | 1 322 2 | 2 | 0 | 30 | 2 323 3 | 1 | 0 | 231 | 1 324 3 | 2 | 0 | 66 | 2 325 4 | 1 | 0 | 259 | 1 326 4 | 2 | 0 | 74 | 2 327 | | 3 | 1305 | 2 328(9 rows) 329 330select t1.a, t2.b, grouping(t1.a, t2.b), sum(t1.v), max(t2.a) 331 from gstest1 t1 join gstest2 t2 on (t1.a=t2.a) 332 group by grouping sets ((t1.a, t2.b), ()); 333 a | b | grouping | sum | max 334---+---+----------+-----+----- 335 1 | 1 | 0 | 420 | 1 336 1 | 2 | 0 | 60 | 1 337 2 | 2 | 0 | 15 | 2 338 | | 3 | 495 | 2 339(4 rows) 340 341select a, b, grouping(a, b), sum(t1.v), max(t2.c) 342 from gstest1 t1 join gstest2 t2 using (a,b) 343 group by grouping sets ((a, b), ()); 344 a | b | grouping | sum | max 345---+---+----------+-----+----- 346 1 | 1 | 0 | 147 | 2 347 1 | 2 | 0 | 25 | 2 348 | | 3 | 172 | 2 349(3 rows) 350 351-- check that functionally dependent cols are not nulled 352select a, d, grouping(a,b,c) 353 from gstest3 354 group by grouping sets ((a,b), (a,c)); 355 a | d | grouping 356---+---+---------- 357 1 | 1 | 1 358 2 | 2 | 1 359 1 | 1 | 2 360 2 | 2 | 2 361(4 rows) 362 363-- check that distinct grouping columns are kept separate 364-- even if they are equal() 365explain (costs off) 366select g as alias1, g as alias2 367 from generate_series(1,3) g 368 group by alias1, rollup(alias2); 369 QUERY PLAN 370------------------------------------------------ 371 GroupAggregate 372 Group Key: g, g 373 Group Key: g 374 -> Sort 375 Sort Key: g 376 -> Function Scan on generate_series g 377(6 rows) 378 379select g as alias1, g as alias2 380 from generate_series(1,3) g 381 group by alias1, rollup(alias2); 382 alias1 | alias2 383--------+-------- 384 1 | 1 385 1 | 386 2 | 2 387 2 | 388 3 | 3 389 3 | 390(6 rows) 391 392-- check that pulled-up subquery outputs still go to null when appropriate 393select four, x 394 from (select four, ten, 'foo'::text as x from tenk1) as t 395 group by grouping sets (four, x) 396 having x = 'foo'; 397 four | x 398------+----- 399 | foo 400(1 row) 401 402select four, x || 'x' 403 from (select four, ten, 'foo'::text as x from tenk1) as t 404 group by grouping sets (four, x) 405 order by four; 406 four | ?column? 407------+---------- 408 0 | 409 1 | 410 2 | 411 3 | 412 | foox 413(5 rows) 414 415select (x+y)*1, sum(z) 416 from (select 1 as x, 2 as y, 3 as z) s 417 group by grouping sets (x+y, x); 418 ?column? | sum 419----------+----- 420 3 | 3 421 | 3 422(2 rows) 423 424select x, not x as not_x, q2 from 425 (select *, q1 = 1 as x from int8_tbl i1) as t 426 group by grouping sets(x, q2) 427 order by x, q2; 428 x | not_x | q2 429---+-------+------------------- 430 f | t | 431 | | -4567890123456789 432 | | 123 433 | | 456 434 | | 4567890123456789 435(5 rows) 436 437-- check qual push-down rules for a subquery with grouping sets 438explain (verbose, costs off) 439select * from ( 440 select 1 as x, q1, sum(q2) 441 from int8_tbl i1 442 group by grouping sets(1, 2) 443) ss 444where x = 1 and q1 = 123; 445 QUERY PLAN 446-------------------------------------------- 447 Subquery Scan on ss 448 Output: ss.x, ss.q1, ss.sum 449 Filter: ((ss.x = 1) AND (ss.q1 = 123)) 450 -> GroupAggregate 451 Output: (1), i1.q1, sum(i1.q2) 452 Group Key: 1 453 Sort Key: i1.q1 454 Group Key: i1.q1 455 -> Seq Scan on public.int8_tbl i1 456 Output: 1, i1.q1, i1.q2 457(10 rows) 458 459select * from ( 460 select 1 as x, q1, sum(q2) 461 from int8_tbl i1 462 group by grouping sets(1, 2) 463) ss 464where x = 1 and q1 = 123; 465 x | q1 | sum 466---+----+----- 467(0 rows) 468 469-- simple rescan tests 470select a, b, sum(v.x) 471 from (values (1),(2)) v(x), gstest_data(v.x) 472 group by rollup (a,b); 473 a | b | sum 474---+---+----- 475 1 | 1 | 1 476 1 | 2 | 1 477 1 | 3 | 1 478 1 | | 3 479 2 | 1 | 2 480 2 | 2 | 2 481 2 | 3 | 2 482 2 | | 6 483 | | 9 484(9 rows) 485 486select * 487 from (values (1),(2)) v(x), 488 lateral (select a, b, sum(v.x) from gstest_data(v.x) group by rollup (a,b)) s; 489ERROR: aggregate functions are not allowed in FROM clause of their own query level 490LINE 3: lateral (select a, b, sum(v.x) from gstest_data(v.x) ... 491 ^ 492-- min max optimization should still work with GROUP BY () 493explain (costs off) 494 select min(unique1) from tenk1 GROUP BY (); 495 QUERY PLAN 496------------------------------------------------------------ 497 Result 498 InitPlan 1 (returns $0) 499 -> Limit 500 -> Index Only Scan using tenk1_unique1 on tenk1 501 Index Cond: (unique1 IS NOT NULL) 502(5 rows) 503 504-- Views with GROUPING SET queries 505CREATE VIEW gstest_view AS select a, b, grouping(a,b), sum(c), count(*), max(c) 506 from gstest2 group by rollup ((a,b,c),(c,d)); 507NOTICE: view "gstest_view" will be a temporary view 508select pg_get_viewdef('gstest_view'::regclass, true); 509 pg_get_viewdef 510------------------------------------------------------------------------------- 511 SELECT gstest2.a, + 512 gstest2.b, + 513 GROUPING(gstest2.a, gstest2.b) AS "grouping", + 514 sum(gstest2.c) AS sum, + 515 count(*) AS count, + 516 max(gstest2.c) AS max + 517 FROM gstest2 + 518 GROUP BY ROLLUP((gstest2.a, gstest2.b, gstest2.c), (gstest2.c, gstest2.d)); 519(1 row) 520 521-- Nested queries with 3 or more levels of nesting 522select(select (select grouping(a,b) from (values (1)) v2(c)) from (values (1,2)) v1(a,b) group by (a,b)) from (values(6,7)) v3(e,f) GROUP BY ROLLUP(e,f); 523 grouping 524---------- 525 0 526 0 527 0 528(3 rows) 529 530select(select (select grouping(e,f) from (values (1)) v2(c)) from (values (1,2)) v1(a,b) group by (a,b)) from (values(6,7)) v3(e,f) GROUP BY ROLLUP(e,f); 531 grouping 532---------- 533 0 534 1 535 3 536(3 rows) 537 538select(select (select grouping(c) from (values (1)) v2(c) GROUP BY c) from (values (1,2)) v1(a,b) group by (a,b)) from (values(6,7)) v3(e,f) GROUP BY ROLLUP(e,f); 539 grouping 540---------- 541 0 542 0 543 0 544(3 rows) 545 546-- Combinations of operations 547select a, b, c, d from gstest2 group by rollup(a,b),grouping sets(c,d); 548 a | b | c | d 549---+---+---+--- 550 1 | 1 | 1 | 551 1 | | 1 | 552 | | 1 | 553 1 | 1 | 2 | 554 1 | 2 | 2 | 555 1 | | 2 | 556 2 | 2 | 2 | 557 2 | | 2 | 558 | | 2 | 559 1 | 1 | | 1 560 1 | | | 1 561 | | | 1 562 1 | 1 | | 2 563 1 | 2 | | 2 564 1 | | | 2 565 2 | 2 | | 2 566 2 | | | 2 567 | | | 2 568(18 rows) 569 570select a, b from (values (1,2),(2,3)) v(a,b) group by a,b, grouping sets(a); 571 a | b 572---+--- 573 1 | 2 574 2 | 3 575(2 rows) 576 577-- Tests for chained aggregates 578select a, b, grouping(a,b), sum(v), count(*), max(v) 579 from gstest1 group by grouping sets ((a,b),(a+1,b+1),(a+2,b+2)) order by 3,6; 580 a | b | grouping | sum | count | max 581---+---+----------+-----+-------+----- 582 1 | 1 | 0 | 21 | 2 | 11 583 1 | 2 | 0 | 25 | 2 | 13 584 1 | 3 | 0 | 14 | 1 | 14 585 2 | 3 | 0 | 15 | 1 | 15 586 3 | 3 | 0 | 16 | 1 | 16 587 3 | 4 | 0 | 17 | 1 | 17 588 4 | 1 | 0 | 37 | 2 | 19 589 | | 3 | 21 | 2 | 11 590 | | 3 | 21 | 2 | 11 591 | | 3 | 25 | 2 | 13 592 | | 3 | 25 | 2 | 13 593 | | 3 | 14 | 1 | 14 594 | | 3 | 14 | 1 | 14 595 | | 3 | 15 | 1 | 15 596 | | 3 | 15 | 1 | 15 597 | | 3 | 16 | 1 | 16 598 | | 3 | 16 | 1 | 16 599 | | 3 | 17 | 1 | 17 600 | | 3 | 17 | 1 | 17 601 | | 3 | 37 | 2 | 19 602 | | 3 | 37 | 2 | 19 603(21 rows) 604 605select(select (select grouping(a,b) from (values (1)) v2(c)) from (values (1,2)) v1(a,b) group by (a,b)) from (values(6,7)) v3(e,f) GROUP BY ROLLUP((e+1),(f+1)); 606 grouping 607---------- 608 0 609 0 610 0 611(3 rows) 612 613select(select (select grouping(a,b) from (values (1)) v2(c)) from (values (1,2)) v1(a,b) group by (a,b)) from (values(6,7)) v3(e,f) GROUP BY CUBE((e+1),(f+1)) ORDER BY (e+1),(f+1); 614 grouping 615---------- 616 0 617 0 618 0 619 0 620(4 rows) 621 622select a, b, sum(c), sum(sum(c)) over (order by a,b) as rsum 623 from gstest2 group by cube (a,b) order by rsum, a, b; 624 a | b | sum | rsum 625---+---+-----+------ 626 1 | 1 | 8 | 8 627 1 | 2 | 2 | 10 628 1 | | 10 | 20 629 2 | 2 | 2 | 22 630 2 | | 2 | 24 631 | 1 | 8 | 32 632 | 2 | 4 | 36 633 | | 12 | 48 634(8 rows) 635 636select a, b, sum(c) from (values (1,1,10),(1,1,11),(1,2,12),(1,2,13),(1,3,14),(2,3,15),(3,3,16),(3,4,17),(4,1,18),(4,1,19)) v(a,b,c) group by rollup (a,b); 637 a | b | sum 638---+---+----- 639 1 | 1 | 21 640 1 | 2 | 25 641 1 | 3 | 14 642 1 | | 60 643 2 | 3 | 15 644 2 | | 15 645 3 | 3 | 16 646 3 | 4 | 17 647 3 | | 33 648 4 | 1 | 37 649 4 | | 37 650 | | 145 651(12 rows) 652 653select a, b, sum(v.x) 654 from (values (1),(2)) v(x), gstest_data(v.x) 655 group by cube (a,b) order by a,b; 656 a | b | sum 657---+---+----- 658 1 | 1 | 1 659 1 | 2 | 1 660 1 | 3 | 1 661 1 | | 3 662 2 | 1 | 2 663 2 | 2 | 2 664 2 | 3 | 2 665 2 | | 6 666 | 1 | 3 667 | 2 | 3 668 | 3 | 3 669 | | 9 670(12 rows) 671 672-- Test reordering of grouping sets 673explain (costs off) 674select * from gstest1 group by grouping sets((a,b,v),(v)) order by v,b,a; 675 QUERY PLAN 676------------------------------------------------------------------------------ 677 GroupAggregate 678 Group Key: "*VALUES*".column3, "*VALUES*".column2, "*VALUES*".column1 679 Group Key: "*VALUES*".column3 680 -> Sort 681 Sort Key: "*VALUES*".column3, "*VALUES*".column2, "*VALUES*".column1 682 -> Values Scan on "*VALUES*" 683(6 rows) 684 685-- Agg level check. This query should error out. 686select (select grouping(a,b) from gstest2) from gstest2 group by a,b; 687ERROR: arguments to GROUPING must be grouping expressions of the associated query level 688LINE 1: select (select grouping(a,b) from gstest2) from gstest2 grou... 689 ^ 690--Nested queries 691select a, b, sum(c), count(*) from gstest2 group by grouping sets (rollup(a,b),a); 692 a | b | sum | count 693---+---+-----+------- 694 1 | 1 | 8 | 7 695 1 | 2 | 2 | 1 696 1 | | 10 | 8 697 1 | | 10 | 8 698 2 | 2 | 2 | 1 699 2 | | 2 | 1 700 2 | | 2 | 1 701 | | 12 | 9 702(8 rows) 703 704-- HAVING queries 705select ten, sum(distinct four) from onek a 706group by grouping sets((ten,four),(ten)) 707having exists (select 1 from onek b where sum(distinct a.four) = b.four); 708 ten | sum 709-----+----- 710 0 | 0 711 0 | 2 712 0 | 2 713 1 | 1 714 1 | 3 715 2 | 0 716 2 | 2 717 2 | 2 718 3 | 1 719 3 | 3 720 4 | 0 721 4 | 2 722 4 | 2 723 5 | 1 724 5 | 3 725 6 | 0 726 6 | 2 727 6 | 2 728 7 | 1 729 7 | 3 730 8 | 0 731 8 | 2 732 8 | 2 733 9 | 1 734 9 | 3 735(25 rows) 736 737-- Tests around pushdown of HAVING clauses, partially testing against previous bugs 738select a,count(*) from gstest2 group by rollup(a) order by a; 739 a | count 740---+------- 741 1 | 8 742 2 | 1 743 | 9 744(3 rows) 745 746select a,count(*) from gstest2 group by rollup(a) having a is distinct from 1 order by a; 747 a | count 748---+------- 749 2 | 1 750 | 9 751(2 rows) 752 753explain (costs off) 754 select a,count(*) from gstest2 group by rollup(a) having a is distinct from 1 order by a; 755 QUERY PLAN 756---------------------------------- 757 GroupAggregate 758 Group Key: a 759 Group Key: () 760 Filter: (a IS DISTINCT FROM 1) 761 -> Sort 762 Sort Key: a 763 -> Seq Scan on gstest2 764(7 rows) 765 766select v.c, (select count(*) from gstest2 group by () having v.c) 767 from (values (false),(true)) v(c) order by v.c; 768 c | count 769---+------- 770 f | 771 t | 9 772(2 rows) 773 774explain (costs off) 775 select v.c, (select count(*) from gstest2 group by () having v.c) 776 from (values (false),(true)) v(c) order by v.c; 777 QUERY PLAN 778----------------------------------------------------------- 779 Sort 780 Sort Key: "*VALUES*".column1 781 -> Values Scan on "*VALUES*" 782 SubPlan 1 783 -> Aggregate 784 Group Key: () 785 Filter: "*VALUES*".column1 786 -> Result 787 One-Time Filter: "*VALUES*".column1 788 -> Seq Scan on gstest2 789(10 rows) 790 791-- HAVING with GROUPING queries 792select ten, grouping(ten) from onek 793group by grouping sets(ten) having grouping(ten) >= 0 794order by 2,1; 795 ten | grouping 796-----+---------- 797 0 | 0 798 1 | 0 799 2 | 0 800 3 | 0 801 4 | 0 802 5 | 0 803 6 | 0 804 7 | 0 805 8 | 0 806 9 | 0 807(10 rows) 808 809select ten, grouping(ten) from onek 810group by grouping sets(ten, four) having grouping(ten) > 0 811order by 2,1; 812 ten | grouping 813-----+---------- 814 | 1 815 | 1 816 | 1 817 | 1 818(4 rows) 819 820select ten, grouping(ten) from onek 821group by rollup(ten) having grouping(ten) > 0 822order by 2,1; 823 ten | grouping 824-----+---------- 825 | 1 826(1 row) 827 828select ten, grouping(ten) from onek 829group by cube(ten) having grouping(ten) > 0 830order by 2,1; 831 ten | grouping 832-----+---------- 833 | 1 834(1 row) 835 836select ten, grouping(ten) from onek 837group by (ten) having grouping(ten) >= 0 838order by 2,1; 839 ten | grouping 840-----+---------- 841 0 | 0 842 1 | 0 843 2 | 0 844 3 | 0 845 4 | 0 846 5 | 0 847 6 | 0 848 7 | 0 849 8 | 0 850 9 | 0 851(10 rows) 852 853-- FILTER queries 854select ten, sum(distinct four) filter (where four::text ~ '123') from onek a 855group by rollup(ten); 856 ten | sum 857-----+----- 858 0 | 859 1 | 860 2 | 861 3 | 862 4 | 863 5 | 864 6 | 865 7 | 866 8 | 867 9 | 868 | 869(11 rows) 870 871-- More rescan tests 872select * from (values (1),(2)) v(a) left join lateral (select v.a, four, ten, count(*) from onek group by cube(four,ten)) s on true order by v.a,four,ten; 873 a | a | four | ten | count 874---+---+------+-----+------- 875 1 | 1 | 0 | 0 | 50 876 1 | 1 | 0 | 2 | 50 877 1 | 1 | 0 | 4 | 50 878 1 | 1 | 0 | 6 | 50 879 1 | 1 | 0 | 8 | 50 880 1 | 1 | 0 | | 250 881 1 | 1 | 1 | 1 | 50 882 1 | 1 | 1 | 3 | 50 883 1 | 1 | 1 | 5 | 50 884 1 | 1 | 1 | 7 | 50 885 1 | 1 | 1 | 9 | 50 886 1 | 1 | 1 | | 250 887 1 | 1 | 2 | 0 | 50 888 1 | 1 | 2 | 2 | 50 889 1 | 1 | 2 | 4 | 50 890 1 | 1 | 2 | 6 | 50 891 1 | 1 | 2 | 8 | 50 892 1 | 1 | 2 | | 250 893 1 | 1 | 3 | 1 | 50 894 1 | 1 | 3 | 3 | 50 895 1 | 1 | 3 | 5 | 50 896 1 | 1 | 3 | 7 | 50 897 1 | 1 | 3 | 9 | 50 898 1 | 1 | 3 | | 250 899 1 | 1 | | 0 | 100 900 1 | 1 | | 1 | 100 901 1 | 1 | | 2 | 100 902 1 | 1 | | 3 | 100 903 1 | 1 | | 4 | 100 904 1 | 1 | | 5 | 100 905 1 | 1 | | 6 | 100 906 1 | 1 | | 7 | 100 907 1 | 1 | | 8 | 100 908 1 | 1 | | 9 | 100 909 1 | 1 | | | 1000 910 2 | 2 | 0 | 0 | 50 911 2 | 2 | 0 | 2 | 50 912 2 | 2 | 0 | 4 | 50 913 2 | 2 | 0 | 6 | 50 914 2 | 2 | 0 | 8 | 50 915 2 | 2 | 0 | | 250 916 2 | 2 | 1 | 1 | 50 917 2 | 2 | 1 | 3 | 50 918 2 | 2 | 1 | 5 | 50 919 2 | 2 | 1 | 7 | 50 920 2 | 2 | 1 | 9 | 50 921 2 | 2 | 1 | | 250 922 2 | 2 | 2 | 0 | 50 923 2 | 2 | 2 | 2 | 50 924 2 | 2 | 2 | 4 | 50 925 2 | 2 | 2 | 6 | 50 926 2 | 2 | 2 | 8 | 50 927 2 | 2 | 2 | | 250 928 2 | 2 | 3 | 1 | 50 929 2 | 2 | 3 | 3 | 50 930 2 | 2 | 3 | 5 | 50 931 2 | 2 | 3 | 7 | 50 932 2 | 2 | 3 | 9 | 50 933 2 | 2 | 3 | | 250 934 2 | 2 | | 0 | 100 935 2 | 2 | | 1 | 100 936 2 | 2 | | 2 | 100 937 2 | 2 | | 3 | 100 938 2 | 2 | | 4 | 100 939 2 | 2 | | 5 | 100 940 2 | 2 | | 6 | 100 941 2 | 2 | | 7 | 100 942 2 | 2 | | 8 | 100 943 2 | 2 | | 9 | 100 944 2 | 2 | | | 1000 945(70 rows) 946 947select array(select row(v.a,s1.*) from (select two,four, count(*) from onek group by cube(two,four) order by two,four) s1) from (values (1),(2)) v(a); 948 array 949------------------------------------------------------------------------------------------------------------------------------------------------------ 950 {"(1,0,0,250)","(1,0,2,250)","(1,0,,500)","(1,1,1,250)","(1,1,3,250)","(1,1,,500)","(1,,0,250)","(1,,1,250)","(1,,2,250)","(1,,3,250)","(1,,,1000)"} 951 {"(2,0,0,250)","(2,0,2,250)","(2,0,,500)","(2,1,1,250)","(2,1,3,250)","(2,1,,500)","(2,,0,250)","(2,,1,250)","(2,,2,250)","(2,,3,250)","(2,,,1000)"} 952(2 rows) 953 954-- Grouping on text columns 955select sum(ten) from onek group by two, rollup(four::text) order by 1; 956 sum 957------ 958 1000 959 1000 960 1250 961 1250 962 2000 963 2500 964(6 rows) 965 966select sum(ten) from onek group by rollup(four::text), two order by 1; 967 sum 968------ 969 1000 970 1000 971 1250 972 1250 973 2000 974 2500 975(6 rows) 976 977-- hashing support 978set enable_hashagg = true; 979-- failure cases 980select count(*) from gstest4 group by rollup(unhashable_col,unsortable_col); 981ERROR: could not implement GROUP BY 982DETAIL: Some of the datatypes only support hashing, while others only support sorting. 983select array_agg(v order by v) from gstest4 group by grouping sets ((id,unsortable_col),(id)); 984ERROR: could not implement GROUP BY 985DETAIL: Some of the datatypes only support hashing, while others only support sorting. 986-- simple cases 987select a, b, grouping(a,b), sum(v), count(*), max(v) 988 from gstest1 group by grouping sets ((a),(b)) order by 3,1,2; 989 a | b | grouping | sum | count | max 990---+---+----------+-----+-------+----- 991 1 | | 1 | 60 | 5 | 14 992 2 | | 1 | 15 | 1 | 15 993 3 | | 1 | 33 | 2 | 17 994 4 | | 1 | 37 | 2 | 19 995 | 1 | 2 | 58 | 4 | 19 996 | 2 | 2 | 25 | 2 | 13 997 | 3 | 2 | 45 | 3 | 16 998 | 4 | 2 | 17 | 1 | 17 999(8 rows) 1000 1001explain (costs off) select a, b, grouping(a,b), sum(v), count(*), max(v) 1002 from gstest1 group by grouping sets ((a),(b)) order by 3,1,2; 1003 QUERY PLAN 1004-------------------------------------------------------------------------------------------------------- 1005 Sort 1006 Sort Key: (GROUPING("*VALUES*".column1, "*VALUES*".column2)), "*VALUES*".column1, "*VALUES*".column2 1007 -> HashAggregate 1008 Hash Key: "*VALUES*".column1 1009 Hash Key: "*VALUES*".column2 1010 -> Values Scan on "*VALUES*" 1011(6 rows) 1012 1013select a, b, grouping(a,b), sum(v), count(*), max(v) 1014 from gstest1 group by cube(a,b) order by 3,1,2; 1015 a | b | grouping | sum | count | max 1016---+---+----------+-----+-------+----- 1017 1 | 1 | 0 | 21 | 2 | 11 1018 1 | 2 | 0 | 25 | 2 | 13 1019 1 | 3 | 0 | 14 | 1 | 14 1020 2 | 3 | 0 | 15 | 1 | 15 1021 3 | 3 | 0 | 16 | 1 | 16 1022 3 | 4 | 0 | 17 | 1 | 17 1023 4 | 1 | 0 | 37 | 2 | 19 1024 1 | | 1 | 60 | 5 | 14 1025 2 | | 1 | 15 | 1 | 15 1026 3 | | 1 | 33 | 2 | 17 1027 4 | | 1 | 37 | 2 | 19 1028 | 1 | 2 | 58 | 4 | 19 1029 | 2 | 2 | 25 | 2 | 13 1030 | 3 | 2 | 45 | 3 | 16 1031 | 4 | 2 | 17 | 1 | 17 1032 | | 3 | 145 | 10 | 19 1033(16 rows) 1034 1035explain (costs off) select a, b, grouping(a,b), sum(v), count(*), max(v) 1036 from gstest1 group by cube(a,b) order by 3,1,2; 1037 QUERY PLAN 1038-------------------------------------------------------------------------------------------------------- 1039 Sort 1040 Sort Key: (GROUPING("*VALUES*".column1, "*VALUES*".column2)), "*VALUES*".column1, "*VALUES*".column2 1041 -> MixedAggregate 1042 Hash Key: "*VALUES*".column1, "*VALUES*".column2 1043 Hash Key: "*VALUES*".column1 1044 Hash Key: "*VALUES*".column2 1045 Group Key: () 1046 -> Values Scan on "*VALUES*" 1047(8 rows) 1048 1049-- shouldn't try and hash 1050explain (costs off) 1051 select a, b, grouping(a,b), array_agg(v order by v) 1052 from gstest1 group by cube(a,b); 1053 QUERY PLAN 1054---------------------------------------------------------- 1055 GroupAggregate 1056 Group Key: "*VALUES*".column1, "*VALUES*".column2 1057 Group Key: "*VALUES*".column1 1058 Group Key: () 1059 Sort Key: "*VALUES*".column2 1060 Group Key: "*VALUES*".column2 1061 -> Sort 1062 Sort Key: "*VALUES*".column1, "*VALUES*".column2 1063 -> Values Scan on "*VALUES*" 1064(9 rows) 1065 1066-- unsortable cases 1067select unsortable_col, count(*) 1068 from gstest4 group by grouping sets ((unsortable_col),(unsortable_col)) 1069 order by unsortable_col::text; 1070 unsortable_col | count 1071----------------+------- 1072 1 | 4 1073 1 | 4 1074 2 | 4 1075 2 | 4 1076(4 rows) 1077 1078-- mixed hashable/sortable cases 1079select unhashable_col, unsortable_col, 1080 grouping(unhashable_col, unsortable_col), 1081 count(*), sum(v) 1082 from gstest4 group by grouping sets ((unhashable_col),(unsortable_col)) 1083 order by 3, 5; 1084 unhashable_col | unsortable_col | grouping | count | sum 1085----------------+----------------+----------+-------+----- 1086 0000 | | 1 | 2 | 17 1087 0001 | | 1 | 2 | 34 1088 0010 | | 1 | 2 | 68 1089 0011 | | 1 | 2 | 136 1090 | 2 | 2 | 4 | 60 1091 | 1 | 2 | 4 | 195 1092(6 rows) 1093 1094explain (costs off) 1095 select unhashable_col, unsortable_col, 1096 grouping(unhashable_col, unsortable_col), 1097 count(*), sum(v) 1098 from gstest4 group by grouping sets ((unhashable_col),(unsortable_col)) 1099 order by 3,5; 1100 QUERY PLAN 1101------------------------------------------------------------------ 1102 Sort 1103 Sort Key: (GROUPING(unhashable_col, unsortable_col)), (sum(v)) 1104 -> MixedAggregate 1105 Hash Key: unsortable_col 1106 Group Key: unhashable_col 1107 -> Sort 1108 Sort Key: unhashable_col 1109 -> Seq Scan on gstest4 1110(8 rows) 1111 1112select unhashable_col, unsortable_col, 1113 grouping(unhashable_col, unsortable_col), 1114 count(*), sum(v) 1115 from gstest4 group by grouping sets ((v,unhashable_col),(v,unsortable_col)) 1116 order by 3,5; 1117 unhashable_col | unsortable_col | grouping | count | sum 1118----------------+----------------+----------+-------+----- 1119 0000 | | 1 | 1 | 1 1120 0001 | | 1 | 1 | 2 1121 0010 | | 1 | 1 | 4 1122 0011 | | 1 | 1 | 8 1123 0000 | | 1 | 1 | 16 1124 0001 | | 1 | 1 | 32 1125 0010 | | 1 | 1 | 64 1126 0011 | | 1 | 1 | 128 1127 | 1 | 2 | 1 | 1 1128 | 1 | 2 | 1 | 2 1129 | 2 | 2 | 1 | 4 1130 | 2 | 2 | 1 | 8 1131 | 2 | 2 | 1 | 16 1132 | 2 | 2 | 1 | 32 1133 | 1 | 2 | 1 | 64 1134 | 1 | 2 | 1 | 128 1135(16 rows) 1136 1137explain (costs off) 1138 select unhashable_col, unsortable_col, 1139 grouping(unhashable_col, unsortable_col), 1140 count(*), sum(v) 1141 from gstest4 group by grouping sets ((v,unhashable_col),(v,unsortable_col)) 1142 order by 3,5; 1143 QUERY PLAN 1144------------------------------------------------------------------ 1145 Sort 1146 Sort Key: (GROUPING(unhashable_col, unsortable_col)), (sum(v)) 1147 -> MixedAggregate 1148 Hash Key: v, unsortable_col 1149 Group Key: v, unhashable_col 1150 -> Sort 1151 Sort Key: v, unhashable_col 1152 -> Seq Scan on gstest4 1153(8 rows) 1154 1155-- empty input: first is 0 rows, second 1, third 3 etc. 1156select a, b, sum(v), count(*) from gstest_empty group by grouping sets ((a,b),a); 1157 a | b | sum | count 1158---+---+-----+------- 1159(0 rows) 1160 1161explain (costs off) 1162 select a, b, sum(v), count(*) from gstest_empty group by grouping sets ((a,b),a); 1163 QUERY PLAN 1164-------------------------------- 1165 HashAggregate 1166 Hash Key: a, b 1167 Hash Key: a 1168 -> Seq Scan on gstest_empty 1169(4 rows) 1170 1171select a, b, sum(v), count(*) from gstest_empty group by grouping sets ((a,b),()); 1172 a | b | sum | count 1173---+---+-----+------- 1174 | | | 0 1175(1 row) 1176 1177select a, b, sum(v), count(*) from gstest_empty group by grouping sets ((a,b),(),(),()); 1178 a | b | sum | count 1179---+---+-----+------- 1180 | | | 0 1181 | | | 0 1182 | | | 0 1183(3 rows) 1184 1185explain (costs off) 1186 select a, b, sum(v), count(*) from gstest_empty group by grouping sets ((a,b),(),(),()); 1187 QUERY PLAN 1188-------------------------------- 1189 MixedAggregate 1190 Hash Key: a, b 1191 Group Key: () 1192 Group Key: () 1193 Group Key: () 1194 -> Seq Scan on gstest_empty 1195(6 rows) 1196 1197select sum(v), count(*) from gstest_empty group by grouping sets ((),(),()); 1198 sum | count 1199-----+------- 1200 | 0 1201 | 0 1202 | 0 1203(3 rows) 1204 1205explain (costs off) 1206 select sum(v), count(*) from gstest_empty group by grouping sets ((),(),()); 1207 QUERY PLAN 1208-------------------------------- 1209 Aggregate 1210 Group Key: () 1211 Group Key: () 1212 Group Key: () 1213 -> Seq Scan on gstest_empty 1214(5 rows) 1215 1216-- check that functionally dependent cols are not nulled 1217select a, d, grouping(a,b,c) 1218 from gstest3 1219 group by grouping sets ((a,b), (a,c)); 1220 a | d | grouping 1221---+---+---------- 1222 1 | 1 | 1 1223 2 | 2 | 1 1224 1 | 1 | 2 1225 2 | 2 | 2 1226(4 rows) 1227 1228explain (costs off) 1229 select a, d, grouping(a,b,c) 1230 from gstest3 1231 group by grouping sets ((a,b), (a,c)); 1232 QUERY PLAN 1233--------------------------- 1234 HashAggregate 1235 Hash Key: a, b 1236 Hash Key: a, c 1237 -> Seq Scan on gstest3 1238(4 rows) 1239 1240-- simple rescan tests 1241select a, b, sum(v.x) 1242 from (values (1),(2)) v(x), gstest_data(v.x) 1243 group by grouping sets (a,b) 1244 order by 1, 2, 3; 1245 a | b | sum 1246---+---+----- 1247 1 | | 3 1248 2 | | 6 1249 | 1 | 3 1250 | 2 | 3 1251 | 3 | 3 1252(5 rows) 1253 1254explain (costs off) 1255 select a, b, sum(v.x) 1256 from (values (1),(2)) v(x), gstest_data(v.x) 1257 group by grouping sets (a,b) 1258 order by 3, 1, 2; 1259 QUERY PLAN 1260--------------------------------------------------------------------- 1261 Sort 1262 Sort Key: (sum("*VALUES*".column1)), gstest_data.a, gstest_data.b 1263 -> HashAggregate 1264 Hash Key: gstest_data.a 1265 Hash Key: gstest_data.b 1266 -> Nested Loop 1267 -> Values Scan on "*VALUES*" 1268 -> Function Scan on gstest_data 1269(8 rows) 1270 1271select * 1272 from (values (1),(2)) v(x), 1273 lateral (select a, b, sum(v.x) from gstest_data(v.x) group by grouping sets (a,b)) s; 1274ERROR: aggregate functions are not allowed in FROM clause of their own query level 1275LINE 3: lateral (select a, b, sum(v.x) from gstest_data(v.x) ... 1276 ^ 1277explain (costs off) 1278 select * 1279 from (values (1),(2)) v(x), 1280 lateral (select a, b, sum(v.x) from gstest_data(v.x) group by grouping sets (a,b)) s; 1281ERROR: aggregate functions are not allowed in FROM clause of their own query level 1282LINE 4: lateral (select a, b, sum(v.x) from gstest_data(v.x... 1283 ^ 1284-- Tests for chained aggregates 1285select a, b, grouping(a,b), sum(v), count(*), max(v) 1286 from gstest1 group by grouping sets ((a,b),(a+1,b+1),(a+2,b+2)) order by 3,6; 1287 a | b | grouping | sum | count | max 1288---+---+----------+-----+-------+----- 1289 1 | 1 | 0 | 21 | 2 | 11 1290 1 | 2 | 0 | 25 | 2 | 13 1291 1 | 3 | 0 | 14 | 1 | 14 1292 2 | 3 | 0 | 15 | 1 | 15 1293 3 | 3 | 0 | 16 | 1 | 16 1294 3 | 4 | 0 | 17 | 1 | 17 1295 4 | 1 | 0 | 37 | 2 | 19 1296 | | 3 | 21 | 2 | 11 1297 | | 3 | 21 | 2 | 11 1298 | | 3 | 25 | 2 | 13 1299 | | 3 | 25 | 2 | 13 1300 | | 3 | 14 | 1 | 14 1301 | | 3 | 14 | 1 | 14 1302 | | 3 | 15 | 1 | 15 1303 | | 3 | 15 | 1 | 15 1304 | | 3 | 16 | 1 | 16 1305 | | 3 | 16 | 1 | 16 1306 | | 3 | 17 | 1 | 17 1307 | | 3 | 17 | 1 | 17 1308 | | 3 | 37 | 2 | 19 1309 | | 3 | 37 | 2 | 19 1310(21 rows) 1311 1312explain (costs off) 1313 select a, b, grouping(a,b), sum(v), count(*), max(v) 1314 from gstest1 group by grouping sets ((a,b),(a+1,b+1),(a+2,b+2)) order by 3,6; 1315 QUERY PLAN 1316------------------------------------------------------------------------------------------- 1317 Sort 1318 Sort Key: (GROUPING("*VALUES*".column1, "*VALUES*".column2)), (max("*VALUES*".column3)) 1319 -> HashAggregate 1320 Hash Key: "*VALUES*".column1, "*VALUES*".column2 1321 Hash Key: ("*VALUES*".column1 + 1), ("*VALUES*".column2 + 1) 1322 Hash Key: ("*VALUES*".column1 + 2), ("*VALUES*".column2 + 2) 1323 -> Values Scan on "*VALUES*" 1324(7 rows) 1325 1326select a, b, sum(c), sum(sum(c)) over (order by a,b) as rsum 1327 from gstest2 group by cube (a,b) order by rsum, a, b; 1328 a | b | sum | rsum 1329---+---+-----+------ 1330 1 | 1 | 8 | 8 1331 1 | 2 | 2 | 10 1332 1 | | 10 | 20 1333 2 | 2 | 2 | 22 1334 2 | | 2 | 24 1335 | 1 | 8 | 32 1336 | 2 | 4 | 36 1337 | | 12 | 48 1338(8 rows) 1339 1340explain (costs off) 1341 select a, b, sum(c), sum(sum(c)) over (order by a,b) as rsum 1342 from gstest2 group by cube (a,b) order by rsum, a, b; 1343 QUERY PLAN 1344--------------------------------------------- 1345 Sort 1346 Sort Key: (sum((sum(c))) OVER (?)), a, b 1347 -> WindowAgg 1348 -> Sort 1349 Sort Key: a, b 1350 -> MixedAggregate 1351 Hash Key: a, b 1352 Hash Key: a 1353 Hash Key: b 1354 Group Key: () 1355 -> Seq Scan on gstest2 1356(11 rows) 1357 1358select a, b, sum(v.x) 1359 from (values (1),(2)) v(x), gstest_data(v.x) 1360 group by cube (a,b) order by a,b; 1361 a | b | sum 1362---+---+----- 1363 1 | 1 | 1 1364 1 | 2 | 1 1365 1 | 3 | 1 1366 1 | | 3 1367 2 | 1 | 2 1368 2 | 2 | 2 1369 2 | 3 | 2 1370 2 | | 6 1371 | 1 | 3 1372 | 2 | 3 1373 | 3 | 3 1374 | | 9 1375(12 rows) 1376 1377explain (costs off) 1378 select a, b, sum(v.x) 1379 from (values (1),(2)) v(x), gstest_data(v.x) 1380 group by cube (a,b) order by a,b; 1381 QUERY PLAN 1382------------------------------------------------ 1383 Sort 1384 Sort Key: gstest_data.a, gstest_data.b 1385 -> MixedAggregate 1386 Hash Key: gstest_data.a, gstest_data.b 1387 Hash Key: gstest_data.a 1388 Hash Key: gstest_data.b 1389 Group Key: () 1390 -> Nested Loop 1391 -> Values Scan on "*VALUES*" 1392 -> Function Scan on gstest_data 1393(10 rows) 1394 1395-- More rescan tests 1396select * from (values (1),(2)) v(a) left join lateral (select v.a, four, ten, count(*) from onek group by cube(four,ten)) s on true order by v.a,four,ten; 1397 a | a | four | ten | count 1398---+---+------+-----+------- 1399 1 | 1 | 0 | 0 | 50 1400 1 | 1 | 0 | 2 | 50 1401 1 | 1 | 0 | 4 | 50 1402 1 | 1 | 0 | 6 | 50 1403 1 | 1 | 0 | 8 | 50 1404 1 | 1 | 0 | | 250 1405 1 | 1 | 1 | 1 | 50 1406 1 | 1 | 1 | 3 | 50 1407 1 | 1 | 1 | 5 | 50 1408 1 | 1 | 1 | 7 | 50 1409 1 | 1 | 1 | 9 | 50 1410 1 | 1 | 1 | | 250 1411 1 | 1 | 2 | 0 | 50 1412 1 | 1 | 2 | 2 | 50 1413 1 | 1 | 2 | 4 | 50 1414 1 | 1 | 2 | 6 | 50 1415 1 | 1 | 2 | 8 | 50 1416 1 | 1 | 2 | | 250 1417 1 | 1 | 3 | 1 | 50 1418 1 | 1 | 3 | 3 | 50 1419 1 | 1 | 3 | 5 | 50 1420 1 | 1 | 3 | 7 | 50 1421 1 | 1 | 3 | 9 | 50 1422 1 | 1 | 3 | | 250 1423 1 | 1 | | 0 | 100 1424 1 | 1 | | 1 | 100 1425 1 | 1 | | 2 | 100 1426 1 | 1 | | 3 | 100 1427 1 | 1 | | 4 | 100 1428 1 | 1 | | 5 | 100 1429 1 | 1 | | 6 | 100 1430 1 | 1 | | 7 | 100 1431 1 | 1 | | 8 | 100 1432 1 | 1 | | 9 | 100 1433 1 | 1 | | | 1000 1434 2 | 2 | 0 | 0 | 50 1435 2 | 2 | 0 | 2 | 50 1436 2 | 2 | 0 | 4 | 50 1437 2 | 2 | 0 | 6 | 50 1438 2 | 2 | 0 | 8 | 50 1439 2 | 2 | 0 | | 250 1440 2 | 2 | 1 | 1 | 50 1441 2 | 2 | 1 | 3 | 50 1442 2 | 2 | 1 | 5 | 50 1443 2 | 2 | 1 | 7 | 50 1444 2 | 2 | 1 | 9 | 50 1445 2 | 2 | 1 | | 250 1446 2 | 2 | 2 | 0 | 50 1447 2 | 2 | 2 | 2 | 50 1448 2 | 2 | 2 | 4 | 50 1449 2 | 2 | 2 | 6 | 50 1450 2 | 2 | 2 | 8 | 50 1451 2 | 2 | 2 | | 250 1452 2 | 2 | 3 | 1 | 50 1453 2 | 2 | 3 | 3 | 50 1454 2 | 2 | 3 | 5 | 50 1455 2 | 2 | 3 | 7 | 50 1456 2 | 2 | 3 | 9 | 50 1457 2 | 2 | 3 | | 250 1458 2 | 2 | | 0 | 100 1459 2 | 2 | | 1 | 100 1460 2 | 2 | | 2 | 100 1461 2 | 2 | | 3 | 100 1462 2 | 2 | | 4 | 100 1463 2 | 2 | | 5 | 100 1464 2 | 2 | | 6 | 100 1465 2 | 2 | | 7 | 100 1466 2 | 2 | | 8 | 100 1467 2 | 2 | | 9 | 100 1468 2 | 2 | | | 1000 1469(70 rows) 1470 1471select array(select row(v.a,s1.*) from (select two,four, count(*) from onek group by cube(two,four) order by two,four) s1) from (values (1),(2)) v(a); 1472 array 1473------------------------------------------------------------------------------------------------------------------------------------------------------ 1474 {"(1,0,0,250)","(1,0,2,250)","(1,0,,500)","(1,1,1,250)","(1,1,3,250)","(1,1,,500)","(1,,0,250)","(1,,1,250)","(1,,2,250)","(1,,3,250)","(1,,,1000)"} 1475 {"(2,0,0,250)","(2,0,2,250)","(2,0,,500)","(2,1,1,250)","(2,1,3,250)","(2,1,,500)","(2,,0,250)","(2,,1,250)","(2,,2,250)","(2,,3,250)","(2,,,1000)"} 1476(2 rows) 1477 1478-- Rescan logic changes when there are no empty grouping sets, so test 1479-- that too: 1480select * from (values (1),(2)) v(a) left join lateral (select v.a, four, ten, count(*) from onek group by grouping sets(four,ten)) s on true order by v.a,four,ten; 1481 a | a | four | ten | count 1482---+---+------+-----+------- 1483 1 | 1 | 0 | | 250 1484 1 | 1 | 1 | | 250 1485 1 | 1 | 2 | | 250 1486 1 | 1 | 3 | | 250 1487 1 | 1 | | 0 | 100 1488 1 | 1 | | 1 | 100 1489 1 | 1 | | 2 | 100 1490 1 | 1 | | 3 | 100 1491 1 | 1 | | 4 | 100 1492 1 | 1 | | 5 | 100 1493 1 | 1 | | 6 | 100 1494 1 | 1 | | 7 | 100 1495 1 | 1 | | 8 | 100 1496 1 | 1 | | 9 | 100 1497 2 | 2 | 0 | | 250 1498 2 | 2 | 1 | | 250 1499 2 | 2 | 2 | | 250 1500 2 | 2 | 3 | | 250 1501 2 | 2 | | 0 | 100 1502 2 | 2 | | 1 | 100 1503 2 | 2 | | 2 | 100 1504 2 | 2 | | 3 | 100 1505 2 | 2 | | 4 | 100 1506 2 | 2 | | 5 | 100 1507 2 | 2 | | 6 | 100 1508 2 | 2 | | 7 | 100 1509 2 | 2 | | 8 | 100 1510 2 | 2 | | 9 | 100 1511(28 rows) 1512 1513select array(select row(v.a,s1.*) from (select two,four, count(*) from onek group by grouping sets(two,four) order by two,four) s1) from (values (1),(2)) v(a); 1514 array 1515--------------------------------------------------------------------------------- 1516 {"(1,0,,500)","(1,1,,500)","(1,,0,250)","(1,,1,250)","(1,,2,250)","(1,,3,250)"} 1517 {"(2,0,,500)","(2,1,,500)","(2,,0,250)","(2,,1,250)","(2,,2,250)","(2,,3,250)"} 1518(2 rows) 1519 1520-- test the knapsack 1521set enable_indexscan = false; 1522set work_mem = '64kB'; 1523explain (costs off) 1524 select unique1, 1525 count(two), count(four), count(ten), 1526 count(hundred), count(thousand), count(twothousand), 1527 count(*) 1528 from tenk1 group by grouping sets (unique1,twothousand,thousand,hundred,ten,four,two); 1529 QUERY PLAN 1530------------------------------- 1531 MixedAggregate 1532 Hash Key: two 1533 Hash Key: four 1534 Hash Key: ten 1535 Hash Key: hundred 1536 Group Key: unique1 1537 Sort Key: twothousand 1538 Group Key: twothousand 1539 Sort Key: thousand 1540 Group Key: thousand 1541 -> Sort 1542 Sort Key: unique1 1543 -> Seq Scan on tenk1 1544(13 rows) 1545 1546explain (costs off) 1547 select unique1, 1548 count(two), count(four), count(ten), 1549 count(hundred), count(thousand), count(twothousand), 1550 count(*) 1551 from tenk1 group by grouping sets (unique1,hundred,ten,four,two); 1552 QUERY PLAN 1553------------------------------- 1554 MixedAggregate 1555 Hash Key: two 1556 Hash Key: four 1557 Hash Key: ten 1558 Hash Key: hundred 1559 Group Key: unique1 1560 -> Sort 1561 Sort Key: unique1 1562 -> Seq Scan on tenk1 1563(9 rows) 1564 1565set work_mem = '384kB'; 1566explain (costs off) 1567 select unique1, 1568 count(two), count(four), count(ten), 1569 count(hundred), count(thousand), count(twothousand), 1570 count(*) 1571 from tenk1 group by grouping sets (unique1,twothousand,thousand,hundred,ten,four,two); 1572 QUERY PLAN 1573------------------------------- 1574 MixedAggregate 1575 Hash Key: two 1576 Hash Key: four 1577 Hash Key: ten 1578 Hash Key: hundred 1579 Hash Key: thousand 1580 Group Key: unique1 1581 Sort Key: twothousand 1582 Group Key: twothousand 1583 -> Sort 1584 Sort Key: unique1 1585 -> Seq Scan on tenk1 1586(12 rows) 1587 1588-- check collation-sensitive matching between grouping expressions 1589-- (similar to a check for aggregates, but there are additional code 1590-- paths for GROUPING, so check again here) 1591select v||'a', case grouping(v||'a') when 1 then 1 else 0 end, count(*) 1592 from unnest(array[1,1], array['a','b']) u(i,v) 1593 group by rollup(i, v||'a') order by 1,3; 1594 ?column? | case | count 1595----------+------+------- 1596 aa | 0 | 1 1597 ba | 0 | 1 1598 | 1 | 2 1599 | 1 | 2 1600(4 rows) 1601 1602select v||'a', case when grouping(v||'a') = 1 then 1 else 0 end, count(*) 1603 from unnest(array[1,1], array['a','b']) u(i,v) 1604 group by rollup(i, v||'a') order by 1,3; 1605 ?column? | case | count 1606----------+------+------- 1607 aa | 0 | 1 1608 ba | 0 | 1 1609 | 1 | 2 1610 | 1 | 2 1611(4 rows) 1612 1613-- end 1614