SQLite4  Check-in [d6d0e21ad2]

      nIn -= 1;
    }
  }
  
  /* If the IGNORE_WHITESPACE flag is set, ignore any leading whitespace. */
  i = 0;
  if( flags & SQLITE4_IGNORE_WHITESPACE ){
    while( sqlite4Isspace(zIn[i]) && i<nIn ) i+=incr;
  }
  if( nIn<=i ) return error_value;

  /* Check for a leading '+' or '-' symbol. */
  if( zIn[i]=='-' ){
    r.sign = 1;
    i += incr;

      memcpy(z, "NaN", 4);
    }else{
      memcpy(z, "inf", 4);
    }
    return (z - zOut)+3;
  }
  if( x.m==0 ){



    memcpy(z, "0", 2);

    return 1+(z-zOut);
  }
  zNum = renderInt(x.m, zBuf, sizeof(zBuf));
  n = &zBuf[sizeof(zBuf)-1] - zNum;
  if( x.e>=0 && x.e+n<=25 ){
    /* Integer values with up to 25 digits */
    memcpy(z, zNum, n+1);

*/
int sqlite4VdbeMemIntegerify(Mem *pMem){
  assert( pMem->db==0 || sqlite4_mutex_held(pMem->db->mutex) );
  assert( (pMem->flags & MEM_RowSet)==0 );
  assert( EIGHT_BYTE_ALIGNMENT(pMem) );

  if( (pMem->flags & MEM_Int)==0 ){

    pMem->u.num = sqlite4_num_from_int64(sqlite4VdbeIntValue(pMem));





    MemSetTypeFlag(pMem, MEM_Int);
  }
  return SQLITE4_OK;
}

/*
** Convert pMem so that it has types MEM_Real or MEM_Int or both.
** Invalidate any prior representations.
**
** Every effort is made to force the conversion, even if the input
** is a string that does not look completely like a number.  Convert
** as much of the string as we can and ignore the rest.
*/
int sqlite4VdbeMemNumerify(Mem *pMem){
  if( (pMem->flags & (MEM_Int|MEM_Real|MEM_Null))==0 ){
    int bReal = 0;
    int flags = (pMem->enc | SQLITE4_PREFIX_ONLY | SQLITE4_IGNORE_WHITESPACE);

    assert( (pMem->flags & (MEM_Blob|MEM_Str))!=0 );
    assert( pMem->db==0 || sqlite4_mutex_held(pMem->db->mutex) );
    pMem->u.num = sqlite4_num_from_text(pMem->z, pMem->n, flags, &bReal);

    MemSetTypeFlag(pMem, (bReal ? MEM_Real : MEM_Int));
  }
  assert( (pMem->flags & (MEM_Int|MEM_Real|MEM_Null))!=0 );
  pMem->flags &= ~(MEM_Str|MEM_Blob);
  return SQLITE4_OK;
}

do_createtable_tests 1.1.1 -error {
  object name reserved for internal use: %s
} {
  1    "CREATE TABLE sqlite_abc(a, b, c)"        sqlite_abc
  2    "CREATE TABLE temp.sqlite_helloworld(x)"  sqlite_helloworld
  3    {CREATE TABLE auxa."sqlite__"(x, y)}      sqlite__
  4    {CREATE TABLE auxb."sqlite_"(z)}          sqlite_
  5    {CREATE TABLE "SQLITE4_TBL"(z)}            SQLITE4_TBL
}
do_createtable_tests 1.1.2 {
  1    "CREATE TABLE sqlit_abc(a, b, c)"         {}
  2    "CREATE TABLE temp.sqlitehelloworld(x)"   {}
  3    {CREATE TABLE auxa."sqlite"(x, y)}        {}
  4    {CREATE TABLE auxb."sqlite-"(z)}          {}
  5    {CREATE TABLE "SQLITE-TBL"(z)}            {}

#
#   table_column_decltypes TBL
#     The argument must be a table name. Return a list of column declared
#     types, from left to right, for the table.
#
proc sci {select cmd} {
  set res [list]
  set STMT [sqlite4_prepare_v2 db $select -1 dummy]
  for {set i 0} {$i < [sqlite4_column_count $STMT]} {incr i} {
    lappend res [$cmd $STMT $i]
  }
  sqlite4_finalize $STMT
  set res
}
proc tci {tbl cmd} { sci "SELECT * FROM $tbl" $cmd }

set sqlite_current_time 1000000000
do_createtable_tests 3.5 -query {
  SELECT quote(a), quote(b), quote(c), quote(d), quote(e), quote(f), 
         quote(g), quote(h), quote(i), quote(j), quote(k)
  FROM t4 ORDER BY rowid DESC LIMIT 1;
} {
  1 "INSERT INTO t4 DEFAULT VALUES" {
    NULL {'string constant'} X'424C4F42' 1 -1 3.14 -3.14 
    'acd' '01:46:40' '2001-09-09' {'2001-09-09 01:46:40'}
  }

  2 "INSERT INTO t4(a, b, c) VALUES(1, 2, 3)" {
    1 2 3 1 -1 3.14 -3.14 'acd' '01:46:40' '2001-09-09' {'2001-09-09 01:46:40'}
  }

  3 "INSERT INTO t4(k, j, i) VALUES(1, 2, 3)" {
    NULL {'string constant'} X'424C4F42' 1 -1 3.14 -3.14 'acd' 3 2 1
  }

  4 "INSERT INTO t4(a,b,c,d,e,f,g,h,i,j,k) VALUES(1,2,3,4,5,6,7,8,9,10,11)" {
    1 2 3 4 5 6 7 8 9 10 11
  }
}

    d DEFAULT -45678.6,
    e DEFAULT 394507
  );
} {}
do_execsql_test e_createtable-3.6.2 {
  INSERT INTO t5 DEFAULT VALUES;
  SELECT quote(a), quote(b), quote(c), quote(d), quote(e) FROM t5;
} {NULL {'text value'} X'424C4F42' -45678.6 394507}

