| -- |
| -- 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'; |
| create function unhashable_bit_in (cstring) returns unhashable_bit immutable |
| language internal as 'bit_in'; |
| 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; |
| 1 1 1 1 1 1 1 1 |
| 1 1 1 1 1 1 1 2 |
| 1 1 1 1 1 1 2 2 |
| 1 1 1 1 1 2 2 2 |
| 1 1 1 1 2 2 2 2 |
| 1 1 1 2 2 2 2 2 |
| 1 1 2 2 2 2 2 2 |
| 1 2 2 2 2 2 2 2 |
| 2 2 2 2 2 2 2 2 |
| \. |
| ANALYZE gstest2; |
| |
| create temp table gstest3 (a integer, b integer, c integer, d integer); |
| copy gstest3 from stdin; |
| 1 1 1 1 |
| 2 2 2 2 |
| \. |
| 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); |
| select a, b, grouping(a,b), sum(v), count(*), max(v) |
| from gstest1 group by rollup (a,b) order by a,b; |
| select a, b, grouping(a,b), sum(v), count(*), max(v) |
| from gstest1 group by rollup (a,b) order by b desc, a; |
| 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); |
| |
| -- 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; |
| |
| -- 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; |
| |
| -- 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; |
| |
| -- nesting with grouping sets |
| select sum(c) from gstest2 |
| group by grouping sets((), grouping sets((), grouping sets(()))) |
| order by 1 desc; |
| select sum(c) from gstest2 |
| group by grouping sets((), grouping sets((), grouping sets(((a, b))))) |
| order by 1 desc; |
| select sum(c) from gstest2 |
| group by grouping sets(grouping sets(rollup(c), grouping sets(cube(c)))) |
| order by 1 desc; |
| select sum(c) from gstest2 |
| group by grouping sets(a, grouping sets(a, cube(b))) |
| order by 1 desc; |
| select sum(c) from gstest2 |
| group by grouping sets(grouping sets((a, (b)))) |
| order by 1 desc; |
| select sum(c) from gstest2 |
| group by grouping sets(grouping sets((a, b))) |
| order by 1 desc; |
| select sum(c) from gstest2 |
| group by grouping sets(grouping sets(a, grouping sets(a), a)) |
| order by 1 desc; |
| 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; |
| select sum(c) from gstest2 |
| group by grouping sets((a,(a,b)), grouping sets((a,(a,b)),a)) |
| order by 1 desc; |
| |
| -- 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); |
| select a, b, sum(v), count(*) from gstest_empty group by grouping sets ((a,b),()); |
| select a, b, sum(v), count(*) from gstest_empty group by grouping sets ((a,b),(),(),()); |
| select sum(v), count(*) from gstest_empty group by grouping sets ((),(),()); |
| |
| -- 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),()); |
| |
| -- 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), ()); |
| |
| 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), ()); |
| |
| 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), ()); |
| |
| -- 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)); |
| |
| -- 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); |
| |
| select g as alias1, g as alias2 |
| from generate_series(1,3) g |
| group by alias1, rollup(alias2); |
| |
| -- 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'; |
| |
| 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; |
| |
| 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); |
| |
| 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; |
| |
| -- 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; |
| |
| 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; |
| |
| -- 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; |
| |
| select grouping(ss.x) |
| from int8_tbl i1 |
| cross join lateral (select (select i1.q1) as x) ss |
| group by ss.x; |
| |
| 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; |
| |
| select (select grouping(ss.x)) |
| from int8_tbl i1 |
| cross join lateral (select (select i1.q1) as x) ss |
| group by ss.x; |
| |
| -- simple rescan tests |
| |
| select a, b, sum(v.x) |
| from (values (1),(2)) v(x), gstest_data(v.x) |
| group by rollup (a,b); |
| |
| 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; |
| |
| -- min max optimization should still work with GROUP BY () |
| explain (costs off) |
| select min(unique1) from tenk1 GROUP BY (); |
| |
| -- 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)); |
| |
| select pg_get_viewdef('gstest_view'::regclass, true); |
| |
| -- 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); |
| 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); |
| 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); |
| |
| -- Combinations of operations |
| select a, b, c, d from gstest2 group by rollup(a,b),grouping sets(c,d); |
| select a, b from (values (1,2),(2,3)) v(a,b) group by a,b, grouping sets(a); |
| |
| -- 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; |
| 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)); |
| 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); |
| 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; |
| 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); |
| 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; |
| |
| -- Test reordering of grouping sets |
| explain (costs off) |
| select * from gstest1 group by grouping sets((a,b,v),(v)) order by v,b,a; |
| |
| -- Agg level check. This query should error out. |
| select (select grouping(a,b) from gstest2) from gstest2 group by a,b; |
| |
| --Nested queries |
| select a, b, sum(c), count(*) from gstest2 group by grouping sets (rollup(a,b),a); |
| |
| -- 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); |
| |
| -- Tests around pushdown of HAVING clauses, partially testing against previous bugs |
| select a,count(*) from gstest2 group by rollup(a) order by a; |
| select a,count(*) from gstest2 group by rollup(a) having a is distinct from 1 order by a; |
| explain (costs off) |
| select a,count(*) from gstest2 group by rollup(a) having a is distinct from 1 order by a; |
| |
| select v.c, (select count(*) from gstest2 group by () having v.c) |
| from (values (false),(true)) v(c) order by v.c; |
| 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; |
| |
| -- HAVING with GROUPING queries |
| select ten, grouping(ten) from onek |
| group by grouping sets(ten) having grouping(ten) >= 0 |
| order by 2,1; |
| select ten, grouping(ten) from onek |
| group by grouping sets(ten, four) having grouping(ten) > 0 |
| order by 2,1; |
| select ten, grouping(ten) from onek |
| group by rollup(ten) having grouping(ten) > 0 |
| order by 2,1; |
| select ten, grouping(ten) from onek |
| group by cube(ten) having grouping(ten) > 0 |
| order by 2,1; |
| select ten, grouping(ten) from onek |
| group by (ten) having grouping(ten) >= 0 |
| order by 2,1; |
| |
| -- FILTER queries |
| select ten, sum(distinct four) filter (where four::text ~ '123') from onek a |
| group by rollup(ten); |
| |
| -- 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; |
| 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); |
| |
| -- Grouping on text columns |
| select sum(ten) from onek group by two, rollup(four::text) order by 1; |
| select sum(ten) from onek group by rollup(four::text), two order by 1; |
| |
| -- hashing support |
| |
| set enable_hashagg = true; |
| |
| -- failure cases |
| |
| select count(*) from gstest4 group by rollup(unhashable_col,unsortable_col); |
| select array_agg(v order by v) from gstest4 group by grouping sets ((id,unsortable_col),(id)); |
| |
| -- 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; |
| 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; |
| |
| select a, b, grouping(a,b), sum(v), count(*), max(v) |
| from gstest1 group by cube(a,b) order by 3,1,2; |
| 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; |
| |
| -- 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); |
| |
| -- unsortable cases |
| select unsortable_col, count(*) |
| from gstest4 group by grouping sets ((unsortable_col),(unsortable_col)) |
| order by unsortable_col::text; |
| |
| -- 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; |
| 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; |
| |
| 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; |
| 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; |
| |
| 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; |
| 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; |
| |
| 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; |
| 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; |
| |
| -- 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); |
| explain (costs off) |
| select a, b, sum(v), count(*) from gstest_empty group by grouping sets ((a,b),a); |
| select a, b, sum(v), count(*) from gstest_empty group by grouping sets ((a,b),()); |
| select a, b, sum(v), count(*) from gstest_empty group by grouping sets ((a,b),(),(),()); |
| explain (costs off) |
| select a, b, sum(v), count(*) from gstest_empty group by grouping sets ((a,b),(),(),()); |
| select sum(v), count(*) from gstest_empty group by grouping sets ((),(),()); |
| explain (costs off) |
| select sum(v), count(*) from gstest_empty group by grouping sets ((),(),()); |
| |
| -- 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)); |
| explain (costs off) |
| select a, d, grouping(a,b,c) |
| from gstest3 |
| group by grouping sets ((a,b), (a,c)); |
| |
| -- 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; |
| 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; |
| 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; |
| 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; |
| |
| -- 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; |
| 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; |
| 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; |
| 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; |
| 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; |
| 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; |
| |
| -- 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; |
| SELECT a, b, count(*), max(a), max(b) FROM gstest3 GROUP BY GROUPING SETS(a, b,()) ORDER BY a, b; |
| 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; |
| SELECT a, b, count(*), max(a), max(b) FROM gstest3 GROUP BY GROUPING SETS(a, b,()) ORDER BY a, b; |
| 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; |
| 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); |
| |
| -- 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; |
| 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); |
| |
| -- test the knapsack |
| |
| set enable_indexscan = false; |
| set hash_mem_multiplier = 1.0; |
| 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); |
| 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); |
| |
| 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); |
| |
| -- 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; |
| 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; |
| |
| -- Bug #16784 |
| create table bug_16784(i int, j int); |
| analyze bug_16784; |
| alter table bug_16784 set (autovacuum_enabled = 'false'); |
| update pg_class set reltuples = 10 where relname='bug_16784'; |
| |
| 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; |
| |
| -- |
| -- 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'); |
| update pg_class set reltuples = 10 where relname='gs_data_1'; |
| |
| 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); |
| |
| 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); |
| |
| 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; |
| set hash_mem_multiplier 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); |
| |
| 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; |
| |
| -- ...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; |
| |
| -- "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; |
| |
| -- ...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; |
| |
| -- test handling of outer GroupingFunc within subqueries |
| explain (costs off) |
| select (select grouping(v1)) from (values ((select 1))) v(v1) group by cube(v1); |
| select (select grouping(v1)) from (values ((select 1))) v(v1) group by cube(v1); |
| |
| explain (costs off) |
| select (select grouping(v1)) from (values ((select 1))) v(v1) group by v1; |
| select (select grouping(v1)) from (values ((select 1))) v(v1) group by v1; |
| |
| 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; |
| |
| 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; |
| |
| 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; |
| |
| 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; |
| |
| -- end |
