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 74declare c5 cursor for select * from int8_tbl order by q1 fetch first 2 rows with ties; 75fetch all in c5; 76fetch 1 in c5; 77fetch backward 1 in c5; 78fetch backward 1 in c5; 79fetch all in c5; 80fetch backward all in c5; 81fetch all in c5; 82fetch backward all in c5; 83 84rollback; 85 86-- Stress test for variable LIMIT in conjunction with bounded-heap sorting 87 88SELECT 89 (SELECT n 90 FROM (VALUES (1)) AS x, 91 (SELECT n FROM generate_series(1,10) AS n 92 ORDER BY n LIMIT 1 OFFSET s-1) AS y) AS z 93 FROM generate_series(1,10) AS s; 94 95-- 96-- Test behavior of volatile and set-returning functions in conjunction 97-- with ORDER BY and LIMIT. 98-- 99 100create temp sequence testseq; 101 102explain (verbose, costs off) 103select unique1, unique2, nextval('testseq') 104 from tenk1 order by unique2 limit 10; 105 106select unique1, unique2, nextval('testseq') 107 from tenk1 order by unique2 limit 10; 108 109select currval('testseq'); 110 111explain (verbose, costs off) 112select unique1, unique2, nextval('testseq') 113 from tenk1 order by tenthous limit 10; 114 115select unique1, unique2, nextval('testseq') 116 from tenk1 order by tenthous limit 10; 117 118select currval('testseq'); 119 120explain (verbose, costs off) 121select unique1, unique2, generate_series(1,10) 122 from tenk1 order by unique2 limit 7; 123 124select unique1, unique2, generate_series(1,10) 125 from tenk1 order by unique2 limit 7; 126 127explain (verbose, costs off) 128select unique1, unique2, generate_series(1,10) 129 from tenk1 order by tenthous limit 7; 130 131select unique1, unique2, generate_series(1,10) 132 from tenk1 order by tenthous limit 7; 133 134-- use of random() is to keep planner from folding the expressions together 135explain (verbose, costs off) 136select generate_series(0,2) as s1, generate_series((random()*.1)::int,2) as s2; 137 138select generate_series(0,2) as s1, generate_series((random()*.1)::int,2) as s2; 139 140explain (verbose, costs off) 141select generate_series(0,2) as s1, generate_series((random()*.1)::int,2) as s2 142order by s2 desc; 143 144select generate_series(0,2) as s1, generate_series((random()*.1)::int,2) as s2 145order by s2 desc; 146 147-- test for failure to set all aggregates' aggtranstype 148explain (verbose, costs off) 149select sum(tenthous) as s1, sum(tenthous) + random()*0 as s2 150 from tenk1 group by thousand order by thousand limit 3; 151 152select sum(tenthous) as s1, sum(tenthous) + random()*0 as s2 153 from tenk1 group by thousand order by thousand limit 3; 154 155-- 156-- FETCH FIRST 157-- Check the WITH TIES clause 158-- 159 160SELECT thousand 161 FROM onek WHERE thousand < 5 162 ORDER BY thousand FETCH FIRST 2 ROW WITH TIES; 163 164SELECT thousand 165 FROM onek WHERE thousand < 5 166 ORDER BY thousand FETCH FIRST ROWS WITH TIES; 167 168SELECT thousand 169 FROM onek WHERE thousand < 5 170 ORDER BY thousand FETCH FIRST 1 ROW WITH TIES; 171 172SELECT thousand 173 FROM onek WHERE thousand < 5 174 ORDER BY thousand FETCH FIRST 2 ROW ONLY; 175 176-- SKIP LOCKED and WITH TIES are incompatible 177SELECT thousand 178 FROM onek WHERE thousand < 5 179 ORDER BY thousand FETCH FIRST 1 ROW WITH TIES FOR UPDATE SKIP LOCKED; 180 181-- should fail 182SELECT ''::text AS two, unique1, unique2, stringu1 183 FROM onek WHERE unique1 > 50 184 FETCH FIRST 2 ROW WITH TIES; 185 186-- test ruleutils 187CREATE VIEW limit_thousand_v_1 AS SELECT thousand FROM onek WHERE thousand < 995 188 ORDER BY thousand FETCH FIRST 5 ROWS WITH TIES OFFSET 10; 189\d+ limit_thousand_v_1 190CREATE VIEW limit_thousand_v_2 AS SELECT thousand FROM onek WHERE thousand < 995 191 ORDER BY thousand OFFSET 10 FETCH FIRST 5 ROWS ONLY; 192\d+ limit_thousand_v_2 193CREATE VIEW limit_thousand_v_3 AS SELECT thousand FROM onek WHERE thousand < 995 194 ORDER BY thousand FETCH FIRST NULL ROWS WITH TIES; -- fails 195CREATE VIEW limit_thousand_v_3 AS SELECT thousand FROM onek WHERE thousand < 995 196 ORDER BY thousand FETCH FIRST (NULL+1) ROWS WITH TIES; 197\d+ limit_thousand_v_3 198CREATE VIEW limit_thousand_v_4 AS SELECT thousand FROM onek WHERE thousand < 995 199 ORDER BY thousand FETCH FIRST NULL ROWS ONLY; 200\d+ limit_thousand_v_4 201-- leave these views 202