# EVIDENCE-OF: R-60616-50251 If the default value of a column is an
# expression in parentheses, then the expression is evaluated once for
# each row inserted and the results used in the new row.
#
#   Test case 3.6.4 demonstrates that the expression is evaluated 
#   separately for each row if the INSERT is an "INSERT INTO ... SELECT ..."

# default compile time value.
#
sqlite4_limit db SQLITE4_LIMIT_COLUMN [expr $::SQLITE4_MAX_COLUMN+2]
do_catchsql_test e_createtable-3.11.3 [subst {
  CREATE TABLE t11([columns [expr $::SQLITE4_MAX_COLUMN+1]]);
}] {1 {too many columns on t11}}

sqlite4_limit db SQLITE4_LIMIT_LENGTH 90010
do_execsql_test e_createtable-3.11.4 {
  CREATE TABLE t12(a, b, c);
  INSERT INTO t12 VALUES(randomblob(30000),randomblob(30000),randomblob(30000));
} {}
do_catchsql_test e_createtable-3.11.5 {
  INSERT INTO t12 VALUES(randomblob(30001),randomblob(30000),randomblob(30000));
} {1 {string or blob too big}}

  CREATE TABLE t2(x, y, PRIMARY KEY(x, y));
  INSERT INTO t2 VALUES(0,          'zero');
  INSERT INTO t2 VALUES(45.5,       'one');
  INSERT INTO t2 VALUES('brambles', 'two');
  INSERT INTO t2 VALUES(X'ABCDEF',  'three');
} {}

do_createtable_tests 4.3.1 -error { %s not unique } {
  1    "INSERT INTO t1 VALUES(0, 0)"                 {"column x is"}
  2    "INSERT INTO t1 VALUES(45.5, 'abc')"          {"column x is"}
  3    "INSERT INTO t1 VALUES(0.0, 'abc')"           {"column x is"}
  4    "INSERT INTO t1 VALUES('brambles', 'abc')"    {"column x is"}
  5    "INSERT INTO t1 VALUES(X'ABCDEF', 'abc')"     {"column x is"}

  6    "INSERT INTO t2 VALUES(0, 'zero')"            {"columns x, y are"}
  7    "INSERT INTO t2 VALUES(45.5, 'one')"          {"columns x, y are"}
  8    "INSERT INTO t2 VALUES(0.0, 'zero')"          {"columns x, y are"}
  9    "INSERT INTO t2 VALUES('brambles', 'two')"    {"columns x, y are"}
  10   "INSERT INTO t2 VALUES(X'ABCDEF', 'three')"   {"columns x, y are"}
}
do_createtable_tests 4.3.2 {
  1    "INSERT INTO t1 VALUES(-1, 0)"                {}
  2    "INSERT INTO t1 VALUES(45.2, 'abc')"          {}
  3    "INSERT INTO t1 VALUES(0.01, 'abc')"          {}
  4    "INSERT INTO t1 VALUES('bramble', 'abc')"     {}
  5    "INSERT INTO t1 VALUES(X'ABCDEE', 'abc')"     {}

  6    "INSERT INTO t2 VALUES(0, 0)"                 {}
  7    "INSERT INTO t2 VALUES(45.5, 'abc')"          {}
  8    "INSERT INTO t2 VALUES(0.0, 'abc')"           {}
  9    "INSERT INTO t2 VALUES('brambles', 'abc')"    {}
  10   "INSERT INTO t2 VALUES(X'ABCDEF', 'abc')"     {}
}
do_createtable_tests 4.3.3 -error { %s not unique } {
  1    "UPDATE t1 SET x=0           WHERE y='two'"    {"column x is"}
  2    "UPDATE t1 SET x='brambles'  WHERE y='three'"  {"column x is"}
  3    "UPDATE t1 SET x=45.5        WHERE y='zero'"   {"column x is"}
  4    "UPDATE t1 SET x=X'ABCDEF'   WHERE y='one'"    {"column x is"}
  5    "UPDATE t1 SET x=0.0         WHERE y='three'"  {"column x is"}

  6    "UPDATE t2 SET x=0, y='zero' WHERE y='two'"    {"columns x, y are"}
  7    "UPDATE t2 SET x='brambles', y='two' WHERE y='three'"  
       {"columns x, y are"}
  8    "UPDATE t2 SET x=45.5, y='one' WHERE y='zero'" {"columns x, y are"}
  9    "UPDATE t2 SET x=X'ABCDEF', y='three' WHERE y='one'" 
       {"columns x, y are"}
  10   "UPDATE t2 SET x=0.0, y='zero'        WHERE y='three'"  
       {"columns x, y are"}
}


# EVIDENCE-OF: R-52572-02078 For the purposes of determining the
# uniqueness of primary key values, NULL values are considered distinct
# from all other values, including other NULLs.
#
do_createtable_tests 4.4 {
  1    "INSERT INTO t1 VALUES(NULL, 0)"              {}
  2    "INSERT INTO t1 VALUES(NULL, 0)"              {}
  3    "INSERT INTO t1 VALUES(NULL, 0)"              {}

  4    "INSERT INTO t2 VALUES(NULL, 'zero')"         {}
  5    "INSERT INTO t2 VALUES(NULL, 'one')"          {}
  6    "INSERT INTO t2 VALUES(NULL, 'two')"          {}
  7    "INSERT INTO t2 VALUES(NULL, 'three')"        {}

  8    "INSERT INTO t2 VALUES(0, NULL)"              {}
  9    "INSERT INTO t2 VALUES(45.5, NULL)"           {}
  10   "INSERT INTO t2 VALUES(0.0, NULL)"            {}
  11   "INSERT INTO t2 VALUES('brambles', NULL)"     {}
  12   "INSERT INTO t2 VALUES(X'ABCDEF', NULL)"      {}

  13   "INSERT INTO t2 VALUES(NULL, NULL)"           {}
  14   "INSERT INTO t2 VALUES(NULL, NULL)"           {}
}

