| -- |
| -- grouping sets |
| -- |
| -- GPDB: Some of the tests in this file test the case that some columns are |
| -- unsortable, and some are unhashable. For the unhashable column, the upstream |
| -- tests use 'bit' datatype. However, we have added a hash opclass for 'bit' |
| -- in GPDB, which makes the tests ineffective in testing that. |
| -- |
| -- To work around that, create a new datatype that is just like the built-in |
| -- 'bit' type, but doesn't have the hash opclass. |
| create type unhashable_bit; |
| create function unhashable_bit_out (unhashable_bit) returns cstring immutable |
| language internal as 'bit_out'; |
| NOTICE: argument type unhashable_bit is only a shell |
| create function unhashable_bit_in (cstring) returns unhashable_bit immutable |
| language internal as 'bit_in'; |
| NOTICE: return type unhashable_bit is only a shell |
| create type unhashable_bit ( |
| input = unhashable_bit_in, |
| output = unhashable_bit_out, |
| typmod_in = bittypmodin, |
| typmod_out = bittypmodout, |
| like = bit); |
| create function unhashable_biteq(unhashable_bit, unhashable_bit) returns bool |
| immutable language internal as 'biteq'; |
| create function unhashable_bitne(unhashable_bit, unhashable_bit) returns bool |
| immutable language internal as 'bitne'; |
| create function unhashable_bitge(unhashable_bit, unhashable_bit) returns bool |
| immutable language internal as 'bitge'; |
| create function unhashable_bitgt(unhashable_bit, unhashable_bit) returns bool |
| immutable language internal as 'bitgt'; |
| create function unhashable_bitle(unhashable_bit, unhashable_bit) returns bool |
| immutable language internal as 'bitle'; |
| create function unhashable_bitlt(unhashable_bit, unhashable_bit) returns bool |
| immutable language internal as 'bitlt'; |
| create function unhashable_bitcmp(unhashable_bit, unhashable_bit) returns int4 |
| immutable language internal as 'bitcmp'; |
| create operator = (function=unhashable_biteq, leftarg=unhashable_bit, rightarg=unhashable_bit, |
| merges, commutator = "=", negator = "<>", |
| restrict = 'eqsel', join = 'eqjoinsel'); |
| create operator <> (function=unhashable_bitne, leftarg=unhashable_bit, rightarg=unhashable_bit, |
| commutator = "<>", negator = "=", |
| restrict = 'neqsel', join = 'neqjoinsel'); |
| create operator >= (function=unhashable_bitge, leftarg=unhashable_bit, rightarg=unhashable_bit, |
| commutator = "<=", negator = "<", |
| restrict = 'scalargesel', join = 'scalargejoinsel'); |
| create operator > (function=unhashable_bitgt, leftarg=unhashable_bit, rightarg=unhashable_bit, |
| commutator = "<", negator = "<=", |
| restrict = 'scalargtsel', join = 'scalargtjoinsel'); |
| create operator <= (function=unhashable_bitle, leftarg=unhashable_bit, rightarg=unhashable_bit, |
| commutator = ">=", negator = ">", |
| restrict = 'scalarlesel', join = 'scalarlejoinsel'); |
| create operator < (function=unhashable_bitlt, leftarg=unhashable_bit, rightarg=unhashable_bit, |
| commutator = ">", negator = ">=", |
| restrict = 'scalarltsel', join = 'scalarltjoinsel'); |
| create operator class unhashable_bit_ops |
| default for type unhashable_bit using btree as |
| operator 1 < , |
| operator 2 <= , |
| operator 3 = , |
| operator 4 >= , |
| operator 5 > , |
| function 1 unhashable_bitcmp(unhashable_bit, unhashable_bit); |
| create cast (bit as unhashable_bit) without function as assignment; |
| -- test data sources |
| create temp view gstest1(a,b,v) |
| as 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); |
| create temp table gstest2 (a integer, b integer, c integer, d integer, |
| e integer, f integer, g integer, h integer); |
| copy gstest2 from stdin; |
| ANALYZE gstest2; |
| create temp table gstest3 (a integer, b integer, c integer, d integer); |
| copy gstest3 from stdin; |
| alter table gstest3 add primary key (a); |
| create temp table gstest4(id integer, v integer, |
| unhashable_col unhashable_bit(4), unsortable_col xid); |
| insert into gstest4 |
| values (1,1,b'0000','1'), (2,2,b'0001','1'), |
| (3,4,b'0010','2'), (4,8,b'0011','2'), |
| (5,16,b'0000','2'), (6,32,b'0001','2'), |
| (7,64,b'0010','1'), (8,128,b'0011','1'); |
| create temp table gstest5(id integer, v integer, |
| unsortable_col1 xid, unsortable_col2 xid); |
| insert into gstest5 |
| values (1,1,'3','1'), (2,2,'3','1'), |
| (3,4,'4','2'), (4,8,'4','2'), |
| (5,16,'4','2'), (6,32,'4','2'), |
| (7,64,'3','1'), (8,128,'3','1'); |
| create temp table gstest_empty (a integer, b integer, v integer); |
| create function gstest_data(v integer, out a integer, out b integer) |
| returns setof record |
| as $f$ |
| begin |
| return query select v, i from generate_series(1,3) i; |
| end; |
| $f$ language plpgsql; |
| -- basic functionality |
| set enable_hashagg = false; -- test hashing explicitly later |
| -- simple rollup with multiple plain aggregates, with and without ordering |
| -- (and with ordering differing from grouping) |
| select a, b, grouping(a,b), sum(v), count(*), max(v) |
| from gstest1 group by rollup (a,b); |
| a | b | grouping | sum | count | max |
| ---+---+----------+-----+-------+----- |
| 1 | 1 | 0 | 21 | 2 | 11 |
| 1 | 2 | 0 | 25 | 2 | 13 |
| 1 | 3 | 0 | 14 | 1 | 14 |
| 1 | | 1 | 60 | 5 | 14 |
| 2 | 3 | 0 | 15 | 1 | 15 |
| 2 | | 1 | 15 | 1 | 15 |
| 3 | 3 | 0 | 16 | 1 | 16 |
| 3 | 4 | 0 | 17 | 1 | 17 |
| 3 | | 1 | 33 | 2 | 17 |
| 4 | 1 | 0 | 37 | 2 | 19 |
| 4 | | 1 | 37 | 2 | 19 |
| | | 3 | 145 | 10 | 19 |
| (12 rows) |
| |
| select a, b, grouping(a,b), sum(v), count(*), max(v) |
| from gstest1 group by rollup (a,b) order by a,b; |
| a | b | grouping | sum | count | max |
| ---+---+----------+-----+-------+----- |
| 1 | 1 | 0 | 21 | 2 | 11 |
| 1 | 2 | 0 | 25 | 2 | 13 |
| 1 | 3 | 0 | 14 | 1 | 14 |
| 1 | | 1 | 60 | 5 | 14 |
| 2 | 3 | 0 | 15 | 1 | 15 |
| 2 | | 1 | 15 | 1 | 15 |
| 3 | 3 | 0 | 16 | 1 | 16 |
| 3 | 4 | 0 | 17 | 1 | 17 |
| 3 | | 1 | 33 | 2 | 17 |
| 4 | 1 | 0 | 37 | 2 | 19 |
| 4 | | 1 | 37 | 2 | 19 |
| | | 3 | 145 | 10 | 19 |
| (12 rows) |
| |
| select a, b, grouping(a,b), sum(v), count(*), max(v) |
| from gstest1 group by rollup (a,b) order by b desc, a; |
| a | b | grouping | sum | count | max |
| ---+---+----------+-----+-------+----- |
| 1 | | 1 | 60 | 5 | 14 |
| 2 | | 1 | 15 | 1 | 15 |
| 3 | | 1 | 33 | 2 | 17 |
| 4 | | 1 | 37 | 2 | 19 |
| | | 3 | 145 | 10 | 19 |
| 3 | 4 | 0 | 17 | 1 | 17 |
| 1 | 3 | 0 | 14 | 1 | 14 |
| 2 | 3 | 0 | 15 | 1 | 15 |
| 3 | 3 | 0 | 16 | 1 | 16 |
| 1 | 2 | 0 | 25 | 2 | 13 |
| 1 | 1 | 0 | 21 | 2 | 11 |
| 4 | 1 | 0 | 37 | 2 | 19 |
| (12 rows) |
| |
| select a, b, grouping(a,b), sum(v), count(*), max(v) |
| from gstest1 group by rollup (a,b) order by coalesce(a,0)+coalesce(b,0); |
| a | b | grouping | sum | count | max |
| ---+---+----------+-----+-------+----- |
| | | 3 | 145 | 10 | 19 |
| 1 | | 1 | 60 | 5 | 14 |
| 1 | 1 | 0 | 21 | 2 | 11 |
| 2 | | 1 | 15 | 1 | 15 |
| 3 | | 1 | 33 | 2 | 17 |
| 1 | 2 | 0 | 25 | 2 | 13 |
| 1 | 3 | 0 | 14 | 1 | 14 |
| 4 | | 1 | 37 | 2 | 19 |
| 4 | 1 | 0 | 37 | 2 | 19 |
| 2 | 3 | 0 | 15 | 1 | 15 |
| 3 | 3 | 0 | 16 | 1 | 16 |
| 3 | 4 | 0 | 17 | 1 | 17 |
| (12 rows) |
| |
| -- various types of ordered aggs |
| select a, b, grouping(a,b), |
| array_agg(v order by v), |
| string_agg(v::text, ':' order by v desc), |
| percentile_disc(0.5) within group (order by v), |
| rank(1,2,12) within group (order by a,b,v) |
| from gstest1 group by rollup (a,b) order by a,b; |
| a | b | grouping | array_agg | string_agg | percentile_disc | rank |
| ---+---+----------+---------------------------------+-------------------------------+-----------------+------ |
| 1 | 1 | 0 | {10,11} | 11:10 | 10 | 3 |
| 1 | 2 | 0 | {12,13} | 13:12 | 12 | 1 |
| 1 | 3 | 0 | {14} | 14 | 14 | 1 |
| 1 | | 1 | {10,11,12,13,14} | 14:13:12:11:10 | 12 | 3 |
| 2 | 3 | 0 | {15} | 15 | 15 | 1 |
| 2 | | 1 | {15} | 15 | 15 | 1 |
| 3 | 3 | 0 | {16} | 16 | 16 | 1 |
| 3 | 4 | 0 | {17} | 17 | 17 | 1 |
| 3 | | 1 | {16,17} | 17:16 | 16 | 1 |
| 4 | 1 | 0 | {18,19} | 19:18 | 18 | 1 |
| 4 | | 1 | {18,19} | 19:18 | 18 | 1 |
| | | 3 | {10,11,12,13,14,15,16,17,18,19} | 19:18:17:16:15:14:13:12:11:10 | 14 | 3 |
| (12 rows) |
| |
| -- test usage of grouped columns in direct args of aggs |
| select grouping(a), a, array_agg(b), |
| rank(a) within group (order by b nulls first), |
| rank(a) within group (order by b nulls last) |
| from (values (1,1),(1,4),(1,5),(3,1),(3,2)) v(a,b) |
| group by rollup (a) order by a; |
| grouping | a | array_agg | rank | rank |
| ----------+---+-------------+------+------ |
| 0 | 1 | {1,4,5} | 1 | 1 |
| 0 | 3 | {1,2} | 3 | 3 |
| 1 | | {1,4,5,1,2} | 1 | 6 |
| (3 rows) |
| |
| -- nesting with window functions |
| select a, b, sum(c), sum(sum(c)) over (order by a,b) as rsum |
| from gstest2 group by rollup (a,b) order by rsum, a, b; |
| a | b | sum | rsum |
| ---+---+-----+------ |
| 1 | 1 | 8 | 8 |
| 1 | 2 | 2 | 10 |
| 1 | | 10 | 20 |
| 2 | 2 | 2 | 22 |
| 2 | | 2 | 24 |
| | | 12 | 36 |
| (6 rows) |
| |
| -- nesting with grouping sets |
| select sum(c) from gstest2 |
| group by grouping sets((), grouping sets((), grouping sets(()))) |
| order by 1 desc; |
| sum |
| ----- |
| 12 |
| 12 |
| 12 |
| (3 rows) |
| |
| select sum(c) from gstest2 |
| group by grouping sets((), grouping sets((), grouping sets(((a, b))))) |
| order by 1 desc; |
| sum |
| ----- |
| 12 |
| 12 |
| 8 |
| 2 |
| 2 |
| (5 rows) |
| |
| select sum(c) from gstest2 |
| group by grouping sets(grouping sets(rollup(c), grouping sets(cube(c)))) |
| order by 1 desc; |
| sum |
| ----- |
| 12 |
| 12 |
| 6 |
| 6 |
| 6 |
| 6 |
| (6 rows) |
| |
| select sum(c) from gstest2 |
| group by grouping sets(a, grouping sets(a, cube(b))) |
| order by 1 desc; |
| sum |
| ----- |
| 12 |
| 10 |
| 10 |
| 8 |
| 4 |
| 2 |
| 2 |
| (7 rows) |
| |
| select sum(c) from gstest2 |
| group by grouping sets(grouping sets((a, (b)))) |
| order by 1 desc; |
| sum |
| ----- |
| 8 |
| 2 |
| 2 |
| (3 rows) |
| |
| select sum(c) from gstest2 |
| group by grouping sets(grouping sets((a, b))) |
| order by 1 desc; |
| sum |
| ----- |
| 8 |
| 2 |
| 2 |
| (3 rows) |
| |
| select sum(c) from gstest2 |
| group by grouping sets(grouping sets(a, grouping sets(a), a)) |
| order by 1 desc; |
| sum |
| ----- |
| 10 |
| 10 |
| 10 |
| 2 |
| 2 |
| 2 |
| (6 rows) |
| |
| select sum(c) from gstest2 |
| group by grouping sets(grouping sets(a, grouping sets(a, grouping sets(a), ((a)), a, grouping sets(a), (a)), a)) |
| order by 1 desc; |
| sum |
| ----- |
| 10 |
| 10 |
| 10 |
| 10 |
| 10 |
| 10 |
| 10 |
| 10 |
| 2 |
| 2 |
| 2 |
| 2 |
| 2 |
| 2 |
| 2 |
| 2 |
| (16 rows) |
| |
| select sum(c) from gstest2 |
| group by grouping sets((a,(a,b)), grouping sets((a,(a,b)),a)) |
| order by 1 desc; |
| sum |
| ----- |
| 10 |
| 8 |
| 8 |
| 2 |
| 2 |
| 2 |
| 2 |
| 2 |
| (8 rows) |
| |
| -- empty input: first is 0 rows, second 1, third 3 etc. |
| select a, b, sum(v), count(*) from gstest_empty group by grouping sets ((a,b),a); |
| a | b | sum | count |
| ---+---+-----+------- |
| (0 rows) |
| |
| select a, b, sum(v), count(*) from gstest_empty group by grouping sets ((a,b),()); |
| a | b | sum | count |
| ---+---+-----+------- |
| | | | 0 |
| (1 row) |
| |
| select a, b, sum(v), count(*) from gstest_empty group by grouping sets ((a,b),(),(),()); |
| a | b | sum | count |
| ---+---+-----+------- |
| | | | 0 |
| | | | 0 |
| | | | 0 |
| (3 rows) |
| |
| select sum(v), count(*) from gstest_empty group by grouping sets ((),(),()); |
| sum | count |
| -----+------- |
| | 0 |
| | 0 |
| | 0 |
| (3 rows) |
| |
| -- empty input with joins tests some important code paths |
| select t1.a, t2.b, sum(t1.v), count(*) from gstest_empty t1, gstest_empty t2 |
| group by grouping sets ((t1.a,t2.b),()); |
| a | b | sum | count |
| ---+---+-----+------- |
| | | | 0 |
| (1 row) |
| |
| -- simple joins, var resolution, GROUPING on join vars |
| select t1.a, t2.b, grouping(t1.a, t2.b), sum(t1.v), max(t2.a) |
| from gstest1 t1, gstest2 t2 |
| group by grouping sets ((t1.a, t2.b), ()); |
| a | b | grouping | sum | max |
| ---+---+----------+------+----- |
| 1 | 1 | 0 | 420 | 1 |
| 1 | 2 | 0 | 120 | 2 |
| 2 | 1 | 0 | 105 | 1 |
| 2 | 2 | 0 | 30 | 2 |
| 3 | 1 | 0 | 231 | 1 |
| 3 | 2 | 0 | 66 | 2 |
| 4 | 1 | 0 | 259 | 1 |
| 4 | 2 | 0 | 74 | 2 |
| | | 3 | 1305 | 2 |
| (9 rows) |
| |
| select t1.a, t2.b, grouping(t1.a, t2.b), sum(t1.v), max(t2.a) |
| from gstest1 t1 join gstest2 t2 on (t1.a=t2.a) |
| group by grouping sets ((t1.a, t2.b), ()); |
| a | b | grouping | sum | max |
| ---+---+----------+-----+----- |
| 1 | 1 | 0 | 420 | 1 |
| 1 | 2 | 0 | 60 | 1 |
| 2 | 2 | 0 | 15 | 2 |
| | | 3 | 495 | 2 |
| (4 rows) |
| |
| select a, b, grouping(a, b), sum(t1.v), max(t2.c) |
| from gstest1 t1 join gstest2 t2 using (a,b) |
| group by grouping sets ((a, b), ()); |
| a | b | grouping | sum | max |
| ---+---+----------+-----+----- |
| 1 | 1 | 0 | 147 | 2 |
| 1 | 2 | 0 | 25 | 2 |
| | | 3 | 172 | 2 |
| (3 rows) |
| |
| -- check that functionally dependent cols are not nulled |
| select a, d, grouping(a,b,c) |
| from gstest3 |
| group by grouping sets ((a,b), (a,c)); |
| a | d | grouping |
| ---+---+---------- |
| 1 | 1 | 1 |
| 2 | 2 | 1 |
| 1 | 1 | 2 |
| 2 | 2 | 2 |
| (4 rows) |
| |
| -- check that distinct grouping columns are kept separate |
| -- even if they are equal() |
| explain (costs off) |
| select g as alias1, g as alias2 |
| from generate_series(1,3) g |
| group by alias1, rollup(alias2); |
| QUERY PLAN |
| ------------------------------------------------ |
| GroupAggregate |
| Group Key: g, g |
| Group Key: g |
| -> Sort |
| Sort Key: g |
| -> Function Scan on generate_series g |
| (6 rows) |
| |
| select g as alias1, g as alias2 |
| from generate_series(1,3) g |
| group by alias1, rollup(alias2); |
| alias1 | alias2 |
| --------+-------- |
| 1 | 1 |
| 1 | |
| 2 | 2 |
| 2 | |
| 3 | 3 |
| 3 | |
| (6 rows) |
| |
| -- check that pulled-up subquery outputs still go to null when appropriate |
| select four, x |
| from (select four, ten, 'foo'::text as x from tenk1) as t |
| group by grouping sets (four, x) |
| having x = 'foo'; |
| four | x |
| ------+----- |
| | foo |
| (1 row) |
| |
| select four, x || 'x' |
| from (select four, ten, 'foo'::text as x from tenk1) as t |
| group by grouping sets (four, x) |
| order by four; |
| four | ?column? |
| ------+---------- |
| 0 | |
| 1 | |
| 2 | |
| 3 | |
| | foox |
| (5 rows) |
| |
| select (x+y)*1, sum(z) |
| from (select 1 as x, 2 as y, 3 as z) s |
| group by grouping sets (x+y, x); |
| ?column? | sum |
| ----------+----- |
| 3 | 3 |
| | 3 |
| (2 rows) |
| |
| select x, not x as not_x, q2 from |
| (select *, q1 = 1 as x from int8_tbl i1) as t |
| group by grouping sets(x, q2) |
| order by x, q2; |
| x | not_x | q2 |
| ---+-------+------------------- |
| f | t | |
| | | -4567890123456789 |
| | | 123 |
| | | 456 |
| | | 4567890123456789 |
| (5 rows) |
| |
| -- check qual push-down rules for a subquery with grouping sets |
| explain (verbose, costs off) |
| select * from ( |
| select 1 as x, q1, sum(q2) |
| from int8_tbl i1 |
| group by grouping sets(1, 2) |
| ) ss |
| where x = 1 and q1 = 123; |
| QUERY PLAN |
| ------------------------------------------------------------------------------------------ |
| Subquery Scan on ss |
| Output: ss.x, ss.q1, ss.sum |
| Filter: ((ss.x = 1) AND (ss.q1 = 123)) |
| -> Gather Motion 3:1 (slice1; segments: 3) |
| Output: 1, i1.q1, (sum(i1.q2)) |
| Merge Key: i1.q1 |
| -> Finalize GroupAggregate |
| Output: (1), i1.q1, sum(i1.q2) |
| Group Key: 1, i1.q1, (GROUPINGSET_ID()) |
| -> Sort |
| Output: 1, i1.q1, (PARTIAL sum(i1.q2)), (GROUPINGSET_ID()) |
| Sort Key: i1.q1, (GROUPINGSET_ID()) |
| -> Redistribute Motion 3:3 (slice2; segments: 3) |
| Output: 1, i1.q1, (PARTIAL sum(i1.q2)), (GROUPINGSET_ID()) |
| Hash Key: 1, i1.q1, (GROUPINGSET_ID()) |
| -> Partial GroupAggregate |
| Output: (1), i1.q1, PARTIAL sum(i1.q2), GROUPINGSET_ID() |
| Group Key: 1 |
| Sort Key: i1.q1 |
| Group Key: i1.q1 |
| -> Seq Scan on public.int8_tbl i1 |
| Output: 1, i1.q1, i1.q2 |
| Settings: enable_hashagg = 'off', optimizer = 'off' |
| Optimizer: Postgres query optimizer |
| (24 rows) |
| |
| select * from ( |
| select 1 as x, q1, sum(q2) |
| from int8_tbl i1 |
| group by grouping sets(1, 2) |
| ) ss |
| where x = 1 and q1 = 123; |
| x | q1 | sum |
| ---+----+----- |
| (0 rows) |
| |
| -- check handling of pulled-up SubPlan in GROUPING() argument (bug #17479) |
| explain (verbose, costs off) |
| select grouping(ss.x) |
| from int8_tbl i1 |
| cross join lateral (select (select i1.q1) as x) ss |
| group by ss.x; |
| QUERY PLAN |
| ------------------------------------------------------------------ |
| Gather Motion 3:1 (slice1; segments: 3) |
| Output: (GROUPING((SubPlan 1))), ((SubPlan 2)) |
| -> GroupAggregate |
| Output: GROUPING((SubPlan 1)), ((SubPlan 2)) |
| Group Key: ((SubPlan 2)) |
| -> Sort |
| Output: ((SubPlan 2)), i1.q1 |
| Sort Key: ((SubPlan 2)) |
| -> Redistribute Motion 3:3 (slice2; segments: 3) |
| Output: ((SubPlan 2)), i1.q1 |
| Hash Key: ((SubPlan 2)) |
| -> Seq Scan on public.int8_tbl i1 |
| Output: (SubPlan 2), i1.q1 |
| SubPlan 2 |
| -> Result |
| Output: i1.q1 |
| Settings: enable_hashagg = 'off', optimizer = 'off' |
| Optimizer: Postgres query optimizer |
| (18 rows) |
| |
| select grouping(ss.x) |
| from int8_tbl i1 |
| cross join lateral (select (select i1.q1) as x) ss |
| group by ss.x; |
| grouping |
| ---------- |
| 0 |
| 0 |
| (2 rows) |
| |
| explain (verbose, costs off) |
| select (select grouping(ss.x)) |
| from int8_tbl i1 |
| cross join lateral (select (select i1.q1) as x) ss |
| group by ss.x; |
| QUERY PLAN |
| ------------------------------------------------------------------ |
| Gather Motion 3:1 (slice1; segments: 3) |
| Output: ((SubPlan 2)), ((SubPlan 3)) |
| -> GroupAggregate |
| Output: (SubPlan 2), ((SubPlan 3)) |
| Group Key: ((SubPlan 3)) |
| -> Sort |
| Output: ((SubPlan 3)), i1.q1 |
| Sort Key: ((SubPlan 3)) |
| -> Redistribute Motion 3:3 (slice2; segments: 3) |
| Output: ((SubPlan 3)), i1.q1 |
| Hash Key: ((SubPlan 3)) |
| -> Seq Scan on public.int8_tbl i1 |
| Output: (SubPlan 3), i1.q1 |
| SubPlan 3 |
| -> Result |
| Output: i1.q1 |
| SubPlan 2 |
| -> Result |
| Output: GROUPING((SubPlan 1)) |
| Settings: enable_hashagg = 'off', optimizer = 'off' |
| Optimizer: Postgres query optimizer |
| (21 rows) |
| |
| select (select grouping(ss.x)) |
| from int8_tbl i1 |
| cross join lateral (select (select i1.q1) as x) ss |
| group by ss.x; |
| grouping |
| ---------- |
| 0 |
| 0 |
| (2 rows) |
| |
| -- simple rescan tests |
| select a, b, sum(v.x) |
| from (values (1),(2)) v(x), gstest_data(v.x) |
| group by rollup (a,b); |
| a | b | sum |
| ---+---+----- |
| 1 | 1 | 1 |
| 1 | 2 | 1 |
| 1 | 3 | 1 |
| 1 | | 3 |
| 2 | 1 | 2 |
| 2 | 2 | 2 |
| 2 | 3 | 2 |
| 2 | | 6 |
| | | 9 |
| (9 rows) |
| |
| select * |
| from (values (1),(2)) v(x), |
| lateral (select a, b, sum(v.x) from gstest_data(v.x) group by rollup (a,b)) s; |
| ERROR: aggregate functions are not allowed in FROM clause of their own query level |
| LINE 3: lateral (select a, b, sum(v.x) from gstest_data(v.x) ... |
| ^ |
| -- min max optimization should still work with GROUP BY () |
| explain (costs off) |
| select min(unique1) from tenk1 GROUP BY (); |
| QUERY PLAN |
| ------------------------------------------------------------------ |
| Result |
| InitPlan 1 (returns $0) (slice1) |
| -> Limit |
| -> Gather Motion 3:1 (slice2; segments: 3) |
| Merge Key: tenk1.unique1 |
| -> Index Only Scan using tenk1_unique1 on tenk1 |
| Index Cond: (unique1 IS NOT NULL) |
| Optimizer: Postgres query optimizer |
| (8 rows) |
| |
| -- Views with GROUPING SET queries |
| CREATE VIEW gstest_view AS select a, b, grouping(a,b), sum(c), count(*), max(c) |
| from gstest2 group by rollup ((a,b,c),(c,d)); |
| NOTICE: view "gstest_view" will be a temporary view |
| select pg_get_viewdef('gstest_view'::regclass, true); |
| pg_get_viewdef |
| ------------------------------------------------------------------------------- |
| SELECT gstest2.a, + |
| gstest2.b, + |
| GROUPING(gstest2.a, gstest2.b) AS "grouping", + |
| sum(gstest2.c) AS sum, + |
| count(*) AS count, + |
| max(gstest2.c) AS max + |
| FROM gstest2 + |
| GROUP BY ROLLUP((gstest2.a, gstest2.b, gstest2.c), (gstest2.c, gstest2.d)); |
| (1 row) |
| |
| -- Nested queries with 3 or more levels of nesting |
| 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); |
| grouping |
| ---------- |
| 0 |
| 0 |
| 0 |
| (3 rows) |
| |
| 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); |
| grouping |
| ---------- |
| 0 |
| 1 |
| 3 |
| (3 rows) |
| |
| 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); |
| grouping |
| ---------- |
| 0 |
| 0 |
| 0 |
| (3 rows) |
| |
| -- Combinations of operations |
| select a, b, c, d from gstest2 group by rollup(a,b),grouping sets(c,d); |
| a | b | c | d |
| ---+---+---+--- |
| 1 | 1 | 1 | |
| 1 | | 1 | |
| | | 1 | |
| 1 | 1 | 2 | |
| 1 | 2 | 2 | |
| 1 | | 2 | |
| 2 | 2 | 2 | |
| 2 | | 2 | |
| | | 2 | |
| 1 | 1 | | 1 |
| 1 | | | 1 |
| | | | 1 |
| 1 | 1 | | 2 |
| 1 | 2 | | 2 |
| 1 | | | 2 |
| 2 | 2 | | 2 |
| 2 | | | 2 |
| | | | 2 |
| (18 rows) |
| |
| select a, b from (values (1,2),(2,3)) v(a,b) group by a,b, grouping sets(a); |
| a | b |
| ---+--- |
| 1 | 2 |
| 2 | 3 |
| (2 rows) |
| |
| -- Tests for chained aggregates |
| select a, b, grouping(a,b), sum(v), count(*), max(v) |
| from gstest1 group by grouping sets ((a,b),(a+1,b+1),(a+2,b+2)) order by 3,6; |
| a | b | grouping | sum | count | max |
| ---+---+----------+-----+-------+----- |
| 1 | 1 | 0 | 21 | 2 | 11 |
| 1 | 2 | 0 | 25 | 2 | 13 |
| 1 | 3 | 0 | 14 | 1 | 14 |
| 2 | 3 | 0 | 15 | 1 | 15 |
| 3 | 3 | 0 | 16 | 1 | 16 |
| 3 | 4 | 0 | 17 | 1 | 17 |
| 4 | 1 | 0 | 37 | 2 | 19 |
| | | 3 | 21 | 2 | 11 |
| | | 3 | 21 | 2 | 11 |
| | | 3 | 25 | 2 | 13 |
| | | 3 | 25 | 2 | 13 |
| | | 3 | 14 | 1 | 14 |
| | | 3 | 14 | 1 | 14 |
| | | 3 | 15 | 1 | 15 |
| | | 3 | 15 | 1 | 15 |
| | | 3 | 16 | 1 | 16 |
| | | 3 | 16 | 1 | 16 |
| | | 3 | 17 | 1 | 17 |
| | | 3 | 17 | 1 | 17 |
| | | 3 | 37 | 2 | 19 |
| | | 3 | 37 | 2 | 19 |
| (21 rows) |
| |
| 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)); |
| grouping |
| ---------- |
| 0 |
| 0 |
| 0 |
| (3 rows) |
| |
| 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); |
| grouping |
| ---------- |
| 0 |
| 0 |
| 0 |
| 0 |
| (4 rows) |
| |
| select a, b, sum(c), sum(sum(c)) over (order by a,b) as rsum |
| from gstest2 group by cube (a,b) order by rsum, a, b; |
| a | b | sum | rsum |
| ---+---+-----+------ |
| 1 | 1 | 8 | 8 |
| 1 | 2 | 2 | 10 |
| 1 | | 10 | 20 |
| 2 | 2 | 2 | 22 |
| 2 | | 2 | 24 |
| | 1 | 8 | 32 |
| | 2 | 4 | 36 |
| | | 12 | 48 |
| (8 rows) |
| |
| 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); |
| a | b | sum |
| ---+---+----- |
| 1 | 1 | 21 |
| 1 | 2 | 25 |
| 1 | 3 | 14 |
| 1 | | 60 |
| 2 | 3 | 15 |
| 2 | | 15 |
| 3 | 3 | 16 |
| 3 | 4 | 17 |
| 3 | | 33 |
| 4 | 1 | 37 |
| 4 | | 37 |
| | | 145 |
| (12 rows) |
| |
| select a, b, sum(v.x) |
| from (values (1),(2)) v(x), gstest_data(v.x) |
| group by cube (a,b) order by a,b; |
| a | b | sum |
| ---+---+----- |
| 1 | 1 | 1 |
| 1 | 2 | 1 |
| 1 | 3 | 1 |
| 1 | | 3 |
| 2 | 1 | 2 |
| 2 | 2 | 2 |
| 2 | 3 | 2 |
| 2 | | 6 |
| | 1 | 3 |
| | 2 | 3 |
| | 3 | 3 |
| | | 9 |
| (12 rows) |
| |
| -- Test reordering of grouping sets |
| explain (costs off) |
| select * from gstest1 group by grouping sets((a,b,v),(v)) order by v,b,a; |
| QUERY PLAN |
| ------------------------------------------------------------------------------ |
| GroupAggregate |
| Group Key: "*VALUES*".column3, "*VALUES*".column2, "*VALUES*".column1 |
| Group Key: "*VALUES*".column3 |
| -> Sort |
| Sort Key: "*VALUES*".column3, "*VALUES*".column2, "*VALUES*".column1 |
| -> Values Scan on "*VALUES*" |
| (6 rows) |
| |
| -- Agg level check. This query should error out. |
| select (select grouping(a,b) from gstest2) from gstest2 group by a,b; |
| ERROR: arguments to GROUPING must be grouping expressions of the associated query level |
| LINE 1: select (select grouping(a,b) from gstest2) from gstest2 grou... |
| ^ |
| --Nested queries |
| select a, b, sum(c), count(*) from gstest2 group by grouping sets (rollup(a,b),a); |
| a | b | sum | count |
| ---+---+-----+------- |
| 1 | 1 | 8 | 7 |
| 1 | 2 | 2 | 1 |
| 1 | | 10 | 8 |
| 1 | | 10 | 8 |
| 2 | 2 | 2 | 1 |
| 2 | | 2 | 1 |
| 2 | | 2 | 1 |
| | | 12 | 9 |
| (8 rows) |
| |
| -- HAVING queries |
| select ten, sum(distinct four) from onek a |
| group by grouping sets((ten,four),(ten)) |
| having exists (select 1 from onek b where sum(distinct a.four) = b.four); |
| ten | sum |
| -----+----- |
| 0 | 0 |
| 0 | 2 |
| 0 | 2 |
| 1 | 1 |
| 1 | 3 |
| 2 | 0 |
| 2 | 2 |
| 2 | 2 |
| 3 | 1 |
| 3 | 3 |
| 4 | 0 |
| 4 | 2 |
| 4 | 2 |
| 5 | 1 |
| 5 | 3 |
| 6 | 0 |
| 6 | 2 |
| 6 | 2 |
| 7 | 1 |
| 7 | 3 |
| 8 | 0 |
| 8 | 2 |
| 8 | 2 |
| 9 | 1 |
| 9 | 3 |
| (25 rows) |
| |
| -- Tests around pushdown of HAVING clauses, partially testing against previous bugs |
| select a,count(*) from gstest2 group by rollup(a) order by a; |
| a | count |
| ---+------- |
| 1 | 8 |
| 2 | 1 |
| | 9 |
| (3 rows) |
| |
| select a,count(*) from gstest2 group by rollup(a) having a is distinct from 1 order by a; |
| a | count |
| ---+------- |
| 2 | 1 |
| | 9 |
| (2 rows) |
| |
| explain (costs off) |
| select a,count(*) from gstest2 group by rollup(a) having a is distinct from 1 order by a; |
| QUERY PLAN |
| ------------------------------------------------ |
| GroupAggregate |
| Group Key: a |
| Group Key: () |
| Filter: (a IS DISTINCT FROM 1) |
| -> Gather Motion 3:1 (slice1; segments: 3) |
| Merge Key: a |
| -> Sort |
| Sort Key: a |
| -> Seq Scan on gstest2 |
| Optimizer: Postgres query optimizer |
| (10 rows) |
| |
| select v.c, (select count(*) from gstest2 group by () having v.c) |
| from (values (false),(true)) v(c) order by v.c; |
| c | count |
| ---+------- |
| f | |
| t | 9 |
| (2 rows) |
| |
| explain (costs off) |
| select v.c, (select count(*) from gstest2 group by () having v.c) |
| from (values (false),(true)) v(c) order by v.c; |
| QUERY PLAN |
| -------------------------------------------------------------------------- |
| Sort |
| Sort Key: "*VALUES*".column1 |
| -> Values Scan on "*VALUES*" |
| SubPlan 1 |
| -> Aggregate |
| Group Key: () |
| Filter: "*VALUES*".column1 |
| -> Result |
| One-Time Filter: "*VALUES*".column1 |
| -> Materialize |
| -> Gather Motion 3:1 (slice1; segments: 3) |
| -> Seq Scan on gstest2 |
| Optimizer: Postgres query optimizer |
| (13 rows) |
| |
| -- HAVING with constant-false predicate on an empty grouping set must emit |
| -- zero rows, not the default scalar-aggregate row. |
| select count(*) from gstest2 group by grouping sets (()) having false; |
| count |
| ------- |
| (0 rows) |
| |
| explain (costs off) |
| select count(*) from gstest2 group by grouping sets (()) having false; |
| QUERY PLAN |
| ------------------------------------- |
| Aggregate |
| Group Key: () |
| Filter: false |
| -> Result |
| One-Time Filter: false |
| Optimizer: Postgres query optimizer |
| (6 rows) |
| |
| -- HAVING with GROUPING queries |
| select ten, grouping(ten) from onek |
| group by grouping sets(ten) having grouping(ten) >= 0 |
| order by 2,1; |
| ten | grouping |
| -----+---------- |
| 0 | 0 |
| 1 | 0 |
| 2 | 0 |
| 3 | 0 |
| 4 | 0 |
| 5 | 0 |
| 6 | 0 |
| 7 | 0 |
| 8 | 0 |
| 9 | 0 |
| (10 rows) |
| |
| select ten, grouping(ten) from onek |
| group by grouping sets(ten, four) having grouping(ten) > 0 |
| order by 2,1; |
| ten | grouping |
| -----+---------- |
| | 1 |
| | 1 |
| | 1 |
| | 1 |
| (4 rows) |
| |
| select ten, grouping(ten) from onek |
| group by rollup(ten) having grouping(ten) > 0 |
| order by 2,1; |
| ten | grouping |
| -----+---------- |
| | 1 |
| (1 row) |
| |
| select ten, grouping(ten) from onek |
| group by cube(ten) having grouping(ten) > 0 |
| order by 2,1; |
| ten | grouping |
| -----+---------- |
| | 1 |
| (1 row) |
| |
| select ten, grouping(ten) from onek |
| group by (ten) having grouping(ten) >= 0 |
| order by 2,1; |
| ten | grouping |
| -----+---------- |
| 0 | 0 |
| 1 | 0 |
| 2 | 0 |
| 3 | 0 |
| 4 | 0 |
| 5 | 0 |
| 6 | 0 |
| 7 | 0 |
| 8 | 0 |
| 9 | 0 |
| (10 rows) |
| |
| -- FILTER queries |
| select ten, sum(distinct four) filter (where four::text ~ '123') from onek a |
| group by rollup(ten); |
| ten | sum |
| -----+----- |
| 0 | |
| 1 | |
| 2 | |
| 3 | |
| 4 | |
| 5 | |
| 6 | |
| 7 | |
| 8 | |
| 9 | |
| | |
| (11 rows) |
| |
| -- More rescan tests |
| 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; |
| a | a | four | ten | count |
| ---+---+------+-----+------- |
| 1 | 1 | 0 | 0 | 50 |
| 1 | 1 | 0 | 2 | 50 |
| 1 | 1 | 0 | 4 | 50 |
| 1 | 1 | 0 | 6 | 50 |
| 1 | 1 | 0 | 8 | 50 |
| 1 | 1 | 0 | | 250 |
| 1 | 1 | 1 | 1 | 50 |
| 1 | 1 | 1 | 3 | 50 |
| 1 | 1 | 1 | 5 | 50 |
| 1 | 1 | 1 | 7 | 50 |
| 1 | 1 | 1 | 9 | 50 |
| 1 | 1 | 1 | | 250 |
| 1 | 1 | 2 | 0 | 50 |
| 1 | 1 | 2 | 2 | 50 |
| 1 | 1 | 2 | 4 | 50 |
| 1 | 1 | 2 | 6 | 50 |
| 1 | 1 | 2 | 8 | 50 |
| 1 | 1 | 2 | | 250 |
| 1 | 1 | 3 | 1 | 50 |
| 1 | 1 | 3 | 3 | 50 |
| 1 | 1 | 3 | 5 | 50 |
| 1 | 1 | 3 | 7 | 50 |
| 1 | 1 | 3 | 9 | 50 |
| 1 | 1 | 3 | | 250 |
| 1 | 1 | | 0 | 100 |
| 1 | 1 | | 1 | 100 |
| 1 | 1 | | 2 | 100 |
| 1 | 1 | | 3 | 100 |
| 1 | 1 | | 4 | 100 |
| 1 | 1 | | 5 | 100 |
| 1 | 1 | | 6 | 100 |
| 1 | 1 | | 7 | 100 |
| 1 | 1 | | 8 | 100 |
| 1 | 1 | | 9 | 100 |
| 1 | 1 | | | 1000 |
| 2 | 2 | 0 | 0 | 50 |
| 2 | 2 | 0 | 2 | 50 |
| 2 | 2 | 0 | 4 | 50 |
| 2 | 2 | 0 | 6 | 50 |
| 2 | 2 | 0 | 8 | 50 |
| 2 | 2 | 0 | | 250 |
| 2 | 2 | 1 | 1 | 50 |
| 2 | 2 | 1 | 3 | 50 |
| 2 | 2 | 1 | 5 | 50 |
| 2 | 2 | 1 | 7 | 50 |
| 2 | 2 | 1 | 9 | 50 |
| 2 | 2 | 1 | | 250 |
| 2 | 2 | 2 | 0 | 50 |
| 2 | 2 | 2 | 2 | 50 |
| 2 | 2 | 2 | 4 | 50 |
| 2 | 2 | 2 | 6 | 50 |
| 2 | 2 | 2 | 8 | 50 |
| 2 | 2 | 2 | | 250 |
| 2 | 2 | 3 | 1 | 50 |
| 2 | 2 | 3 | 3 | 50 |
| 2 | 2 | 3 | 5 | 50 |
| 2 | 2 | 3 | 7 | 50 |
| 2 | 2 | 3 | 9 | 50 |
| 2 | 2 | 3 | | 250 |
| 2 | 2 | | 0 | 100 |
| 2 | 2 | | 1 | 100 |
| 2 | 2 | | 2 | 100 |
| 2 | 2 | | 3 | 100 |
| 2 | 2 | | 4 | 100 |
| 2 | 2 | | 5 | 100 |
| 2 | 2 | | 6 | 100 |
| 2 | 2 | | 7 | 100 |
| 2 | 2 | | 8 | 100 |
| 2 | 2 | | 9 | 100 |
| 2 | 2 | | | 1000 |
| (70 rows) |
| |
| 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); |
| array |
| ------------------------------------------------------------------------------------------------------------------------------------------------------ |
| {"(1,0,0,250)","(1,0,2,250)","(1,0,,500)","(1,1,1,250)","(1,1,3,250)","(1,1,,500)","(1,,0,250)","(1,,1,250)","(1,,2,250)","(1,,3,250)","(1,,,1000)"} |
| {"(2,0,0,250)","(2,0,2,250)","(2,0,,500)","(2,1,1,250)","(2,1,3,250)","(2,1,,500)","(2,,0,250)","(2,,1,250)","(2,,2,250)","(2,,3,250)","(2,,,1000)"} |
| (2 rows) |
| |
| -- Grouping on text columns |
| select sum(ten) from onek group by two, rollup(four::text) order by 1; |
| sum |
| ------ |
| 1000 |
| 1000 |
| 1250 |
| 1250 |
| 2000 |
| 2500 |
| (6 rows) |
| |
| select sum(ten) from onek group by rollup(four::text), two order by 1; |
| sum |
| ------ |
| 1000 |
| 1000 |
| 1250 |
| 1250 |
| 2000 |
| 2500 |
| (6 rows) |
| |
| -- hashing support |
| set enable_hashagg = true; |
| -- failure cases |
| select count(*) from gstest4 group by rollup(unhashable_col,unsortable_col); |
| ERROR: could not implement GROUP BY |
| DETAIL: Some of the datatypes only support hashing, while others only support sorting. |
| select array_agg(v order by v) from gstest4 group by grouping sets ((id,unsortable_col),(id)); |
| ERROR: could not implement GROUP BY |
| DETAIL: Some of the datatypes only support hashing, while others only support sorting. |
| -- simple cases |
| select a, b, grouping(a,b), sum(v), count(*), max(v) |
| from gstest1 group by grouping sets ((a),(b)) order by 3,1,2; |
| a | b | grouping | sum | count | max |
| ---+---+----------+-----+-------+----- |
| 1 | | 1 | 60 | 5 | 14 |
| 2 | | 1 | 15 | 1 | 15 |
| 3 | | 1 | 33 | 2 | 17 |
| 4 | | 1 | 37 | 2 | 19 |
| | 1 | 2 | 58 | 4 | 19 |
| | 2 | 2 | 25 | 2 | 13 |
| | 3 | 2 | 45 | 3 | 16 |
| | 4 | 2 | 17 | 1 | 17 |
| (8 rows) |
| |
| explain (costs off) select a, b, grouping(a,b), sum(v), count(*), max(v) |
| from gstest1 group by grouping sets ((a),(b)) order by 3,1,2; |
| QUERY PLAN |
| -------------------------------------------------------------------------------------------------------- |
| Sort |
| Sort Key: (GROUPING("*VALUES*".column1, "*VALUES*".column2)), "*VALUES*".column1, "*VALUES*".column2 |
| -> HashAggregate |
| Hash Key: "*VALUES*".column1 |
| Hash Key: "*VALUES*".column2 |
| -> Values Scan on "*VALUES*" |
| (6 rows) |
| |
| select a, b, grouping(a,b), sum(v), count(*), max(v) |
| from gstest1 group by cube(a,b) order by 3,1,2; |
| a | b | grouping | sum | count | max |
| ---+---+----------+-----+-------+----- |
| 1 | 1 | 0 | 21 | 2 | 11 |
| 1 | 2 | 0 | 25 | 2 | 13 |
| 1 | 3 | 0 | 14 | 1 | 14 |
| 2 | 3 | 0 | 15 | 1 | 15 |
| 3 | 3 | 0 | 16 | 1 | 16 |
| 3 | 4 | 0 | 17 | 1 | 17 |
| 4 | 1 | 0 | 37 | 2 | 19 |
| 1 | | 1 | 60 | 5 | 14 |
| 2 | | 1 | 15 | 1 | 15 |
| 3 | | 1 | 33 | 2 | 17 |
| 4 | | 1 | 37 | 2 | 19 |
| | 1 | 2 | 58 | 4 | 19 |
| | 2 | 2 | 25 | 2 | 13 |
| | 3 | 2 | 45 | 3 | 16 |
| | 4 | 2 | 17 | 1 | 17 |
| | | 3 | 145 | 10 | 19 |
| (16 rows) |
| |
| explain (costs off) select a, b, grouping(a,b), sum(v), count(*), max(v) |
| from gstest1 group by cube(a,b) order by 3,1,2; |
| QUERY PLAN |
| -------------------------------------------------------------------------------------------------------- |
| Sort |
| Sort Key: (GROUPING("*VALUES*".column1, "*VALUES*".column2)), "*VALUES*".column1, "*VALUES*".column2 |
| -> MixedAggregate |
| Hash Key: "*VALUES*".column1, "*VALUES*".column2 |
| Hash Key: "*VALUES*".column1 |
| Hash Key: "*VALUES*".column2 |
| Group Key: () |
| -> Values Scan on "*VALUES*" |
| (8 rows) |
| |
| -- shouldn't try and hash |
| explain (costs off) |
| select a, b, grouping(a,b), array_agg(v order by v) |
| from gstest1 group by cube(a,b); |
| QUERY PLAN |
| ---------------------------------------------------------- |
| GroupAggregate |
| Group Key: "*VALUES*".column1, "*VALUES*".column2 |
| Group Key: "*VALUES*".column1 |
| Group Key: () |
| Sort Key: "*VALUES*".column2 |
| Group Key: "*VALUES*".column2 |
| -> Sort |
| Sort Key: "*VALUES*".column1, "*VALUES*".column2 |
| -> Values Scan on "*VALUES*" |
| (9 rows) |
| |
| -- unsortable cases |
| select unsortable_col, count(*) |
| from gstest4 group by grouping sets ((unsortable_col),(unsortable_col)) |
| order by unsortable_col::text; |
| unsortable_col | count |
| ----------------+------- |
| 1 | 4 |
| 1 | 4 |
| 2 | 4 |
| 2 | 4 |
| (4 rows) |
| |
| -- mixed hashable/sortable cases |
| select unhashable_col, unsortable_col, |
| grouping(unhashable_col, unsortable_col), |
| count(*), sum(v) |
| from gstest4 group by grouping sets ((unhashable_col),(unsortable_col)) |
| order by 3, 5; |
| unhashable_col | unsortable_col | grouping | count | sum |
| ----------------+----------------+----------+-------+----- |
| 0000 | | 1 | 2 | 17 |
| 0001 | | 1 | 2 | 34 |
| 0010 | | 1 | 2 | 68 |
| 0011 | | 1 | 2 | 136 |
| | 2 | 2 | 4 | 60 |
| | 1 | 2 | 4 | 195 |
| (6 rows) |
| |
| explain (costs off) |
| select unhashable_col, unsortable_col, |
| grouping(unhashable_col, unsortable_col), |
| count(*), sum(v) |
| from gstest4 group by grouping sets ((unhashable_col),(unsortable_col)) |
| order by 3,5; |
| QUERY PLAN |
| ------------------------------------------------------------------ |
| Sort |
| Sort Key: (GROUPING(unhashable_col, unsortable_col)), (sum(v)) |
| -> MixedAggregate |
| Hash Key: unsortable_col |
| Group Key: unhashable_col |
| -> Gather Motion 3:1 (slice1; segments: 3) |
| Merge Key: unhashable_col |
| -> Sort |
| Sort Key: unhashable_col |
| -> Seq Scan on gstest4 |
| Optimizer: Postgres query optimizer |
| (11 rows) |
| |
| select unhashable_col, unsortable_col, |
| grouping(unhashable_col, unsortable_col), |
| count(*), sum(v) |
| from gstest4 group by grouping sets ((v,unhashable_col),(v,unsortable_col)) |
| order by 3,5; |
| unhashable_col | unsortable_col | grouping | count | sum |
| ----------------+----------------+----------+-------+----- |
| 0000 | | 1 | 1 | 1 |
| 0001 | | 1 | 1 | 2 |
| 0010 | | 1 | 1 | 4 |
| 0011 | | 1 | 1 | 8 |
| 0000 | | 1 | 1 | 16 |
| 0001 | | 1 | 1 | 32 |
| 0010 | | 1 | 1 | 64 |
| 0011 | | 1 | 1 | 128 |
| | 1 | 2 | 1 | 1 |
| | 1 | 2 | 1 | 2 |
| | 2 | 2 | 1 | 4 |
| | 2 | 2 | 1 | 8 |
| | 2 | 2 | 1 | 16 |
| | 2 | 2 | 1 | 32 |
| | 1 | 2 | 1 | 64 |
| | 1 | 2 | 1 | 128 |
| (16 rows) |
| |
| explain (costs off) |
| select unhashable_col, unsortable_col, |
| grouping(unhashable_col, unsortable_col), |
| count(*), sum(v) |
| from gstest4 group by grouping sets ((v,unhashable_col),(v,unsortable_col)) |
| order by 3,5; |
| QUERY PLAN |
| ------------------------------------------------------------------------ |
| Gather Motion 3:1 (slice1; segments: 3) |
| Merge Key: (GROUPING(unhashable_col, unsortable_col)), (sum(v)) |
| -> Sort |
| Sort Key: (GROUPING(unhashable_col, unsortable_col)), (sum(v)) |
| -> MixedAggregate |
| Hash Key: v, unsortable_col |
| Group Key: v, unhashable_col |
| -> Sort |
| Sort Key: v, unhashable_col |
| -> Redistribute Motion 3:3 (slice2; segments: 3) |
| Hash Key: v |
| -> Seq Scan on gstest4 |
| Optimizer: Postgres query optimizer |
| (13 rows) |
| |
| select unsortable_col1, unsortable_col2, |
| grouping(unsortable_col1, unsortable_col2), |
| count(*), sum(v) |
| from gstest5 group by grouping sets ((unsortable_col1),(unsortable_col2)) |
| order by 3,5; |
| unsortable_col1 | unsortable_col2 | grouping | count | sum |
| -----------------+-----------------+----------+-------+----- |
| 4 | | 1 | 4 | 60 |
| 3 | | 1 | 4 | 195 |
| | 2 | 2 | 4 | 60 |
| | 1 | 2 | 4 | 195 |
| (4 rows) |
| |
| explain (costs off) |
| select unsortable_col1, unsortable_col2, |
| grouping(unsortable_col1, unsortable_col2), |
| count(*), sum(v) |
| from gstest5 group by grouping sets ((unsortable_col1),(unsortable_col2)) |
| order by 3,5; |
| QUERY PLAN |
| ------------------------------------------------------------------------------- |
| Gather Motion 3:1 (slice1; segments: 3) |
| Merge Key: (GROUPING(unsortable_col1, unsortable_col2)), (sum(v)) |
| -> Sort |
| Sort Key: (GROUPING(unsortable_col1, unsortable_col2)), (sum(v)) |
| -> Finalize HashAggregate |
| Group Key: unsortable_col1, unsortable_col2, (GROUPINGSET_ID()) |
| -> Redistribute Motion 3:3 (slice2; segments: 3) |
| Hash Key: (GROUPINGSET_ID()) |
| -> Partial HashAggregate |
| Hash Key: unsortable_col1 |
| Hash Key: unsortable_col2 |
| -> Seq Scan on gstest5 |
| Optimizer: Postgres query optimizer |
| (13 rows) |
| |
| select unsortable_col1, unsortable_col2, |
| grouping(unsortable_col1, unsortable_col2), |
| count(*), sum(v) |
| from gstest5 group by grouping sets ((unsortable_col1),(unsortable_col2),()) |
| order by 3,5; |
| unsortable_col1 | unsortable_col2 | grouping | count | sum |
| -----------------+-----------------+----------+-------+----- |
| 4 | | 1 | 4 | 60 |
| 3 | | 1 | 4 | 195 |
| | 2 | 2 | 4 | 60 |
| | 1 | 2 | 4 | 195 |
| | | 3 | 8 | 255 |
| (5 rows) |
| |
| explain (costs off) |
| select unsortable_col1, unsortable_col2, |
| grouping(unsortable_col1, unsortable_col2), |
| count(*), sum(v) |
| from gstest5 group by grouping sets ((unsortable_col1),(unsortable_col2),()) |
| order by 3,5; |
| QUERY PLAN |
| ------------------------------------------------------------------------------- |
| Gather Motion 3:1 (slice1; segments: 3) |
| Merge Key: (GROUPING(unsortable_col1, unsortable_col2)), (sum(v)) |
| -> Sort |
| Sort Key: (GROUPING(unsortable_col1, unsortable_col2)), (sum(v)) |
| -> Finalize HashAggregate |
| Group Key: unsortable_col1, unsortable_col2, (GROUPINGSET_ID()) |
| -> Redistribute Motion 3:3 (slice2; segments: 3) |
| Hash Key: (GROUPINGSET_ID()) |
| -> Partial MixedAggregate |
| Hash Key: unsortable_col1 |
| Hash Key: unsortable_col2 |
| Group Key: () |
| -> Seq Scan on gstest5 |
| Optimizer: Postgres query optimizer |
| (14 rows) |
| |
| -- empty input: first is 0 rows, second 1, third 3 etc. |
| select a, b, sum(v), count(*) from gstest_empty group by grouping sets ((a,b),a); |
| a | b | sum | count |
| ---+---+-----+------- |
| (0 rows) |
| |
| explain (costs off) |
| select a, b, sum(v), count(*) from gstest_empty group by grouping sets ((a,b),a); |
| QUERY PLAN |
| ------------------------------------------ |
| Gather Motion 3:1 (slice1; segments: 3) |
| -> HashAggregate |
| Hash Key: a, b |
| Hash Key: a |
| -> Seq Scan on gstest_empty |
| Optimizer: Postgres query optimizer |
| (6 rows) |
| |
| select a, b, sum(v), count(*) from gstest_empty group by grouping sets ((a,b),()); |
| a | b | sum | count |
| ---+---+-----+------- |
| | | | 0 |
| (1 row) |
| |
| select a, b, sum(v), count(*) from gstest_empty group by grouping sets ((a,b),(),(),()); |
| a | b | sum | count |
| ---+---+-----+------- |
| | | | 0 |
| | | | 0 |
| | | | 0 |
| (3 rows) |
| |
| explain (costs off) |
| select a, b, sum(v), count(*) from gstest_empty group by grouping sets ((a,b),(),(),()); |
| QUERY PLAN |
| ------------------------------------------------------------ |
| Gather Motion 3:1 (slice1; segments: 3) |
| -> Finalize HashAggregate |
| Group Key: a, b, (GROUPINGSET_ID()) |
| -> Redistribute Motion 3:3 (slice2; segments: 3) |
| Hash Key: a, b, (GROUPINGSET_ID()) |
| -> Partial MixedAggregate |
| Hash Key: a, b |
| Group Key: () |
| Group Key: () |
| Group Key: () |
| -> Seq Scan on gstest_empty |
| Optimizer: Postgres query optimizer |
| (8 rows) |
| |
| select sum(v), count(*) from gstest_empty group by grouping sets ((),(),()); |
| sum | count |
| -----+------- |
| | 0 |
| | 0 |
| | 0 |
| (3 rows) |
| |
| explain (costs off) |
| select sum(v), count(*) from gstest_empty group by grouping sets ((),(),()); |
| QUERY PLAN |
| -------------------------------------------------- |
| Finalize GroupAggregate |
| Group Key: (GROUPINGSET_ID()) |
| -> Gather Motion 3:1 (slice1; segments: 3) |
| Merge Key: (GROUPINGSET_ID()) |
| -> Sort |
| Sort Key: (GROUPINGSET_ID()) |
| -> Partial Aggregate |
| Group Key: () |
| Group Key: () |
| Group Key: () |
| -> Seq Scan on gstest_empty |
| Optimizer: Postgres query optimizer |
| (12 rows) |
| |
| -- check that functionally dependent cols are not nulled |
| select a, d, grouping(a,b,c) |
| from gstest3 |
| group by grouping sets ((a,b), (a,c)); |
| a | d | grouping |
| ---+---+---------- |
| 1 | 1 | 1 |
| 2 | 2 | 1 |
| 1 | 1 | 2 |
| 2 | 2 | 2 |
| (4 rows) |
| |
| explain (costs off) |
| select a, d, grouping(a,b,c) |
| from gstest3 |
| group by grouping sets ((a,b), (a,c)); |
| QUERY PLAN |
| ------------------------------------------ |
| Gather Motion 3:1 (slice1; segments: 3) |
| -> HashAggregate |
| Hash Key: a, b |
| Hash Key: a, c |
| -> Seq Scan on gstest3 |
| Optimizer: Postgres query optimizer |
| (6 rows) |
| |
| -- simple rescan tests |
| select a, b, sum(v.x) |
| from (values (1),(2)) v(x), gstest_data(v.x) |
| group by grouping sets (a,b) |
| order by 1, 2, 3; |
| a | b | sum |
| ---+---+----- |
| 1 | | 3 |
| 2 | | 6 |
| | 1 | 3 |
| | 2 | 3 |
| | 3 | 3 |
| (5 rows) |
| |
| explain (costs off) |
| select a, b, sum(v.x) |
| from (values (1),(2)) v(x), gstest_data(v.x) |
| group by grouping sets (a,b) |
| order by 3, 1, 2; |
| QUERY PLAN |
| --------------------------------------------------------------------- |
| Sort |
| Sort Key: (sum("*VALUES*".column1)), gstest_data.a, gstest_data.b |
| -> HashAggregate |
| Hash Key: gstest_data.a |
| Hash Key: gstest_data.b |
| -> Nested Loop |
| -> Values Scan on "*VALUES*" |
| -> Function Scan on gstest_data |
| (8 rows) |
| |
| select * |
| from (values (1),(2)) v(x), |
| lateral (select a, b, sum(v.x) from gstest_data(v.x) group by grouping sets (a,b)) s; |
| ERROR: aggregate functions are not allowed in FROM clause of their own query level |
| LINE 3: lateral (select a, b, sum(v.x) from gstest_data(v.x) ... |
| ^ |
| explain (costs off) |
| select * |
| from (values (1),(2)) v(x), |
| lateral (select a, b, sum(v.x) from gstest_data(v.x) group by grouping sets (a,b)) s; |
| ERROR: aggregate functions are not allowed in FROM clause of their own query level |
| LINE 4: lateral (select a, b, sum(v.x) from gstest_data(v.x... |
| ^ |
| -- Tests for chained aggregates |
| select a, b, grouping(a,b), sum(v), count(*), max(v) |
| from gstest1 group by grouping sets ((a,b),(a+1,b+1),(a+2,b+2)) order by 3,6; |
| a | b | grouping | sum | count | max |
| ---+---+----------+-----+-------+----- |
| 1 | 1 | 0 | 21 | 2 | 11 |
| 1 | 2 | 0 | 25 | 2 | 13 |
| 1 | 3 | 0 | 14 | 1 | 14 |
| 2 | 3 | 0 | 15 | 1 | 15 |
| 3 | 3 | 0 | 16 | 1 | 16 |
| 3 | 4 | 0 | 17 | 1 | 17 |
| 4 | 1 | 0 | 37 | 2 | 19 |
| | | 3 | 21 | 2 | 11 |
| | | 3 | 21 | 2 | 11 |
| | | 3 | 25 | 2 | 13 |
| | | 3 | 25 | 2 | 13 |
| | | 3 | 14 | 1 | 14 |
| | | 3 | 14 | 1 | 14 |
| | | 3 | 15 | 1 | 15 |
| | | 3 | 15 | 1 | 15 |
| | | 3 | 16 | 1 | 16 |
| | | 3 | 16 | 1 | 16 |
| | | 3 | 17 | 1 | 17 |
| | | 3 | 17 | 1 | 17 |
| | | 3 | 37 | 2 | 19 |
| | | 3 | 37 | 2 | 19 |
| (21 rows) |
| |
| explain (costs off) |
| select a, b, grouping(a,b), sum(v), count(*), max(v) |
| from gstest1 group by grouping sets ((a,b),(a+1,b+1),(a+2,b+2)) order by 3,6; |
| QUERY PLAN |
| ------------------------------------------------------------------------------------------- |
| Sort |
| Sort Key: (GROUPING("*VALUES*".column1, "*VALUES*".column2)), (max("*VALUES*".column3)) |
| -> HashAggregate |
| Hash Key: "*VALUES*".column1, "*VALUES*".column2 |
| Hash Key: ("*VALUES*".column1 + 1), ("*VALUES*".column2 + 1) |
| Hash Key: ("*VALUES*".column1 + 2), ("*VALUES*".column2 + 2) |
| -> Values Scan on "*VALUES*" |
| (7 rows) |
| |
| select a, b, sum(c), sum(d), sum(e), sum(sum(c)) over (order by a,b) as rsum |
| from gstest2 group by cube (a,b) order by rsum, a, b; |
| a | b | sum | sum | sum | rsum |
| ---+---+-----+-----+-----+------ |
| 1 | 1 | 8 | 9 | 10 | 8 |
| 1 | 2 | 2 | 2 | 2 | 10 |
| 1 | | 10 | 11 | 12 | 20 |
| 2 | 2 | 2 | 2 | 2 | 22 |
| 2 | | 2 | 2 | 2 | 24 |
| | 1 | 8 | 9 | 10 | 32 |
| | 2 | 4 | 4 | 4 | 36 |
| | | 12 | 13 | 14 | 48 |
| (8 rows) |
| |
| explain (costs off) |
| select a, b, sum(c), sum(d), sum(e), sum(sum(c)) over (order by a,b) as rsum |
| from gstest2 group by cube (a,b) order by rsum, a, b; |
| QUERY PLAN |
| ------------------------------------------------------------------------------ |
| Sort |
| Sort Key: (sum((sum(c))) OVER (?)), a, b |
| -> WindowAgg |
| Order By: a, b |
| -> Gather Motion 3:1 (slice1; segments: 3) |
| Merge Key: a, b |
| -> Sort |
| Sort Key: a, b |
| -> Finalize HashAggregate |
| Group Key: a, b, (GROUPINGSET_ID()) |
| -> Redistribute Motion 3:3 (slice2; segments: 3) |
| Hash Key: a, b, (GROUPINGSET_ID()) |
| -> Partial GroupAggregate |
| Group Key: a, b |
| Group Key: a |
| Group Key: () |
| Sort Key: b |
| Group Key: b |
| -> Sort |
| Sort Key: a, b |
| -> Seq Scan on gstest2 |
| Optimizer: Postgres query optimizer |
| (22 rows) |
| |
| select a, b, sum(v.x) |
| from (values (1),(2)) v(x), gstest_data(v.x) |
| group by cube (a,b) order by a,b; |
| a | b | sum |
| ---+---+----- |
| 1 | 1 | 1 |
| 1 | 2 | 1 |
| 1 | 3 | 1 |
| 1 | | 3 |
| 2 | 1 | 2 |
| 2 | 2 | 2 |
| 2 | 3 | 2 |
| 2 | | 6 |
| | 1 | 3 |
| | 2 | 3 |
| | 3 | 3 |
| | | 9 |
| (12 rows) |
| |
| explain (costs off) |
| select a, b, sum(v.x) |
| from (values (1),(2)) v(x), gstest_data(v.x) |
| group by cube (a,b) order by a,b; |
| QUERY PLAN |
| ------------------------------------------------ |
| Sort |
| Sort Key: gstest_data.a, gstest_data.b |
| -> MixedAggregate |
| Hash Key: gstest_data.a, gstest_data.b |
| Hash Key: gstest_data.a |
| Hash Key: gstest_data.b |
| Group Key: () |
| -> Nested Loop |
| -> Values Scan on "*VALUES*" |
| -> Function Scan on gstest_data |
| (10 rows) |
| |
| -- Verify that we correctly handle the child node returning a |
| -- non-minimal slot, which happens if the input is pre-sorted, |
| -- e.g. due to an index scan. |
| BEGIN; |
| SET LOCAL enable_hashagg = false; |
| EXPLAIN (COSTS OFF) SELECT a, b, count(*), max(a), max(b) FROM gstest3 GROUP BY GROUPING SETS(a, b,()) ORDER BY a, b; |
| QUERY PLAN |
| ------------------------------------------------------------------ |
| Finalize GroupAggregate |
| Group Key: a, b, (GROUPINGSET_ID()) |
| -> Gather Motion 3:1 (slice1; segments: 3) |
| Merge Key: a, b, (GROUPINGSET_ID()) |
| -> Sort |
| Sort Key: a, b, (GROUPINGSET_ID()) |
| -> Partial GroupAggregate |
| Group Key: a |
| Group Key: () |
| Sort Key: b |
| Group Key: b |
| -> Index Scan using gstest3_pkey on gstest3 |
| Optimizer: Postgres query optimizer |
| (13 rows) |
| |
| SELECT a, b, count(*), max(a), max(b) FROM gstest3 GROUP BY GROUPING SETS(a, b,()) ORDER BY a, b; |
| a | b | count | max | max |
| ---+---+-------+-----+----- |
| 1 | | 1 | 1 | 1 |
| 2 | | 1 | 2 | 2 |
| | 1 | 1 | 1 | 1 |
| | 2 | 1 | 2 | 2 |
| | | 2 | 2 | 2 |
| (5 rows) |
| |
| SET LOCAL enable_seqscan = false; |
| EXPLAIN (COSTS OFF) SELECT a, b, count(*), max(a), max(b) FROM gstest3 GROUP BY GROUPING SETS(a, b,()) ORDER BY a, b; |
| QUERY PLAN |
| ------------------------------------------------------------------ |
| Finalize GroupAggregate |
| Group Key: a, b, (GROUPINGSET_ID()) |
| -> Gather Motion 3:1 (slice1; segments: 3) |
| Merge Key: a, b, (GROUPINGSET_ID()) |
| -> Sort |
| Sort Key: a, b, (GROUPINGSET_ID()) |
| -> Partial GroupAggregate |
| Group Key: a |
| Group Key: () |
| Sort Key: b |
| Group Key: b |
| -> Index Scan using gstest3_pkey on gstest3 |
| Optimizer: Postgres query optimizer |
| (13 rows) |
| |
| SELECT a, b, count(*), max(a), max(b) FROM gstest3 GROUP BY GROUPING SETS(a, b,()) ORDER BY a, b; |
| a | b | count | max | max |
| ---+---+-------+-----+----- |
| 1 | | 1 | 1 | 1 |
| 2 | | 1 | 2 | 2 |
| | 1 | 1 | 1 | 1 |
| | 2 | 1 | 2 | 2 |
| | | 2 | 2 | 2 |
| (5 rows) |
| |
| COMMIT; |
| -- More rescan tests |
| 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; |
| a | a | four | ten | count |
| ---+---+------+-----+------- |
| 1 | 1 | 0 | 0 | 50 |
| 1 | 1 | 0 | 2 | 50 |
| 1 | 1 | 0 | 4 | 50 |
| 1 | 1 | 0 | 6 | 50 |
| 1 | 1 | 0 | 8 | 50 |
| 1 | 1 | 0 | | 250 |
| 1 | 1 | 1 | 1 | 50 |
| 1 | 1 | 1 | 3 | 50 |
| 1 | 1 | 1 | 5 | 50 |
| 1 | 1 | 1 | 7 | 50 |
| 1 | 1 | 1 | 9 | 50 |
| 1 | 1 | 1 | | 250 |
| 1 | 1 | 2 | 0 | 50 |
| 1 | 1 | 2 | 2 | 50 |
| 1 | 1 | 2 | 4 | 50 |
| 1 | 1 | 2 | 6 | 50 |
| 1 | 1 | 2 | 8 | 50 |
| 1 | 1 | 2 | | 250 |
| 1 | 1 | 3 | 1 | 50 |
| 1 | 1 | 3 | 3 | 50 |
| 1 | 1 | 3 | 5 | 50 |
| 1 | 1 | 3 | 7 | 50 |
| 1 | 1 | 3 | 9 | 50 |
| 1 | 1 | 3 | | 250 |
| 1 | 1 | | 0 | 100 |
| 1 | 1 | | 1 | 100 |
| 1 | 1 | | 2 | 100 |
| 1 | 1 | | 3 | 100 |
| 1 | 1 | | 4 | 100 |
| 1 | 1 | | 5 | 100 |
| 1 | 1 | | 6 | 100 |
| 1 | 1 | | 7 | 100 |
| 1 | 1 | | 8 | 100 |
| 1 | 1 | | 9 | 100 |
| 1 | 1 | | | 1000 |
| 2 | 2 | 0 | 0 | 50 |
| 2 | 2 | 0 | 2 | 50 |
| 2 | 2 | 0 | 4 | 50 |
| 2 | 2 | 0 | 6 | 50 |
| 2 | 2 | 0 | 8 | 50 |
| 2 | 2 | 0 | | 250 |
| 2 | 2 | 1 | 1 | 50 |
| 2 | 2 | 1 | 3 | 50 |
| 2 | 2 | 1 | 5 | 50 |
| 2 | 2 | 1 | 7 | 50 |
| 2 | 2 | 1 | 9 | 50 |
| 2 | 2 | 1 | | 250 |
| 2 | 2 | 2 | 0 | 50 |
| 2 | 2 | 2 | 2 | 50 |
| 2 | 2 | 2 | 4 | 50 |
| 2 | 2 | 2 | 6 | 50 |
| 2 | 2 | 2 | 8 | 50 |
| 2 | 2 | 2 | | 250 |
| 2 | 2 | 3 | 1 | 50 |
| 2 | 2 | 3 | 3 | 50 |
| 2 | 2 | 3 | 5 | 50 |
| 2 | 2 | 3 | 7 | 50 |
| 2 | 2 | 3 | 9 | 50 |
| 2 | 2 | 3 | | 250 |
| 2 | 2 | | 0 | 100 |
| 2 | 2 | | 1 | 100 |
| 2 | 2 | | 2 | 100 |
| 2 | 2 | | 3 | 100 |
| 2 | 2 | | 4 | 100 |
| 2 | 2 | | 5 | 100 |
| 2 | 2 | | 6 | 100 |
| 2 | 2 | | 7 | 100 |
| 2 | 2 | | 8 | 100 |
| 2 | 2 | | 9 | 100 |
| 2 | 2 | | | 1000 |
| (70 rows) |
| |
| 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); |
| array |
| ------------------------------------------------------------------------------------------------------------------------------------------------------ |
| {"(1,0,0,250)","(1,0,2,250)","(1,0,,500)","(1,1,1,250)","(1,1,3,250)","(1,1,,500)","(1,,0,250)","(1,,1,250)","(1,,2,250)","(1,,3,250)","(1,,,1000)"} |
| {"(2,0,0,250)","(2,0,2,250)","(2,0,,500)","(2,1,1,250)","(2,1,3,250)","(2,1,,500)","(2,,0,250)","(2,,1,250)","(2,,2,250)","(2,,3,250)","(2,,,1000)"} |
| (2 rows) |
| |
| -- Rescan logic changes when there are no empty grouping sets, so test |
| -- that too: |
| 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; |
| a | a | four | ten | count |
| ---+---+------+-----+------- |
| 1 | 1 | 0 | | 250 |
| 1 | 1 | 1 | | 250 |
| 1 | 1 | 2 | | 250 |
| 1 | 1 | 3 | | 250 |
| 1 | 1 | | 0 | 100 |
| 1 | 1 | | 1 | 100 |
| 1 | 1 | | 2 | 100 |
| 1 | 1 | | 3 | 100 |
| 1 | 1 | | 4 | 100 |
| 1 | 1 | | 5 | 100 |
| 1 | 1 | | 6 | 100 |
| 1 | 1 | | 7 | 100 |
| 1 | 1 | | 8 | 100 |
| 1 | 1 | | 9 | 100 |
| 2 | 2 | 0 | | 250 |
| 2 | 2 | 1 | | 250 |
| 2 | 2 | 2 | | 250 |
| 2 | 2 | 3 | | 250 |
| 2 | 2 | | 0 | 100 |
| 2 | 2 | | 1 | 100 |
| 2 | 2 | | 2 | 100 |
| 2 | 2 | | 3 | 100 |
| 2 | 2 | | 4 | 100 |
| 2 | 2 | | 5 | 100 |
| 2 | 2 | | 6 | 100 |
| 2 | 2 | | 7 | 100 |
| 2 | 2 | | 8 | 100 |
| 2 | 2 | | 9 | 100 |
| (28 rows) |
| |
| 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); |
| array |
| --------------------------------------------------------------------------------- |
| {"(1,0,,500)","(1,1,,500)","(1,,0,250)","(1,,1,250)","(1,,2,250)","(1,,3,250)"} |
| {"(2,0,,500)","(2,1,,500)","(2,,0,250)","(2,,1,250)","(2,,2,250)","(2,,3,250)"} |
| (2 rows) |
| |
| -- test the knapsack |
| set hash_mem_multiplier = 1; |
| set enable_indexscan = false; |
| set work_mem = '64kB'; |
| explain (costs off) |
| select unique1, |
| count(two), count(four), count(ten), |
| count(hundred), count(thousand), count(twothousand), |
| count(*) |
| from tenk1 group by grouping sets (unique1,twothousand,thousand,hundred,ten,four,two); |
| QUERY PLAN |
| ----------------------------------------------------------------------------------------------------------- |
| Gather Motion 3:1 (slice1; segments: 3) |
| -> Finalize GroupAggregate |
| Group Key: unique1, twothousand, thousand, hundred, ten, four, two, (GROUPINGSET_ID()) |
| -> Sort |
| Sort Key: unique1, twothousand, thousand, hundred, ten, four, two, (GROUPINGSET_ID()) |
| -> Redistribute Motion 3:3 (slice2; segments: 3) |
| Hash Key: unique1, twothousand, thousand, hundred, ten, four, two, (GROUPINGSET_ID()) |
| -> Partial HashAggregate |
| Hash Key: unique1 |
| Hash Key: twothousand |
| Hash Key: thousand |
| Hash Key: hundred |
| Hash Key: ten |
| Hash Key: four |
| Hash Key: two |
| -> Seq Scan on tenk1 |
| Optimizer: Postgres query optimizer |
| (17 rows) |
| |
| explain (costs off) |
| select unique1, |
| count(two), count(four), count(ten), |
| count(hundred), count(thousand), count(twothousand), |
| count(*) |
| from tenk1 group by grouping sets (unique1,hundred,ten,four,two); |
| QUERY PLAN |
| ------------------------------------------------------------------------------ |
| Finalize GroupAggregate |
| Group Key: unique1, hundred, ten, four, two, (GROUPINGSET_ID()) |
| -> Gather Motion 3:1 (slice1; segments: 3) |
| Merge Key: unique1, hundred, ten, four, two, (GROUPINGSET_ID()) |
| -> Sort |
| Sort Key: unique1, hundred, ten, four, two, (GROUPINGSET_ID()) |
| -> Partial HashAggregate |
| Hash Key: unique1 |
| Hash Key: hundred |
| Hash Key: ten |
| Hash Key: four |
| Hash Key: two |
| -> Seq Scan on tenk1 |
| Optimizer: Postgres query optimizer |
| (14 rows) |
| |
| set work_mem = '384kB'; |
| explain (costs off) |
| select unique1, |
| count(two), count(four), count(ten), |
| count(hundred), count(thousand), count(twothousand), |
| count(*) |
| from tenk1 group by grouping sets (unique1,twothousand,thousand,hundred,ten,four,two); |
| QUERY PLAN |
| ----------------------------------------------------------------------------------------------------- |
| Gather Motion 3:1 (slice1; segments: 3) |
| -> Finalize HashAggregate |
| Group Key: unique1, twothousand, thousand, hundred, ten, four, two, (GROUPINGSET_ID()) |
| -> Redistribute Motion 3:3 (slice2; segments: 3) |
| Hash Key: unique1, twothousand, thousand, hundred, ten, four, two, (GROUPINGSET_ID()) |
| -> Partial HashAggregate |
| Hash Key: unique1 |
| Hash Key: twothousand |
| Hash Key: thousand |
| Hash Key: hundred |
| Hash Key: ten |
| Hash Key: four |
| Hash Key: two |
| -> Seq Scan on tenk1 |
| Optimizer: Postgres query optimizer |
| (15 rows) |
| |
| reset hash_mem_multiplier; |
| -- check collation-sensitive matching between grouping expressions |
| -- (similar to a check for aggregates, but there are additional code |
| -- paths for GROUPING, so check again here) |
| select v||'a', case grouping(v||'a') when 1 then 1 else 0 end, count(*) |
| from unnest(array[1,1], array['a','b']) u(i,v) |
| group by rollup(i, v||'a') order by 1,3; |
| ?column? | case | count |
| ----------+------+------- |
| aa | 0 | 1 |
| ba | 0 | 1 |
| | 1 | 2 |
| | 1 | 2 |
| (4 rows) |
| |
| select v||'a', case when grouping(v||'a') = 1 then 1 else 0 end, count(*) |
| from unnest(array[1,1], array['a','b']) u(i,v) |
| group by rollup(i, v||'a') order by 1,3; |
| ?column? | case | count |
| ----------+------+------- |
| aa | 0 | 1 |
| ba | 0 | 1 |
| | 1 | 2 |
| | 1 | 2 |
| (4 rows) |
| |
| -- Bug #16784 |
| create table bug_16784(i int, j int); |
| analyze bug_16784; |
| alter table bug_16784 set (autovacuum_enabled = 'false'); |
| WARNING: autovacuum is not supported in Cloudberry |
| update pg_class set reltuples = 10 where relname='bug_16784'; |
| ERROR: permission denied: "pg_class" is a system catalog |
| insert into bug_16784 select g/10, g from generate_series(1,40) g; |
| set work_mem='64kB'; |
| set enable_sort = false; |
| select * from |
| (values (1),(2)) v(a), |
| lateral (select a, i, j, count(*) from |
| bug_16784 group by cube(i,j)) s |
| order by v.a, i, j; |
| a | a | i | j | count |
| ---+---+---+----+------- |
| 1 | 1 | 0 | 1 | 1 |
| 1 | 1 | 0 | 2 | 1 |
| 1 | 1 | 0 | 3 | 1 |
| 1 | 1 | 0 | 4 | 1 |
| 1 | 1 | 0 | 5 | 1 |
| 1 | 1 | 0 | 6 | 1 |
| 1 | 1 | 0 | 7 | 1 |
| 1 | 1 | 0 | 8 | 1 |
| 1 | 1 | 0 | 9 | 1 |
| 1 | 1 | 0 | | 9 |
| 1 | 1 | 1 | 10 | 1 |
| 1 | 1 | 1 | 11 | 1 |
| 1 | 1 | 1 | 12 | 1 |
| 1 | 1 | 1 | 13 | 1 |
| 1 | 1 | 1 | 14 | 1 |
| 1 | 1 | 1 | 15 | 1 |
| 1 | 1 | 1 | 16 | 1 |
| 1 | 1 | 1 | 17 | 1 |
| 1 | 1 | 1 | 18 | 1 |
| 1 | 1 | 1 | 19 | 1 |
| 1 | 1 | 1 | | 10 |
| 1 | 1 | 2 | 20 | 1 |
| 1 | 1 | 2 | 21 | 1 |
| 1 | 1 | 2 | 22 | 1 |
| 1 | 1 | 2 | 23 | 1 |
| 1 | 1 | 2 | 24 | 1 |
| 1 | 1 | 2 | 25 | 1 |
| 1 | 1 | 2 | 26 | 1 |
| 1 | 1 | 2 | 27 | 1 |
| 1 | 1 | 2 | 28 | 1 |
| 1 | 1 | 2 | 29 | 1 |
| 1 | 1 | 2 | | 10 |
| 1 | 1 | 3 | 30 | 1 |
| 1 | 1 | 3 | 31 | 1 |
| 1 | 1 | 3 | 32 | 1 |
| 1 | 1 | 3 | 33 | 1 |
| 1 | 1 | 3 | 34 | 1 |
| 1 | 1 | 3 | 35 | 1 |
| 1 | 1 | 3 | 36 | 1 |
| 1 | 1 | 3 | 37 | 1 |
| 1 | 1 | 3 | 38 | 1 |
| 1 | 1 | 3 | 39 | 1 |
| 1 | 1 | 3 | | 10 |
| 1 | 1 | 4 | 40 | 1 |
| 1 | 1 | 4 | | 1 |
| 1 | 1 | | 1 | 1 |
| 1 | 1 | | 2 | 1 |
| 1 | 1 | | 3 | 1 |
| 1 | 1 | | 4 | 1 |
| 1 | 1 | | 5 | 1 |
| 1 | 1 | | 6 | 1 |
| 1 | 1 | | 7 | 1 |
| 1 | 1 | | 8 | 1 |
| 1 | 1 | | 9 | 1 |
| 1 | 1 | | 10 | 1 |
| 1 | 1 | | 11 | 1 |
| 1 | 1 | | 12 | 1 |
| 1 | 1 | | 13 | 1 |
| 1 | 1 | | 14 | 1 |
| 1 | 1 | | 15 | 1 |
| 1 | 1 | | 16 | 1 |
| 1 | 1 | | 17 | 1 |
| 1 | 1 | | 18 | 1 |
| 1 | 1 | | 19 | 1 |
| 1 | 1 | | 20 | 1 |
| 1 | 1 | | 21 | 1 |
| 1 | 1 | | 22 | 1 |
| 1 | 1 | | 23 | 1 |
| 1 | 1 | | 24 | 1 |
| 1 | 1 | | 25 | 1 |
| 1 | 1 | | 26 | 1 |
| 1 | 1 | | 27 | 1 |
| 1 | 1 | | 28 | 1 |
| 1 | 1 | | 29 | 1 |
| 1 | 1 | | 30 | 1 |
| 1 | 1 | | 31 | 1 |
| 1 | 1 | | 32 | 1 |
| 1 | 1 | | 33 | 1 |
| 1 | 1 | | 34 | 1 |
| 1 | 1 | | 35 | 1 |
| 1 | 1 | | 36 | 1 |
| 1 | 1 | | 37 | 1 |
| 1 | 1 | | 38 | 1 |
| 1 | 1 | | 39 | 1 |
| 1 | 1 | | 40 | 1 |
| 1 | 1 | | | 40 |
| 2 | 2 | 0 | 1 | 1 |
| 2 | 2 | 0 | 2 | 1 |
| 2 | 2 | 0 | 3 | 1 |
| 2 | 2 | 0 | 4 | 1 |
| 2 | 2 | 0 | 5 | 1 |
| 2 | 2 | 0 | 6 | 1 |
| 2 | 2 | 0 | 7 | 1 |
| 2 | 2 | 0 | 8 | 1 |
| 2 | 2 | 0 | 9 | 1 |
| 2 | 2 | 0 | | 9 |
| 2 | 2 | 1 | 10 | 1 |
| 2 | 2 | 1 | 11 | 1 |
| 2 | 2 | 1 | 12 | 1 |
| 2 | 2 | 1 | 13 | 1 |
| 2 | 2 | 1 | 14 | 1 |
| 2 | 2 | 1 | 15 | 1 |
| 2 | 2 | 1 | 16 | 1 |
| 2 | 2 | 1 | 17 | 1 |
| 2 | 2 | 1 | 18 | 1 |
| 2 | 2 | 1 | 19 | 1 |
| 2 | 2 | 1 | | 10 |
| 2 | 2 | 2 | 20 | 1 |
| 2 | 2 | 2 | 21 | 1 |
| 2 | 2 | 2 | 22 | 1 |
| 2 | 2 | 2 | 23 | 1 |
| 2 | 2 | 2 | 24 | 1 |
| 2 | 2 | 2 | 25 | 1 |
| 2 | 2 | 2 | 26 | 1 |
| 2 | 2 | 2 | 27 | 1 |
| 2 | 2 | 2 | 28 | 1 |
| 2 | 2 | 2 | 29 | 1 |
| 2 | 2 | 2 | | 10 |
| 2 | 2 | 3 | 30 | 1 |
| 2 | 2 | 3 | 31 | 1 |
| 2 | 2 | 3 | 32 | 1 |
| 2 | 2 | 3 | 33 | 1 |
| 2 | 2 | 3 | 34 | 1 |
| 2 | 2 | 3 | 35 | 1 |
| 2 | 2 | 3 | 36 | 1 |
| 2 | 2 | 3 | 37 | 1 |
| 2 | 2 | 3 | 38 | 1 |
| 2 | 2 | 3 | 39 | 1 |
| 2 | 2 | 3 | | 10 |
| 2 | 2 | 4 | 40 | 1 |
| 2 | 2 | 4 | | 1 |
| 2 | 2 | | 1 | 1 |
| 2 | 2 | | 2 | 1 |
| 2 | 2 | | 3 | 1 |
| 2 | 2 | | 4 | 1 |
| 2 | 2 | | 5 | 1 |
| 2 | 2 | | 6 | 1 |
| 2 | 2 | | 7 | 1 |
| 2 | 2 | | 8 | 1 |
| 2 | 2 | | 9 | 1 |
| 2 | 2 | | 10 | 1 |
| 2 | 2 | | 11 | 1 |
| 2 | 2 | | 12 | 1 |
| 2 | 2 | | 13 | 1 |
| 2 | 2 | | 14 | 1 |
| 2 | 2 | | 15 | 1 |
| 2 | 2 | | 16 | 1 |
| 2 | 2 | | 17 | 1 |
| 2 | 2 | | 18 | 1 |
| 2 | 2 | | 19 | 1 |
| 2 | 2 | | 20 | 1 |
| 2 | 2 | | 21 | 1 |
| 2 | 2 | | 22 | 1 |
| 2 | 2 | | 23 | 1 |
| 2 | 2 | | 24 | 1 |
| 2 | 2 | | 25 | 1 |
| 2 | 2 | | 26 | 1 |
| 2 | 2 | | 27 | 1 |
| 2 | 2 | | 28 | 1 |
| 2 | 2 | | 29 | 1 |
| 2 | 2 | | 30 | 1 |
| 2 | 2 | | 31 | 1 |
| 2 | 2 | | 32 | 1 |
| 2 | 2 | | 33 | 1 |
| 2 | 2 | | 34 | 1 |
| 2 | 2 | | 35 | 1 |
| 2 | 2 | | 36 | 1 |
| 2 | 2 | | 37 | 1 |
| 2 | 2 | | 38 | 1 |
| 2 | 2 | | 39 | 1 |
| 2 | 2 | | 40 | 1 |
| 2 | 2 | | | 40 |
| (172 rows) |
| |
| -- |
| -- Compare results between plans using sorting and plans using hash |
| -- aggregation. Force spilling in both cases by setting work_mem low |
| -- and altering the statistics. |
| -- |
| create table gs_data_1 as |
| select g%1000 as g1000, g%100 as g100, g%10 as g10, g |
| from generate_series(0,1999) g; |
| analyze gs_data_1; |
| alter table gs_data_1 set (autovacuum_enabled = 'false'); |
| WARNING: autovacuum is not supported in Cloudberry |
| update pg_class set reltuples = 10 where relname='gs_data_1'; |
| ERROR: permission denied: "pg_class" is a system catalog |
| set work_mem='64kB'; |
| -- Produce results with sorting. |
| set enable_sort = true; |
| set enable_hashagg = false; |
| set jit_above_cost = 0; |
| explain (costs off) |
| select g1000, g100, g10, sum(g::numeric), count(*), max(g::text) from |
| (select g%1000 as g1000, g%100 as g100, g%10 as g10, g |
| from generate_series(0,199999) g) s |
| group by cube (g1000,g100,g10); |
| QUERY PLAN |
| --------------------------------------------------------------- |
| GroupAggregate |
| Group Key: ((g.g % 1000)), ((g.g % 100)), ((g.g % 10)) |
| Group Key: ((g.g % 1000)), ((g.g % 100)) |
| Group Key: ((g.g % 1000)) |
| Group Key: () |
| Sort Key: ((g.g % 100)), ((g.g % 10)) |
| Group Key: ((g.g % 100)), ((g.g % 10)) |
| Group Key: ((g.g % 100)) |
| Sort Key: ((g.g % 10)), ((g.g % 1000)) |
| Group Key: ((g.g % 10)), ((g.g % 1000)) |
| Group Key: ((g.g % 10)) |
| -> Sort |
| Sort Key: ((g.g % 1000)), ((g.g % 100)), ((g.g % 10)) |
| -> Function Scan on generate_series g |
| Optimizer: Postgres query optimizer |
| (15 rows) |
| |
| create table gs_group_1 as |
| select g1000, g100, g10, sum(g::numeric), count(*), max(g::text) from |
| (select g%1000 as g1000, g%100 as g100, g%10 as g10, g |
| from generate_series(0,199999) g) s |
| group by cube (g1000,g100,g10) distributed by (g1000); |
| -- Produce results with hash aggregation. |
| set enable_hashagg = true; |
| set enable_sort = false; |
| explain (costs off) |
| select g1000, g100, g10, sum(g::numeric), count(*), max(g::text) from |
| (select g%1000 as g1000, g%100 as g100, g%10 as g10, g |
| from generate_series(0,199999) g) s |
| group by cube (g1000,g100,g10); |
| QUERY PLAN |
| --------------------------------------------------------------- |
| GroupAggregate |
| Group Key: ((g.g % 1000)), ((g.g % 100)), ((g.g % 10)) |
| Group Key: ((g.g % 1000)), ((g.g % 100)) |
| Group Key: ((g.g % 1000)) |
| Group Key: () |
| Sort Key: ((g.g % 100)), ((g.g % 10)) |
| Group Key: ((g.g % 100)), ((g.g % 10)) |
| Group Key: ((g.g % 100)) |
| Sort Key: ((g.g % 10)), ((g.g % 1000)) |
| Group Key: ((g.g % 10)), ((g.g % 1000)) |
| Group Key: ((g.g % 10)) |
| -> Sort |
| Sort Key: ((g.g % 1000)), ((g.g % 100)), ((g.g % 10)) |
| -> Function Scan on generate_series g |
| Optimizer: Postgres query optimizer |
| (15 rows) |
| |
| create table gs_hash_1 as |
| select g1000, g100, g10, sum(g::numeric), count(*), max(g::text) from |
| (select g%1000 as g1000, g%100 as g100, g%10 as g10, g |
| from generate_series(0,199999) g) s |
| group by cube (g1000,g100,g10) distributed by (g1000); |
| set jit_above_cost to default; |
| set enable_sort = true; |
| set work_mem to default; |
| -- Compare results of ORCA plan that relies on "IS NOT DISTINCT FROM" HASH Join |
| (select * from gs_hash_1 except select * from gs_group_1) |
| union all |
| (select * from gs_group_1 except select * from gs_hash_1); |
| g1000 | g100 | g10 | sum | count | max |
| -------+------+-----+-----+-------+----- |
| (0 rows) |
| |
| RESET optimizer; |
| drop table gs_group_1; |
| drop table gs_hash_1; |
| -- GROUP BY DISTINCT |
| -- "normal" behavior... |
| select a, b, c |
| from (values (1, 2, 3), (4, null, 6), (7, 8, 9)) as t (a, b, c) |
| group by all rollup(a, b), rollup(a, c) |
| order by a, b, c; |
| a | b | c |
| ---+---+--- |
| 1 | 2 | 3 |
| 1 | 2 | |
| 1 | 2 | |
| 1 | | 3 |
| 1 | | 3 |
| 1 | | |
| 1 | | |
| 1 | | |
| 4 | | 6 |
| 4 | | 6 |
| 4 | | 6 |
| 4 | | |
| 4 | | |
| 4 | | |
| 4 | | |
| 4 | | |
| 7 | 8 | 9 |
| 7 | 8 | |
| 7 | 8 | |
| 7 | | 9 |
| 7 | | 9 |
| 7 | | |
| 7 | | |
| 7 | | |
| | | |
| (25 rows) |
| |
| -- ...which is also the default |
| select a, b, c |
| from (values (1, 2, 3), (4, null, 6), (7, 8, 9)) as t (a, b, c) |
| group by rollup(a, b), rollup(a, c) |
| order by a, b, c; |
| a | b | c |
| ---+---+--- |
| 1 | 2 | 3 |
| 1 | 2 | |
| 1 | 2 | |
| 1 | | 3 |
| 1 | | 3 |
| 1 | | |
| 1 | | |
| 1 | | |
| 4 | | 6 |
| 4 | | 6 |
| 4 | | 6 |
| 4 | | |
| 4 | | |
| 4 | | |
| 4 | | |
| 4 | | |
| 7 | 8 | 9 |
| 7 | 8 | |
| 7 | 8 | |
| 7 | | 9 |
| 7 | | 9 |
| 7 | | |
| 7 | | |
| 7 | | |
| | | |
| (25 rows) |
| |
| -- "group by distinct" behavior... |
| select a, b, c |
| from (values (1, 2, 3), (4, null, 6), (7, 8, 9)) as t (a, b, c) |
| group by distinct rollup(a, b), rollup(a, c) |
| order by a, b, c; |
| a | b | c |
| ---+---+--- |
| 1 | 2 | 3 |
| 1 | 2 | |
| 1 | | 3 |
| 1 | | |
| 4 | | 6 |
| 4 | | 6 |
| 4 | | |
| 4 | | |
| 7 | 8 | 9 |
| 7 | 8 | |
| 7 | | 9 |
| 7 | | |
| | | |
| (13 rows) |
| |
| -- ...which is not the same as "select distinct" |
| select distinct a, b, c |
| from (values (1, 2, 3), (4, null, 6), (7, 8, 9)) as t (a, b, c) |
| group by rollup(a, b), rollup(a, c) |
| order by a, b, c; |
| a | b | c |
| ---+---+--- |
| 1 | 2 | 3 |
| 1 | 2 | |
| 1 | | 3 |
| 1 | | |
| 4 | | 6 |
| 4 | | |
| 7 | 8 | 9 |
| 7 | 8 | |
| 7 | | 9 |
| 7 | | |
| | | |
| (11 rows) |
| |
| -- test handling of outer GroupingFunc within subqueries |
| explain (costs off) |
| select (select grouping(v1)) from (values ((select 1))) v(v1) group by cube(v1); |
| QUERY PLAN |
| ------------------------------------- |
| MixedAggregate |
| Hash Key: $2 |
| Group Key: () |
| InitPlan 3 (returns $2) |
| -> Result |
| -> Result |
| SubPlan 2 |
| -> Result |
| Optimizer: Postgres query optimizer |
| (9 rows) |
| |
| select (select grouping(v1)) from (values ((select 1))) v(v1) group by cube(v1); |
| grouping |
| ---------- |
| 1 |
| 0 |
| (2 rows) |
| |
| explain (costs off) |
| select (select grouping(v1)) from (values ((select 1))) v(v1) group by v1; |
| QUERY PLAN |
| --------------------------- |
| GroupAggregate |
| Group Key: $2 |
| InitPlan 1 (returns $1) |
| -> Result |
| InitPlan 3 (returns $2) |
| -> Result |
| -> Result |
| SubPlan 2 |
| -> Result |
| (9 rows) |
| |
| select (select grouping(v1)) from (values ((select 1))) v(v1) group by v1; |
| grouping |
| ---------- |
| 0 |
| (1 row) |
| |
| select a, rank(a+3) within group (order by b nulls last) |
| from (values (1,1),(1,4),(1,5),(3,1),(3,2)) v(a,b) |
| group by rollup (a) order by a; |
| a | rank |
| ---+------ |
| 1 | 2 |
| 3 | 3 |
| | 6 |
| (3 rows) |
| |
| select a, rank((select a+3)) within group (order by b nulls last) |
| from (values (1,1),(1,4),(1,5),(3,1),(3,2)) v(a,b) |
| group by rollup (a) order by a; |
| a | rank |
| ---+------ |
| 1 | 2 |
| 3 | 3 |
| | 6 |
| (3 rows) |
| |
| select a, rank((select 1+2)) within group (order by b nulls last) |
| from (values (1,1),(1,4),(1,5),(3,1),(3,2)) v(a,b) |
| group by rollup (a) order by a; |
| a | rank |
| ---+------ |
| 1 | 2 |
| 3 | 3 |
| | 4 |
| (3 rows) |
| |
| select a, b, rank(b) within group (order by b nulls last) |
| from (values (1,1),(1,4),(1,5),(3,1),(3,2)) v(a,b) |
| group by rollup (a,b) order by a; |
| a | b | rank |
| ---+---+------ |
| 1 | 1 | 1 |
| 1 | 4 | 1 |
| 1 | 5 | 1 |
| 1 | | 4 |
| 3 | 1 | 1 |
| 3 | 2 | 1 |
| 3 | | 3 |
| | | 6 |
| (8 rows) |
| |
| -- end |
