SQLite4
Check-in [cb7a8b464e]
Not logged in

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
SHA1 Hash:cb7a8b464e3d9b43d535d4c508c11093dd4c9e3b
Date: 2013-06-29 18:46:45
User: dan
Comment:Run further legacy tests. Fixes for bugs brought to light by the same.
Tags And Properties
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/expr.c

1379
1380
1381
1382
1383
1384
1385
1386
1387
1388
1389
1390
1391

1392
1393
1394
1395
1396
1397
1398
1399
1400
1401
1402
1403
1404
  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 aff;

    /* 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);


    /* 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.  */
    aff = comparisonAffinity(pX);
    if( aff!=SQLITE4_AFF_NONE && aff!=pTab->aCol[iCol].affinity ) 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;







|





>




|
|







1379
1380
1381
1382
1383
1384
1385
1386
1387
1388
1389
1390
1391
1392
1393
1394
1395
1396
1397
1398
1399
1400
1401
1402
1403
1404
1405
  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;

Changes to src/kv.c

374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
  return rc;
}

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

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







|







374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
  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){

Changes to src/math.c

138
139
140
141
142
143
144





145

146
147

148

149
150

151
152
153
154
155
156
157
...
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
  return 0;
}

/*
** Multiply two numbers and return the result.
*/
sqlite4_num sqlite4_num_mul(sqlite4_num A, sqlite4_num B){





  sqlite4_num r;

  if( A.e>SQLITE4_MX_EXP ){
    A.sign ^= B.sign;

    return A;

  }else if( B.e>SQLITE4_MX_EXP ){
    B.sign ^= A.sign;

    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 = 1;
    r.approx = 1;
    return r;
  }
  if( A.m==0 ){
    return A;
  }
  while( A.m<TENTH_MAX ){







>
>
>
>
>

>


>

>
|

>







 







|







138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
...
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
  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 ){

Changes to src/vdbe.c

1255
1256
1257
1258
1259
1260
1261

1262
1263
1264
1265
1266
1267
1268
        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;

        pOut->u.num = sqlite4_num_from_int64(iB % iA);
        break;
      }
    }

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







>







1255
1256
1257
1258
1259
1260
1261
1262
1263
1264
1265
1266
1267
1268
1269
        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;

Changes to src/vdbecodec.c

146
147
148
149
150
151
152

153
154
155
156
157
158
159
...
473
474
475
476
477
478
479



480
481
482
483
484
485
486
487
488
        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);

    }
  }
  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 ){



        memcpy(p->aOut+p->nOut, z, pMem->n);
        p->nOut += pMem->n;
      }
    }else{
      int rc;                     /* xMkKey() return code */
      int nReq;                   /* Space required by xMkKey() */
      int nSpc;                   /* Space available */

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







>







 







>
>
>
|
|







146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
...
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
        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;

Changes to src/where.c

3559
3560
3561
3562
3563
3564
3565

3566



3567
3568
3569
3570
3571
3572
3573
        (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;

      pIn->addrInTop = sqlite4VdbeAddOp3(v, OP_Column, iTab, iCol, iReg);



      sqlite4VdbeAddOp1(v, OP_IsNull, iReg);
    }else{
      assert( db->mallocFailed );
      pLevel->u.in.nIn = 0;
    }
#endif
  }







>
|
>
>
>







3559
3560
3561
3562
3563
3564
3565
3566
3567
3568
3569
3570
3571
3572
3573
3574
3575
3576
3577
        (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
  }

Changes to test/e_fkey.test

125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
...
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
...
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
...
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
....
1095
1096
1097
1098
1099
1100
1101
1102
1103
1104
1105
1106
1107
1108
1109
1110
1111
1112
1113
1114
1115
1116
....
2011
2012
2013
2014
2015
2016
2017
2018
2019
2020
2021
2022
2023
2024
2025
2026
2027
2028
2029
2030
2031
2032
2033
2034
2035
2036
2037
2038
2039
....
2044
2045
2046
2047
2048
2049
2050
2051
2052
2053
2054
2055
2056
2057
2058
2059
2060
2061
2062
2063
2064
2065
2066
2067
2068
2069
2070
2071
2072
2073
2074
2075
2076
2077
2078
2079
2080
2081
2082
2083
2084
....
2323
2324
2325
2326
2327
2328
2329
2330
2331
2332
2333
2334
2335
2336
2337
2338
2339
2340
2341
2342
2343
2344
2345
2346
2347
....
2387
2388
2389
2390
2391
2392
2393
2394
2395
2396
2397
2398
2399
2400
2401
2402
2403
2404
....
2582
2583
2584
2585
2586
2587
2588
2589
2590
2591
2592
2593
2594
2595
2596
....
2617
2618
2619
2620
2621
2622
2623
2624
2625
2626
2627
2628
2629
2630
2631
....
2714
2715
2716
2717
2718
2719
2720
2721
2722
2723
2724
2725
2726
2727
2728
2729
2730
2731
2732
2733
2734
}

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


