-- pg_orca aggfunc regression tests
-- Ported from Greenplum testrepo/query/aggfunc
LOAD 'pg_orca';
SET pg_orca.enable_orca = on;
SET client_min_messages = warning;
-- query01.sql
-- Test Aggregate Functions
-- Create testemp table
create table testemp(a integer, b integer);
-- insert values into table testemp
insert into testemp values(1,2);
-- returning count of the actor able
select count(*) from testemp;
 count 
-------
     1
(1 row)

-- drop table if exists testemp
drop table if exists testemp;
-- query02.sql
-- Test Aggregate Functions
-- Create product_agg table 
create table product_agg(sale integer, product integer);
-- inserting values into product_agg table
insert into product_agg values (10, 20);
insert into product_agg values (20, 25);
insert into product_agg values (30, 30);
insert into product_agg values (40, 35);
insert into product_agg values (45, 40);
-- returning minimum of product_agg table
select min(sale) from product_agg;
 min 
-----
  10
(1 row)

-- dropt product_agg table
drop table if exists product_agg;
-- query03.sql
-- Test Aggregate Functions
-- Create quantity table
create table quantity (qty integer, price integer);
-- inserting values into quantity table
insert into quantity values (100, 50);
insert into quantity values (200, 100);
insert into quantity values (300, 200);
insert into quantity values (400, 35);
insert into quantity values (500, 40);
-- returning maximum of quantity table
select max(price) from quantity;
 max 
-----
 200
(1 row)

-- drop quantity table
drop table if exists quantity;
-- query04.sql
-- Test Aggregate Functions
-- Create product table 
create table product (sale integer, prod integer);
-- inserting values into product table
insert into product values (10, 20);
insert into product values (20, 25);
insert into product values (30, 30);
insert into product values (40, 35);
insert into product values (45, 40);
-- returning minimum of product table
select min(sale*prod) from product;
 min 
-----
 200
(1 row)

-- drop product table
drop table if exists product;
-- query05.sql
-- Test Aggregate Functions
-- Create quantity table
create table quantity (qty integer, price integer, product varchar(10));
-- inserting values into quantity table
insert into quantity values (100, 50, 'p1');
insert into quantity values (200, 100, 'p2');
insert into quantity values (300, 200, 'p3');
insert into quantity values (400, 35, 'p4');
insert into quantity values (500, 40, 'p5');
-- returning maximum of quantity table
select max(qty*price) from quantity;
  max  
-------
 60000
(1 row)

-- drop quantity table
drop table if exists quantity;
-- query06.sql
-- Test Aggregate Functions
-- Create product table 
create table prod_agg (sale integer, prod varchar);
-- inserting values into prod_agg table
insert into prod_agg values (100, 'shirts');
insert into prod_agg values (200, 'pants');
insert into prod_agg values (300, 't-shirts');
insert into prod_agg values (400, 'caps');
insert into prod_agg values (450, 'hats');
-- Create cust_agg table
create table cust_agg (cusname varchar, sale integer, prod varchar);
-- inserting values into customer table
insert into cust_agg values ('aryan', 100, 'shirts');
insert into cust_agg values ('jay', 200, 'pants');
insert into cust_agg values ('mahi', 300, 't-shirts');
insert into cust_agg values ('nitu', 400, 'caps');
insert into cust_agg values ('verru', 450, 'hats');
-- returning customer name from cust_agg name with count of prod_add table 
select cusname,(select count(*) from prod_agg) from cust_agg;
 cusname | count 
---------+-------
 aryan   |     5
 jay     |     5
 mahi    |     5
 nitu    |     5
 verru   |     5
(5 rows)

-- drop prod_agg and cust_agg table
drop table if exists prod_agg;
drop table if exists cust_agg;
-- query07.sql
-- Test Aggregate Functions
-- create prod7 table 
create table prod7 (sale integer, prod varchar);
-- inserting values into prod7 table
insert into prod7 values (100, 'shirts');
insert into prod7 values (200, 'pants');
insert into prod7 values (300, 't-shirts');
insert into prod7 values (400, 'caps');
insert into prod7 values (450, 'hats');
-- create cust7 table
create table cust7 (cusname varchar, sale integer, prod varchar);
-- inserting values into cust7 table
insert into cust7 values ('jay', 100, 'shirts');
insert into cust7 values ('aryan', 200, 'pants');
insert into cust7 values ('mahi', 300, 't-shirts');
insert into cust7 values ('veeru', 400, 'caps');
insert into cust7 values ('jay', 450, 'hats');
-- returning customer name with count using order by customer name
\echo -- order 1
-- order 1
select cusname, (select count(*) from prod7) from cust7 order by cusname;
 cusname | count 
---------+-------
 aryan   |     5
 jay     |     5
 jay     |     5
 mahi    |     5
 veeru   |     5
(5 rows)