# EVIDENCE-OF: R-61866-38053 Unless the column is an INTEGER PRIMARY KEY
# SQLite allows NULL values in a PRIMARY KEY column.
#
#     If the column is an integer primary key, attempting to insert a NULL
#     into the column triggers the auto-increment behaviour. Attempting
#     to use UPDATE to set an ipk column to a NULL value is an error.
#
do_createtable_tests 4.5.1 {
  1    "SELECT count(*) FROM t1 WHERE x IS NULL"                   3
  2    "SELECT count(*) FROM t2 WHERE x IS NULL"                   6
  3    "SELECT count(*) FROM t2 WHERE y IS NULL"                   7
  4    "SELECT count(*) FROM t2 WHERE x IS NULL AND y IS NULL"     2
}
do_execsql_test 4.5.2 {
  CREATE TABLE t3(s, u INTEGER PRIMARY KEY, v);
  INSERT INTO t3 VALUES(1, NULL, 2);
  INSERT INTO t3 VALUES('x', NULL, 'y');
  SELECT u FROM t3;
} {1 2}
do_catchsql_test 4.5.3 {
  INSERT INTO t3 VALUES(2, 5, 3);
  UPDATE t3 SET u = NULL WHERE s = 2;
} {1 {datatype mismatch}}

# EVIDENCE-OF: R-00227-21080 A UNIQUE constraint is similar to a PRIMARY
# KEY constraint, except that a single table may have any number of
# UNIQUE constraints.
#
drop_all_tables
do_createtable_tests 4.6 {

# EVIDENCE-OF: R-26983-26377 INTEGER PRIMARY KEY columns aside, both
# UNIQUE and PRIMARY KEY constraints are implemented by creating an
# index in the database (in the same way as a "CREATE UNIQUE INDEX"
# statement would).
do_createtable_tests 4.9 -repair drop_all_tables -query {
  SELECT count(*) FROM sqlite_master WHERE type='index'
} {
  1    "CREATE TABLE t1(a TEXT PRIMARY KEY, b)"              1
  2    "CREATE TABLE t1(a INTEGER PRIMARY KEY, b)"           0
  3    "CREATE TABLE t1(a TEXT UNIQUE, b)"                   1
  4    "CREATE TABLE t1(a PRIMARY KEY, b TEXT UNIQUE)"       2
  5    "CREATE TABLE t1(a PRIMARY KEY, b, c, UNIQUE(c, b))"  2
}

# EVIDENCE-OF: R-02252-33116 Such an index is used like any other index
# in the database to optimize queries.
#
do_execsql_test 4.10.0 {
  CREATE TABLE t1(a, b PRIMARY KEY);
  CREATE TABLE t2(a, b, c, UNIQUE(b, c));
}
do_createtable_tests 4.10 {
  1    "EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE b = 5" 
       {0 0 0 {SEARCH TABLE t1 USING INDEX sqlite_autoindex_t1_1 (b=?) (~1 rows)}}

  2    "EXPLAIN QUERY PLAN SELECT * FROM t2 ORDER BY b, c"
       {0 0 0 {SCAN TABLE t2 USING INDEX sqlite_autoindex_t2_1 (~1000000 rows)}}

  3    "EXPLAIN QUERY PLAN SELECT * FROM t2 WHERE b=10 AND c>10"
       {0 0 0 {SEARCH TABLE t2 USING INDEX sqlite_autoindex_t2_1 (b=? AND c>?) (~2 rows)}}
}

# EVIDENCE-OF: R-45493-35653 A CHECK constraint may be attached to a
# column definition or specified as a table constraint. In practice it
# makes no difference.
#
#   All the tests that deal with CHECK constraints below (4.11.* and 

  INSERT INTO t3_ig SELECT * FROM t3_ab;
  INSERT INTO t3_fa SELECT * FROM t3_ab;
  INSERT INTO t3_re SELECT * FROM t3_ab;
  INSERT INTO t3_xx SELECT * FROM t3_ab;
}

foreach {tn tbl res ac data} {
  1   t1_ab    {1 {column a is not unique}} 0 {1 one 2 two 3 three}
  2   t1_ro    {1 {column a is not unique}} 1 {1 one 2 two}
  3   t1_fa    {1 {column a is not unique}} 0 {1 one 2 two 3 three 4 string}
  4   t1_ig    {0 {}} 0 {1 one 2 two 3 three 4 string 6 string}
  5   t1_re    {0 {}} 0 {1 one 2 two 4 string 3 string 6 string}
  6   t1_xx    {1 {column a is not unique}} 0 {1 one 2 two 3 three}
} {
  catchsql COMMIT
  do_execsql_test  4.15.$tn.1 "BEGIN; INSERT INTO $tbl VALUES(3, 'three')"

  do_catchsql_test 4.15.$tn.2 " 
    INSERT INTO $tbl SELECT ((a%2)*a+3), 'string' FROM $tbl;
  " $res

  do_test e_createtable-4.15.$tn.3 { sqlite4_get_autocommit db } $ac
  do_execsql_test 4.15.$tn.4 "SELECT * FROM $tbl" $data
}
foreach {tn tbl res ac data} {
  1   t2_ab    {1 {t2_ab.b may not be NULL}} 0 {1 one 2 two 3 three}
  2   t2_ro    {1 {t2_ro.b may not be NULL}} 1 {1 one 2 two}
  3   t2_fa    {1 {t2_fa.b may not be NULL}} 0 {1 one 2 two 3 three 4 xx}
  4   t2_ig    {0 {}} 0 {1 one 2 two 3 three 4 xx 6 xx}
  5   t2_re    {1 {t2_re.b may not be NULL}} 0 {1 one 2 two 3 three}
  6   t2_xx    {1 {t2_xx.b may not be NULL}} 0 {1 one 2 two 3 three}
} {
  catchsql COMMIT
  do_execsql_test  4.16.$tn.1 "BEGIN; INSERT INTO $tbl VALUES(3, 'three')"

  do_catchsql_test 4.16.$tn.2 " 
    INSERT INTO $tbl SELECT a+3, CASE a WHEN 2 THEN NULL ELSE 'xx' END FROM $tbl
  " $res

  do_test e_createtable-4.16.$tn.3 { sqlite4_get_autocommit db } $ac
  do_execsql_test 4.16.$tn.4 "SELECT * FROM $tbl" $data
}
foreach {tn tbl res ac data} {
  1   t3_ab    {1 {columns a, b are not unique}} 0 {1 one 2 two 3 three}
  2   t3_ro    {1 {columns a, b are not unique}} 1 {1 one 2 two}
  3   t3_fa    {1 {columns a, b are not unique}} 0 {1 one 2 two 3 three 4 three}
  4   t3_ig    {0 {}} 0 {1 one 2 two 3 three 4 three 6 three}
  5   t3_re    {0 {}} 0 {1 one 2 two 4 three 3 three 6 three}
  6   t3_xx    {1 {columns a, b are not unique}} 0 {1 one 2 two 3 three}
} {
  catchsql COMMIT
  do_execsql_test  4.17.$tn.1 "BEGIN; INSERT INTO $tbl VALUES(3, 'three')"

  do_catchsql_test 4.17.$tn.2 " 
    INSERT INTO $tbl SELECT ((a%2)*a+3), 'three' FROM $tbl
  " $res

  do_test e_createtable-4.17.$tn.3 { sqlite4_get_autocommit db } $ac
  do_execsql_test 4.17.$tn.4 "SELECT * FROM $tbl" $data
}
catchsql COMMIT

