--
-- LIMIT
-- Check the LIMIT/OFFSET feature of SELECT
--
--Testcase 27:
CREATE EXTENSION sqlite_fdw;
--Testcase 28:
CREATE SERVER sqlite_svr FOREIGN DATA WRAPPER sqlite_fdw
OPTIONS (database '/tmp/sqlitefdw_test_core.db');
--Testcase 29:
CREATE FOREIGN TABLE onek(
	unique1		int4 OPTIONS (key 'true'),
	unique2		int4,
	two 		int4,
	four		int4,
	ten 		int4,
	twenty		int4,
	hundred		int4,
	thousand	int4,
	twothousand	int4,
	fivethous	int4,
	tenthous	int4,
	odd     	int4,
	even		int4,
	stringu1	name,
	stringu2	name,
	string4		name
) SERVER sqlite_svr;
--Testcase 30:
CREATE FOREIGN TABLE int8_tbl(q1 int8 OPTIONS (key 'true'), q2 int8) SERVER sqlite_svr;
--Testcase 31:
CREATE FOREIGN TABLE INT8_TMP(
        q1 int8,
        q2 int8,
        q3 int4,
        q4 int2,
        q5 text,
        id int options (key 'true')
) SERVER sqlite_svr;
--Testcase 32:
CREATE FOREIGN TABLE tenk1 (
	unique1		int4 OPTIONS (key 'true'),
	unique2		int4,
	two 		int4,
	four		int4,
	ten 		int4,
	twenty		int4,
	hundred		int4,
	thousand	int4,
	twothousand	int4,
	fivethous	int4,
	tenthous	int4,
	odd     	int4,
	even		int4,
	stringu1	name,
	stringu2	name,
	string4		name
) SERVER sqlite_svr;
--Testcase 88:
CREATE TABLE parent_table (
	unique1		int4 PRIMARY KEY,
	unique2		int4,
	two 		int4,
	four		int4,
	ten 		int4,
	twenty		int4,
	hundred		int4,
	thousand	int4,
	twothousand	int4,
	fivethous	int4,
	tenthous	int4,
	odd			int4,
	even		int4,
	stringu1	name,
	stringu2	name,
	string4		name
);
--Testcase 89:
CREATE FOREIGN table inherited_table ()
INHERITS (parent_table)
SERVER sqlite_svr options (table 'tenk1');
--Testcase 1:
SELECT ''::text AS two, unique1, unique2, stringu1
		FROM onek WHERE unique1 > 50
		ORDER BY unique1 LIMIT 2;
 two | unique1 | unique2 | stringu1 
-----+---------+---------+----------
     |      51 |      76 | ZBAAAA
     |      52 |     985 | ACAAAA
(2 rows)

--Testcase 2:
SELECT ''::text AS five, unique1, unique2, stringu1
		FROM onek WHERE unique1 > 60
		ORDER BY unique1 LIMIT 5;
 five | unique1 | unique2 | stringu1 
------+---------+---------+----------
      |      61 |     560 | JCAAAA
      |      62 |     633 | KCAAAA
      |      63 |     296 | LCAAAA
      |      64 |     479 | MCAAAA
      |      65 |      64 | NCAAAA
(5 rows)

--Testcase 3:
SELECT ''::text AS two, unique1, unique2, stringu1
		FROM onek WHERE unique1 > 60 AND unique1 < 63
		ORDER BY unique1 LIMIT 5;
 two | unique1 | unique2 | stringu1 
-----+---------+---------+----------
     |      61 |     560 | JCAAAA
     |      62 |     633 | KCAAAA
(2 rows)

--Testcase 4:
SELECT ''::text AS three, unique1, unique2, stringu1
		FROM onek WHERE unique1 > 100
		ORDER BY unique1 LIMIT 3 OFFSET 20;
 three | unique1 | unique2 | stringu1 
-------+---------+---------+----------
       |     121 |     700 | REAAAA
       |     122 |     519 | SEAAAA
       |     123 |     777 | TEAAAA
(3 rows)

--Testcase 5:
SELECT ''::text AS zero, unique1, unique2, stringu1
		FROM onek WHERE unique1 < 50
		ORDER BY unique1 DESC LIMIT 8 OFFSET 99;
 zero | unique1 | unique2 | stringu1 
