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),
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;
27 create temp table gstest3 (a integer, b integer, c integer, d integer);
28 copy gstest3 from stdin;
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);
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)
48 return query select v, i from generate_series(1,3) i;
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(())))
91 select sum(c) from gstest2
92 group by grouping sets((), grouping sets((), grouping sets(((a, b)))))
94 select sum(c) from gstest2
95 group by grouping sets(grouping sets(rollup(c), grouping sets(cube(c))))
97 select sum(c) from gstest2
98 group by grouping sets(a, grouping sets(a, cube(b)))
100 select sum(c) from gstest2
101 group by grouping sets(grouping sets((a, (b))))
103 select sum(c) from gstest2
104 group by grouping sets(grouping sets((a, b)))
106 select sum(c) from gstest2
107 group by grouping sets(grouping sets(a, grouping sets(a), a))
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))
112 select sum(c) from gstest2
113 group by grouping sets((a,(a,b)), grouping sets((a,(a,b)),a))
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)
142 group by grouping sets ((a,b), (a,c));
144 -- check that distinct grouping columns are kept separate
145 -- even if they are equal()
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
157 from (select four, ten, 'foo'::text as x from tenk1) as t
158 group by grouping sets (four, x)
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)
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)
175 -- check qual push-down rules for a subquery with grouping sets
176 explain (verbose, costs off)
178 select 1 as x, q1, sum(q2)
180 group by grouping sets(1, 2)
182 where x = 1 and q1 = 123;
185 select 1 as x, q1, sum(q2)
187 group by grouping sets(1, 2)
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)
195 cross join lateral (select (select i1.q1) as x) ss
198 select grouping(ss.x)
200 cross join lateral (select (select i1.q1) as x) ss
203 explain (verbose, costs off)
204 select (select grouping(ss.x))
206 cross join lateral (select (select i1.q1) as x) ss
209 select (select grouping(ss.x))
211 cross join lateral (select (select i1.q1) as x) ss
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);
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 ()
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
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;
263 select a, b, sum(c), count(*) from gstest2 group by grouping sets (rollup(a,b),a);
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;
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;
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
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
291 select ten, grouping(ten) from onek
292 group by grouping sets(ten, four) having grouping(ten) > 0
294 select ten, grouping(ten) from onek
295 group by rollup(ten) having grouping(ten) > 0
297 select ten, grouping(ten) from onek
298 group by cube(ten) having grouping(ten) > 0
300 select ten, grouping(ten) from onek
301 group by (ten) having grouping(ten) >= 0
305 select ten, sum(distinct four) filter (where four::text ~ '123') from onek a
306 group by rollup(ten);
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;
318 set enable_hashagg = true;
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));
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
339 select a, b, grouping(a,b), array_agg(v order by v)
340 from gstest1 group by cube(a,b);
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),
351 from gstest4 group by grouping sets ((unhashable_col),(unsortable_col))
354 select unhashable_col, unsortable_col,
355 grouping(unhashable_col, unsortable_col),
357 from gstest4 group by grouping sets ((unhashable_col),(unsortable_col))
360 select unhashable_col, unsortable_col,
361 grouping(unhashable_col, unsortable_col),
363 from gstest4 group by grouping sets ((v,unhashable_col),(v,unsortable_col))
366 select unhashable_col, unsortable_col,
367 grouping(unhashable_col, unsortable_col),
369 from gstest4 group by grouping sets ((v,unhashable_col),(v,unsortable_col))
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);
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),(),(),());
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 ((),(),());
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)
387 group by grouping sets ((a,b), (a,c));
389 select a, d, grouping(a,b,c)
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)
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)
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;
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;
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;
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;
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.
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;
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
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);
454 set enable_indexscan = false;
455 set hash_mem_multiplier = 1.0;
456 set work_mem = '64kB';
459 count(two), count(four), count(ten),
460 count(hundred), count(thousand), count(twothousand),
462 from tenk1 group by grouping sets (unique1,twothousand,thousand,hundred,ten,four,two);
465 count(two), count(four), count(ten),
466 count(hundred), count(thousand), count(twothousand),
468 from tenk1 group by grouping sets (unique1,hundred,ten,four,two);
470 set work_mem = '384kB';
473 count(two), count(four), count(ten),
474 count(hundred), count(thousand), count(twothousand),
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;
490 create table bug_16784(i int, j int);
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;
498 set enable_sort = false;
501 (values (1),(2)) v(a),
502 lateral (select a, i, j, count(*) from
503 bug_16784 group by cube(i,j)) s
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;
517 alter table gs_data_1 set (autovacuum_enabled = 'false');
518 update pg_class set reltuples = 10 where relname='gs_data_1';
522 -- Produce results with sorting.
524 set enable_sort = true;
525 set enable_hashagg = false;
526 set jit_above_cost = 0;
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;
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;
555 (select * from gs_hash_1 except select * from gs_group_1)
557 (select * from gs_group_1 except select * from gs_hash_1);
559 drop table gs_group_1;
560 drop table gs_hash_1;
564 -- "normal" behavior...
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)
570 -- ...which is also the default
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)
576 -- "group by distinct" behavior...
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)
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)
588 -- test handling of outer GroupingFunc within subqueries
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);
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
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)
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
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)
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)
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)
636 group by grouping sets(v, s)
637 order by o nulls first;
639 -- test handling of expressions that should match lower target items
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;
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
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))
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))
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))
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))
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;
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;
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;
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));