wasCSharpSQLite – Blame information for rev 1

Subversion Repositories:
Rev:
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