------+---------+---------+----------
(0 rows)

--Testcase 6:
SELECT ''::text AS eleven, unique1, unique2, stringu1
		FROM onek WHERE unique1 < 50
		ORDER BY unique1 DESC LIMIT 20 OFFSET 39;
 eleven | unique1 | unique2 | stringu1 
--------+---------+---------+----------
        |      10 |     520 | KAAAAA
        |       9 |      49 | JAAAAA
        |       8 |     653 | IAAAAA
        |       7 |     647 | HAAAAA
        |       6 |     978 | GAAAAA
        |       5 |     541 | FAAAAA
        |       4 |     833 | EAAAAA
        |       3 |     431 | DAAAAA
        |       2 |     326 | CAAAAA
        |       1 |     214 | BAAAAA
        |       0 |     998 | AAAAAA
(11 rows)

--Testcase 7:
SELECT ''::text AS ten, unique1, unique2, stringu1
		FROM onek
		ORDER BY unique1 OFFSET 990;
 ten | unique1 | unique2 | stringu1 
-----+---------+---------+----------
     |     990 |     369 | CMAAAA
     |     991 |     426 | DMAAAA
     |     992 |     363 | EMAAAA
     |     993 |     661 | FMAAAA
     |     994 |     695 | GMAAAA
     |     995 |     144 | HMAAAA
     |     996 |     258 | IMAAAA
     |     997 |      21 | JMAAAA
     |     998 |     549 | KMAAAA
     |     999 |     152 | LMAAAA
(10 rows)

--Testcase 8:
SELECT ''::text AS five, unique1, unique2, stringu1
		FROM onek
		ORDER BY unique1 OFFSET 990 LIMIT 5;
 five | unique1 | unique2 | stringu1 
------+---------+---------+----------
      |     990 |     369 | CMAAAA
      |     991 |     426 | DMAAAA
      |     992 |     363 | EMAAAA
      |     993 |     661 | FMAAAA
      |     994 |     695 | GMAAAA
(5 rows)

--Testcase 9:
SELECT ''::text AS five, unique1, unique2, stringu1
		FROM onek
		ORDER BY unique1 LIMIT 5 OFFSET 900;
 five | unique1 | unique2 | stringu1 
------+---------+---------+----------
      |     900 |     913 | QIAAAA
      |     901 |     931 | RIAAAA
      |     902 |     702 | SIAAAA
      |     903 |     641 | TIAAAA
      |     904 |     793 | UIAAAA
(5 rows)

-- Test null limit and offset.  The planner would discard a simple null
-- constant, so to ensure executor is exercised, do this:
--Testcase 10:
select * from int8_tbl limit (case when random() < 0.5 then null::bigint end);
        q1        |        q2         
------------------+-------------------
              123 |               456
              123 |  4567890123456789
 4567890123456789 |               123
 4567890123456789 |  4567890123456789
 4567890123456789 | -4567890123456789
(5 rows)

--Testcase 11:
select * from int8_tbl offset (case when random() < 0.5 then null::bigint end);
        q1        |        q2         
------------------+-------------------
              123 |               456
              123 |  4567890123456789
 4567890123456789 |               123
 4567890123456789 |  4567890123456789
 4567890123456789 | -4567890123456789
(5 rows)

-- Test assorted cases involving backwards fetch from a LIMIT plan node
begin;
declare c1 scroll cursor for select * from int8_tbl order by q1 limit 10;
--Testcase 12:
fetch all in c1;
        q1        |        q2         
------------------+-------------------
              123 |               456
              123 |  4567890123456789
 4567890123456789 | -4567890123456789
 4567890123456789 |               123
 4567890123456789 |  4567890123456789
(5 rows)

--Testcase 13:
fetch 1 in c1;
 q1 | q2 
----+----
(0 rows)

--Testcase 14:
fetch backward 1 in c1;
        q1        |        q2        
------------------+------------------
 4567890123456789 | 4567890123456789
(1 row)

--Testcase 33:
fetch backward all in c1;
        q1        |        q2         
------------------+-------------------
 4567890123456789 |               123
 4567890123456789 | -4567890123456789
              123 |  4567890123456789
              123 |               456
(4 rows)

--Testcase 34:
fetch backward 1 in c1;
 q1 | q2 
----+----
(0 rows)

