Support GROUPING SETS, CUBE and ROLLUP.

[postgresql] / src / test / regress / expected / groupingsets.out

--
-- grouping sets
--
-- 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;
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 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
-- 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)

-- 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)

-- 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 optimisation should still work with GROUP BY ()
explain (costs off)
  select min(unique1) from tenk1 GROUP BY ();
                         QUERY PLAN                         
------------------------------------------------------------
 Result
   InitPlan 1 (returns $0)
     ->  Limit
           ->  Index Only Scan using tenk1_unique1 on tenk1
                 Index Cond: (unique1 IS NOT NULL)
(5 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));
 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 |  25 |     2 |  13
   |   |        3 |  14 |     1 |  14
   |   |        3 |  15 |     1 |  15
   |   |        3 |  16 |     1 |  16
   |   |        3 |  17 |     1 |  17
   |   |        3 |  37 |     2 |  19
   |   |        3 |  21 |     2 |  11
   |   |        3 |  25 |     2 |  13
   |   |        3 |  14 |     1 |  14
   |   |        3 |  15 |     1 |  15
   |   |        3 |  16 |     1 |  16
   |   |        3 |  17 |     1 |  17
   |   |        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)

-- 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)

-- 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)

-- end