| blob | 5dd2f33d1cae4ace8299d24c08a55f143141b20a |
1 #
2 # 2001 November 6
3 #
4 # The author disclaims copyright to this source code. In place of
5 # a legal notice, here is a blessing:
6 #
7 # May you do good and not evil.
8 # May you find forgiveness for yourself and forgive others.
9 # May you share freely, never taking more than you give.
10 #
11 #***********************************************************************
12 # This file implements regression tests for SQLite library. The
13 # focus of this file is testing the LIMIT ... OFFSET ... clause
14 # of SELECT statements.
15 #
16 # $Id: limit.test,v 1.11.2.1 2004/07/19 23:33:04 drh Exp $
18 set testdir [file dirname $argv0]
19 source $testdir/tester.tcl
21 # Build some test data
22 #
23 set fd [open data1.txt w]
24 for {set i 1} {$i<=32} {incr i} {
25 for {set j 0} {pow(2,$j)<$i} {incr j} {}
26 puts $fd "[expr {32-$i}]\t[expr {10-$j}]"
27 }
28 close $fd
29 execsql {
30 CREATE TABLE t1(x int, y int);
31 COPY t1 FROM 'data1.txt'
32 }
33 file delete data1.txt
35 do_test limit-1.0 {
36 execsql {SELECT count(*) FROM t1}
37 } {32}
38 do_test limit-1.1 {
39 execsql {SELECT count(*) FROM t1 LIMIT 5}
40 } {32}
41 do_test limit-1.2.1 {
42 execsql {SELECT x FROM t1 ORDER BY x LIMIT 5}
43 } {0 1 2 3 4}
44 do_test limit-1.2.2 {
45 execsql {SELECT x FROM t1 ORDER BY x LIMIT 5 OFFSET 2}
46 } {2 3 4 5 6}
47 do_test limit-1.2.3 {
48 execsql {SELECT x FROM t1 ORDER BY x LIMIT 2, 5}
49 } {2 3 4 5 6}
50 do_test limit-1.3 {
51 execsql {SELECT x FROM t1 ORDER BY x LIMIT 5 OFFSET 5}
52 } {5 6 7 8 9}
53 do_test limit-1.4.1 {
54 execsql {SELECT x FROM t1 ORDER BY x LIMIT 50 OFFSET 30}
55 } {30 31}
56 do_test limit-1.4.2 {
57 execsql {SELECT x FROM t1 ORDER BY x LIMIT 30, 50}
58 } {30 31}
59 do_test limit-1.5 {
60 execsql {SELECT x FROM t1 ORDER BY x LIMIT 50 OFFSET 50}
61 } {}
62 do_test limit-1.6 {
63 execsql {SELECT * FROM t1 AS a, t1 AS b ORDER BY a.x, b.x LIMIT 5}
64 } {0 5 0 5 0 5 1 5 0 5 2 5 0 5 3 5 0 5 4 5}
65 do_test limit-1.7 {
66 execsql {SELECT * FROM t1 AS a, t1 AS b ORDER BY a.x, b.x LIMIT 5 OFFSET 32}
67 } {1 5 0 5 1 5 1 5 1 5 2 5 1 5 3 5 1 5 4 5}
69 do_test limit-2.1 {
70 execsql {
71 CREATE VIEW v1 AS SELECT * FROM t1 LIMIT 2;
72 SELECT count(*) FROM (SELECT * FROM v1);
73 }
74 } 2
75 do_test limit-2.2 {
76 execsql {
77 CREATE TABLE t2 AS SELECT * FROM t1 LIMIT 2;
78 SELECT count(*) FROM t2;
79 }
80 } 2
81 do_test limit-2.3 {
82 execsql {
83 SELECT count(*) FROM t1 WHERE rowid IN (SELECT rowid FROM t1 LIMIT 2);
84 }
85 } 2
87 do_test limit-3.1 {
88 execsql {
89 SELECT z FROM (SELECT y*10+x AS z FROM t1 ORDER BY x LIMIT 10)
90 ORDER BY z LIMIT 5;
91 }
92 } {50 51 52 53 54}
94 do_test limit-4.1 {
95 execsql {
96 BEGIN;
97 CREATE TABLE t3(x);
98 INSERT INTO t3 SELECT x FROM t1 ORDER BY x LIMIT 10 OFFSET 1;
99 INSERT INTO t3 SELECT x+(SELECT max(x) FROM t3) FROM t3;
100 INSERT INTO t3 SELECT x+(SELECT max(x) FROM t3) FROM t3;
101 INSERT INTO t3 SELECT x+(SELECT max(x) FROM t3) FROM t3;
102 INSERT INTO t3 SELECT x+(SELECT max(x) FROM t3) FROM t3;
103 INSERT INTO t3 SELECT x+(SELECT max(x) FROM t3) FROM t3;
104 INSERT INTO t3 SELECT x+(SELECT max(x) FROM t3) FROM t3;
105 INSERT INTO t3 SELECT x+(SELECT max(x) FROM t3) FROM t3;
106 INSERT INTO t3 SELECT x+(SELECT max(x) FROM t3) FROM t3;
107 INSERT INTO t3 SELECT x+(SELECT max(x) FROM t3) FROM t3;
108 INSERT INTO t3 SELECT x+(SELECT max(x) FROM t3) FROM t3;
109 END;
110 SELECT count(*) FROM t3;
111 }
112 } {10240}
113 do_test limit-4.2 {
114 execsql {
115 SELECT x FROM t3 LIMIT 2 OFFSET 10000
116 }
117 } {10001 10002}
118 do_test limit-4.3 {
119 execsql {
120 CREATE TABLE t4 AS SELECT x,
121 'abcdefghijklmnopqrstuvwyxz ABCDEFGHIJKLMNOPQRSTUVWYXZ' || x ||
122 'abcdefghijklmnopqrstuvwyxz ABCDEFGHIJKLMNOPQRSTUVWYXZ' || x ||
123 'abcdefghijklmnopqrstuvwyxz ABCDEFGHIJKLMNOPQRSTUVWYXZ' || x ||
124 'abcdefghijklmnopqrstuvwyxz ABCDEFGHIJKLMNOPQRSTUVWYXZ' || x ||
125 'abcdefghijklmnopqrstuvwyxz ABCDEFGHIJKLMNOPQRSTUVWYXZ' || x AS y
126 FROM t3 LIMIT 1000;
127 SELECT x FROM t4 ORDER BY y DESC LIMIT 1 OFFSET 999;
128 }
129 } {1000}
131 do_test limit-5.1 {
132 execsql {
133 CREATE TABLE t5(x,y);
134 INSERT INTO t5 SELECT x-y, x+y FROM t1 WHERE x BETWEEN 10 AND 15
135 ORDER BY x LIMIT 2;
136 SELECT * FROM t5 ORDER BY x;
137 }
138 } {5 15 6 16}
139 do_test limit-5.2 {
140 execsql {
141 DELETE FROM t5;
142 INSERT INTO t5 SELECT x-y, x+y FROM t1 WHERE x BETWEEN 10 AND 15
143 ORDER BY x DESC LIMIT 2;
144 SELECT * FROM t5 ORDER BY x;
145 }
146 } {9 19 10 20}
147 do_test limit-5.3 {
148 execsql {
149 DELETE FROM t5;
150 INSERT INTO t5 SELECT x-y, x+y FROM t1 WHERE x ORDER BY x DESC LIMIT 31;
151 SELECT * FROM t5 ORDER BY x LIMIT 2;
152 }
153 } {-4 6 -3 7}
154 do_test limit-5.4 {
155 execsql {
156 SELECT * FROM t5 ORDER BY x DESC, y DESC LIMIT 2;
157 }
158 } {21 41 21 39}
159 do_test limit-5.5 {
160 execsql {
161 DELETE FROM t5;
162 INSERT INTO t5 SELECT a.x*100+b.x, a.y*100+b.y FROM t1 AS a, t1 AS b
163 ORDER BY 1, 2 LIMIT 1000;
164 SELECT count(*), sum(x), sum(y), min(x), max(x), min(y), max(y) FROM t5;
165 }
166 } {1000 1528204 593161 0 3107 505 1005}
168 # There is some contraversy about whether LIMIT 0 should be the same as
169 # no limit at all or if LIMIT 0 should result in zero output rows.
170 #
171 do_test limit-6.1 {
172 execsql {
173 BEGIN;
174 CREATE TABLE t6(a);
175 INSERT INTO t6 VALUES(1);
176 INSERT INTO t6 VALUES(2);
177 INSERT INTO t6 SELECT a+2 FROM t6;
178 COMMIT;
179 SELECT * FROM t6;
180 }
181 } {1 2 3 4}
182 do_test limit-6.2 {
183 execsql {
184 SELECT * FROM t6 LIMIT -1 OFFSET -1;
185 }
186 } {1 2 3 4}
187 do_test limit-6.3 {
188 execsql {
189 SELECT * FROM t6 LIMIT 2 OFFSET -123;
190 }
191 } {1 2}
192 do_test limit-6.4 {
193 execsql {
194 SELECT * FROM t6 LIMIT -432 OFFSET 2;
195 }
196 } {3 4}
197 do_test limit-6.5 {
198 execsql {
199 SELECT * FROM t6 LIMIT -1
200 }
201 } {1 2 3 4}
202 do_test limit-6.6 {
203 execsql {
204 SELECT * FROM t6 LIMIT -1 OFFSET 1
205 }
206 } {2 3 4}
207 do_test limit-6.7 {
208 execsql {
209 SELECT * FROM t6 LIMIT 0
210 }
211 } {}
212 do_test limit-6.8 {
213 execsql {
214 SELECT * FROM t6 LIMIT 0 OFFSET 1
215 }
216 } {}
218 # Make sure LIMIT works well with compound SELECT statements.
219 # Ticket #393
220 #
221 do_test limit-7.1.1 {
222 catchsql {
223 SELECT x FROM t2 LIMIT 5 UNION ALL SELECT a FROM t6;
224 }
225 } {1 {LIMIT clause should come after UNION ALL not before}}
226 do_test limit-7.1.2 {
227 catchsql {
228 SELECT x FROM t2 LIMIT 5 UNION SELECT a FROM t6;
229 }
230 } {1 {LIMIT clause should come after UNION not before}}
231 do_test limit-7.1.3 {
232 catchsql {
233 SELECT x FROM t2 LIMIT 5 EXCEPT SELECT a FROM t6 LIMIT 3;
234 }
235 } {1 {LIMIT clause should come after EXCEPT not before}}
236 do_test limit-7.1.4 {
237 catchsql {
238 SELECT x FROM t2 LIMIT 0,5 INTERSECT SELECT a FROM t6;
239 }
240 } {1 {LIMIT clause should come after INTERSECT not before}}
241 do_test limit-7.2 {
242 execsql {
243 SELECT x FROM t2 UNION ALL SELECT a FROM t6 LIMIT 5;
244 }
245 } {31 30 1 2 3}
246 do_test limit-7.3 {
247 execsql {
248 SELECT x FROM t2 UNION ALL SELECT a FROM t6 LIMIT 3 OFFSET 1;
249 }
250 } {30 1 2}
251 do_test limit-7.4 {
252 execsql {
253 SELECT x FROM t2 UNION ALL SELECT a FROM t6 ORDER BY 1 LIMIT 3 OFFSET 1;
254 }
255 } {2 3 4}
256 do_test limit-7.5 {
257 execsql {
258 SELECT x FROM t2 UNION SELECT x+2 FROM t2 LIMIT 2 OFFSET 1;
259 }
260 } {31 32}
261 do_test limit-7.6 {
262 execsql {
263 SELECT x FROM t2 UNION SELECT x+2 FROM t2 ORDER BY 1 DESC LIMIT 2 OFFSET 1;
264 }
265 } {32 31}
266 do_test limit-7.7 {
267 execsql {
268 SELECT a+9 FROM t6 EXCEPT SELECT y FROM t2 LIMIT 2;
269 }
270 } {11 12}
271 do_test limit-7.8 {
272 execsql {
273 SELECT a+9 FROM t6 EXCEPT SELECT y FROM t2 ORDER BY 1 DESC LIMIT 2;
274 }
275 } {13 12}
276 do_test limit-7.9 {
277 execsql {
278 SELECT a+26 FROM t6 INTERSECT SELECT x FROM t2 LIMIT 1;
279 }
280 } {30}
281 do_test limit-7.10 {
282 execsql {
283 SELECT a+27 FROM t6 INTERSECT SELECT x FROM t2 LIMIT 1;
284 }
285 } {30}
286 do_test limit-7.11 {
287 execsql {
288 SELECT a+27 FROM t6 INTERSECT SELECT x FROM t2 LIMIT 1 OFFSET 1;
289 }
290 } {31}
291 do_test limit-7.12 {
292 execsql {
293 SELECT a+27 FROM t6 INTERSECT SELECT x FROM t2
294 ORDER BY 1 DESC LIMIT 1 OFFSET 1;
295 }
296 } {30}
298 # Tests for limit in conjunction with distinct. The distinct should
299 # occur before both the limit and the offset. Ticket #749.
300 #
301 do_test limit-8.1 {
302 execsql {
303 SELECT DISTINCT round(x/100) FROM t3 LIMIT 5;
304 }
305 } {0 1 2 3 4}
306 do_test limit-8.2 {
307 execsql {
308 SELECT DISTINCT round(x/100) FROM t3 LIMIT 5 OFFSET 5;
309 }
310 } {5 6 7 8 9}
311 do_test limit-8.3 {
312 execsql {
313 SELECT DISTINCT round(x/100) FROM t3 LIMIT 5 OFFSET 25;
314 }
315 } {25 26 27 28 29}
318 finish_test