#-------------------------------------------------------------------------
# EVIDENCE-OF: R-07280-60510 Assuming the library is compiled with
# foreign key constraints enabled, it must still be enabled by the
# application at runtime, using the PRAGMA foreign_keys command.
#
# This also tests that foreign key constraints are disabled by default.
#
# EVIDENCE-OF: R-59578-04990 Foreign key constraints are disabled by
# default (for backwards compatibility), so must be enabled separately
# for each database connection separately.
#
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;
  } 
} {hello}
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 }
} {0}
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 rowid = 1 }
} {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 rowid = 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 COVERING INDEX trackindex (trackartist=?) (~10 rows)} 
  0 0 0 {SEARCH TABLE track USING COVERING 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 COVERING 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 rowid = 1;
    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 rowid = 2;
    SELECT quote(x) FROM pA;
  }
} {X'8765'}
do_test e_fkey-44.5 {
  execsql { SELECT quote(c) FROM cB }
} {NULL}

#-------------------------------------------------------------------------
# Test SET DEFAULT actions.
#
................................................................................
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(rowid, x) VALUES(1, X'0000');
    INSERT INTO pA(rowid, x) VALUES(2, X'9999');
    INSERT INTO pA(rowid, x) VALUES(3, X'ABCD');
    INSERT INTO pA(rowid, x) VALUES(4, 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 rowid = 3;
    SELECT quote(x) FROM pA;
  }
} {X'0000' X'9999' X'1234'}
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 rowid = 4;
    SELECT quote(x) FROM pA;
  }
} {X'0000' X'9999' X'8765'}
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;
  }
} {22 21 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;
  }
} {22 23 21 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 = NULL;
    SELECT typeof(c), c, typeof(d), d FROM apollo;
  }
} {integer 1 null {}}

#-------------------------------------------------------------------------
# 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.rowid);
    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 1}}

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


breakpoint
execsql { PRAGMA trace = 1 }
execsql { DROP TABLE p }
execsql { PRAGMA trace = 0 }


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







|
<
<
<
<
<
<
<
<











|







 







|







 







|







 







|







 







|
|





|







 







|


|







|


|







 







|
|
|
|







|


|


|


|


|


|







 







|









|







 







|
|

|







 







|







 







|







 







<
<
<
<
<
<
<







125
126
127
128
129
130
131
132








133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
...
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
...
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
...
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
....
1087
1088
1089
1090
1091
1092
1093
1094
1095
1096
1097
1098
1099
1100
1101
1102
1103
1104
1105
1106
1107
1108
....
2003
2004
2005
2006
2007
2008
2009
2010
2011
2012
2013
2014
2015
2016
2017
2018
2019
2020
2021
2022
2023
2024
2025
2026
2027
2028
2029
2030
2031
....
2036
2037
2038
2039
2040
2041
2042
2043
2044
2045
2046
2047
2048
2049
2050
2051
2052
2053
2054
2055
2056
2057
2058
2059
2060
2061
2062
2063
2064
2065
2066
2067
2068
2069
2070
2071
2072
2073
2074
2075
2076
....
2315
2316
2317
2318
2319
2320
2321
2322
2323
2324
2325
2326
2327
2328
2329
2330
2331
2332
2333
2334
2335
2336
2337
2338
2339
....
2379
2380
2381
2382
2383
2384
2385
2386
2387
2388
2389
2390
2391
2392
2393
2394
2395
2396
....
2574
2575
2576
2577
2578
2579
2580
2581
2582
2583
2584
2585
2586
2587
2588
....
2609
2610
2611
2612
2613
2614
2615
2616
2617
2618
2619
2620
2621
2622
2623
....
2706
2707
2708
2709
2710
2711
2712







2713
2714
2715
2716
2717
2718
2719
}

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

Changes to test/e_insert.test

