Change publication's publish_generated_columns option type to enum.
[pgsql.git] / src / test / regress / sql / groupingsets.sql
blob21cd31219406c91e1be18cacdf03584f430db183
1 --
2 -- grouping sets
3 --
5 -- test data sources
7 create 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);
13 create temp table gstest2 (a integer, b integer, c integer, d integer,
14                            e integer, f integer, g integer, h integer);
15 copy gstest2 from stdin;
16 1       1       1       1       1       1       1       1
17 1       1       1       1       1       1       1       2
18 1       1       1       1       1       1       2       2
19 1       1       1       1       1       2       2       2
20 1       1       1       1       2       2       2       2
21 1       1       1       2       2       2       2       2
22 1       1       2       2       2       2       2       2
23 1       2       2       2       2       2       2       2
24 2       2       2       2       2       2       2       2
27 create temp table gstest3 (a integer, b integer, c integer, d integer);
28 copy gstest3 from stdin;
29 1       1       1       1
30 2       2       2       2
32 alter table gstest3 add primary key (a);
34 create temp table gstest4(id integer, v integer,
35                           unhashable_col bit(4), unsortable_col xid);
36 insert into gstest4
37 values (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');
42 create temp table gstest_empty (a integer, b integer, v integer);
44 create 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;
52 -- basic functionality
54 set enable_hashagg = false;  -- test hashing explicitly later
56 -- simple rollup with multiple plain aggregates, with and without ordering
57 -- (and with ordering differing from grouping)
59 select a, b, grouping(a,b), sum(v), count(*), max(v)
60   from gstest1 group by rollup (a,b);
61 select a, b, grouping(a,b), sum(v), count(*), max(v)
62   from gstest1 group by rollup (a,b) order by a,b;
63 select a, b, grouping(a,b), sum(v), count(*), max(v)
64   from gstest1 group by rollup (a,b) order by b desc, a;
65 select 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);
68 -- various types of ordered aggs
69 select 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;
76 -- test usage of grouped columns in direct args of aggs
77 select 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;
83 -- nesting with window functions
84 select 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;
87 -- nesting with grouping sets
88 select sum(c) from gstest2
89   group by grouping sets((), grouping sets((), grouping sets(())))
90   order by 1 desc;
91 select sum(c) from gstest2
92   group by grouping sets((), grouping sets((), grouping sets(((a, b)))))
93   order by 1 desc;
94 select sum(c) from gstest2
95   group by grouping sets(grouping sets(rollup(c), grouping sets(cube(c))))
96   order by 1 desc;
97 select sum(c) from gstest2
98   group by grouping sets(a, grouping sets(a, cube(b)))
99   order by 1 desc;
100 select sum(c) from gstest2
101   group by grouping sets(grouping sets((a, (b))))
102   order by 1 desc;
103 select sum(c) from gstest2
104   group by grouping sets(grouping sets((a, b)))
105   order by 1 desc;
106 select sum(c) from gstest2
107   group by grouping sets(grouping sets(a, grouping sets(a), a))
108   order by 1 desc;
109 select 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;
112 select sum(c) from gstest2
113   group by grouping sets((a,(a,b)), grouping sets((a,(a,b)),a))
114   order by 1 desc;
116 -- empty input: first is 0 rows, second 1, third 3 etc.
117 select a, b, sum(v), count(*) from gstest_empty group by grouping sets ((a,b),a);
118 select a, b, sum(v), count(*) from gstest_empty group by grouping sets ((a,b),());
119 select a, b, sum(v), count(*) from gstest_empty group by grouping sets ((a,b),(),(),());
120 select sum(v), count(*) from gstest_empty group by grouping sets ((),(),());
122 -- empty input with joins tests some important code paths
123 select t1.a, t2.b, sum(t1.v), count(*) from gstest_empty t1, gstest_empty t2
124  group by grouping sets ((t1.a,t2.b),());
126 -- simple joins, var resolution, GROUPING on join vars
127 select 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), ());
131 select 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), ());
135 select 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), ());
139 -- check that functionally dependent cols are not nulled
140 select a, d, grouping(a,b,c)
141   from gstest3
142  group by grouping sets ((a,b), (a,c));
144 -- check that distinct grouping columns are kept separate
145 -- even if they are equal()
146 explain (costs off)
147 select g as alias1, g as alias2
148   from generate_series(1,3) g
149  group by alias1, rollup(alias2);
151 select g as alias1, g as alias2
152   from generate_series(1,3) g
153  group by alias1, rollup(alias2);
155 -- check that pulled-up subquery outputs still go to null when appropriate
156 select 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';
161 select 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;
166 select (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);
170 select 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;
175 -- check qual push-down rules for a subquery with grouping sets
176 explain (verbose, costs off)
177 select * from (
178   select 1 as x, q1, sum(q2)
179   from int8_tbl i1
180   group by grouping sets(1, 2)
181 ) ss
182 where x = 1 and q1 = 123;
184 select * from (
185   select 1 as x, q1, sum(q2)
186   from int8_tbl i1
187   group by grouping sets(1, 2)
188 ) ss
189 where x = 1 and q1 = 123;
191 -- check handling of pulled-up SubPlan in GROUPING() argument (bug #17479)
192 explain (verbose, costs off)
193 select grouping(ss.x)
194 from int8_tbl i1
195 cross join lateral (select (select i1.q1) as x) ss
196 group by ss.x;
198 select grouping(ss.x)
199 from int8_tbl i1
200 cross join lateral (select (select i1.q1) as x) ss
201 group by ss.x;
203 explain (verbose, costs off)
204 select (select grouping(ss.x))
205 from int8_tbl i1
206 cross join lateral (select (select i1.q1) as x) ss
207 group by ss.x;
209 select (select grouping(ss.x))
210 from int8_tbl i1
211 cross join lateral (select (select i1.q1) as x) ss
212 group by ss.x;
214 -- simple rescan tests
216 select a, b, sum(v.x)
217   from (values (1),(2)) v(x), gstest_data(v.x)
218  group by rollup (a,b);
220 select *
221   from (values (1),(2)) v(x),
222        lateral (select a, b, sum(v.x) from gstest_data(v.x) group by rollup (a,b)) s;
224 -- min max optimization should still work with GROUP BY ()
225 explain (costs off)
226   select min(unique1) from tenk1 GROUP BY ();
228 -- Views with GROUPING SET queries
229 CREATE VIEW gstest_view AS select a, b, grouping(a,b), sum(c), count(*), max(c)
230   from gstest2 group by rollup ((a,b,c),(c,d));
232 select pg_get_viewdef('gstest_view'::regclass, true);
234 -- Nested queries with 3 or more levels of nesting
235 select(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);
236 select(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);
237 select(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);
239 -- Combinations of operations
240 select a, b, c, d from gstest2 group by rollup(a,b),grouping sets(c,d);
241 select a, b from (values (1,2),(2,3)) v(a,b) group by a,b, grouping sets(a);
243 -- Tests for chained aggregates
244 select a, b, grouping(a,b), sum(v), count(*), max(v)
245   from gstest1 group by grouping sets ((a,b),(a+1,b+1),(a+2,b+2)) order by 3,6;
246 select(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));
247 select(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);
248 select a, b, sum(c), sum(sum(c)) over (order by a,b) as rsum
249   from gstest2 group by cube (a,b) order by rsum, a, b;
250 select 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);
251 select a, b, sum(v.x)
252   from (values (1),(2)) v(x), gstest_data(v.x)
253  group by cube (a,b) order by a,b;
255 -- Test reordering of grouping sets
256 explain (costs off)
257 select * from gstest1 group by grouping sets((a,b,v),(v)) order by v,b,a;
259 -- Agg level check. This query should error out.
260 select (select grouping(a,b) from gstest2) from gstest2 group by a,b;
262 --Nested queries
263 select a, b, sum(c), count(*) from gstest2 group by grouping sets (rollup(a,b),a);
265 -- HAVING queries
266 select ten, sum(distinct four) from onek a
267 group by grouping sets((ten,four),(ten))
268 having exists (select 1 from onek b where sum(distinct a.four) = b.four);
270 -- Tests around pushdown of HAVING clauses, partially testing against previous bugs
271 select a,count(*) from gstest2 group by rollup(a) order by a;
272 select a,count(*) from gstest2 group by rollup(a) having a is distinct from 1 order by a;
273 explain (costs off)
274   select a,count(*) from gstest2 group by rollup(a) having a is distinct from 1 order by a;
276 select v.c, (select count(*) from gstest2 group by () having v.c)
277   from (values (false),(true)) v(c) order by v.c;
278 explain (costs off)
279   select v.c, (select count(*) from gstest2 group by () having v.c)
280     from (values (false),(true)) v(c) order by v.c;
282 -- test pushdown of HAVING clause that does not reference any columns that are nullable by grouping sets
283 explain (costs off)
284 select a, b, count(*) from gstest2 group by grouping sets ((a, b), (a)) having a > 1 and b > 1;
285 select a, b, count(*) from gstest2 group by grouping sets ((a, b), (a)) having a > 1 and b > 1;
287 -- HAVING with GROUPING queries
288 select ten, grouping(ten) from onek
289 group by grouping sets(ten) having grouping(ten) >= 0
290 order by 2,1;
291 select ten, grouping(ten) from onek
292 group by grouping sets(ten, four) having grouping(ten) > 0
293 order by 2,1;
294 select ten, grouping(ten) from onek
295 group by rollup(ten) having grouping(ten) > 0
296 order by 2,1;
297 select ten, grouping(ten) from onek
298 group by cube(ten) having grouping(ten) > 0
299 order by 2,1;
300 select ten, grouping(ten) from onek
301 group by (ten) having grouping(ten) >= 0
302 order by 2,1;
304 -- FILTER queries
305 select ten, sum(distinct four) filter (where four::text ~ '123') from onek a
306 group by rollup(ten);
308 -- More rescan tests
309 select * 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;
310 select 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);
312 -- Grouping on text columns
313 select sum(ten) from onek group by two, rollup(four::text) order by 1;
314 select sum(ten) from onek group by rollup(four::text), two order by 1;
316 -- hashing support
318 set enable_hashagg = true;
320 -- failure cases
322 select count(*) from gstest4 group by rollup(unhashable_col,unsortable_col);
323 select array_agg(v order by v) from gstest4 group by grouping sets ((id,unsortable_col),(id));
325 -- simple cases
327 select a, b, grouping(a,b), sum(v), count(*), max(v)
328   from gstest1 group by grouping sets ((a),(b)) order by 3,1,2;
329 explain (costs off) select a, b, grouping(a,b), sum(v), count(*), max(v)
330   from gstest1 group by grouping sets ((a),(b)) order by 3,1,2;
332 select a, b, grouping(a,b), sum(v), count(*), max(v)
333   from gstest1 group by cube(a,b) order by 3,1,2;
334 explain (costs off) select a, b, grouping(a,b), sum(v), count(*), max(v)
335   from gstest1 group by cube(a,b) order by 3,1,2;
337 -- shouldn't try and hash
338 explain (costs off)
339   select a, b, grouping(a,b), array_agg(v order by v)
340     from gstest1 group by cube(a,b);
342 -- unsortable cases
343 select unsortable_col, count(*)
344   from gstest4 group by grouping sets ((unsortable_col),(unsortable_col))
345   order by unsortable_col::text;
347 -- mixed hashable/sortable cases
348 select unhashable_col, unsortable_col,
349        grouping(unhashable_col, unsortable_col),
350        count(*), sum(v)
351   from gstest4 group by grouping sets ((unhashable_col),(unsortable_col))
352  order by 3, 5;
353 explain (costs off)
354   select unhashable_col, unsortable_col,
355          grouping(unhashable_col, unsortable_col),
356          count(*), sum(v)
357     from gstest4 group by grouping sets ((unhashable_col),(unsortable_col))
358    order by 3,5;
360 select unhashable_col, unsortable_col,
361        grouping(unhashable_col, unsortable_col),
362        count(*), sum(v)
363   from gstest4 group by grouping sets ((v,unhashable_col),(v,unsortable_col))
364  order by 3,5;
365 explain (costs off)
366   select unhashable_col, unsortable_col,
367          grouping(unhashable_col, unsortable_col),
368          count(*), sum(v)
369     from gstest4 group by grouping sets ((v,unhashable_col),(v,unsortable_col))
370    order by 3,5;
372 -- empty input: first is 0 rows, second 1, third 3 etc.
373 select a, b, sum(v), count(*) from gstest_empty group by grouping sets ((a,b),a);
374 explain (costs off)
375   select a, b, sum(v), count(*) from gstest_empty group by grouping sets ((a,b),a);
376 select a, b, sum(v), count(*) from gstest_empty group by grouping sets ((a,b),());
377 select a, b, sum(v), count(*) from gstest_empty group by grouping sets ((a,b),(),(),());
378 explain (costs off)
379   select a, b, sum(v), count(*) from gstest_empty group by grouping sets ((a,b),(),(),());
380 select sum(v), count(*) from gstest_empty group by grouping sets ((),(),());
381 explain (costs off)
382   select sum(v), count(*) from gstest_empty group by grouping sets ((),(),());
384 -- check that functionally dependent cols are not nulled
385 select a, d, grouping(a,b,c)
386   from gstest3
387  group by grouping sets ((a,b), (a,c));
388 explain (costs off)
389   select a, d, grouping(a,b,c)
390     from gstest3
391    group by grouping sets ((a,b), (a,c));
393 -- simple rescan tests
395 select a, b, sum(v.x)
396   from (values (1),(2)) v(x), gstest_data(v.x)
397  group by grouping sets (a,b)
398  order by 1, 2, 3;
399 explain (costs off)
400   select a, b, sum(v.x)
401     from (values (1),(2)) v(x), gstest_data(v.x)
402    group by grouping sets (a,b)
403    order by 3, 1, 2;
404 select *
405   from (values (1),(2)) v(x),
406        lateral (select a, b, sum(v.x) from gstest_data(v.x) group by grouping sets (a,b)) s;
407 explain (costs off)
408   select *
409     from (values (1),(2)) v(x),
410          lateral (select a, b, sum(v.x) from gstest_data(v.x) group by grouping sets (a,b)) s;
412 -- Tests for chained aggregates
413 select a, b, grouping(a,b), sum(v), count(*), max(v)
414   from gstest1 group by grouping sets ((a,b),(a+1,b+1),(a+2,b+2)) order by 3,6;
415 explain (costs off)
416   select a, b, grouping(a,b), sum(v), count(*), max(v)
417     from gstest1 group by grouping sets ((a,b),(a+1,b+1),(a+2,b+2)) order by 3,6;
418 select a, b, sum(c), sum(sum(c)) over (order by a,b) as rsum
419   from gstest2 group by cube (a,b) order by rsum, a, b;
420 explain (costs off)
421   select a, b, sum(c), sum(sum(c)) over (order by a,b) as rsum
422     from gstest2 group by cube (a,b) order by rsum, a, b;
423 select a, b, sum(v.x)
424   from (values (1),(2)) v(x), gstest_data(v.x)
425  group by cube (a,b) order by a,b;
426 explain (costs off)
427   select a, b, sum(v.x)
428     from (values (1),(2)) v(x), gstest_data(v.x)
429    group by cube (a,b) order by a,b;
431 -- Verify that we correctly handle the child node returning a
432 -- non-minimal slot, which happens if the input is pre-sorted,
433 -- e.g. due to an index scan.
434 BEGIN;
435 SET LOCAL enable_hashagg = false;
436 EXPLAIN (COSTS OFF) SELECT a, b, count(*), max(a), max(b) FROM gstest3 GROUP BY GROUPING SETS(a, b,()) ORDER BY a, b;
437 SELECT a, b, count(*), max(a), max(b) FROM gstest3 GROUP BY GROUPING SETS(a, b,()) ORDER BY a, b;
438 SET LOCAL enable_seqscan = false;
439 EXPLAIN (COSTS OFF) SELECT a, b, count(*), max(a), max(b) FROM gstest3 GROUP BY GROUPING SETS(a, b,()) ORDER BY a, b;
440 SELECT a, b, count(*), max(a), max(b) FROM gstest3 GROUP BY GROUPING SETS(a, b,()) ORDER BY a, b;
441 COMMIT;
443 -- More rescan tests
444 select * 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;
445 select 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);
447 -- Rescan logic changes when there are no empty grouping sets, so test
448 -- that too:
449 select * 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;
450 select 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);
452 -- test the knapsack
454 set enable_indexscan = false;
455 set hash_mem_multiplier = 1.0;
456 set work_mem = '64kB';
457 explain (costs off)
458   select unique1,
459          count(two), count(four), count(ten),
460          count(hundred), count(thousand), count(twothousand),
461          count(*)
462     from tenk1 group by grouping sets (unique1,twothousand,thousand,hundred,ten,four,two);
463 explain (costs off)
464   select unique1,
465          count(two), count(four), count(ten),
466          count(hundred), count(thousand), count(twothousand),
467          count(*)
468     from tenk1 group by grouping sets (unique1,hundred,ten,four,two);
470 set work_mem = '384kB';
471 explain (costs off)
472   select unique1,
473          count(two), count(four), count(ten),
474          count(hundred), count(thousand), count(twothousand),
475          count(*)
476     from tenk1 group by grouping sets (unique1,twothousand,thousand,hundred,ten,four,two);
478 -- check collation-sensitive matching between grouping expressions
479 -- (similar to a check for aggregates, but there are additional code
480 -- paths for GROUPING, so check again here)
482 select v||'a', case grouping(v||'a') when 1 then 1 else 0 end, count(*)
483   from unnest(array[1,1], array['a','b']) u(i,v)
484  group by rollup(i, v||'a') order by 1,3;
485 select v||'a', case when grouping(v||'a') = 1 then 1 else 0 end, count(*)
486   from unnest(array[1,1], array['a','b']) u(i,v)
487  group by rollup(i, v||'a') order by 1,3;
489 -- Bug #16784
490 create table bug_16784(i int, j int);
491 analyze bug_16784;
492 alter table bug_16784 set (autovacuum_enabled = 'false');
493 update pg_class set reltuples = 10 where relname='bug_16784';
495 insert into bug_16784 select g/10, g from generate_series(1,40) g;
497 set work_mem='64kB';
498 set enable_sort = false;
500 select * from
501   (values (1),(2)) v(a),
502   lateral (select a, i, j, count(*) from
503              bug_16784 group by cube(i,j)) s
504   order by v.a, i, j;
507 -- Compare results between plans using sorting and plans using hash
508 -- aggregation. Force spilling in both cases by setting work_mem low
509 -- and altering the statistics.
512 create table gs_data_1 as
513 select g%1000 as g1000, g%100 as g100, g%10 as g10, g
514    from generate_series(0,1999) g;
516 analyze gs_data_1;
517 alter table gs_data_1 set (autovacuum_enabled = 'false');
518 update pg_class set reltuples = 10 where relname='gs_data_1';
520 set work_mem='64kB';
522 -- Produce results with sorting.
524 set enable_sort = true;
525 set enable_hashagg = false;
526 set jit_above_cost = 0;
528 explain (costs off)
529 select g100, g10, sum(g::numeric), count(*), max(g::text)
530 from gs_data_1 group by cube (g1000, g100,g10);
532 create table gs_group_1 as
533 select g100, g10, sum(g::numeric), count(*), max(g::text)
534 from gs_data_1 group by cube (g1000, g100,g10);
536 -- Produce results with hash aggregation.
538 set enable_hashagg = true;
539 set enable_sort = false;
541 explain (costs off)
542 select g100, g10, sum(g::numeric), count(*), max(g::text)
543 from gs_data_1 group by cube (g1000, g100,g10);
545 create table gs_hash_1 as
546 select g100, g10, sum(g::numeric), count(*), max(g::text)
547 from gs_data_1 group by cube (g1000, g100,g10);
549 set enable_sort = true;
550 set work_mem to default;
551 set hash_mem_multiplier to default;
553 -- Compare results
555 (select * from gs_hash_1 except select * from gs_group_1)
556   union all
557 (select * from gs_group_1 except select * from gs_hash_1);
559 drop table gs_group_1;
560 drop table gs_hash_1;
562 -- GROUP BY DISTINCT
564 -- "normal" behavior...
565 select a, b, c
566 from (values (1, 2, 3), (4, null, 6), (7, 8, 9)) as t (a, b, c)
567 group by all rollup(a, b), rollup(a, c)
568 order by a, b, c;
570 -- ...which is also the default
571 select a, b, c
572 from (values (1, 2, 3), (4, null, 6), (7, 8, 9)) as t (a, b, c)
573 group by rollup(a, b), rollup(a, c)
574 order by a, b, c;
576 -- "group by distinct" behavior...
577 select a, b, c
578 from (values (1, 2, 3), (4, null, 6), (7, 8, 9)) as t (a, b, c)
579 group by distinct rollup(a, b), rollup(a, c)
580 order by a, b, c;
582 -- ...which is not the same as "select distinct"
583 select distinct a, b, c
584 from (values (1, 2, 3), (4, null, 6), (7, 8, 9)) as t (a, b, c)
585 group by rollup(a, b), rollup(a, c)
586 order by a, b, c;
588 -- test handling of outer GroupingFunc within subqueries
589 explain (costs off)
590 select (select grouping(v1)) from (values ((select 1))) v(v1) group by cube(v1);
591 select (select grouping(v1)) from (values ((select 1))) v(v1) group by cube(v1);
593 explain (costs off)
594 select (select grouping(v1)) from (values ((select 1))) v(v1) group by v1;
595 select (select grouping(v1)) from (values ((select 1))) v(v1) group by v1;
597 -- test handling of subqueries in grouping sets
598 create temp table gstest5(id integer primary key, v integer);
599 insert into gstest5 select i, i from generate_series(1,5)i;
601 explain (verbose, costs off)
602 select grouping((select t1.v from gstest5 t2 where id = t1.id)),
603        (select t1.v from gstest5 t2 where id = t1.id) as s
604 from gstest5 t1
605 group by grouping sets(v, s)
606 order by case when grouping((select t1.v from gstest5 t2 where id = t1.id)) = 0
607               then (select t1.v from gstest5 t2 where id = t1.id)
608               else null end
609          nulls first;
611 select grouping((select t1.v from gstest5 t2 where id = t1.id)),
612        (select t1.v from gstest5 t2 where id = t1.id) as s
613 from gstest5 t1
614 group by grouping sets(v, s)
615 order by case when grouping((select t1.v from gstest5 t2 where id = t1.id)) = 0
616               then (select t1.v from gstest5 t2 where id = t1.id)
617               else null end
618          nulls first;
620 explain (verbose, costs off)
621 select grouping((select t1.v from gstest5 t2 where id = t1.id)),
622        (select t1.v from gstest5 t2 where id = t1.id) as s,
623        case when grouping((select t1.v from gstest5 t2 where id = t1.id)) = 0
624             then (select t1.v from gstest5 t2 where id = t1.id)
625             else null end as o
626 from gstest5 t1
627 group by grouping sets(v, s)
628 order by o nulls first;
630 select grouping((select t1.v from gstest5 t2 where id = t1.id)),
631        (select t1.v from gstest5 t2 where id = t1.id) as s,
632        case when grouping((select t1.v from gstest5 t2 where id = t1.id)) = 0
633             then (select t1.v from gstest5 t2 where id = t1.id)
634             else null end as o
635 from gstest5 t1
636 group by grouping sets(v, s)
637 order by o nulls first;
639 -- test handling of expressions that should match lower target items
640 explain (costs off)
641 select a < b and b < 3 from (values (1, 2)) t(a, b) group by rollup(a < b and b < 3) having a < b and b < 3;
642 select a < b and b < 3 from (values (1, 2)) t(a, b) group by rollup(a < b and b < 3) having a < b and b < 3;
644 explain (costs off)
645 select not a from (values(true)) t(a) group by rollup(not a) having not not a;
646 select not a from (values(true)) t(a) group by rollup(not a) having not not a;
648 -- test handling of expressions nullable by grouping sets
649 explain (costs off)
650 select distinct on (a, b) a, b
651 from (values (1, 1), (2, 2)) as t (a, b) where a = b
652 group by grouping sets((a, b), (a))
653 order by a, b;
655 select distinct on (a, b) a, b
656 from (values (1, 1), (2, 2)) as t (a, b) where a = b
657 group by grouping sets((a, b), (a))
658 order by a, b;
660 explain (costs off)
661 select distinct on (a, b+1) a, b+1
662 from (values (1, 0), (2, 1)) as t (a, b) where a = b+1
663 group by grouping sets((a, b+1), (a))
664 order by a, b+1;
666 select distinct on (a, b+1) a, b+1
667 from (values (1, 0), (2, 1)) as t (a, b) where a = b+1
668 group by grouping sets((a, b+1), (a))
669 order by a, b+1;
671 explain (costs off)
672 select a, b
673 from (values (1, 1), (2, 2)) as t (a, b) where a = b
674 group by grouping sets((a, b), (a))
675 order by a, b nulls first;
677 select a, b
678 from (values (1, 1), (2, 2)) as t (a, b) where a = b
679 group by grouping sets((a, b), (a))
680 order by a, b nulls first;
682 explain (costs off)
683 select 1 as one group by rollup(one) order by one nulls first;
684 select 1 as one group by rollup(one) order by one nulls first;
686 explain (costs off)
687 select a, b, row_number() over (order by a, b nulls first)
688 from (values (1, 1), (2, 2)) as t (a, b) where a = b
689 group by grouping sets((a, b), (a));
691 select a, b, row_number() over (order by a, b nulls first)
692 from (values (1, 1), (2, 2)) as t (a, b) where a = b
693 group by grouping sets((a, b), (a));
695 -- end