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 # $Id: rowid.test,v 1.20 2008/01/19 20:11:26 drh Exp $

    16 

    17 set testdir [file dirname $argv0]

    18 source $testdir/tester.tcl

    19 

    20 # Basic ROWID functionality tests.

    21 #

    22 do_test rowid-1.1 {

    23   execsql {

    24     CREATE TABLE t1(x int, y int);

    25     INSERT INTO t1 VALUES(1,2);

    26     INSERT INTO t1 VALUES(3,4);

    27     SELECT x FROM t1 ORDER BY y;

    28   }

    29 } {1 3}

    30 do_test rowid-1.2 {

    31   set r [execsql {SELECT rowid FROM t1 ORDER BY x}]

    32   global x2rowid rowid2x

    33   set x2rowid(1) [lindex $r 0]

    34   set x2rowid(3) [lindex $r 1]

    35   set rowid2x($x2rowid(1)) 1

    36   set rowid2x($x2rowid(3)) 3

    37   llength $r

    38 } {2}

    39 do_test rowid-1.3 {

    40   global x2rowid

    41   set sql "SELECT x FROM t1 WHERE rowid==$x2rowid(1)"

    42   execsql $sql

    43 } {1}

    44 do_test rowid-1.4 {

    45   global x2rowid

    46   set sql "SELECT x FROM t1 WHERE rowid==$x2rowid(3)"

    47   execsql $sql

    48 } {3}

    49 do_test rowid-1.5 {

    50   global x2rowid

    51   set sql "SELECT x FROM t1 WHERE oid==$x2rowid(1)"

    52   execsql $sql

    53 } {1}

    54 do_test rowid-1.6 {

    55   global x2rowid

    56   set sql "SELECT x FROM t1 WHERE OID==$x2rowid(3)"

    57   execsql $sql

    58 } {3}

    59 do_test rowid-1.7 {

    60   global x2rowid

    61   set sql "SELECT x FROM t1 WHERE _rowid_==$x2rowid(1)"

    62   execsql $sql

    63 } {1}

    64 do_test rowid-1.7.1 {

    65   while 1 {

    66     set norow [expr {int(rand()*1000000)}]

    67     if {$norow!=$x2rowid(1) && $norow!=$x2rowid(3)} break

    68   }

    69   execsql "SELECT x FROM t1 WHERE rowid=$norow"

    70 } {}

    71 do_test rowid-1.8 {

    72   global x2rowid

    73   set v [execsql {SELECT x, oid FROM t1 order by x}]

    74   set v2 [list 1 $x2rowid(1) 3 $x2rowid(3)]

    75   expr {$v==$v2}

    76 } {1}

    77 do_test rowid-1.9 {

    78   global x2rowid

    79   set v [execsql {SELECT x, RowID FROM t1 order by x}]

    80   set v2 [list 1 $x2rowid(1) 3 $x2rowid(3)]

    81   expr {$v==$v2}

    82 } {1}

    83 do_test rowid-1.10 {

    84   global x2rowid

    85   set v [execsql {SELECT x, _rowid_ FROM t1 order by x}]

    86   set v2 [list 1 $x2rowid(1) 3 $x2rowid(3)]

    87   expr {$v==$v2}

    88 } {1}

    89 

    90 # We can insert or update the ROWID column.

    91 #

    92 do_test rowid-2.1 {

    93   catchsql {

    94     INSERT INTO t1(rowid,x,y) VALUES(1234,5,6);

    95     SELECT rowid, * FROM t1;

    96   }

    97 } {0 {1 1 2 2 3 4 1234 5 6}}

    98 do_test rowid-2.2 {

    99   catchsql {

   100     UPDATE t1 SET rowid=12345 WHERE x==1;

   101     SELECT rowid, * FROM t1

   102   }

   103 } {0 {2 3 4 1234 5 6 12345 1 2}}

   104 do_test rowid-2.3 {

   105   catchsql {

   106     INSERT INTO t1(y,x,oid) VALUES(8,7,1235);

   107     SELECT rowid, * FROM t1 WHERE rowid>1000;

   108   }

   109 } {0 {1234 5 6 1235 7 8 12345 1 2}}

   110 do_test rowid-2.4 {

   111   catchsql {

   112     UPDATE t1 SET oid=12346 WHERE x==1;

   113     SELECT rowid, * FROM t1;

   114   }

   115 } {0 {2 3 4 1234 5 6 1235 7 8 12346 1 2}}

   116 do_test rowid-2.5 {

   117   catchsql {

   118     INSERT INTO t1(x,_rowid_,y) VALUES(9,1236,10);

   119     SELECT rowid, * FROM t1 WHERE rowid>1000;

   120   }

   121 } {0 {1234 5 6 1235 7 8 1236 9 10 12346 1 2}}

   122 do_test rowid-2.6 {

   123   catchsql {

   124     UPDATE t1 SET _rowid_=12347 WHERE x==1;

   125     SELECT rowid, * FROM t1 WHERE rowid>1000;

   126   }

   127 } {0 {1234 5 6 1235 7 8 1236 9 10 12347 1 2}}

   128 

   129 # But we can use ROWID in the WHERE clause of an UPDATE that does not

   130 # change the ROWID.

   131 #

   132 do_test rowid-2.7 {

   133   global x2rowid

   134   set sql "UPDATE t1 SET x=2 WHERE OID==$x2rowid(3)"

   135   execsql $sql

   136   execsql {SELECT x FROM t1 ORDER BY x}

   137 } {1 2 5 7 9}

   138 do_test rowid-2.8 {

   139   global x2rowid

   140   set sql "UPDATE t1 SET x=3 WHERE _rowid_==$x2rowid(3)"

   141   execsql $sql

   142   execsql {SELECT x FROM t1 ORDER BY x}

   143 } {1 3 5 7 9}

   144 

   145 # We cannot index by ROWID

   146 #

   147 do_test rowid-2.9 {

   148   set v [catch {execsql {CREATE INDEX idxt1 ON t1(rowid)}} msg]

   149   lappend v $msg

   150 } {1 {table t1 has no column named rowid}}

   151 do_test rowid-2.10 {

   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_}}

   155 do_test rowid-2.11 {

   156   set v [catch {execsql {CREATE INDEX idxt1 ON t1(oid)}} msg]

   157   lappend v $msg

   158 } {1 {table t1 has no column named oid}}

   159 do_test rowid-2.12 {

   160   set v [catch {execsql {CREATE INDEX idxt1 ON t1(x, rowid)}} msg]

   161   lappend v $msg

   162 } {1 {table t1 has no column named rowid}}

   163 

   164 # Columns defined in the CREATE statement override the buildin ROWID

   165 # column names.

   166 #

   167 do_test rowid-3.1 {

   168   execsql {

   169     CREATE TABLE t2(rowid int, x int, y int);

   170     INSERT INTO t2 VALUES(0,2,3);

   171     INSERT INTO t2 VALUES(4,5,6);

   172     INSERT INTO t2 VALUES(7,8,9);

   173     SELECT * FROM t2 ORDER BY x;

   174   }

   175 } {0 2 3 4 5 6 7 8 9}

   176 do_test rowid-3.2 {

   177   execsql {SELECT * FROM t2 ORDER BY rowid}

   178 } {0 2 3 4 5 6 7 8 9}

   179 do_test rowid-3.3 {

   180   execsql {SELECT rowid, x, y FROM t2 ORDER BY rowid}

   181 } {0 2 3 4 5 6 7 8 9}

   182 do_test rowid-3.4 {

   183   set r1 [execsql {SELECT _rowid_, rowid FROM t2 ORDER BY rowid}]

   184   foreach {a b c d e f} $r1 {}

   185   set r2 [execsql {SELECT _rowid_, rowid FROM t2 ORDER BY x DESC}]

   186   foreach {u v w x y z} $r2 {}

   187   expr {$u==$e && $w==$c && $y==$a}

   188 } {1}

   189 # sqlite3 v3 - do_probtest doesn't exist anymore?

   190 if 0 {

   191 do_probtest rowid-3.5 {

   192   set r1 [execsql {SELECT _rowid_, rowid FROM t2 ORDER BY rowid}]

   193   foreach {a b c d e f} $r1 {}

   194   expr {$a!=$b && $c!=$d && $e!=$f}

   195 } {1}

   196 }

   197 

   198 # Let's try some more complex examples, including some joins.

   199 #

   200 do_test rowid-4.1 {

   201   execsql {

   202     DELETE FROM t1;

   203     DELETE FROM t2;

   204   }

   205   for {set i 1} {$i<=50} {incr i} {

   206     execsql "INSERT INTO t1(x,y) VALUES($i,[expr {$i*$i}])"

   207   }

   208   execsql {INSERT INTO t2 SELECT _rowid_, x*y, y*y FROM t1}

   209   execsql {SELECT t2.y FROM t1, t2 WHERE t1.x==4 AND t1.rowid==t2.rowid}

   210 } {256}

   211 do_test rowid-4.2 {

   212   execsql {SELECT t2.y FROM t2, t1 WHERE t1.x==4 AND t1.rowid==t2.rowid}

   213 } {256}

   214 do_test rowid-4.2.1 {

   215   execsql {SELECT t2.y FROM t2, t1 WHERE t1.x==4 AND t1.oid==t2.rowid}

   216 } {256}

   217 do_test rowid-4.2.2 {

   218   execsql {SELECT t2.y FROM t2, t1 WHERE t1.x==4 AND t1._rowid_==t2.rowid}

   219 } {256}

   220 do_test rowid-4.2.3 {

   221   execsql {SELECT t2.y FROM t2, t1 WHERE t1.x==4 AND t2.rowid==t1.rowid}

   222 } {256}

   223 do_test rowid-4.2.4 {

   224   execsql {SELECT t2.y FROM t2, t1 WHERE t2.rowid==t1.oid AND t1.x==4}

   225 } {256}

   226 do_test rowid-4.2.5 {

   227   execsql {SELECT t2.y FROM t1, t2 WHERE t1.x==4 AND t1._rowid_==t2.rowid}

   228 } {256}

   229 do_test rowid-4.2.6 {

   230   execsql {SELECT t2.y FROM t1, t2 WHERE t1.x==4 AND t2.rowid==t1.rowid}

   231 } {256}

   232 do_test rowid-4.2.7 {

   233   execsql {SELECT t2.y FROM t1, t2 WHERE t2.rowid==t1.oid AND t1.x==4}

   234 } {256}

   235 do_test rowid-4.3 {

   236   execsql {CREATE INDEX idxt1 ON t1(x)}

   237   execsql {SELECT t2.y FROM t1, t2 WHERE t1.x==4 AND t1.rowid==t2.rowid}

   238 } {256}

   239 do_test rowid-4.3.1 {

   240   execsql {SELECT t2.y FROM t1, t2 WHERE t1.x==4 AND t1._rowid_==t2.rowid}

   241 } {256}

   242 do_test rowid-4.3.2 {

   243   execsql {SELECT t2.y FROM t1, t2 WHERE t2.rowid==t1.oid AND 4==t1.x}

   244 } {256}

   245 do_test rowid-4.4 {

   246   execsql {SELECT t2.y FROM t2, t1 WHERE t1.x==4 AND t1.rowid==t2.rowid}

   247 } {256}

   248 do_test rowid-4.4.1 {

   249   execsql {SELECT t2.y FROM t2, t1 WHERE t1.x==4 AND t1._rowid_==t2.rowid}

   250 } {256}

   251 do_test rowid-4.4.2 {

   252   execsql {SELECT t2.y FROM t2, t1 WHERE t2.rowid==t1.oid AND 4==t1.x}

   253 } {256}

   254 do_test rowid-4.5 {

   255   execsql {CREATE INDEX idxt2 ON t2(y)}

   256   set sqlite_search_count 0

   257   concat [execsql {

   258     SELECT t1.x FROM t2, t1 

   259     WHERE t2.y==256 AND t1.rowid==t2.rowid

   260   }] $sqlite_search_count

   261 } {4 3}

   262 do_test rowid-4.5.1 {

   263   set sqlite_search_count 0

   264   concat [execsql {

   265     SELECT t1.x FROM t2, t1 

   266     WHERE t1.OID==t2.rowid AND t2.y==81

   267   }] $sqlite_search_count

   268 } {3 3}

   269 do_test rowid-4.6 {

   270   execsql {

   271     SELECT t1.x FROM t1, t2

   272     WHERE t2.y==256 AND t1.rowid==t2.rowid

   273   }

   274 } {4}

   275 

   276 do_test rowid-5.1.1 {

   277   ifcapable subquery {

   278     execsql {DELETE FROM t1 WHERE _rowid_ IN (SELECT oid FROM t1 WHERE x>8)}

   279   } else {

   280     set oids [execsql {SELECT oid FROM t1 WHERE x>8}]

   281     set where "_rowid_ = [join $oids { OR _rowid_ = }]"

   282     execsql "DELETE FROM t1 WHERE $where"

   283   }

   284 } {}

   285 do_test rowid-5.1.2 {

   286   execsql {SELECT max(x) FROM t1}

   287 } {8}

   288 

   289 # Make sure a "WHERE rowid=X" clause works when there is no ROWID of X.

   290 #

   291 do_test rowid-6.1 {

   292   execsql {

   293     SELECT x FROM t1

   294   }

   295 } {1 2 3 4 5 6 7 8}

   296 do_test rowid-6.2 {

   297   for {set ::norow 1} {1} {incr ::norow} {

   298     if {[execsql "SELECT x FROM t1 WHERE rowid=$::norow"]==""}  break

   299   }

   300   execsql [subst {

   301     DELETE FROM t1 WHERE rowid=$::norow

   302   }]

   303 } {}

   304 do_test rowid-6.3 {

   305   execsql {

   306     SELECT x FROM t1

   307   }

   308 } {1 2 3 4 5 6 7 8}

   309 

   310 # Beginning with version 2.3.4, SQLite computes rowids of new rows by

   311 # finding the maximum current rowid and adding one.  It falls back to

   312 # the old random algorithm if the maximum rowid is the largest integer.

   313 # The following tests are for this new behavior.

   314 #

   315 do_test rowid-7.0 {

   316   execsql {

   317     DELETE FROM t1;

   318     DROP TABLE t2;

   319     DROP INDEX idxt1;

   320     INSERT INTO t1 VALUES(1,2);

   321     SELECT rowid, * FROM t1;

   322   }

   323 } {1 1 2}

   324 do_test rowid-7.1 {

   325   execsql {

   326     INSERT INTO t1 VALUES(99,100);

   327     SELECT rowid,* FROM t1

   328   }

   329 } {1 1 2 2 99 100}

   330 do_test rowid-7.2 {

   331   execsql {

   332     CREATE TABLE t2(a INTEGER PRIMARY KEY, b);

   333     INSERT INTO t2(b) VALUES(55);

   334     SELECT * FROM t2;

   335   }

   336 } {1 55}

   337 do_test rowid-7.3 {

   338   execsql {

   339     INSERT INTO t2(b) VALUES(66);

   340     SELECT * FROM t2;

   341   }

   342 } {1 55 2 66}

   343 do_test rowid-7.4 {

   344   execsql {

   345     INSERT INTO t2(a,b) VALUES(1000000,77);

   346     INSERT INTO t2(b) VALUES(88);

   347     SELECT * FROM t2;

   348   }

   349 } {1 55 2 66 1000000 77 1000001 88}

   350 do_test rowid-7.5 {

   351   execsql {

   352     INSERT INTO t2(a,b) VALUES(2147483647,99);

   353     INSERT INTO t2(b) VALUES(11);

   354     SELECT b FROM t2 ORDER BY b;

   355   }

   356 } {11 55 66 77 88 99}

   357 ifcapable subquery {

   358   do_test rowid-7.6 {

   359     execsql {

   360       SELECT b FROM t2 WHERE a NOT IN(1,2,1000000,1000001,2147483647);

   361     }

   362   } {11}

   363   do_test rowid-7.7 {

   364     execsql {

   365       INSERT INTO t2(b) VALUES(22);

   366       INSERT INTO t2(b) VALUES(33);

   367       INSERT INTO t2(b) VALUES(44);

   368       INSERT INTO t2(b) VALUES(55);

   369       SELECT b FROM t2 WHERE a NOT IN(1,2,1000000,1000001,2147483647) 

   370           ORDER BY b;

   371     }

   372   } {11 22 33 44 55}

   373 }

   374 do_test rowid-7.8 {

   375   execsql {

   376     DELETE FROM t2 WHERE a!=2;

   377     INSERT INTO t2(b) VALUES(111);

   378     SELECT * FROM t2;

   379   }

   380 } {2 66 3 111}

   381 

   382 ifcapable {trigger} {

   383 # Make sure AFTER triggers that do INSERTs do not change the last_insert_rowid.

   384 # Ticket #290

   385 #

   386 do_test rowid-8.1 {

   387   execsql {

   388     CREATE TABLE t3(a integer primary key);

   389     CREATE TABLE t4(x);

   390     INSERT INTO t4 VALUES(1);

   391     CREATE TRIGGER r3 AFTER INSERT on t3 FOR EACH ROW BEGIN

   392       INSERT INTO t4 VALUES(NEW.a+10);

   393     END;

   394     SELECT * FROM t3;

   395   }

   396 } {}

   397 do_test rowid-8.2 {

   398   execsql {

   399     SELECT rowid, * FROM t4;

   400   }

   401 } {1 1}

   402 do_test rowid-8.3 {

   403   execsql {

   404     INSERT INTO t3 VALUES(123);

   405     SELECT last_insert_rowid();

   406   }

   407 } {123}

   408 do_test rowid-8.4 {

   409   execsql {

   410     SELECT * FROM t3;

   411   }

   412 } {123}

   413 do_test rowid-8.5 {

   414   execsql {

   415     SELECT rowid, * FROM t4;

   416   }

   417 } {1 1 2 133}

   418 do_test rowid-8.6 {

   419   execsql {

   420     INSERT INTO t3 VALUES(NULL);

   421     SELECT last_insert_rowid();

   422   }

   423 } {124}

   424 do_test rowid-8.7 {

   425   execsql {

   426     SELECT * FROM t3;

   427   }

   428 } {123 124}

   429 do_test rowid-8.8 {

   430   execsql {

   431     SELECT rowid, * FROM t4;

   432   }

   433 } {1 1 2 133 3 134}

   434 } ;# endif trigger

   435 

   436 # If triggers are not enable, simulate their effect for the tests that

   437 # follow.

   438 ifcapable {!trigger} {

   439   execsql {

   440     CREATE TABLE t3(a integer primary key);

   441     INSERT INTO t3 VALUES(123);

   442     INSERT INTO t3 VALUES(124);

   443   }

   444 }

   445 

   446 # ticket #377: Comparison between integer primiary key and floating point

   447 # values.

   448 #

   449 do_test rowid-9.1 {

   450   execsql {

   451     SELECT * FROM t3 WHERE a<123.5

   452   }

   453 } {123}

   454 do_test rowid-9.2 {

   455   execsql {

   456     SELECT * FROM t3 WHERE a<124.5

   457   }

   458 } {123 124}

   459 do_test rowid-9.3 {

   460   execsql {

   461     SELECT * FROM t3 WHERE a>123.5

   462   }

   463 } {124}

   464 do_test rowid-9.4 {

   465   execsql {

   466     SELECT * FROM t3 WHERE a>122.5

   467   }

   468 } {123 124}

   469 do_test rowid-9.5 {

   470   execsql {

   471     SELECT * FROM t3 WHERE a==123.5

   472   }

   473 } {}

   474 do_test rowid-9.6 {

   475   execsql {

   476     SELECT * FROM t3 WHERE a==123.000

   477   }

   478 } {123}

   479 do_test rowid-9.7 {

   480   execsql {

   481     SELECT * FROM t3 WHERE a>100.5 AND a<200.5

   482   }

   483 } {123 124}

   484 do_test rowid-9.8 {

   485   execsql {

   486     SELECT * FROM t3 WHERE a>'xyz';

   487   }

   488 } {}

   489 do_test rowid-9.9 {

   490   execsql {

   491     SELECT * FROM t3 WHERE a<'xyz';

   492   }

   493 } {123 124}

   494 do_test rowid-9.10 {

   495   execsql {

   496     SELECT * FROM t3 WHERE a>=122.9 AND a<=123.1

   497   }

   498 } {123}

   499 

   500 # Ticket #567.  Comparisons of ROWID or integery primary key against

   501 # floating point numbers still do not always work.

   502 #

   503 do_test rowid-10.1 {

   504   execsql {

   505     CREATE TABLE t5(a);

   506     INSERT INTO t5 VALUES(1);

   507     INSERT INTO t5 VALUES(2);

   508     INSERT INTO t5 SELECT a+2 FROM t5;

   509     INSERT INTO t5 SELECT a+4 FROM t5;

   510     SELECT rowid, * FROM t5;

   511   }

   512 } {1 1 2 2 3 3 4 4 5 5 6 6 7 7 8 8}

   513 do_test rowid-10.2 {

   514   execsql {SELECT rowid, a FROM t5 WHERE rowid>=5.5}

   515 } {6 6 7 7 8 8}

   516 do_test rowid-10.3 {

   517   execsql {SELECT rowid, a FROM t5 WHERE rowid>=5.0}

   518 } {5 5 6 6 7 7 8 8}

   519 do_test rowid-10.4 {

   520   execsql {SELECT rowid, a FROM t5 WHERE rowid>5.5}

   521 } {6 6 7 7 8 8}

   522 do_test rowid-10.3.2 {

   523   execsql {SELECT rowid, a FROM t5 WHERE rowid>5.0}

   524 } {6 6 7 7 8 8}

   525 do_test rowid-10.5 {

   526   execsql {SELECT rowid, a FROM t5 WHERE 5.5<=rowid}

   527 } {6 6 7 7 8 8}

   528 do_test rowid-10.6 {

   529   execsql {SELECT rowid, a FROM t5 WHERE 5.5<rowid}

   530 } {6 6 7 7 8 8}

   531 do_test rowid-10.7 {

   532   execsql {SELECT rowid, a FROM t5 WHERE rowid<=5.5}

   533 } {1 1 2 2 3 3 4 4 5 5}

   534 do_test rowid-10.8 {

   535   execsql {SELECT rowid, a FROM t5 WHERE rowid<5.5}

   536 } {1 1 2 2 3 3 4 4 5 5}

   537 do_test rowid-10.9 {

   538   execsql {SELECT rowid, a FROM t5 WHERE 5.5>=rowid}

   539 } {1 1 2 2 3 3 4 4 5 5}

   540 do_test rowid-10.10 {

   541   execsql {SELECT rowid, a FROM t5 WHERE 5.5>rowid}

   542 } {1 1 2 2 3 3 4 4 5 5}

   543 do_test rowid-10.11 {

   544   execsql {SELECT rowid, a FROM t5 WHERE rowid>=5.5 ORDER BY rowid DESC}

   545 } {8 8 7 7 6 6}

   546 do_test rowid-10.11.2 {

   547   execsql {SELECT rowid, a FROM t5 WHERE rowid>=5.0 ORDER BY rowid DESC}

   548 } {8 8 7 7 6 6 5 5}

   549 do_test rowid-10.12 {

   550   execsql {SELECT rowid, a FROM t5 WHERE rowid>5.5 ORDER BY rowid DESC}

   551 } {8 8 7 7 6 6}

   552 do_test rowid-10.12.2 {

   553   execsql {SELECT rowid, a FROM t5 WHERE rowid>5.0 ORDER BY rowid DESC}

   554 } {8 8 7 7 6 6}

   555 do_test rowid-10.13 {

   556   execsql {SELECT rowid, a FROM t5 WHERE 5.5<=rowid ORDER BY rowid DESC}

   557 } {8 8 7 7 6 6}

   558 do_test rowid-10.14 {

   559   execsql {SELECT rowid, a FROM t5 WHERE 5.5<rowid ORDER BY rowid DESC}

   560 } {8 8 7 7 6 6}

   561 do_test rowid-10.15 {

   562   execsql {SELECT rowid, a FROM t5 WHERE rowid<=5.5 ORDER BY rowid DESC}

   563 } {5 5 4 4 3 3 2 2 1 1}

   564 do_test rowid-10.16 {

   565   execsql {SELECT rowid, a FROM t5 WHERE rowid<5.5 ORDER BY rowid DESC}

   566 } {5 5 4 4 3 3 2 2 1 1}

   567 do_test rowid-10.17 {

   568   execsql {SELECT rowid, a FROM t5 WHERE 5.5>=rowid ORDER BY rowid DESC}

   569 } {5 5 4 4 3 3 2 2 1 1}

   570 do_test rowid-10.18 {

   571   execsql {SELECT rowid, a FROM t5 WHERE 5.5>rowid ORDER BY rowid DESC}

   572 } {5 5 4 4 3 3 2 2 1 1}

   573 

   574 do_test rowid-10.30 {

   575   execsql {

   576     CREATE TABLE t6(a);

   577     INSERT INTO t6(rowid,a) SELECT -a,a FROM t5;

   578     SELECT rowid, * FROM t6;

   579   }

   580 } {-8 8 -7 7 -6 6 -5 5 -4 4 -3 3 -2 2 -1 1}

   581 do_test rowid-10.31.1 {

   582   execsql {SELECT rowid, a FROM t6 WHERE rowid>=-5.5}

   583 } {-5 5 -4 4 -3 3 -2 2 -1 1}

   584 do_test rowid-10.31.2 {

   585   execsql {SELECT rowid, a FROM t6 WHERE rowid>=-5.0}

   586 } {-5 5 -4 4 -3 3 -2 2 -1 1}

   587 do_test rowid-10.32.1 {

   588   execsql {SELECT rowid, a FROM t6 WHERE rowid>=-5.5 ORDER BY rowid DESC}

   589 } {-1 1 -2 2 -3 3 -4 4 -5 5}

   590 do_test rowid-10.32.1 {

   591   execsql {SELECT rowid, a FROM t6 WHERE rowid>=-5.0 ORDER BY rowid DESC}

   592 } {-1 1 -2 2 -3 3 -4 4 -5 5}

   593 do_test rowid-10.33 {

   594   execsql {SELECT rowid, a FROM t6 WHERE -5.5<=rowid}

   595 } {-5 5 -4 4 -3 3 -2 2 -1 1}

   596 do_test rowid-10.34 {

   597   execsql {SELECT rowid, a FROM t6 WHERE -5.5<=rowid ORDER BY rowid DESC}

   598 } {-1 1 -2 2 -3 3 -4 4 -5 5}

   599 do_test rowid-10.35.1 {

   600   execsql {SELECT rowid, a FROM t6 WHERE rowid>-5.5}

   601 } {-5 5 -4 4 -3 3 -2 2 -1 1}

   602 do_test rowid-10.35.2 {

   603   execsql {SELECT rowid, a FROM t6 WHERE rowid>-5.0}

   604 } {-4 4 -3 3 -2 2 -1 1}

   605 do_test rowid-10.36.1 {

   606   execsql {SELECT rowid, a FROM t6 WHERE rowid>-5.5 ORDER BY rowid DESC}

   607 } {-1 1 -2 2 -3 3 -4 4 -5 5}

   608 do_test rowid-10.36.2 {

   609   execsql {SELECT rowid, a FROM t6 WHERE rowid>-5.0 ORDER BY rowid DESC}

   610 } {-1 1 -2 2 -3 3 -4 4}

   611 do_test rowid-10.37 {

   612   execsql {SELECT rowid, a FROM t6 WHERE -5.5<rowid}

   613 } {-5 5 -4 4 -3 3 -2 2 -1 1}

   614 do_test rowid-10.38 {

   615   execsql {SELECT rowid, a FROM t6 WHERE -5.5<rowid ORDER BY rowid DESC}

   616 } {-1 1 -2 2 -3 3 -4 4 -5 5}

   617 do_test rowid-10.39 {

   618   execsql {SELECT rowid, a FROM t6 WHERE rowid<=-5.5}

   619 } {-8 8 -7 7 -6 6}

   620 do_test rowid-10.40 {

   621   execsql {SELECT rowid, a FROM t6 WHERE rowid<=-5.5 ORDER BY rowid DESC}

   622 } {-6 6 -7 7 -8 8}

   623 do_test rowid-10.41 {

   624   execsql {SELECT rowid, a FROM t6 WHERE -5.5>=rowid}

   625 } {-8 8 -7 7 -6 6}

   626 do_test rowid-10.42 {

   627   execsql {SELECT rowid, a FROM t6 WHERE -5.5>=rowid ORDER BY rowid DESC}

   628 } {-6 6 -7 7 -8 8}

   629 do_test rowid-10.43 {

   630   execsql {SELECT rowid, a FROM t6 WHERE rowid<-5.5}

   631 } {-8 8 -7 7 -6 6}

   632 do_test rowid-10.44 {

   633   execsql {SELECT rowid, a FROM t6 WHERE rowid<-5.5 ORDER BY rowid DESC}

   634 } {-6 6 -7 7 -8 8}

   635 do_test rowid-10.44 {

   636   execsql {SELECT rowid, a FROM t6 WHERE -5.5>rowid}

   637 } {-8 8 -7 7 -6 6}

   638 do_test rowid-10.46 {

   639   execsql {SELECT rowid, a FROM t6 WHERE -5.5>rowid ORDER BY rowid DESC}

   640 } {-6 6 -7 7 -8 8}

   641 

   642 # Comparison of rowid against string values.

   643 #

   644 do_test rowid-11.1 {

   645   execsql {SELECT rowid, a FROM t5 WHERE rowid>'abc'}

   646 } {}

   647 do_test rowid-11.2 {

   648   execsql {SELECT rowid, a FROM t5 WHERE rowid>='abc'}

   649 } {}

   650 do_test rowid-11.3 {

   651   execsql {SELECT rowid, a FROM t5 WHERE rowid<'abc'}

   652 } {1 1 2 2 3 3 4 4 5 5 6 6 7 7 8 8}

   653 do_test rowid-11.4 {

   654   execsql {SELECT rowid, a FROM t5 WHERE rowid<='abc'}

   655 } {1 1 2 2 3 3 4 4 5 5 6 6 7 7 8 8}

   656 

   657 # Test the automatic generation of rowids when the table already contains

   658 # a rowid with the maximum value.

   659 #

   660 # Once the the maximum rowid is taken, rowids are normally chosen at

   661 # random.  By by reseting the random number generator, we can cause

   662 # the rowid guessing loop to collide with prior rowids, and test the

   663 # loop out to its limit of 100 iterations.  After 100 collisions, the

   664 # rowid guesser gives up and reports SQLITE_FULL.

   665 #

   666 do_test rowid-12.1 {

   667   execsql {

   668     CREATE TABLE t7(x INTEGER PRIMARY KEY, y);

   669     INSERT INTO t7 VALUES(9223372036854775807,'a');

   670     SELECT y FROM t7;

   671   }

   672 } {a}

   673 do_test rowid-12.2 {

   674   db close

   675   sqlite3 db test.db

   676   save_prng_state

   677   execsql {

   678     INSERT INTO t7 VALUES(NULL,'b');

   679     SELECT x, y FROM t7;

   680   }

   681 } {1 b 9223372036854775807 a}

   682 execsql {INSERT INTO t7 VALUES(2,'y');}

   683 for {set i 1} {$i<=101} {incr i} {

   684   do_test rowid-12.3.$i {

   685     restore_prng_state

   686     execsql {

   687       INSERT INTO t7 VALUES(NULL,'x');

   688       INSERT OR IGNORE INTO t7 VALUES(last_insert_rowid()+1,'y');

   689       SELECT count(*) FROM t7 WHERE y=='x';

   690     }

   691   } $i

   692 }

   693 do_test rowid-12.4 {

   694   restore_prng_state

   695   catchsql {

   696     INSERT INTO t7 VALUES(NULL,'x');

   697   }

   698 } {1 {database or disk is full}}

   699 

   700 

   701 finish_test