--SET log_min_messages  TO DEBUG1;
--SET client_min_messages  TO DEBUG1;
CREATE EXTENSION sqlite_fdw;
CREATE SERVER sqlite_svr FOREIGN DATA WRAPPER sqlite_fdw
OPTIONS (database '/tmp/sqlitefdw_test.db');
CREATE FOREIGN TABLE multiprimary(a int, b int OPTIONS (key 'true'), c int OPTIONS(key 'true')) SERVER sqlite_svr;
-- test for aggregate pushdown
--Testcase 8:
DROP SERVER IF EXISTS sqlite_svr CASCADE;
NOTICE:  drop cascades to foreign table multiprimary
--Testcase 9:
DROP EXTENSION IF EXISTS sqlite_fdw CASCADE;
--Testcase 10:
CREATE EXTENSION sqlite_fdw;
--Testcase 11:
CREATE SERVER sqlite_svr FOREIGN DATA WRAPPER sqlite_fdw
OPTIONS (database '/tmp/sqlitefdw_test.db');
--Testcase 12:
CREATE FOREIGN TABLE multiprimary(a int, b int OPTIONS (key 'true'), c int OPTIONS(key 'true')) SERVER sqlite_svr;
--Testcase 1:
explain (costs off, verbose) select count(distinct a) from multiprimary;
                             QUERY PLAN                              
---------------------------------------------------------------------
 Foreign Scan
   Output: (count(DISTINCT a))
   SQLite query: SELECT count(DISTINCT `a`) FROM main."multiprimary"
(3 rows)

--Testcase 2:
explain (costs off, verbose) select sum(b),max(b), min(b), avg(b) from multiprimary;
                                       QUERY PLAN                                       
----------------------------------------------------------------------------------------
 Foreign Scan
   Output: (sum(b)), (max(b)), (min(b)), (avg(b))
   SQLite query: SELECT sum(`b`), max(`b`), min(`b`), avg(`b`) FROM main."multiprimary"
(3 rows)

--Testcase 3:
explain (costs off, verbose) select sum(b+5)+2 from multiprimary group by b/2 order by b/2;
                                                          QUERY PLAN                                                          
------------------------------------------------------------------------------------------------------------------------------
 Foreign Scan
   Output: ((sum((b + 5)) + 2)), ((b / 2))
   SQLite query: SELECT (sum((`b` + 5)) + 2), (`b` / 2) FROM main."multiprimary" GROUP BY 2 ORDER BY (`b` / 2) ASC NULLS LAST
(3 rows)

--Testcase 4:
explain (costs off, verbose) select sum(a) from multiprimary group by b having sum(a) > 0;
                                            QUERY PLAN                                            
--------------------------------------------------------------------------------------------------
 Foreign Scan
   Output: (sum(a)), b
   SQLite query: SELECT sum(`a`), `b` FROM main."multiprimary" GROUP BY 2 HAVING ((sum(`a`) > 0))
(3 rows)

--Testcase 5:
explain (costs off, verbose) select sum(a) from multiprimary group by b having avg(a^2) > 0 and sum(a) > 0;
                                                              QUERY PLAN                                                               
---------------------------------------------------------------------------------------------------------------------------------------
 GroupAggregate
   Output: sum(a), b
   Group Key: multiprimary.b
   Filter: ((avg(((multiprimary.a)::double precision ^ '2'::double precision)) > '0'::double precision) AND (sum(multiprimary.a) > 0))
   ->  Foreign Scan on public.multiprimary
         Output: a, b, c
         SQLite query: SELECT `a`, `b` FROM main."multiprimary" ORDER BY `b` ASC NULLS LAST
(7 rows)

-- stddev and variance are not pushed down
--Testcase 6:
explain (costs off, verbose) select stddev(a) from multiprimary;
                        QUERY PLAN                         
-----------------------------------------------------------
 Aggregate
   Output: stddev(a)
   ->  Foreign Scan on public.multiprimary
         Output: a, b, c
         SQLite query: SELECT `a` FROM main."multiprimary"
(5 rows)

--Testcase 7:
explain (costs off, verbose) select sum(a) from multiprimary group by b having variance(a) > 0;
                                         QUERY PLAN                                         
--------------------------------------------------------------------------------------------
 GroupAggregate
   Output: sum(a), b
   Group Key: multiprimary.b
   Filter: (variance(multiprimary.a) > '0'::numeric)
   ->  Foreign Scan on public.multiprimary
         Output: a, b, c
         SQLite query: SELECT `a`, `b` FROM main."multiprimary" ORDER BY `b` ASC NULLS LAST
(7 rows)

--Testcase 13:
DROP FOREIGN TABLE multiprimary;
--Testcase 14:
DROP SERVER sqlite_svr;
--Testcase 15:
DROP EXTENSION sqlite_fdw CASCADE;