wasCSharpSQLite – Blame information for rev 7
?pathlinks?
Rev | Author | Line No. | Line |
---|---|---|---|
1 | office | 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.32 2008/08/02 03:50:39 drh 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 |