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

#

# This file implements tests for the special processing associated

# with INTEGER PRIMARY KEY columns.

#

# $Id: intpkey.test,v 1.24 2007/11/29 17:43:28 danielk1977 Exp $

set testdir [file dirname $argv0]

source $testdir/tester.tcl

# Create a table with a primary key and a datatype other than

# integer

#

do_test intpkey-1.0 {

  execsql {

    CREATE TABLE t1(a TEXT PRIMARY KEY, b, c);

  }

} {}

# There should be an index associated with the primary key

#

do_test intpkey-1.1 {

  execsql {

    SELECT name FROM sqlite_master

    WHERE type='index' AND tbl_name='t1';

  }

} {sqlite_autoindex_t1_1}

# Now create a table with an integer primary key and verify that

# there is no associated index.

#

do_test intpkey-1.2 {

  execsql {

    DROP TABLE t1;

    CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c);

    SELECT name FROM sqlite_master

      WHERE type='index' AND tbl_name='t1';

  }

} {}

# Insert some records into the new table.  Specify the primary key

# and verify that the key is used as the record number.

#

do_test intpkey-1.3 {

  execsql {

    INSERT INTO t1 VALUES(5,'hello','world');

  }

  db last_insert_rowid

} {5}

do_test intpkey-1.4 {

  execsql {

    SELECT * FROM t1;

  }

} {5 hello world}

do_test intpkey-1.5 {

  execsql {

    SELECT rowid, * FROM t1;

  }

} {5 5 hello world}

# Attempting to insert a duplicate primary key should give a constraint

# failure.

#

do_test intpkey-1.6 {

  set r [catch {execsql {

     INSERT INTO t1 VALUES(5,'second','entry');

  }} msg]

  lappend r $msg

} {1 {PRIMARY KEY must be unique}}

do_test intpkey-1.7 {

  execsql {

    SELECT rowid, * FROM t1;

  }

} {5 5 hello world}

do_test intpkey-1.8 {

  set r [catch {execsql {

     INSERT INTO t1 VALUES(6,'second','entry');

  }} msg]

  lappend r $msg

} {0 {}}

do_test intpkey-1.8.1 {

  db last_insert_rowid

} {6}

do_test intpkey-1.9 {

  execsql {

    SELECT rowid, * FROM t1;

  }

} {5 5 hello world 6 6 second entry}

# A ROWID is automatically generated for new records that do not specify

# the integer primary key.

#

do_test intpkey-1.10 {

  execsql {

    INSERT INTO t1(b,c) VALUES('one','two');

    SELECT b FROM t1 ORDER BY b;

  }

} {hello one second}

# Try to change the ROWID for the new entry.

#

do_test intpkey-1.11 {

  execsql {

    UPDATE t1 SET a=4 WHERE b='one';

    SELECT * FROM t1;

  }

} {4 one two 5 hello world 6 second entry}

# Make sure SELECT statements are able to use the primary key column

# as an index.

#

do_test intpkey-1.12.1 {

  execsql {

    SELECT * FROM t1 WHERE a==4;

  }

} {4 one two}

do_test intpkey-1.12.2 {

  set sqlite_query_plan

} {t1 *}

# Try to insert a non-integer value into the primary key field.  This

# should result in a data type mismatch.

#

do_test intpkey-1.13.1 {

  set r [catch {execsql {

    INSERT INTO t1 VALUES('x','y','z');

  }} msg]

  lappend r $msg

} {1 {datatype mismatch}}

do_test intpkey-1.13.2 {

  set r [catch {execsql {

    INSERT INTO t1 VALUES('','y','z');

  }} msg]

  lappend r $msg

} {1 {datatype mismatch}}

do_test intpkey-1.14 {

  set r [catch {execsql {

    INSERT INTO t1 VALUES(3.4,'y','z');

  }} msg]

  lappend r $msg

} {1 {datatype mismatch}}

do_test intpkey-1.15 {

  set r [catch {execsql {

    INSERT INTO t1 VALUES(-3,'y','z');

  }} msg]

  lappend r $msg

} {0 {}}

do_test intpkey-1.16 {

  execsql {SELECT * FROM t1}

} {-3 y z 4 one two 5 hello world 6 second entry}

#### INDICES

# Check to make sure indices work correctly with integer primary keys

#

do_test intpkey-2.1 {

  execsql {

    CREATE INDEX i1 ON t1(b);

    SELECT * FROM t1 WHERE b=='y'

  }

} {-3 y z}

