| # 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 |
| set testprefix rowid |
| |
| # 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} |
| |
| if 0 { # With the index-on-expressions enhancement, creating |
| # an index on ROWID has become possible. |
| # 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} |
| |
| do_test rowid-11.asc.1 { |
| execsql {SELECT rowid, a FROM t5 WHERE rowid>'abc' ORDER BY 1 ASC} |
| } {} |
| do_test rowid-11.asc.2 { |
| execsql {SELECT rowid, a FROM t5 WHERE rowid>='abc' ORDER BY 1 ASC} |
| } {} |
| do_test rowid-11.asc.3 { |
| execsql {SELECT rowid, a FROM t5 WHERE rowid<'abc' ORDER BY 1 ASC} |
| } {1 1 2 2 3 3 4 4 5 5 6 6 7 7 8 8} |
| do_test rowid-11.asc.4 { |
| execsql {SELECT rowid, a FROM t5 WHERE rowid<='abc' ORDER BY 1 ASC} |
| } {1 1 2 2 3 3 4 4 5 5 6 6 7 7 8 8} |
| |
| do_test rowid-11.desc.1 { |
| execsql {SELECT rowid, a FROM t5 WHERE rowid>'abc' ORDER BY 1 DESC} |
| } {} |
| do_test rowid-11.desc.2 { |
| execsql {SELECT rowid, a FROM t5 WHERE rowid>='abc' ORDER BY 1 DESC} |
| } {} |
| do_test rowid-11.desc.3 { |
| execsql {SELECT rowid, a FROM t5 WHERE rowid<'abc' ORDER BY 1 DESC} |
| } {8 8 7 7 6 6 5 5 4 4 3 3 2 2 1 1} |
| do_test rowid-11.desc.4 { |
| execsql {SELECT rowid, a FROM t5 WHERE rowid<='abc' ORDER BY 1 DESC} |
| } {8 8 7 7 6 6 5 5 4 4 3 3 2 2 1 1} |
| |
| # 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} |
| |
| #------------------------------------------------------------------------- |
| do_execsql_test rowid-14.0 { |
| CREATE TABLE t14(x INTEGER PRIMARY KEY); |
| INSERT INTO t14(x) VALUES (100); |
| } |
| do_execsql_test rowid-14.1 { |
| SELECT * FROM t14 WHERE x < 'a' ORDER BY rowid ASC; |
| } {100} |
| do_execsql_test rowid-14.2 { |
| SELECT * FROM t14 WHERE x < 'a' ORDER BY rowid DESC; |
| } {100} |
| |
| do_execsql_test rowid-14.3 { |
| DELETE FROM t14; |
| SELECT * FROM t14 WHERE x < 'a' ORDER BY rowid ASC; |
| } {} |
| do_execsql_test rowid-14.4 { |
| SELECT * FROM t14 WHERE x < 'a' ORDER BY rowid DESC; |
| } {} |
| |
| reset_db |
| do_execsql_test rowid-15.0 { |
| PRAGMA reverse_unordered_selects=true; |
| CREATE TABLE t1 (c0, c1); |
| CREATE TABLE t2 (c0 INT UNIQUE); |
| INSERT INTO t1(c0, c1) VALUES (0, 0), (0, NULL); |
| INSERT INTO t2(c0) VALUES (1); |
| } |
| |
| do_execsql_test rowid-15.1 { |
| SELECT t2.c0, t1.c1 FROM t1, t2 |
| WHERE (t2.rowid <= 'a') OR (t1.c0 <= t2.c0) LIMIT 100 |
| } {1 {} 1 0} |
| |
| do_execsql_test rowid-15.2 { |
| SELECT 1, NULL INTERSECT SELECT * FROM ( |
| SELECT t2.c0, t1.c1 FROM t1, t2 |
| WHERE ((t2.rowid <= 'a')) OR (t1.c0 <= t2.c0) ORDER BY 'a' DESC LIMIT 100 |
| ); |
| } {1 {}} |
| |
| #------------------------------------------------------------------------- |
| # Check that an unqualified "rowid" can be used in join queries so long |
| # as only one of the source objects has a rowid column. |
| # |
| reset_db |
| do_execsql_test 16.0 { |
| CREATE TABLE t1(x); |
| CREATE TABLE t2(y PRIMARY KEY) WITHOUT ROWID; |
| CREATE VIEW v1 AS SELECT x FROM t1; |
| CREATE TABLE t3(z); |
| |
| INSERT INTO t1(rowid, x) VALUES(1, 1); |
| INSERT INTO t2(y) VALUES(2); |
| INSERT INTO t3(rowid, z) VALUES(3, 3); |
| } |
| |
| do_execsql_test 16.1 { SELECT rowid FROM t1, t2; } {1} |
| do_execsql_test 16.2 { SELECT rowid FROM t1, v1; } {1} |
| do_execsql_test 16.3 { SELECT rowid FROM t3, v1; } {3} |
| do_execsql_test 16.4 { SELECT rowid FROM t3, (SELECT 123); } {3} |
| |
| do_execsql_test 16.5 { SELECT rowid FROM t2, t1; } {1} |
| do_execsql_test 16.6 { SELECT rowid FROM v1, t1; } {1} |
| do_execsql_test 16.7 { SELECT rowid FROM v1, t3; } {3} |
| do_execsql_test 16.8 { SELECT rowid FROM (SELECT 123), t3; } {3} |
| |
| do_catchsql_test 16.5 { SELECT rowid FROM t1, t3; } {1 {no such column: rowid}} |
| |
| |
| |
| finish_test |