SQLite4  Check-in [cb7a8b464e]

  p = (ExprHasProperty(pX, EP_xIsSelect) ? pX->x.pSelect : 0);
  if( ALWAYS(pParse->nErr==0) && isCandidateForInOpt(p) ){
    sqlite4 *db = pParse->db;
    Table *pTab = p->pSrc->a[0].pTab;
    Expr *pRhs = p->pEList->a[0].pExpr;
    int iCol = pRhs->iColumn;
    CollSeq *pReq;
    char colaff;

    /* The collation sequence used by the comparison. If an index is to
    ** be used in place of a temp-table, it must be ordered according
    ** to this collation sequence.  */
    pReq = sqlite4BinaryCompareCollSeq(pParse, pX->pLeft, pRhs);
    if( !pReq ) pReq = db->pDfltColl;

    /* Check that the affinity that will be used to perform the 
    ** comparison is the same as the affinity of the column. If
    ** it is not, it is not possible to use any index.  */

    colaff = (iCol<0) ? SQLITE4_AFF_NUMERIC : pTab->aCol[iCol].affinity;
    if( 0==sqlite4IndexAffinityOk(pX, colaff) ) return 0;

    for(pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext){
      if( (pIdx->aiColumn[0]==iCol)
       && sqlite4FindCollSeq(db, pIdx->azColl[0], 0)==pReq
       && (!bReqUnique || (pIdx->nColumn==1 && pIdx->onError!=OE_None))
      ){
        break;

  return rc;
}

/*
** Store schema cookie value iVal.
*/
int sqlite4KVStorePutSchema(KVStore *p, unsigned int iVal){
  kvTrace(p, "xPutMeta(%d,%d)", p->kvId, (int)iVal);
  return p->pStoreVfunc->xPutMeta(p, iVal);
}

/*
** Read the schema cookie value into *piVal.
*/
int sqlite4KVStoreGetSchema(KVStore *p, unsigned int *piVal){

  return 0;
}

/*
** Multiply two numbers and return the result.
*/
sqlite4_num sqlite4_num_mul(sqlite4_num A, sqlite4_num B){
  sqlite4_num nan = {0, 0, SQLITE4_MX_EXP+1, 0};
  unsigned char sign;     /* Sign of the overall value */
  unsigned char approx;   /* True if the value is approximate */
  short e;                /* The exponent. */
  sqlite4_uint64 m;       /* The significant */
  sqlite4_num r;

  if( A.e>SQLITE4_MX_EXP ){
    A.sign ^= B.sign;
    A.m = B.m;
    return A;
  }
  if( B.e>SQLITE4_MX_EXP ){
    B.sign ^= A.sign;
    B.m = A.m;
    return B;
  }
  if( A.m==0 ) return A;
  if( B.m==0 ) return B;
  while( A.m%10==0 ){ A.m /= 10; A.e++; }
  while( B.m%10==0 ){ B.m /= 10; B.e++; }
  r.sign = A.sign ^ B.sign;

      return r;
    }
    return B;
  }
  if( B.m==0 ){
    r.sign = A.sign ^ B.sign;
    r.e = SQLITE4_NAN_EXP;
    r.m = 0;
    r.approx = 1;
    return r;
  }
  if( A.m==0 ){
    return A;
  }
  while( A.m<TENTH_MAX ){

        pOut->u.num = sqlite4_num_mul(num1, num2); break;
      case OP_Divide: 
        pOut->u.num = sqlite4_num_div(num2, num1); break;
      default: {
        iA = sqlite4_num_to_int64(num1, 0);
        iB = sqlite4_num_to_int64(num2, 0);
        if( iA==0 ) goto arithmetic_result_is_null;
        if( iA==-1 ) iA = 1;
        pOut->u.num = sqlite4_num_from_int64(iB % iA);
        break;
      }
    }

    if( sqlite4_num_isnan(pOut->u.num) ){
      goto arithmetic_result_is_null;

        static const u8 enc[] = {SQLITE4_UTF8,SQLITE4_UTF16LE,SQLITE4_UTF16BE };
        sqlite4VdbeMemSetStr(pOut, (char*)(p->a+ofst+1), size-1, 
                             enc[p->a[ofst]], SQLITE4_TRANSIENT, 0);
      }
    }else{
      sqlite4VdbeMemSetStr(pOut, (char*)(p->a+ofst), size, 0,
                           SQLITE4_TRANSIENT, 0);
      pOut->enc = ENC(p->db);
    }
  }
  testcase( i==iVal );
  testcase( i==iVal+1 );
  if( i<=iVal ){
    if( pDefault ){
      sqlite4VdbeMemShallowCopy(pOut, pDefault, MEM_Static);

    /* Write the encoded key to the output buffer. */
    if( enlargeEncoderAllocation(p, pMem->n*4 + 2) ) return SQLITE4_NOMEM;
    p->aOut[p->nOut++] = 0x24;   /* Text */
    if( pColl==0 || pColl->xMkKey==0 ){
      const char *z = (const char *)sqlite4ValueText(pMem, SQLITE4_UTF8);
      if( z ){
        char *zCsr = z;
        char *zEnd = &z[pMem->n];
        while( *zCsr && zCsr<zEnd ) zCsr++;
        memcpy(p->aOut+p->nOut, z, (zCsr-z));
        p->nOut += (zCsr-z);
      }
    }else{
      int rc;                     /* xMkKey() return code */
      int nReq;                   /* Space required by xMkKey() */
      int nSpc;                   /* Space available */

      nSpc = p->nAlloc-p->nOut;

        (sizeof(pLevel->u.in.aInLoop[0])*pLevel->u.in.nIn)
    );
    pIn = pLevel->u.in.aInLoop;

    if( pIn ){
      pIn += pLevel->u.in.nIn - 1;
      pIn->iCur = iTab;
      if( iCol>=0 ){
        pIn->addrInTop = sqlite4VdbeAddOp3(v, OP_Column, iTab, iCol, iReg);
      }else{
        pIn->addrInTop = sqlite4VdbeAddOp2(v, OP_Rowid, iTab, iReg);
      }
      sqlite4VdbeAddOp1(v, OP_IsNull, iReg);
    }else{
      assert( db->mallocFailed );
      pLevel->u.in.nIn = 0;
    }
#endif
  }

}

