000001 # 2001 September 15
000002 #
000003 # The author disclaims copyright to this source code. In place of
000004 # a legal notice, here is a blessing:
000005 #
000006 # May you do good and not evil.
000007 # May you find forgiveness for yourself and forgive others.
000008 # May you share freely, never taking more than you give.
000009 #
000010 #***********************************************************************
000011 # This file implements regression tests for SQLite library. The
000012 # focus of this file is testing the magic ROWID column that is
000013 # found on all tables.
000014 #
000015 # EVIDENCE-OF: R-36924-43758 By default, every row in SQLite has a
000016 # special column, usually called the "rowid", that uniquely identifies
000017 # that row within the table.
000018
000019 set testdir [file dirname $argv0]
000020 source $testdir/tester.tcl
000021
000022 # Basic ROWID functionality tests.
000023 #
000024 do_test rowid-1.1 {
000025 execsql {
000026 CREATE TABLE t1(x int, y int);
000027 INSERT INTO t1 VALUES(1,2);
000028 INSERT INTO t1 VALUES(3,4);
000029 SELECT x FROM t1 ORDER BY y;
000030 }
000031 } {1 3}
000032 do_test rowid-1.2 {
000033 set r [execsql {SELECT rowid FROM t1 ORDER BY x}]
000034 global x2rowid rowid2x
000035 set x2rowid(1) [lindex $r 0]
000036 set x2rowid(3) [lindex $r 1]
000037 set rowid2x($x2rowid(1)) 1
000038 set rowid2x($x2rowid(3)) 3
000039 llength $r
000040 } {2}
000041 do_test rowid-1.3 {
000042 global x2rowid
000043 set sql "SELECT x FROM t1 WHERE rowid==$x2rowid(1)"
000044 execsql $sql
000045 } {1}
000046 do_test rowid-1.4 {
000047 global x2rowid
000048 set sql "SELECT x FROM t1 WHERE rowid==$x2rowid(3)"
000049 execsql $sql
000050 } {3}
000051 do_test rowid-1.5 {
000052 global x2rowid
000053 set sql "SELECT x FROM t1 WHERE oid==$x2rowid(1)"
000054 execsql $sql
000055 } {1}
000056 do_test rowid-1.6 {
000057 global x2rowid
000058 set sql "SELECT x FROM t1 WHERE OID==$x2rowid(3)"
000059 execsql $sql
000060 } {3}
000061 do_test rowid-1.7 {
000062 global x2rowid
000063 set sql "SELECT x FROM t1 WHERE _rowid_==$x2rowid(1)"
000064 execsql $sql
000065 } {1}
000066 do_test rowid-1.7.1 {
000067 while 1 {
000068 set norow [expr {int(rand()*1000000)}]
000069 if {$norow!=$x2rowid(1) && $norow!=$x2rowid(3)} break
000070 }
000071 execsql "SELECT x FROM t1 WHERE rowid=$norow"
000072 } {}
000073 do_test rowid-1.8 {
000074 global x2rowid
000075 set v [execsql {SELECT x, oid FROM t1 order by x}]
000076 set v2 [list 1 $x2rowid(1) 3 $x2rowid(3)]
000077 expr {$v==$v2}
000078 } {1}
000079 do_test rowid-1.9 {
000080 global x2rowid
000081 set v [execsql {SELECT x, RowID FROM t1 order by x}]
000082 set v2 [list 1 $x2rowid(1) 3 $x2rowid(3)]
000083 expr {$v==$v2}
000084 } {1}
000085 do_test rowid-1.10 {
000086 global x2rowid
000087 set v [execsql {SELECT x, _rowid_ FROM t1 order by x}]
000088 set v2 [list 1 $x2rowid(1) 3 $x2rowid(3)]
000089 expr {$v==$v2}
000090 } {1}
000091
000092 # We can insert or update the ROWID column.
000093 #
000094 do_test rowid-2.1 {
000095 catchsql {
000096 INSERT INTO t1(rowid,x,y) VALUES(1234,5,6);
000097 SELECT rowid, * FROM t1;
000098 }
000099 } {0 {1 1 2 2 3 4 1234 5 6}}
000100 do_test rowid-2.2 {
000101 catchsql {
000102 UPDATE t1 SET rowid=12345 WHERE x==1;
000103 SELECT rowid, * FROM t1
000104 }
000105 } {0 {2 3 4 1234 5 6 12345 1 2}}
000106 do_test rowid-2.3 {
000107 catchsql {
000108 INSERT INTO t1(y,x,oid) VALUES(8,7,1235);
000109 SELECT rowid, * FROM t1 WHERE rowid>1000;
000110 }
000111 } {0 {1234 5 6 1235 7 8 12345 1 2}}
000112 do_test rowid-2.4 {
000113 catchsql {
000114 UPDATE t1 SET oid=12346 WHERE x==1;
000115 SELECT rowid, * FROM t1;
000116 }
000117 } {0 {2 3 4 1234 5 6 1235 7 8 12346 1 2}}
000118 do_test rowid-2.5 {
000119 catchsql {
000120 INSERT INTO t1(x,_rowid_,y) VALUES(9,1236,10);
000121 SELECT rowid, * FROM t1 WHERE rowid>1000;
000122 }
000123 } {0 {1234 5 6 1235 7 8 1236 9 10 12346 1 2}}
000124 do_test rowid-2.6 {
000125 catchsql {
000126 UPDATE t1 SET _rowid_=12347 WHERE x==1;
000127 SELECT rowid, * FROM t1 WHERE rowid>1000;
000128 }
000129 } {0 {1234 5 6 1235 7 8 1236 9 10 12347 1 2}}
000130
000131 # But we can use ROWID in the WHERE clause of an UPDATE that does not
000132 # change the ROWID.
000133 #
000134 do_test rowid-2.7 {
000135 global x2rowid
000136 set sql "UPDATE t1 SET x=2 WHERE OID==$x2rowid(3)"
000137 execsql $sql
000138 execsql {SELECT x FROM t1 ORDER BY x}
000139 } {1 2 5 7 9}
000140 do_test rowid-2.8 {
000141 global x2rowid
000142 set sql "UPDATE t1 SET x=3 WHERE _rowid_==$x2rowid(3)"
000143 execsql $sql
000144 execsql {SELECT x FROM t1 ORDER BY x}
000145 } {1 3 5 7 9}
000146
000147 if 0 { # With the index-on-expressions enhancement, creating
000148 # an index on ROWID has become possible.
000149 # We cannot index by ROWID
000150 #
000151 do_test rowid-2.9 {
000152 set v [catch {execsql {CREATE INDEX idxt1 ON t1(rowid)}} msg]
000153 lappend v $msg
000154 } {1 {table t1 has no column named rowid}}
000155 do_test rowid-2.10 {
000156 set v [catch {execsql {CREATE INDEX idxt1 ON t1(_rowid_)}} msg]
000157 lappend v $msg
000158 } {1 {table t1 has no column named _rowid_}}
000159 do_test rowid-2.11 {
000160 set v [catch {execsql {CREATE INDEX idxt1 ON t1(oid)}} msg]
000161 lappend v $msg
000162 } {1 {table t1 has no column named oid}}
000163 do_test rowid-2.12 {
000164 set v [catch {execsql {CREATE INDEX idxt1 ON t1(x, rowid)}} msg]
000165 lappend v $msg
000166 } {1 {table t1 has no column named rowid}}
000167 }
000168
000169 # Columns defined in the CREATE statement override the buildin ROWID
000170 # column names.
000171 #
000172 do_test rowid-3.1 {
000173 execsql {
000174 CREATE TABLE t2(rowid int, x int, y int);
000175 INSERT INTO t2 VALUES(0,2,3);
000176 INSERT INTO t2 VALUES(4,5,6);
000177 INSERT INTO t2 VALUES(7,8,9);
000178 SELECT * FROM t2 ORDER BY x;
000179 }
000180 } {0 2 3 4 5 6 7 8 9}
000181 do_test rowid-3.2 {
000182 execsql {SELECT * FROM t2 ORDER BY rowid}
000183 } {0 2 3 4 5 6 7 8 9}
000184 do_test rowid-3.3 {
000185 execsql {SELECT rowid, x, y FROM t2 ORDER BY rowid}
000186 } {0 2 3 4 5 6 7 8 9}
000187 do_test rowid-3.4 {
000188 set r1 [execsql {SELECT _rowid_, rowid FROM t2 ORDER BY rowid}]
000189 foreach {a b c d e f} $r1 {}
000190 set r2 [execsql {SELECT _rowid_, rowid FROM t2 ORDER BY x DESC}]
000191 foreach {u v w x y z} $r2 {}
000192 expr {$u==$e && $w==$c && $y==$a}
000193 } {1}
000194 # sqlite3 v3 - do_probtest doesn't exist anymore?
000195 if 0 {
000196 do_probtest rowid-3.5 {
000197 set r1 [execsql {SELECT _rowid_, rowid FROM t2 ORDER BY rowid}]
000198 foreach {a b c d e f} $r1 {}
000199 expr {$a!=$b && $c!=$d && $e!=$f}
000200 } {1}
000201 }
000202
000203 # Let's try some more complex examples, including some joins.
000204 #
000205 do_test rowid-4.1 {
000206 execsql {
000207 DELETE FROM t1;
000208 DELETE FROM t2;
000209 }
000210 for {set i 1} {$i<=50} {incr i} {
000211 execsql "INSERT INTO t1(x,y) VALUES($i,[expr {$i*$i}])"
000212 }
000213 execsql {INSERT INTO t2 SELECT _rowid_, x*y, y*y FROM t1}
000214 execsql {SELECT t2.y FROM t1, t2 WHERE t1.x==4 AND t1.rowid==t2.rowid}
000215 } {256}
000216 do_test rowid-4.2 {
000217 execsql {SELECT t2.y FROM t2, t1 WHERE t1.x==4 AND t1.rowid==t2.rowid}
000218 } {256}
000219 do_test rowid-4.2.1 {
000220 execsql {SELECT t2.y FROM t2, t1 WHERE t1.x==4 AND t1.oid==t2.rowid}
000221 } {256}
000222 do_test rowid-4.2.2 {
000223 execsql {SELECT t2.y FROM t2, t1 WHERE t1.x==4 AND t1._rowid_==t2.rowid}
000224 } {256}
000225 do_test rowid-4.2.3 {
000226 execsql {SELECT t2.y FROM t2, t1 WHERE t1.x==4 AND t2.rowid==t1.rowid}
000227 } {256}
000228 do_test rowid-4.2.4 {
000229 execsql {SELECT t2.y FROM t2, t1 WHERE t2.rowid==t1.oid AND t1.x==4}
000230 } {256}
000231 do_test rowid-4.2.5 {
000232 execsql {SELECT t2.y FROM t1, t2 WHERE t1.x==4 AND t1._rowid_==t2.rowid}
000233 } {256}
000234 do_test rowid-4.2.6 {
000235 execsql {SELECT t2.y FROM t1, t2 WHERE t1.x==4 AND t2.rowid==t1.rowid}
000236 } {256}
000237 do_test rowid-4.2.7 {
000238 execsql {SELECT t2.y FROM t1, t2 WHERE t2.rowid==t1.oid AND t1.x==4}
000239 } {256}
000240 do_test rowid-4.3 {
000241 execsql {CREATE INDEX idxt1 ON t1(x)}
000242 execsql {SELECT t2.y FROM t1, t2 WHERE t1.x==4 AND t1.rowid==t2.rowid}
000243 } {256}
000244 do_test rowid-4.3.1 {
000245 execsql {SELECT t2.y FROM t1, t2 WHERE t1.x==4 AND t1._rowid_==t2.rowid}
000246 } {256}
000247 do_test rowid-4.3.2 {
000248 execsql {SELECT t2.y FROM t1, t2 WHERE t2.rowid==t1.oid AND 4==t1.x}
000249 } {256}
000250 do_test rowid-4.4 {
000251 execsql {SELECT t2.y FROM t2, t1 WHERE t1.x==4 AND t1.rowid==t2.rowid}
000252 } {256}
000253 do_test rowid-4.4.1 {
000254 execsql {SELECT t2.y FROM t2, t1 WHERE t1.x==4 AND t1._rowid_==t2.rowid}
000255 } {256}
000256 do_test rowid-4.4.2 {
000257 execsql {SELECT t2.y FROM t2, t1 WHERE t2.rowid==t1.oid AND 4==t1.x}
000258 } {256}
000259 do_test rowid-4.5 {
000260 execsql {CREATE INDEX idxt2 ON t2(y)}
000261 set sqlite_search_count 0
000262 concat [execsql {
000263 SELECT t1.x FROM t2, t1
000264 WHERE t2.y==256 AND t1.rowid==t2.rowid
000265 }] $sqlite_search_count
000266 } {4 3}
000267 do_test rowid-4.5.1 {
000268 set sqlite_search_count 0
000269 concat [execsql {
000270 SELECT t1.x FROM t2, t1
000271 WHERE t1.OID==t2.rowid AND t2.y==81
000272 }] $sqlite_search_count
000273 } {3 3}
000274 do_test rowid-4.6 {
000275 execsql {
000276 SELECT t1.x FROM t1, t2
000277 WHERE t2.y==256 AND t1.rowid==t2.rowid
000278 }
000279 } {4}
000280
000281 do_test rowid-5.1.1 {
000282 ifcapable subquery {
000283 execsql {DELETE FROM t1 WHERE _rowid_ IN (SELECT oid FROM t1 WHERE x>8)}
000284 } else {
000285 set oids [execsql {SELECT oid FROM t1 WHERE x>8}]
000286 set where "_rowid_ = [join $oids { OR _rowid_ = }]"
000287 execsql "DELETE FROM t1 WHERE $where"
000288 }
000289 } {}
000290 do_test rowid-5.1.2 {
000291 execsql {SELECT max(x) FROM t1}
000292 } {8}
000293
000294 # Make sure a "WHERE rowid=X" clause works when there is no ROWID of X.
000295 #
000296 do_test rowid-6.1 {
000297 execsql {
000298 SELECT x FROM t1
000299 }
000300 } {1 2 3 4 5 6 7 8}
000301 do_test rowid-6.2 {
000302 for {set ::norow 1} {1} {incr ::norow} {
000303 if {[execsql "SELECT x FROM t1 WHERE rowid=$::norow"]==""} break
000304 }
000305 execsql [subst {
000306 DELETE FROM t1 WHERE rowid=$::norow
000307 }]
000308 } {}
000309 do_test rowid-6.3 {
000310 execsql {
000311 SELECT x FROM t1
000312 }
000313 } {1 2 3 4 5 6 7 8}
000314
000315 # Beginning with version 2.3.4, SQLite computes rowids of new rows by
000316 # finding the maximum current rowid and adding one. It falls back to
000317 # the old random algorithm if the maximum rowid is the largest integer.
000318 # The following tests are for this new behavior.
000319 #
000320 do_test rowid-7.0 {
000321 execsql {
000322 DELETE FROM t1;
000323 DROP TABLE t2;
000324 DROP INDEX idxt1;
000325 INSERT INTO t1 VALUES(1,2);
000326 SELECT rowid, * FROM t1;
000327 }
000328 } {1 1 2}
000329 do_test rowid-7.1 {
000330 execsql {
000331 INSERT INTO t1 VALUES(99,100);
000332 SELECT rowid,* FROM t1
000333 }
000334 } {1 1 2 2 99 100}
000335 do_test rowid-7.2 {
000336 execsql {
000337 CREATE TABLE t2(a INTEGER PRIMARY KEY, b);
000338 INSERT INTO t2(b) VALUES(55);
000339 SELECT * FROM t2;
000340 }
000341 } {1 55}
000342 do_test rowid-7.3 {
000343 execsql {
000344 INSERT INTO t2(b) VALUES(66);
000345 SELECT * FROM t2;
000346 }
000347 } {1 55 2 66}
000348 do_test rowid-7.4 {
000349 execsql {
000350 INSERT INTO t2(a,b) VALUES(1000000,77);
000351 INSERT INTO t2(b) VALUES(88);
000352 SELECT * FROM t2;
000353 }
000354 } {1 55 2 66 1000000 77 1000001 88}
000355 do_test rowid-7.5 {
000356 execsql {
000357 INSERT INTO t2(a,b) VALUES(2147483647,99);
000358 INSERT INTO t2(b) VALUES(11);
000359 SELECT b FROM t2 ORDER BY b;
000360 }
000361 } {11 55 66 77 88 99}
000362 ifcapable subquery {
000363 do_test rowid-7.6 {
000364 execsql {
000365 SELECT b FROM t2 WHERE a NOT IN(1,2,1000000,1000001,2147483647);
000366 }
000367 } {11}
000368 do_test rowid-7.7 {
000369 execsql {
000370 INSERT INTO t2(b) VALUES(22);
000371 INSERT INTO t2(b) VALUES(33);
000372 INSERT INTO t2(b) VALUES(44);
000373 INSERT INTO t2(b) VALUES(55);
000374 SELECT b FROM t2 WHERE a NOT IN(1,2,1000000,1000001,2147483647)
000375 ORDER BY b;
000376 }
000377 } {11 22 33 44 55}
000378 }
000379 do_test rowid-7.8 {
000380 execsql {
000381 DELETE FROM t2 WHERE a!=2;
000382 INSERT INTO t2(b) VALUES(111);
000383 SELECT * FROM t2;
000384 }
000385 } {2 66 3 111}
000386
000387 ifcapable {trigger} {
000388 # Make sure AFTER triggers that do INSERTs do not change the last_insert_rowid.
000389 # Ticket #290
000390 #
000391 do_test rowid-8.1 {
000392 execsql {
000393 CREATE TABLE t3(a integer primary key);
000394 CREATE TABLE t4(x);
000395 INSERT INTO t4 VALUES(1);
000396 CREATE TRIGGER r3 AFTER INSERT on t3 FOR EACH ROW BEGIN
000397 INSERT INTO t4 VALUES(NEW.a+10);
000398 END;
000399 SELECT * FROM t3;
000400 }
000401 } {}
000402 do_test rowid-8.2 {
000403 execsql {
000404 SELECT rowid, * FROM t4;
000405 }
000406 } {1 1}
000407 do_test rowid-8.3 {
000408 execsql {
000409 INSERT INTO t3 VALUES(123);
000410 SELECT last_insert_rowid();
000411 }
000412 } {123}
000413 do_test rowid-8.4 {
000414 execsql {
000415 SELECT * FROM t3;
000416 }
000417 } {123}
000418 do_test rowid-8.5 {
000419 execsql {
000420 SELECT rowid, * FROM t4;
000421 }
000422 } {1 1 2 133}
000423 do_test rowid-8.6 {
000424 execsql {
000425 INSERT INTO t3 VALUES(NULL);
000426 SELECT last_insert_rowid();
000427 }
000428 } {124}
000429 do_test rowid-8.7 {
000430 execsql {
000431 SELECT * FROM t3;
000432 }
000433 } {123 124}
000434 do_test rowid-8.8 {
000435 execsql {
000436 SELECT rowid, * FROM t4;
000437 }
000438 } {1 1 2 133 3 134}
000439 } ;# endif trigger
000440
000441 # If triggers are not enable, simulate their effect for the tests that
000442 # follow.
000443 ifcapable {!trigger} {
000444 execsql {
000445 CREATE TABLE t3(a integer primary key);
000446 INSERT INTO t3 VALUES(123);
000447 INSERT INTO t3 VALUES(124);
000448 }
000449 }
000450
000451 # ticket #377: Comparison between integer primiary key and floating point
000452 # values.
000453 #
000454 do_test rowid-9.1 {
000455 execsql {
000456 SELECT * FROM t3 WHERE a<123.5
000457 }
000458 } {123}
000459 do_test rowid-9.2 {
000460 execsql {
000461 SELECT * FROM t3 WHERE a<124.5
000462 }
000463 } {123 124}
000464 do_test rowid-9.3 {
000465 execsql {
000466 SELECT * FROM t3 WHERE a>123.5
000467 }
000468 } {124}
000469 do_test rowid-9.4 {
000470 execsql {
000471 SELECT * FROM t3 WHERE a>122.5
000472 }
000473 } {123 124}
000474 do_test rowid-9.5 {
000475 execsql {
000476 SELECT * FROM t3 WHERE a==123.5
000477 }
000478 } {}
000479 do_test rowid-9.6 {
000480 execsql {
000481 SELECT * FROM t3 WHERE a==123.000
000482 }
000483 } {123}
000484 do_test rowid-9.7 {
000485 execsql {
000486 SELECT * FROM t3 WHERE a>100.5 AND a<200.5
000487 }
000488 } {123 124}
000489 do_test rowid-9.8 {
000490 execsql {
000491 SELECT * FROM t3 WHERE a>'xyz';
000492 }
000493 } {}
000494 do_test rowid-9.9 {
000495 execsql {
000496 SELECT * FROM t3 WHERE a<'xyz';
000497 }
000498 } {123 124}
000499 do_test rowid-9.10 {
000500 execsql {
000501 SELECT * FROM t3 WHERE a>=122.9 AND a<=123.1
000502 }
000503 } {123}
000504
000505 # Ticket #567. Comparisons of ROWID or integery primary key against
000506 # floating point numbers still do not always work.
000507 #
000508 do_test rowid-10.1 {
000509 execsql {
000510 CREATE TABLE t5(a);
000511 INSERT INTO t5 VALUES(1);
000512 INSERT INTO t5 VALUES(2);
000513 INSERT INTO t5 SELECT a+2 FROM t5;
000514 INSERT INTO t5 SELECT a+4 FROM t5;
000515 SELECT rowid, * FROM t5;
000516 }
000517 } {1 1 2 2 3 3 4 4 5 5 6 6 7 7 8 8}
000518 do_test rowid-10.2 {
000519 execsql {SELECT rowid, a FROM t5 WHERE rowid>=5.5}
000520 } {6 6 7 7 8 8}
000521 do_test rowid-10.3 {
000522 execsql {SELECT rowid, a FROM t5 WHERE rowid>=5.0}
000523 } {5 5 6 6 7 7 8 8}
000524 do_test rowid-10.4 {
000525 execsql {SELECT rowid, a FROM t5 WHERE rowid>5.5}
000526 } {6 6 7 7 8 8}
000527 do_test rowid-10.3.2 {
000528 execsql {SELECT rowid, a FROM t5 WHERE rowid>5.0}
000529 } {6 6 7 7 8 8}
000530 do_test rowid-10.5 {
000531 execsql {SELECT rowid, a FROM t5 WHERE 5.5<=rowid}
000532 } {6 6 7 7 8 8}
000533 do_test rowid-10.6 {
000534 execsql {SELECT rowid, a FROM t5 WHERE 5.5<rowid}
000535 } {6 6 7 7 8 8}
000536 do_test rowid-10.7 {
000537 execsql {SELECT rowid, a FROM t5 WHERE rowid<=5.5}
000538 } {1 1 2 2 3 3 4 4 5 5}
000539 do_test rowid-10.8 {
000540 execsql {SELECT rowid, a FROM t5 WHERE rowid<5.5}
000541 } {1 1 2 2 3 3 4 4 5 5}
000542 do_test rowid-10.9 {
000543 execsql {SELECT rowid, a FROM t5 WHERE 5.5>=rowid}
000544 } {1 1 2 2 3 3 4 4 5 5}
000545 do_test rowid-10.10 {
000546 execsql {SELECT rowid, a FROM t5 WHERE 5.5>rowid}
000547 } {1 1 2 2 3 3 4 4 5 5}
000548 do_test rowid-10.11 {
000549 execsql {SELECT rowid, a FROM t5 WHERE rowid>=5.5 ORDER BY rowid DESC}
000550 } {8 8 7 7 6 6}
000551 do_test rowid-10.11.2 {
000552 execsql {SELECT rowid, a FROM t5 WHERE rowid>=5.0 ORDER BY rowid DESC}
000553 } {8 8 7 7 6 6 5 5}
000554 do_test rowid-10.12 {
000555 execsql {SELECT rowid, a FROM t5 WHERE rowid>5.5 ORDER BY rowid DESC}
000556 } {8 8 7 7 6 6}
000557 do_test rowid-10.12.2 {
000558 execsql {SELECT rowid, a FROM t5 WHERE rowid>5.0 ORDER BY rowid DESC}
000559 } {8 8 7 7 6 6}
000560 do_test rowid-10.13 {
000561 execsql {SELECT rowid, a FROM t5 WHERE 5.5<=rowid ORDER BY rowid DESC}
000562 } {8 8 7 7 6 6}
000563 do_test rowid-10.14 {
000564 execsql {SELECT rowid, a FROM t5 WHERE 5.5<rowid ORDER BY rowid DESC}
000565 } {8 8 7 7 6 6}
000566 do_test rowid-10.15 {
000567 execsql {SELECT rowid, a FROM t5 WHERE rowid<=5.5 ORDER BY rowid DESC}
000568 } {5 5 4 4 3 3 2 2 1 1}
000569 do_test rowid-10.16 {
000570 execsql {SELECT rowid, a FROM t5 WHERE rowid<5.5 ORDER BY rowid DESC}
000571 } {5 5 4 4 3 3 2 2 1 1}
000572 do_test rowid-10.17 {
000573 execsql {SELECT rowid, a FROM t5 WHERE 5.5>=rowid ORDER BY rowid DESC}
000574 } {5 5 4 4 3 3 2 2 1 1}
000575 do_test rowid-10.18 {
000576 execsql {SELECT rowid, a FROM t5 WHERE 5.5>rowid ORDER BY rowid DESC}
000577 } {5 5 4 4 3 3 2 2 1 1}
000578
000579 do_test rowid-10.30 {
000580 execsql {
000581 CREATE TABLE t6(a);
000582 INSERT INTO t6(rowid,a) SELECT -a,a FROM t5;
000583 SELECT rowid, * FROM t6;
000584 }
000585 } {-8 8 -7 7 -6 6 -5 5 -4 4 -3 3 -2 2 -1 1}
000586 do_test rowid-10.31.1 {
000587 execsql {SELECT rowid, a FROM t6 WHERE rowid>=-5.5}
000588 } {-5 5 -4 4 -3 3 -2 2 -1 1}
000589 do_test rowid-10.31.2 {
000590 execsql {SELECT rowid, a FROM t6 WHERE rowid>=-5.0}
000591 } {-5 5 -4 4 -3 3 -2 2 -1 1}
000592 do_test rowid-10.32.1 {
000593 execsql {SELECT rowid, a FROM t6 WHERE rowid>=-5.5 ORDER BY rowid DESC}
000594 } {-1 1 -2 2 -3 3 -4 4 -5 5}
000595 do_test rowid-10.32.1 {
000596 execsql {SELECT rowid, a FROM t6 WHERE rowid>=-5.0 ORDER BY rowid DESC}
000597 } {-1 1 -2 2 -3 3 -4 4 -5 5}
000598 do_test rowid-10.33 {
000599 execsql {SELECT rowid, a FROM t6 WHERE -5.5<=rowid}
000600 } {-5 5 -4 4 -3 3 -2 2 -1 1}
000601 do_test rowid-10.34 {
000602 execsql {SELECT rowid, a FROM t6 WHERE -5.5<=rowid ORDER BY rowid DESC}
000603 } {-1 1 -2 2 -3 3 -4 4 -5 5}
000604 do_test rowid-10.35.1 {
000605 execsql {SELECT rowid, a FROM t6 WHERE rowid>-5.5}
000606 } {-5 5 -4 4 -3 3 -2 2 -1 1}
000607 do_test rowid-10.35.2 {
000608 execsql {SELECT rowid, a FROM t6 WHERE rowid>-5.0}
000609 } {-4 4 -3 3 -2 2 -1 1}
000610 do_test rowid-10.36.1 {
000611 execsql {SELECT rowid, a FROM t6 WHERE rowid>-5.5 ORDER BY rowid DESC}
000612 } {-1 1 -2 2 -3 3 -4 4 -5 5}
000613 do_test rowid-10.36.2 {
000614 execsql {SELECT rowid, a FROM t6 WHERE rowid>-5.0 ORDER BY rowid DESC}
000615 } {-1 1 -2 2 -3 3 -4 4}
000616 do_test rowid-10.37 {
000617 execsql {SELECT rowid, a FROM t6 WHERE -5.5<rowid}
000618 } {-5 5 -4 4 -3 3 -2 2 -1 1}
000619 do_test rowid-10.38 {
000620 execsql {SELECT rowid, a FROM t6 WHERE -5.5<rowid ORDER BY rowid DESC}
000621 } {-1 1 -2 2 -3 3 -4 4 -5 5}
000622 do_test rowid-10.39 {
000623 execsql {SELECT rowid, a FROM t6 WHERE rowid<=-5.5}
000624 } {-8 8 -7 7 -6 6}
000625 do_test rowid-10.40 {
000626 execsql {SELECT rowid, a FROM t6 WHERE rowid<=-5.5 ORDER BY rowid DESC}
000627 } {-6 6 -7 7 -8 8}
000628 do_test rowid-10.41 {
000629 execsql {SELECT rowid, a FROM t6 WHERE -5.5>=rowid}
000630 } {-8 8 -7 7 -6 6}
000631 do_test rowid-10.42 {
000632 execsql {SELECT rowid, a FROM t6 WHERE -5.5>=rowid ORDER BY rowid DESC}
000633 } {-6 6 -7 7 -8 8}
000634 do_test rowid-10.43 {
000635 execsql {SELECT rowid, a FROM t6 WHERE rowid<-5.5}
000636 } {-8 8 -7 7 -6 6}
000637 do_test rowid-10.44 {
000638 execsql {SELECT rowid, a FROM t6 WHERE rowid<-5.5 ORDER BY rowid DESC}
000639 } {-6 6 -7 7 -8 8}
000640 do_test rowid-10.44 {
000641 execsql {SELECT rowid, a FROM t6 WHERE -5.5>rowid}
000642 } {-8 8 -7 7 -6 6}
000643 do_test rowid-10.46 {
000644 execsql {SELECT rowid, a FROM t6 WHERE -5.5>rowid ORDER BY rowid DESC}
000645 } {-6 6 -7 7 -8 8}
000646
000647 # Comparison of rowid against string values.
000648 #
000649 do_test rowid-11.1 {
000650 execsql {SELECT rowid, a FROM t5 WHERE rowid>'abc'}
000651 } {}
000652 do_test rowid-11.2 {
000653 execsql {SELECT rowid, a FROM t5 WHERE rowid>='abc'}
000654 } {}
000655 do_test rowid-11.3 {
000656 execsql {SELECT rowid, a FROM t5 WHERE rowid<'abc'}
000657 } {1 1 2 2 3 3 4 4 5 5 6 6 7 7 8 8}
000658 do_test rowid-11.4 {
000659 execsql {SELECT rowid, a FROM t5 WHERE rowid<='abc'}
000660 } {1 1 2 2 3 3 4 4 5 5 6 6 7 7 8 8}
000661
000662 # Test the automatic generation of rowids when the table already contains
000663 # a rowid with the maximum value.
000664 #
000665 # Once the maximum rowid is taken, rowids are normally chosen at
000666 # random. By by reseting the random number generator, we can cause
000667 # the rowid guessing loop to collide with prior rowids, and test the
000668 # loop out to its limit of 100 iterations. After 100 collisions, the
000669 # rowid guesser gives up and reports SQLITE_FULL.
000670 #
000671 do_test rowid-12.1 {
000672 execsql {
000673 CREATE TABLE t7(x INTEGER PRIMARY KEY, y);
000674 CREATE TABLE t7temp(a INTEGER PRIMARY KEY);
000675 INSERT INTO t7 VALUES(9223372036854775807,'a');
000676 SELECT y FROM t7;
000677 }
000678 } {a}
000679 do_test rowid-12.2 {
000680 db close
000681 sqlite3 db test.db
000682 save_prng_state
000683 execsql {
000684 INSERT INTO t7 VALUES(NULL,'b');
000685 SELECT x, y FROM t7 ORDER BY x;
000686 }
000687 } {/\d+ b 9223372036854775807 a/}
000688 execsql {INSERT INTO t7 VALUES(2,'y');}
000689 for {set i 1} {$i<100} {incr i} {
000690 do_test rowid-12.3.$i {
000691 db eval {DELETE FROM t7temp; INSERT INTO t7temp VALUES(1);}
000692 restore_prng_state
000693 execsql {
000694 INSERT INTO t7 VALUES(NULL,'x');
000695 SELECT count(*) FROM t7 WHERE y=='x';
000696 }
000697 } $i
000698 }
000699 do_test rowid-12.4 {
000700 db eval {DELETE FROM t7temp; INSERT INTO t7temp VALUES(1);}
000701 restore_prng_state
000702 catchsql {
000703 INSERT INTO t7 VALUES(NULL,'x');
000704 }
000705 } {1 {database or disk is full}}
000706
000707 # INSERTs that happen inside of nested function calls are recorded
000708 # by last_insert_rowid.
000709 #
000710 proc rowid_addrow_func {n} {
000711 db eval {INSERT INTO t13(rowid,x) VALUES($n,$n*$n)}
000712 return [db last_insert_rowid]
000713 }
000714 db function addrow rowid_addrow_func
000715 do_execsql_test rowid-13.1 {
000716 CREATE TABLE t13(x);
000717 INSERT INTO t13(rowid,x) VALUES(1234,5);
000718 SELECT rowid, x, addrow(rowid+1000), '|' FROM t13 LIMIT 3;
000719 SELECT last_insert_rowid();
000720 } {1234 5 2234 | 2234 4990756 3234 | 3234 10458756 4234 | 4234}
000721
000722 finish_test