1-- 2-- LIMIT 3-- Check the LIMIT/OFFSET feature of SELECT 4-- 5 6SELECT ''::text AS two, unique1, unique2, stringu1 7 FROM onek WHERE unique1 > 50 8 ORDER BY unique1 LIMIT 2; 9SELECT ''::text AS five, unique1, unique2, stringu1 10 FROM onek WHERE unique1 > 60 11 ORDER BY unique1 LIMIT 5; 12SELECT ''::text AS two, unique1, unique2, stringu1 13 FROM onek WHERE unique1 > 60 AND unique1 < 63 14 ORDER BY unique1 LIMIT 5; 15SELECT ''::text AS three, unique1, unique2, stringu1 16 FROM onek WHERE unique1 > 100 17 ORDER BY unique1 LIMIT 3 OFFSET 20; 18SELECT ''::text AS zero, unique1, unique2, stringu1 19 FROM onek WHERE unique1 < 50 20 ORDER BY unique1 DESC LIMIT 8 OFFSET 99; 21SELECT ''::text AS eleven, unique1, unique2, stringu1 22 FROM onek WHERE unique1 < 50 23 ORDER BY unique1 DESC LIMIT 20 OFFSET 39; 24SELECT ''::text AS ten, unique1, unique2, stringu1 25 FROM onek 26 ORDER BY unique1 OFFSET 990; 27SELECT ''::text AS five, unique1, unique2, stringu1 28 FROM onek 29 ORDER BY unique1 OFFSET 990 LIMIT 5; 30SELECT ''::text AS five, unique1, unique2, stringu1 31 FROM onek 32 ORDER BY unique1 LIMIT 5 OFFSET 900; 33 34-- Test null limit and offset. The planner would discard a simple null 35-- constant, so to ensure executor is exercised, do this: 36select * from int8_tbl limit (case when random() < 0.5 then null::bigint end); 37select * from int8_tbl offset (case when random() < 0.5 then null::bigint end); 38 39-- Test assorted cases involving backwards fetch from a LIMIT plan node 40begin; 41 42declare c1 cursor for select * from int8_tbl limit 10; 43fetch all in c1; 44fetch 1 in c1; 45fetch backward 1 in c1; 46fetch backward all in c1; 47fetch backward 1 in c1; 48fetch all in c1; 49 50declare c2 cursor for select * from int8_tbl limit 3; 51fetch all in c2; 52fetch 1 in c2; 53fetch backward 1 in c2; 54fetch backward all in c2; 55fetch backward 1 in c2; 56fetch all in c2; 57 58declare c3 cursor for select * from int8_tbl offset 3; 59fetch all in c3; 60fetch 1 in c3; 61fetch backward 1 in c3; 62fetch backward all in c3; 63fetch backward 1 in c3; 64fetch all in c3; 65 66declare c4 cursor for select * from int8_tbl offset 10; 67fetch all in c4; 68fetch 1 in c4; 69fetch backward 1 in c4; 70fetch backward all in c4; 71fetch backward 1 in c4; 72fetch all in c4; 73 74rollback; 75 76-- Stress test for variable LIMIT in conjunction with bounded-heap sorting 77 78SELECT 79 (SELECT n 80 FROM (VALUES (1)) AS x, 81 (SELECT n FROM generate_series(1,10) AS n 82 ORDER BY n LIMIT 1 OFFSET s-1) AS y) AS z 83 FROM generate_series(1,10) AS s; 84 85-- 86-- Test behavior of volatile and set-returning functions in conjunction 87-- with ORDER BY and LIMIT. 88-- 89 90create temp sequence testseq; 91 92explain (verbose, costs off) 93select unique1, unique2, nextval('testseq') 94 from tenk1 order by unique2 limit 10; 95 96select unique1, unique2, nextval('testseq') 97 from tenk1 order by unique2 limit 10; 98 99select currval('testseq'); 100 101explain (verbose, costs off) 102select unique1, unique2, nextval('testseq') 103 from tenk1 order by tenthous limit 10; 104 105select unique1, unique2, nextval('testseq') 106 from tenk1 order by tenthous limit 10; 107 108select currval('testseq'); 109 110explain (verbose, costs off) 111select unique1, unique2, generate_series(1,10) 112 from tenk1 order by unique2 limit 7; 113 114select unique1, unique2, generate_series(1,10) 115 from tenk1 order by unique2 limit 7; 116 117explain (verbose, costs off) 118select unique1, unique2, generate_series(1,10) 119 from tenk1 order by tenthous limit 7; 120 121select unique1, unique2, generate_series(1,10) 122 from tenk1 order by tenthous limit 7; 123 124-- use of random() is to keep planner from folding the expressions together 125explain (verbose, costs off) 126select generate_series(0,2) as s1, generate_series((random()*.1)::int,2) as s2; 127 128select generate_series(0,2) as s1, generate_series((random()*.1)::int,2) as s2; 129 130explain (verbose, costs off) 131select generate_series(0,2) as s1, generate_series((random()*.1)::int,2) as s2 132order by s2 desc; 133 134select generate_series(0,2) as s1, generate_series((random()*.1)::int,2) as s2 135order by s2 desc; 136 137-- test for failure to set all aggregates' aggtranstype 138explain (verbose, costs off) 139select sum(tenthous) as s1, sum(tenthous) + random()*0 as s2 140 from tenk1 group by thousand order by thousand limit 3; 141 142select sum(tenthous) as s1, sum(tenthous) + random()*0 as s2 143 from tenk1 group by thousand order by thousand limit 3; 144