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