do_test intpkey-2.1.1 {

  execsql {

    SELECT * FROM t1 WHERE b=='y' AND rowid<0

  }

} {-3 y z}

do_test intpkey-2.1.2 {

  execsql {

    SELECT * FROM t1 WHERE b=='y' AND rowid<0 AND rowid>=-20

  }

} {-3 y z}

do_test intpkey-2.1.3 {

  execsql {

    SELECT * FROM t1 WHERE b>='y'

  }

} {-3 y z}

do_test intpkey-2.1.4 {

  execsql {

    SELECT * FROM t1 WHERE b>='y' AND rowid<10

  }

} {-3 y z}

do_test intpkey-2.2 {

  execsql {

    UPDATE t1 SET a=8 WHERE b=='y';

    SELECT * FROM t1 WHERE b=='y';

  }

} {8 y z}

do_test intpkey-2.3 {

  execsql {

    SELECT rowid, * FROM t1;

  }

} {4 4 one two 5 5 hello world 6 6 second entry 8 8 y z}

do_test intpkey-2.4 {

  execsql {

    SELECT rowid, * FROM t1 WHERE b<'second'

  }

} {5 5 hello world 4 4 one two}

do_test intpkey-2.4.1 {

  execsql {

    SELECT rowid, * FROM t1 WHERE 'second'>b

  }

} {5 5 hello world 4 4 one two}

do_test intpkey-2.4.2 {

  execsql {

    SELECT rowid, * FROM t1 WHERE 8>rowid AND 'second'>b

  }

} {4 4 one two 5 5 hello world}

do_test intpkey-2.4.3 {

  execsql {

    SELECT rowid, * FROM t1 WHERE 8>rowid AND 'second'>b AND 0<rowid

  }

} {4 4 one two 5 5 hello world}

do_test intpkey-2.5 {

  execsql {

    SELECT rowid, * FROM t1 WHERE b>'a'

  }

} {5 5 hello world 4 4 one two 6 6 second entry 8 8 y z}

do_test intpkey-2.6 {

  execsql {

    DELETE FROM t1 WHERE rowid=4;

    SELECT * FROM t1 WHERE b>'a';

  }

} {5 hello world 6 second entry 8 y z}

do_test intpkey-2.7 {

  execsql {

    UPDATE t1 SET a=-4 WHERE rowid=8;

    SELECT * FROM t1 WHERE b>'a';

  }

} {5 hello world 6 second entry -4 y z}

do_test intpkey-2.7 {

  execsql {

    SELECT * FROM t1

  }

} {-4 y z 5 hello world 6 second entry}

# Do an SQL statement.  Append the search count to the end of the result.

#

proc count sql {

  set ::sqlite_search_count 0

  return [concat [execsql $sql] $::sqlite_search_count]

}

# Create indices that include the integer primary key as one of their

# columns.

#

do_test intpkey-3.1 {

  execsql {

    CREATE INDEX i2 ON t1(a);

  }

} {}

do_test intpkey-3.2 {

  count {

    SELECT * FROM t1 WHERE a=5;

  }

} {5 hello world 0}

do_test intpkey-3.3 {

  count {

    SELECT * FROM t1 WHERE a>4 AND a<6;

  }

} {5 hello world 2}

do_test intpkey-3.4 {

  count {

    SELECT * FROM t1 WHERE b>='hello' AND b<'hello2';

  }

} {5 hello world 3}

do_test intpkey-3.5 {

  execsql {

    CREATE INDEX i3 ON t1(c,a);

  }

} {}

do_test intpkey-3.6 {

  count {

    SELECT * FROM t1 WHERE c=='world';

  }

} {5 hello world 3}

do_test intpkey-3.7 {

  execsql {INSERT INTO t1 VALUES(11,'hello','world')}

  count {

    SELECT * FROM t1 WHERE c=='world';

  }

} {5 hello world 11 hello world 5}

do_test intpkey-3.8 {

  count {

    SELECT * FROM t1 WHERE c=='world' AND a>7;

  }

} {11 hello world 4}

do_test intpkey-3.9 {

  count {

    SELECT * FROM t1 WHERE 7<a;

  }

} {11 hello world 1}

# Test inequality constraints on integer primary keys and rowids

#

do_test intpkey-4.1 {

  count {

    SELECT * FROM t1 WHERE 11=rowid

  }

} {11 hello world 0}

do_test intpkey-4.2 {

  count {

    SELECT * FROM t1 WHERE 11=rowid AND b=='hello'

  }

} {11 hello world 0}

