diff options
Diffstat (limited to 'src/test/regress/sql')
| -rw-r--r-- | src/test/regress/sql/groupingsets.sql | 155 | ||||
| -rw-r--r-- | src/test/regress/sql/tsrf.sql | 2 |
2 files changed, 156 insertions, 1 deletions
diff --git a/src/test/regress/sql/groupingsets.sql b/src/test/regress/sql/groupingsets.sql index cc557cc552d..0e023d9ddc5 100644 --- a/src/test/regress/sql/groupingsets.sql +++ b/src/test/regress/sql/groupingsets.sql @@ -31,6 +31,14 @@ copy gstest3 from stdin; \. alter table gstest3 add primary key (a); +create temp table gstest4(id integer, v integer, + unhashable_col 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 gstest_empty (a integer, b integer, v integer); create function gstest_data(v integer, out a integer, out b integer) @@ -43,8 +51,11 @@ create function gstest_data(v integer, out a integer, out b integer) -- 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) @@ -161,7 +172,7 @@ 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)); + 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 @@ -224,4 +235,146 @@ select array(select row(v.a,s1.*) from (select two,four, count(*) from onek grou 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); + +-- 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; + +-- 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); +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); + +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(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(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; + +-- 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 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); + -- end diff --git a/src/test/regress/sql/tsrf.sql b/src/test/regress/sql/tsrf.sql index e627bb99ed9..417e78c53dd 100644 --- a/src/test/regress/sql/tsrf.sql +++ b/src/test/regress/sql/tsrf.sql @@ -66,12 +66,14 @@ SELECT SUM(count(*)) OVER(PARTITION BY generate_series(1,3) ORDER BY generate_se SELECT few.dataa, count(*), min(id), max(id), generate_series(1,3) FROM few GROUP BY few.dataa ORDER BY 5, 1; -- grouping sets are a bit special, they produce NULLs in columns not actually NULL +set enable_hashagg = false; SELECT dataa, datab b, generate_series(1,2) g, count(*) FROM few GROUP BY CUBE(dataa, datab); SELECT dataa, datab b, generate_series(1,2) g, count(*) FROM few GROUP BY CUBE(dataa, datab) ORDER BY dataa; SELECT dataa, datab b, generate_series(1,2) g, count(*) FROM few GROUP BY CUBE(dataa, datab) ORDER BY g; SELECT dataa, datab b, generate_series(1,2) g, count(*) FROM few GROUP BY CUBE(dataa, datab, g); SELECT dataa, datab b, generate_series(1,2) g, count(*) FROM few GROUP BY CUBE(dataa, datab, g) ORDER BY dataa; SELECT dataa, datab b, generate_series(1,2) g, count(*) FROM few GROUP BY CUBE(dataa, datab, g) ORDER BY g; +reset enable_hashagg; -- data modification CREATE TABLE fewmore AS SELECT generate_series(1,3) AS data; |