third_party/sqlite/sqlite-src-3080704/test/rowid.test - chromium/src - Git at Google

 # 2001 September 15
 #
 # The author disclaims copyright to this source code.  In place of
 # a legal notice, here is a blessing:
 #
 #    May you do good and not evil.
 #    May you find forgiveness for yourself and forgive others.
 #    May you share freely, never taking more than you give.
 #
 #***********************************************************************
 # This file implements regression tests for SQLite library.  The
 # focus of this file is testing the magic ROWID column that is
 # found on all tables.
 #
 # EVIDENCE-OF: R-36924-43758 By default, every row in SQLite has a
 # special column, usually called the "rowid", that uniquely identifies
 # that row within the table.

 set testdir [file dirname $argv0]
 source $testdir/tester.tcl

 # Basic ROWID functionality tests.
 #
 do_test rowid-1.1 {
   execsql {
     CREATE TABLE t1(x int, y int);
     INSERT INTO t1 VALUES(1,2);
     INSERT INTO t1 VALUES(3,4);
     SELECT x FROM t1 ORDER BY y;
   }
 } {1 3}
 do_test rowid-1.2 {
   set r [execsql {SELECT rowid FROM t1 ORDER BY x}]
   global x2rowid rowid2x
   set x2rowid(1) [lindex $r 0]
   set x2rowid(3) [lindex $r 1]
   set rowid2x($x2rowid(1)) 1
   set rowid2x($x2rowid(3)) 3
   llength $r
 } {2}
 do_test rowid-1.3 {
   global x2rowid
   set sql "SELECT x FROM t1 WHERE rowid==$x2rowid(1)"
   execsql $sql
 } {1}
 do_test rowid-1.4 {
   global x2rowid
   set sql "SELECT x FROM t1 WHERE rowid==$x2rowid(3)"
   execsql $sql
 } {3}
 do_test rowid-1.5 {
   global x2rowid
   set sql "SELECT x FROM t1 WHERE oid==$x2rowid(1)"
   execsql $sql
 } {1}
 do_test rowid-1.6 {
   global x2rowid
   set sql "SELECT x FROM t1 WHERE OID==$x2rowid(3)"
   execsql $sql
 } {3}
 do_test rowid-1.7 {
   global x2rowid
   set sql "SELECT x FROM t1 WHERE _rowid_==$x2rowid(1)"
   execsql $sql
 } {1}
 do_test rowid-1.7.1 {
   while 1 {
     set norow [expr {int(rand()*1000000)}]
     if {$norow!=$x2rowid(1) && $norow!=$x2rowid(3)} break
   }
   execsql "SELECT x FROM t1 WHERE rowid=$norow"
 } {}
 do_test rowid-1.8 {
   global x2rowid
   set v [execsql {SELECT x, oid FROM t1 order by x}]
   set v2 [list 1 $x2rowid(1) 3 $x2rowid(3)]
   expr {$v==$v2}
 } {1}
 do_test rowid-1.9 {
   global x2rowid
   set v [execsql {SELECT x, RowID FROM t1 order by x}]
   set v2 [list 1 $x2rowid(1) 3 $x2rowid(3)]
   expr {$v==$v2}
 } {1}
 do_test rowid-1.10 {
   global x2rowid
   set v [execsql {SELECT x, _rowid_ FROM t1 order by x}]
   set v2 [list 1 $x2rowid(1) 3 $x2rowid(3)]
   expr {$v==$v2}
 } {1}

 # We can insert or update the ROWID column.
 #
 do_test rowid-2.1 {
   catchsql {
     INSERT INTO t1(rowid,x,y) VALUES(1234,5,6);
     SELECT rowid, * FROM t1;
   }
 } {0 {1 1 2 2 3 4 1234 5 6}}
 do_test rowid-2.2 {
   catchsql {
     UPDATE t1 SET rowid=12345 WHERE x==1;
     SELECT rowid, * FROM t1
   }
 } {0 {2 3 4 1234 5 6 12345 1 2}}
 do_test rowid-2.3 {
   catchsql {
     INSERT INTO t1(y,x,oid) VALUES(8,7,1235);
     SELECT rowid, * FROM t1 WHERE rowid>1000;
   }
 } {0 {1234 5 6 1235 7 8 12345 1 2}}
 do_test rowid-2.4 {
   catchsql {
     UPDATE t1 SET oid=12346 WHERE x==1;
     SELECT rowid, * FROM t1;
   }
 } {0 {2 3 4 1234 5 6 1235 7 8 12346 1 2}}
 do_test rowid-2.5 {
   catchsql {
     INSERT INTO t1(x,_rowid_,y) VALUES(9,1236,10);
     SELECT rowid, * FROM t1 WHERE rowid>1000;
   }
 } {0 {1234 5 6 1235 7 8 1236 9 10 12346 1 2}}
 do_test rowid-2.6 {
   catchsql {
     UPDATE t1 SET _rowid_=12347 WHERE x==1;
     SELECT rowid, * FROM t1 WHERE rowid>1000;
   }
 } {0 {1234 5 6 1235 7 8 1236 9 10 12347 1 2}}

 # But we can use ROWID in the WHERE clause of an UPDATE that does not
 # change the ROWID.
 #
 do_test rowid-2.7 {
   global x2rowid
   set sql "UPDATE t1 SET x=2 WHERE OID==$x2rowid(3)"
   execsql $sql
   execsql {SELECT x FROM t1 ORDER BY x}
 } {1 2 5 7 9}
 do_test rowid-2.8 {
   global x2rowid
   set sql "UPDATE t1 SET x=3 WHERE _rowid_==$x2rowid(3)"
   execsql $sql
   execsql {SELECT x FROM t1 ORDER BY x}
 } {1 3 5 7 9}

 # We cannot index by ROWID
 #
 do_test rowid-2.9 {
   set v [catch {execsql {CREATE INDEX idxt1 ON t1(rowid)}} msg]
   lappend v $msg
 } {1 {table t1 has no column named rowid}}
 do_test rowid-2.10 {
   set v [catch {execsql {CREATE INDEX idxt1 ON t1(_rowid_)}} msg]
   lappend v $msg
 } {1 {table t1 has no column named _rowid_}}
 do_test rowid-2.11 {
   set v [catch {execsql {CREATE INDEX idxt1 ON t1(oid)}} msg]
   lappend v $msg
 } {1 {table t1 has no column named oid}}
 do_test rowid-2.12 {
   set v [catch {execsql {CREATE INDEX idxt1 ON t1(x, rowid)}} msg]
   lappend v $msg
 } {1 {table t1 has no column named rowid}}

 # Columns defined in the CREATE statement override the buildin ROWID
 # column names.
 #
 do_test rowid-3.1 {
   execsql {
     CREATE TABLE t2(rowid int, x int, y int);
     INSERT INTO t2 VALUES(0,2,3);
     INSERT INTO t2 VALUES(4,5,6);
     INSERT INTO t2 VALUES(7,8,9);
     SELECT * FROM t2 ORDER BY x;
   }
 } {0 2 3 4 5 6 7 8 9}
 do_test rowid-3.2 {
   execsql {SELECT * FROM t2 ORDER BY rowid}
 } {0 2 3 4 5 6 7 8 9}
 do_test rowid-3.3 {
   execsql {SELECT rowid, x, y FROM t2 ORDER BY rowid}
 } {0 2 3 4 5 6 7 8 9}
 do_test rowid-3.4 {
   set r1 [execsql {SELECT _rowid_, rowid FROM t2 ORDER BY rowid}]
   foreach {a b c d e f} $r1 {}
   set r2 [execsql {SELECT _rowid_, rowid FROM t2 ORDER BY x DESC}]
   foreach {u v w x y z} $r2 {}
   expr {$u==$e && $w==$c && $y==$a}
 } {1}
 # sqlite3 v3 - do_probtest doesn't exist anymore?
 if 0 {
 do_probtest rowid-3.5 {
   set r1 [execsql {SELECT _rowid_, rowid FROM t2 ORDER BY rowid}]
   foreach {a b c d e f} $r1 {}
   expr {$a!=$b && $c!=$d && $e!=$f}
 } {1}
 }

 # Let's try some more complex examples, including some joins.
 #
 do_test rowid-4.1 {
   execsql {
     DELETE FROM t1;
     DELETE FROM t2;
   }
   for {set i 1} {$i<=50} {incr i} {
     execsql "INSERT INTO t1(x,y) VALUES($i,[expr {$i*$i}])"
   }
   execsql {INSERT INTO t2 SELECT _rowid_, x*y, y*y FROM t1}
   execsql {SELECT t2.y FROM t1, t2 WHERE t1.x==4 AND t1.rowid==t2.rowid}
 } {256}
 do_test rowid-4.2 {
   execsql {SELECT t2.y FROM t2, t1 WHERE t1.x==4 AND t1.rowid==t2.rowid}
 } {256}
 do_test rowid-4.2.1 {
   execsql {SELECT t2.y FROM t2, t1 WHERE t1.x==4 AND t1.oid==t2.rowid}
 } {256}
 do_test rowid-4.2.2 {
   execsql {SELECT t2.y FROM t2, t1 WHERE t1.x==4 AND t1._rowid_==t2.rowid}
 } {256}
 do_test rowid-4.2.3 {
   execsql {SELECT t2.y FROM t2, t1 WHERE t1.x==4 AND t2.rowid==t1.rowid}
 } {256}
 do_test rowid-4.2.4 {
   execsql {SELECT t2.y FROM t2, t1 WHERE t2.rowid==t1.oid AND t1.x==4}
 } {256}
 do_test rowid-4.2.5 {
   execsql {SELECT t2.y FROM t1, t2 WHERE t1.x==4 AND t1._rowid_==t2.rowid}
 } {256}
 do_test rowid-4.2.6 {
   execsql {SELECT t2.y FROM t1, t2 WHERE t1.x==4 AND t2.rowid==t1.rowid}
 } {256}
 do_test rowid-4.2.7 {
   execsql {SELECT t2.y FROM t1, t2 WHERE t2.rowid==t1.oid AND t1.x==4}
 } {256}
 do_test rowid-4.3 {
   execsql {CREATE INDEX idxt1 ON t1(x)}
   execsql {SELECT t2.y FROM t1, t2 WHERE t1.x==4 AND t1.rowid==t2.rowid}
 } {256}
 do_test rowid-4.3.1 {
   execsql {SELECT t2.y FROM t1, t2 WHERE t1.x==4 AND t1._rowid_==t2.rowid}
 } {256}
 do_test rowid-4.3.2 {
   execsql {SELECT t2.y FROM t1, t2 WHERE t2.rowid==t1.oid AND 4==t1.x}
 } {256}
 do_test rowid-4.4 {
   execsql {SELECT t2.y FROM t2, t1 WHERE t1.x==4 AND t1.rowid==t2.rowid}
 } {256}
 do_test rowid-4.4.1 {
   execsql {SELECT t2.y FROM t2, t1 WHERE t1.x==4 AND t1._rowid_==t2.rowid}
 } {256}
 do_test rowid-4.4.2 {
   execsql {SELECT t2.y FROM t2, t1 WHERE t2.rowid==t1.oid AND 4==t1.x}
 } {256}
 do_test rowid-4.5 {
   execsql {CREATE INDEX idxt2 ON t2(y)}
   set sqlite_search_count 0
   concat [execsql {
     SELECT t1.x FROM t2, t1
     WHERE t2.y==256 AND t1.rowid==t2.rowid
   }] $sqlite_search_count
 } {4 3}
 do_test rowid-4.5.1 {
   set sqlite_search_count 0
   concat [execsql {
     SELECT t1.x FROM t2, t1
     WHERE t1.OID==t2.rowid AND t2.y==81
   }] $sqlite_search_count
 } {3 3}
 do_test rowid-4.6 {
   execsql {
     SELECT t1.x FROM t1, t2
     WHERE t2.y==256 AND t1.rowid==t2.rowid
   }
 } {4}

 do_test rowid-5.1.1 {
   ifcapable subquery {
     execsql {DELETE FROM t1 WHERE _rowid_ IN (SELECT oid FROM t1 WHERE x>8)}
   } else {
     set oids [execsql {SELECT oid FROM t1 WHERE x>8}]
     set where "_rowid_ = [join $oids { OR _rowid_ = }]"
     execsql "DELETE FROM t1 WHERE $where"
   }
 } {}
 do_test rowid-5.1.2 {
   execsql {SELECT max(x) FROM t1}
 } {8}

 # Make sure a "WHERE rowid=X" clause works when there is no ROWID of X.
 #
 do_test rowid-6.1 {
   execsql {
     SELECT x FROM t1
   }
 } {1 2 3 4 5 6 7 8}
 do_test rowid-6.2 {
   for {set ::norow 1} {1} {incr ::norow} {
     if {[execsql "SELECT x FROM t1 WHERE rowid=$::norow"]==""}  break
   }
   execsql [subst {
     DELETE FROM t1 WHERE rowid=$::norow
   }]
 } {}
 do_test rowid-6.3 {
   execsql {
     SELECT x FROM t1
   }
 } {1 2 3 4 5 6 7 8}

 # Beginning with version 2.3.4, SQLite computes rowids of new rows by
 # finding the maximum current rowid and adding one.  It falls back to
 # the old random algorithm if the maximum rowid is the largest integer.
 # The following tests are for this new behavior.
 #
 do_test rowid-7.0 {
   execsql {
     DELETE FROM t1;
     DROP TABLE t2;
     DROP INDEX idxt1;
     INSERT INTO t1 VALUES(1,2);
     SELECT rowid, * FROM t1;
   }
 } {1 1 2}
 do_test rowid-7.1 {
   execsql {
     INSERT INTO t1 VALUES(99,100);
     SELECT rowid,* FROM t1
   }
 } {1 1 2 2 99 100}
 do_test rowid-7.2 {
   execsql {
     CREATE TABLE t2(a INTEGER PRIMARY KEY, b);
     INSERT INTO t2(b) VALUES(55);
     SELECT * FROM t2;
   }
 } {1 55}
 do_test rowid-7.3 {
   execsql {
     INSERT INTO t2(b) VALUES(66);
     SELECT * FROM t2;
   }
 } {1 55 2 66}
 do_test rowid-7.4 {
   execsql {
     INSERT INTO t2(a,b) VALUES(1000000,77);
     INSERT INTO t2(b) VALUES(88);
     SELECT * FROM t2;
   }
 } {1 55 2 66 1000000 77 1000001 88}
 do_test rowid-7.5 {
   execsql {
     INSERT INTO t2(a,b) VALUES(2147483647,99);
     INSERT INTO t2(b) VALUES(11);
     SELECT b FROM t2 ORDER BY b;
   }
 } {11 55 66 77 88 99}
 ifcapable subquery {
   do_test rowid-7.6 {
     execsql {
       SELECT b FROM t2 WHERE a NOT IN(1,2,1000000,1000001,2147483647);
     }
   } {11}
   do_test rowid-7.7 {
     execsql {
       INSERT INTO t2(b) VALUES(22);
       INSERT INTO t2(b) VALUES(33);
       INSERT INTO t2(b) VALUES(44);
       INSERT INTO t2(b) VALUES(55);
       SELECT b FROM t2 WHERE a NOT IN(1,2,1000000,1000001,2147483647)
           ORDER BY b;
     }
   } {11 22 33 44 55}
 }
 do_test rowid-7.8 {
   execsql {
     DELETE FROM t2 WHERE a!=2;
     INSERT INTO t2(b) VALUES(111);
     SELECT * FROM t2;
   }
 } {2 66 3 111}

 ifcapable {trigger} {
 # Make sure AFTER triggers that do INSERTs do not change the last_insert_rowid.
 # Ticket #290
 #
 do_test rowid-8.1 {
   execsql {
     CREATE TABLE t3(a integer primary key);
     CREATE TABLE t4(x);
     INSERT INTO t4 VALUES(1);
     CREATE TRIGGER r3 AFTER INSERT on t3 FOR EACH ROW BEGIN
       INSERT INTO t4 VALUES(NEW.a+10);
     END;
     SELECT * FROM t3;
   }
 } {}
 do_test rowid-8.2 {
   execsql {
     SELECT rowid, * FROM t4;
   }
 } {1 1}
 do_test rowid-8.3 {
   execsql {
     INSERT INTO t3 VALUES(123);
     SELECT last_insert_rowid();
   }
 } {123}
 do_test rowid-8.4 {
   execsql {
     SELECT * FROM t3;
   }
 } {123}
 do_test rowid-8.5 {
   execsql {
     SELECT rowid, * FROM t4;
   }
 } {1 1 2 133}
 do_test rowid-8.6 {
   execsql {
     INSERT INTO t3 VALUES(NULL);
     SELECT last_insert_rowid();
   }
 } {124}
 do_test rowid-8.7 {
   execsql {
     SELECT * FROM t3;
   }
 } {123 124}
 do_test rowid-8.8 {
   execsql {
     SELECT rowid, * FROM t4;
   }
 } {1 1 2 133 3 134}
 } ;# endif trigger

 # If triggers are not enable, simulate their effect for the tests that
 # follow.
 ifcapable {!trigger} {
   execsql {
     CREATE TABLE t3(a integer primary key);
     INSERT INTO t3 VALUES(123);
     INSERT INTO t3 VALUES(124);
   }
 }

 # ticket #377: Comparison between integer primiary key and floating point
 # values.
 #
 do_test rowid-9.1 {
   execsql {
     SELECT * FROM t3 WHERE a<123.5
   }
 } {123}
 do_test rowid-9.2 {
   execsql {
     SELECT * FROM t3 WHERE a<124.5
   }
 } {123 124}
 do_test rowid-9.3 {
   execsql {
     SELECT * FROM t3 WHERE a>123.5
   }
 } {124}
 do_test rowid-9.4 {
   execsql {
     SELECT * FROM t3 WHERE a>122.5
   }
 } {123 124}
 do_test rowid-9.5 {
   execsql {
     SELECT * FROM t3 WHERE a==123.5
   }
 } {}
 do_test rowid-9.6 {
   execsql {
     SELECT * FROM t3 WHERE a==123.000
   }
 } {123}
 do_test rowid-9.7 {
   execsql {
     SELECT * FROM t3 WHERE a>100.5 AND a<200.5
   }
 } {123 124}
 do_test rowid-9.8 {
   execsql {
     SELECT * FROM t3 WHERE a>'xyz';
   }
 } {}
 do_test rowid-9.9 {
   execsql {
     SELECT * FROM t3 WHERE a<'xyz';
   }
 } {123 124}
 do_test rowid-9.10 {
   execsql {
     SELECT * FROM t3 WHERE a>=122.9 AND a<=123.1
   }
 } {123}

 # Ticket #567.  Comparisons of ROWID or integery primary key against
 # floating point numbers still do not always work.
 #
 do_test rowid-10.1 {
   execsql {
     CREATE TABLE t5(a);
     INSERT INTO t5 VALUES(1);
     INSERT INTO t5 VALUES(2);
     INSERT INTO t5 SELECT a+2 FROM t5;
     INSERT INTO t5 SELECT a+4 FROM t5;
     SELECT rowid, * FROM t5;
   }
 } {1 1 2 2 3 3 4 4 5 5 6 6 7 7 8 8}
 do_test rowid-10.2 {
   execsql {SELECT rowid, a FROM t5 WHERE rowid>=5.5}
 } {6 6 7 7 8 8}
 do_test rowid-10.3 {
   execsql {SELECT rowid, a FROM t5 WHERE rowid>=5.0}
 } {5 5 6 6 7 7 8 8}
 do_test rowid-10.4 {
   execsql {SELECT rowid, a FROM t5 WHERE rowid>5.5}
 } {6 6 7 7 8 8}
 do_test rowid-10.3.2 {
   execsql {SELECT rowid, a FROM t5 WHERE rowid>5.0}
 } {6 6 7 7 8 8}
 do_test rowid-10.5 {
   execsql {SELECT rowid, a FROM t5 WHERE 5.5<=rowid}
 } {6 6 7 7 8 8}
 do_test rowid-10.6 {
   execsql {SELECT rowid, a FROM t5 WHERE 5.5<rowid}
 } {6 6 7 7 8 8}
 do_test rowid-10.7 {
   execsql {SELECT rowid, a FROM t5 WHERE rowid<=5.5}
 } {1 1 2 2 3 3 4 4 5 5}
 do_test rowid-10.8 {
   execsql {SELECT rowid, a FROM t5 WHERE rowid<5.5}
 } {1 1 2 2 3 3 4 4 5 5}
 do_test rowid-10.9 {
   execsql {SELECT rowid, a FROM t5 WHERE 5.5>=rowid}
 } {1 1 2 2 3 3 4 4 5 5}
 do_test rowid-10.10 {
   execsql {SELECT rowid, a FROM t5 WHERE 5.5>rowid}
 } {1 1 2 2 3 3 4 4 5 5}
 do_test rowid-10.11 {
   execsql {SELECT rowid, a FROM t5 WHERE rowid>=5.5 ORDER BY rowid DESC}
 } {8 8 7 7 6 6}
 do_test rowid-10.11.2 {
   execsql {SELECT rowid, a FROM t5 WHERE rowid>=5.0 ORDER BY rowid DESC}
 } {8 8 7 7 6 6 5 5}
 do_test rowid-10.12 {
   execsql {SELECT rowid, a FROM t5 WHERE rowid>5.5 ORDER BY rowid DESC}
 } {8 8 7 7 6 6}
 do_test rowid-10.12.2 {
   execsql {SELECT rowid, a FROM t5 WHERE rowid>5.0 ORDER BY rowid DESC}
 } {8 8 7 7 6 6}
 do_test rowid-10.13 {
   execsql {SELECT rowid, a FROM t5 WHERE 5.5<=rowid ORDER BY rowid DESC}
 } {8 8 7 7 6 6}
 do_test rowid-10.14 {
   execsql {SELECT rowid, a FROM t5 WHERE 5.5<rowid ORDER BY rowid DESC}
 } {8 8 7 7 6 6}
 do_test rowid-10.15 {
   execsql {SELECT rowid, a FROM t5 WHERE rowid<=5.5 ORDER BY rowid DESC}
 } {5 5 4 4 3 3 2 2 1 1}
 do_test rowid-10.16 {
   execsql {SELECT rowid, a FROM t5 WHERE rowid<5.5 ORDER BY rowid DESC}
 } {5 5 4 4 3 3 2 2 1 1}
 do_test rowid-10.17 {
   execsql {SELECT rowid, a FROM t5 WHERE 5.5>=rowid ORDER BY rowid DESC}
 } {5 5 4 4 3 3 2 2 1 1}
 do_test rowid-10.18 {
   execsql {SELECT rowid, a FROM t5 WHERE 5.5>rowid ORDER BY rowid DESC}
 } {5 5 4 4 3 3 2 2 1 1}

 do_test rowid-10.30 {
   execsql {
     CREATE TABLE t6(a);
     INSERT INTO t6(rowid,a) SELECT -a,a FROM t5;
     SELECT rowid, * FROM t6;
   }
 } {-8 8 -7 7 -6 6 -5 5 -4 4 -3 3 -2 2 -1 1}
 do_test rowid-10.31.1 {
   execsql {SELECT rowid, a FROM t6 WHERE rowid>=-5.5}
 } {-5 5 -4 4 -3 3 -2 2 -1 1}
 do_test rowid-10.31.2 {
   execsql {SELECT rowid, a FROM t6 WHERE rowid>=-5.0}
 } {-5 5 -4 4 -3 3 -2 2 -1 1}
 do_test rowid-10.32.1 {
   execsql {SELECT rowid, a FROM t6 WHERE rowid>=-5.5 ORDER BY rowid DESC}
 } {-1 1 -2 2 -3 3 -4 4 -5 5}
 do_test rowid-10.32.1 {
   execsql {SELECT rowid, a FROM t6 WHERE rowid>=-5.0 ORDER BY rowid DESC}
 } {-1 1 -2 2 -3 3 -4 4 -5 5}
 do_test rowid-10.33 {
   execsql {SELECT rowid, a FROM t6 WHERE -5.5<=rowid}
 } {-5 5 -4 4 -3 3 -2 2 -1 1}
 do_test rowid-10.34 {
   execsql {SELECT rowid, a FROM t6 WHERE -5.5<=rowid ORDER BY rowid DESC}
 } {-1 1 -2 2 -3 3 -4 4 -5 5}
 do_test rowid-10.35.1 {
   execsql {SELECT rowid, a FROM t6 WHERE rowid>-5.5}
 } {-5 5 -4 4 -3 3 -2 2 -1 1}
 do_test rowid-10.35.2 {
   execsql {SELECT rowid, a FROM t6 WHERE rowid>-5.0}
 } {-4 4 -3 3 -2 2 -1 1}
 do_test rowid-10.36.1 {
   execsql {SELECT rowid, a FROM t6 WHERE rowid>-5.5 ORDER BY rowid DESC}
 } {-1 1 -2 2 -3 3 -4 4 -5 5}
 do_test rowid-10.36.2 {
   execsql {SELECT rowid, a FROM t6 WHERE rowid>-5.0 ORDER BY rowid DESC}
 } {-1 1 -2 2 -3 3 -4 4}
 do_test rowid-10.37 {
   execsql {SELECT rowid, a FROM t6 WHERE -5.5<rowid}
 } {-5 5 -4 4 -3 3 -2 2 -1 1}
 do_test rowid-10.38 {
   execsql {SELECT rowid, a FROM t6 WHERE -5.5<rowid ORDER BY rowid DESC}
 } {-1 1 -2 2 -3 3 -4 4 -5 5}
 do_test rowid-10.39 {
   execsql {SELECT rowid, a FROM t6 WHERE rowid<=-5.5}
 } {-8 8 -7 7 -6 6}
 do_test rowid-10.40 {
   execsql {SELECT rowid, a FROM t6 WHERE rowid<=-5.5 ORDER BY rowid DESC}
 } {-6 6 -7 7 -8 8}
 do_test rowid-10.41 {
   execsql {SELECT rowid, a FROM t6 WHERE -5.5>=rowid}
 } {-8 8 -7 7 -6 6}
 do_test rowid-10.42 {
   execsql {SELECT rowid, a FROM t6 WHERE -5.5>=rowid ORDER BY rowid DESC}
 } {-6 6 -7 7 -8 8}
 do_test rowid-10.43 {
   execsql {SELECT rowid, a FROM t6 WHERE rowid<-5.5}
 } {-8 8 -7 7 -6 6}
 do_test rowid-10.44 {
   execsql {SELECT rowid, a FROM t6 WHERE rowid<-5.5 ORDER BY rowid DESC}
 } {-6 6 -7 7 -8 8}
 do_test rowid-10.44 {
   execsql {SELECT rowid, a FROM t6 WHERE -5.5>rowid}
 } {-8 8 -7 7 -6 6}
 do_test rowid-10.46 {
   execsql {SELECT rowid, a FROM t6 WHERE -5.5>rowid ORDER BY rowid DESC}
 } {-6 6 -7 7 -8 8}

 # Comparison of rowid against string values.
 #
 do_test rowid-11.1 {
   execsql {SELECT rowid, a FROM t5 WHERE rowid>'abc'}
 } {}
 do_test rowid-11.2 {
   execsql {SELECT rowid, a FROM t5 WHERE rowid>='abc'}
 } {}
 do_test rowid-11.3 {
   execsql {SELECT rowid, a FROM t5 WHERE rowid<'abc'}
 } {1 1 2 2 3 3 4 4 5 5 6 6 7 7 8 8}
 do_test rowid-11.4 {
   execsql {SELECT rowid, a FROM t5 WHERE rowid<='abc'}
 } {1 1 2 2 3 3 4 4 5 5 6 6 7 7 8 8}

 # Test the automatic generation of rowids when the table already contains
 # a rowid with the maximum value.
 #
 # Once the maximum rowid is taken, rowids are normally chosen at
 # random.  By by reseting the random number generator, we can cause
 # the rowid guessing loop to collide with prior rowids, and test the
 # loop out to its limit of 100 iterations.  After 100 collisions, the
 # rowid guesser gives up and reports SQLITE_FULL.
 #
 do_test rowid-12.1 {
   execsql {
     CREATE TABLE t7(x INTEGER PRIMARY KEY, y);
     CREATE TABLE t7temp(a INTEGER PRIMARY KEY);
     INSERT INTO t7 VALUES(9223372036854775807,'a');
     SELECT y FROM t7;
   }
 } {a}
 do_test rowid-12.2 {
   db close
   sqlite3 db test.db
   save_prng_state
   execsql {
     INSERT INTO t7 VALUES(NULL,'b');
     SELECT x, y FROM t7 ORDER BY x;
   }
 } {/\d+ b 9223372036854775807 a/}
 execsql {INSERT INTO t7 VALUES(2,'y');}
 for {set i 1} {$i<100} {incr i} {
   do_test rowid-12.3.$i {
     db eval {DELETE FROM t7temp; INSERT INTO t7temp VALUES(1);}
     restore_prng_state
     execsql {
       INSERT INTO t7 VALUES(NULL,'x');
       SELECT count(*) FROM t7 WHERE y=='x';
     }
   } $i
 }
 do_test rowid-12.4 {
   db eval {DELETE FROM t7temp; INSERT INTO t7temp VALUES(1);}
   restore_prng_state
   catchsql {
     INSERT INTO t7 VALUES(NULL,'x');
   }
 } {1 {database or disk is full}}

 # INSERTs that happen inside of nested function calls are recorded
 # by last_insert_rowid.
 #
 proc rowid_addrow_func {n} {
   db eval {INSERT INTO t13(rowid,x) VALUES($n,$n*$n)}
   return [db last_insert_rowid]
 }
 db function addrow rowid_addrow_func
 do_execsql_test rowid-13.1 {
   CREATE TABLE t13(x);
   INSERT INTO t13(rowid,x) VALUES(1234,5);
   SELECT rowid, x, addrow(rowid+1000), '|' FROM t13 LIMIT 3;
   SELECT last_insert_rowid();
 } {1234 5 2234 | 2234 4990756 3234 | 3234 10458756 4234 | 4234}

 finish_test
	# 2001 September 15
	#
	# The author disclaims copyright to this source code. In place of
	# a legal notice, here is a blessing:
	#
	# May you do good and not evil.
	# May you find forgiveness for yourself and forgive others.
	# May you share freely, never taking more than you give.
	#
	#***********************************************************************
	# This file implements regression tests for SQLite library. The
	# focus of this file is testing the magic ROWID column that is
	# found on all tables.
	#
	# EVIDENCE-OF: R-36924-43758 By default, every row in SQLite has a
	# special column, usually called the "rowid", that uniquely identifies
	# that row within the table.

	set testdir [file dirname $argv0]
	source $testdir/tester.tcl

	# Basic ROWID functionality tests.
	#
	do_test rowid-1.1 {
	execsql {
	CREATE TABLE t1(x int, y int);
	INSERT INTO t1 VALUES(1,2);
	INSERT INTO t1 VALUES(3,4);
	SELECT x FROM t1 ORDER BY y;
	}
	} {1 3}
	do_test rowid-1.2 {
	set r [execsql {SELECT rowid FROM t1 ORDER BY x}]
	global x2rowid rowid2x
	set x2rowid(1) [lindex $r 0]
	set x2rowid(3) [lindex $r 1]
	set rowid2x($x2rowid(1)) 1
	set rowid2x($x2rowid(3)) 3
	llength $r
	} {2}
	do_test rowid-1.3 {
	global x2rowid
	set sql "SELECT x FROM t1 WHERE rowid==$x2rowid(1)"
	execsql $sql
	} {1}
	do_test rowid-1.4 {
	global x2rowid
	set sql "SELECT x FROM t1 WHERE rowid==$x2rowid(3)"
	execsql $sql
	} {3}
	do_test rowid-1.5 {
	global x2rowid
	set sql "SELECT x FROM t1 WHERE oid==$x2rowid(1)"
	execsql $sql
	} {1}
	do_test rowid-1.6 {
	global x2rowid
	set sql "SELECT x FROM t1 WHERE OID==$x2rowid(3)"
	execsql $sql
	} {3}
	do_test rowid-1.7 {
	global x2rowid
	set sql "SELECT x FROM t1 WHERE _rowid_==$x2rowid(1)"
	execsql $sql
	} {1}
	do_test rowid-1.7.1 {
	while 1 {
	set norow [expr {int(rand()*1000000)}]
	if {$norow!=$x2rowid(1) && $norow!=$x2rowid(3)} break
	}
	execsql "SELECT x FROM t1 WHERE rowid=$norow"
	} {}
	do_test rowid-1.8 {
	global x2rowid
	set v [execsql {SELECT x, oid FROM t1 order by x}]
	set v2 [list 1 $x2rowid(1) 3 $x2rowid(3)]
	expr {$v==$v2}
	} {1}
	do_test rowid-1.9 {
	global x2rowid
	set v [execsql {SELECT x, RowID FROM t1 order by x}]
	set v2 [list 1 $x2rowid(1) 3 $x2rowid(3)]
	expr {$v==$v2}
	} {1}
	do_test rowid-1.10 {
	global x2rowid
	set v [execsql {SELECT x, _rowid_ FROM t1 order by x}]
	set v2 [list 1 $x2rowid(1) 3 $x2rowid(3)]
	expr {$v==$v2}
	} {1}

	# We can insert or update the ROWID column.
	#
	do_test rowid-2.1 {
	catchsql {
	INSERT INTO t1(rowid,x,y) VALUES(1234,5,6);
	SELECT rowid, * FROM t1;
	}
	} {0 {1 1 2 2 3 4 1234 5 6}}
	do_test rowid-2.2 {
	catchsql {
	UPDATE t1 SET rowid=12345 WHERE x==1;
	SELECT rowid, * FROM t1
	}
	} {0 {2 3 4 1234 5 6 12345 1 2}}
	do_test rowid-2.3 {
	catchsql {
	INSERT INTO t1(y,x,oid) VALUES(8,7,1235);
	SELECT rowid, * FROM t1 WHERE rowid>1000;
	}
	} {0 {1234 5 6 1235 7 8 12345 1 2}}
	do_test rowid-2.4 {
	catchsql {
	UPDATE t1 SET oid=12346 WHERE x==1;
	SELECT rowid, * FROM t1;
	}
	} {0 {2 3 4 1234 5 6 1235 7 8 12346 1 2}}
	do_test rowid-2.5 {
	catchsql {
	INSERT INTO t1(x,_rowid_,y) VALUES(9,1236,10);
	SELECT rowid, * FROM t1 WHERE rowid>1000;
	}
	} {0 {1234 5 6 1235 7 8 1236 9 10 12346 1 2}}
	do_test rowid-2.6 {
	catchsql {
	UPDATE t1 SET _rowid_=12347 WHERE x==1;
	SELECT rowid, * FROM t1 WHERE rowid>1000;
	}
	} {0 {1234 5 6 1235 7 8 1236 9 10 12347 1 2}}

	# But we can use ROWID in the WHERE clause of an UPDATE that does not
	# change the ROWID.
	#
	do_test rowid-2.7 {
	global x2rowid
	set sql "UPDATE t1 SET x=2 WHERE OID==$x2rowid(3)"
	execsql $sql
	execsql {SELECT x FROM t1 ORDER BY x}
	} {1 2 5 7 9}
	do_test rowid-2.8 {
	global x2rowid
	set sql "UPDATE t1 SET x=3 WHERE _rowid_==$x2rowid(3)"
	execsql $sql
	execsql {SELECT x FROM t1 ORDER BY x}
	} {1 3 5 7 9}

	# We cannot index by ROWID
	#
	do_test rowid-2.9 {
	set v [catch {execsql {CREATE INDEX idxt1 ON t1(rowid)}} msg]
	lappend v $msg
	} {1 {table t1 has no column named rowid}}
	do_test rowid-2.10 {
	set v [catch {execsql {CREATE INDEX idxt1 ON t1(_rowid_)}} msg]
	lappend v $msg
	} {1 {table t1 has no column named _rowid_}}
	do_test rowid-2.11 {
	set v [catch {execsql {CREATE INDEX idxt1 ON t1(oid)}} msg]
	lappend v $msg
	} {1 {table t1 has no column named oid}}
	do_test rowid-2.12 {
	set v [catch {execsql {CREATE INDEX idxt1 ON t1(x, rowid)}} msg]
	lappend v $msg
	} {1 {table t1 has no column named rowid}}

	# Columns defined in the CREATE statement override the buildin ROWID
	# column names.
	#
	do_test rowid-3.1 {
	execsql {
	CREATE TABLE t2(rowid int, x int, y int);
	INSERT INTO t2 VALUES(0,2,3);
	INSERT INTO t2 VALUES(4,5,6);
	INSERT INTO t2 VALUES(7,8,9);
	SELECT * FROM t2 ORDER BY x;
	}
	} {0 2 3 4 5 6 7 8 9}
	do_test rowid-3.2 {
	execsql {SELECT * FROM t2 ORDER BY rowid}
	} {0 2 3 4 5 6 7 8 9}
	do_test rowid-3.3 {
	execsql {SELECT rowid, x, y FROM t2 ORDER BY rowid}
	} {0 2 3 4 5 6 7 8 9}
	do_test rowid-3.4 {
	set r1 [execsql {SELECT _rowid_, rowid FROM t2 ORDER BY rowid}]
	foreach {a b c d e f} $r1 {}
	set r2 [execsql {SELECT _rowid_, rowid FROM t2 ORDER BY x DESC}]
	foreach {u v w x y z} $r2 {}
	expr {$u==$e && $w==$c && $y==$a}
	} {1}
	# sqlite3 v3 - do_probtest doesn't exist anymore?
	if 0 {
	do_probtest rowid-3.5 {
	set r1 [execsql {SELECT _rowid_, rowid FROM t2 ORDER BY rowid}]
	foreach {a b c d e f} $r1 {}
	expr {$a!=$b && $c!=$d && $e!=$f}
	} {1}
	}

	# Let's try some more complex examples, including some joins.
	#
	do_test rowid-4.1 {
	execsql {
	DELETE FROM t1;
	DELETE FROM t2;
	}
	for {set i 1} {$i<=50} {incr i} {
	execsql "INSERT INTO t1(x,y) VALUES($i,[expr {$i*$i}])"
	}
	execsql {INSERT INTO t2 SELECT _rowid_, xy, yy FROM t1}
	execsql {SELECT t2.y FROM t1, t2 WHERE t1.x==4 AND t1.rowid==t2.rowid}
	} {256}
	do_test rowid-4.2 {
	execsql {SELECT t2.y FROM t2, t1 WHERE t1.x==4 AND t1.rowid==t2.rowid}
	} {256}
	do_test rowid-4.2.1 {
	execsql {SELECT t2.y FROM t2, t1 WHERE t1.x==4 AND t1.oid==t2.rowid}
	} {256}
	do_test rowid-4.2.2 {
	execsql {SELECT t2.y FROM t2, t1 WHERE t1.x==4 AND t1._rowid_==t2.rowid}
	} {256}
	do_test rowid-4.2.3 {
	execsql {SELECT t2.y FROM t2, t1 WHERE t1.x==4 AND t2.rowid==t1.rowid}
	} {256}
	do_test rowid-4.2.4 {
	execsql {SELECT t2.y FROM t2, t1 WHERE t2.rowid==t1.oid AND t1.x==4}
	} {256}
	do_test rowid-4.2.5 {
	execsql {SELECT t2.y FROM t1, t2 WHERE t1.x==4 AND t1._rowid_==t2.rowid}
	} {256}
	do_test rowid-4.2.6 {
	execsql {SELECT t2.y FROM t1, t2 WHERE t1.x==4 AND t2.rowid==t1.rowid}
	} {256}
	do_test rowid-4.2.7 {
	execsql {SELECT t2.y FROM t1, t2 WHERE t2.rowid==t1.oid AND t1.x==4}
	} {256}
	do_test rowid-4.3 {
	execsql {CREATE INDEX idxt1 ON t1(x)}
	execsql {SELECT t2.y FROM t1, t2 WHERE t1.x==4 AND t1.rowid==t2.rowid}
	} {256}
	do_test rowid-4.3.1 {
	execsql {SELECT t2.y FROM t1, t2 WHERE t1.x==4 AND t1._rowid_==t2.rowid}
	} {256}
	do_test rowid-4.3.2 {
	execsql {SELECT t2.y FROM t1, t2 WHERE t2.rowid==t1.oid AND 4==t1.x}
	} {256}
	do_test rowid-4.4 {
	execsql {SELECT t2.y FROM t2, t1 WHERE t1.x==4 AND t1.rowid==t2.rowid}
	} {256}
	do_test rowid-4.4.1 {
	execsql {SELECT t2.y FROM t2, t1 WHERE t1.x==4 AND t1._rowid_==t2.rowid}
	} {256}
	do_test rowid-4.4.2 {
	execsql {SELECT t2.y FROM t2, t1 WHERE t2.rowid==t1.oid AND 4==t1.x}
	} {256}
	do_test rowid-4.5 {
	execsql {CREATE INDEX idxt2 ON t2(y)}
	set sqlite_search_count 0
	concat [execsql {
	SELECT t1.x FROM t2, t1
	WHERE t2.y==256 AND t1.rowid==t2.rowid
	}] $sqlite_search_count
	} {4 3}
	do_test rowid-4.5.1 {
	set sqlite_search_count 0
	concat [execsql {
	SELECT t1.x FROM t2, t1
	WHERE t1.OID==t2.rowid AND t2.y==81
	}] $sqlite_search_count
	} {3 3}
	do_test rowid-4.6 {
	execsql {
	SELECT t1.x FROM t1, t2
	WHERE t2.y==256 AND t1.rowid==t2.rowid
	}
	} {4}

	do_test rowid-5.1.1 {
	ifcapable subquery {
	execsql {DELETE FROM t1 WHERE _rowid_ IN (SELECT oid FROM t1 WHERE x>8)}
	} else {
	set oids [execsql {SELECT oid FROM t1 WHERE x>8}]
	set where "_rowid_ = [join $oids { OR _rowid_ = }]"
	execsql "DELETE FROM t1 WHERE $where"
	}
	} {}
	do_test rowid-5.1.2 {
	execsql {SELECT max(x) FROM t1}
	} {8}

	# Make sure a "WHERE rowid=X" clause works when there is no ROWID of X.
	#
	do_test rowid-6.1 {
	execsql {
	SELECT x FROM t1
	}
	} {1 2 3 4 5 6 7 8}
	do_test rowid-6.2 {
	for {set ::norow 1} {1} {incr ::norow} {
	if {[execsql "SELECT x FROM t1 WHERE rowid=$::norow"]==""} break
	}
	execsql [subst {
	DELETE FROM t1 WHERE rowid=$::norow
	}]
	} {}
	do_test rowid-6.3 {
	execsql {
	SELECT x FROM t1
	}
	} {1 2 3 4 5 6 7 8}

	# Beginning with version 2.3.4, SQLite computes rowids of new rows by
	# finding the maximum current rowid and adding one. It falls back to
	# the old random algorithm if the maximum rowid is the largest integer.
	# The following tests are for this new behavior.
	#
	do_test rowid-7.0 {
	execsql {
	DELETE FROM t1;
	DROP TABLE t2;
	DROP INDEX idxt1;
	INSERT INTO t1 VALUES(1,2);
	SELECT rowid, * FROM t1;
	}
	} {1 1 2}
	do_test rowid-7.1 {
	execsql {
	INSERT INTO t1 VALUES(99,100);
	SELECT rowid,* FROM t1
	}
	} {1 1 2 2 99 100}
	do_test rowid-7.2 {
	execsql {
	CREATE TABLE t2(a INTEGER PRIMARY KEY, b);
	INSERT INTO t2(b) VALUES(55);
	SELECT * FROM t2;
	}
	} {1 55}
	do_test rowid-7.3 {
	execsql {
	INSERT INTO t2(b) VALUES(66);
	SELECT * FROM t2;
	}
	} {1 55 2 66}
	do_test rowid-7.4 {
	execsql {
	INSERT INTO t2(a,b) VALUES(1000000,77);
	INSERT INTO t2(b) VALUES(88);
	SELECT * FROM t2;
	}
	} {1 55 2 66 1000000 77 1000001 88}
	do_test rowid-7.5 {
	execsql {
	INSERT INTO t2(a,b) VALUES(2147483647,99);
	INSERT INTO t2(b) VALUES(11);
	SELECT b FROM t2 ORDER BY b;
	}
	} {11 55 66 77 88 99}
	ifcapable subquery {
	do_test rowid-7.6 {
	execsql {
	SELECT b FROM t2 WHERE a NOT IN(1,2,1000000,1000001,2147483647);
	}
	} {11}
	do_test rowid-7.7 {
	execsql {
	INSERT INTO t2(b) VALUES(22);
	INSERT INTO t2(b) VALUES(33);
	INSERT INTO t2(b) VALUES(44);
	INSERT INTO t2(b) VALUES(55);
	SELECT b FROM t2 WHERE a NOT IN(1,2,1000000,1000001,2147483647)
	ORDER BY b;
	}
	} {11 22 33 44 55}
	}
	do_test rowid-7.8 {
	execsql {
	DELETE FROM t2 WHERE a!=2;
	INSERT INTO t2(b) VALUES(111);
	SELECT * FROM t2;
	}
	} {2 66 3 111}

	ifcapable {trigger} {
	# Make sure AFTER triggers that do INSERTs do not change the last_insert_rowid.
	# Ticket #290
	#
	do_test rowid-8.1 {
	execsql {
	CREATE TABLE t3(a integer primary key);
	CREATE TABLE t4(x);
	INSERT INTO t4 VALUES(1);
	CREATE TRIGGER r3 AFTER INSERT on t3 FOR EACH ROW BEGIN
	INSERT INTO t4 VALUES(NEW.a+10);
	END;
	SELECT * FROM t3;
	}
	} {}
	do_test rowid-8.2 {
	execsql {
	SELECT rowid, * FROM t4;
	}
	} {1 1}
	do_test rowid-8.3 {
	execsql {
	INSERT INTO t3 VALUES(123);
	SELECT last_insert_rowid();
	}
	} {123}
	do_test rowid-8.4 {
	execsql {
	SELECT * FROM t3;
	}
	} {123}
	do_test rowid-8.5 {
	execsql {
	SELECT rowid, * FROM t4;
	}
	} {1 1 2 133}
	do_test rowid-8.6 {
	execsql {
	INSERT INTO t3 VALUES(NULL);
	SELECT last_insert_rowid();
	}
	} {124}
	do_test rowid-8.7 {
	execsql {
	SELECT * FROM t3;
	}
	} {123 124}
	do_test rowid-8.8 {
	execsql {
	SELECT rowid, * FROM t4;
	}
	} {1 1 2 133 3 134}
	} ;# endif trigger

	# If triggers are not enable, simulate their effect for the tests that
	# follow.
	ifcapable {!trigger} {
	execsql {
	CREATE TABLE t3(a integer primary key);
	INSERT INTO t3 VALUES(123);
	INSERT INTO t3 VALUES(124);
	}
	}

	# ticket #377: Comparison between integer primiary key and floating point
	# values.
	#
	do_test rowid-9.1 {
	execsql {
	SELECT * FROM t3 WHERE a<123.5
	}
	} {123}
	do_test rowid-9.2 {
	execsql {
	SELECT * FROM t3 WHERE a<124.5
	}
	} {123 124}
	do_test rowid-9.3 {
	execsql {
	SELECT * FROM t3 WHERE a>123.5
	}
	} {124}
	do_test rowid-9.4 {
	execsql {
	SELECT * FROM t3 WHERE a>122.5
	}
	} {123 124}
	do_test rowid-9.5 {
	execsql {
	SELECT * FROM t3 WHERE a==123.5
	}
	} {}
	do_test rowid-9.6 {
	execsql {
	SELECT * FROM t3 WHERE a==123.000
	}
	} {123}
	do_test rowid-9.7 {
	execsql {
	SELECT * FROM t3 WHERE a>100.5 AND a<200.5
	}
	} {123 124}
	do_test rowid-9.8 {
	execsql {
	SELECT * FROM t3 WHERE a>'xyz';
	}
	} {}
	do_test rowid-9.9 {
	execsql {
	SELECT * FROM t3 WHERE a<'xyz';
	}
	} {123 124}
	do_test rowid-9.10 {
	execsql {
	SELECT * FROM t3 WHERE a>=122.9 AND a<=123.1
	}
	} {123}

	# Ticket #567. Comparisons of ROWID or integery primary key against
	# floating point numbers still do not always work.
	#
	do_test rowid-10.1 {
	execsql {
	CREATE TABLE t5(a);
	INSERT INTO t5 VALUES(1);
	INSERT INTO t5 VALUES(2);
	INSERT INTO t5 SELECT a+2 FROM t5;
	INSERT INTO t5 SELECT a+4 FROM t5;
	SELECT rowid, * FROM t5;
	}
	} {1 1 2 2 3 3 4 4 5 5 6 6 7 7 8 8}
	do_test rowid-10.2 {
	execsql {SELECT rowid, a FROM t5 WHERE rowid>=5.5}
	} {6 6 7 7 8 8}
	do_test rowid-10.3 {
	execsql {SELECT rowid, a FROM t5 WHERE rowid>=5.0}
	} {5 5 6 6 7 7 8 8}
	do_test rowid-10.4 {
	execsql {SELECT rowid, a FROM t5 WHERE rowid>5.5}
	} {6 6 7 7 8 8}
	do_test rowid-10.3.2 {
	execsql {SELECT rowid, a FROM t5 WHERE rowid>5.0}
	} {6 6 7 7 8 8}
	do_test rowid-10.5 {
	execsql {SELECT rowid, a FROM t5 WHERE 5.5<=rowid}
	} {6 6 7 7 8 8}
	do_test rowid-10.6 {
	execsql {SELECT rowid, a FROM t5 WHERE 5.5<rowid}
	} {6 6 7 7 8 8}
	do_test rowid-10.7 {
	execsql {SELECT rowid, a FROM t5 WHERE rowid<=5.5}
	} {1 1 2 2 3 3 4 4 5 5}
	do_test rowid-10.8 {
	execsql {SELECT rowid, a FROM t5 WHERE rowid<5.5}
	} {1 1 2 2 3 3 4 4 5 5}
	do_test rowid-10.9 {
	execsql {SELECT rowid, a FROM t5 WHERE 5.5>=rowid}
	} {1 1 2 2 3 3 4 4 5 5}
	do_test rowid-10.10 {
	execsql {SELECT rowid, a FROM t5 WHERE 5.5>rowid}
	} {1 1 2 2 3 3 4 4 5 5}
	do_test rowid-10.11 {
	execsql {SELECT rowid, a FROM t5 WHERE rowid>=5.5 ORDER BY rowid DESC}
	} {8 8 7 7 6 6}
	do_test rowid-10.11.2 {
	execsql {SELECT rowid, a FROM t5 WHERE rowid>=5.0 ORDER BY rowid DESC}
	} {8 8 7 7 6 6 5 5}
	do_test rowid-10.12 {
	execsql {SELECT rowid, a FROM t5 WHERE rowid>5.5 ORDER BY rowid DESC}
	} {8 8 7 7 6 6}
	do_test rowid-10.12.2 {
	execsql {SELECT rowid, a FROM t5 WHERE rowid>5.0 ORDER BY rowid DESC}
	} {8 8 7 7 6 6}
	do_test rowid-10.13 {
	execsql {SELECT rowid, a FROM t5 WHERE 5.5<=rowid ORDER BY rowid DESC}
	} {8 8 7 7 6 6}
	do_test rowid-10.14 {
	execsql {SELECT rowid, a FROM t5 WHERE 5.5<rowid ORDER BY rowid DESC}
	} {8 8 7 7 6 6}
	do_test rowid-10.15 {
	execsql {SELECT rowid, a FROM t5 WHERE rowid<=5.5 ORDER BY rowid DESC}
	} {5 5 4 4 3 3 2 2 1 1}
	do_test rowid-10.16 {
	execsql {SELECT rowid, a FROM t5 WHERE rowid<5.5 ORDER BY rowid DESC}
	} {5 5 4 4 3 3 2 2 1 1}
	do_test rowid-10.17 {
	execsql {SELECT rowid, a FROM t5 WHERE 5.5>=rowid ORDER BY rowid DESC}
	} {5 5 4 4 3 3 2 2 1 1}
	do_test rowid-10.18 {
	execsql {SELECT rowid, a FROM t5 WHERE 5.5>rowid ORDER BY rowid DESC}
	} {5 5 4 4 3 3 2 2 1 1}

	do_test rowid-10.30 {
	execsql {
	CREATE TABLE t6(a);
	INSERT INTO t6(rowid,a) SELECT -a,a FROM t5;
	SELECT rowid, * FROM t6;
	}
	} {-8 8 -7 7 -6 6 -5 5 -4 4 -3 3 -2 2 -1 1}
	do_test rowid-10.31.1 {
	execsql {SELECT rowid, a FROM t6 WHERE rowid>=-5.5}
	} {-5 5 -4 4 -3 3 -2 2 -1 1}
	do_test rowid-10.31.2 {
	execsql {SELECT rowid, a FROM t6 WHERE rowid>=-5.0}
	} {-5 5 -4 4 -3 3 -2 2 -1 1}
	do_test rowid-10.32.1 {
	execsql {SELECT rowid, a FROM t6 WHERE rowid>=-5.5 ORDER BY rowid DESC}
	} {-1 1 -2 2 -3 3 -4 4 -5 5}
	do_test rowid-10.32.1 {
	execsql {SELECT rowid, a FROM t6 WHERE rowid>=-5.0 ORDER BY rowid DESC}
	} {-1 1 -2 2 -3 3 -4 4 -5 5}
	do_test rowid-10.33 {
	execsql {SELECT rowid, a FROM t6 WHERE -5.5<=rowid}
	} {-5 5 -4 4 -3 3 -2 2 -1 1}
	do_test rowid-10.34 {
	execsql {SELECT rowid, a FROM t6 WHERE -5.5<=rowid ORDER BY rowid DESC}
	} {-1 1 -2 2 -3 3 -4 4 -5 5}
	do_test rowid-10.35.1 {
	execsql {SELECT rowid, a FROM t6 WHERE rowid>-5.5}
	} {-5 5 -4 4 -3 3 -2 2 -1 1}
	do_test rowid-10.35.2 {
	execsql {SELECT rowid, a FROM t6 WHERE rowid>-5.0}
	} {-4 4 -3 3 -2 2 -1 1}
	do_test rowid-10.36.1 {
	execsql {SELECT rowid, a FROM t6 WHERE rowid>-5.5 ORDER BY rowid DESC}
	} {-1 1 -2 2 -3 3 -4 4 -5 5}
	do_test rowid-10.36.2 {
	execsql {SELECT rowid, a FROM t6 WHERE rowid>-5.0 ORDER BY rowid DESC}
	} {-1 1 -2 2 -3 3 -4 4}
	do_test rowid-10.37 {
	execsql {SELECT rowid, a FROM t6 WHERE -5.5<rowid}
	} {-5 5 -4 4 -3 3 -2 2 -1 1}
	do_test rowid-10.38 {
	execsql {SELECT rowid, a FROM t6 WHERE -5.5<rowid ORDER BY rowid DESC}
	} {-1 1 -2 2 -3 3 -4 4 -5 5}
	do_test rowid-10.39 {
	execsql {SELECT rowid, a FROM t6 WHERE rowid<=-5.5}
	} {-8 8 -7 7 -6 6}
	do_test rowid-10.40 {
	execsql {SELECT rowid, a FROM t6 WHERE rowid<=-5.5 ORDER BY rowid DESC}
	} {-6 6 -7 7 -8 8}
	do_test rowid-10.41 {
	execsql {SELECT rowid, a FROM t6 WHERE -5.5>=rowid}
	} {-8 8 -7 7 -6 6}
	do_test rowid-10.42 {
	execsql {SELECT rowid, a FROM t6 WHERE -5.5>=rowid ORDER BY rowid DESC}
	} {-6 6 -7 7 -8 8}
	do_test rowid-10.43 {
	execsql {SELECT rowid, a FROM t6 WHERE rowid<-5.5}
	} {-8 8 -7 7 -6 6}
	do_test rowid-10.44 {
	execsql {SELECT rowid, a FROM t6 WHERE rowid<-5.5 ORDER BY rowid DESC}
	} {-6 6 -7 7 -8 8}
	do_test rowid-10.44 {
	execsql {SELECT rowid, a FROM t6 WHERE -5.5>rowid}
	} {-8 8 -7 7 -6 6}
	do_test rowid-10.46 {
	execsql {SELECT rowid, a FROM t6 WHERE -5.5>rowid ORDER BY rowid DESC}
	} {-6 6 -7 7 -8 8}

	# Comparison of rowid against string values.
	#
	do_test rowid-11.1 {
	execsql {SELECT rowid, a FROM t5 WHERE rowid>'abc'}
	} {}
	do_test rowid-11.2 {
	execsql {SELECT rowid, a FROM t5 WHERE rowid>='abc'}
	} {}
	do_test rowid-11.3 {
	execsql {SELECT rowid, a FROM t5 WHERE rowid<'abc'}
	} {1 1 2 2 3 3 4 4 5 5 6 6 7 7 8 8}
	do_test rowid-11.4 {
	execsql {SELECT rowid, a FROM t5 WHERE rowid<='abc'}
	} {1 1 2 2 3 3 4 4 5 5 6 6 7 7 8 8}

	# Test the automatic generation of rowids when the table already contains
	# a rowid with the maximum value.
	#
	# Once the maximum rowid is taken, rowids are normally chosen at
	# random. By by reseting the random number generator, we can cause
	# the rowid guessing loop to collide with prior rowids, and test the
	# loop out to its limit of 100 iterations. After 100 collisions, the
	# rowid guesser gives up and reports SQLITE_FULL.
	#
	do_test rowid-12.1 {
	execsql {
	CREATE TABLE t7(x INTEGER PRIMARY KEY, y);
	CREATE TABLE t7temp(a INTEGER PRIMARY KEY);
	INSERT INTO t7 VALUES(9223372036854775807,'a');
	SELECT y FROM t7;
	}
	} {a}
	do_test rowid-12.2 {
	db close
	sqlite3 db test.db
	save_prng_state
	execsql {
	INSERT INTO t7 VALUES(NULL,'b');
	SELECT x, y FROM t7 ORDER BY x;
	}
	} {/\d+ b 9223372036854775807 a/}
	execsql {INSERT INTO t7 VALUES(2,'y');}
	for {set i 1} {$i<100} {incr i} {
	do_test rowid-12.3.$i {
	db eval {DELETE FROM t7temp; INSERT INTO t7temp VALUES(1);}
	restore_prng_state
	execsql {
	INSERT INTO t7 VALUES(NULL,'x');
	SELECT count(*) FROM t7 WHERE y=='x';
	}
	} $i
	}
	do_test rowid-12.4 {
	db eval {DELETE FROM t7temp; INSERT INTO t7temp VALUES(1);}
	restore_prng_state
	catchsql {
	INSERT INTO t7 VALUES(NULL,'x');
	}
	} {1 {database or disk is full}}

	# INSERTs that happen inside of nested function calls are recorded
	# by last_insert_rowid.
	#
	proc rowid_addrow_func {n} {
	db eval {INSERT INTO t13(rowid,x) VALUES($n,$n*$n)}
	return [db last_insert_rowid]
	}
	db function addrow rowid_addrow_func
	do_execsql_test rowid-13.1 {
	CREATE TABLE t13(x);
	INSERT INTO t13(rowid,x) VALUES(1234,5);
	SELECT rowid, x, addrow(rowid+1000), '\|' FROM t13 LIMIT 3;
	SELECT last_insert_rowid();
	} {1234 5 2234 \| 2234 4990756 3234 \| 3234 10458756 4234 \| 4234}

	finish_test