ifcapable !foreignkey||!trigger { finish_test ; return }
reset_db


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




# Update for src4: foreign-keys are on by default.




#
drop_all_tables
do_test e_fkey-4.1 {
  execsql {
    CREATE TABLE p(i PRIMARY KEY);
    CREATE TABLE c(j REFERENCES p ON UPDATE CASCADE);
    INSERT INTO p VALUES('hello');
    INSERT INTO c VALUES('hello');
    UPDATE p SET i = 'world';
    SELECT * FROM c;
  } 
} {world}
do_test e_fkey-4.2 {
  execsql {
    DELETE FROM c;
    DELETE FROM p;
    PRAGMA foreign_keys = ON;
    INSERT INTO p VALUES('hello');
    INSERT INTO c VALUES('hello');

# This also tests the example code in section 2 of foreignkeys.in.
#
# EVIDENCE-OF: R-11255-19907
# 
reset_db
do_test e_fkey-5.1 {
  execsql { PRAGMA foreign_keys }
} {1}
do_test e_fkey-5.2 {
  execsql { 
    PRAGMA foreign_keys = ON;
    PRAGMA foreign_keys;
  }
} {1}
do_test e_fkey-5.3 {

    UPDATE t1 SET a = 'ONE';
  }
} {}
do_test e_fkey-16.3 {
  catchsql { UPDATE t2 SET b = 'two' WHERE rowid = 1 }
} {1 {foreign key constraint failed}}
do_test e_fkey-16.4 {
  catchsql { DELETE FROM t1 WHERE a = 'oNe' }
} {1 {foreign key constraint failed}}

#-------------------------------------------------------------------------
# Specifically, test that when comparing child and parent key values the
# affinity of the parent key column is applied to the child key value
# before the comparison takes place.
#

    SELECT b, typeof(b) FROM t2;
  }
} {2.0 text}
do_test e_fkey-17.3 {
  execsql { SELECT typeof(a) FROM t1 }
} {integer integer text}
do_test e_fkey-17.4 {
  catchsql { DELETE FROM t1 WHERE a = 2 }
} {1 {foreign key constraint failed}}

###########################################################################
### SECTION 3: Required and Suggested Database Indexes
###########################################################################

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

do_test e_fkey-27.2 {
  eqp { INSERT INTO artist VALUES(?, ?) }
} {}
do_execsql_test e_fkey-27.3 {
  EXPLAIN QUERY PLAN UPDATE artist SET artistid = ?, artistname = ?
} {
  0 0 0 {SCAN TABLE artist (~1000000 rows)} 
  0 0 0 {SEARCH TABLE track USING INDEX trackindex (trackartist=?) (~10 rows)} 
  0 0 0 {SEARCH TABLE track USING INDEX trackindex (trackartist=?) (~10 rows)}
}
do_execsql_test e_fkey-27.4 {
  EXPLAIN QUERY PLAN DELETE FROM artist
} {
  0 0 0 {SCAN TABLE artist (~1000000 rows)} 
  0 0 0 {SEARCH TABLE track USING INDEX trackindex (trackartist=?) (~10 rows)}
}