155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
...
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
# 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 oid=last_insert_rowid()" {1 2 3}

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

    3a   "INSERT INTO a2 VALUES((SELECT count(*) FROM a2), 'x', 'y')" {}
    3b   "SELECT * FROM a2 WHERE oid=last_insert_rowid()" {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 ac data } {
  1.1  "INSERT INTO a4 VALUES(2,'b')"  {column c is not unique}  1 {1 a 2 a 3 a}
  1.2  "INSERT OR REPLACE INTO a4 VALUES(2, 'b')"            {}  1 {1 a 3 a 2 b}
  1.3  "INSERT OR IGNORE INTO a4 VALUES(3, 'c')"             {}  1 {1 a 3 a 2 b}
  1.4  "BEGIN" {} 0 {1 a 3 a 2 b}
  1.5  "INSERT INTO a4 VALUES(1, 'd')" {column c is not unique}  0 {1 a 3 a 2 b}
  1.6  "INSERT OR ABORT INTO a4 VALUES(1, 'd')" 
        {column c is not unique}  0 {1 a 3 a 2 b}
  1.7  "INSERT OR ROLLBACK INTO a4 VALUES(1, 'd')" 
        {column c is not unique}  1 {1 a 3 a 2 b}
  1.8  "INSERT INTO a4 SELECT 4, 'e' UNION ALL SELECT 3, 'e'"
        {column c is not unique}  1 {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}  1 {1 a 3 a 2 b 4 e}

  2.1  "INSERT INTO a4 VALUES(2,'f')"  
        {column c is not unique}  1 {1 a 3 a 2 b 4 e}
  2.2  "REPLACE INTO a4 VALUES(2, 'f')" {}  1 {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_get_autocommit db} $ac
}

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







|


|


|







 







|
|
|
|
|
|

|

|

|

|


|
|



|







155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
...
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
# 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.

Changes to test/e_reindex.test

62
63
64
65
66
67
68
69
70
71
72
73
74
75
76

77
78
79
80
81
82
83
  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;
} [list \
  {rowid 4 missing from index i2} \
  {rowid 4 missing from index i1} \
  {rowid 5 missing from index i2} \
  {rowid 5 missing from index i1} \
  {wrong # of entries in index i2} \
  {wrong # of entries in index i1}
]


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

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







|
|
|
|
|
|
|
<
>







62
63
64
65
66
67
68
69
70
71
72
73
74
75

76
77
78
79
80
81
82
83
  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}

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

Changes to test/e_select.test

1014
1015
1016
1017
1018
1019
1020
1021
1022
1023
1024
1025
1026
1027
1028
1029
1030
1031
1032
1033
1034
1035
1036
1037
....
1425
1426
1427
1428
1429
1430
1431
1432
1433
1434
1435
1436
1437
1438
1439
....
1443
1444
1445
1446
1447
1448
1449
1450
1451
1452
1453
1454
1455
1456
1457
#   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   7,6   s 3,2 t
  }
  2  "SELECT group_concat(one), sum(one) FROM b1 GROUP BY (one>4)" {
    1,4,3,2 10    5,7,6 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')" {
    4,3,5,7,6    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 oid=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}
................................................................................
# 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 oid=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}







|


|





|







 







|







 







|







1014
1015
1016
1017
1018
1019
1020
1021
1022
1023
1024
1025
1026
1027
1028
1029
1030
1031
1032
1033
1034
1035
1036
1037
....
1425
1426
1427
1428
1429
1430
1431
1432
1433
1434
1435
1436
1437
1438
1439
....
1443
1444
1445
1446
1447
1448
1449
1450
1451
1452
1453
1454
1455
1456
1457
#   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}
................................................................................
# 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}

Changes to test/e_update.test

200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
...
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
...
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
# 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(rowid, a, b, c) VALUES(1,  3, 1, 4);
  INSERT INTO t2(rowid, a, b, c) VALUES(2,  1, 5, 9);
  INSERT INTO t2(rowid, a, b, c) VALUES(3,  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(rowid, a, b, c) VALUES(1,  3, 1, 4);
  INSERT INTO t2(rowid, a, b, c) VALUES(2,  1, 5, 9);
  INSERT INTO t2(rowid, a, b, c) VALUES(3,  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 {}}
................................................................................
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 ac data } {
  1  "UPDATE t3 SET b='one' WHERE a=3" 
     {column b is not unique} 1 {1 one 2 two 3 three 4 four}

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

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

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

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

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

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

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

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

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

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

  12 "UPDATE OR ROLLBACK t3 SET b='four'"
     {column b is not unique} 1 {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_get_autocommit db} $ac
}



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







|
|
|







 







|
|
|







 







|

|


|


|


|


|

|


|


|


|


|


|


|



|







200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
...
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
...
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
# 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 {}}
................................................................................
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.
#

Changes to test/enc2.test

