diff options
Diffstat (limited to 'src/test')
| -rw-r--r-- | src/test/regress/expected/groupingsets.out | 138 | ||||
| -rw-r--r-- | src/test/regress/sql/groupingsets.sql | 51 |
2 files changed, 189 insertions, 0 deletions
diff --git a/src/test/regress/expected/groupingsets.out b/src/test/regress/expected/groupingsets.out index e1f06608104..c860eab1c60 100644 --- a/src/test/regress/expected/groupingsets.out +++ b/src/test/regress/expected/groupingsets.out @@ -2150,4 +2150,142 @@ select (select grouping(v1)) from (values ((select 1))) v(v1) group by v1; 0 (1 row) +-- test handling of subqueries in grouping sets +create temp table gstest5(id integer primary key, v integer); +insert into gstest5 select i, i from generate_series(1,5)i; +explain (verbose, costs off) +select grouping((select t1.v from gstest5 t2 where id = t1.id)), + (select t1.v from gstest5 t2 where id = t1.id) as s +from gstest5 t1 +group by grouping sets(v, s) +order by case when grouping((select t1.v from gstest5 t2 where id = t1.id)) = 0 + then (select t1.v from gstest5 t2 where id = t1.id) + else null end + nulls first; + QUERY PLAN +--------------------------------------------------------------------------------------------------------------------------------------------- + Sort + Output: (GROUPING((SubPlan 1))), ((SubPlan 3)), (CASE WHEN (GROUPING((SubPlan 2)) = 0) THEN ((SubPlan 3)) ELSE NULL::integer END), t1.v + Sort Key: (CASE WHEN (GROUPING((SubPlan 2)) = 0) THEN ((SubPlan 3)) ELSE NULL::integer END) NULLS FIRST + -> HashAggregate + Output: GROUPING((SubPlan 1)), ((SubPlan 3)), CASE WHEN (GROUPING((SubPlan 2)) = 0) THEN ((SubPlan 3)) ELSE NULL::integer END, t1.v + Hash Key: t1.v + Hash Key: (SubPlan 3) + -> Seq Scan on pg_temp.gstest5 t1 + Output: (SubPlan 3), t1.v, t1.id + SubPlan 3 + -> Bitmap Heap Scan on pg_temp.gstest5 t2 + Output: t1.v + Recheck Cond: (t2.id = t1.id) + -> Bitmap Index Scan on gstest5_pkey + Index Cond: (t2.id = t1.id) +(15 rows) + +select grouping((select t1.v from gstest5 t2 where id = t1.id)), + (select t1.v from gstest5 t2 where id = t1.id) as s +from gstest5 t1 +group by grouping sets(v, s) +order by case when grouping((select t1.v from gstest5 t2 where id = t1.id)) = 0 + then (select t1.v from gstest5 t2 where id = t1.id) + else null end + nulls first; + grouping | s +----------+--- + 1 | + 1 | + 1 | + 1 | + 1 | + 0 | 1 + 0 | 2 + 0 | 3 + 0 | 4 + 0 | 5 +(10 rows) + +explain (verbose, costs off) +select grouping((select t1.v from gstest5 t2 where id = t1.id)), + (select t1.v from gstest5 t2 where id = t1.id) as s, + case when grouping((select t1.v from gstest5 t2 where id = t1.id)) = 0 + then (select t1.v from gstest5 t2 where id = t1.id) + else null end as o +from gstest5 t1 +group by grouping sets(v, s) +order by o nulls first; + QUERY PLAN +--------------------------------------------------------------------------------------------------------------------------------------------- + Sort + Output: (GROUPING((SubPlan 1))), ((SubPlan 3)), (CASE WHEN (GROUPING((SubPlan 2)) = 0) THEN ((SubPlan 3)) ELSE NULL::integer END), t1.v + Sort Key: (CASE WHEN (GROUPING((SubPlan 2)) = 0) THEN ((SubPlan 3)) ELSE NULL::integer END) NULLS FIRST + -> HashAggregate + Output: GROUPING((SubPlan 1)), ((SubPlan 3)), CASE WHEN (GROUPING((SubPlan 2)) = 0) THEN ((SubPlan 3)) ELSE NULL::integer END, t1.v + Hash Key: t1.v + Hash Key: (SubPlan 3) + -> Seq Scan on pg_temp.gstest5 t1 + Output: (SubPlan 3), t1.v, t1.id + SubPlan 3 + -> Bitmap Heap Scan on pg_temp.gstest5 t2 + Output: t1.v + Recheck Cond: (t2.id = t1.id) + -> Bitmap Index Scan on gstest5_pkey + Index Cond: (t2.id = t1.id) +(15 rows) + +select grouping((select t1.v from gstest5 t2 where id = t1.id)), + (select t1.v from gstest5 t2 where id = t1.id) as s, + case when grouping((select t1.v from gstest5 t2 where id = t1.id)) = 0 + then (select t1.v from gstest5 t2 where id = t1.id) + else null end as o +from gstest5 t1 +group by grouping sets(v, s) +order by o nulls first; + grouping | s | o +----------+---+--- + 1 | | + 1 | | + 1 | | + 1 | | + 1 | | + 0 | 1 | 1 + 0 | 2 | 2 + 0 | 3 | 3 + 0 | 4 | 4 + 0 | 5 | 5 +(10 rows) + +-- test handling of expressions that should match lower target items +explain (costs off) +select a < b and b < 3 from (values (1, 2)) t(a, b) group by rollup(a < b and b < 3) having a < b and b < 3; + QUERY PLAN +----------------------------------- + MixedAggregate + Hash Key: ((1 < 2) AND (2 < 3)) + Group Key: () + Filter: (((1 < 2) AND (2 < 3))) + -> Result +(5 rows) + +select a < b and b < 3 from (values (1, 2)) t(a, b) group by rollup(a < b and b < 3) having a < b and b < 3; + ?column? +---------- + t +(1 row) + +explain (costs off) +select not a from (values(true)) t(a) group by rollup(not a) having not not a; + QUERY PLAN +------------------------------ + MixedAggregate + Hash Key: (NOT true) + Group Key: () + Filter: (NOT ((NOT true))) + -> Result +(5 rows) + +select not a from (values(true)) t(a) group by rollup(not a) having not not a; + ?column? +---------- + f +(1 row) + -- end diff --git a/src/test/regress/sql/groupingsets.sql b/src/test/regress/sql/groupingsets.sql index 90ba27257a9..add76ac4a3a 100644 --- a/src/test/regress/sql/groupingsets.sql +++ b/src/test/regress/sql/groupingsets.sql @@ -589,4 +589,55 @@ 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; +-- test handling of subqueries in grouping sets +create temp table gstest5(id integer primary key, v integer); +insert into gstest5 select i, i from generate_series(1,5)i; + +explain (verbose, costs off) +select grouping((select t1.v from gstest5 t2 where id = t1.id)), + (select t1.v from gstest5 t2 where id = t1.id) as s +from gstest5 t1 +group by grouping sets(v, s) +order by case when grouping((select t1.v from gstest5 t2 where id = t1.id)) = 0 + then (select t1.v from gstest5 t2 where id = t1.id) + else null end + nulls first; + +select grouping((select t1.v from gstest5 t2 where id = t1.id)), + (select t1.v from gstest5 t2 where id = t1.id) as s +from gstest5 t1 +group by grouping sets(v, s) +order by case when grouping((select t1.v from gstest5 t2 where id = t1.id)) = 0 + then (select t1.v from gstest5 t2 where id = t1.id) + else null end + nulls first; + +explain (verbose, costs off) +select grouping((select t1.v from gstest5 t2 where id = t1.id)), + (select t1.v from gstest5 t2 where id = t1.id) as s, + case when grouping((select t1.v from gstest5 t2 where id = t1.id)) = 0 + then (select t1.v from gstest5 t2 where id = t1.id) + else null end as o +from gstest5 t1 +group by grouping sets(v, s) +order by o nulls first; + +select grouping((select t1.v from gstest5 t2 where id = t1.id)), + (select t1.v from gstest5 t2 where id = t1.id) as s, + case when grouping((select t1.v from gstest5 t2 where id = t1.id)) = 0 + then (select t1.v from gstest5 t2 where id = t1.id) + else null end as o +from gstest5 t1 +group by grouping sets(v, s) +order by o nulls first; + +-- test handling of expressions that should match lower target items +explain (costs off) +select a < b and b < 3 from (values (1, 2)) t(a, b) group by rollup(a < b and b < 3) having a < b and b < 3; +select a < b and b < 3 from (values (1, 2)) t(a, b) group by rollup(a < b and b < 3) having a < b and b < 3; + +explain (costs off) +select not a from (values(true)) t(a) group by rollup(not a) having not not a; +select not a from (values(true)) t(a) group by rollup(not a) having not not a; + -- end |