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-- Stress test for variable LIMIT in conjunction with bounded-heap sorting 35 36SELECT 37 (SELECT n 38 FROM (VALUES (1)) AS x, 39 (SELECT n FROM generate_series(1,10) AS n 40 ORDER BY n LIMIT 1 OFFSET s-1) AS y) AS z 41 FROM generate_series(1,10) AS s; 42 43-- 44-- Test behavior of volatile and set-returning functions in conjunction 45-- with ORDER BY and LIMIT. 46-- 47 48create temp sequence testseq; 49 50explain (verbose, costs off) 51select unique1, unique2, nextval('testseq') 52 from tenk1 order by unique2 limit 10; 53 54select unique1, unique2, nextval('testseq') 55 from tenk1 order by unique2 limit 10; 56 57select currval('testseq'); 58 59explain (verbose, costs off) 60select unique1, unique2, nextval('testseq') 61 from tenk1 order by tenthous limit 10; 62 63select unique1, unique2, nextval('testseq') 64 from tenk1 order by tenthous limit 10; 65 66select currval('testseq'); 67 68explain (verbose, costs off) 69select unique1, unique2, generate_series(1,10) 70 from tenk1 order by unique2 limit 7; 71 72select unique1, unique2, generate_series(1,10) 73 from tenk1 order by unique2 limit 7; 74 75explain (verbose, costs off) 76select unique1, unique2, generate_series(1,10) 77 from tenk1 order by tenthous limit 7; 78 79select unique1, unique2, generate_series(1,10) 80 from tenk1 order by tenthous limit 7; 81 82-- use of random() is to keep planner from folding the expressions together 83explain (verbose, costs off) 84select generate_series(0,2) as s1, generate_series((random()*.1)::int,2) as s2; 85 86select generate_series(0,2) as s1, generate_series((random()*.1)::int,2) as s2; 87 88explain (verbose, costs off) 89select generate_series(0,2) as s1, generate_series((random()*.1)::int,2) as s2 90order by s2 desc; 91 92select generate_series(0,2) as s1, generate_series((random()*.1)::int,2) as s2 93order by s2 desc; 94 95-- test for failure to set all aggregates' aggtranstype 96explain (verbose, costs off) 97select sum(tenthous) as s1, sum(tenthous) + random()*0 as s2 98 from tenk1 group by thousand order by thousand limit 3; 99 100select sum(tenthous) as s1, sum(tenthous) + random()*0 as s2 101 from tenk1 group by thousand order by thousand limit 3; 102