--Testcase 35:
fetch all in c1;
        q1        |        q2         
------------------+-------------------
              123 |               456
              123 |  4567890123456789
 4567890123456789 | -4567890123456789
 4567890123456789 |               123
 4567890123456789 |  4567890123456789
(5 rows)

declare c2 scroll cursor for select * from int8_tbl limit 3;
--Testcase 36:
fetch all in c2;
        q1        |        q2        
------------------+------------------
              123 |              456
              123 | 4567890123456789
 4567890123456789 |              123
(3 rows)

--Testcase 37:
fetch 1 in c2;
 q1 | q2 
----+----
(0 rows)

--Testcase 38:
fetch backward 1 in c2;
        q1        | q2  
------------------+-----
 4567890123456789 | 123
(1 row)

--Testcase 39:
fetch backward all in c2;
 q1  |        q2        
-----+------------------
 123 | 4567890123456789
 123 |              456
(2 rows)

--Testcase 40:
fetch backward 1 in c2;
 q1 | q2 
----+----
(0 rows)

--Testcase 41:
fetch all in c2;
        q1        |        q2        
------------------+------------------
              123 |              456
              123 | 4567890123456789
 4567890123456789 |              123
(3 rows)

declare c3 scroll cursor for select * from int8_tbl offset 3;
--Testcase 42:
fetch all in c3;
        q1        |        q2         
------------------+-------------------
 4567890123456789 |  4567890123456789
 4567890123456789 | -4567890123456789
(2 rows)

--Testcase 43:
fetch 1 in c3;
 q1 | q2 
----+----
(0 rows)

--Testcase 44:
fetch backward 1 in c3;
        q1        |        q2         
------------------+-------------------
 4567890123456789 | -4567890123456789
(1 row)

--Testcase 45:
fetch backward all in c3;
        q1        |        q2        
------------------+------------------
 4567890123456789 | 4567890123456789
(1 row)

--Testcase 46:
fetch backward 1 in c3;
 q1 | q2 
----+----
(0 rows)

--Testcase 47:
fetch all in c3;
        q1        |        q2         
------------------+-------------------
 4567890123456789 |  4567890123456789
 4567890123456789 | -4567890123456789
(2 rows)

declare c4 scroll cursor for select * from int8_tbl offset 10;
--Testcase 48:
fetch all in c4;
 q1 | q2 
----+----
(0 rows)

--Testcase 49:
fetch 1 in c4;
 q1 | q2 
----+----
(0 rows)

--Testcase 50:
fetch backward 1 in c4;
 q1 | q2 
----+----
(0 rows)

--Testcase 51:
fetch backward all in c4;
 q1 | q2 
----+----
(0 rows)

--Testcase 52:
fetch backward 1 in c4;
 q1 | q2 
----+----
(0 rows)

--Testcase 53:
fetch all in c4;
 q1 | q2 
----+----
(0 rows)

declare c5 scroll cursor for select * from int8_tbl order by q1 fetch first 2 rows with ties;
--Testcase 54:
fetch all in c5;
 q1  |        q2        
-----+------------------
 123 |              456
 123 | 4567890123456789
(2 rows)

--Testcase 55:
fetch 1 in c5;
 q1 | q2 
----+----
(0 rows)

--Testcase 56:
fetch backward 1 in c5;
 q1  |        q2        
-----+------------------
 123 | 4567890123456789
(1 row)

--Testcase 57:
fetch backward 1 in c5;
 q1  | q2  
-----+-----
 123 | 456
(1 row)

--Testcase 58:
fetch all in c5;
 q1  |        q2        
-----+------------------
 123 | 4567890123456789
(1 row)

--Testcase 59:
fetch backward all in c5;
 q1  |        q2        
-----+------------------
 123 | 4567890123456789
 123 |              456
(2 rows)

--Testcase 60:
fetch all in c5;
 q1  |        q2        
-----+------------------
 123 |              456
 123 | 4567890123456789
(2 rows)

--Testcase 61:
fetch backward all in c5;
 q1  |        q2        
-----+------------------
 123 | 4567890123456789
 123 |              456
(2 rows)

