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