wasCSharpSQLite – Blame information for rev 1

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