rollback;
-- Stress test for variable LIMIT in conjunction with bounded-heap sorting
--Testcase 62:
DELETE FROM INT8_TMP;
--Testcase 63:
INSERT INTO INT8_TMP SELECT q1 FROM generate_series(1,10) q1;
--Testcase 64:
SELECT
  (SELECT s.q1 
     FROM (VALUES (1)) AS x,
          (SELECT q1 FROM INT8_TMP as n 
             ORDER BY q1 LIMIT 1 OFFSET s.q1-1) AS y) AS z
  FROM INT8_TMP AS s;
 z  
----
  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
(10 rows)

--
-- Test behavior of volatile and set-returning functions in conjunction
-- with ORDER BY and LIMIT.
--
--Testcase 65:
create temp sequence testseq;
--Testcase 15:
explain (verbose, costs off)
select unique1, unique2, nextval('testseq')
  from tenk1 order by unique2 limit 10;
                                                QUERY PLAN                                                
----------------------------------------------------------------------------------------------------------
 Foreign Scan on public.tenk1
   Output: unique1, unique2, nextval('testseq'::regclass)
   SQLite query: SELECT `unique1`, `unique2` FROM main."tenk1" ORDER BY `unique2` ASC NULLS LAST LIMIT 10
(3 rows)

--Testcase 16:
select unique1, unique2, nextval('testseq')
  from tenk1 order by unique2 limit 10;
 unique1 | unique2 | nextval 
---------+---------+---------
    8800 |       0 |       1
    1891 |       1 |       2
    3420 |       2 |       3
    9850 |       3 |       4
    7164 |       4 |       5
    8009 |       5 |       6
    5057 |       6 |       7
    6701 |       7 |       8
    4321 |       8 |       9
    3043 |       9 |      10
(10 rows)

--Testcase 90:
explain (verbose, costs off)
select unique1, unique2, nextval('testseq')
  from tenk1 order by unique2 limit 10 offset 5;
                                                    QUERY PLAN                                                     
-------------------------------------------------------------------------------------------------------------------
 Foreign Scan on public.tenk1
   Output: unique1, unique2, nextval('testseq'::regclass)
   SQLite query: SELECT `unique1`, `unique2` FROM main."tenk1" ORDER BY `unique2` ASC NULLS LAST LIMIT 10 OFFSET 5
(3 rows)

--Testcase 91:
select unique1, unique2, nextval('testseq')
  from tenk1 order by unique2 limit 10 offset 5;
 unique1 | unique2 | nextval 
---------+---------+---------
    8009 |       5 |      11
    5057 |       6 |      12
    6701 |       7 |      13
    4321 |       8 |      14
    3043 |       9 |      15
    1314 |      10 |      16
    1504 |      11 |      17
    5222 |      12 |      18
    6243 |      13 |      19
    5471 |      14 |      20
(10 rows)

--Testcase 17:
select currval('testseq');
 currval 
---------
      20
(1 row)

--Testcase 92:
explain (verbose, costs off)
select unique1, unique2, nextval('testseq')
  from tenk1 order by unique2 desc limit 10;
                                                 QUERY PLAN                                                 
------------------------------------------------------------------------------------------------------------
 Foreign Scan on public.tenk1
   Output: unique1, unique2, nextval('testseq'::regclass)
   SQLite query: SELECT `unique1`, `unique2` FROM main."tenk1" ORDER BY `unique2` DESC NULLS FIRST LIMIT 10
(3 rows)

--Testcase 93:
select unique1, unique2, nextval('testseq')
  from tenk1 order by unique2 desc limit 10;
 unique1 | unique2 | nextval 
---------+---------+---------
    2968 |    9999 |      21
       0 |    9998 |      22
    2992 |    9997 |      23
    1783 |    9996 |      24
    5780 |    9995 |      25
     429 |    9994 |      26
    6093 |    9993 |      27
    6587 |    9992 |      28
    4093 |    9991 |      29
    4773 |    9990 |      30
(10 rows)

--Testcase 94:
explain (verbose, costs off)
select unique1, unique2, nextval('testseq')
  from tenk1 order by unique2 desc limit 10 offset 5;
                                                     QUERY PLAN                                                      
---------------------------------------------------------------------------------------------------------------------
 Foreign Scan on public.tenk1
   Output: unique1, unique2, nextval('testseq'::regclass)
   SQLite query: SELECT `unique1`, `unique2` FROM main."tenk1" ORDER BY `unique2` DESC NULLS FIRST LIMIT 10 OFFSET 5