85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
...
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
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}
} {one I 1 two II 2 three III 3 four IV 4 five V 5}

# 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
} {four}

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

do_test $t.9 {
  sqlite4_finalize $STMT
} SQLITE4_OK

ifcapable vacuum {
  execsql VACUUM
}

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

}

# The three unicode encodings understood by SQLite.







|







 







|




|





<
<
<
<







85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
...
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128




129
130
131
132
133
134
135
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.

Changes to test/enc3.test

46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
      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}







|







46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
      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}

Changes to test/errmsg.test

36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
..
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
..
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
..
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
  lappend ret [sqlite4_finalize $stmt]
  lappend ret [sqlite4_errmsg db]

  set ret
}

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

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

proc sql_error {msg} { error $msg }
................................................................................

#-------------------------------------------------------------------------
# Test error messages returned by user-defined SQL functions.
#
do_test 1.1 {
  error_messages "SELECT sql_error('custom message')"
} [list {*}{
    SQLITE4_ERROR {SQL logic error or missing database} 
    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}
................................................................................
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_ERROR      {SQL logic error or missing database} 
    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 {SQL logic error or missing database} 
    SQLITE4_SCHEMA {database schema has changed}
}]
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







|







 







|







 







|







 







|
|













36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
..
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
..
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
..
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
  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 }
................................................................................

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

Changes to test/eval.test

65
66
67
68
69
70
71
72
73
74
75
  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







|



65
66
67
68
69
70
71
72
73
74
75
  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

Changes to test/expr.test

331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
  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=1e300, r2=1e300} {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)} 99.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







|
|







331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
  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

Changes to test/fuzz.test

30
31
32
33
34
35
36







37
38
39
40
41
42
43
if {[info exists ::G(isquick)]} {
  if {$::G(isquick)} { set ::REPEATS 20 }
}

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








#----------------------------------------------------------------
# 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';
  }







>
>
>
>
>
>
>







30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
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';
  }

Changes to test/in3.test

44
45
46
47
48
49
50





51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
...
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143

144
145
146
147
148
149
150
...
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
...
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271

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);





  }
} {}

# All of these queries should avoid using a temp-table:
#
do_test in3-1.2 {
  exec_neph { SELECT rowid FROM t1 WHERE rowid IN (SELECT rowid FROM t1); }
} {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 t1 WHERE rowid+0 IN (SELECT rowid FROM t1); }
} {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 t1 WHERE rowid IN (SELECT rowid+0 FROM t1); }
} {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}

# Neither of these queries require a temp-table. The collation sequence
# makes no difference when using a rowid.
#
do_test in3-1.16 {
  exec_neph {SELECT a FROM t1 WHERE a COLLATE nocase IN (SELECT rowid FROM t1)}
} {0 1 3}
do_test in3-1.17 {
  exec_neph {SELECT a FROM t1 WHERE a COLLATE binary IN (SELECT rowid FROM t1)}
} {0 1 3}

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

    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}]
................................................................................
  }
  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 t1 
    WHERE rowid IN (SELECT rowid FROM t1 WHERE rowid IN (1, 2));
  }
} {1 2}
do_test in3-2.3 {
  execsql {
    select rowid from t1 where rowid IN (-1,2,4)
  }
} {2 4}
do_test in3-2.4 {
  execsql {
    SELECT rowid FROM t1 WHERE rowid IN 
       (select rowid from t1 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) }
} {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.
#







>
>
>
>
>






|





|










|







 







<
<
<
<
<
<
<
<
<
<






>







 







|
|




|




|
|







 







|







44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
...
126
127
128
129
130
131
132










133
134
135
136
137
138
139
140
141
142
143
144
145
146
...
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
...
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267

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

Changes to test/permutations.test

167
168
169
170
171
172
173



174

175
176
177
178


179
180
181
182
183
184
185
186
187
188
189
  cse.test
  ctime.test
  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_createtable.test e_delete.test e_droptrigger.test e_dropview.test
  e_expr.test
  e_resolve.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
  keyword1.test
  limit.test
  main.test
  manydb.test







>
>
>
|
>
|
<
|
<
>
>


|
|







167
168
169
170
171
172
173
174
175
176
177
178
179

180

181
182
183
184
185
186
187
188
189
190
191
192
193
  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

Changes to test/simple.test

1593
1594
1595
1596
1597
1598
1599







1600
1601
1602
} {-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}








finish_test








>
>
>
>
>
>
>



1593
1594
1595
1596
1597
1598
1599
1600
1601
1602
1603
1604
1605
1606
1607
1608
1609
} {-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