# 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.

#

# $Id: rowid.test,v 1.20 2008/01/19 20:11:26 drh Exp $

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 {