1-- 2-- grouping sets 3-- 4 5-- test data sources 6 7create temp view gstest1(a,b,v) 8 as values (1,1,10),(1,1,11),(1,2,12),(1,2,13),(1,3,14), 9 (2,3,15), 10 (3,3,16),(3,4,17), 11 (4,1,18),(4,1,19); 12 13create temp table gstest2 (a integer, b integer, c integer, d integer, 14 e integer, f integer, g integer, h integer); 15copy gstest2 from stdin; 161 1 1 1 1 1 1 1 171 1 1 1 1 1 1 2 181 1 1 1 1 1 2 2 191 1 1 1 1 2 2 2 201 1 1 1 2 2 2 2 211 1 1 2 2 2 2 2 221 1 2 2 2 2 2 2 231 2 2 2 2 2 2 2 242 2 2 2 2 2 2 2 25\. 26 27create temp table gstest3 (a integer, b integer, c integer, d integer); 28copy gstest3 from stdin; 291 1 1 1 302 2 2 2 31\. 32alter table gstest3 add primary key (a); 33 34create temp table gstest4(id integer, v integer, 35 unhashable_col bit(4), unsortable_col xid); 36insert into gstest4 37values (1,1,b'0000','1'), (2,2,b'0001','1'), 38 (3,4,b'0010','2'), (4,8,b'0011','2'), 39 (5,16,b'0000','2'), (6,32,b'0001','2'), 40 (7,64,b'0010','1'), (8,128,b'0011','1'); 41 42create temp table gstest_empty (a integer, b integer, v integer); 43 44create function gstest_data(v integer, out a integer, out b integer) 45 returns setof record 46 as $f$ 47 begin 48 return query select v, i from generate_series(1,3) i; 49 end; 50 $f$ language plpgsql; 51 52-- basic functionality 53 54set enable_hashagg = false; -- test hashing explicitly later 55 56-- simple rollup with multiple plain aggregates, with and without ordering 57-- (and with ordering differing from grouping) 58 59select a, b, grouping(a,b), sum(v), count(*), max(v) 60 from gstest1 group by rollup (a,b); 61select a, b, grouping(a,b), sum(v), count(*), max(v) 62 from gstest1 group by rollup (a,b) order by a,b; 63select a, b, grouping(a,b), sum(v), count(*), max(v) 64 from gstest1 group by rollup (a,b) order by b desc, a; 65select a, b, grouping(a,b), sum(v), count(*), max(v) 66 from gstest1 group by rollup (a,b) order by coalesce(a,0)+coalesce(b,0); 67 68-- various types of ordered aggs 69select a, b, grouping(a,b), 70 array_agg(v order by v), 71 string_agg(v::text, ':' order by v desc), 72 percentile_disc(0.5) within group (order by v), 73 rank(1,2,12) within group (order by a,b,v) 74 from gstest1 group by rollup (a,b) order by a,b; 75 76-- test usage of grouped columns in direct args of aggs 77select grouping(a), a, array_agg(b), 78 rank(a) within group (order by b nulls first), 79 rank(a) within group (order by b nulls last) 80 from (values (1,1),(1,4),(1,5),(3,1),(3,2)) v(a,b) 81 group by rollup (a) order by a; 82 83-- nesting with window functions 84select a, b, sum(c), sum(sum(c)) over (order by a,b) as rsum 85 from gstest2 group by rollup (a,b) order by rsum, a, b; 86 87-- nesting with grouping sets 88select sum(c) from gstest2 89 group by grouping sets((), grouping sets((), grouping sets(()))) 90 order by 1 desc; 91select sum(c) from gstest2 92 group by grouping sets((), grouping sets((), grouping sets(((a, b))))) 93 order by 1 desc; 94select sum(c) from gstest2 95 group by grouping sets(grouping sets(rollup(c), grouping sets(cube(c)))) 96 order by 1 desc; 97select sum(c) from gstest2 98 group by grouping sets(a, grouping sets(a, cube(b))) 99 order by 1 desc; 100select sum(c) from gstest2 101 group by grouping sets(grouping sets((a, (b)))) 102 order by 1 desc; 103select sum(c) from gstest2 104 group by grouping sets(grouping sets((a, b))) 105 order by 1 desc; 106select sum(c) from gstest2 107 group by grouping sets(grouping sets(a, grouping sets(a), a)) 108 order by 1 desc; 109select sum(c) from gstest2 110 group by grouping sets(grouping sets(a, grouping sets(a, grouping sets(a), ((a)), a, grouping sets(a), (a)), a)) 111 order by 1 desc; 112select sum(c) from gstest2 113 group by grouping sets((a,(a,b)), grouping sets((a,(a,b)),a)) 114 order by 1 desc; 115 116-- empty input: first is 0 rows, second 1, third 3 etc. 117select a, b, sum(v), count(*) from gstest_empty group by grouping sets ((a,b),a); 118select a, b, sum(v), count(*) from gstest_empty group by grouping sets ((a,b),()); 119select a, b, sum(v), count(*) from gstest_empty group by grouping sets ((a,b),(),(),()); 120select sum(v), count(*) from gstest_empty group by grouping sets ((),(),()); 121 122-- empty input with joins tests some important code paths 123select t1.a, t2.b, sum(t1.v), count(*) from gstest_empty t1, gstest_empty t2 124 group by grouping sets ((t1.a,t2.b),()); 125 126-- simple joins, var resolution, GROUPING on join vars 127select t1.a, t2.b, grouping(t1.a, t2.b), sum(t1.v), max(t2.a) 128 from gstest1 t1, gstest2 t2 129 group by grouping sets ((t1.a, t2.b), ()); 130 131select t1.a, t2.b, grouping(t1.a, t2.b), sum(t1.v), max(t2.a) 132 from gstest1 t1 join gstest2 t2 on (t1.a=t2.a) 133 group by grouping sets ((t1.a, t2.b), ()); 134 135select a, b, grouping(a, b), sum(t1.v), max(t2.c) 136 from gstest1 t1 join gstest2 t2 using (a,b) 137 group by grouping sets ((a, b), ()); 138 139-- check that functionally dependent cols are not nulled 140select a, d, grouping(a,b,c) 141 from gstest3 142 group by grouping sets ((a,b), (a,c)); 143 144-- check that distinct grouping columns are kept separate 145-- even if they are equal() 146explain (costs off) 147select g as alias1, g as alias2 148 from generate_series(1,3) g 149 group by alias1, rollup(alias2); 150 151select g as alias1, g as alias2 152 from generate_series(1,3) g 153 group by alias1, rollup(alias2); 154 155-- check that pulled-up subquery outputs still go to null when appropriate 156select four, x 157 from (select four, ten, 'foo'::text as x from tenk1) as t 158 group by grouping sets (four, x) 159 having x = 'foo'; 160 161select four, x || 'x' 162 from (select four, ten, 'foo'::text as x from tenk1) as t 163 group by grouping sets (four, x) 164 order by four; 165 166select (x+y)*1, sum(z) 167 from (select 1 as x, 2 as y, 3 as z) s 168 group by grouping sets (x+y, x); 169 170select x, not x as not_x, q2 from 171 (select *, q1 = 1 as x from int8_tbl i1) as t 172 group by grouping sets(x, q2) 173 order by x, q2; 174 175-- check qual push-down rules for a subquery with grouping sets 176explain (verbose, costs off) 177select * from ( 178 select 1 as x, q1, sum(q2) 179 from int8_tbl i1 180 group by grouping sets(1, 2) 181) ss 182where x = 1 and q1 = 123; 183 184select * from ( 185 select 1 as x, q1, sum(q2) 186 from int8_tbl i1 187 group by grouping sets(1, 2) 188) ss 189where x = 1 and q1 = 123; 190 191-- simple rescan tests 192 193select a, b, sum(v.x) 194 from (values (1),(2)) v(x), gstest_data(v.x) 195 group by rollup (a,b); 196 197select * 198 from (values (1),(2)) v(x), 199 lateral (select a, b, sum(v.x) from gstest_data(v.x) group by rollup (a,b)) s; 200 201-- min max optimization should still work with GROUP BY () 202explain (costs off) 203 select min(unique1) from tenk1 GROUP BY (); 204 205-- Views with GROUPING SET queries 206CREATE VIEW gstest_view AS select a, b, grouping(a,b), sum(c), count(*), max(c) 207 from gstest2 group by rollup ((a,b,c),(c,d)); 208 209select pg_get_viewdef('gstest_view'::regclass, true); 210 211-- Nested queries with 3 or more levels of nesting 212select(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); 213select(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); 214select(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); 215 216-- Combinations of operations 217select a, b, c, d from gstest2 group by rollup(a,b),grouping sets(c,d); 218select a, b from (values (1,2),(2,3)) v(a,b) group by a,b, grouping sets(a); 219 220-- Tests for chained aggregates 221select a, b, grouping(a,b), sum(v), count(*), max(v) 222 from gstest1 group by grouping sets ((a,b),(a+1,b+1),(a+2,b+2)) order by 3,6; 223select(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)); 224select(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); 225select a, b, sum(c), sum(sum(c)) over (order by a,b) as rsum 226 from gstest2 group by cube (a,b) order by rsum, a, b; 227select 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); 228select a, b, sum(v.x) 229 from (values (1),(2)) v(x), gstest_data(v.x) 230 group by cube (a,b) order by a,b; 231 232-- Test reordering of grouping sets 233explain (costs off) 234select * from gstest1 group by grouping sets((a,b,v),(v)) order by v,b,a; 235 236-- Agg level check. This query should error out. 237select (select grouping(a,b) from gstest2) from gstest2 group by a,b; 238 239--Nested queries 240select a, b, sum(c), count(*) from gstest2 group by grouping sets (rollup(a,b),a); 241 242-- HAVING queries 243select ten, sum(distinct four) from onek a 244group by grouping sets((ten,four),(ten)) 245having exists (select 1 from onek b where sum(distinct a.four) = b.four); 246 247-- Tests around pushdown of HAVING clauses, partially testing against previous bugs 248select a,count(*) from gstest2 group by rollup(a) order by a; 249select a,count(*) from gstest2 group by rollup(a) having a is distinct from 1 order by a; 250explain (costs off) 251 select a,count(*) from gstest2 group by rollup(a) having a is distinct from 1 order by a; 252 253select v.c, (select count(*) from gstest2 group by () having v.c) 254 from (values (false),(true)) v(c) order by v.c; 255explain (costs off) 256 select v.c, (select count(*) from gstest2 group by () having v.c) 257 from (values (false),(true)) v(c) order by v.c; 258 259-- HAVING with GROUPING queries 260select ten, grouping(ten) from onek 261group by grouping sets(ten) having grouping(ten) >= 0 262order by 2,1; 263select ten, grouping(ten) from onek 264group by grouping sets(ten, four) having grouping(ten) > 0 265order by 2,1; 266select ten, grouping(ten) from onek 267group by rollup(ten) having grouping(ten) > 0 268order by 2,1; 269select ten, grouping(ten) from onek 270group by cube(ten) having grouping(ten) > 0 271order by 2,1; 272select ten, grouping(ten) from onek 273group by (ten) having grouping(ten) >= 0 274order by 2,1; 275 276-- FILTER queries 277select ten, sum(distinct four) filter (where four::text ~ '123') from onek a 278group by rollup(ten); 279 280-- More rescan tests 281select * 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; 282select 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); 283 284-- Grouping on text columns 285select sum(ten) from onek group by two, rollup(four::text) order by 1; 286select sum(ten) from onek group by rollup(four::text), two order by 1; 287 288-- hashing support 289 290set enable_hashagg = true; 291 292-- failure cases 293 294select count(*) from gstest4 group by rollup(unhashable_col,unsortable_col); 295select array_agg(v order by v) from gstest4 group by grouping sets ((id,unsortable_col),(id)); 296 297-- simple cases 298 299select a, b, grouping(a,b), sum(v), count(*), max(v) 300 from gstest1 group by grouping sets ((a),(b)) order by 3,1,2; 301explain (costs off) select a, b, grouping(a,b), sum(v), count(*), max(v) 302 from gstest1 group by grouping sets ((a),(b)) order by 3,1,2; 303 304select a, b, grouping(a,b), sum(v), count(*), max(v) 305 from gstest1 group by cube(a,b) order by 3,1,2; 306explain (costs off) select a, b, grouping(a,b), sum(v), count(*), max(v) 307 from gstest1 group by cube(a,b) order by 3,1,2; 308 309-- shouldn't try and hash 310explain (costs off) 311 select a, b, grouping(a,b), array_agg(v order by v) 312 from gstest1 group by cube(a,b); 313 314-- unsortable cases 315select unsortable_col, count(*) 316 from gstest4 group by grouping sets ((unsortable_col),(unsortable_col)) 317 order by unsortable_col::text; 318 319-- mixed hashable/sortable cases 320select unhashable_col, unsortable_col, 321 grouping(unhashable_col, unsortable_col), 322 count(*), sum(v) 323 from gstest4 group by grouping sets ((unhashable_col),(unsortable_col)) 324 order by 3, 5; 325explain (costs off) 326 select unhashable_col, unsortable_col, 327 grouping(unhashable_col, unsortable_col), 328 count(*), sum(v) 329 from gstest4 group by grouping sets ((unhashable_col),(unsortable_col)) 330 order by 3,5; 331 332select unhashable_col, unsortable_col, 333 grouping(unhashable_col, unsortable_col), 334 count(*), sum(v) 335 from gstest4 group by grouping sets ((v,unhashable_col),(v,unsortable_col)) 336 order by 3,5; 337explain (costs off) 338 select unhashable_col, unsortable_col, 339 grouping(unhashable_col, unsortable_col), 340 count(*), sum(v) 341 from gstest4 group by grouping sets ((v,unhashable_col),(v,unsortable_col)) 342 order by 3,5; 343 344-- empty input: first is 0 rows, second 1, third 3 etc. 345select a, b, sum(v), count(*) from gstest_empty group by grouping sets ((a,b),a); 346explain (costs off) 347 select a, b, sum(v), count(*) from gstest_empty group by grouping sets ((a,b),a); 348select a, b, sum(v), count(*) from gstest_empty group by grouping sets ((a,b),()); 349select a, b, sum(v), count(*) from gstest_empty group by grouping sets ((a,b),(),(),()); 350explain (costs off) 351 select a, b, sum(v), count(*) from gstest_empty group by grouping sets ((a,b),(),(),()); 352select sum(v), count(*) from gstest_empty group by grouping sets ((),(),()); 353explain (costs off) 354 select sum(v), count(*) from gstest_empty group by grouping sets ((),(),()); 355 356-- check that functionally dependent cols are not nulled 357select a, d, grouping(a,b,c) 358 from gstest3 359 group by grouping sets ((a,b), (a,c)); 360explain (costs off) 361 select a, d, grouping(a,b,c) 362 from gstest3 363 group by grouping sets ((a,b), (a,c)); 364 365-- simple rescan tests 366 367select a, b, sum(v.x) 368 from (values (1),(2)) v(x), gstest_data(v.x) 369 group by grouping sets (a,b) 370 order by 1, 2, 3; 371explain (costs off) 372 select a, b, sum(v.x) 373 from (values (1),(2)) v(x), gstest_data(v.x) 374 group by grouping sets (a,b) 375 order by 3, 1, 2; 376select * 377 from (values (1),(2)) v(x), 378 lateral (select a, b, sum(v.x) from gstest_data(v.x) group by grouping sets (a,b)) s; 379explain (costs off) 380 select * 381 from (values (1),(2)) v(x), 382 lateral (select a, b, sum(v.x) from gstest_data(v.x) group by grouping sets (a,b)) s; 383 384-- Tests for chained aggregates 385select a, b, grouping(a,b), sum(v), count(*), max(v) 386 from gstest1 group by grouping sets ((a,b),(a+1,b+1),(a+2,b+2)) order by 3,6; 387explain (costs off) 388 select a, b, grouping(a,b), sum(v), count(*), max(v) 389 from gstest1 group by grouping sets ((a,b),(a+1,b+1),(a+2,b+2)) order by 3,6; 390select a, b, sum(c), sum(sum(c)) over (order by a,b) as rsum 391 from gstest2 group by cube (a,b) order by rsum, a, b; 392explain (costs off) 393 select a, b, sum(c), sum(sum(c)) over (order by a,b) as rsum 394 from gstest2 group by cube (a,b) order by rsum, a, b; 395select a, b, sum(v.x) 396 from (values (1),(2)) v(x), gstest_data(v.x) 397 group by cube (a,b) order by a,b; 398explain (costs off) 399 select a, b, sum(v.x) 400 from (values (1),(2)) v(x), gstest_data(v.x) 401 group by cube (a,b) order by a,b; 402 403-- Verify that we correctly handle the child node returning a 404-- non-minimal slot, which happens if the input is pre-sorted, 405-- e.g. due to an index scan. 406BEGIN; 407SET LOCAL enable_hashagg = false; 408EXPLAIN (COSTS OFF) SELECT a, b, count(*), max(a), max(b) FROM gstest3 GROUP BY GROUPING SETS(a, b,()) ORDER BY a, b; 409SELECT a, b, count(*), max(a), max(b) FROM gstest3 GROUP BY GROUPING SETS(a, b,()) ORDER BY a, b; 410SET LOCAL enable_seqscan = false; 411EXPLAIN (COSTS OFF) SELECT a, b, count(*), max(a), max(b) FROM gstest3 GROUP BY GROUPING SETS(a, b,()) ORDER BY a, b; 412SELECT a, b, count(*), max(a), max(b) FROM gstest3 GROUP BY GROUPING SETS(a, b,()) ORDER BY a, b; 413COMMIT; 414 415-- More rescan tests 416select * 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; 417select 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); 418 419-- Rescan logic changes when there are no empty grouping sets, so test 420-- that too: 421select * 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; 422select 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); 423 424-- test the knapsack 425 426set enable_indexscan = false; 427set work_mem = '64kB'; 428explain (costs off) 429 select unique1, 430 count(two), count(four), count(ten), 431 count(hundred), count(thousand), count(twothousand), 432 count(*) 433 from tenk1 group by grouping sets (unique1,twothousand,thousand,hundred,ten,four,two); 434explain (costs off) 435 select unique1, 436 count(two), count(four), count(ten), 437 count(hundred), count(thousand), count(twothousand), 438 count(*) 439 from tenk1 group by grouping sets (unique1,hundred,ten,four,two); 440 441set work_mem = '384kB'; 442explain (costs off) 443 select unique1, 444 count(two), count(four), count(ten), 445 count(hundred), count(thousand), count(twothousand), 446 count(*) 447 from tenk1 group by grouping sets (unique1,twothousand,thousand,hundred,ten,four,two); 448 449-- check collation-sensitive matching between grouping expressions 450-- (similar to a check for aggregates, but there are additional code 451-- paths for GROUPING, so check again here) 452 453select v||'a', case grouping(v||'a') when 1 then 1 else 0 end, count(*) 454 from unnest(array[1,1], array['a','b']) u(i,v) 455 group by rollup(i, v||'a') order by 1,3; 456select v||'a', case when grouping(v||'a') = 1 then 1 else 0 end, count(*) 457 from unnest(array[1,1], array['a','b']) u(i,v) 458 group by rollup(i, v||'a') order by 1,3; 459 460-- end 461