AryaWu/sqlite
0
1# 2001 September 152#3# The author disclaims copyright to this source code. In place of4# 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. The12# focus of this file is testing the magic ROWID column that is13# found on all tables.14#15# EVIDENCE-OF: R-36924-43758 By default, every row in SQLite has a16# special column, usually called the "rowid", that uniquely identifies17# that row within the table.18 19set testdir [file dirname $argv0]20source $testdir/tester.tcl21set testprefix rowid22 23# Basic ROWID functionality tests.24#25do_test rowid-1.1 {26 execsql {27 CREATE TABLE t1(x int, y int);28 INSERT INTO t1 VALUES(1,2);29 INSERT INTO t1 VALUES(3,4);30 SELECT x FROM t1 ORDER BY y;31 }32} {1 3}33do_test rowid-1.2 {34 set r [execsql {SELECT rowid FROM t1 ORDER BY x}]35 global x2rowid rowid2x36 set x2rowid(1) [lindex $r 0]37 set x2rowid(3) [lindex $r 1]38 set rowid2x($x2rowid(1)) 139 set rowid2x($x2rowid(3)) 340 llength $r41} {2}42do_test rowid-1.3 {43 global x2rowid44 set sql "SELECT x FROM t1 WHERE rowid==$x2rowid(1)"45 execsql $sql46} {1}47do_test rowid-1.4 {48 global x2rowid49 set sql "SELECT x FROM t1 WHERE rowid==$x2rowid(3)"50 execsql $sql51} {3}52do_test rowid-1.5 {53 global x2rowid54 set sql "SELECT x FROM t1 WHERE oid==$x2rowid(1)"55 execsql $sql56} {1}57do_test rowid-1.6 {58 global x2rowid59 set sql "SELECT x FROM t1 WHERE OID==$x2rowid(3)"60 execsql $sql61} {3}62do_test rowid-1.7 {63 global x2rowid64 set sql "SELECT x FROM t1 WHERE _rowid_==$x2rowid(1)"65 execsql $sql66} {1}67do_test rowid-1.7.1 {68 while 1 {69 set norow [expr {int(rand()*1000000)}]70 if {$norow!=$x2rowid(1) && $norow!=$x2rowid(3)} break71 }72 execsql "SELECT x FROM t1 WHERE rowid=$norow"73} {}74do_test rowid-1.8 {75 global x2rowid76 set v [execsql {SELECT x, oid FROM t1 order by x}]77 set v2 [list 1 $x2rowid(1) 3 $x2rowid(3)]78 expr {$v==$v2}79} {1}80do_test rowid-1.9 {81 global x2rowid82 set v [execsql {SELECT x, RowID FROM t1 order by x}]83 set v2 [list 1 $x2rowid(1) 3 $x2rowid(3)]84 expr {$v==$v2}85} {1}86do_test rowid-1.10 {87 global x2rowid88 set v [execsql {SELECT x, _rowid_ FROM t1 order by x}]89 set v2 [list 1 $x2rowid(1) 3 $x2rowid(3)]90 expr {$v==$v2}91} {1}92 93# We can insert or update the ROWID column.94#95do_test rowid-2.1 {96 catchsql {97 INSERT INTO t1(rowid,x,y) VALUES(1234,5,6);98 SELECT rowid, * FROM t1;99 }100} {0 {1 1 2 2 3 4 1234 5 6}}101do_test rowid-2.2 {102 catchsql {103 UPDATE t1 SET rowid=12345 WHERE x==1;104 SELECT rowid, * FROM t1105 }106} {0 {2 3 4 1234 5 6 12345 1 2}}107do_test rowid-2.3 {108 catchsql {109 INSERT INTO t1(y,x,oid) VALUES(8,7,1235);110 SELECT rowid, * FROM t1 WHERE rowid>1000;111 }112} {0 {1234 5 6 1235 7 8 12345 1 2}}113do_test rowid-2.4 {114 catchsql {115 UPDATE t1 SET oid=12346 WHERE x==1;116 SELECT rowid, * FROM t1;117 }118} {0 {2 3 4 1234 5 6 1235 7 8 12346 1 2}}119do_test rowid-2.5 {120 catchsql {121 INSERT INTO t1(x,_rowid_,y) VALUES(9,1236,10);122 SELECT rowid, * FROM t1 WHERE rowid>1000;123 }124} {0 {1234 5 6 1235 7 8 1236 9 10 12346 1 2}}125do_test rowid-2.6 {126 catchsql {127 UPDATE t1 SET _rowid_=12347 WHERE x==1;128 SELECT rowid, * FROM t1 WHERE rowid>1000;129 }130} {0 {1234 5 6 1235 7 8 1236 9 10 12347 1 2}}131 132# But we can use ROWID in the WHERE clause of an UPDATE that does not133# change the ROWID.134#135do_test rowid-2.7 {136 global x2rowid137 set sql "UPDATE t1 SET x=2 WHERE OID==$x2rowid(3)"138 execsql $sql139 execsql {SELECT x FROM t1 ORDER BY x}140} {1 2 5 7 9}141do_test rowid-2.8 {142 global x2rowid143 set sql "UPDATE t1 SET x=3 WHERE _rowid_==$x2rowid(3)"144 execsql $sql145 execsql {SELECT x FROM t1 ORDER BY x}146} {1 3 5 7 9}147 148if 0 { # With the index-on-expressions enhancement, creating149 # an index on ROWID has become possible.150# We cannot index by ROWID151#152do_test rowid-2.9 {153 set v [catch {execsql {CREATE INDEX idxt1 ON t1(rowid)}} msg]154 lappend v $msg155} {1 {table t1 has no column named rowid}}156do_test rowid-2.10 {157 set v [catch {execsql {CREATE INDEX idxt1 ON t1(_rowid_)}} msg]158 lappend v $msg159} {1 {table t1 has no column named _rowid_}}160do_test rowid-2.11 {161 set v [catch {execsql {CREATE INDEX idxt1 ON t1(oid)}} msg]162 lappend v $msg163} {1 {table t1 has no column named oid}}164do_test rowid-2.12 {165 set v [catch {execsql {CREATE INDEX idxt1 ON t1(x, rowid)}} msg]166 lappend v $msg167} {1 {table t1 has no column named rowid}}168}169 170# Columns defined in the CREATE statement override the buildin ROWID171# column names.172#173do_test rowid-3.1 {174 execsql {175 CREATE TABLE t2(rowid int, x int, y int);176 INSERT INTO t2 VALUES(0,2,3);177 INSERT INTO t2 VALUES(4,5,6);178 INSERT INTO t2 VALUES(7,8,9);179 SELECT * FROM t2 ORDER BY x;180 }181} {0 2 3 4 5 6 7 8 9}182do_test rowid-3.2 {183 execsql {SELECT * FROM t2 ORDER BY rowid}184} {0 2 3 4 5 6 7 8 9}185do_test rowid-3.3 {186 execsql {SELECT rowid, x, y FROM t2 ORDER BY rowid}187} {0 2 3 4 5 6 7 8 9}188do_test rowid-3.4 {189 set r1 [execsql {SELECT _rowid_, rowid FROM t2 ORDER BY rowid}]190 foreach {a b c d e f} $r1 {}191 set r2 [execsql {SELECT _rowid_, rowid FROM t2 ORDER BY x DESC}]192 foreach {u v w x y z} $r2 {}193 expr {$u==$e && $w==$c && $y==$a}194} {1}195# sqlite3 v3 - do_probtest doesn't exist anymore?196if 0 {197do_probtest rowid-3.5 {198 set r1 [execsql {SELECT _rowid_, rowid FROM t2 ORDER BY rowid}]199 foreach {a b c d e f} $r1 {}200 expr {$a!=$b && $c!=$d && $e!=$f}201} {1}202}203 204# Let's try some more complex examples, including some joins.205#206do_test rowid-4.1 {207 execsql {208 DELETE FROM t1;209 DELETE FROM t2;210 }211 for {set i 1} {$i<=50} {incr i} {212 execsql "INSERT INTO t1(x,y) VALUES($i,[expr {$i*$i}])"213 }214 execsql {INSERT INTO t2 SELECT _rowid_, x*y, y*y FROM t1}215 execsql {SELECT t2.y FROM t1, t2 WHERE t1.x==4 AND t1.rowid==t2.rowid}216} {256}217do_test rowid-4.2 {218 execsql {SELECT t2.y FROM t2, t1 WHERE t1.x==4 AND t1.rowid==t2.rowid}219} {256}220do_test rowid-4.2.1 {221 execsql {SELECT t2.y FROM t2, t1 WHERE t1.x==4 AND t1.oid==t2.rowid}222} {256}223do_test rowid-4.2.2 {224 execsql {SELECT t2.y FROM t2, t1 WHERE t1.x==4 AND t1._rowid_==t2.rowid}225} {256}226do_test rowid-4.2.3 {227 execsql {SELECT t2.y FROM t2, t1 WHERE t1.x==4 AND t2.rowid==t1.rowid}228} {256}229do_test rowid-4.2.4 {230 execsql {SELECT t2.y FROM t2, t1 WHERE t2.rowid==t1.oid AND t1.x==4}231} {256}232do_test rowid-4.2.5 {233 execsql {SELECT t2.y FROM t1, t2 WHERE t1.x==4 AND t1._rowid_==t2.rowid}234} {256}235do_test rowid-4.2.6 {236 execsql {SELECT t2.y FROM t1, t2 WHERE t1.x==4 AND t2.rowid==t1.rowid}237} {256}238do_test rowid-4.2.7 {239 execsql {SELECT t2.y FROM t1, t2 WHERE t2.rowid==t1.oid AND t1.x==4}240} {256}241do_test rowid-4.3 {242 execsql {CREATE INDEX idxt1 ON t1(x)}243 execsql {SELECT t2.y FROM t1, t2 WHERE t1.x==4 AND t1.rowid==t2.rowid}244} {256}245do_test rowid-4.3.1 {246 execsql {SELECT t2.y FROM t1, t2 WHERE t1.x==4 AND t1._rowid_==t2.rowid}247} {256}248do_test rowid-4.3.2 {249 execsql {SELECT t2.y FROM t1, t2 WHERE t2.rowid==t1.oid AND 4==t1.x}250} {256}251do_test rowid-4.4 {252 execsql {SELECT t2.y FROM t2, t1 WHERE t1.x==4 AND t1.rowid==t2.rowid}253} {256}254do_test rowid-4.4.1 {255 execsql {SELECT t2.y FROM t2, t1 WHERE t1.x==4 AND t1._rowid_==t2.rowid}256} {256}257do_test rowid-4.4.2 {258 execsql {SELECT t2.y FROM t2, t1 WHERE t2.rowid==t1.oid AND 4==t1.x}259} {256}260do_test rowid-4.5 {261 execsql {CREATE INDEX idxt2 ON t2(y)}262 set sqlite_search_count 0263 concat [execsql {264 SELECT t1.x FROM t2, t1 265 WHERE t2.y==256 AND t1.rowid==t2.rowid266 }] $sqlite_search_count267} {4 3}268do_test rowid-4.5.1 {269 set sqlite_search_count 0270 concat [execsql {271 SELECT t1.x FROM t2, t1 272 WHERE t1.OID==t2.rowid AND t2.y==81273 }] $sqlite_search_count274} {3 3}275do_test rowid-4.6 {276 execsql {277 SELECT t1.x FROM t1, t2278 WHERE t2.y==256 AND t1.rowid==t2.rowid279 }280} {4}281 282do_test rowid-5.1.1 {283 ifcapable subquery {284 execsql {DELETE FROM t1 WHERE _rowid_ IN (SELECT oid FROM t1 WHERE x>8)}285 } else {286 set oids [execsql {SELECT oid FROM t1 WHERE x>8}]287 set where "_rowid_ = [join $oids { OR _rowid_ = }]"288 execsql "DELETE FROM t1 WHERE $where"289 }290} {}291do_test rowid-5.1.2 {292 execsql {SELECT max(x) FROM t1}293} {8}294 295# Make sure a "WHERE rowid=X" clause works when there is no ROWID of X.296#297do_test rowid-6.1 {298 execsql {299 SELECT x FROM t1300 }301} {1 2 3 4 5 6 7 8}302do_test rowid-6.2 {303 for {set ::norow 1} {1} {incr ::norow} {304 if {[execsql "SELECT x FROM t1 WHERE rowid=$::norow"]==""} break305 }306 execsql [subst {307 DELETE FROM t1 WHERE rowid=$::norow308 }]309} {}310do_test rowid-6.3 {311 execsql {312 SELECT x FROM t1313 }314} {1 2 3 4 5 6 7 8}315 316# Beginning with version 2.3.4, SQLite computes rowids of new rows by317# finding the maximum current rowid and adding one. It falls back to318# the old random algorithm if the maximum rowid is the largest integer.319# The following tests are for this new behavior.320#321do_test rowid-7.0 {322 execsql {323 DELETE FROM t1;324 DROP TABLE t2;325 DROP INDEX idxt1;326 INSERT INTO t1 VALUES(1,2);327 SELECT rowid, * FROM t1;328 }329} {1 1 2}330do_test rowid-7.1 {331 execsql {332 INSERT INTO t1 VALUES(99,100);333 SELECT rowid,* FROM t1334 }335} {1 1 2 2 99 100}336do_test rowid-7.2 {337 execsql {338 CREATE TABLE t2(a INTEGER PRIMARY KEY, b);339 INSERT INTO t2(b) VALUES(55);340 SELECT * FROM t2;341 }342} {1 55}343do_test rowid-7.3 {344 execsql {345 INSERT INTO t2(b) VALUES(66);346 SELECT * FROM t2;347 }348} {1 55 2 66}349do_test rowid-7.4 {350 execsql {351 INSERT INTO t2(a,b) VALUES(1000000,77);352 INSERT INTO t2(b) VALUES(88);353 SELECT * FROM t2;354 }355} {1 55 2 66 1000000 77 1000001 88}356do_test rowid-7.5 {357 execsql {358 INSERT INTO t2(a,b) VALUES(2147483647,99);359 INSERT INTO t2(b) VALUES(11);360 SELECT b FROM t2 ORDER BY b;361 }362} {11 55 66 77 88 99}363ifcapable subquery {364 do_test rowid-7.6 {365 execsql {366 SELECT b FROM t2 WHERE a NOT IN(1,2,1000000,1000001,2147483647);367 }368 } {11}369 do_test rowid-7.7 {370 execsql {371 INSERT INTO t2(b) VALUES(22);372 INSERT INTO t2(b) VALUES(33);373 INSERT INTO t2(b) VALUES(44);374 INSERT INTO t2(b) VALUES(55);375 SELECT b FROM t2 WHERE a NOT IN(1,2,1000000,1000001,2147483647) 376 ORDER BY b;377 }378 } {11 22 33 44 55}379}380do_test rowid-7.8 {381 execsql {382 DELETE FROM t2 WHERE a!=2;383 INSERT INTO t2(b) VALUES(111);384 SELECT * FROM t2;385 }386} {2 66 3 111}387 388ifcapable {trigger} {389# Make sure AFTER triggers that do INSERTs do not change the last_insert_rowid.390# Ticket #290391#392do_test rowid-8.1 {393 execsql {394 CREATE TABLE t3(a integer primary key);395 CREATE TABLE t4(x);396 INSERT INTO t4 VALUES(1);397 CREATE TRIGGER r3 AFTER INSERT on t3 FOR EACH ROW BEGIN398 INSERT INTO t4 VALUES(NEW.a+10);399 END;400 SELECT * FROM t3;401 }402} {}403do_test rowid-8.2 {404 execsql {405 SELECT rowid, * FROM t4;406 }407} {1 1}408do_test rowid-8.3 {409 execsql {410 INSERT INTO t3 VALUES(123);411 SELECT last_insert_rowid();412 }413} {123}414do_test rowid-8.4 {415 execsql {416 SELECT * FROM t3;417 }418} {123}419do_test rowid-8.5 {420 execsql {421 SELECT rowid, * FROM t4;422 }423} {1 1 2 133}424do_test rowid-8.6 {425 execsql {426 INSERT INTO t3 VALUES(NULL);427 SELECT last_insert_rowid();428 }429} {124}430do_test rowid-8.7 {431 execsql {432 SELECT * FROM t3;433 }434} {123 124}435do_test rowid-8.8 {436 execsql {437 SELECT rowid, * FROM t4;438 }439} {1 1 2 133 3 134}440} ;# endif trigger441 442# If triggers are not enable, simulate their effect for the tests that443# follow.444ifcapable {!trigger} {445 execsql {446 CREATE TABLE t3(a integer primary key);447 INSERT INTO t3 VALUES(123);448 INSERT INTO t3 VALUES(124);449 }450}451 452# ticket #377: Comparison between integer primiary key and floating point453# values.454#455do_test rowid-9.1 {456 execsql {457 SELECT * FROM t3 WHERE a<123.5458 }459} {123}460do_test rowid-9.2 {461 execsql {462 SELECT * FROM t3 WHERE a<124.5463 }464} {123 124}465do_test rowid-9.3 {466 execsql {467 SELECT * FROM t3 WHERE a>123.5468 }469} {124}470do_test rowid-9.4 {471 execsql {472 SELECT * FROM t3 WHERE a>122.5473 }474} {123 124}475do_test rowid-9.5 {476 execsql {477 SELECT * FROM t3 WHERE a==123.5478 }479} {}480do_test rowid-9.6 {481 execsql {482 SELECT * FROM t3 WHERE a==123.000483 }484} {123}485do_test rowid-9.7 {486 execsql {487 SELECT * FROM t3 WHERE a>100.5 AND a<200.5488 }489} {123 124}490do_test rowid-9.8 {491 execsql {492 SELECT * FROM t3 WHERE a>'xyz';493 }494} {}495do_test rowid-9.9 {496 execsql {497 SELECT * FROM t3 WHERE a<'xyz';498 }499} {123 124}500do_test rowid-9.10 {501 execsql {502 SELECT * FROM t3 WHERE a>=122.9 AND a<=123.1503 }504} {123}505 506# Ticket #567. Comparisons of ROWID or integery primary key against507# floating point numbers still do not always work.508#509do_test rowid-10.1 {510 execsql {511 CREATE TABLE t5(a);512 INSERT INTO t5 VALUES(1);513 INSERT INTO t5 VALUES(2);514 INSERT INTO t5 SELECT a+2 FROM t5;515 INSERT INTO t5 SELECT a+4 FROM t5;516 SELECT rowid, * FROM t5;517 }518} {1 1 2 2 3 3 4 4 5 5 6 6 7 7 8 8}519do_test rowid-10.2 {520 execsql {SELECT rowid, a FROM t5 WHERE rowid>=5.5}521} {6 6 7 7 8 8}522do_test rowid-10.3 {523 execsql {SELECT rowid, a FROM t5 WHERE rowid>=5.0}524} {5 5 6 6 7 7 8 8}525do_test rowid-10.4 {526 execsql {SELECT rowid, a FROM t5 WHERE rowid>5.5}527} {6 6 7 7 8 8}528do_test rowid-10.3.2 {529 execsql {SELECT rowid, a FROM t5 WHERE rowid>5.0}530} {6 6 7 7 8 8}531do_test rowid-10.5 {532 execsql {SELECT rowid, a FROM t5 WHERE 5.5<=rowid}533} {6 6 7 7 8 8}534do_test rowid-10.6 {535 execsql {SELECT rowid, a FROM t5 WHERE 5.5<rowid}536} {6 6 7 7 8 8}537do_test rowid-10.7 {538 execsql {SELECT rowid, a FROM t5 WHERE rowid<=5.5}539} {1 1 2 2 3 3 4 4 5 5}540do_test rowid-10.8 {541 execsql {SELECT rowid, a FROM t5 WHERE rowid<5.5}542} {1 1 2 2 3 3 4 4 5 5}543do_test rowid-10.9 {544 execsql {SELECT rowid, a FROM t5 WHERE 5.5>=rowid}545} {1 1 2 2 3 3 4 4 5 5}546do_test rowid-10.10 {547 execsql {SELECT rowid, a FROM t5 WHERE 5.5>rowid}548} {1 1 2 2 3 3 4 4 5 5}549do_test rowid-10.11 {550 execsql {SELECT rowid, a FROM t5 WHERE rowid>=5.5 ORDER BY rowid DESC}551} {8 8 7 7 6 6}552do_test rowid-10.11.2 {553 execsql {SELECT rowid, a FROM t5 WHERE rowid>=5.0 ORDER BY rowid DESC}554} {8 8 7 7 6 6 5 5}555do_test rowid-10.12 {556 execsql {SELECT rowid, a FROM t5 WHERE rowid>5.5 ORDER BY rowid DESC}557} {8 8 7 7 6 6}558do_test rowid-10.12.2 {559 execsql {SELECT rowid, a FROM t5 WHERE rowid>5.0 ORDER BY rowid DESC}560} {8 8 7 7 6 6}561do_test rowid-10.13 {562 execsql {SELECT rowid, a FROM t5 WHERE 5.5<=rowid ORDER BY rowid DESC}563} {8 8 7 7 6 6}564do_test rowid-10.14 {565 execsql {SELECT rowid, a FROM t5 WHERE 5.5<rowid ORDER BY rowid DESC}566} {8 8 7 7 6 6}567do_test rowid-10.15 {568 execsql {SELECT rowid, a FROM t5 WHERE rowid<=5.5 ORDER BY rowid DESC}569} {5 5 4 4 3 3 2 2 1 1}570do_test rowid-10.16 {571 execsql {SELECT rowid, a FROM t5 WHERE rowid<5.5 ORDER BY rowid DESC}572} {5 5 4 4 3 3 2 2 1 1}573do_test rowid-10.17 {574 execsql {SELECT rowid, a FROM t5 WHERE 5.5>=rowid ORDER BY rowid DESC}575} {5 5 4 4 3 3 2 2 1 1}576do_test rowid-10.18 {577 execsql {SELECT rowid, a FROM t5 WHERE 5.5>rowid ORDER BY rowid DESC}578} {5 5 4 4 3 3 2 2 1 1}579 580do_test rowid-10.30 {581 execsql {582 CREATE TABLE t6(a);583 INSERT INTO t6(rowid,a) SELECT -a,a FROM t5;584 SELECT rowid, * FROM t6;585 }586} {-8 8 -7 7 -6 6 -5 5 -4 4 -3 3 -2 2 -1 1}587do_test rowid-10.31.1 {588 execsql {SELECT rowid, a FROM t6 WHERE rowid>=-5.5}589} {-5 5 -4 4 -3 3 -2 2 -1 1}590do_test rowid-10.31.2 {591 execsql {SELECT rowid, a FROM t6 WHERE rowid>=-5.0}592} {-5 5 -4 4 -3 3 -2 2 -1 1}593do_test rowid-10.32.1 {594 execsql {SELECT rowid, a FROM t6 WHERE rowid>=-5.5 ORDER BY rowid DESC}595} {-1 1 -2 2 -3 3 -4 4 -5 5}596do_test rowid-10.32.1 {597 execsql {SELECT rowid, a FROM t6 WHERE rowid>=-5.0 ORDER BY rowid DESC}598} {-1 1 -2 2 -3 3 -4 4 -5 5}599do_test rowid-10.33 {600 execsql {SELECT rowid, a FROM t6 WHERE -5.5<=rowid}601} {-5 5 -4 4 -3 3 -2 2 -1 1}602do_test rowid-10.34 {603 execsql {SELECT rowid, a FROM t6 WHERE -5.5<=rowid ORDER BY rowid DESC}604} {-1 1 -2 2 -3 3 -4 4 -5 5}605do_test rowid-10.35.1 {606 execsql {SELECT rowid, a FROM t6 WHERE rowid>-5.5}607} {-5 5 -4 4 -3 3 -2 2 -1 1}608do_test rowid-10.35.2 {609 execsql {SELECT rowid, a FROM t6 WHERE rowid>-5.0}610} {-4 4 -3 3 -2 2 -1 1}611do_test rowid-10.36.1 {612 execsql {SELECT rowid, a FROM t6 WHERE rowid>-5.5 ORDER BY rowid DESC}613} {-1 1 -2 2 -3 3 -4 4 -5 5}614do_test rowid-10.36.2 {615 execsql {SELECT rowid, a FROM t6 WHERE rowid>-5.0 ORDER BY rowid DESC}616} {-1 1 -2 2 -3 3 -4 4}617do_test rowid-10.37 {618 execsql {SELECT rowid, a FROM t6 WHERE -5.5<rowid}619} {-5 5 -4 4 -3 3 -2 2 -1 1}620do_test rowid-10.38 {621 execsql {SELECT rowid, a FROM t6 WHERE -5.5<rowid ORDER BY rowid DESC}622} {-1 1 -2 2 -3 3 -4 4 -5 5}623do_test rowid-10.39 {624 execsql {SELECT rowid, a FROM t6 WHERE rowid<=-5.5}625} {-8 8 -7 7 -6 6}626do_test rowid-10.40 {627 execsql {SELECT rowid, a FROM t6 WHERE rowid<=-5.5 ORDER BY rowid DESC}628} {-6 6 -7 7 -8 8}629do_test rowid-10.41 {630 execsql {SELECT rowid, a FROM t6 WHERE -5.5>=rowid}631} {-8 8 -7 7 -6 6}632do_test rowid-10.42 {633 execsql {SELECT rowid, a FROM t6 WHERE -5.5>=rowid ORDER BY rowid DESC}634} {-6 6 -7 7 -8 8}635do_test rowid-10.43 {636 execsql {SELECT rowid, a FROM t6 WHERE rowid<-5.5}637} {-8 8 -7 7 -6 6}638do_test rowid-10.44 {639 execsql {SELECT rowid, a FROM t6 WHERE rowid<-5.5 ORDER BY rowid DESC}640} {-6 6 -7 7 -8 8}641do_test rowid-10.44 {642 execsql {SELECT rowid, a FROM t6 WHERE -5.5>rowid}643} {-8 8 -7 7 -6 6}644do_test rowid-10.46 {645 execsql {SELECT rowid, a FROM t6 WHERE -5.5>rowid ORDER BY rowid DESC}646} {-6 6 -7 7 -8 8}647 648# Comparison of rowid against string values.649#650do_test rowid-11.1 {651 execsql {SELECT rowid, a FROM t5 WHERE rowid>'abc'}652} {}653do_test rowid-11.2 {654 execsql {SELECT rowid, a FROM t5 WHERE rowid>='abc'}655} {}656do_test rowid-11.3 {657 execsql {SELECT rowid, a FROM t5 WHERE rowid<'abc'}658} {1 1 2 2 3 3 4 4 5 5 6 6 7 7 8 8}659do_test rowid-11.4 {660 execsql {SELECT rowid, a FROM t5 WHERE rowid<='abc'}661} {1 1 2 2 3 3 4 4 5 5 6 6 7 7 8 8}662 663do_test rowid-11.asc.1 {664 execsql {SELECT rowid, a FROM t5 WHERE rowid>'abc' ORDER BY 1 ASC}665} {}666do_test rowid-11.asc.2 {667 execsql {SELECT rowid, a FROM t5 WHERE rowid>='abc' ORDER BY 1 ASC}668} {}669do_test rowid-11.asc.3 {670 execsql {SELECT rowid, a FROM t5 WHERE rowid<'abc' ORDER BY 1 ASC}671} {1 1 2 2 3 3 4 4 5 5 6 6 7 7 8 8}672do_test rowid-11.asc.4 {673 execsql {SELECT rowid, a FROM t5 WHERE rowid<='abc' ORDER BY 1 ASC}674} {1 1 2 2 3 3 4 4 5 5 6 6 7 7 8 8}675 676do_test rowid-11.desc.1 {677 execsql {SELECT rowid, a FROM t5 WHERE rowid>'abc' ORDER BY 1 DESC}678} {}679do_test rowid-11.desc.2 {680 execsql {SELECT rowid, a FROM t5 WHERE rowid>='abc' ORDER BY 1 DESC}681} {}682do_test rowid-11.desc.3 {683 execsql {SELECT rowid, a FROM t5 WHERE rowid<'abc' ORDER BY 1 DESC}684} {8 8 7 7 6 6 5 5 4 4 3 3 2 2 1 1}685do_test rowid-11.desc.4 {686 execsql {SELECT rowid, a FROM t5 WHERE rowid<='abc' ORDER BY 1 DESC}687} {8 8 7 7 6 6 5 5 4 4 3 3 2 2 1 1}688 689# Test the automatic generation of rowids when the table already contains690# a rowid with the maximum value.691#692# Once the maximum rowid is taken, rowids are normally chosen at693# random. By by reseting the random number generator, we can cause694# the rowid guessing loop to collide with prior rowids, and test the695# loop out to its limit of 100 iterations. After 100 collisions, the696# rowid guesser gives up and reports SQLITE_FULL.697#698do_test rowid-12.1 {699 execsql {700 CREATE TABLE t7(x INTEGER PRIMARY KEY, y);701 CREATE TABLE t7temp(a INTEGER PRIMARY KEY);702 INSERT INTO t7 VALUES(9223372036854775807,'a');703 SELECT y FROM t7;704 }705} {a}706do_test rowid-12.2 {707 db close708 sqlite3 db test.db709 save_prng_state710 execsql {711 INSERT INTO t7 VALUES(NULL,'b');712 SELECT x, y FROM t7 ORDER BY x;713 }714} {/\d+ b 9223372036854775807 a/}715execsql {INSERT INTO t7 VALUES(2,'y');}716for {set i 1} {$i<100} {incr i} {717 do_test rowid-12.3.$i {718 db eval {DELETE FROM t7temp; INSERT INTO t7temp VALUES(1);}719 restore_prng_state720 execsql {721 INSERT INTO t7 VALUES(NULL,'x');722 SELECT count(*) FROM t7 WHERE y=='x';723 }724 } $i725}726do_test rowid-12.4 {727 db eval {DELETE FROM t7temp; INSERT INTO t7temp VALUES(1);}728 restore_prng_state729 catchsql {730 INSERT INTO t7 VALUES(NULL,'x');731 }732} {1 {database or disk is full}}733 734# INSERTs that happen inside of nested function calls are recorded735# by last_insert_rowid.736#737proc rowid_addrow_func {n} {738 db eval {INSERT INTO t13(rowid,x) VALUES($n,$n*$n)}739 return [db last_insert_rowid]740}741db function addrow rowid_addrow_func742do_execsql_test rowid-13.1 {743 CREATE TABLE t13(x);744 INSERT INTO t13(rowid,x) VALUES(1234,5);745 SELECT rowid, x, addrow(rowid+1000), '|' FROM t13 LIMIT 3;746 SELECT last_insert_rowid();747} {1234 5 2234 | 2234 4990756 3234 | 3234 10458756 4234 | 4234}748 749#-------------------------------------------------------------------------750do_execsql_test rowid-14.0 {751 CREATE TABLE t14(x INTEGER PRIMARY KEY);752 INSERT INTO t14(x) VALUES (100);753}754do_execsql_test rowid-14.1 {755 SELECT * FROM t14 WHERE x < 'a' ORDER BY rowid ASC;756} {100}757do_execsql_test rowid-14.2 {758 SELECT * FROM t14 WHERE x < 'a' ORDER BY rowid DESC;759} {100}760 761do_execsql_test rowid-14.3 {762 DELETE FROM t14;763 SELECT * FROM t14 WHERE x < 'a' ORDER BY rowid ASC;764} {}765do_execsql_test rowid-14.4 {766 SELECT * FROM t14 WHERE x < 'a' ORDER BY rowid DESC;767} {}768 769reset_db770do_execsql_test rowid-15.0 {771 PRAGMA reverse_unordered_selects=true;772 CREATE TABLE t1 (c0, c1);773 CREATE TABLE t2 (c0 INT UNIQUE);774 INSERT INTO t1(c0, c1) VALUES (0, 0), (0, NULL);775 INSERT INTO t2(c0) VALUES (1);776}777 778do_execsql_test rowid-15.1 {779 SELECT t2.c0, t1.c1 FROM t1, t2 780 WHERE (t2.rowid <= 'a') OR (t1.c0 <= t2.c0) LIMIT 100781} {1 {} 1 0}782 783do_execsql_test rowid-15.2 {784 SELECT 1, NULL INTERSECT SELECT * FROM (785 SELECT t2.c0, t1.c1 FROM t1, t2786 WHERE ((t2.rowid <= 'a')) OR (t1.c0 <= t2.c0) ORDER BY 'a' DESC LIMIT 100787 );788} {1 {}}789 790#-------------------------------------------------------------------------791# Check that an unqualified "rowid" can be used in join queries so long 792# as only one of the source objects has a rowid column.793#794reset_db795do_execsql_test 16.0 {796 CREATE TABLE t1(x);797 CREATE TABLE t2(y PRIMARY KEY) WITHOUT ROWID;798 CREATE VIEW v1 AS SELECT x FROM t1;799 CREATE TABLE t3(z);800 801 INSERT INTO t1(rowid, x) VALUES(1, 1);802 INSERT INTO t2(y) VALUES(2);803 INSERT INTO t3(rowid, z) VALUES(3, 3);804}805 806ifcapable allow_rowid_in_view {807 set nosuch "1 {ambiguous column name: rowid}"808 do_execsql_test 16.1 { SELECT rowid FROM t1, t2; } {1}809 do_catchsql_test 16.2 { SELECT rowid FROM t1, v1; } $nosuch810 do_catchsql_test 16.3 { SELECT rowid FROM t3, v1; } $nosuch811 do_catchsql_test 16.4 { SELECT rowid FROM t3, (SELECT 123); } $nosuch812 813 do_execsql_test 16.5 { SELECT rowid FROM t2, t1; } {1}814 do_catchsql_test 16.6 { SELECT rowid FROM v1, t1; } $nosuch815 do_catchsql_test 16.7 { SELECT rowid FROM v1, t3; } $nosuch816 do_execsql_test 16.8 { SELECT rowid FROM (SELECT 123), t3; } {3}817} else {818 do_execsql_test 16.1 { SELECT rowid FROM t1, t2; } {1}819 do_execsql_test 16.2 { SELECT rowid FROM t1, v1; } {1}820 do_execsql_test 16.3 { SELECT rowid FROM t3, v1; } {3}821 do_execsql_test 16.4 { SELECT rowid FROM t3, (SELECT 123); } {3}822 823 do_execsql_test 16.5 { SELECT rowid FROM t2, t1; } {1}824 do_execsql_test 16.6 { SELECT rowid FROM v1, t1; } {1}825 do_execsql_test 16.7 { SELECT rowid FROM v1, t3; } {3}826 do_execsql_test 16.8 { SELECT rowid FROM (SELECT 123), t3; } {3}827}828 829do_catchsql_test 16.9 { 830 SELECT rowid FROM t1, t3; 831} {1 {ambiguous column name: rowid}}832 833finish_test834