# EVIDENCE-OF: R-12645-39772 Or, if a constraint definition does not
# include a conflict-clause or it is a CHECK constraint, the default
# conflict resolution algorithm is ABORT.

  INSERT INTO t4 VALUES(1, 2);
  INSERT INTO t4 VALUES(3, 4);
}
do_execsql_test  4.18.2 { BEGIN; INSERT INTO t4 VALUES(5, 6) }
do_catchsql_test 4.18.3 { 
  INSERT INTO t4 SELECT a+4, b+4 FROM t4
} {1 {constraint failed}}
do_test e_createtable-4.18.4 { sqlite4_get_autocommit db } 0
do_execsql_test 4.18.5 { SELECT * FROM t4 } {1 2 3 4 5 6}

# EVIDENCE-OF: R-19114-56113 Different constraints within the same table
# may have different default conflict resolution algorithms.
#
do_execsql_test 4.19.0 {
  CREATE TABLE t5(a NOT NULL ON CONFLICT IGNORE, b NOT NULL ON CONFLICT ABORT);
}
do_catchsql_test 4.19.1 { INSERT INTO t5 VALUES(NULL, 'not null') } {0 {}}
do_execsql_test  4.19.2 { SELECT * FROM t5 } {}
do_catchsql_test 4.19.3 { INSERT INTO t5 VALUES('not null', NULL) } \
  {1 {t5.b may not be NULL}}
do_execsql_test  4.19.4 { SELECT * FROM t5 } {}

#------------------------------------------------------------------------
# Tests for INTEGER PRIMARY KEY and rowid related statements.
#

# EVIDENCE-OF: R-52584-04009 The rowid value can be accessed using one
# of the special case-independent names "rowid", "oid", or "_rowid_" in
# place of a column name.
#
drop_all_tables
do_execsql_test 5.1.0 {
  CREATE TABLE t1(x, y);
  INSERT INTO t1 VALUES('one', 'first');
  INSERT INTO t1 VALUES('two', 'second');
  INSERT INTO t1 VALUES('three', 'third');
}
do_createtable_tests 5.1 {
  1   "SELECT rowid FROM t1"        {1 2 3}
  2   "SELECT oid FROM t1"          {1 2 3}
  3   "SELECT _rowid_ FROM t1"      {1 2 3}
  4   "SELECT ROWID FROM t1"        {1 2 3}
  5   "SELECT OID FROM t1"          {1 2 3}
  6   "SELECT _ROWID_ FROM t1"      {1 2 3}
  7   "SELECT RoWiD FROM t1"        {1 2 3}
  8   "SELECT OiD FROM t1"          {1 2 3}
  9   "SELECT _RoWiD_ FROM t1"      {1 2 3}
}

# EVIDENCE-OF: R-26501-17306 If a table contains a user defined column
# named "rowid", "oid" or "_rowid_", then that name always refers the
# explicitly declared column and cannot be used to retrieve the integer
# rowid value.
#
do_execsql_test 5.2.0 {
  CREATE TABLE t2(oid, b);
  CREATE TABLE t3(a, _rowid_);
  CREATE TABLE t4(a, b, rowid);

  INSERT INTO t2 VALUES('one', 'two');
  INSERT INTO t2 VALUES('three', 'four');

  INSERT INTO t3 VALUES('five', 'six');
  INSERT INTO t3 VALUES('seven', 'eight');

  INSERT INTO t4 VALUES('nine', 'ten', 'eleven');
  INSERT INTO t4 VALUES('twelve', 'thirteen', 'fourteen');
}
do_createtable_tests 5.2 {
  1   "SELECT oid, rowid, _rowid_ FROM t2"   {one 1 1      three 2 2}
  2   "SELECT oid, rowid, _rowid_ FROM t3"   {1 1 six      2 2 eight} 
  3   "SELECT oid, rowid, _rowid_ FROM t4"   {1 eleven 1   2 fourteen 2}
}