###########################################################################
### SECTION 4.1: Composite Foreign Key Constraints
###########################################################################

    INSERT INTO pA VALUES(X'1234');
    INSERT INTO cA VALUES(X'ABCD');
    INSERT INTO cB VALUES(X'1234');
  }
} {}
do_test e_fkey-44.2 {
  execsql {
    DELETE FROM pA WHERE x = x'abcd';
    SELECT quote(x) FROM pA;
  }
} {x'1234'}
do_test e_fkey-44.3 {
  execsql {
    SELECT quote(c) FROM cA;
  }
} {NULL}
do_test e_fkey-44.4 {
  execsql {
    UPDATE pA SET x = X'8765' WHERE x = X'1234';
    SELECT quote(x) FROM pA;
  }
} {x'8765'}
do_test e_fkey-44.5 {
  execsql { SELECT quote(c) FROM cB }
} {NULL}

#-------------------------------------------------------------------------
# Test SET DEFAULT actions.
#
# EVIDENCE-OF: R-43054-54832 The "SET DEFAULT" actions are similar to
# "SET NULL", except that each of the child key columns is set to
# contain the columns default value instead of NULL.
#
drop_all_tables
do_test e_fkey-45.1 {
  execsql {
    CREATE TABLE pA(x PRIMARY KEY);
    CREATE TABLE cA(c DEFAULT X'0000' REFERENCES pA ON DELETE SET DEFAULT);
    CREATE TABLE cB(c DEFAULT X'9999' REFERENCES pA ON UPDATE SET DEFAULT);

    INSERT INTO pA(x) VALUES(X'0000');
    INSERT INTO pA(x) VALUES(X'9999');
    INSERT INTO pA(x) VALUES(X'ABCD');
    INSERT INTO pA(x) VALUES(X'1234');

    INSERT INTO cA VALUES(X'ABCD');
    INSERT INTO cB VALUES(X'1234');
  }
} {}
do_test e_fkey-45.2 {
  execsql {
    DELETE FROM pA WHERE x = X'ABCD';
    SELECT quote(x) FROM pA;
  }
} {x'0000' x'1234' x'9999'}
do_test e_fkey-45.3 {
  execsql { SELECT quote(c) FROM cA }
} {x'0000'}
do_test e_fkey-45.4 {
  execsql {
    UPDATE pA SET x = X'8765' WHERE x = X'1234';
    SELECT quote(x) FROM pA;
  }
} {x'0000' x'8765' x'9999'}
do_test e_fkey-45.5 {
  execsql { SELECT quote(c) FROM cB }
} {x'9999'}

#-------------------------------------------------------------------------
# Test ON DELETE CASCADE actions.
#
# EVIDENCE-OF: R-61376-57267 A "CASCADE" action propagates the delete or
# update operation on the parent key to each dependent child key.
#

  }
} {}
do_test e_fkey-51.2 {
  execsql {
    UPDATE parent SET x = 22;
    SELECT * FROM parent UNION ALL SELECT 'xxx' UNION ALL SELECT a FROM child;
  }
} {21 22 23 xxx 22}
do_test e_fkey-51.3 {
  execsql {
    DELETE FROM child;
    DELETE FROM parent;
    INSERT INTO parent VALUES(-1);
    INSERT INTO child VALUES(-1);
    UPDATE parent SET x = 22;
    SELECT * FROM parent UNION ALL SELECT 'xxx' UNION ALL SELECT a FROM child;
  }
} {21 22 23 xxx 23}


#-------------------------------------------------------------------------
# Verify that ON UPDATE actions only actually take place if the parent key
# is set to a new value that is distinct from the old value. The default
# collation sequence and affinity are used to determine if the new value
# is 'distinct' from the old or not.

  execsql {
    UPDATE zeus SET b = '1';
    SELECT typeof(c), c, typeof(d), d FROM apollo;
  }
} {integer 1 text 1}
do_test e_fkey-52.6 {
  execsql {
    UPDATE zeus SET b = x'1234';
    SELECT typeof(c), c, typeof(d), quote(d) FROM apollo;
  }
} {integer 1 blob x'1234'}

#-------------------------------------------------------------------------
# EVIDENCE-OF: R-35129-58141
#
# Test an example from the "ON DELETE and ON UPDATE Actions" section 
# of foreignkeys.html. This example demonstrates that ON UPDATE actions
# only take place if at least one parent key column is set to a value 

    CREATE TABLE c7(c, d, 
      FOREIGN KEY(c, d) REFERENCES p ON DELETE NO ACTION
      DEFERRABLE INITIALLY DEFERRED
    );

    CREATE TABLE log(msg);
    CREATE TRIGGER tt AFTER DELETE ON p BEGIN
      INSERT INTO log VALUES('delete ' || old.a || '|' || old.b);
    END;
  }
} {}

