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