# Argument $tbl is the name of a table in the database. Argument $col is
# the name of one of the tables columns. Return 1 if $col is an alias for
# the rowid, or 0 otherwise.
#
proc is_integer_primary_key {tbl col} {
  lindex [db eval [subst {
    DELETE FROM $tbl;
    INSERT INTO $tbl ($col) VALUES(0);
    SELECT (rowid==$col) FROM $tbl;
    DELETE FROM $tbl;
  }]] 0
}

# EVIDENCE-OF: R-53738-31673 With one exception, if a table has a
# primary key that consists of a single column, and the declared type of
# that column is "INTEGER" in any mixture of upper and lower case, then
# the column becomes an alias for the rowid.
#
# EVIDENCE-OF: R-45951-08347 if the declaration of a column with
# declared type "INTEGER" includes an "PRIMARY KEY DESC" clause, it does
# not become an alias for the rowid and is not classified as an integer
# primary key.
#
do_createtable_tests 5.3 -tclquery { 
  is_integer_primary_key t5 pk
} -repair {
  catchsql { DROP TABLE t5 }
} {
  1   "CREATE TABLE t5(pk integer primary key)"                         1
  2   "CREATE TABLE t5(pk integer, primary key(pk))"                    1
  3   "CREATE TABLE t5(pk integer, v integer, primary key(pk))"         1
  4   "CREATE TABLE t5(pk integer, v integer, primary key(pk, v))"      0
  5   "CREATE TABLE t5(pk int, v integer, primary key(pk, v))"          0
  6   "CREATE TABLE t5(pk int, v integer, primary key(pk))"             0
  7   "CREATE TABLE t5(pk int primary key, v integer)"                  0
  8   "CREATE TABLE t5(pk inTEger primary key)"                         1
  9   "CREATE TABLE t5(pk inteGEr, primary key(pk))"                    1
  10  "CREATE TABLE t5(pk INTEGER, v integer, primary key(pk))"         1
}

# EVIDENCE-OF: R-41444-49665 Other integer type names like "INT" or
# "BIGINT" or "SHORT INTEGER" or "UNSIGNED INTEGER" causes the primary
# key column to behave as an ordinary table column with integer affinity
# and a unique index, not as an alias for the rowid.
#
do_execsql_test 5.4.1 {
  CREATE TABLE t6(pk INT primary key);
  CREATE TABLE t7(pk BIGINT primary key);
  CREATE TABLE t8(pk SHORT INTEGER primary key);
  CREATE TABLE t9(pk UNSIGNED INTEGER primary key);
} 
do_test e_createtable-5.4.2.1 { is_integer_primary_key t6 pk } 0
do_test e_createtable-5.4.2.2 { is_integer_primary_key t7 pk } 0
do_test e_createtable-5.4.2.3 { is_integer_primary_key t8 pk } 0
do_test e_createtable-5.4.2.4 { is_integer_primary_key t9 pk } 0

do_execsql_test 5.4.3 {
  INSERT INTO t6 VALUES('2.0');
  INSERT INTO t7 VALUES('2.0');
  INSERT INTO t8 VALUES('2.0');
  INSERT INTO t9 VALUES('2.0');
  SELECT typeof(pk), pk FROM t6;
  SELECT typeof(pk), pk FROM t7;
  SELECT typeof(pk), pk FROM t8;
  SELECT typeof(pk), pk FROM t9;
} {integer 2 integer 2 integer 2 integer 2}

do_catchsql_test 5.4.4.1 { 
  INSERT INTO t6 VALUES(2) 
} {1 {column pk is not unique}}
do_catchsql_test 5.4.4.2 { 
  INSERT INTO t7 VALUES(2) 
} {1 {column pk is not unique}}
do_catchsql_test 5.4.4.3 { 
  INSERT INTO t8 VALUES(2) 
} {1 {column pk is not unique}}
do_catchsql_test 5.4.4.4 { 
  INSERT INTO t9 VALUES(2) 
} {1 {column pk is not unique}}

# EVIDENCE-OF: R-56094-57830 the following three table declarations all
# cause the column "x" to be an alias for the rowid (an integer primary
# key): CREATE TABLE t(x INTEGER PRIMARY KEY ASC, y, z); CREATE TABLE
# t(x INTEGER, y, z, PRIMARY KEY(x ASC)); CREATE TABLE t(x INTEGER, y,
# z, PRIMARY KEY(x DESC));
#
# EVIDENCE-OF: R-20149-25884 the following declaration does not result
# in "x" being an alias for the rowid: CREATE TABLE t(x INTEGER PRIMARY
# KEY DESC, y, z);
#
do_createtable_tests 5 -tclquery { 
  is_integer_primary_key t x
} -repair {
  catchsql { DROP TABLE t }
} {
  5.1    "CREATE TABLE t(x INTEGER PRIMARY KEY ASC, y, z)"      1
  5.2    "CREATE TABLE t(x INTEGER, y, z, PRIMARY KEY(x ASC))"  1
  5.3    "CREATE TABLE t(x INTEGER, y, z, PRIMARY KEY(x DESC))" 1
  6.1    "CREATE TABLE t(x INTEGER PRIMARY KEY DESC, y, z)"     0
}

# EVIDENCE-OF: R-03733-29734 Rowid values may be modified using an
# UPDATE statement in the same way as any other column value can, either
# using one of the built-in aliases ("rowid", "oid" or "_rowid_") or by
# using an alias created by an integer primary key.
#
do_execsql_test 5.7.0 {
  CREATE TABLE t10(a, b);
  INSERT INTO t10 VALUES('ten', 10);

  CREATE TABLE t11(a, b INTEGER PRIMARY KEY);
  INSERT INTO t11 VALUES('ten', 10);
}
do_createtable_tests 5.7.1 -query { 
  SELECT rowid, _rowid_, oid FROM t10;
} {
  1    "UPDATE t10 SET rowid = 5"   {5 5 5}
  2    "UPDATE t10 SET _rowid_ = 6" {6 6 6}
  3    "UPDATE t10 SET oid = 7"     {7 7 7}
}
do_createtable_tests 5.7.2 -query { 
  SELECT rowid, _rowid_, oid, b FROM t11;
} {
  1    "UPDATE t11 SET rowid = 5"   {5 5 5 5}
  2    "UPDATE t11 SET _rowid_ = 6" {6 6 6 6}
  3    "UPDATE t11 SET oid = 7"     {7 7 7 7}
  4    "UPDATE t11 SET b = 8"       {8 8 8 8}
}

