-- -- LIMIT -- Check the LIMIT/OFFSET feature of SELECT -- CREATE EXTENSION sqlite_fdw; CREATE SERVER sqlite_svr FOREIGN DATA WRAPPER sqlite_fdw OPTIONS (database '/tmp/sqlitefdw_test_core.db'); 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; CREATE FOREIGN TABLE int8_tbl(q1 int8 OPTIONS (key 'true'), q2 int8) SERVER sqlite_svr; 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; 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) 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) 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) 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) 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) 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) 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) 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) 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: select * from int8_tbl limit (case when random() < 0.5 then null::bigint end); q1 | q2 ------------------+------------------- 123 | 456 123 | 4567890123456789 4567890123456789 | -4567890123456789 4567890123456789 | 123 4567890123456789 | 4567890123456789 (5 rows) select * from int8_tbl offset (case when random() < 0.5 then null::bigint end); q1 | q2 ------------------+------------------- 123 | 456 123 | 4567890123456789 4567890123456789 | -4567890123456789 4567890123456789 | 123 4567890123456789 | 4567890123456789 (5 rows) -- Test assorted cases involving backwards fetch from a LIMIT plan node begin; declare c1 cursor for select * from int8_tbl limit 10; fetch all in c1; q1 | q2 ------------------+------------------- 123 | 456 123 | 4567890123456789 4567890123456789 | -4567890123456789 4567890123456789 | 123 4567890123456789 | 4567890123456789 (5 rows) fetch 1 in c1; q1 | q2 ----+---- (0 rows) fetch backward 1 in c1; ERROR: cursor can only scan forward HINT: Declare it with SCROLL option to enable backward scan. rollback; -- -- Test behavior of volatile and set-returning functions in conjunction -- with ORDER BY and LIMIT. -- create temp sequence testseq; explain (verbose, costs off) select unique1, unique2, nextval('testseq') from tenk1 order by unique2 limit 10; QUERY PLAN --------------------------------------------------------------------------------- Limit Output: unique1, unique2, (nextval('testseq'::regclass)) -> Result Output: unique1, unique2, nextval('testseq'::regclass) -> Sort Output: unique1, unique2 Sort Key: tenk1.unique2 -> Foreign Scan on public.tenk1 Output: unique1, unique2 SQLite query: SELECT `unique1`, `unique2` FROM main."tenk1" (10 rows) 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) select currval('testseq'); currval --------- 10 (1 row) explain (verbose, costs off) select unique1, unique2, nextval('testseq') from tenk1 order by tenthous limit 10; QUERY PLAN --------------------------------------------------------------------------------------------- Limit Output: unique1, unique2, (nextval('testseq'::regclass)), tenthous -> Result Output: unique1, unique2, nextval('testseq'::regclass), tenthous -> Sort Output: unique1, unique2, tenthous Sort Key: tenk1.tenthous -> Foreign Scan on public.tenk1 Output: unique1, unique2, tenthous SQLite query: SELECT `unique1`, `unique2`, `tenthous` FROM main."tenk1" (10 rows) select unique1, unique2, nextval('testseq') from tenk1 order by tenthous limit 10; unique1 | unique2 | nextval ---------+---------+--------- 0 | 9998 | 11 1 | 2838 | 12 2 | 2716 | 13 3 | 5679 | 14 4 | 1621 | 15 5 | 5557 | 16 6 | 2855 | 17 7 | 8518 | 18 8 | 5435 | 19 9 | 4463 | 20 (10 rows) select currval('testseq'); currval --------- 20 (1 row) 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) -> Sort Output: unique1, unique2 Sort Key: tenk1.unique2 -> Foreign Scan on public.tenk1 Output: unique1, unique2 SQLite query: SELECT `unique1`, `unique2` FROM main."tenk1" (10 rows) 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) 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 -> Sort Output: unique1, unique2, tenthous Sort Key: tenk1.tenthous -> Foreign Scan on public.tenk1 Output: unique1, unique2, tenthous SQLite query: SELECT `unique1`, `unique2`, `tenthous` FROM main."tenk1" (10 rows) 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) -- test for failure to set all aggregates' aggtranstype 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 -> Result Output: (sum(tenthous)), (((sum(tenthous)))::double precision + (random() * '0'::double precision)), thousand -> Sort Output: (sum(tenthous)), thousand Sort Key: tenk1.thousand -> Foreign Scan Output: (sum(tenthous)), thousand SQLite query: SELECT sum(`tenthous`), `thousand` FROM main."tenk1" GROUP BY 2 (10 rows) 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) DROP FOREIGN TABLE onek; DROP FOREIGN TABLE int8_tbl; DROP FOREIGN TABLE tenk1; DROP SERVER sqlite_svr; DROP EXTENSION sqlite_fdw CASCADE;