wasCSharpSQLite – Blame information for rev 7
?pathlinks?
Rev | Author | Line No. | Line |
---|---|---|---|
1 | office | 1 | # 2001 September 15 |
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 magic ROWID column that is |
||
13 | # found on all tables. |
||
14 | # |
||
15 | # $Id: rowid.test,v 1.21 2009/06/26 15:14:55 drh Exp $ |
||
16 | |||
17 | set testdir [file dirname $argv0] |
||
18 | source $testdir/tester.tcl |
||
19 | |||
20 | # Basic ROWID functionality tests. |
||
21 | # |
||
22 | do_test rowid-1.1 { |
||
23 | execsql { |
||
24 | CREATE TABLE t1(x int, y int); |
||
25 | INSERT INTO t1 VALUES(1,2); |
||
26 | INSERT INTO t1 VALUES(3,4); |
||
27 | SELECT x FROM t1 ORDER BY y; |
||
28 | } |
||
29 | } {1 3} |
||
30 | do_test rowid-1.2 { |
||
31 | set r [execsql {SELECT rowid FROM t1 ORDER BY x}] |
||
32 | global x2rowid rowid2x |
||
33 | set x2rowid(1) [lindex $r 0] |
||
34 | set x2rowid(3) [lindex $r 1] |
||
35 | set rowid2x($x2rowid(1)) 1 |
||
36 | set rowid2x($x2rowid(3)) 3 |
||
37 | llength $r |
||
38 | } {2} |
||
39 | do_test rowid-1.3 { |
||
40 | global x2rowid |
||
41 | set sql "SELECT x FROM t1 WHERE rowid==$x2rowid(1)" |
||
42 | execsql $sql |
||
43 | } {1} |
||
44 | do_test rowid-1.4 { |
||
45 | global x2rowid |
||
46 | set sql "SELECT x FROM t1 WHERE rowid==$x2rowid(3)" |
||
47 | execsql $sql |
||
48 | } {3} |
||
49 | do_test rowid-1.5 { |
||
50 | global x2rowid |
||
51 | set sql "SELECT x FROM t1 WHERE oid==$x2rowid(1)" |
||
52 | execsql $sql |
||
53 | } {1} |
||
54 | do_test rowid-1.6 { |
||
55 | global x2rowid |
||
56 | set sql "SELECT x FROM t1 WHERE OID==$x2rowid(3)" |
||
57 | execsql $sql |
||
58 | } {3} |
||
59 | do_test rowid-1.7 { |
||
60 | global x2rowid |
||
61 | set sql "SELECT x FROM t1 WHERE _rowid_==$x2rowid(1)" |
||
62 | execsql $sql |
||
63 | } {1} |
||
64 | do_test rowid-1.7.1 { |
||
65 | while 1 { |
||
66 | set norow [expr {int(rand()*1000000)}] |
||
67 | if {$norow!=$x2rowid(1) && $norow!=$x2rowid(3)} break |
||
68 | } |
||
69 | execsql "SELECT x FROM t1 WHERE rowid=$norow" |
||
70 | } {} |
||
71 | do_test rowid-1.8 { |
||
72 | global x2rowid |
||
73 | set v [execsql {SELECT x, oid FROM t1 order by x}] |
||
74 | set v2 [list 1 $x2rowid(1) 3 $x2rowid(3)] |
||
75 | expr {$v==$v2} |
||
76 | } {1} |
||
77 | do_test rowid-1.9 { |
||
78 | global x2rowid |
||
79 | set v [execsql {SELECT x, RowID FROM t1 order by x}] |
||
80 | set v2 [list 1 $x2rowid(1) 3 $x2rowid(3)] |
||
81 | expr {$v==$v2} |
||
82 | } {1} |
||
83 | do_test rowid-1.10 { |
||
84 | global x2rowid |
||
85 | set v [execsql {SELECT x, _rowid_ FROM t1 order by x}] |
||
86 | set v2 [list 1 $x2rowid(1) 3 $x2rowid(3)] |
||
87 | expr {$v==$v2} |
||
88 | } {1} |
||
89 | |||
90 | # We can insert or update the ROWID column. |
||
91 | # |
||
92 | do_test rowid-2.1 { |
||
93 | catchsql { |
||
94 | INSERT INTO t1(rowid,x,y) VALUES(1234,5,6); |
||
95 | SELECT rowid, * FROM t1; |
||
96 | } |
||
97 | } {0 {1 1 2 2 3 4 1234 5 6}} |
||
98 | do_test rowid-2.2 { |
||
99 | catchsql { |
||
100 | UPDATE t1 SET rowid=12345 WHERE x==1; |
||
101 | SELECT rowid, * FROM t1 |
||
102 | } |
||
103 | } {0 {2 3 4 1234 5 6 12345 1 2}} |
||
104 | do_test rowid-2.3 { |
||
105 | catchsql { |
||
106 | INSERT INTO t1(y,x,oid) VALUES(8,7,1235); |
||
107 | SELECT rowid, * FROM t1 WHERE rowid>1000; |
||
108 | } |
||
109 | } {0 {1234 5 6 1235 7 8 12345 1 2}} |
||
110 | do_test rowid-2.4 { |
||
111 | catchsql { |
||
112 | UPDATE t1 SET oid=12346 WHERE x==1; |
||
113 | SELECT rowid, * FROM t1; |
||
114 | } |
||
115 | } {0 {2 3 4 1234 5 6 1235 7 8 12346 1 2}} |
||
116 | do_test rowid-2.5 { |
||
117 | catchsql { |
||
118 | INSERT INTO t1(x,_rowid_,y) VALUES(9,1236,10); |
||
119 | SELECT rowid, * FROM t1 WHERE rowid>1000; |
||
120 | } |
||
121 | } {0 {1234 5 6 1235 7 8 1236 9 10 12346 1 2}} |
||
122 | do_test rowid-2.6 { |
||
123 | catchsql { |
||
124 | UPDATE t1 SET _rowid_=12347 WHERE x==1; |
||
125 | SELECT rowid, * FROM t1 WHERE rowid>1000; |
||
126 | } |
||
127 | } {0 {1234 5 6 1235 7 8 1236 9 10 12347 1 2}} |
||
128 | |||
129 | # But we can use ROWID in the WHERE clause of an UPDATE that does not |
||
130 | # change the ROWID. |
||
131 | # |
||
132 | do_test rowid-2.7 { |
||
133 | global x2rowid |
||
134 | set sql "UPDATE t1 SET x=2 WHERE OID==$x2rowid(3)" |
||
135 | execsql $sql |
||
136 | execsql {SELECT x FROM t1 ORDER BY x} |
||
137 | } {1 2 5 7 9} |
||
138 | do_test rowid-2.8 { |
||
139 | global x2rowid |
||
140 | set sql "UPDATE t1 SET x=3 WHERE _rowid_==$x2rowid(3)" |
||
141 | execsql $sql |
||
142 | execsql {SELECT x FROM t1 ORDER BY x} |
||
143 | } {1 3 5 7 9} |
||
144 | |||
145 | # We cannot index by ROWID |
||
146 | # |
||
147 | do_test rowid-2.9 { |
||
148 | set v [catch {execsql {CREATE INDEX idxt1 ON t1(rowid)}} msg] |
||
149 | lappend v $msg |
||
150 | } {1 {table t1 has no column named rowid}} |
||
151 | do_test rowid-2.10 { |
||
152 | set v [catch {execsql {CREATE INDEX idxt1 ON t1(_rowid_)}} msg] |
||
153 | lappend v $msg |
||
154 | } {1 {table t1 has no column named _rowid_}} |
||
155 | do_test rowid-2.11 { |
||
156 | set v [catch {execsql {CREATE INDEX idxt1 ON t1(oid)}} msg] |
||
157 | lappend v $msg |
||
158 | } {1 {table t1 has no column named oid}} |
||
159 | do_test rowid-2.12 { |
||
160 | set v [catch {execsql {CREATE INDEX idxt1 ON t1(x, rowid)}} msg] |
||
161 | lappend v $msg |
||
162 | } {1 {table t1 has no column named rowid}} |
||
163 | |||
164 | # Columns defined in the CREATE statement override the buildin ROWID |
||
165 | # column names. |
||
166 | # |
||
167 | do_test rowid-3.1 { |
||
168 | execsql { |
||
169 | CREATE TABLE t2(rowid int, x int, y int); |
||
170 | INSERT INTO t2 VALUES(0,2,3); |
||
171 | INSERT INTO t2 VALUES(4,5,6); |
||
172 | INSERT INTO t2 VALUES(7,8,9); |
||
173 | SELECT * FROM t2 ORDER BY x; |
||
174 | } |
||
175 | } {0 2 3 4 5 6 7 8 9} |
||
176 | do_test rowid-3.2 { |
||
177 | execsql {SELECT * FROM t2 ORDER BY rowid} |
||
178 | } {0 2 3 4 5 6 7 8 9} |
||
179 | do_test rowid-3.3 { |
||
180 | execsql {SELECT rowid, x, y FROM t2 ORDER BY rowid} |
||
181 | } {0 2 3 4 5 6 7 8 9} |
||
182 | do_test rowid-3.4 { |
||
183 | set r1 [execsql {SELECT _rowid_, rowid FROM t2 ORDER BY rowid}] |
||
184 | foreach {a b c d e f} $r1 {} |
||
185 | set r2 [execsql {SELECT _rowid_, rowid FROM t2 ORDER BY x DESC}] |
||
186 | foreach {u v w x y z} $r2 {} |
||
187 | expr {$u==$e && $w==$c && $y==$a} |
||
188 | } {1} |
||
189 | # sqlite3 v3 - do_probtest doesn't exist anymore? |
||
190 | if 0 { |
||
191 | do_probtest rowid-3.5 { |
||
192 | set r1 [execsql {SELECT _rowid_, rowid FROM t2 ORDER BY rowid}] |
||
193 | foreach {a b c d e f} $r1 {} |
||
194 | expr {$a!=$b && $c!=$d && $e!=$f} |
||
195 | } {1} |
||
196 | } |
||
197 | |||
198 | # Let's try some more complex examples, including some joins. |
||
199 | # |
||
200 | do_test rowid-4.1 { |
||
201 | execsql { |
||
202 | DELETE FROM t1; |
||
203 | DELETE FROM t2; |
||
204 | } |
||
205 | for {set i 1} {$i<=50} {incr i} { |
||
206 | execsql "INSERT INTO t1(x,y) VALUES($i,[expr {$i*$i}])" |
||
207 | } |
||
208 | execsql {INSERT INTO t2 SELECT _rowid_, x*y, y*y FROM t1} |
||
209 | execsql {SELECT t2.y FROM t1, t2 WHERE t1.x==4 AND t1.rowid==t2.rowid} |
||
210 | } {256} |
||
211 | do_test rowid-4.2 { |
||
212 | execsql {SELECT t2.y FROM t2, t1 WHERE t1.x==4 AND t1.rowid==t2.rowid} |
||
213 | } {256} |
||
214 | do_test rowid-4.2.1 { |
||
215 | execsql {SELECT t2.y FROM t2, t1 WHERE t1.x==4 AND t1.oid==t2.rowid} |
||
216 | } {256} |
||
217 | do_test rowid-4.2.2 { |
||
218 | execsql {SELECT t2.y FROM t2, t1 WHERE t1.x==4 AND t1._rowid_==t2.rowid} |
||
219 | } {256} |
||
220 | do_test rowid-4.2.3 { |
||
221 | execsql {SELECT t2.y FROM t2, t1 WHERE t1.x==4 AND t2.rowid==t1.rowid} |
||
222 | } {256} |
||
223 | do_test rowid-4.2.4 { |
||
224 | execsql {SELECT t2.y FROM t2, t1 WHERE t2.rowid==t1.oid AND t1.x==4} |
||
225 | } {256} |
||
226 | do_test rowid-4.2.5 { |
||
227 | execsql {SELECT t2.y FROM t1, t2 WHERE t1.x==4 AND t1._rowid_==t2.rowid} |
||
228 | } {256} |
||
229 | do_test rowid-4.2.6 { |
||
230 | execsql {SELECT t2.y FROM t1, t2 WHERE t1.x==4 AND t2.rowid==t1.rowid} |
||
231 | } {256} |
||
232 | do_test rowid-4.2.7 { |
||
233 | execsql {SELECT t2.y FROM t1, t2 WHERE t2.rowid==t1.oid AND t1.x==4} |
||
234 | } {256} |
||
235 | do_test rowid-4.3 { |
||
236 | execsql {CREATE INDEX idxt1 ON t1(x)} |
||
237 | execsql {SELECT t2.y FROM t1, t2 WHERE t1.x==4 AND t1.rowid==t2.rowid} |
||
238 | } {256} |
||
239 | do_test rowid-4.3.1 { |
||
240 | execsql {SELECT t2.y FROM t1, t2 WHERE t1.x==4 AND t1._rowid_==t2.rowid} |
||
241 | } {256} |
||
242 | do_test rowid-4.3.2 { |
||
243 | execsql {SELECT t2.y FROM t1, t2 WHERE t2.rowid==t1.oid AND 4==t1.x} |
||
244 | } {256} |
||
245 | do_test rowid-4.4 { |
||
246 | execsql {SELECT t2.y FROM t2, t1 WHERE t1.x==4 AND t1.rowid==t2.rowid} |
||
247 | } {256} |
||
248 | do_test rowid-4.4.1 { |
||
249 | execsql {SELECT t2.y FROM t2, t1 WHERE t1.x==4 AND t1._rowid_==t2.rowid} |
||
250 | } {256} |
||
251 | do_test rowid-4.4.2 { |
||
252 | execsql {SELECT t2.y FROM t2, t1 WHERE t2.rowid==t1.oid AND 4==t1.x} |
||
253 | } {256} |
||
254 | do_test rowid-4.5 { |
||
255 | execsql {CREATE INDEX idxt2 ON t2(y)} |
||
256 | set sqlite_search_count 0 |
||
257 | concat [execsql { |
||
258 | SELECT t1.x FROM t2, t1 |
||
259 | WHERE t2.y==256 AND t1.rowid==t2.rowid |
||
260 | }] $sqlite_search_count |
||
261 | } {4 3} |
||
262 | do_test rowid-4.5.1 { |
||
263 | set sqlite_search_count 0 |
||
264 | concat [execsql { |
||
265 | SELECT t1.x FROM t2, t1 |
||
266 | WHERE t1.OID==t2.rowid AND t2.y==81 |
||
267 | }] $sqlite_search_count |
||
268 | } {3 3} |
||
269 | do_test rowid-4.6 { |
||
270 | execsql { |
||
271 | SELECT t1.x FROM t1, t2 |
||
272 | WHERE t2.y==256 AND t1.rowid==t2.rowid |
||
273 | } |
||
274 | } {4} |
||
275 | |||
276 | do_test rowid-5.1.1 { |
||
277 | ifcapable subquery { |
||
278 | execsql {DELETE FROM t1 WHERE _rowid_ IN (SELECT oid FROM t1 WHERE x>8)} |
||
279 | } else { |
||
280 | set oids [execsql {SELECT oid FROM t1 WHERE x>8}] |
||
281 | set where "_rowid_ = [join $oids { OR _rowid_ = }]" |
||
282 | execsql "DELETE FROM t1 WHERE $where" |
||
283 | } |
||
284 | } {} |
||
285 | do_test rowid-5.1.2 { |
||
286 | execsql {SELECT max(x) FROM t1} |
||
287 | } {8} |
||
288 | |||
289 | # Make sure a "WHERE rowid=X" clause works when there is no ROWID of X. |
||
290 | # |
||
291 | do_test rowid-6.1 { |
||
292 | execsql { |
||
293 | SELECT x FROM t1 |
||
294 | } |
||
295 | } {1 2 3 4 5 6 7 8} |
||
296 | do_test rowid-6.2 { |
||
297 | for {set ::norow 1} {1} {incr ::norow} { |
||
298 | if {[execsql "SELECT x FROM t1 WHERE rowid=$::norow"]==""} break |
||
299 | } |
||
300 | execsql [subst { |
||
301 | DELETE FROM t1 WHERE rowid=$::norow |
||
302 | }] |
||
303 | } {} |
||
304 | do_test rowid-6.3 { |
||
305 | execsql { |
||
306 | SELECT x FROM t1 |
||
307 | } |
||
308 | } {1 2 3 4 5 6 7 8} |
||
309 | |||
310 | # Beginning with version 2.3.4, SQLite computes rowids of new rows by |
||
311 | # finding the maximum current rowid and adding one. It falls back to |
||
312 | # the old random algorithm if the maximum rowid is the largest integer. |
||
313 | # The following tests are for this new behavior. |
||
314 | # |
||
315 | do_test rowid-7.0 { |
||
316 | execsql { |
||
317 | DELETE FROM t1; |
||
318 | DROP TABLE t2; |
||
319 | DROP INDEX idxt1; |
||
320 | INSERT INTO t1 VALUES(1,2); |
||
321 | SELECT rowid, * FROM t1; |
||
322 | } |
||
323 | } {1 1 2} |
||
324 | do_test rowid-7.1 { |
||
325 | execsql { |
||
326 | INSERT INTO t1 VALUES(99,100); |
||
327 | SELECT rowid,* FROM t1 |
||
328 | } |
||
329 | } {1 1 2 2 99 100} |
||
330 | do_test rowid-7.2 { |
||
331 | execsql { |
||
332 | CREATE TABLE t2(a INTEGER PRIMARY KEY, b); |
||
333 | INSERT INTO t2(b) VALUES(55); |
||
334 | SELECT * FROM t2; |
||
335 | } |
||
336 | } {1 55} |
||
337 | do_test rowid-7.3 { |
||
338 | execsql { |
||
339 | INSERT INTO t2(b) VALUES(66); |
||
340 | SELECT * FROM t2; |
||
341 | } |
||
342 | } {1 55 2 66} |
||
343 | do_test rowid-7.4 { |
||
344 | execsql { |
||
345 | INSERT INTO t2(a,b) VALUES(1000000,77); |
||
346 | INSERT INTO t2(b) VALUES(88); |
||
347 | SELECT * FROM t2; |
||
348 | } |
||
349 | } {1 55 2 66 1000000 77 1000001 88} |
||
350 | do_test rowid-7.5 { |
||
351 | execsql { |
||
352 | INSERT INTO t2(a,b) VALUES(2147483647,99); |
||
353 | INSERT INTO t2(b) VALUES(11); |
||
354 | SELECT b FROM t2 ORDER BY b; |
||
355 | } |
||
356 | } {11 55 66 77 88 99} |
||
357 | ifcapable subquery { |
||
358 | do_test rowid-7.6 { |
||
359 | execsql { |
||
360 | SELECT b FROM t2 WHERE a NOT IN(1,2,1000000,1000001,2147483647); |
||
361 | } |
||
362 | } {11} |
||
363 | do_test rowid-7.7 { |
||
364 | execsql { |
||
365 | INSERT INTO t2(b) VALUES(22); |
||
366 | INSERT INTO t2(b) VALUES(33); |
||
367 | INSERT INTO t2(b) VALUES(44); |
||
368 | INSERT INTO t2(b) VALUES(55); |
||
369 | SELECT b FROM t2 WHERE a NOT IN(1,2,1000000,1000001,2147483647) |
||
370 | ORDER BY b; |
||
371 | } |
||
372 | } {11 22 33 44 55} |
||
373 | } |
||
374 | do_test rowid-7.8 { |
||
375 | execsql { |
||
376 | DELETE FROM t2 WHERE a!=2; |
||
377 | INSERT INTO t2(b) VALUES(111); |
||
378 | SELECT * FROM t2; |
||
379 | } |
||
380 | } {2 66 3 111} |
||
381 | |||
382 | ifcapable {trigger} { |
||
383 | # Make sure AFTER triggers that do INSERTs do not change the last_insert_rowid. |
||
384 | # Ticket #290 |
||
385 | # |
||
386 | do_test rowid-8.1 { |
||
387 | execsql { |
||
388 | CREATE TABLE t3(a integer primary key); |
||
389 | CREATE TABLE t4(x); |
||
390 | INSERT INTO t4 VALUES(1); |
||
391 | CREATE TRIGGER r3 AFTER INSERT on t3 FOR EACH ROW BEGIN |
||
392 | INSERT INTO t4 VALUES(NEW.a+10); |
||
393 | END; |
||
394 | SELECT * FROM t3; |
||
395 | } |
||
396 | } {} |
||
397 | do_test rowid-8.2 { |
||
398 | execsql { |
||
399 | SELECT rowid, * FROM t4; |
||
400 | } |
||
401 | } {1 1} |
||
402 | do_test rowid-8.3 { |
||
403 | execsql { |
||
404 | INSERT INTO t3 VALUES(123); |
||
405 | SELECT last_insert_rowid(); |
||
406 | } |
||
407 | } {123} |
||
408 | do_test rowid-8.4 { |
||
409 | execsql { |
||
410 | SELECT * FROM t3; |
||
411 | } |
||
412 | } {123} |
||
413 | do_test rowid-8.5 { |
||
414 | execsql { |
||
415 | SELECT rowid, * FROM t4; |
||
416 | } |
||
417 | } {1 1 2 133} |
||
418 | do_test rowid-8.6 { |
||
419 | execsql { |
||
420 | INSERT INTO t3 VALUES(NULL); |
||
421 | SELECT last_insert_rowid(); |
||
422 | } |
||
423 | } {124} |
||
424 | do_test rowid-8.7 { |
||
425 | execsql { |
||
426 | SELECT * FROM t3; |
||
427 | } |
||
428 | } {123 124} |
||
429 | do_test rowid-8.8 { |
||
430 | execsql { |
||
431 | SELECT rowid, * FROM t4; |
||
432 | } |
||
433 | } {1 1 2 133 3 134} |
||
434 | } ;# endif trigger |
||
435 | |||
436 | # If triggers are not enable, simulate their effect for the tests that |
||
437 | # follow. |
||
438 | ifcapable {!trigger} { |
||
439 | execsql { |
||
440 | CREATE TABLE t3(a integer primary key); |
||
441 | INSERT INTO t3 VALUES(123); |
||
442 | INSERT INTO t3 VALUES(124); |
||
443 | } |
||
444 | } |
||
445 | |||
446 | # ticket #377: Comparison between integer primiary key and floating point |
||
447 | # values. |
||
448 | # |
||
449 | do_test rowid-9.1 { |
||
450 | execsql { |
||
451 | SELECT * FROM t3 WHERE a<123.5 |
||
452 | } |
||
453 | } {123} |
||
454 | do_test rowid-9.2 { |
||
455 | execsql { |
||
456 | SELECT * FROM t3 WHERE a<124.5 |
||
457 | } |
||
458 | } {123 124} |
||
459 | do_test rowid-9.3 { |
||
460 | execsql { |
||
461 | SELECT * FROM t3 WHERE a>123.5 |
||
462 | } |
||
463 | } {124} |
||
464 | do_test rowid-9.4 { |
||
465 | execsql { |
||
466 | SELECT * FROM t3 WHERE a>122.5 |
||
467 | } |
||
468 | } {123 124} |
||
469 | do_test rowid-9.5 { |
||
470 | execsql { |
||
471 | SELECT * FROM t3 WHERE a==123.5 |
||
472 | } |
||
473 | } {} |
||
474 | do_test rowid-9.6 { |
||
475 | execsql { |
||
476 | SELECT * FROM t3 WHERE a==123.000 |
||
477 | } |
||
478 | } {123} |
||
479 | do_test rowid-9.7 { |
||
480 | execsql { |
||
481 | SELECT * FROM t3 WHERE a>100.5 AND a<200.5 |
||
482 | } |
||
483 | } {123 124} |
||
484 | do_test rowid-9.8 { |
||
485 | execsql { |
||
486 | SELECT * FROM t3 WHERE a>'xyz'; |
||
487 | } |
||
488 | } {} |
||
489 | do_test rowid-9.9 { |
||
490 | execsql { |
||
491 | SELECT * FROM t3 WHERE a<'xyz'; |
||
492 | } |
||
493 | } {123 124} |
||
494 | do_test rowid-9.10 { |
||
495 | execsql { |
||
496 | SELECT * FROM t3 WHERE a>=122.9 AND a<=123.1 |
||
497 | } |
||
498 | } {123} |
||
499 | |||
500 | # Ticket #567. Comparisons of ROWID or integery primary key against |
||
501 | # floating point numbers still do not always work. |
||
502 | # |
||
503 | do_test rowid-10.1 { |
||
504 | execsql { |
||
505 | CREATE TABLE t5(a); |
||
506 | INSERT INTO t5 VALUES(1); |
||
507 | INSERT INTO t5 VALUES(2); |
||
508 | INSERT INTO t5 SELECT a+2 FROM t5; |
||
509 | INSERT INTO t5 SELECT a+4 FROM t5; |
||
510 | SELECT rowid, * FROM t5; |
||
511 | } |
||
512 | } {1 1 2 2 3 3 4 4 5 5 6 6 7 7 8 8} |
||
513 | do_test rowid-10.2 { |
||
514 | execsql {SELECT rowid, a FROM t5 WHERE rowid>=5.5} |
||
515 | } {6 6 7 7 8 8} |
||
516 | do_test rowid-10.3 { |
||
517 | execsql {SELECT rowid, a FROM t5 WHERE rowid>=5.0} |
||
518 | } {5 5 6 6 7 7 8 8} |
||
519 | do_test rowid-10.4 { |
||
520 | execsql {SELECT rowid, a FROM t5 WHERE rowid>5.5} |
||
521 | } {6 6 7 7 8 8} |
||
522 | do_test rowid-10.3.2 { |
||
523 | execsql {SELECT rowid, a FROM t5 WHERE rowid>5.0} |
||
524 | } {6 6 7 7 8 8} |
||
525 | do_test rowid-10.5 { |
||
526 | execsql {SELECT rowid, a FROM t5 WHERE 5.5<=rowid} |
||
527 | } {6 6 7 7 8 8} |
||
528 | do_test rowid-10.6 { |
||
529 | execsql {SELECT rowid, a FROM t5 WHERE 5.5<rowid} |
||
530 | } {6 6 7 7 8 8} |
||
531 | do_test rowid-10.7 { |
||
532 | execsql {SELECT rowid, a FROM t5 WHERE rowid<=5.5} |
||
533 | } {1 1 2 2 3 3 4 4 5 5} |
||
534 | do_test rowid-10.8 { |
||
535 | execsql {SELECT rowid, a FROM t5 WHERE rowid<5.5} |
||
536 | } {1 1 2 2 3 3 4 4 5 5} |
||
537 | do_test rowid-10.9 { |
||
538 | execsql {SELECT rowid, a FROM t5 WHERE 5.5>=rowid} |
||
539 | } {1 1 2 2 3 3 4 4 5 5} |
||
540 | do_test rowid-10.10 { |
||
541 | execsql {SELECT rowid, a FROM t5 WHERE 5.5>rowid} |
||
542 | } {1 1 2 2 3 3 4 4 5 5} |
||
543 | do_test rowid-10.11 { |
||
544 | execsql {SELECT rowid, a FROM t5 WHERE rowid>=5.5 ORDER BY rowid DESC} |
||
545 | } {8 8 7 7 6 6} |
||
546 | do_test rowid-10.11.2 { |
||
547 | execsql {SELECT rowid, a FROM t5 WHERE rowid>=5.0 ORDER BY rowid DESC} |
||
548 | } {8 8 7 7 6 6 5 5} |
||
549 | do_test rowid-10.12 { |
||
550 | execsql {SELECT rowid, a FROM t5 WHERE rowid>5.5 ORDER BY rowid DESC} |
||
551 | } {8 8 7 7 6 6} |
||
552 | do_test rowid-10.12.2 { |
||
553 | execsql {SELECT rowid, a FROM t5 WHERE rowid>5.0 ORDER BY rowid DESC} |
||
554 | } {8 8 7 7 6 6} |
||
555 | do_test rowid-10.13 { |
||
556 | execsql {SELECT rowid, a FROM t5 WHERE 5.5<=rowid ORDER BY rowid DESC} |
||
557 | } {8 8 7 7 6 6} |
||
558 | do_test rowid-10.14 { |
||
559 | execsql {SELECT rowid, a FROM t5 WHERE 5.5<rowid ORDER BY rowid DESC} |
||
560 | } {8 8 7 7 6 6} |
||
561 | do_test rowid-10.15 { |
||
562 | execsql {SELECT rowid, a FROM t5 WHERE rowid<=5.5 ORDER BY rowid DESC} |
||
563 | } {5 5 4 4 3 3 2 2 1 1} |
||
564 | do_test rowid-10.16 { |
||
565 | execsql {SELECT rowid, a FROM t5 WHERE rowid<5.5 ORDER BY rowid DESC} |
||
566 | } {5 5 4 4 3 3 2 2 1 1} |
||
567 | do_test rowid-10.17 { |
||
568 | execsql {SELECT rowid, a FROM t5 WHERE 5.5>=rowid ORDER BY rowid DESC} |
||
569 | } {5 5 4 4 3 3 2 2 1 1} |
||
570 | do_test rowid-10.18 { |
||
571 | execsql {SELECT rowid, a FROM t5 WHERE 5.5>rowid ORDER BY rowid DESC} |
||
572 | } {5 5 4 4 3 3 2 2 1 1} |
||
573 | |||
574 | do_test rowid-10.30 { |
||
575 | execsql { |
||
576 | CREATE TABLE t6(a); |
||
577 | INSERT INTO t6(rowid,a) SELECT -a,a FROM t5; |
||
578 | SELECT rowid, * FROM t6; |
||
579 | } |
||
580 | } {-8 8 -7 7 -6 6 -5 5 -4 4 -3 3 -2 2 -1 1} |
||
581 | do_test rowid-10.31.1 { |
||
582 | execsql {SELECT rowid, a FROM t6 WHERE rowid>=-5.5} |
||
583 | } {-5 5 -4 4 -3 3 -2 2 -1 1} |
||
584 | do_test rowid-10.31.2 { |
||
585 | execsql {SELECT rowid, a FROM t6 WHERE rowid>=-5.0} |
||
586 | } {-5 5 -4 4 -3 3 -2 2 -1 1} |
||
587 | do_test rowid-10.32.1 { |
||
588 | execsql {SELECT rowid, a FROM t6 WHERE rowid>=-5.5 ORDER BY rowid DESC} |
||
589 | } {-1 1 -2 2 -3 3 -4 4 -5 5} |
||
590 | do_test rowid-10.32.1 { |
||
591 | execsql {SELECT rowid, a FROM t6 WHERE rowid>=-5.0 ORDER BY rowid DESC} |
||
592 | } {-1 1 -2 2 -3 3 -4 4 -5 5} |
||
593 | do_test rowid-10.33 { |
||
594 | execsql {SELECT rowid, a FROM t6 WHERE -5.5<=rowid} |
||
595 | } {-5 5 -4 4 -3 3 -2 2 -1 1} |
||
596 | do_test rowid-10.34 { |
||
597 | execsql {SELECT rowid, a FROM t6 WHERE -5.5<=rowid ORDER BY rowid DESC} |
||
598 | } {-1 1 -2 2 -3 3 -4 4 -5 5} |
||
599 | do_test rowid-10.35.1 { |
||
600 | execsql {SELECT rowid, a FROM t6 WHERE rowid>-5.5} |
||
601 | } {-5 5 -4 4 -3 3 -2 2 -1 1} |
||
602 | do_test rowid-10.35.2 { |
||
603 | execsql {SELECT rowid, a FROM t6 WHERE rowid>-5.0} |
||
604 | } {-4 4 -3 3 -2 2 -1 1} |
||
605 | do_test rowid-10.36.1 { |
||
606 | execsql {SELECT rowid, a FROM t6 WHERE rowid>-5.5 ORDER BY rowid DESC} |
||
607 | } {-1 1 -2 2 -3 3 -4 4 -5 5} |
||
608 | do_test rowid-10.36.2 { |
||
609 | execsql {SELECT rowid, a FROM t6 WHERE rowid>-5.0 ORDER BY rowid DESC} |
||
610 | } {-1 1 -2 2 -3 3 -4 4} |
||
611 | do_test rowid-10.37 { |
||
612 | execsql {SELECT rowid, a FROM t6 WHERE -5.5<rowid} |
||
613 | } {-5 5 -4 4 -3 3 -2 2 -1 1} |
||
614 | do_test rowid-10.38 { |
||
615 | execsql {SELECT rowid, a FROM t6 WHERE -5.5<rowid ORDER BY rowid DESC} |
||
616 | } {-1 1 -2 2 -3 3 -4 4 -5 5} |
||
617 | do_test rowid-10.39 { |
||
618 | execsql {SELECT rowid, a FROM t6 WHERE rowid<=-5.5} |
||
619 | } {-8 8 -7 7 -6 6} |
||
620 | do_test rowid-10.40 { |
||
621 | execsql {SELECT rowid, a FROM t6 WHERE rowid<=-5.5 ORDER BY rowid DESC} |
||
622 | } {-6 6 -7 7 -8 8} |
||
623 | do_test rowid-10.41 { |
||
624 | execsql {SELECT rowid, a FROM t6 WHERE -5.5>=rowid} |
||
625 | } {-8 8 -7 7 -6 6} |
||
626 | do_test rowid-10.42 { |
||
627 | execsql {SELECT rowid, a FROM t6 WHERE -5.5>=rowid ORDER BY rowid DESC} |
||
628 | } {-6 6 -7 7 -8 8} |
||
629 | do_test rowid-10.43 { |
||
630 | execsql {SELECT rowid, a FROM t6 WHERE rowid<-5.5} |
||
631 | } {-8 8 -7 7 -6 6} |
||
632 | do_test rowid-10.44 { |
||
633 | execsql {SELECT rowid, a FROM t6 WHERE rowid<-5.5 ORDER BY rowid DESC} |
||
634 | } {-6 6 -7 7 -8 8} |
||
635 | do_test rowid-10.44 { |
||
636 | execsql {SELECT rowid, a FROM t6 WHERE -5.5>rowid} |
||
637 | } {-8 8 -7 7 -6 6} |
||
638 | do_test rowid-10.46 { |
||
639 | execsql {SELECT rowid, a FROM t6 WHERE -5.5>rowid ORDER BY rowid DESC} |
||
640 | } {-6 6 -7 7 -8 8} |
||
641 | |||
642 | # Comparison of rowid against string values. |
||
643 | # |
||
644 | do_test rowid-11.1 { |
||
645 | execsql {SELECT rowid, a FROM t5 WHERE rowid>'abc'} |
||
646 | } {} |
||
647 | do_test rowid-11.2 { |
||
648 | execsql {SELECT rowid, a FROM t5 WHERE rowid>='abc'} |
||
649 | } {} |
||
650 | do_test rowid-11.3 { |
||
651 | execsql {SELECT rowid, a FROM t5 WHERE rowid<'abc'} |
||
652 | } {1 1 2 2 3 3 4 4 5 5 6 6 7 7 8 8} |
||
653 | do_test rowid-11.4 { |
||
654 | execsql {SELECT rowid, a FROM t5 WHERE rowid<='abc'} |
||
655 | } {1 1 2 2 3 3 4 4 5 5 6 6 7 7 8 8} |
||
656 | |||
657 | # Test the automatic generation of rowids when the table already contains |
||
658 | # a rowid with the maximum value. |
||
659 | # |
||
660 | # Once the the maximum rowid is taken, rowids are normally chosen at |
||
661 | # random. By by reseting the random number generator, we can cause |
||
662 | # the rowid guessing loop to collide with prior rowids, and test the |
||
663 | # loop out to its limit of 100 iterations. After 100 collisions, the |
||
664 | # rowid guesser gives up and reports SQLITE_FULL. |
||
665 | # |
||
666 | do_test rowid-12.1 { |
||
667 | execsql { |
||
668 | CREATE TABLE t7(x INTEGER PRIMARY KEY, y); |
||
669 | CREATE TABLE t7temp(a INTEGER PRIMARY KEY); |
||
670 | INSERT INTO t7 VALUES(9223372036854775807,'a'); |
||
671 | SELECT y FROM t7; |
||
672 | } |
||
673 | } {a} |
||
674 | do_test rowid-12.2 { |
||
675 | db close |
||
676 | sqlite3 db test.db |
||
677 | save_prng_state |
||
678 | execsql { |
||
679 | INSERT INTO t7 VALUES(NULL,'b'); |
||
680 | SELECT x, y FROM t7; |
||
681 | } |
||
682 | } {1 b 9223372036854775807 a} |
||
683 | execsql {INSERT INTO t7 VALUES(2,'y');} |
||
684 | for {set i 1} {$i<100} {incr i} { |
||
685 | do_test rowid-12.3.$i { |
||
686 | db eval {DELETE FROM t7temp; INSERT INTO t7temp VALUES(1);} |
||
687 | restore_prng_state |
||
688 | execsql { |
||
689 | INSERT INTO t7 VALUES(NULL,'x'); |
||
690 | SELECT count(*) FROM t7 WHERE y=='x'; |
||
691 | } |
||
692 | } $i |
||
693 | } |
||
694 | do_test rowid-12.4 { |
||
695 | db eval {DELETE FROM t7temp; INSERT INTO t7temp VALUES(1);} |
||
696 | restore_prng_state |
||
697 | catchsql { |
||
698 | INSERT INTO t7 VALUES(NULL,'x'); |
||
699 | } |
||
700 | } {1 {database or disk is full}} |
||
701 | |||
702 | |||
703 | finish_test |