(3 rows)

--Testcase 95:
select unique1, unique2, nextval('testseq')
  from tenk1 order by unique2 desc limit 10 offset 5;
 unique1 | unique2 | nextval 
---------+---------+---------
     429 |    9994 |      31
    6093 |    9993 |      32
    6587 |    9992 |      33
    4093 |    9991 |      34
    4773 |    9990 |      35
    8592 |    9989 |      36
     858 |    9988 |      37
    9194 |    9987 |      38
    1384 |    9986 |      39
    2406 |    9985 |      40
(10 rows)

--Testcase 96:
select currval('testseq');
 currval 
---------
      40
(1 row)

--Testcase 18:
explain (verbose, costs off)
select unique1, unique2, nextval('testseq')
  from tenk1 order by tenthous limit 10;
                                                      QUERY PLAN                                                       
-----------------------------------------------------------------------------------------------------------------------
 Foreign Scan on public.tenk1
   Output: unique1, unique2, nextval('testseq'::regclass), tenthous
   SQLite query: SELECT `unique1`, `unique2`, `tenthous` FROM main."tenk1" ORDER BY `tenthous` ASC NULLS LAST LIMIT 10
(3 rows)

--Testcase 19:
select unique1, unique2, nextval('testseq')
  from tenk1 order by tenthous limit 10;
 unique1 | unique2 | nextval 
---------+---------+---------
       0 |    9998 |      41
       1 |    2838 |      42
       2 |    2716 |      43
       3 |    5679 |      44
       4 |    1621 |      45
       5 |    5557 |      46
       6 |    2855 |      47
       7 |    8518 |      48
       8 |    5435 |      49
       9 |    4463 |      50
(10 rows)

--Testcase 97:
explain (verbose, costs off)
select unique1, unique2, nextval('testseq')
  from tenk1 order by tenthous limit 10 offset 5;
                                                           QUERY PLAN                                                           
--------------------------------------------------------------------------------------------------------------------------------
 Foreign Scan on public.tenk1
   Output: unique1, unique2, nextval('testseq'::regclass), tenthous
   SQLite query: SELECT `unique1`, `unique2`, `tenthous` FROM main."tenk1" ORDER BY `tenthous` ASC NULLS LAST LIMIT 10 OFFSET 5
(3 rows)

--Testcase 98:
select unique1, unique2, nextval('testseq')
  from tenk1 order by tenthous limit 10 offset 5;
 unique1 | unique2 | nextval 
---------+---------+---------
       5 |    5557 |      51
       6 |    2855 |      52
       7 |    8518 |      53
       8 |    5435 |      54
       9 |    4463 |      55
      10 |    8788 |      56
      11 |    8396 |      57
      12 |    6605 |      58
      13 |    5696 |      59
      14 |    4341 |      60
(10 rows)

--Testcase 20:
select currval('testseq');
 currval 
---------
      60
(1 row)

-- test for limit and offset when querying table and foreign table inherited
--Testcase 99:
explain (verbose, costs off)
select unique1, unique2, nextval('testseq')
  from parent_table order by tenthous limit 10;
                                                               QUERY PLAN                                                                
-----------------------------------------------------------------------------------------------------------------------------------------
 Limit
   Output: parent_table.unique1, parent_table.unique2, (nextval('testseq'::regclass)), parent_table.tenthous
   ->  Result
         Output: parent_table.unique1, parent_table.unique2, nextval('testseq'::regclass), parent_table.tenthous
         ->  Merge Append
               Sort Key: parent_table.tenthous
               ->  Sort
                     Output: parent_table_1.unique1, parent_table_1.unique2, parent_table_1.tenthous
                     Sort Key: parent_table_1.tenthous
                     ->  Seq Scan on public.parent_table parent_table_1
                           Output: parent_table_1.unique1, parent_table_1.unique2, parent_table_1.tenthous
               ->  Foreign Scan on public.inherited_table parent_table_2
                     Output: parent_table_2.unique1, parent_table_2.unique2, parent_table_2.tenthous
                     SQLite query: SELECT `unique1`, `unique2`, `tenthous` FROM main."tenk1" ORDER BY `tenthous` ASC NULLS LAST LIMIT 10
(14 rows)