do_test intpkey-4.3 {

  count {

    SELECT * FROM t1 WHERE 11=rowid AND b=='hello' AND c IS NOT NULL;

  }

} {11 hello world 0}

do_test intpkey-4.4 {

  count {

    SELECT * FROM t1 WHERE rowid==11

  }

} {11 hello world 0}

do_test intpkey-4.5 {

  count {

    SELECT * FROM t1 WHERE oid==11 AND b=='hello'

  }

} {11 hello world 0}

do_test intpkey-4.6 {

  count {

    SELECT * FROM t1 WHERE a==11 AND b=='hello' AND c IS NOT NULL;

  }

} {11 hello world 0}

do_test intpkey-4.7 {

  count {

    SELECT * FROM t1 WHERE 8<rowid;

  }

} {11 hello world 1}

do_test intpkey-4.8 {

  count {

    SELECT * FROM t1 WHERE 8<rowid AND 11>=oid;

  }

} {11 hello world 1}

do_test intpkey-4.9 {

  count {

    SELECT * FROM t1 WHERE 11<=_rowid_ AND 12>=a;

  }

} {11 hello world 1}

do_test intpkey-4.10 {

  count {

    SELECT * FROM t1 WHERE 0>=_rowid_;

  }

} {-4 y z 1}

do_test intpkey-4.11 {

  count {

    SELECT * FROM t1 WHERE a<0;

  }

} {-4 y z 1}

do_test intpkey-4.12 {

  count {

    SELECT * FROM t1 WHERE a<0 AND a>10;

  }

} {1}

# Make sure it is OK to insert a rowid of 0

#

do_test intpkey-5.1 {

  execsql {

    INSERT INTO t1 VALUES(0,'zero','entry');

  }

  count {

    SELECT * FROM t1 WHERE a=0;

  }

} {0 zero entry 0}

do_test intpkey-5.2 {

  execsql {

    SELECT rowid, a FROM t1

  }

} {-4 -4 0 0 5 5 6 6 11 11}

# Test the ability of the COPY command to put data into a

# table that contains an integer primary key.

#

# COPY command has been removed.  But we retain these tests so

# that the tables will contain the right data for tests that follow.

#

do_test intpkey-6.1 {

  execsql {

    BEGIN;

    INSERT INTO t1 VALUES(20,'b-20','c-20');

    INSERT INTO t1 VALUES(21,'b-21','c-21');

    INSERT INTO t1 VALUES(22,'b-22','c-22');

    COMMIT;

    SELECT * FROM t1 WHERE a>=20;

  }

} {20 b-20 c-20 21 b-21 c-21 22 b-22 c-22}

do_test intpkey-6.2 {

  execsql {

    SELECT * FROM t1 WHERE b=='hello'

  }

} {5 hello world 11 hello world}

do_test intpkey-6.3 {

  execsql {

    DELETE FROM t1 WHERE b='b-21';

    SELECT * FROM t1 WHERE b=='b-21';

  }

} {}

do_test intpkey-6.4 {

  execsql {

    SELECT * FROM t1 WHERE a>=20

  }

} {20 b-20 c-20 22 b-22 c-22}

# Do an insert of values with the columns specified out of order.

#

do_test intpkey-7.1 {

  execsql {

    INSERT INTO t1(c,b,a) VALUES('row','new',30);

    SELECT * FROM t1 WHERE rowid>=30;

  }

} {30 new row}

do_test intpkey-7.2 {

  execsql {

    SELECT * FROM t1 WHERE rowid>20;

  }

} {22 b-22 c-22 30 new row}

# Do an insert from a select statement.

#

do_test intpkey-8.1 {

  execsql {

    CREATE TABLE t2(x INTEGER PRIMARY KEY, y, z);

    INSERT INTO t2 SELECT * FROM t1;

    SELECT rowid FROM t2;

  }

} {-4 0 5 6 11 20 22 30}

do_test intpkey-8.2 {

  execsql {

    SELECT x FROM t2;

  }

} {-4 0 5 6 11 20 22 30}

do_test intpkey-9.1 {

  execsql {

    UPDATE t1 SET c='www' WHERE c='world';

    SELECT rowid, a, c FROM t1 WHERE c=='www';

  }

} {5 5 www 11 11 www}

# Check insert of NULL for primary key

#

