--SET log_min_messages  TO DEBUG1;
--SET client_min_messages  TO DEBUG1;
--Testcase 16:
CREATE EXTENSION sqlite_fdw;
--Testcase 17:
CREATE SERVER sqlite_svr FOREIGN DATA WRAPPER sqlite_fdw
OPTIONS (database '/tmp/sqlitefdw_test.db');
--Testcase 18:
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 16:
CREATE FOREIGN TABLE limittest(id serial OPTIONS (key 'true'), x int, y text) SERVER sqlite_svr;
--Testcase 17:
INSERT INTO limittest(x, y) VALUES (1, 'x'), (2, 'x'), (3, 'x'), (4, 'x');
--Testcase 18:
INSERT INTO limittest(x, y) VALUES (1, 'y'), (2, 'y'), (3, 'y'), (4, 'y');
--Testcase 19:
INSERT INTO limittest(x, y) VALUES (1, 'z'), (2, 'z'), (3, 'z'), (4, 'z');
--Testcase 20:
EXPLAIN VERBOSE 
SELECT avg(x) FROM limittest GROUP BY y ORDER BY 1 DESC FETCH FIRST 2 ROWS WITH TIES;
                                                   QUERY PLAN                                                   
----------------------------------------------------------------------------------------------------------------
 Limit  (cost=1.00..1.00 rows=1 width=64)
   Output: (avg(x)), y
   ->  Foreign Scan  (cost=1.00..1.00 rows=1 width=64)
         Output: (avg(x)), y
         SQLite query: SELECT avg(`x`), `y` FROM main."limittest" GROUP BY 2 ORDER BY avg(`x`) DESC NULLS FIRST
(5 rows)

--Testcase 21:
SELECT avg(x) FROM limittest GROUP BY y ORDER BY 1 DESC FETCH FIRST 2 ROWS WITH TIES;
 avg 
-----
 2.5
 2.5
 2.5
(3 rows)

--Testcase 22:
EXPLAIN VERBOSE 
SELECT avg(x) FROM limittest WHERE  x >= 0 GROUP BY y ORDER BY 1 DESC FETCH FIRST 2 ROWS WITH TIES;
                                                            QUERY PLAN                                                             
-----------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=1.00..1.00 rows=1 width=64)
   Output: (avg(x)), y
   ->  Foreign Scan  (cost=1.00..1.00 rows=1 width=64)
         Output: (avg(x)), y
         SQLite query: SELECT avg(`x`), `y` FROM main."limittest" WHERE ((`x` >= 0)) GROUP BY 2 ORDER BY avg(`x`) DESC NULLS FIRST
(5 rows)

--Testcase 23:
SELECT avg(x) FROM limittest WHERE  x >= 0 GROUP BY y ORDER BY 1 DESC FETCH FIRST 2 ROWS WITH TIES;
 avg 
-----
 2.5
 2.5
 2.5
(3 rows)

--Testcase 24:
EXPLAIN VERBOSE 
SELECT x FROM limittest WHERE x > 0 ORDER BY 1 FETCH FIRST 2 ROWS WITH TIES;
                                              QUERY PLAN                                              
------------------------------------------------------------------------------------------------------
 Limit  (cost=10.00..10.00 rows=2 width=4)
   Output: x
   ->  Foreign Scan on public.limittest  (cost=10.00..10.00 rows=10 width=4)
         Output: x
         SQLite query: SELECT `x` FROM main."limittest" WHERE ((`x` > 0)) ORDER BY `x` ASC NULLS LAST
(5 rows)

--Testcase 25:
SELECT x FROM limittest WHERE x > 0 ORDER BY 1 FETCH FIRST 2 ROWS WITH TIES;
 x 
---
 1
 1
 1
(3 rows)

--Testcase 26:
EXPLAIN VERBOSE 
SELECT x FROM limittest ORDER BY 1 FETCH FIRST 2 ROWS ONLY;
                                      QUERY PLAN                                      
--------------------------------------------------------------------------------------
 Foreign Scan on public.limittest  (cost=1.00..1.00 rows=1 width=4)
   Output: x
   SQLite query: SELECT `x` FROM main."limittest" ORDER BY `x` ASC NULLS LAST LIMIT 2
(3 rows)

--Testcase 27:
SELECT x FROM limittest ORDER BY 1 FETCH FIRST 2 ROWS ONLY;
 x 
---
 1
 1
(2 rows)

--Testcase 28:
DROP FOREIGN TABLE limittest;
--Testcase 14:
DROP SERVER sqlite_svr;
--Testcase 15:
DROP EXTENSION sqlite_fdw CASCADE;