--Testcase 100:
select unique1, unique2, nextval('testseq')
  from parent_table order by tenthous limit 10;
 unique1 | unique2 | nextval 
---------+---------+---------
       0 |    9998 |      61
       1 |    2838 |      62
       2 |    2716 |      63
       3 |    5679 |      64
       4 |    1621 |      65
       5 |    5557 |      66
       6 |    2855 |      67
       7 |    8518 |      68
       8 |    5435 |      69
       9 |    4463 |      70
(10 rows)

-- when querying regular tables with inherited tables, only limit is pushed-down when no offset is specified
--Testcase 101:
explain (verbose, costs off)
select unique1, unique2, nextval('testseq')
  from parent_table order by tenthous limit 10 offset 5;
                                                           QUERY PLAN                                                           
--------------------------------------------------------------------------------------------------------------------------------
 Limit
   Output: parent_table.unique1, parent_table.unique2, (nextval('testseq'::regclass)), parent_table.tenthous
   ->  Result
         Output: parent_table.unique1, parent_table.unique2, nextval('testseq'::regclass), parent_table.tenthous
         ->  Merge Append
               Sort Key: parent_table.tenthous
               ->  Sort
                     Output: parent_table_1.unique1, parent_table_1.unique2, parent_table_1.tenthous
                     Sort Key: parent_table_1.tenthous
                     ->  Seq Scan on public.parent_table parent_table_1
                           Output: parent_table_1.unique1, parent_table_1.unique2, parent_table_1.tenthous
               ->  Foreign Scan on public.inherited_table parent_table_2
                     Output: parent_table_2.unique1, parent_table_2.unique2, parent_table_2.tenthous
                     SQLite query: SELECT `unique1`, `unique2`, `tenthous` FROM main."tenk1" ORDER BY `tenthous` ASC NULLS LAST
(14 rows)

--Testcase 102:
select unique1, unique2, nextval('testseq')
  from parent_table order by tenthous limit 10 offset 5;
 unique1 | unique2 | nextval 
---------+---------+---------
       5 |    5557 |      76
       6 |    2855 |      77
       7 |    8518 |      78
       8 |    5435 |      79
       9 |    4463 |      80
      10 |    8788 |      81
      11 |    8396 |      82
      12 |    6605 |      83
      13 |    5696 |      84
      14 |    4341 |      85
(10 rows)

--Testcase 103:
select currval('testseq');
 currval 
---------
      85
(1 row)

--Testcase 21:
explain (verbose, costs off)
select unique1, unique2, generate_series(1,10)
  from tenk1 order by unique2 limit 7;
                                                                         QUERY PLAN                                                                          
-------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit
   Output: unique1, unique2, (generate_series(1, 10))
   ->  ProjectSet
         Output: unique1, unique2, generate_series(1, 10)
         ->  Foreign Scan on public.tenk1
               Output: unique1, unique2, two, four, ten, twenty, hundred, thousand, twothousand, fivethous, tenthous, odd, even, stringu1, stringu2, string4
               SQLite query: SELECT `unique1`, `unique2` FROM main."tenk1" ORDER BY `unique2` ASC NULLS LAST
(7 rows)

--Testcase 22:
select unique1, unique2, generate_series(1,10)
  from tenk1 order by unique2 limit 7;
 unique1 | unique2 | generate_series 
---------+---------+-----------------
    8800 |       0 |               1
    8800 |       0 |               2
    8800 |       0 |               3
    8800 |       0 |               4
    8800 |       0 |               5
    8800 |       0 |               6
    8800 |       0 |               7
(7 rows)

--Testcase 23:
explain (verbose, costs off)
select unique1, unique2, generate_series(1,10)
  from tenk1 order by tenthous limit 7;
                                                                         QUERY PLAN                                                                          
-------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit
   Output: unique1, unique2, (generate_series(1, 10)), tenthous
   ->  ProjectSet
         Output: unique1, unique2, generate_series(1, 10), tenthous
         ->  Foreign Scan on public.tenk1
               Output: unique1, unique2, two, four, ten, twenty, hundred, thousand, twothousand, fivethous, tenthous, odd, even, stringu1, stringu2, string4
               SQLite query: SELECT `unique1`, `unique2`, `tenthous` FROM main."tenk1" ORDER BY `tenthous` ASC NULLS LAST
