1: # 2001 November 6
2: #
3: # The author disclaims copyright to this source code. In place of
4: # a legal notice, here is a blessing:
5: #
6: # May you do good and not evil.
7: # May you find forgiveness for yourself and forgive others.
8: # May you share freely, never taking more than you give.
9: #
10: #***********************************************************************
11: # This file implements regression tests for SQLite library. The
12: # focus of this file is testing the LIMIT ... OFFSET ... clause
13: # of SELECT statements.
14: #
15: # $Id: limit.test,v 1.1.1.1 2012/02/21 17:04:16 misho Exp $
16:
17: set testdir [file dirname $argv0]
18: source $testdir/tester.tcl
19:
20: # Build some test data
21: #
22: execsql {
23: CREATE TABLE t1(x int, y int);
24: BEGIN;
25: }
26: for {set i 1} {$i<=32} {incr i} {
27: for {set j 0} {(1<<$j)<$i} {incr j} {}
28: execsql "INSERT INTO t1 VALUES([expr {32-$i}],[expr {10-$j}])"
29: }
30: execsql {
31: COMMIT;
32: }
33:
34: do_test limit-1.0 {
35: execsql {SELECT count(*) FROM t1}
36: } {32}
37: do_test limit-1.1 {
38: execsql {SELECT count(*) FROM t1 LIMIT 5}
39: } {32}
40: do_test limit-1.2.1 {
41: execsql {SELECT x FROM t1 ORDER BY x LIMIT 5}
42: } {0 1 2 3 4}
43: do_test limit-1.2.2 {
44: execsql {SELECT x FROM t1 ORDER BY x LIMIT 5 OFFSET 2}
45: } {2 3 4 5 6}
46: do_test limit-1.2.3 {
47: execsql {SELECT x FROM t1 ORDER BY x+1 LIMIT 5 OFFSET -2}
48: } {0 1 2 3 4}
49: do_test limit-1.2.4 {
50: execsql {SELECT x FROM t1 ORDER BY x+1 LIMIT 2, -5}
51: } {2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31}
52: do_test limit-1.2.5 {
53: execsql {SELECT x FROM t1 ORDER BY x+1 LIMIT -2, 5}
54: } {0 1 2 3 4}
55: do_test limit-1.2.6 {
56: execsql {SELECT x FROM t1 ORDER BY x+1 LIMIT -2, -5}
57: } {0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31}
58: do_test limit-1.2.7 {
59: execsql {SELECT x FROM t1 ORDER BY x LIMIT 2, 5}
60: } {2 3 4 5 6}
61: do_test limit-1.3 {
62: execsql {SELECT x FROM t1 ORDER BY x LIMIT 5 OFFSET 5}
63: } {5 6 7 8 9}
64: do_test limit-1.4.1 {
65: execsql {SELECT x FROM t1 ORDER BY x LIMIT 50 OFFSET 30}
66: } {30 31}
67: do_test limit-1.4.2 {
68: execsql {SELECT x FROM t1 ORDER BY x LIMIT 30, 50}
69: } {30 31}
70: do_test limit-1.5 {
71: execsql {SELECT x FROM t1 ORDER BY x LIMIT 50 OFFSET 50}
72: } {}
73: do_test limit-1.6 {
74: execsql {SELECT * FROM t1 AS a, t1 AS b ORDER BY a.x, b.x LIMIT 5}
75: } {0 5 0 5 0 5 1 5 0 5 2 5 0 5 3 5 0 5 4 5}
76: do_test limit-1.7 {
77: execsql {SELECT * FROM t1 AS a, t1 AS b ORDER BY a.x, b.x LIMIT 5 OFFSET 32}
78: } {1 5 0 5 1 5 1 5 1 5 2 5 1 5 3 5 1 5 4 5}
79:
80: ifcapable {view && subquery} {
81: do_test limit-2.1 {
82: execsql {
83: CREATE VIEW v1 AS SELECT * FROM t1 LIMIT 2;
84: SELECT count(*) FROM (SELECT * FROM v1);
85: }
86: } 2
87: } ;# ifcapable view
88: do_test limit-2.2 {
89: execsql {
90: CREATE TABLE t2 AS SELECT * FROM t1 LIMIT 2;
91: SELECT count(*) FROM t2;
92: }
93: } 2
94: ifcapable subquery {
95: do_test limit-2.3 {
96: execsql {
97: SELECT count(*) FROM t1 WHERE rowid IN (SELECT rowid FROM t1 LIMIT 2);
98: }
99: } 2
100: }
101:
102: ifcapable subquery {
103: do_test limit-3.1 {
104: execsql {
105: SELECT z FROM (SELECT y*10+x AS z FROM t1 ORDER BY x LIMIT 10)
106: ORDER BY z LIMIT 5;
107: }
108: } {50 51 52 53 54}
109: }
110:
111: do_test limit-4.1 {
112: ifcapable subquery {
113: execsql {
114: BEGIN;
115: CREATE TABLE t3(x);
116: INSERT INTO t3 SELECT x FROM t1 ORDER BY x LIMIT 10 OFFSET 1;
117: INSERT INTO t3 SELECT x+(SELECT max(x) FROM t3) FROM t3;
118: INSERT INTO t3 SELECT x+(SELECT max(x) FROM t3) FROM t3;
119: INSERT INTO t3 SELECT x+(SELECT max(x) FROM t3) FROM t3;
120: INSERT INTO t3 SELECT x+(SELECT max(x) FROM t3) FROM t3;
121: INSERT INTO t3 SELECT x+(SELECT max(x) FROM t3) FROM t3;
122: INSERT INTO t3 SELECT x+(SELECT max(x) FROM t3) FROM t3;
123: INSERT INTO t3 SELECT x+(SELECT max(x) FROM t3) FROM t3;
124: INSERT INTO t3 SELECT x+(SELECT max(x) FROM t3) FROM t3;
125: INSERT INTO t3 SELECT x+(SELECT max(x) FROM t3) FROM t3;
126: INSERT INTO t3 SELECT x+(SELECT max(x) FROM t3) FROM t3;
127: END;
128: SELECT count(*) FROM t3;
129: }
130: } else {
131: execsql {
132: BEGIN;
133: CREATE TABLE t3(x);
134: INSERT INTO t3 SELECT x FROM t1 ORDER BY x LIMIT 10 OFFSET 1;
135: }
136: for {set i 0} {$i<10} {incr i} {
137: set max_x_t3 [execsql {SELECT max(x) FROM t3}]
138: execsql "INSERT INTO t3 SELECT x+$max_x_t3 FROM t3;"
139: }
140: execsql {
141: END;
142: SELECT count(*) FROM t3;
143: }
144: }
145: } {10240}
146: do_test limit-4.2 {
147: execsql {
148: SELECT x FROM t3 LIMIT 2 OFFSET 10000
149: }
150: } {10001 10002}
151: do_test limit-4.3 {
152: execsql {
153: CREATE TABLE t4 AS SELECT x,
154: 'abcdefghijklmnopqrstuvwyxz ABCDEFGHIJKLMNOPQRSTUVWYXZ' || x ||
155: 'abcdefghijklmnopqrstuvwyxz ABCDEFGHIJKLMNOPQRSTUVWYXZ' || x ||
156: 'abcdefghijklmnopqrstuvwyxz ABCDEFGHIJKLMNOPQRSTUVWYXZ' || x ||
157: 'abcdefghijklmnopqrstuvwyxz ABCDEFGHIJKLMNOPQRSTUVWYXZ' || x ||
158: 'abcdefghijklmnopqrstuvwyxz ABCDEFGHIJKLMNOPQRSTUVWYXZ' || x AS y
159: FROM t3 LIMIT 1000;
160: SELECT x FROM t4 ORDER BY y DESC LIMIT 1 OFFSET 999;
161: }
162: } {1000}
163:
164: do_test limit-5.1 {
165: execsql {
166: CREATE TABLE t5(x,y);
167: INSERT INTO t5 SELECT x-y, x+y FROM t1 WHERE x BETWEEN 10 AND 15
168: ORDER BY x LIMIT 2;
169: SELECT * FROM t5 ORDER BY x;
170: }
171: } {5 15 6 16}
172: do_test limit-5.2 {
173: execsql {
174: DELETE FROM t5;
175: INSERT INTO t5 SELECT x-y, x+y FROM t1 WHERE x BETWEEN 10 AND 15
176: ORDER BY x DESC LIMIT 2;
177: SELECT * FROM t5 ORDER BY x;
178: }
179: } {9 19 10 20}
180: do_test limit-5.3 {
181: execsql {
182: DELETE FROM t5;
183: INSERT INTO t5 SELECT x-y, x+y FROM t1 WHERE x ORDER BY x DESC LIMIT 31;
184: SELECT * FROM t5 ORDER BY x LIMIT 2;
185: }
186: } {-4 6 -3 7}
187: do_test limit-5.4 {
188: execsql {
189: SELECT * FROM t5 ORDER BY x DESC, y DESC LIMIT 2;
190: }
191: } {21 41 21 39}
192: do_test limit-5.5 {
193: execsql {
194: DELETE FROM t5;
195: INSERT INTO t5 SELECT a.x*100+b.x, a.y*100+b.y FROM t1 AS a, t1 AS b
196: ORDER BY 1, 2 LIMIT 1000;
197: SELECT count(*), sum(x), sum(y), min(x), max(x), min(y), max(y) FROM t5;
198: }
199: } {1000 1528204 593161 0 3107 505 1005}
200:
201: # There is some contraversy about whether LIMIT 0 should be the same as
202: # no limit at all or if LIMIT 0 should result in zero output rows.
203: #
204: do_test limit-6.1 {
205: execsql {
206: BEGIN;
207: CREATE TABLE t6(a);
208: INSERT INTO t6 VALUES(1);
209: INSERT INTO t6 VALUES(2);
210: INSERT INTO t6 SELECT a+2 FROM t6;
211: COMMIT;
212: SELECT * FROM t6;
213: }
214: } {1 2 3 4}
215: do_test limit-6.2 {
216: execsql {
217: SELECT * FROM t6 LIMIT -1 OFFSET -1;
218: }
219: } {1 2 3 4}
220: do_test limit-6.3 {
221: execsql {
222: SELECT * FROM t6 LIMIT 2 OFFSET -123;
223: }
224: } {1 2}
225: do_test limit-6.4 {
226: execsql {
227: SELECT * FROM t6 LIMIT -432 OFFSET 2;
228: }
229: } {3 4}
230: do_test limit-6.5 {
231: execsql {
232: SELECT * FROM t6 LIMIT -1
233: }
234: } {1 2 3 4}
235: do_test limit-6.6 {
236: execsql {
237: SELECT * FROM t6 LIMIT -1 OFFSET 1
238: }
239: } {2 3 4}
240: do_test limit-6.7 {
241: execsql {
242: SELECT * FROM t6 LIMIT 0
243: }
244: } {}
245: do_test limit-6.8 {
246: execsql {
247: SELECT * FROM t6 LIMIT 0 OFFSET 1
248: }
249: } {}
250:
251: # Make sure LIMIT works well with compound SELECT statements.
252: # Ticket #393
253: #
254: ifcapable compound {
255: do_test limit-7.1.1 {
256: catchsql {
257: SELECT x FROM t2 LIMIT 5 UNION ALL SELECT a FROM t6;
258: }
259: } {1 {LIMIT clause should come after UNION ALL not before}}
260: do_test limit-7.1.2 {
261: catchsql {
262: SELECT x FROM t2 LIMIT 5 UNION SELECT a FROM t6;
263: }
264: } {1 {LIMIT clause should come after UNION not before}}
265: do_test limit-7.1.3 {
266: catchsql {
267: SELECT x FROM t2 LIMIT 5 EXCEPT SELECT a FROM t6 LIMIT 3;
268: }
269: } {1 {LIMIT clause should come after EXCEPT not before}}
270: do_test limit-7.1.4 {
271: catchsql {
272: SELECT x FROM t2 LIMIT 0,5 INTERSECT SELECT a FROM t6;
273: }
274: } {1 {LIMIT clause should come after INTERSECT not before}}
275: do_test limit-7.2 {
276: execsql {
277: SELECT x FROM t2 UNION ALL SELECT a FROM t6 LIMIT 5;
278: }
279: } {31 30 1 2 3}
280: do_test limit-7.3 {
281: execsql {
282: SELECT x FROM t2 UNION ALL SELECT a FROM t6 LIMIT 3 OFFSET 1;
283: }
284: } {30 1 2}
285: do_test limit-7.4 {
286: execsql {
287: SELECT x FROM t2 UNION ALL SELECT a FROM t6 ORDER BY 1 LIMIT 3 OFFSET 1;
288: }
289: } {2 3 4}
290: do_test limit-7.5 {
291: execsql {
292: SELECT x FROM t2 UNION SELECT x+2 FROM t2 LIMIT 2 OFFSET 1;
293: }
294: } {31 32}
295: do_test limit-7.6 {
296: execsql {
297: SELECT x FROM t2 UNION SELECT x+2 FROM t2 ORDER BY 1 DESC LIMIT 2 OFFSET 1;
298: }
299: } {32 31}
300: do_test limit-7.7 {
301: execsql {
302: SELECT a+9 FROM t6 EXCEPT SELECT y FROM t2 LIMIT 2;
303: }
304: } {11 12}
305: do_test limit-7.8 {
306: execsql {
307: SELECT a+9 FROM t6 EXCEPT SELECT y FROM t2 ORDER BY 1 DESC LIMIT 2;
308: }
309: } {13 12}
310: do_test limit-7.9 {
311: execsql {
312: SELECT a+26 FROM t6 INTERSECT SELECT x FROM t2 LIMIT 1;
313: }
314: } {30}
315: do_test limit-7.10 {
316: execsql {
317: SELECT a+27 FROM t6 INTERSECT SELECT x FROM t2 LIMIT 1;
318: }
319: } {30}
320: do_test limit-7.11 {
321: execsql {
322: SELECT a+27 FROM t6 INTERSECT SELECT x FROM t2 LIMIT 1 OFFSET 1;
323: }
324: } {31}
325: do_test limit-7.12 {
326: execsql {
327: SELECT a+27 FROM t6 INTERSECT SELECT x FROM t2
328: ORDER BY 1 DESC LIMIT 1 OFFSET 1;
329: }
330: } {30}
331: } ;# ifcapable compound
332:
333: # Tests for limit in conjunction with distinct. The distinct should
334: # occur before both the limit and the offset. Ticket #749.
335: #
336: do_test limit-8.1 {
337: execsql {
338: SELECT DISTINCT cast(round(x/100) as integer) FROM t3 LIMIT 5;
339: }
340: } {0 1 2 3 4}
341: do_test limit-8.2 {
342: execsql {
343: SELECT DISTINCT cast(round(x/100) as integer) FROM t3 LIMIT 5 OFFSET 5;
344: }
345: } {5 6 7 8 9}
346: do_test limit-8.3 {
347: execsql {
348: SELECT DISTINCT cast(round(x/100) as integer) FROM t3 LIMIT 5 OFFSET 25;
349: }
350: } {25 26 27 28 29}
351:
352: # Make sure limits on multiple subqueries work correctly.
353: # Ticket #1035
354: #
355: ifcapable subquery {
356: do_test limit-9.1 {
357: execsql {
358: SELECT * FROM (SELECT * FROM t6 LIMIT 3);
359: }
360: } {1 2 3}
361: }
362: do_test limit-9.2.1 {
363: execsql {
364: CREATE TABLE t7 AS SELECT * FROM t6;
365: }
366: } {}
367: ifcapable subquery {
368: do_test limit-9.2.2 {
369: execsql {
370: SELECT * FROM (SELECT * FROM t7 LIMIT 3);
371: }
372: } {1 2 3}
373: }
374: ifcapable compound {
375: ifcapable subquery {
376: do_test limit-9.3 {
377: execsql {
378: SELECT * FROM (SELECT * FROM t6 LIMIT 3)
379: UNION
380: SELECT * FROM (SELECT * FROM t7 LIMIT 3)
381: ORDER BY 1
382: }
383: } {1 2 3}
384: do_test limit-9.4 {
385: execsql {
386: SELECT * FROM (SELECT * FROM t6 LIMIT 3)
387: UNION
388: SELECT * FROM (SELECT * FROM t7 LIMIT 3)
389: ORDER BY 1
390: LIMIT 2
391: }
392: } {1 2}
393: }
394: do_test limit-9.5 {
395: catchsql {
396: SELECT * FROM t6 LIMIT 3
397: UNION
398: SELECT * FROM t7 LIMIT 3
399: }
400: } {1 {LIMIT clause should come after UNION not before}}
401: }
402:
403: # Test LIMIT and OFFSET using SQL variables.
404: do_test limit-10.1 {
405: set limit 10
406: db eval {
407: SELECT x FROM t1 LIMIT :limit;
408: }
409: } {31 30 29 28 27 26 25 24 23 22}
410: do_test limit-10.2 {
411: set limit 5
412: set offset 5
413: db eval {
414: SELECT x FROM t1 LIMIT :limit OFFSET :offset;
415: }
416: } {26 25 24 23 22}
417: do_test limit-10.3 {
418: set limit -1
419: db eval {
420: SELECT x FROM t1 WHERE x<10 LIMIT :limit;
421: }
422: } {9 8 7 6 5 4 3 2 1 0}
423: do_test limit-10.4 {
424: set limit 1.5
425: set rc [catch {
426: db eval {
427: SELECT x FROM t1 WHERE x<10 LIMIT :limit;
428: } } msg]
429: list $rc $msg
430: } {1 {datatype mismatch}}
431: do_test limit-10.5 {
432: set limit "hello world"
433: set rc [catch {
434: db eval {
435: SELECT x FROM t1 WHERE x<10 LIMIT :limit;
436: } } msg]
437: list $rc $msg
438: } {1 {datatype mismatch}}
439:
440: ifcapable subquery {
441: do_test limit-11.1 {
442: db eval {
443: SELECT x FROM (SELECT x FROM t1 ORDER BY x LIMIT 0) ORDER BY x
444: }
445: } {}
446: } ;# ifcapable subquery
447:
448: # Test error processing.
449: #
450: do_test limit-12.1 {
451: catchsql {
452: SELECT * FROM t1 LIMIT replace(1)
453: }
454: } {1 {wrong number of arguments to function replace()}}
455: do_test limit-12.2 {
456: catchsql {
457: SELECT * FROM t1 LIMIT 5 OFFSET replace(1)
458: }
459: } {1 {wrong number of arguments to function replace()}}
460: do_test limit-12.3 {
461: catchsql {
462: SELECT * FROM t1 LIMIT x
463: }
464: } {1 {no such column: x}}
465: do_test limit-12.4 {
466: catchsql {
467: SELECT * FROM t1 LIMIT 1 OFFSET x
468: }
469: } {1 {no such column: x}}
470:
471:
472: finish_test
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>
![[BACK]](/icons/cvsweb/back.gif){kind=icon}
Return to limit.test CVS log ![[TXT]](/icons/cvsweb/text.gif){kind=icon}
![[DIR]](/icons/cvsweb/dir.gif){kind=icon}
Up to [ELWIX - Embedded LightWeight unIX -] / embedaddon / sqlite3 / test