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