do_test e_fkey-57.2 {
  execsql {
    INSERT INTO p VALUES('a', 'b');

do_test e_fkey-57.7 {
  execsql {
    BEGIN;
      DELETE FROM p;
      SELECT * FROM log;
    ROLLBACK;
  }
} {{delete a|b}}

#-------------------------------------------------------------------------
# If an IMMEDIATE foreign key fails as a result of a DROP TABLE, the
# DROP TABLE command fails.
#
# EVIDENCE-OF: R-32768-47925 If an immediate foreign key constraint is
# violated, the DROP TABLE statement fails and the table is not dropped.

    INSERT INTO c3 VALUES(1, 2);
  }
} {}
do_test e_fkey-60.2 {
  execsql { PRAGMA foreign_keys = ON }
  catchsql { DELETE FROM p }
} {1 {no such table: main.nosuchtable}}








do_test e_fkey-60.3 {
  execsql {
    BEGIN;
      DROP TABLE p;
      SELECT * FROM c3;
    ROLLBACK;

# EVIDENCE-OF: R-04006-57648 In this case the result of evaluating the
# left-most expression in the VALUES list is inserted into the left-most
# column of the new row, and so on.
#
delete_all_data
do_insert_tests e_insert-1.3 {
    1a   "INSERT INTO a2 VALUES(1, 2, 3)"    {}
    1b   "SELECT * FROM a2 WHERE rowid=(SELECT max(rowid) FROM a2)" {1 2 3}

    2a   "INSERT INTO a2 VALUES('abc', NULL, 3*3+1)"      {}
    2b   "SELECT * FROM a2 WHERE rowid=(SELECT max(rowid) FROM a2)" {abc {} 10}

    3a   "INSERT INTO a2 VALUES((SELECT count(*) FROM a2), 'x', 'y')" {}
    3b   "SELECT * FROM a2 WHERE rowid=(SELECT max(rowid) FROM a2)" {2 x y}
}

# EVIDENCE-OF: R-62524-00361 If a column-list is specified, then the
# number of values must match the number of specified columns.
#
do_insert_tests e_insert-1.4 -error { 
  %d values for %d columns

#    lang_replace.html.
#
do_execsql_test e_insert-4.1.0 {
  INSERT INTO a4 VALUES(1, 'a');
  INSERT INTO a4 VALUES(2, 'a');
  INSERT INTO a4 VALUES(3, 'a');
} {}
foreach {tn sql error ts data } {
  1.1  "INSERT INTO a4 VALUES(2,'b')"  {column c is not unique}  0 {1 a 2 a 3 a}
  1.2  "INSERT OR REPLACE INTO a4 VALUES(2, 'b')"            {}  0 {1 a 3 a 2 b}
  1.3  "INSERT OR IGNORE INTO a4 VALUES(3, 'c')"             {}  0 {1 a 3 a 2 b}
  1.4  "BEGIN" {} 1 {1 a 3 a 2 b}
  1.5  "INSERT INTO a4 VALUES(1, 'd')" {column c is not unique}  1 {1 a 3 a 2 b}
  1.6  "INSERT OR ABORT INTO a4 VALUES(1, 'd')" 
        {column c is not unique}  1 {1 a 3 a 2 b}
  1.7  "INSERT OR ROLLBACK INTO a4 VALUES(1, 'd')" 
        {column c is not unique}  0 {1 a 3 a 2 b}
  1.8  "INSERT INTO a4 SELECT 4, 'e' UNION ALL SELECT 3, 'e'"
        {column c is not unique}  0 {1 a 3 a 2 b}
  1.9  "INSERT OR FAIL INTO a4 SELECT 4, 'e' UNION ALL SELECT 3, 'e'"
        {column c is not unique}  0 {1 a 3 a 2 b 4 e}

  2.1  "INSERT INTO a4 VALUES(2,'f')"  
        {column c is not unique}  0 {1 a 3 a 2 b 4 e}
  2.2  "REPLACE INTO a4 VALUES(2, 'f')" {}  0 {1 a 3 a 4 e 2 f}
} {
  do_catchsql_test e_insert-4.1.$tn.1 $sql [list [expr {$error!=""}] $error]
  do_execsql_test  e_insert-4.1.$tn.2 {SELECT * FROM a4} [list {*}$data]
  do_test          e_insert-4.1.$tn.3 {sqlite4_db_transaction_status db} $ts
}

# EVIDENCE-OF: R-64196-02418 The optional "database-name." prefix on the
# table-name is support for top-level INSERT statements only.
#
# EVIDENCE-OF: R-05731-00924 The table name must be unqualified for
# INSERT statements that occur within CREATE TRIGGER statements.

  UPDATE sqlite_master SET sql = substr(sql, 4) WHERE type = 'index';
} {}

db close
sqlite4 db test.db
do_execsql_test e_reindex-1.3 {
  PRAGMA integrity_check;
} {{6 errors:
entry missing from index i2: 041810180e1808
entry missing from index i1: 03180e18101808
entry missing from index i2: 0418141812180a
entry missing from index i1: 0318121814180a
wrong # number of entries in index i2
wrong # number of entries in index i1

}}

do_execsql_test e_reindex-1.4 {
  REINDEX;
  PRAGMA integrity_check;
} {ok}

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

#   These tests also show that the following is not untrue:
#
# EVIDENCE-OF: R-25883-55063 The expressions in the GROUP BY clause do
# not have to be expressions that appear in the result.
#
do_select_tests e_select-4.9 {
  1  "SELECT group_concat(one), two FROM b1 GROUP BY two" {
    4,5 f   1 o   6,7   s 2,3 t
  }
  2  "SELECT group_concat(one), sum(one) FROM b1 GROUP BY (one>4)" {
    1,2,3,4 10    5,6,7 18
  }
  3  "SELECT group_concat(one) FROM b1 GROUP BY (two>'o'), one%2" {
    4  1,5    2,6   3,7
  }
  4  "SELECT group_concat(one) FROM b1 GROUP BY (one==2 OR two=='o')" {
    3,4,5,6,7    1,2
  }
}

# EVIDENCE-OF: R-14926-50129 For the purposes of grouping rows, NULL
# values are considered equal.
#
do_select_tests e_select-4.10 {

  INSERT INTO q3 VALUES('beauty', 2);
  INSERT INTO q3 VALUES('beauty', 2);
} {}
do_select_tests e_select-7.4 {
  1   {SELECT a FROM q1 UNION ALL SELECT d FROM q2}
      {16 legible beauty legible beauty -65.91 emanating}

  2   {SELECT * FROM q1 WHERE a=16 UNION ALL SELECT 'x',* FROM q2 WHERE rowid=1}
      {16 -87.66 {} x legible 1}

  3   {SELECT count(*) FROM q1 UNION ALL SELECT min(e) FROM q2} 
      {3 -16.56}

  4   {SELECT * FROM q2 UNION ALL SELECT * FROM q3} 
      {legible 1 beauty 2 -65.91 4 emanating -16.56 beauty 2 beauty 2}
} 

# EVIDENCE-OF: R-20560-39162 The UNION operator works the same way as
# UNION ALL, except that duplicate rows are removed from the final
# result set.
#
do_select_tests e_select-7.5 {
  1   {SELECT a FROM q1 UNION SELECT d FROM q2}
      {-65.91 16 beauty emanating legible}

  2   {SELECT * FROM q1 WHERE a=16 UNION SELECT 'x', * FROM q2 WHERE rowid=1}
      {16 -87.66 {} x legible 1}

  3   {SELECT count(*) FROM q1 UNION SELECT min(e) FROM q2} 
      {-16.56 3}

  4   {SELECT * FROM q2 UNION SELECT * FROM q3} 
      {-65.91 4 beauty 2 emanating -16.56 legible 1}

# are set to the values found by evaluating the corresponding scalar
# expressions.
#
# EVIDENCE-OF: R-40472-60438 Columns that do not appear in the list of
# assignments are left unmodified.
#
do_execsql_test e_update-1.5.0 {
  INSERT INTO t2(a, b, c) VALUES(3, 1, 4);
  INSERT INTO t2(a, b, c) VALUES(1, 5, 9);
  INSERT INTO t2(a, b, c) VALUES(2, 6, 5);
} {}
do_update_tests e_update-1.5 -query {
  SELECT * FROM t2
} {
  1   "UPDATE t2 SET c = 1+1 WHERE a=2" 
      {3 1 4   1 5 9   2 6 2}

# of the row being updated.
#
# EVIDENCE-OF: R-04558-24451 In this case all scalar expressions are
# evaluated before any assignments are made.
#
do_execsql_test e_update-1.7.0 {
  DELETE FROM t2;
  INSERT INTO t2(a, b, c) VALUES(3, 1, 4);
  INSERT INTO t2(a, b, c) VALUES(1, 5, 9);
  INSERT INTO t2(a, b, c) VALUES(2, 6, 5);
} {}
do_update_tests e_update-1.7 -query {
  SELECT * FROM t2
} {
  1   "UPDATE t2 SET a=b+c"          {5 1 4     14 5 9   11  6 5}
  2   "UPDATE t2 SET a=b, b=a"       {1 5 4     5 14 9    6 11 5}
  3   "UPDATE t2 SET a=c||c, c=NULL" {44 5 {}  99 14 {}  55 11 {}}
}

# EVIDENCE-OF: R-12619-24112 The optional conflict-clause allows the
# user to nominate a specific constraint conflict resolution algorithm
# to use during this one UPDATE command.
#
do_execsql_test e_update-1.8.0 {
  DELETE FROM t3;
  INSERT INTO t3 VALUES(1, 'one');
  INSERT INTO t3 VALUES(2, 'two');
  INSERT INTO t3 VALUES(3, 'three');
  INSERT INTO t3 VALUES(4, 'four');
} {}
foreach {tn sql error ts data } {
  1  "UPDATE t3 SET b='one' WHERE a=3" 
     {column b is not unique} 0 {1 one 2 two 3 three 4 four}

  2  "UPDATE OR REPLACE t3 SET b='one' WHERE a=3" 
     {} 0 {2 two 3 one 4 four}

  3  "UPDATE OR FAIL t3 SET b='three'"
     {column b is not unique} 0 {2 three 3 one 4 four}

  4  "UPDATE OR IGNORE t3 SET b='three' WHERE a=3" 
     {} 0 {2 three 3 one 4 four}

  5  "UPDATE OR ABORT t3 SET b='three' WHERE a=3" 
     {column b is not unique} 0 {2 three 3 one 4 four}

  6  "BEGIN" {} 1 {2 three 3 one 4 four}

  7  "UPDATE t3 SET b='three' WHERE a=3" 
     {column b is not unique} 1 {2 three 3 one 4 four}

  8  "UPDATE OR ABORT t3 SET b='three' WHERE a=3" 
     {column b is not unique} 1 {2 three 3 one 4 four}

  9  "UPDATE OR FAIL t3 SET b='two'"
     {column b is not unique} 1 {2 two 3 one 4 four}

  10 "UPDATE OR IGNORE t3 SET b='four' WHERE a=3"
     {} 1 {2 two 3 one 4 four}

  11 "UPDATE OR REPLACE t3 SET b='four' WHERE a=3"
     {} 1 {2 two 3 four}

  12 "UPDATE OR ROLLBACK t3 SET b='four'"
     {column b is not unique} 0 {2 three 3 one 4 four}
} {
  do_catchsql_test e_update-1.8.$tn.1 $sql [list [expr {$error!=""}] $error]
  do_execsql_test  e_update-1.8.$tn.2 {SELECT * FROM t3} [list {*}$data]
  do_test          e_update-1.8.$tn.3 {sqlite4_db_transaction_status db} $ts
}



# EVIDENCE-OF: R-12123-54095 The table-name specified as part of an
# UPDATE statement within a trigger body must be unqualified.
#

do_test $t.3 {
  execsql {
    INSERT INTO t1 VALUES('three','III',3);
    INSERT INTO t1 VALUES('four','IV',4);
    INSERT INTO t1 VALUES('five','V',5);
  }
  execsql {SELECT * FROM t1}
} {five V 5 four IV 4 one I 1 three III 3 two II 2}

# Use the index
do_test $t.4 {
  execsql {
    SELECT * FROM t1 WHERE a = 'one';
  }
} {one I 1}

}

# Now check that we can retrieve data in both UTF-16 and UTF-8
do_test $t.7 {
  set STMT [sqlite4_prepare $DB "SELECT a FROM t1 WHERE c>3;" -1 TAIL]
  sqlite4_step $STMT
  sqlite4_column_text $STMT 0
} {five}

do_test $t.8 {
  sqlite4_step $STMT
  utf8 [sqlite4_column_text16 $STMT 0]
} {four}

do_test $t.9 {
  sqlite4_finalize $STMT
} SQLITE4_OK





do_test $t.10 {
  db eval {PRAGMA encoding}
} $enc

}

# The three unicode encodings understood by SQLite.

      SELECT * FROM t1
    }
  } {abcdef {}}
  do_test enc3-1.5 {
    execsql {
      SELECT quote(x) || ' ' || quote(y) FROM t1
    }
  } {{x'616263646566' NULL}}
}
ifcapable {bloblit && utf16} {
  do_test enc3-2.1 {
    execsql {
      PRAGMA encoding
    }
  } {UTF-16le}

  lappend ret [sqlite4_finalize $stmt]
  lappend ret [sqlite4_errmsg db]

  set ret
}