(7 rows)

--Testcase 24:
select unique1, unique2, generate_series(1,10)
  from tenk1 order by tenthous limit 7;
 unique1 | unique2 | generate_series 
---------+---------+-----------------
       0 |    9998 |               1
       0 |    9998 |               2
       0 |    9998 |               3
       0 |    9998 |               4
       0 |    9998 |               5
       0 |    9998 |               6
       0 |    9998 |               7
(7 rows)

-- use of random() is to keep planner from folding the expressions together
--Testcase 66:
DELETE FROM INT8_TMP;
--Testcase 67:
INSERT INTO INT8_TMP VALUES (generate_series(0,2), generate_series((random()*.1)::int,2));
--Testcase 68:
explain (verbose, costs off)
select q1, q2 from int8_tmp;
                       QUERY PLAN                       
--------------------------------------------------------
 Foreign Scan on public.int8_tmp
   Output: q1, q2
   SQLite query: SELECT `q1`, `q2` FROM main."int8_tmp"
(3 rows)

--Testcase 69:
select q1, q2 from int8_tmp;
 q1 | q2 
----+----
  0 |  0
  1 |  1
  2 |  2
(3 rows)

--Testcase 70:
explain (verbose, costs off)
select q1, q2 from int8_tmp order by q2 desc;
                                      QUERY PLAN                                       
---------------------------------------------------------------------------------------
 Foreign Scan on public.int8_tmp
   Output: q1, q2
   SQLite query: SELECT `q1`, `q2` FROM main."int8_tmp" ORDER BY `q2` DESC NULLS FIRST
(3 rows)

--Testcase 71:
select q1, q2 from int8_tmp order by q2 desc;
 q1 | q2 
----+----
  2 |  2
  1 |  1
  0 |  0
(3 rows)

-- test for failure to set all aggregates' aggtranstype
--Testcase 25:
explain (verbose, costs off)
select sum(tenthous) as s1, sum(tenthous) + random()*0 as s2
  from tenk1 group by thousand order by thousand limit 3;
                                                        QUERY PLAN                                                        
--------------------------------------------------------------------------------------------------------------------------
 Limit
   Output: (sum(tenthous)), ((((sum(tenthous)))::double precision + (random() * '0'::double precision))), thousand
   ->  Foreign Scan
         Output: (sum(tenthous)), (((sum(tenthous)))::double precision + (random() * '0'::double precision)), thousand
         SQLite query: SELECT sum(`tenthous`), `thousand` FROM main."tenk1" GROUP BY 2 ORDER BY `thousand` ASC NULLS LAST
(5 rows)

--Testcase 26:
select sum(tenthous) as s1, sum(tenthous) + random()*0 as s2
  from tenk1 group by thousand order by thousand limit 3;
  s1   |  s2   
-------+-------
 45000 | 45000
 45010 | 45010
 45020 | 45020
(3 rows)

--
-- FETCH FIRST
-- Check the WITH TIES clause
--
--Testcase 72:
SELECT  thousand
		FROM onek WHERE thousand < 5
		ORDER BY thousand FETCH FIRST 2 ROW WITH TIES;
 thousand 
----------
        0
        0
        0
        0
        0
        0
        0
        0
        0
        0
(10 rows)

--Testcase 73:
SELECT  thousand
		FROM onek WHERE thousand < 5
		ORDER BY thousand FETCH FIRST ROWS WITH TIES;
 thousand 
----------
        0
        0
        0
        0
        0
        0
        0
        0
        0
        0
(10 rows)

--Testcase 74:
SELECT  thousand
		FROM onek WHERE thousand < 5
		ORDER BY thousand FETCH FIRST 1 ROW WITH TIES;
 thousand 
----------
        0
        0
        0
        0
        0
        0
        0
        0
        0
        0
(10 rows)

--Testcase 75:
SELECT  thousand
		FROM onek WHERE thousand < 5
		ORDER BY thousand FETCH FIRST 2 ROW ONLY;
 thousand 
----------
        0
        0
(2 rows)

-- SKIP LOCKED and WITH TIES are incompatible
--Testcase 104:
SELECT  thousand
		FROM onek WHERE thousand < 5
		ORDER BY thousand FETCH FIRST 1 ROW WITH TIES FOR UPDATE SKIP LOCKED;
