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