/*--create database
create database test;
-- Running script for pgbanch tables against a scaling factor of 1,00000 / drop constraint from pgbench_tables
\! ./pgbench -i -s 100000 postgres > /tmp/my_logs 2>&1
\c postgres
create table t1 (i int, j int, k int);
create table t2 (i int, j int, k int);
create table t3 (i int, j int, k int);
insert into t1 values (generate_series(1,100)*random(), generate_series(5,900)*random(), generate_series(8,800)*random());
insert into t2 values (generate_series(4,100)*random(), generate_series(5,900)*random(), generate_series(2,1000)*random());
insert into t3 values (generate_series(1,100)*random(), generate_series(3,900)*random(), generate_series(1,900)*random());
\c test
--create objects -
create table tv(n int,n1 char(100));
insert into tv values (generate_series(1,1000000),'aaa');
create table tv1(n int,n1 char(100));
insert into tv values (generate_series(10000,1000000),'a');
insert into tv1 values (generate_series(10000,1000000),'a');
analyze tv;
analyze tv1;
CREATE TABLE part_tbl6 (c1 INTEGER, c2 INTEGER, c3 VARCHAR) PARTITION BY RANGE (c1);
CREATE TABLE part_tbl6_p1 PARTITION OF part_tbl6 default;
CREATE TABLE part_tbl6_p2 PARTITION OF part_tbl6 FOR VALUES FROM (0) TO (10000);
CREATE TABLE part_tbl6_p3 PARTITION OF part_tbl6 FOR VALUES FROM (10001) TO (50000);
insert into part_tbl6 values (generate_series(1,10000),generate_series(10001,30000));
*/
\c test
---TestCase 1- Subquery compared with aggregate functions against having clause
explain analyze SELECT n,n1 FROM tv
GROUP BY n,n1 HAVING max(n) = (select max(n) from tv where n=(select min(n) from tv)) ORDER BY n,n1;
---TestCase 2-Subquery compared with having clause
explain analyze SELECT n,n1 FROM tv
GROUP BY n,n1 HAVING n = (select max(n) from tv where n=(select min(n) from tv)) ORDER BY n,n1;
---Testcase 3 -Subquery with aggregate function in where / having clause with multiple conditions
explain analyze SELECT lower(n1), count(n) FROM tv
where 1=(select max(n) from tv) group by lower(n1) HAVING count(*) = (select max(n) from tv) and min(n) = (select max(n) from tv)
ORDER BY lower(n1);
---Testcase 4 - Subquery with aggregate functions using different operator in order by clause
explain analyze SELECT DISTINCT ON (n, n1) n , n1
FROM tv where n =(select min(n) from tv)
ORDER BY n using <, n1 using <, n1 using <;
---Testcase 5- Subquery with aggregagte functions used in order by
explain analyze SELECT count(*) FROM tv x, tv y
WHERE x.n = y.n
GROUP BY x.n ORDER BY (select min(n) from tv where n1=(select lower(n1) from tv limit 1));
--Testcase 6- Aggregate function in group by/ order by clause
explain verbose SELECT count(*) FROM tv x, tv y
WHERE x.n = y.n
GROUP BY (select min(n) from tv where n1 not in (select lower(n1) from tv)) ORDER BY (select min(n) from tv where n1=(select lower(n1) from tv));
--Testcase 7 - Aggregate function in where / group by/ order by clause
explain verbose SELECT count(*) FROM tv x, tv y
WHERE x.n = (select min(n) from tv)
GROUP BY (select min(n) from tv where n1 not in (select lower(n1) from tv)) ORDER BY (select min(n) from tv where n1=(select lower(n1) from tv));
---Testcase 8- Subquery having multiple comparision in where clause against aggregate functions also having union all
explain analyze select count(*) from tv where (n,n1)=(select max(n),lower(n1) from tv group by n1 limit 1) union all select 5;
explain analyze select count(*) from tv where (n,n1,1) in (select max(n),lower(n1),2 from tv group by n1) union all select min(n) from tv where n=(select max(n) from tv);
---Testcase 9 - Subquery in where clause with IN operator against aggregate functions in where condition in subquery
explain analyze SELECT '' AS six, n AS "Correlated Field", n1 AS "Second Field"
FROM tv
WHERE n IN
(SELECT n FROM tv WHERE CAST(n AS float) = (select min(n) from tv));
explain analyze select 5/(select max(n) from tv where n in (select min(n) from tv where n=(select avg(n) from tv)) and n1 in (select lower(n1) from tv));
---Testcase 10 - Inline subquery and aggregate function in where clause of inline suquery
Explain analyze select count(distinct n) from
(select n from tv a
where n IN (select distinct avg(n) from tv b where n=(select avg(9)))) ss;
explain analyze select * from (select max(n) from tv where n=(select max(n) from tv) and n is null) d;
---Testcase 11 - Subselect with multiple columns comparisions in where clause against subquery
Explain analyze select (select (select max(n) from tv where (n,n,n,n,n,n,n,n,n,n,n,n,n,n,n,n,n,n,n,n,n,n)=(select 1,2,3,4,5,6,7,8,9,1,2,3,3,4,5,6,7,8,9,1,1,1)));
---Testcase 12 - Multiple conditions in where clause
explain analyze select max(n) from tv where (n=(select max(n) from tv) and n=(select min(n) from tv));
---Testcase 13 -Subquery used in Group by
explain analyze select count(*) from tv group by (select n from tv limit 1);
explain analyze select count(*) from tv group by (select n from tv where n not in (select max(n) from tv) limit 1);
---Testcase 14 - Subquery used in Order by
explain analyze Select count(*) from tv order by (select max(n) from tv where n=(select (select avg(n) from tv)));
explain analyze Select count(*) from tv order by (select max(n) from tv where (n,n)<>(select sum(1),avg(1)));
explain analyze Select count(*) from tv order by (select max(n) from tv where (n,n,1)<>(select sum(1),avg(1),count(*)));
--Testcase 15- CTE query ,having aggregate functions in where clause
explain analyze with x as (select count(*) from tv where n=(select max(n) from tv order by 1)) select * from tv x1 where x1.n=(select max(n) from tv) order by 1;
\c postgres
SET min_parallel_table_scan_size = 0;
SET min_parallel_index_scan_size = 0;
set parallel_tuple_cost = 0;
set parallel_setup_cost = 0;
---Testcase 16- Multiple table comparison with subquery in where clause
explain analyze verbose select * from t1 where t1.k = (select max(t3.k) from t3 where t3.j = (select avg(j) from t2));
explain analyze verbose select * from t1 where t1.k = (select max(t3.k) from t3 );
explain analyze verbose select * from t1 where (k,k)= (select max(k),min(k) from t3);
\c test
--Testcase 17-
explain verbose select * from (select * from pgbench_accounts) as t where aid= (select max(aid) from pgbench_accounts);
explain verbose select * from (select * from pgbench_accounts where aid=(select min(bid) from pgbench_accounts)) as t where bid = (select max(aid) from pgbench_accounts);
--Testcase 18- aggregate function in where condition of subquery as well as inline query
explain analyze verbose select * from (select count(*) n from pgbench_accounts) as t where n = (select max(aid) from pgbench_accounts where aid=(select count(*) from pgbench_tellers));
explain analyze verbose select * from (select count(*) n from pgbench_accounts) as t where n = (select max(aid) from pgbench_accounts where aid<>(select count(*) from pgbench_tellers));
--Testcase 19 - multiple columns comparisions in where clause of subquery
explain analyze verbose select count(*) from tv where n=(select count(n1) from tv1 where (n,n) not in (select avg(n),count(n) from tv where n1=(select 'a')));
explain analyze verbose select count(*) from tv where n!=(select count(n1) from tv1 where (n,n) not in (select avg(n),count(n) from tv where n1=(select 'a')));
--Testcase 20 - aggregate functions used with different joins conditions
explain analyze verbose select count(*) from tv f right outer join tv1 g on f.n not in (select avg(g.n) from tv g ,tv1 f where g.n=(select max(f.n) from tv1 f)) where f.n in (1,2,3) ;
explain verbose select count(*) from tv f right outer join tv1 g on f.n != (select avg(g.n) from tv g ,tv1 f where (g.n,g.n,g.n)!=Any(select max(f.n),min(f.n),count(*) from tv1 f)) where f.n in (1,2,3) limit 5;
explain analyze verbose select count(*) from tv f right outer join tv1 g on (f.n,f.n,f.n)=(select max(f.n),min(f.n),count(*) from tv1 f);
explain verbose select count(*) from tv f left outer join tv1 g on (f.n,f.n,f.n)=(select max(f.n),min(f.n),count(*) from tv1 f);
explain verbose select count(*) from tv f left outer join tv1 g on (f.n,f.n,f.n)=(select max(f.n),min(f.n),count(*) from tv1 f where f.n1=(select string_agg('ss','sss')));
explain verbose select sum(a.aid),min(b.n),count(c.n1) from pgbench_accounts a ,tv b,tv1 c where b.n=c.n and a.aid=(select max(bid) from pgbench_tellers);
--Testcase 21 - aggregate in aggregate function used in subquery of where clause
explain select max(n) from tv a where a.n =(select trunc(avg(123.33)) from tv1,tv);
--Testcase 22 - aggreagte functions used against partition table .
SET min_parallel_table_scan_size = 0;
SET min_parallel_index_scan_size = 0;
set parallel_tuple_cost = 0;
set parallel_setup_cost = 0;
explain analyze select * from part_tbl6 where c1=(select max(c1) from part_tbl6_p3) and c1=(select max(c1) from part_tbl6_p3) and c1=(select max(c1) from part_tbl6_p3) or c1=(select max(c1) from part_tbl6_p3) and c1=(select max(c1) from part_tbl6_p3) and c1=(select max(c1) from part_tbl6_p3)or c1=(select max(c1) from part_tbl6_p3)and c1=(select max(c1) from part_tbl6_p3)or c1=(select max(c1) from part_tbl6_p3) and c1=(select max(c1) from part_tbl6_p3) or c1=(select max(c1) from part_tbl6_p3) or c1=(select max(c1) from part_tbl6) and c1=(select max(c1) from part_tbl6) ;