# EVIDENCE-OF: R-58706-14229 Similarly, an INSERT statement may provide
# a value to use as the rowid for each row inserted.
#
do_createtable_tests 5.8.1 -query { 
  SELECT rowid, _rowid_, oid FROM t10;
} -repair { 
  execsql { DELETE FROM t10 } 
} {
  1    "INSERT INTO t10(oid) VALUES(15)"           {15 15 15}
  2    "INSERT INTO t10(rowid) VALUES(16)"         {16 16 16}
  3    "INSERT INTO t10(_rowid_) VALUES(17)"       {17 17 17}
  4    "INSERT INTO t10(a, b, oid) VALUES(1,2,3)"  {3 3 3}
}
do_createtable_tests 5.8.2 -query { 
  SELECT rowid, _rowid_, oid, b FROM t11;
} -repair { 
  execsql { DELETE FROM t11 } 
} {
  1    "INSERT INTO t11(oid) VALUES(15)"           {15 15 15 15}
  2    "INSERT INTO t11(rowid) VALUES(16)"         {16 16 16 16}
  3    "INSERT INTO t11(_rowid_) VALUES(17)"       {17 17 17 17}
  4    "INSERT INTO t11(a, b) VALUES(1,2)"         {2 2 2 2}
}

# EVIDENCE-OF: R-32326-44592 Unlike normal SQLite columns, an integer
# primary key or rowid column must contain integer values. Integer
# primary key or rowid columns are not able to hold floating point
# values, strings, BLOBs, or NULLs.
#
#     This is considered by the tests for the following 3 statements,
#     which show that:
#
#       1. Attempts to UPDATE a rowid column to a non-integer value fail,
#       2. Attempts to INSERT a real, string or blob value into a rowid 
#          column fail, and
#       3. Attempting to INSERT a NULL value into a rowid column causes the
#          system to automatically select an integer value to use.
#


# EVIDENCE-OF: R-64224-62578 If an UPDATE statement attempts to set an
# integer primary key or rowid column to a NULL or blob value, or to a
# string or real value that cannot be losslessly converted to an
# integer, a "datatype mismatch" error occurs and the statement is
# aborted.
#
drop_all_tables
do_execsql_test 5.9.0 {
  CREATE TABLE t12(x INTEGER PRIMARY KEY, y);
  INSERT INTO t12 VALUES(5, 'five');
}
do_createtable_tests 5.9.1 -query { SELECT typeof(x), x FROM t12 } {
  1   "UPDATE t12 SET x = 4"       {integer 4}
  2   "UPDATE t12 SET x = 10.0"    {integer 10}
  3   "UPDATE t12 SET x = '12.0'"  {integer 12}
  4   "UPDATE t12 SET x = '-15.0'" {integer -15}
}
do_createtable_tests 5.9.2 -error {
  datatype mismatch
} {
  1   "UPDATE t12 SET x = 4.1"         {}
  2   "UPDATE t12 SET x = 'hello'"     {}
  3   "UPDATE t12 SET x = NULL"        {}
  4   "UPDATE t12 SET x = X'ABCD'"     {}
  5   "UPDATE t12 SET x = X'3900'"     {}
  6   "UPDATE t12 SET x = X'39'"       {}
}

# EVIDENCE-OF: R-05734-13629 If an INSERT statement attempts to insert a
# blob value, or a string or real value that cannot be losslessly
# converted to an integer into an integer primary key or rowid column, a
# "datatype mismatch" error occurs and the statement is aborted.
#
do_execsql_test 5.10.0 { DELETE FROM t12 }
do_createtable_tests 5.10.1 -error { 
  datatype mismatch
} {
  1   "INSERT INTO t12(x) VALUES(4.1)"     {}
  2   "INSERT INTO t12(x) VALUES('hello')" {}
  3   "INSERT INTO t12(x) VALUES(X'ABCD')" {}
  4   "INSERT INTO t12(x) VALUES(X'3900')" {}
  5   "INSERT INTO t12(x) VALUES(X'39')"   {}
}
do_createtable_tests 5.10.2 -query { 
  SELECT typeof(x), x FROM t12 
} -repair {
  execsql { DELETE FROM t12 }
} {
  1   "INSERT INTO t12(x) VALUES(4)"       {integer 4}
  2   "INSERT INTO t12(x) VALUES(10.0)"    {integer 10}
  3   "INSERT INTO t12(x) VALUES('12.0')"  {integer 12}
  4   "INSERT INTO t12(x) VALUES('4e3')"   {integer 4000}
  5   "INSERT INTO t12(x) VALUES('-14.0')" {integer -14}
}

# EVIDENCE-OF: R-07986-46024 If an INSERT statement attempts to insert a
# NULL value into a rowid or integer primary key column, the system
# chooses an integer value to use as the rowid automatically.
#
do_execsql_test 5.11.0 { DELETE FROM t12 }
do_createtable_tests 5.11 -query { 
  SELECT typeof(x), x FROM t12 WHERE y IS (SELECT max(y) FROM t12)
} {
  1   "INSERT INTO t12 DEFAULT VALUES"                {integer 1}
  2   "INSERT INTO t12(y)   VALUES(5)"                {integer 2}
  3   "INSERT INTO t12(x,y) VALUES(NULL, 10)"         {integer 3}
  4   "INSERT INTO t12(x,y) SELECT NULL, 15 FROM t12" 
      {integer 4 integer 5 integer 6}
  5   "INSERT INTO t12(y) SELECT 20 FROM t12 LIMIT 3"
      {integer 7 integer 8 integer 9}
}