-- drop prod7 and cust7 table
drop table if exists prod7;
drop table if exists cust7;
-- query08.sql
-- Test Aggregate Functions
-- create prod8 table 
create table prod8 (sale integer, prodnm varchar,price integer);
-- inserting values into prod8 table
insert into prod8 values (100, 'shirts', 500);
insert into prod8 values (200, 'pants',800);
insert into prod8 values (300, 't-shirts', 300);
-- returning product and price using Havingand Group by clause
select prodnm, price, count(*) from prod8 GROUP BY prodnm, price;
  prodnm  | price | count 
----------+-------+-------
 shirts   |   500 |     1
 pants    |   800 |     1
 t-shirts |   300 |     1
(3 rows)

-- drop prod8 table
drop table if exists prod8;
-- query09.sql
-- Test Aggregate Functions
-- create prod9 table 
create table prod9 (sale integer, prodnm varchar,price integer);
-- inserting values into prod9 table
insert into prod9 values (100, 'shirts', 500);
insert into prod9 values (200, 'pants',800);
insert into prod9 values (300, 't-shirts', 300);
-- returning product and price using Havingand Group by clause
select prodnm, price from prod9 GROUP BY prodnm, price HAVING price !=300;
 prodnm | price 
--------+-------
 shirts |   500
 pants  |   800
(2 rows)

-- drop prod9 table
drop table if exists prod9;
-- query10.sql
-- Test Aggregate Functions
-- create prod10 table 
create table prod10 (sale integer, prodnm varchar,price integer);
-- inserting values into prod10 table
insert into prod10 values (100, 'shirts', 500);
insert into prod10 values (200, 'pants',800);
insert into prod10 values (300, 't-shirts', 300);
-- Returning product and price using HAVING and GROUP BY clauses.
-- Note: we can get away with ORDER BY prodnm because for this data set 
-- all prodnm values are unique.
\echo -- order 1
-- order 1
select prodnm, price from prod10 GROUP BY prodnm, price HAVING price !=400 ORDER BY prodnm;
  prodnm  | price 
----------+-------
 pants    |   800
 shirts   |   500
 t-shirts |   300
(3 rows)

-- drop prod10 table
drop table if exists prod10;
-- query11.sql
--@skip Skipping the test since the fix for MPP-15959 was not ported(from old main) as it was causing 3% performance regression.
select x,y,count(*), grouping(x), grouping(y),grouping(x,y) from generate_series(1,1) x, generate_series(1,1) y group by cube(x,y);
 x | y | count | grouping | grouping | grouping 
---+---+-------+----------+----------+----------
   |   |     1 |        1 |        1 |        3
 1 | 1 |     1 |        0 |        0 |        0
 1 |   |     1 |        0 |        1 |        1
   | 1 |     1 |        1 |        0 |        2
(4 rows)

select x,y,count(*), grouping(x,y) from generate_series(1,1) x, generate_series(1,1) y group by grouping sets((x,y),(x),(y),());
 x | y | count | grouping 
---+---+-------+----------
   |   |     1 |        3
 1 | 1 |     1 |        0
 1 |   |     1 |        1
   | 1 |     1 |        2
(4 rows)

select x,y,count(*), grouping(x,y) from generate_series(1,2) x, generate_series(1,2) y group by cube(x,y);
 x | y | count | grouping 
---+---+-------+----------
   |   |     4 |        3
 2 | 2 |     1 |        0
 2 | 1 |     1 |        0
 1 | 2 |     1 |        0
 1 | 1 |     1 |        0
 1 |   |     2 |        1
 2 |   |     2 |        1
   | 1 |     2 |        2
   | 2 |     2 |        2
(9 rows)

-- query12.sql
--@skip Skipping the test since the fix for MPP-15959 was not ported(from old main) as it was causing 3% performance regression.
--start_ignore
drop table if exists test;
--end_ignore
create table test (i int, n numeric);
insert into test values (0, 0), (0, 1), (0,2);
select i,n,count(*), grouping(i), grouping(n), grouping(i,n) from test group by grouping sets((), (i,n)) having n is null;
 i | n | count | grouping | grouping | grouping 
---+---+-------+----------+----------+----------
   |   |     3 |        1 |        1 |        3
(1 row)

select x, y, count(*), grouping(x,y) from generate_series(1,1) x, generate_series(1,1) y group by grouping sets(x,y) having x is not null;
 x | y | count | grouping 
---+---+-------+----------
 1 |   |     1 |        1
(1 row)

-- setup.sql
drop table if exists testemp;
drop table if exists product_agg;
drop table if exists quantity ;
drop table if exists product ;
drop table if exists quantity ;
drop table if exists prod_agg ;
drop table if exists cust_agg ;
drop table if exists prod7 ;
drop table if exists cust7 ;
drop table if exists prod8 ;
drop table if exists prod9 ;
drop table if exists prod10 ;