ERROR:  SKIP LOCKED and WITH TIES options cannot be used together
-- should fail
--Testcase 76:
SELECT ''::text AS two, unique1, unique2, stringu1
		FROM onek WHERE unique1 > 50
		FETCH FIRST 2 ROW WITH TIES;
ERROR:  WITH TIES cannot be specified without ORDER BY clause
-- test ruleutils
--Testcase 77:
CREATE VIEW limit_thousand_v_1 AS SELECT thousand FROM onek WHERE thousand < 995
		ORDER BY thousand FETCH FIRST 5 ROWS WITH TIES OFFSET 10;
--Testcase 78:
\d+ limit_thousand_v_1
                      View "public.limit_thousand_v_1"
  Column  |  Type   | Collation | Nullable | Default | Storage | Description 
----------+---------+-----------+----------+---------+---------+-------------
 thousand | integer |           |          |         | plain   | 
View definition:
 SELECT onek.thousand
   FROM onek
  WHERE onek.thousand < 995
  ORDER BY onek.thousand
 OFFSET 10
 FETCH FIRST 5 ROWS WITH TIES;

--Testcase 79:
CREATE VIEW limit_thousand_v_2 AS SELECT thousand FROM onek WHERE thousand < 995
		ORDER BY thousand OFFSET 10 FETCH FIRST 5 ROWS ONLY;
--Testcase 80:
\d+ limit_thousand_v_2
                      View "public.limit_thousand_v_2"
  Column  |  Type   | Collation | Nullable | Default | Storage | Description 
----------+---------+-----------+----------+---------+---------+-------------
 thousand | integer |           |          |         | plain   | 
View definition:
 SELECT onek.thousand
   FROM onek
  WHERE onek.thousand < 995
  ORDER BY onek.thousand
 OFFSET 10
 LIMIT 5;

--Testcase 81:
CREATE VIEW limit_thousand_v_3 AS SELECT thousand FROM onek WHERE thousand < 995
		ORDER BY thousand FETCH FIRST NULL ROWS WITH TIES;		-- fails
ERROR:  row count cannot be null in FETCH FIRST ... WITH TIES clause
--Testcase 82:
CREATE VIEW limit_thousand_v_3 AS SELECT thousand FROM onek WHERE thousand < 995
		ORDER BY thousand FETCH FIRST (NULL+1) ROWS WITH TIES;
--Testcase 83:
\d+ limit_thousand_v_3
                      View "public.limit_thousand_v_3"
  Column  |  Type   | Collation | Nullable | Default | Storage | Description 
----------+---------+-----------+----------+---------+---------+-------------
 thousand | integer |           |          |         | plain   | 
View definition:
 SELECT onek.thousand
   FROM onek
  WHERE onek.thousand < 995
  ORDER BY onek.thousand
 FETCH FIRST (NULL::integer + 1) ROWS WITH TIES;

--Testcase 84:
CREATE VIEW limit_thousand_v_4 AS SELECT thousand FROM onek WHERE thousand < 995
		ORDER BY thousand FETCH FIRST NULL ROWS ONLY;
--Testcase 85:
\d+ limit_thousand_v_4
                      View "public.limit_thousand_v_4"
  Column  |  Type   | Collation | Nullable | Default | Storage | Description 
----------+---------+-----------+----------+---------+---------+-------------
 thousand | integer |           |          |         | plain   | 
View definition:
 SELECT onek.thousand
   FROM onek
  WHERE onek.thousand < 995
  ORDER BY onek.thousand
 LIMIT ALL;

-- leave these views
-- Clean up
DO $d$
declare
  l_rec record;
begin
  for l_rec in (select foreign_table_schema, foreign_table_name
                from information_schema.foreign_tables) loop
     execute format('drop foreign table %I.%I cascade;', l_rec.foreign_table_schema, l_rec.foreign_table_name);
  end loop;
end;
$d$;
NOTICE:  drop cascades to 4 other objects
DETAIL:  drop cascades to view limit_thousand_v_1
drop cascades to view limit_thousand_v_2
drop cascades to view limit_thousand_v_3
drop cascades to view limit_thousand_v_4
--Testcase 86:
DROP SERVER sqlite_svr;
--Testcase 87:
DROP EXTENSION sqlite_fdw CASCADE;