finish_test

# top-level statement (by searching first the TEMP database, then the
# main database, then any other databases in the order they were
# attached).
#
do_execsql_test e_delete-2.3.0 {
  DROP TRIGGER aux.tr1;
  DROP TRIGGER main.tr1;
  DELETE FROM main.t8 WHERE oid>1;
  DELETE FROM aux.t8 WHERE oid>1;
  INSERT INTO aux.t9 VALUES(1, 2);
  INSERT INTO main.t7 VALUES(3, 4);
} {}
do_execsql_test e_delete-2.3.1 {
  SELECT count(*) FROM temp.t7 UNION ALL SELECT count(*) FROM main.t7 UNION ALL
  SELECT count(*) FROM aux.t7  UNION ALL SELECT count(*) FROM aux2.t7;

#   ::oplist         A list of all SQL operators supported by SQLite.
#
foreach {op opn} {
      ||   cat     *   mul       /  div       %     mod       +      add
      -    sub     <<  lshift    >> rshift    &     bitand    |      bitor
      <    less    <=  lesseq    >  more      >=    moreeq    =      eq1
      ==   eq2     <>  ne1       != ne2       IS    is        LIKE   like
      GLOB glob    AND and       OR or        MATCH match     REGEXP regexp
      {IS NOT} isnt
} {
  set ::opname($op) $opn
}
set oplist [list]
foreach {prec opl} {
  1   ||
  2   {* / %}
  3   {+ -}
  4   {<< >> & |}
  5   {< <= > >=}
  6   {= == != <> IS {IS NOT} LIKE GLOB MATCH REGEXP}
  7   AND
  8   OR
} {
  foreach op $opl { 
    set ::opprec($op) $prec 
    lappend oplist $op
  }

# the operand.
#
foreach {tn literal type} {
  1     'helloworld'   text
  2     45             integer
  3     45.2           real
  4     45.0           real
  5     X'ABCDEF'      blob
  6     NULL           null
} {
  set sql " SELECT quote( + $literal ), typeof( + $literal) "
  do_execsql_test e_expr-3.$tn $sql [list $literal $type]
}

#-------------------------------------------------------------------------

 
  54 "EXPR1 LIKE EXPR2"
  55 "EXPR1 LIKE EXPR2 ESCAPE EXPR"
  56 "EXPR1 GLOB EXPR2"
  57 "EXPR1 GLOB EXPR2 ESCAPE EXPR"
  58 "EXPR1 REGEXP EXPR2"
  59 "EXPR1 REGEXP EXPR2 ESCAPE EXPR"
  60 "EXPR1 MATCH EXPR2"
  61 "EXPR1 MATCH EXPR2 ESCAPE EXPR"
  62 "EXPR1 NOT LIKE EXPR2"
  63 "EXPR1 NOT LIKE EXPR2 ESCAPE EXPR"
  64 "EXPR1 NOT GLOB EXPR2"
  65 "EXPR1 NOT GLOB EXPR2 ESCAPE EXPR"
  66 "EXPR1 NOT REGEXP EXPR2"
  67 "EXPR1 NOT REGEXP EXPR2 ESCAPE EXPR"
  68 "EXPR1 NOT MATCH EXPR2"

do_execsql_test e_expr-18.2.1 { SELECT 'abc' REGEXP 'def' } 1
do_test         e_expr-18.2.2 { set regexpargs } {def abc}
set ::regexpargs [list]
do_execsql_test e_expr-18.2.3 { SELECT 'X' NOT REGEXP 'Y' } 0
do_test         e_expr-18.2.4 { set regexpargs } {Y X}
sqlite4 db test.db

# EVIDENCE-OF: R-42037-37826 The default match() function implementation
# raises an exception and is not really useful for anything.
#
do_catchsql_test e_expr-19.1.1 { 
  SELECT 'abc' MATCH 'def' 
} {1 {unable to use function MATCH in the requested context}}
do_catchsql_test e_expr-19.1.2 { 
  SELECT match('abc', 'def')
} {1 {unable to use function MATCH in the requested context}}

# EVIDENCE-OF: R-37916-47407 The MATCH operator is a special syntax for
# the match() application-defined function.
#
# EVIDENCE-OF: R-06021-09373 But extensions can override the match()
# function with more helpful logic.
#
proc matchfunc {args} {
  eval lappend ::matchargs $args
  return 1
}
db func match -argcount 2 matchfunc
set ::matchargs [list]
do_execsql_test e_expr-19.2.1 { SELECT 'abc' MATCH 'def' } 1
do_test         e_expr-19.2.2 { set matchargs } {def abc}
set ::matchargs [list]
do_execsql_test e_expr-19.2.3 { SELECT 'X' NOT MATCH 'Y' } 0
do_test         e_expr-19.2.4 { set matchargs } {Y X}
sqlite4 db test.db

#-------------------------------------------------------------------------
# Test cases for the testable statements related to the CASE expression.
#
# EVIDENCE-OF: R-15199-61389 There are two basic forms of the CASE
# expression: those with a base expression and those without.
#
do_execsql_test e_expr-20.1 {

  SELECT CASE 'AbC' WHEN 'abc' THEN 'A' WHEN a THEN 'B' END FROM t1
} {B}
do_execsql_test e_expr-23.1.4 {
  SELECT CASE a WHEN b THEN 'A' ELSE 'B' END FROM t1
} {B}
do_execsql_test e_expr-23.1.5 {
  SELECT CASE b WHEN a THEN 'A' ELSE 'B' END FROM t1
} {A}
do_execsql_test e_expr-23.1.6 {
  SELECT CASE 55 WHEN '55' THEN 'A' ELSE 'B' END
} {B}
do_execsql_test e_expr-23.1.7 {
  SELECT CASE c WHEN '55' THEN 'A' ELSE 'B' END FROM t1
} {A}
do_execsql_test e_expr-23.1.8 {

do_expr_test e_expr-27.3.2 { CAST('def' AS shobblob_x) } blob def
do_expr_test e_expr-27.3.3 { CAST('ghi' AS abbLOb10)   } blob ghi

# EVIDENCE-OF: R-22956-37754 Casting to a BLOB consists of first casting
# the value to TEXT in the encoding of the database connection, then
# interpreting the resulting byte sequence as a BLOB instead of as TEXT.
#
do_qexpr_test e_expr-27.4.1 { CAST('ghi' AS blob) } X'676869'
do_qexpr_test e_expr-27.4.2 { CAST(456 AS blob) }   X'343536'
do_qexpr_test e_expr-27.4.3 { CAST(1.78 AS blob) }  X'312E3738'
rename db db2
sqlite4 db :memory:
ifcapable {utf16} {
db eval { PRAGMA encoding = 'utf-16le' }
do_qexpr_test e_expr-27.4.4 { CAST('ghi' AS blob) } X'670068006900'
do_qexpr_test e_expr-27.4.5 { CAST(456 AS blob) }   X'340035003600'
do_qexpr_test e_expr-27.4.6 { CAST(1.78 AS blob) }  X'31002E0037003800'
}
db close
sqlite4 db :memory:
db eval { PRAGMA encoding = 'utf-16be' }
ifcapable {utf16} {
do_qexpr_test e_expr-27.4.7 { CAST('ghi' AS blob) } X'006700680069'
do_qexpr_test e_expr-27.4.8 { CAST(456 AS blob) }   X'003400350036'
do_qexpr_test e_expr-27.4.9 { CAST(1.78 AS blob) }  X'0031002E00370038'
}
db close
rename db2 db

# EVIDENCE-OF: R-04207-37981 To cast a BLOB value to TEXT, the sequence
# of bytes that make up the BLOB is interpreted as text encoded using
# the database encoding.

# resulting TEXT uses the encoding of the database connection.
#
do_expr_test e_expr-28.2.1 { CAST (1 AS text)   }     text 1
do_expr_test e_expr-28.2.2 { CAST (45 AS text)  }     text 45
do_expr_test e_expr-28.2.3 { CAST (-45 AS text) }     text -45
do_expr_test e_expr-28.2.4 { CAST (8.8 AS text)    }  text 8.8
do_expr_test e_expr-28.2.5 { CAST (2.3e+5 AS text) }  text 230000.0
do_expr_test e_expr-28.2.6 { CAST (-2.3e-5 AS text) } text -2.3e-05
do_expr_test e_expr-28.2.7 { CAST (0.0 AS text) }     text 0.0
do_expr_test e_expr-28.2.7 { CAST (0 AS text) }       text 0

# EVIDENCE-OF: R-26346-36443 When casting a BLOB value to a REAL, the
# value is first converted to TEXT.
#
do_expr_test e_expr-29.1.1 { CAST (X'312E3233' AS REAL) } real 1.23
do_expr_test e_expr-29.1.2 { CAST (X'3233302E30' AS REAL) } real 230.0
do_expr_test e_expr-29.1.3 { CAST (X'2D392E3837' AS REAL) } real -9.87

do_expr_test e_expr-31.1.3 { CAST(-1.99999 AS INTEGER) } integer -1
do_expr_test e_expr-31.1.4 { CAST(-0.99999 AS INTEGER) } integer 0

# EVIDENCE-OF: R-49503-28105 If a REAL is too large to be represented as
# an INTEGER then the result of the cast is the largest negative
# integer: -9223372036854775808.
#
do_expr_test e_expr-31.2.1 { CAST(2e+50 AS INT) } integer -9223372036854775808
do_expr_test e_expr-31.2.2 { CAST(-2e+50 AS INT) } integer -9223372036854775808
do_expr_test e_expr-31.2.3 { 
  CAST(-9223372036854775809.0 AS INT)
} integer -9223372036854775808
do_expr_test e_expr-31.2.4 { 
  CAST(9223372036854775809.0 AS INT)
} integer -9223372036854775808


# EVIDENCE-OF: R-09295-61337 Casting a TEXT or BLOB value into NUMERIC
# first does a forced conversion into REAL but then further converts the
# result into INTEGER if and only if the conversion from REAL to INTEGER
# is lossless and reversible.
#

  date.test
  default.test
  delete.test delete2.test delete3.test
  descidx1.test descidx2.test descidx3.test 
  distinct.test distinctagg.test
  enc.test enc4.test
  exists.test
  e_droptrigger.test e_dropview.test

  e_resolve.test e_dropview.test
  e_select2.test
  fkey1.test fkey2.test fkey3.test fkey4.test
  func.test func2.test func3.test 
  fuzz2.test 
  in.test in4.test
  index2.test index3.test index4.test 
  insert.test insert2.test insert3.test insert5.test
  join.test join2.test join3.test join4.test join5.test join6.test

do_execsql_test 80.2 {
  SELECT idx, count(*), sum(length(sample)) FROM t1 GROUP BY idx
} {t1 2 4 t1i1 2 4 t1i2 2 4 t1i3 2 4}

#-------------------------------------------------------------------------
reset_db
do_test alter-81.1 {
  execsql {
    CREATE TABLE t1(a TEXT);
    INSERT INTO t1 VALUES(5.4e-08);
    SELECT a FROM t1;
  }
} {5.4e-8}

#-------------------------------------------------------------------------
reset_db
do_test alter-82.1 {
  execsql { CREATE TABLE t1(a TEXT) }
  execsql { SELECT * FROM sqlite_master }
} {table t1 t1 2 {CREATE TABLE t1(a TEXT)}}

do_test alter-82.2 {
  execsql { DROP TABLE t1 }
  execsql { CREATE TABLE t1(a TEXT) }
  execsql { SELECT * FROM sqlite_master }
} {table t1 t1 2 {CREATE TABLE t1(a TEXT)}}
















finish_test