2N/A#pragma ident "%Z%%M% %I% %E% SMI"
2N/A# The author disclaims copyright to this source code. In place of
2N/A# a legal notice, here is a blessing:
2N/A# May you do good and not evil.
2N/A# May you find forgiveness for yourself and forgive others.
2N/A# May you share freely, never taking more than you give.
2N/A#***********************************************************************
2N/A# This file implements regression tests for SQLite library. The
2N/A# focus of this file is testing the magic ROWID column that is
2N/A# found on all tables.
2N/Aset testdir [file dirname $argv0]
2N/A# Basic ROWID functionality tests.
2N/A CREATE TABLE t1(x int, y int);
2N/A INSERT INTO t1 VALUES(1,2);
2N/A INSERT INTO t1 VALUES(3,4);
2N/A SELECT x FROM t1 ORDER BY y;
2N/A set r [execsql {SELECT rowid FROM t1 ORDER BY x}]
2N/A global x2rowid rowid2x
2N/A set x2rowid(1) [lindex $r 0]
2N/A set x2rowid(3) [lindex $r 1]
2N/A set rowid2x($x2rowid(1)) 1
2N/A set rowid2x($x2rowid(3)) 3
2N/A set sql "SELECT x FROM t1 WHERE rowid==$x2rowid(1)"
2N/A set sql "SELECT x FROM t1 WHERE rowid==$x2rowid(3)"
2N/A set sql "SELECT x FROM t1 WHERE oid==$x2rowid(1)"
2N/A set sql "SELECT x FROM t1 WHERE OID==$x2rowid(3)"
2N/A set sql "SELECT x FROM t1 WHERE _rowid_==$x2rowid(1)"
2N/Ado_test rowid-1.7.1 {
2N/A set norow [expr {int(rand()*1000000)}]
2N/A if {$norow!=$x2rowid(1) && $norow!=$x2rowid(3)} break
2N/A execsql "SELECT x FROM t1 WHERE rowid=$norow"
2N/A set v [execsql {SELECT x, oid FROM t1 order by x}]
2N/A set v2 [list 1 $x2rowid(1) 3 $x2rowid(3)]
2N/A set v [execsql {SELECT x, RowID FROM t1 order by x}]
2N/A set v2 [list 1 $x2rowid(1) 3 $x2rowid(3)]
2N/A set v [execsql {SELECT x, _rowid_ FROM t1 order by x}]
2N/A set v2 [list 1 $x2rowid(1) 3 $x2rowid(3)]
2N/A# We can insert or update the ROWID column.
2N/A INSERT INTO t1(rowid,x,y) VALUES(1234,5,6);
2N/A SELECT rowid, * FROM t1;
2N/A} {0 {1 1 2 2 3 4 1234 5 6}}
2N/A UPDATE t1 SET rowid=12345 WHERE x==1;
2N/A SELECT rowid, * FROM t1
2N/A} {0 {2 3 4 1234 5 6 12345 1 2}}
2N/A INSERT INTO t1(y,x,oid) VALUES(8,7,1235);
2N/A SELECT rowid, * FROM t1 WHERE rowid>1000;
2N/A} {0 {1234 5 6 1235 7 8 12345 1 2}}
2N/A UPDATE t1 SET oid=12346 WHERE x==1;
2N/A SELECT rowid, * FROM t1;
2N/A} {0 {2 3 4 1234 5 6 1235 7 8 12346 1 2}}
2N/A INSERT INTO t1(x,_rowid_,y) VALUES(9,1236,10);
2N/A SELECT rowid, * FROM t1 WHERE rowid>1000;
2N/A} {0 {1234 5 6 1235 7 8 1236 9 10 12346 1 2}}
2N/A UPDATE t1 SET _rowid_=12347 WHERE x==1;
2N/A SELECT rowid, * FROM t1 WHERE rowid>1000;
2N/A} {0 {1234 5 6 1235 7 8 1236 9 10 12347 1 2}}
2N/A# But we can use ROWID in the WHERE clause of an UPDATE that does not
2N/A set sql "UPDATE t1 SET x=2 WHERE OID==$x2rowid(3)"
2N/A execsql {SELECT x FROM t1 ORDER BY x}
2N/A set sql "UPDATE t1 SET x=3 WHERE _rowid_==$x2rowid(3)"
2N/A execsql {SELECT x FROM t1 ORDER BY x}
2N/A# We cannot index by ROWID
2N/A set v [catch {execsql {CREATE INDEX idxt1 ON t1(rowid)}} msg]
2N/A} {1 {table t1 has no column named rowid}}
2N/A set v [catch {execsql {CREATE INDEX idxt1 ON t1(_rowid_)}} msg]
2N/A} {1 {table t1 has no column named _rowid_}}
2N/A set v [catch {execsql {CREATE INDEX idxt1 ON t1(oid)}} msg]
2N/A} {1 {table t1 has no column named oid}}
2N/A set v [catch {execsql {CREATE INDEX idxt1 ON t1(x, rowid)}} msg]
2N/A} {1 {table t1 has no column named rowid}}
2N/A# Columns defined in the CREATE statement override the buildin ROWID
2N/A CREATE TABLE t2(rowid int, x int, y int);
2N/A INSERT INTO t2 VALUES(0,2,3);
2N/A INSERT INTO t2 VALUES(4,5,6);
2N/A INSERT INTO t2 VALUES(7,8,9);
2N/A SELECT * FROM t2 ORDER BY x;
2N/A} {0 2 3 4 5 6 7 8 9}
2N/A execsql {SELECT * FROM t2 ORDER BY rowid}
2N/A} {0 2 3 4 5 6 7 8 9}
2N/A execsql {SELECT rowid, x, y FROM t2 ORDER BY rowid}
2N/A} {0 2 3 4 5 6 7 8 9}
2N/A set r1 [execsql {SELECT _rowid_, rowid FROM t2 ORDER BY rowid}]
2N/A foreach {a b c d e f} $r1 {}
2N/A set r2 [execsql {SELECT _rowid_, rowid FROM t2 ORDER BY x DESC}]
2N/A foreach {u v w x y z} $r2 {}
2N/A expr {$u==$e && $w==$c && $y==$a}
2N/Ado_probtest rowid-3.5 {
2N/A set r1 [execsql {SELECT _rowid_, rowid FROM t2 ORDER BY rowid}]
2N/A foreach {a b c d e f} $r1 {}
2N/A expr {$a!=$b && $c!=$d && $e!=$f}
2N/A# Let's try some more complex examples, including some joins.
2N/A for {set i 1} {$i<=50} {incr i} {
2N/A execsql "INSERT INTO t1(x,y) VALUES($i,[expr {$i*$i}])"
2N/A execsql {INSERT INTO t2 SELECT _rowid_, x*y, y*y FROM t1}
2N/Ado_test rowid-4.2.1 {
2N/Ado_test rowid-4.2.2 {
2N/Ado_test rowid-4.2.3 {
2N/Ado_test rowid-4.2.4 {
2N/Ado_test rowid-4.2.5 {
2N/Ado_test rowid-4.2.6 {
2N/Ado_test rowid-4.2.7 {
2N/A execsql {CREATE INDEX idxt1 ON t1(x)}
2N/Ado_test rowid-4.3.1 {
2N/Ado_test rowid-4.3.2 {
2N/Ado_test rowid-4.4.1 {
2N/Ado_test rowid-4.4.2 {
2N/A execsql {CREATE INDEX idxt2 ON t2(y)}
2N/A set sqlite_search_count 0
2N/A }] $sqlite_search_count
2N/Ado_test rowid-4.5.1 {
2N/A set sqlite_search_count 0
2N/A }] $sqlite_search_count
2N/A execsql {DELETE FROM t1 WHERE _rowid_ IN (SELECT oid FROM t1 WHERE x>8)}
2N/A execsql {SELECT max(x) FROM t1}
2N/A# Make sure a "WHERE rowid=X" clause works when there is no ROWID of X.
2N/A for {set ::norow 1} {1} {incr ::norow} {
2N/A if {[execsql "SELECT x FROM t1 WHERE rowid=$::norow"]==""} break
2N/A DELETE FROM t1 WHERE rowid=$::norow
2N/A# Beginning with version 2.3.4, SQLite computes rowids of new rows by
2N/A# finding the maximum current rowid and adding one. It falls back to
2N/A# the old random algorithm if the maximum rowid is the largest integer.
2N/A# The following tests are for this new behavior.
2N/A INSERT INTO t1 VALUES(1,2);
2N/A SELECT rowid, * FROM t1;
2N/A INSERT INTO t1 VALUES(99,100);
2N/A SELECT rowid,* FROM t1
2N/A CREATE TABLE t2(a INTEGER PRIMARY KEY, b);
2N/A INSERT INTO t2(b) VALUES(55);
2N/A INSERT INTO t2(b) VALUES(66);
2N/A INSERT INTO t2(a,b) VALUES(1000000,77);
2N/A INSERT INTO t2(b) VALUES(88);
2N/A} {1 55 2 66 1000000 77 1000001 88}
2N/A INSERT INTO t2(a,b) VALUES(2147483647,99);
2N/A INSERT INTO t2(b) VALUES(11);
2N/A SELECT b FROM t2 ORDER BY b;
2N/A} {11 55 66 77 88 99}
2N/A SELECT b FROM t2 WHERE a NOT IN(1,2,1000000,1000001,2147483647);
2N/A INSERT INTO t2(b) VALUES(22);
2N/A INSERT INTO t2(b) VALUES(33);
2N/A INSERT INTO t2(b) VALUES(44);
2N/A INSERT INTO t2(b) VALUES(55);
2N/A SELECT b FROM t2 WHERE a NOT IN(1,2,1000000,1000001,2147483647) ORDER BY b;
2N/A DELETE FROM t2 WHERE a!=2;
2N/A INSERT INTO t2(b) VALUES(111);
2N/A# Make sure AFTER triggers that do INSERTs do not change the last_insert_rowid.
2N/A CREATE TABLE t3(a integer primary key);
2N/A INSERT INTO t4 VALUES(1);
2N/A CREATE TRIGGER r3 AFTER INSERT on t3 FOR EACH ROW BEGIN
2N/A SELECT rowid, * FROM t4;
2N/A INSERT INTO t3 VALUES(123);
2N/A SELECT last_insert_rowid();
2N/A SELECT rowid, * FROM t4;
2N/A INSERT INTO t3 VALUES(NULL);
2N/A SELECT last_insert_rowid();
2N/A SELECT rowid, * FROM t4;
2N/A# ticket #377: Comparison between integer primiary key and floating point
2N/A SELECT * FROM t3 WHERE a<123.5
2N/A SELECT * FROM t3 WHERE a<124.5
2N/A SELECT * FROM t3 WHERE a>123.5
2N/A SELECT * FROM t3 WHERE a>122.5
2N/A SELECT * FROM t3 WHERE a==123.5
2N/A SELECT * FROM t3 WHERE a==123.000
2N/A SELECT * FROM t3 WHERE a>100.5 AND a<200.5
2N/A SELECT * FROM t3 WHERE a>'xyz';
2N/A SELECT * FROM t3 WHERE a<'xyz';
2N/A SELECT * FROM t3 WHERE a>=122.9 AND a<=123.1
2N/A# Ticket #567. Comparisons of ROWID or integery primary key against
2N/A# floating point numbers still do not always work.
2N/A INSERT INTO t5 VALUES(1);
2N/A INSERT INTO t5 VALUES(2);
2N/A INSERT INTO t5 SELECT a+2 FROM t5;
2N/A INSERT INTO t5 SELECT a+4 FROM t5;
2N/A SELECT rowid, * FROM t5;
2N/A} {1 1 2 2 3 3 4 4 5 5 6 6 7 7 8 8}
2N/A execsql {SELECT rowid, a FROM t5 WHERE rowid>=5.5}
2N/A execsql {SELECT rowid, a FROM t5 WHERE rowid>=5.0}
2N/A execsql {SELECT rowid, a FROM t5 WHERE rowid>5.5}
2N/Ado_test rowid-10.3.2 {
2N/A execsql {SELECT rowid, a FROM t5 WHERE rowid>5.0}
2N/A execsql {SELECT rowid, a FROM t5 WHERE 5.5<=rowid}
2N/A execsql {SELECT rowid, a FROM t5 WHERE 5.5<rowid}
2N/A execsql {SELECT rowid, a FROM t5 WHERE rowid<=5.5}
2N/A} {1 1 2 2 3 3 4 4 5 5}
2N/A execsql {SELECT rowid, a FROM t5 WHERE rowid<5.5}
2N/A} {1 1 2 2 3 3 4 4 5 5}
2N/A execsql {SELECT rowid, a FROM t5 WHERE 5.5>=rowid}
2N/A} {1 1 2 2 3 3 4 4 5 5}
2N/Ado_test rowid-10.10 {
2N/A execsql {SELECT rowid, a FROM t5 WHERE 5.5>rowid}
2N/A} {1 1 2 2 3 3 4 4 5 5}
2N/Ado_test rowid-10.11 {
2N/A execsql {SELECT rowid, a FROM t5 WHERE rowid>=5.5 ORDER BY rowid DESC}
2N/Ado_test rowid-10.11.2 {
2N/A execsql {SELECT rowid, a FROM t5 WHERE rowid>=5.0 ORDER BY rowid DESC}
2N/Ado_test rowid-10.12 {
2N/A execsql {SELECT rowid, a FROM t5 WHERE rowid>5.5 ORDER BY rowid DESC}
2N/Ado_test rowid-10.12.2 {
2N/A execsql {SELECT rowid, a FROM t5 WHERE rowid>5.0 ORDER BY rowid DESC}
2N/Ado_test rowid-10.13 {
2N/A execsql {SELECT rowid, a FROM t5 WHERE 5.5<=rowid ORDER BY rowid DESC}
2N/Ado_test rowid-10.14 {
2N/A execsql {SELECT rowid, a FROM t5 WHERE 5.5<rowid ORDER BY rowid DESC}
2N/Ado_test rowid-10.15 {
2N/A execsql {SELECT rowid, a FROM t5 WHERE rowid<=5.5 ORDER BY rowid DESC}
2N/A} {5 5 4 4 3 3 2 2 1 1}
2N/Ado_test rowid-10.16 {
2N/A execsql {SELECT rowid, a FROM t5 WHERE rowid<5.5 ORDER BY rowid DESC}
2N/A} {5 5 4 4 3 3 2 2 1 1}
2N/Ado_test rowid-10.17 {
2N/A execsql {SELECT rowid, a FROM t5 WHERE 5.5>=rowid ORDER BY rowid DESC}
2N/A} {5 5 4 4 3 3 2 2 1 1}
2N/Ado_test rowid-10.18 {
2N/A execsql {SELECT rowid, a FROM t5 WHERE 5.5>rowid ORDER BY rowid DESC}
2N/A} {5 5 4 4 3 3 2 2 1 1}
2N/Ado_test rowid-10.30 {
2N/A INSERT INTO t6(rowid,a) SELECT -a,a FROM t5;
2N/A SELECT rowid, * FROM t6;
2N/A} {-8 8 -7 7 -6 6 -5 5 -4 4 -3 3 -2 2 -1 1}
2N/Ado_test rowid-10.31.1 {
2N/A execsql {SELECT rowid, a FROM t6 WHERE rowid>=-5.5}
2N/A} {-5 5 -4 4 -3 3 -2 2 -1 1}
2N/Ado_test rowid-10.31.2 {
2N/A execsql {SELECT rowid, a FROM t6 WHERE rowid>=-5.0}
2N/A} {-5 5 -4 4 -3 3 -2 2 -1 1}
2N/Ado_test rowid-10.32.1 {
2N/A execsql {SELECT rowid, a FROM t6 WHERE rowid>=-5.5 ORDER BY rowid DESC}
2N/A} {-1 1 -2 2 -3 3 -4 4 -5 5}
2N/Ado_test rowid-10.32.1 {
2N/A execsql {SELECT rowid, a FROM t6 WHERE rowid>=-5.0 ORDER BY rowid DESC}
2N/A} {-1 1 -2 2 -3 3 -4 4 -5 5}
2N/Ado_test rowid-10.33 {
2N/A execsql {SELECT rowid, a FROM t6 WHERE -5.5<=rowid}
2N/A} {-5 5 -4 4 -3 3 -2 2 -1 1}
2N/Ado_test rowid-10.34 {
2N/A execsql {SELECT rowid, a FROM t6 WHERE -5.5<=rowid ORDER BY rowid DESC}
2N/A} {-1 1 -2 2 -3 3 -4 4 -5 5}
2N/Ado_test rowid-10.35.1 {
2N/A execsql {SELECT rowid, a FROM t6 WHERE rowid>-5.5}
2N/A} {-5 5 -4 4 -3 3 -2 2 -1 1}
2N/Ado_test rowid-10.35.2 {
2N/A execsql {SELECT rowid, a FROM t6 WHERE rowid>-5.0}
2N/A} {-4 4 -3 3 -2 2 -1 1}
2N/Ado_test rowid-10.36.1 {
2N/A execsql {SELECT rowid, a FROM t6 WHERE rowid>-5.5 ORDER BY rowid DESC}
2N/A} {-1 1 -2 2 -3 3 -4 4 -5 5}
2N/Ado_test rowid-10.36.2 {
2N/A execsql {SELECT rowid, a FROM t6 WHERE rowid>-5.0 ORDER BY rowid DESC}
2N/A} {-1 1 -2 2 -3 3 -4 4}
2N/Ado_test rowid-10.37 {
2N/A execsql {SELECT rowid, a FROM t6 WHERE -5.5<rowid}
2N/A} {-5 5 -4 4 -3 3 -2 2 -1 1}
2N/Ado_test rowid-10.38 {
2N/A execsql {SELECT rowid, a FROM t6 WHERE -5.5<rowid ORDER BY rowid DESC}
2N/A} {-1 1 -2 2 -3 3 -4 4 -5 5}
2N/Ado_test rowid-10.39 {
2N/A execsql {SELECT rowid, a FROM t6 WHERE rowid<=-5.5}
2N/Ado_test rowid-10.40 {
2N/A execsql {SELECT rowid, a FROM t6 WHERE rowid<=-5.5 ORDER BY rowid DESC}
2N/Ado_test rowid-10.41 {
2N/A execsql {SELECT rowid, a FROM t6 WHERE -5.5>=rowid}
2N/Ado_test rowid-10.42 {
2N/A execsql {SELECT rowid, a FROM t6 WHERE -5.5>=rowid ORDER BY rowid DESC}
2N/Ado_test rowid-10.43 {
2N/A execsql {SELECT rowid, a FROM t6 WHERE rowid<-5.5}
2N/Ado_test rowid-10.44 {
2N/A execsql {SELECT rowid, a FROM t6 WHERE rowid<-5.5 ORDER BY rowid DESC}
2N/Ado_test rowid-10.44 {
2N/A execsql {SELECT rowid, a FROM t6 WHERE -5.5>rowid}
2N/Ado_test rowid-10.46 {
2N/A execsql {SELECT rowid, a FROM t6 WHERE -5.5>rowid ORDER BY rowid DESC}
2N/A# Comparison of rowid against string values.
2N/A execsql {SELECT rowid, a FROM t5 WHERE rowid>'abc'}
2N/A execsql {SELECT rowid, a FROM t5 WHERE rowid>='abc'}
2N/A execsql {SELECT rowid, a FROM t5 WHERE rowid<'abc'}
2N/A} {1 1 2 2 3 3 4 4 5 5 6 6 7 7 8 8}
2N/A execsql {SELECT rowid, a FROM t5 WHERE rowid<='abc'}
2N/A} {1 1 2 2 3 3 4 4 5 5 6 6 7 7 8 8}