proc error_messages_v2 {sql {schema {}}} {
  error_messages_worker sqlite4_prepare $sql $schema
}

proc error_messages {sql {schema {}}} {
  error_messages_worker sqlite4_prepare $sql $schema
}

proc sql_error {msg} { error $msg }
db func sql_error sql_error

#-------------------------------------------------------------------------
# Test error messages returned by user-defined SQL functions.
#
do_test 1.1 {
  error_messages "SELECT sql_error('custom message')"
} [list {*}{
    SQLITE4_ERROR {custom message}
    SQLITE4_ERROR {custom message}
}]
do_test 1.2 {
  error_messages_v2 "SELECT sql_error('custom message')"
} [list {*}{
    SQLITE4_ERROR {custom message}
    SQLITE4_ERROR {custom message}
}]

#-------------------------------------------------------------------------
# Test error messages generated directly by VDBE code (e.g. constraint
# failures).
#
do_execsql_test 2.1 {
  CREATE TABLE t1(a PRIMARY KEY, b UNIQUE);
  INSERT INTO t1 VALUES('abc', 'def');
}
do_test 2.2 {
  error_messages "INSERT INTO t1 VALUES('ghi', 'def')"
} [list {*}{
    SQLITE4_CONSTRAINT {column b is not unique}
    SQLITE4_CONSTRAINT {column b is not unique}
}]
do_test 2.3 {
  error_messages_v2 "INSERT INTO t1 VALUES('ghi', 'def')"
} [list {*}{
    SQLITE4_CONSTRAINT {column b is not unique}
    SQLITE4_CONSTRAINT {column b is not unique}

do_execsql_test 3.1.1 {
  CREATE TABLE t2(a PRIMARY KEY, b UNIQUE);
  INSERT INTO t2 VALUES('abc', 'def');
}
do_test 3.1.2 {
  error_messages "SELECT a FROM t2" "DROP TABLE t2"
} [list {*}{
    SQLITE4_ERROR {no such table: t2} 
    SQLITE4_ERROR {no such table: t2}
}]
do_execsql_test 3.2.1 {
  CREATE TABLE t2(a PRIMARY KEY, b UNIQUE);
  INSERT INTO t2 VALUES('abc', 'def');
}
do_test 3.2.2 {
  error_messages_v2 "SELECT a FROM t2" "DROP TABLE t2"
} [list {*}{
    SQLITE4_ERROR {no such table: t2} 
    SQLITE4_ERROR {no such table: t2}
}]

finish_test

  execsql {
    INSERT INTO t2 SELECT x, x+1 FROM t1 WHERE x<5;
    SELECT x, test_eval('UPDATE t2 SET y=y+100 WHERE x='||x), y FROM t2;
  }
} {1 {} 102 2 {} 103 3 {} 104 4 {} 105}

do_test eval-4.1 {
  execsql { SELECT test_eval('SELECT ''abcdefghij''') }
} {abcdefghij}

finish_test

  test_expr expr-2.19 {r1=2.34, r2=2.34} {r2=r1} 1
  test_expr expr-2.20 {r1=2.34, r2=2.34} {r2<>r1} 0
  test_expr expr-2.21 {r1=2.34, r2=2.34} {r2==r1} 1
  test_expr expr-2.22 {r1=1.23, r2=2.34} {min(r1,r2,r1+r2,r1-r2)} {-1.11}
  test_expr expr-2.23 {r1=1.23, r2=2.34} {max(r1,r2,r1+r2,r1-r2)} {3.57}
  test_expr expr-2.24 {r1=25.0, r2=11.0} {r1%r2} 3.0
  test_expr expr-2.25 {r1=1.23, r2=NULL} {coalesce(r1+r2,99.0)} 99.0
  test_expr expr-2.26 {r1=1e600, r2=1e600} {coalesce((r1*r2)*0.0,99.0)} 99.0
  test_expr expr-2.26b {r1=1e300, r2=-1e300} {coalesce((r1*r2)*0.0,99.0)} 0.0
  test_expr expr-2.27 {r1=1.1, r2=0.0} {r1/r2} {{}}
  test_expr expr-2.28 {r1=1.1, r2=0.0} {r1%r2} {{}}
}

