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