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 gstest_empty (a integer, b integer, v integer); 35 36create function gstest_data(v integer, out a integer, out b integer) 37 returns setof record 38 as $f$ 39 begin 40 return query select v, i from generate_series(1,3) i; 41 end; 42 $f$ language plpgsql; 43 44-- basic functionality 45 46-- simple rollup with multiple plain aggregates, with and without ordering 47-- (and with ordering differing from grouping) 48select a, b, grouping(a,b), sum(v), count(*), max(v) 49 from gstest1 group by rollup (a,b); 50select a, b, grouping(a,b), sum(v), count(*), max(v) 51 from gstest1 group by rollup (a,b) order by a,b; 52select a, b, grouping(a,b), sum(v), count(*), max(v) 53 from gstest1 group by rollup (a,b) order by b desc, a; 54select a, b, grouping(a,b), sum(v), count(*), max(v) 55 from gstest1 group by rollup (a,b) order by coalesce(a,0)+coalesce(b,0); 56 57-- various types of ordered aggs 58select a, b, grouping(a,b), 59 array_agg(v order by v), 60 string_agg(v::text, ':' order by v desc), 61 percentile_disc(0.5) within group (order by v), 62 rank(1,2,12) within group (order by a,b,v) 63 from gstest1 group by rollup (a,b) order by a,b; 64 65-- test usage of grouped columns in direct args of aggs 66select grouping(a), a, array_agg(b), 67 rank(a) within group (order by b nulls first), 68 rank(a) within group (order by b nulls last) 69 from (values (1,1),(1,4),(1,5),(3,1),(3,2)) v(a,b) 70 group by rollup (a) order by a; 71 72-- nesting with window functions 73select a, b, sum(c), sum(sum(c)) over (order by a,b) as rsum 74 from gstest2 group by rollup (a,b) order by rsum, a, b; 75 76-- nesting with grouping sets 77select sum(c) from gstest2 78 group by grouping sets((), grouping sets((), grouping sets(()))) 79 order by 1 desc; 80select sum(c) from gstest2 81 group by grouping sets((), grouping sets((), grouping sets(((a, b))))) 82 order by 1 desc; 83select sum(c) from gstest2 84 group by grouping sets(grouping sets(rollup(c), grouping sets(cube(c)))) 85 order by 1 desc; 86select sum(c) from gstest2 87 group by grouping sets(a, grouping sets(a, cube(b))) 88 order by 1 desc; 89select sum(c) from gstest2 90 group by grouping sets(grouping sets((a, (b)))) 91 order by 1 desc; 92select sum(c) from gstest2 93 group by grouping sets(grouping sets((a, b))) 94 order by 1 desc; 95select sum(c) from gstest2 96 group by grouping sets(grouping sets(a, grouping sets(a), a)) 97 order by 1 desc; 98select sum(c) from gstest2 99 group by grouping sets(grouping sets(a, grouping sets(a, grouping sets(a), ((a)), a, grouping sets(a), (a)), a)) 100 order by 1 desc; 101select sum(c) from gstest2 102 group by grouping sets((a,(a,b)), grouping sets((a,(a,b)),a)) 103 order by 1 desc; 104 105-- empty input: first is 0 rows, second 1, third 3 etc. 106select a, b, sum(v), count(*) from gstest_empty group by grouping sets ((a,b),a); 107select a, b, sum(v), count(*) from gstest_empty group by grouping sets ((a,b),()); 108select a, b, sum(v), count(*) from gstest_empty group by grouping sets ((a,b),(),(),()); 109select sum(v), count(*) from gstest_empty group by grouping sets ((),(),()); 110 111-- empty input with joins tests some important code paths 112select t1.a, t2.b, sum(t1.v), count(*) from gstest_empty t1, gstest_empty t2 113 group by grouping sets ((t1.a,t2.b),()); 114 115-- simple joins, var resolution, GROUPING on join vars 116select t1.a, t2.b, grouping(t1.a, t2.b), sum(t1.v), max(t2.a) 117 from gstest1 t1, gstest2 t2 118 group by grouping sets ((t1.a, t2.b), ()); 119 120select t1.a, t2.b, grouping(t1.a, t2.b), sum(t1.v), max(t2.a) 121 from gstest1 t1 join gstest2 t2 on (t1.a=t2.a) 122 group by grouping sets ((t1.a, t2.b), ()); 123 124select a, b, grouping(a, b), sum(t1.v), max(t2.c) 125 from gstest1 t1 join gstest2 t2 using (a,b) 126 group by grouping sets ((a, b), ()); 127 128-- check that functionally dependent cols are not nulled 129select a, d, grouping(a,b,c) 130 from gstest3 131 group by grouping sets ((a,b), (a,c)); 132 133-- check that distinct grouping columns are kept separate 134-- even if they are equal() 135explain (costs off) 136select g as alias1, g as alias2 137 from generate_series(1,3) g 138 group by alias1, rollup(alias2); 139 140select g as alias1, g as alias2 141 from generate_series(1,3) g 142 group by alias1, rollup(alias2); 143 144-- check that pulled-up subquery outputs still go to null when appropriate 145select four, x 146 from (select four, ten, 'foo'::text as x from tenk1) as t 147 group by grouping sets (four, x) 148 having x = 'foo'; 149 150select four, x || 'x' 151 from (select four, ten, 'foo'::text as x from tenk1) as t 152 group by grouping sets (four, x) 153 order by four; 154 155select (x+y)*1, sum(z) 156 from (select 1 as x, 2 as y, 3 as z) s 157 group by grouping sets (x+y, x); 158 159select x, not x as not_x, q2 from 160 (select *, q1 = 1 as x from int8_tbl i1) as t 161 group by grouping sets(x, q2) 162 order by x, q2; 163 164-- check qual push-down rules for a subquery with grouping sets 165explain (verbose, costs off) 166select * from ( 167 select 1 as x, q1, sum(q2) 168 from int8_tbl i1 169 group by grouping sets(1, 2) 170) ss 171where x = 1 and q1 = 123; 172 173select * from ( 174 select 1 as x, q1, sum(q2) 175 from int8_tbl i1 176 group by grouping sets(1, 2) 177) ss 178where x = 1 and q1 = 123; 179 180-- simple rescan tests 181 182select a, b, sum(v.x) 183 from (values (1),(2)) v(x), gstest_data(v.x) 184 group by rollup (a,b); 185 186select * 187 from (values (1),(2)) v(x), 188 lateral (select a, b, sum(v.x) from gstest_data(v.x) group by rollup (a,b)) s; 189 190-- min max optimisation should still work with GROUP BY () 191explain (costs off) 192 select min(unique1) from tenk1 GROUP BY (); 193 194-- Views with GROUPING SET queries 195CREATE VIEW gstest_view AS select a, b, grouping(a,b), sum(c), count(*), max(c) 196 from gstest2 group by rollup ((a,b,c),(c,d)); 197 198select pg_get_viewdef('gstest_view'::regclass, true); 199 200-- Nested queries with 3 or more levels of nesting 201select(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); 202select(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); 203select(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); 204 205-- Combinations of operations 206select a, b, c, d from gstest2 group by rollup(a,b),grouping sets(c,d); 207select a, b from (values (1,2),(2,3)) v(a,b) group by a,b, grouping sets(a); 208 209-- Tests for chained aggregates 210select a, b, grouping(a,b), sum(v), count(*), max(v) 211 from gstest1 group by grouping sets ((a,b),(a+1,b+1),(a+2,b+2)); 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+1),(f+1)); 213select(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); 214select a, b, sum(c), sum(sum(c)) over (order by a,b) as rsum 215 from gstest2 group by cube (a,b) order by rsum, a, b; 216select 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); 217select a, b, sum(v.x) 218 from (values (1),(2)) v(x), gstest_data(v.x) 219 group by cube (a,b) order by a,b; 220 221-- Test reordering of grouping sets 222explain (costs off) 223select * from gstest1 group by grouping sets((a,b,v),(v)) order by v,b,a; 224 225-- Agg level check. This query should error out. 226select (select grouping(a,b) from gstest2) from gstest2 group by a,b; 227 228--Nested queries 229select a, b, sum(c), count(*) from gstest2 group by grouping sets (rollup(a,b),a); 230 231-- HAVING queries 232select ten, sum(distinct four) from onek a 233group by grouping sets((ten,four),(ten)) 234having exists (select 1 from onek b where sum(distinct a.four) = b.four); 235 236-- Tests around pushdown of HAVING clauses, partially testing against previous bugs 237select a,count(*) from gstest2 group by rollup(a) order by a; 238select a,count(*) from gstest2 group by rollup(a) having a is distinct from 1 order by a; 239explain (costs off) 240 select a,count(*) from gstest2 group by rollup(a) having a is distinct from 1 order by a; 241 242select v.c, (select count(*) from gstest2 group by () having v.c) 243 from (values (false),(true)) v(c) order by v.c; 244explain (costs off) 245 select v.c, (select count(*) from gstest2 group by () having v.c) 246 from (values (false),(true)) v(c) order by v.c; 247 248-- HAVING with GROUPING queries 249select ten, grouping(ten) from onek 250group by grouping sets(ten) having grouping(ten) >= 0 251order by 2,1; 252select ten, grouping(ten) from onek 253group by grouping sets(ten, four) having grouping(ten) > 0 254order by 2,1; 255select ten, grouping(ten) from onek 256group by rollup(ten) having grouping(ten) > 0 257order by 2,1; 258select ten, grouping(ten) from onek 259group by cube(ten) having grouping(ten) > 0 260order by 2,1; 261select ten, grouping(ten) from onek 262group by (ten) having grouping(ten) >= 0 263order by 2,1; 264 265-- FILTER queries 266select ten, sum(distinct four) filter (where four::text ~ '123') from onek a 267group by rollup(ten); 268 269-- More rescan tests 270select * 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; 271select 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); 272 273-- Grouping on text columns 274select sum(ten) from onek group by two, rollup(four::text) order by 1; 275select sum(ten) from onek group by rollup(four::text), two order by 1; 276 277-- check collation-sensitive matching between grouping expressions 278-- (similar to a check for aggregates, but there are additional code 279-- paths for GROUPING, so check again here) 280 281select v||'a', case grouping(v||'a') when 1 then 1 else 0 end, count(*) 282 from unnest(array[1,1], array['a','b']) u(i,v) 283 group by rollup(i, v||'a') order by 1,3; 284select v||'a', case when grouping(v||'a') = 1 then 1 else 0 end, count(*) 285 from unnest(array[1,1], array['a','b']) u(i,v) 286 group by rollup(i, v||'a') order by 1,3; 287 288-- end 289