do_test intpkey-10.1 {

  execsql {

    DROP TABLE t2;

    CREATE TABLE t2(x INTEGER PRIMARY KEY, y, z);

    INSERT INTO t2 VALUES(NULL, 1, 2);

    SELECT * from t2;

  }

} {1 1 2}

do_test intpkey-10.2 {

  execsql {

    INSERT INTO t2 VALUES(NULL, 2, 3);

    SELECT * from t2 WHERE x=2;

  }

} {2 2 3}

do_test intpkey-10.3 {

  execsql {

    INSERT INTO t2 SELECT NULL, z, y FROM t2;

    SELECT * FROM t2;

  }

} {1 1 2 2 2 3 3 2 1 4 3 2}

# This tests checks to see if a floating point number can be used

# to reference an integer primary key.

#

do_test intpkey-11.1 {

  execsql {

    SELECT b FROM t1 WHERE a=2.0+3.0;

  }

} {hello}

do_test intpkey-11.1 {

  execsql {

    SELECT b FROM t1 WHERE a=2.0+3.5;

  }

} {}

integrity_check intpkey-12.1

# Try to use a string that looks like a floating point number as

# an integer primary key.  This should actually work when the floating

# point value can be rounded to an integer without loss of data.

#

do_test intpkey-13.1 {

  execsql {

    SELECT * FROM t1 WHERE a=1;

  }

} {}

do_test intpkey-13.2 {

  execsql {

    INSERT INTO t1 VALUES('1.0',2,3);

    SELECT * FROM t1 WHERE a=1;

  }

} {1 2 3}

do_test intpkey-13.3 {

  catchsql {

    INSERT INTO t1 VALUES('1.5',3,4);

  }

} {1 {datatype mismatch}}

ifcapable {bloblit} {

  do_test intpkey-13.4 {

    catchsql {

      INSERT INTO t1 VALUES(x'123456',3,4);

    }

  } {1 {datatype mismatch}}

}

do_test intpkey-13.5 {

  catchsql {

    INSERT INTO t1 VALUES('+1234567890',3,4);

  }

} {0 {}}

# Compare an INTEGER PRIMARY KEY against a TEXT expression. The INTEGER

# affinity should be applied to the text value before the comparison

# takes place.

#

do_test intpkey-14.1 {

  execsql {

    CREATE TABLE t3(a INTEGER PRIMARY KEY, b INTEGER, c TEXT);

    INSERT INTO t3 VALUES(1, 1, 'one');

    INSERT INTO t3 VALUES(2, 2, '2');

    INSERT INTO t3 VALUES(3, 3, 3);

  }

} {}

do_test intpkey-14.2 {

  execsql {

    SELECT * FROM t3 WHERE a>2;

  }

} {3 3 3}

do_test intpkey-14.3 {

  execsql {

    SELECT * FROM t3 WHERE a>'2';

  }

} {3 3 3}

do_test intpkey-14.4 {

  execsql {

    SELECT * FROM t3 WHERE a<'2';

  }

} {1 1 one}

do_test intpkey-14.5 {

  execsql {

    SELECT * FROM t3 WHERE a<c;

  }

} {1 1 one}

do_test intpkey-14.6 {

  execsql {

    SELECT * FROM t3 WHERE a=c;

  }

} {2 2 2 3 3 3}

# Check for proper handling of primary keys greater than 2^31.

# Ticket #1188

#

do_test intpkey-15.1 {

  execsql {

    INSERT INTO t1 VALUES(2147483647, 'big-1', 123);

    SELECT * FROM t1 WHERE a>2147483648;

  }

} {}

do_test intpkey-15.2 {

  execsql {

    INSERT INTO t1 VALUES(NULL, 'big-2', 234);

    SELECT b FROM t1 WHERE a>=2147483648;

  }

} {big-2}

do_test intpkey-15.3 {

  execsql {

    SELECT b FROM t1 WHERE a>2147483648;

  }

} {}

do_test intpkey-15.4 {

  execsql {

    SELECT b FROM t1 WHERE a>=2147483647;

  }

} {big-1 big-2}

do_test intpkey-15.5 {

  execsql {

    SELECT b FROM t1 WHERE a<2147483648;

  }

} {y zero 2 hello second hello b-20 b-22 new 3 big-1}

do_test intpkey-15.6 {

  execsql {

    SELECT b FROM t1 WHERE a<12345678901;

  }

} {y zero 2 hello second hello b-20 b-22 new 3 big-1 big-2}

do_test intpkey-15.7 {

  execsql {

    SELECT b FROM t1 WHERE a>12345678901;

  }

} {}

finish_test