test_expr expr-3.1 {t1='abc', t2='xyz'} {t1<t2} 1
test_expr expr-3.2 {t1='xyz', t2='abc'} {t1<t2} 0
test_expr expr-3.3 {t1='abc', t2='abc'} {t1<t2} 0

if {[info exists ::G(isquick)]} {
  if {$::G(isquick)} { set ::REPEATS 20 }
}

source $testdir/fuzz_common.tcl
expr srand(0)

proc zeroblob {n} {
  set L 0
  while {[llength $L] < $n} { set L [concat $L $L] }
  binary format c$n $L
}
db func zeroblob zeroblob

#----------------------------------------------------------------
# These tests caused errors that were first caught by the tests
# in this file. They are still here.
do_test fuzz-1.1 {
  execsql {
    SELECT 'abc' LIKE X'ABCD';
  }

do_test in3-1.1 {
  execsql {
    CREATE TABLE t1(a PRIMARY KEY, b);
    INSERT INTO t1 VALUES(1, 2);
    INSERT INTO t1 VALUES(3, 4);
    INSERT INTO t1 VALUES(5, 6);

    CREATE TABLE t2(a, b);
    INSERT INTO t2 VALUES(1, 2);
    INSERT INTO t2 VALUES(3, 4);
    INSERT INTO t2 VALUES(5, 6);
  }
} {}

# All of these queries should avoid using a temp-table:
#
do_test in3-1.2 {
  exec_neph { SELECT rowid FROM t2 WHERE rowid IN (SELECT rowid FROM t2); }
} {0 1 2 3}
do_test in3-1.3 {
  exec_neph { SELECT a FROM t1 WHERE a IN (SELECT a FROM t1); }
} {0 1 3 5}
do_test in3-1.4 {
  exec_neph { SELECT rowid FROM t2 WHERE rowid+0 IN (SELECT rowid FROM t2); }
} {0 1 2 3}
do_test in3-1.5 {
  exec_neph { SELECT a FROM t1 WHERE a+0 IN (SELECT a FROM t1); }
} {0 1 3 5}

# Because none of the sub-select queries in the following statements
# match the pattern ("SELECT <column> FROM <table>"), the following do 
# require a temp table.
#
do_test in3-1.6 {
  exec_neph { SELECT rowid FROM t2 WHERE rowid IN (SELECT rowid+0 FROM t2); }
} {1 1 2 3}
do_test in3-1.7 {
  exec_neph { SELECT a FROM t1 WHERE a IN (SELECT a+0 FROM t1); }
} {1 1 3 5}
do_test in3-1.8 {
  exec_neph { SELECT a FROM t1 WHERE a IN (SELECT a FROM t1 WHERE 1); }
} {1 1 3 5}

do_test in3-1.14 {
  exec_neph { SELECT a FROM t1 WHERE a COLLATE nocase IN (SELECT a FROM t1) }
} {1 1 3 5}
do_test in3-1.15 {
  exec_neph { SELECT a FROM t1 WHERE a COLLATE binary IN (SELECT a FROM t1) }
} {0 1 3 5}











# The following tests - in3.2.* - test a bug that was difficult to track
# down during development. They are not particularly well focused.
#
do_test in3-2.1 {
  execsql {
    DROP TABLE IF EXISTS t1;
    DROP TABLE IF EXISTS t2;
    CREATE TABLE t1(w int, x int, y int);
    CREATE TABLE t2(p int, q int, r int, s int);
  }
  for {set i 1} {$i<=100} {incr i} {
    set w $i
    set x [expr {int(log($i)/log(2))}]
    set y [expr {$i*$i + 2*$i + 1}]
    execsql "INSERT INTO t1 VALUES($w,$x,$y)"
  }
  set maxy [execsql {select max(y) from t1}]
  db eval { INSERT INTO t2 SELECT 101-w, x, $maxy+1-y, y FROM t1 }
} {}
do_test in3-2.2 {
  execsql {
    SELECT rowid 
    FROM t2 
    WHERE rowid IN (SELECT rowid FROM t2 WHERE rowid IN (1, 2));
  }
} {1 2}
do_test in3-2.3 {
  execsql {
    select rowid from t2 where rowid IN (-1,2,4)
  }
} {2 4}
do_test in3-2.4 {
  execsql {
    SELECT rowid FROM t2 WHERE rowid IN 
       (select rowid from t2 where rowid IN (-1,2,4))
  }
} {2 4}

#-------------------------------------------------------------------------
# This next block of tests - in3-3.* - verify that column affinity is
# correctly handled in cases where an index might be used to optimise
# an IN (SELECT) expression.

} {1 1}
do_test in3-4.4 {
  # A temp table must be used because t3_i.b is not guaranteed to be unique.
  exec_neph { SELECT b FROM t3 WHERE b IN (SELECT b FROM t3) }
} {1 none numeric real text}
do_test in3-4.5 {
  execsql { CREATE UNIQUE INDEX t3_i2 ON t3(b) }
  exec_neph { SELECT b FROM t3 WHERE b IN (SELECT b FROM t3) ORDER BY b}
} {0 none numeric real text}
do_test in3-4.6 {
  execsql { DROP INDEX t3_i2 }
} {}

# The following two test cases verify that ticket #2991 has been fixed.
#

  cse.test
  ctime.test
  date.test
  default.test
  delete.test delete2.test delete3.test
  descidx1.test descidx2.test descidx3.test 
  distinct.test distinctagg.test
  e_createtable.test e_delete.test e_droptrigger.test e_dropview.test
  e_expr.test e_fkey.test e_insert.test e_reindex.test
  e_resolve.test e_select.test e_select2.test e_update.test
  enc.test enc3.test enc4.test
  errmsg.test
  eval.test
  expr.test
  exec.test
  exists.test
  fkey1.test fkey2.test fkey3.test fkey4.test
  func.test func2.test func3.test 
  fuzz.test fuzz2.test 
  in.test in2.test in3.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
  keyword1.test
  limit.test
  main.test
  manydb.test

} {-2}
do_test 83.3 {
  execsql { SELECT CAST('45.0' AS NUMERIC) }
} {45}
do_test 83.4 {
  execsql { SELECT CAST(0.0 AS TEXT) }
} {0.0}

#-------------------------------------------------------------------------
reset_db
do_execsql_test 84.1 {
  SELECT 1e600 * 1e600 * 0.0;
} {{}}


finish_test