/ Check-in [21302076]
Login
SQLite training in Houston TX on 2019-11-05 (details)
Part of the 2019 Tcl Conference

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

Overview
Comment:Updates to requirements marks. No code changes.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 213020769f310aec1591d97756b53891d0b64005
User & Date: drh 2013-08-02 23:40:45
Context
2013-08-05
12:31
Add a missing '#include "tcl.h"' to test_rtree.c. check-in: 4b8b426f user: drh tags: trunk
2013-08-03
20:24
Begin adding experimental sqlite_stat4 table. This commit is buggy. check-in: 2beea303 user: dan tags: sqlite_stat4
2013-08-02
23:40
Updates to requirements marks. No code changes. check-in: 21302076 user: drh tags: trunk
20:11
Add NEVER() and ALWAYS() macros on some unreachable yet prudent branches. check-in: c5c0a8ab user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Show Whitespace Changes Patch

Changes to src/tokenize.c.

119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
...
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
      testcase( z[0]=='\r' );
      for(i=1; sqlite3Isspace(z[i]); i++){}
      *tokenType = TK_SPACE;
      return i;
    }
    case '-': {
      if( z[1]=='-' ){
        /* IMP: R-50417-27976 -- syntax diagram for comments */
        for(i=2; (c=z[i])!=0 && c!='\n'; i++){}
        *tokenType = TK_SPACE;   /* IMP: R-22934-25134 */
        return i;
      }
      *tokenType = TK_MINUS;
      return 1;
    }
................................................................................
      return 1;
    }
    case '/': {
      if( z[1]!='*' || z[2]==0 ){
        *tokenType = TK_SLASH;
        return 1;
      }
      /* IMP: R-50417-27976 -- syntax diagram for comments */
      for(i=3, c=z[2]; (c!='*' || z[i]!='/') && (c=z[i])!=0; i++){}
      if( c ) i++;
      *tokenType = TK_SPACE;   /* IMP: R-22934-25134 */
      return i;
    }
    case '%': {
      *tokenType = TK_REM;







<







 







<







119
120
121
122
123
124
125

126
127
128
129
130
131
132
...
151
152
153
154
155
156
157

158
159
160
161
162
163
164
      testcase( z[0]=='\r' );
      for(i=1; sqlite3Isspace(z[i]); i++){}
      *tokenType = TK_SPACE;
      return i;
    }
    case '-': {
      if( z[1]=='-' ){

        for(i=2; (c=z[i])!=0 && c!='\n'; i++){}
        *tokenType = TK_SPACE;   /* IMP: R-22934-25134 */
        return i;
      }
      *tokenType = TK_MINUS;
      return 1;
    }
................................................................................
      return 1;
    }
    case '/': {
      if( z[1]!='*' || z[2]==0 ){
        *tokenType = TK_SLASH;
        return 1;
      }

      for(i=3, c=z[2]; (c!='*' || z[i]!='/') && (c=z[i])!=0; i++){}
      if( c ) i++;
      *tokenType = TK_SPACE;   /* IMP: R-22934-25134 */
      return i;
    }
    case '%': {
      *tokenType = TK_REM;

Changes to src/where.c.

654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
...
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
....
1479
1480
1481
1482
1483
1484
1485
1486
1487
1488
1489
1490
1491
1492
1493
1494
....
1722
1723
1724
1725
1726
1727
1728
1729
1730
1731
1732
1733
1734
1735
1736
1737
1738
....
2814
2815
2816
2817
2818
2819
2820
2821
2822
2823
2824
2825
2826
2827
2828
2829
2830
....
3046
3047
3048
3049
3050
3051
3052
3053
3054
3055
3056
3057
3058
3059
3060
....
3357
3358
3359
3360
3361
3362
3363
3364
3365
3366
3367
3368
3369
3370
3371
....
3405
3406
3407
3408
3409
3410
3411
3412
3413
3414
3415
3416
3417
3418
3419
....
3424
3425
3426
3427
3428
3429
3430
3431
3432
3433
3434
3435
3436
3437
3438
....
3590
3591
3592
3593
3594
3595
3596
3597
3598
3599
3600
3601
3602
3603
3604
....
3632
3633
3634
3635
3636
3637
3638
3639
3640
3641
3642
3643
3644
3645
3646
....
3919
3920
3921
3922
3923
3924
3925
3926
3927
3928
3929
3930
3931
3932
3933
3934
3935
3936
3937
3938
3939
3940
....
3986
3987
3988
3989
3990
3991
3992
3993
3994
3995
3996
3997
3998
3999
4000
....
5759
5760
5761
5762
5763
5764
5765
5766
5767
5768
5769
5770
5771
5772
5773
** WhereTerms.  All pointers to WhereTerms should be invalidated after
** calling this routine.  Such pointers may be reinitialized by referencing
** the pWC->a[] array.
*/
static int whereClauseInsert(WhereClause *pWC, Expr *p, u8 wtFlags){
  WhereTerm *pTerm;
  int idx;
  testcase( wtFlags & TERM_VIRTUAL );  /* EV: R-00211-15100 */
  if( pWC->nTerm>=pWC->nSlot ){
    WhereTerm *pOld = pWC->a;
    sqlite3 *db = pWC->pWInfo->pParse->db;
    pWC->a = sqlite3DbMallocRaw(db, sizeof(pWC->a[0])*pWC->nSlot*2 );
    if( pWC->a==0 ){
      if( wtFlags & TERM_DYNAMIC ){
        sqlite3ExprDelete(db, p);
................................................................................
  return mask;
}

/*
** Return TRUE if the given operator is one of the operators that is
** allowed for an indexable WHERE clause term.  The allowed operators are
** "=", "<", ">", "<=", ">=", "IN", and "IS NULL"
**
** IMPLEMENTATION-OF: R-59926-26393 To be usable by an index a term must be
** of one of the following forms: column = expression column > expression
** column >= expression column < expression column <= expression
** expression = column expression > column expression >= column
** expression < column expression <= column column IN
** (expression-list) column IN (subquery) column IS NULL
*/
static int allowedOp(int op){
  assert( TK_GT>TK_EQ && TK_GT<TK_GE );
  assert( TK_LT>TK_EQ && TK_LT<TK_GE );
  assert( TK_LE>TK_EQ && TK_LE<TK_GE );
  assert( TK_GE==TK_EQ+4 );
  return op==TK_IN || (op>=TK_EQ && op<=TK_GE) || op==TK_ISNULL;
................................................................................
        }
      }
    }

    /* At this point, okToChngToIN is true if original pTerm satisfies
    ** case 1.  In that case, construct a new virtual term that is 
    ** pTerm converted into an IN operator.
    **
    ** EV: R-00211-15100
    */
    if( okToChngToIN ){
      Expr *pDup;            /* A transient duplicate expression */
      ExprList *pList = 0;   /* The RHS of the IN operator */
      Expr *pLeft = 0;       /* The LHS of the IN operator */
      Expr *pNew;            /* The complete IN operator */

................................................................................
      if( noCase ){
        /* The point is to increment the last character before the first
        ** wildcard.  But if we increment '@', that will push it into the
        ** alphabetic range where case conversions will mess up the 
        ** inequality.  To avoid this, make sure to also run the full
        ** LIKE on all candidate expressions by clearing the isComplete flag
        */
        if( c=='A'-1 ) isComplete = 0;   /* EV: R-64339-08207 */


        c = sqlite3UpperToLower[c];
      }
      *pC = c + 1;
    }
    sCollSeqName.z = noCase ? "NOCASE" : "BINARY";
    sCollSeqName.n = 6;
    pNewExpr1 = sqlite3ExprDup(db, pLeft, 0);
................................................................................
**   (2)  SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.x AND t2.z='ok'
**   (3)  SELECT * FROM t1, t2 WHERE t1.a=t2.x AND t2.z='ok'
**
** The t2.z='ok' is disabled in the in (2) because it originates
** in the ON clause.  The term is disabled in (3) because it is not part
** of a LEFT OUTER JOIN.  In (1), the term is not disabled.
**
** IMPLEMENTATION-OF: R-24597-58655 No tests are done for terms that are
** completely satisfied by indices.
**
** Disabling a term causes that term to not be tested in the inner loop
** of the join.  Disabling is an optimization.  When terms are satisfied
** by indices, we disable them to prevent redundant tests in the inner
** loop.  We would get the correct results if nothing were ever disabled,
** but joins might run a little slower.  The trick is to disable as much
** as we can without disabling too much.  If we disabled in (1), we'd get
** the wrong answer.  See ticket #813.
................................................................................
  for(j=0; j<nEq; j++){
    int r1;
    pTerm = pLoop->aLTerm[j];
    assert( pTerm!=0 );
    /* The following true for indices with redundant columns. 
    ** Ex: CREATE INDEX i1 ON t1(a,b,a); SELECT * FROM t1 WHERE a=0 AND b=0; */
    testcase( (pTerm->wtFlags & TERM_CODED)!=0 );
    testcase( pTerm->wtFlags & TERM_VIRTUAL ); /* EV: R-30575-11662 */
    r1 = codeEqualityTerm(pParse, pTerm, pLevel, j, bRev, regBase+j);
    if( r1!=regBase+j ){
      if( nReg==1 ){
        sqlite3ReleaseTempReg(pParse, regBase);
        regBase = r1;
      }else{
        sqlite3VdbeAddOp2(v, OP_SCopy, r1, regBase+j);
................................................................................
    */
    assert( pLoop->u.btree.nEq==1 );
    iReleaseReg = sqlite3GetTempReg(pParse);
    pTerm = pLoop->aLTerm[0];
    assert( pTerm!=0 );
    assert( pTerm->pExpr!=0 );
    assert( omitTable==0 );
    testcase( pTerm->wtFlags & TERM_VIRTUAL ); /* EV: R-30575-11662 */
    iRowidReg = codeEqualityTerm(pParse, pTerm, pLevel, 0, bRev, iReleaseReg);
    addrNxt = pLevel->addrNxt;
    sqlite3VdbeAddOp2(v, OP_MustBeInt, iRowidReg, addrNxt);
    sqlite3VdbeAddOp3(v, OP_NotExists, iCur, addrNxt, iRowidReg);
    sqlite3ExprCacheAffinityChange(pParse, iRowidReg, 1);
    sqlite3ExprCacheStore(pParse, iCur, -1, iRowidReg);
    VdbeComment((v, "pk"));
................................................................................
           /* TK_GE */  OP_SeekGe
      };
      assert( TK_LE==TK_GT+1 );      /* Make sure the ordering.. */
      assert( TK_LT==TK_GT+2 );      /*  ... of the TK_xx values... */
      assert( TK_GE==TK_GT+3 );      /*  ... is correcct. */

      assert( (pStart->wtFlags & TERM_VNULL)==0 );
      testcase( pStart->wtFlags & TERM_VIRTUAL ); /* EV: R-30575-11662 */
      pX = pStart->pExpr;
      assert( pX!=0 );
      testcase( pStart->leftCursor!=iCur ); /* transitive constraints */
      r1 = sqlite3ExprCodeTemp(pParse, pX->pRight, &rTemp);
      sqlite3VdbeAddOp3(v, aMoveOp[pX->op-TK_GT], iCur, addrBrk, r1);
      VdbeComment((v, "pk"));
      sqlite3ExprCacheAffinityChange(pParse, r1, 1);
................................................................................
    }
    if( pEnd ){
      Expr *pX;
      pX = pEnd->pExpr;
      assert( pX!=0 );
      assert( (pEnd->wtFlags & TERM_VNULL)==0 );
      testcase( pEnd->leftCursor!=iCur ); /* Transitive constraints */
      testcase( pEnd->wtFlags & TERM_VIRTUAL ); /* EV: R-30575-11662 */
      memEndValue = ++pParse->nMem;
      sqlite3ExprCode(pParse, pX->pRight, memEndValue);
      if( pX->op==TK_LT || pX->op==TK_GT ){
        testOp = bRev ? OP_Le : OP_Ge;
      }else{
        testOp = bRev ? OP_Lt : OP_Gt;
      }
................................................................................
          zStartAff[nEq] = SQLITE_AFF_NONE;
        }
        if( sqlite3ExprNeedsNoAffinityChange(pRight, zStartAff[nEq]) ){
          zStartAff[nEq] = SQLITE_AFF_NONE;
        }
      }  
      nConstraint++;
      testcase( pRangeStart->wtFlags & TERM_VIRTUAL ); /* EV: R-30575-11662 */
    }else if( isMinQuery ){
      sqlite3VdbeAddOp2(v, OP_Null, 0, regBase+nEq);
      nConstraint++;
      startEq = 0;
      start_constraints = 1;
    }
    codeApplyAffinity(pParse, regBase, nConstraint, zStartAff);
................................................................................
        }
        if( sqlite3ExprNeedsNoAffinityChange(pRight, zEndAff[nEq]) ){
          zEndAff[nEq] = SQLITE_AFF_NONE;
        }
      }  
      codeApplyAffinity(pParse, regBase, nEq+1, zEndAff);
      nConstraint++;
      testcase( pRangeEnd->wtFlags & TERM_VIRTUAL ); /* EV: R-30575-11662 */
    }
    sqlite3DbFree(db, zStartAff);
    sqlite3DbFree(db, zEndAff);

    /* Top of the loop body */
    pLevel->p2 = sqlite3VdbeCurrentAddr(v);

................................................................................
    pLevel->p2 = 1 + sqlite3VdbeAddOp2(v, aStart[bRev], iCur, addrBrk);
    pLevel->p5 = SQLITE_STMTSTATUS_FULLSCAN_STEP;
  }
  newNotReady = notReady & ~getMask(&pWInfo->sMaskSet, iCur);

  /* Insert code to test every subexpression that can be completely
  ** computed using the current set of tables.
  **
  ** IMPLEMENTATION-OF: R-49525-50935 Terms that cannot be satisfied through
  ** the use of indices become tests that are evaluated against each row of
  ** the relevant input tables.
  */
  for(pTerm=pWC->a, j=pWC->nTerm; j>0; j--, pTerm++){
    Expr *pE;
    testcase( pTerm->wtFlags & TERM_VIRTUAL ); /* IMP: R-30575-11662 */
    testcase( pTerm->wtFlags & TERM_CODED );
    if( pTerm->wtFlags & (TERM_VIRTUAL|TERM_CODED) ) continue;
    if( (pTerm->prereqAll & newNotReady)!=0 ){
      testcase( pWInfo->untestedTerms==0
               && (pWInfo->wctrlFlags & WHERE_ONETABLE_ONLY)!=0 );
      pWInfo->untestedTerms = 1;
      continue;
................................................................................
  */
  if( pLevel->iLeftJoin ){
    pLevel->addrFirst = sqlite3VdbeCurrentAddr(v);
    sqlite3VdbeAddOp2(v, OP_Integer, 1, pLevel->iLeftJoin);
    VdbeComment((v, "record LEFT JOIN hit"));
    sqlite3ExprCacheClear(pParse);
    for(pTerm=pWC->a, j=0; j<pWC->nTerm; j++, pTerm++){
      testcase( pTerm->wtFlags & TERM_VIRTUAL );  /* IMP: R-30575-11662 */
      testcase( pTerm->wtFlags & TERM_CODED );
      if( pTerm->wtFlags & (TERM_VIRTUAL|TERM_CODED) ) continue;
      if( (pTerm->prereqAll & newNotReady)!=0 ){
        assert( pWInfo->untestedTerms );
        continue;
      }
      assert( pTerm->pExpr );
................................................................................

  /* Split the WHERE clause into separate subexpressions where each
  ** subexpression is separated by an AND operator.
  */
  initMaskSet(pMaskSet);
  whereClauseInit(&pWInfo->sWC, pWInfo);
  sqlite3ExprCodeConstants(pParse, pWhere);
  whereSplit(&pWInfo->sWC, pWhere, TK_AND);   /* IMP: R-15842-53296 */
  sqlite3CodeVerifySchema(pParse, -1); /* Insert the cookie verifier Goto */
    
  /* Special case: a WHERE clause that is constant.  Evaluate the
  ** expression and either jump over all of the code or fall thru.
  */
  if( pWhere && (nTabList==0 || sqlite3ExprIsConstantNotJoin(pWhere)) ){
    sqlite3ExprIfFalse(pParse, pWhere, pWInfo->iBreak, SQLITE_JUMPIFNULL);







|







 







<
<
<
<
<
<
<







 







<
<







 







|
<
<







 







<
<
<







 







|







 







|







 







|







 







|







 







|







 







|







 







<
<
<
<



|







 







|







 







|







654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
...
799
800
801
802
803
804
805







806
807
808
809
810
811
812
....
1472
1473
1474
1475
1476
1477
1478


1479
1480
1481
1482
1483
1484
1485
....
1713
1714
1715
1716
1717
1718
1719
1720


1721
1722
1723
1724
1725
1726
1727
....
2803
2804
2805
2806
2807
2808
2809



2810
2811
2812
2813
2814
2815
2816
....
3032
3033
3034
3035
3036
3037
3038
3039
3040
3041
3042
3043
3044
3045
3046
....
3343
3344
3345
3346
3347
3348
3349
3350
3351
3352
3353
3354
3355
3356
3357
....
3391
3392
3393
3394
3395
3396
3397
3398
3399
3400
3401
3402
3403
3404
3405
....
3410
3411
3412
3413
3414
3415
3416
3417
3418
3419
3420
3421
3422
3423
3424
....
3576
3577
3578
3579
3580
3581
3582
3583
3584
3585
3586
3587
3588
3589
3590
....
3618
3619
3620
3621
3622
3623
3624
3625
3626
3627
3628
3629
3630
3631
3632
....
3905
3906
3907
3908
3909
3910
3911




3912
3913
3914
3915
3916
3917
3918
3919
3920
3921
3922
....
3968
3969
3970
3971
3972
3973
3974
3975
3976
3977
3978
3979
3980
3981
3982
....
5741
5742
5743
5744
5745
5746
5747
5748
5749
5750
5751
5752
5753
5754
5755
** WhereTerms.  All pointers to WhereTerms should be invalidated after
** calling this routine.  Such pointers may be reinitialized by referencing
** the pWC->a[] array.
*/
static int whereClauseInsert(WhereClause *pWC, Expr *p, u8 wtFlags){
  WhereTerm *pTerm;
  int idx;
  testcase( wtFlags & TERM_VIRTUAL );
  if( pWC->nTerm>=pWC->nSlot ){
    WhereTerm *pOld = pWC->a;
    sqlite3 *db = pWC->pWInfo->pParse->db;
    pWC->a = sqlite3DbMallocRaw(db, sizeof(pWC->a[0])*pWC->nSlot*2 );
    if( pWC->a==0 ){
      if( wtFlags & TERM_DYNAMIC ){
        sqlite3ExprDelete(db, p);
................................................................................
  return mask;
}

/*
** Return TRUE if the given operator is one of the operators that is
** allowed for an indexable WHERE clause term.  The allowed operators are
** "=", "<", ">", "<=", ">=", "IN", and "IS NULL"







*/
static int allowedOp(int op){
  assert( TK_GT>TK_EQ && TK_GT<TK_GE );
  assert( TK_LT>TK_EQ && TK_LT<TK_GE );
  assert( TK_LE>TK_EQ && TK_LE<TK_GE );
  assert( TK_GE==TK_EQ+4 );
  return op==TK_IN || (op>=TK_EQ && op<=TK_GE) || op==TK_ISNULL;
................................................................................
        }
      }
    }

    /* At this point, okToChngToIN is true if original pTerm satisfies
    ** case 1.  In that case, construct a new virtual term that is 
    ** pTerm converted into an IN operator.


    */
    if( okToChngToIN ){
      Expr *pDup;            /* A transient duplicate expression */
      ExprList *pList = 0;   /* The RHS of the IN operator */
      Expr *pLeft = 0;       /* The LHS of the IN operator */
      Expr *pNew;            /* The complete IN operator */

................................................................................
      if( noCase ){
        /* The point is to increment the last character before the first
        ** wildcard.  But if we increment '@', that will push it into the
        ** alphabetic range where case conversions will mess up the 
        ** inequality.  To avoid this, make sure to also run the full
        ** LIKE on all candidate expressions by clearing the isComplete flag
        */
        if( c=='A'-1 ) isComplete = 0;


        c = sqlite3UpperToLower[c];
      }
      *pC = c + 1;
    }
    sCollSeqName.z = noCase ? "NOCASE" : "BINARY";
    sCollSeqName.n = 6;
    pNewExpr1 = sqlite3ExprDup(db, pLeft, 0);
................................................................................
**   (2)  SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.x AND t2.z='ok'
**   (3)  SELECT * FROM t1, t2 WHERE t1.a=t2.x AND t2.z='ok'
**
** The t2.z='ok' is disabled in the in (2) because it originates
** in the ON clause.  The term is disabled in (3) because it is not part
** of a LEFT OUTER JOIN.  In (1), the term is not disabled.
**



** Disabling a term causes that term to not be tested in the inner loop
** of the join.  Disabling is an optimization.  When terms are satisfied
** by indices, we disable them to prevent redundant tests in the inner
** loop.  We would get the correct results if nothing were ever disabled,
** but joins might run a little slower.  The trick is to disable as much
** as we can without disabling too much.  If we disabled in (1), we'd get
** the wrong answer.  See ticket #813.
................................................................................
  for(j=0; j<nEq; j++){
    int r1;
    pTerm = pLoop->aLTerm[j];
    assert( pTerm!=0 );
    /* The following true for indices with redundant columns. 
    ** Ex: CREATE INDEX i1 ON t1(a,b,a); SELECT * FROM t1 WHERE a=0 AND b=0; */
    testcase( (pTerm->wtFlags & TERM_CODED)!=0 );
    testcase( pTerm->wtFlags & TERM_VIRTUAL );
    r1 = codeEqualityTerm(pParse, pTerm, pLevel, j, bRev, regBase+j);
    if( r1!=regBase+j ){
      if( nReg==1 ){
        sqlite3ReleaseTempReg(pParse, regBase);
        regBase = r1;
      }else{
        sqlite3VdbeAddOp2(v, OP_SCopy, r1, regBase+j);
................................................................................
    */
    assert( pLoop->u.btree.nEq==1 );
    iReleaseReg = sqlite3GetTempReg(pParse);
    pTerm = pLoop->aLTerm[0];
    assert( pTerm!=0 );
    assert( pTerm->pExpr!=0 );
    assert( omitTable==0 );
    testcase( pTerm->wtFlags & TERM_VIRTUAL );
    iRowidReg = codeEqualityTerm(pParse, pTerm, pLevel, 0, bRev, iReleaseReg);
    addrNxt = pLevel->addrNxt;
    sqlite3VdbeAddOp2(v, OP_MustBeInt, iRowidReg, addrNxt);
    sqlite3VdbeAddOp3(v, OP_NotExists, iCur, addrNxt, iRowidReg);
    sqlite3ExprCacheAffinityChange(pParse, iRowidReg, 1);
    sqlite3ExprCacheStore(pParse, iCur, -1, iRowidReg);
    VdbeComment((v, "pk"));
................................................................................
           /* TK_GE */  OP_SeekGe
      };
      assert( TK_LE==TK_GT+1 );      /* Make sure the ordering.. */
      assert( TK_LT==TK_GT+2 );      /*  ... of the TK_xx values... */
      assert( TK_GE==TK_GT+3 );      /*  ... is correcct. */

      assert( (pStart->wtFlags & TERM_VNULL)==0 );
      testcase( pStart->wtFlags & TERM_VIRTUAL );
      pX = pStart->pExpr;
      assert( pX!=0 );
      testcase( pStart->leftCursor!=iCur ); /* transitive constraints */
      r1 = sqlite3ExprCodeTemp(pParse, pX->pRight, &rTemp);
      sqlite3VdbeAddOp3(v, aMoveOp[pX->op-TK_GT], iCur, addrBrk, r1);
      VdbeComment((v, "pk"));
      sqlite3ExprCacheAffinityChange(pParse, r1, 1);
................................................................................
    }
    if( pEnd ){
      Expr *pX;
      pX = pEnd->pExpr;
      assert( pX!=0 );
      assert( (pEnd->wtFlags & TERM_VNULL)==0 );
      testcase( pEnd->leftCursor!=iCur ); /* Transitive constraints */
      testcase( pEnd->wtFlags & TERM_VIRTUAL );
      memEndValue = ++pParse->nMem;
      sqlite3ExprCode(pParse, pX->pRight, memEndValue);
      if( pX->op==TK_LT || pX->op==TK_GT ){
        testOp = bRev ? OP_Le : OP_Ge;
      }else{
        testOp = bRev ? OP_Lt : OP_Gt;
      }
................................................................................
          zStartAff[nEq] = SQLITE_AFF_NONE;
        }
        if( sqlite3ExprNeedsNoAffinityChange(pRight, zStartAff[nEq]) ){
          zStartAff[nEq] = SQLITE_AFF_NONE;
        }
      }  
      nConstraint++;
      testcase( pRangeStart->wtFlags & TERM_VIRTUAL );
    }else if( isMinQuery ){
      sqlite3VdbeAddOp2(v, OP_Null, 0, regBase+nEq);
      nConstraint++;
      startEq = 0;
      start_constraints = 1;
    }
    codeApplyAffinity(pParse, regBase, nConstraint, zStartAff);
................................................................................
        }
        if( sqlite3ExprNeedsNoAffinityChange(pRight, zEndAff[nEq]) ){
          zEndAff[nEq] = SQLITE_AFF_NONE;
        }
      }  
      codeApplyAffinity(pParse, regBase, nEq+1, zEndAff);
      nConstraint++;
      testcase( pRangeEnd->wtFlags & TERM_VIRTUAL );
    }
    sqlite3DbFree(db, zStartAff);
    sqlite3DbFree(db, zEndAff);

    /* Top of the loop body */
    pLevel->p2 = sqlite3VdbeCurrentAddr(v);

................................................................................
    pLevel->p2 = 1 + sqlite3VdbeAddOp2(v, aStart[bRev], iCur, addrBrk);
    pLevel->p5 = SQLITE_STMTSTATUS_FULLSCAN_STEP;
  }
  newNotReady = notReady & ~getMask(&pWInfo->sMaskSet, iCur);

  /* Insert code to test every subexpression that can be completely
  ** computed using the current set of tables.




  */
  for(pTerm=pWC->a, j=pWC->nTerm; j>0; j--, pTerm++){
    Expr *pE;
    testcase( pTerm->wtFlags & TERM_VIRTUAL );
    testcase( pTerm->wtFlags & TERM_CODED );
    if( pTerm->wtFlags & (TERM_VIRTUAL|TERM_CODED) ) continue;
    if( (pTerm->prereqAll & newNotReady)!=0 ){
      testcase( pWInfo->untestedTerms==0
               && (pWInfo->wctrlFlags & WHERE_ONETABLE_ONLY)!=0 );
      pWInfo->untestedTerms = 1;
      continue;
................................................................................
  */
  if( pLevel->iLeftJoin ){
    pLevel->addrFirst = sqlite3VdbeCurrentAddr(v);
    sqlite3VdbeAddOp2(v, OP_Integer, 1, pLevel->iLeftJoin);
    VdbeComment((v, "record LEFT JOIN hit"));
    sqlite3ExprCacheClear(pParse);
    for(pTerm=pWC->a, j=0; j<pWC->nTerm; j++, pTerm++){
      testcase( pTerm->wtFlags & TERM_VIRTUAL );
      testcase( pTerm->wtFlags & TERM_CODED );
      if( pTerm->wtFlags & (TERM_VIRTUAL|TERM_CODED) ) continue;
      if( (pTerm->prereqAll & newNotReady)!=0 ){
        assert( pWInfo->untestedTerms );
        continue;
      }
      assert( pTerm->pExpr );
................................................................................

  /* Split the WHERE clause into separate subexpressions where each
  ** subexpression is separated by an AND operator.
  */
  initMaskSet(pMaskSet);
  whereClauseInit(&pWInfo->sWC, pWInfo);
  sqlite3ExprCodeConstants(pParse, pWhere);
  whereSplit(&pWInfo->sWC, pWhere, TK_AND);
  sqlite3CodeVerifySchema(pParse, -1); /* Insert the cookie verifier Goto */
    
  /* Special case: a WHERE clause that is constant.  Evaluate the
  ** expression and either jump over all of the code or fall thru.
  */
  if( pWhere && (nTabList==0 || sqlite3ExprIsConstantNotJoin(pWhere)) ){
    sqlite3ExprIfFalse(pParse, pWhere, pWInfo->iBreak, SQLITE_JUMPIFNULL);

Changes to test/e_createtable.test.

54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
..
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
...
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
...
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
167
168
169
170
...
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
      db eval "SELECT DISTINCT tbl_name FROM $master ORDER BY tbl_name"
    ]
  }
  set res
}


# EVIDENCE-OF: R-47266-09114 -- syntax diagram type-name
#
do_createtable_tests 0.1.1 -repair {
  drop_all_tables
} {
  1   "CREATE TABLE t1(c1 one)"                        {}
  2   "CREATE TABLE t1(c1 one two)"                    {}
  3   "CREATE TABLE t1(c1 one two three)"              {}
  4   "CREATE TABLE t1(c1 one two three four)"         {}
................................................................................
do_createtable_tests 0.1.2 -error {
  near "%s": syntax error
} {
  1   "CREATE TABLE t1(c1 one(number))"                {number}
}


# EVIDENCE-OF: R-60689-48779 -- syntax diagram column-constraint
#
do_createtable_tests 0.2.1 -repair {
  drop_all_tables 
  execsql { CREATE TABLE t2(x PRIMARY KEY) }
} {
  1.1   "CREATE TABLE t1(c1 text PRIMARY KEY)"                         {}
  1.2   "CREATE TABLE t1(c1 text PRIMARY KEY ASC)"                     {}
................................................................................
  8.2   {
    CREATE TABLE t1(c1 
      REFERENCES t1 DEFAULT 123 CHECK(c1 IS 'ten') UNIQUE NOT NULL PRIMARY KEY 
    );
  } {}
}

# EVIDENCE-OF: R-58169-51804 -- syntax diagram table-constraint
#
do_createtable_tests 0.3.1 -repair {
  drop_all_tables 
  execsql { CREATE TABLE t2(x PRIMARY KEY) }
} {
  1.1   "CREATE TABLE t1(c1, c2, PRIMARY KEY(c1))"                         {}
  1.2   "CREATE TABLE t1(c1, c2, PRIMARY KEY(c1, c2))"                     {}
................................................................................
  2.3   "CREATE TABLE t1(c1, c2, UNIQUE(c1, c2) ON CONFLICT IGNORE)"       {}

  3.1   "CREATE TABLE t1(c1, c2, CHECK(c1 IS NOT c2))"                     {}

  4.1   "CREATE TABLE t1(c1, c2, FOREIGN KEY(c1) REFERENCES t2)"           {}
}

# EVIDENCE-OF: R-44826-22243 -- syntax diagram column-def
#
do_createtable_tests 0.4.1 -repair {
  drop_all_tables 
} {
  1     {CREATE TABLE t1(
           col1,
           col2 TEXT,
................................................................................
           col3 INTEGER UNIQUE,
           col4 VARCHAR(10, 10) PRIMARY KEY,
           "name with spaces" REFERENCES t1
         );
        } {}
}

# EVIDENCE-OF: R-45698-45677 -- syntax diagram create-table-stmt
#
do_createtable_tests 0.5.1 -repair {
  drop_all_tables 
  execsql { CREATE TABLE t2(a, b, c) }
} {
  1     "CREATE TABLE t1(a, b, c)"                                    {}
  2     "CREATE TEMP TABLE t1(a, b, c)"                               {}
................................................................................
  12    "CREATE TEMPORARY TABLE IF NOT EXISTS temp.t1(a, b, c)"       {}

  13    "CREATE TABLE t1 AS SELECT * FROM t2"                         {}
  14    "CREATE TEMP TABLE t1 AS SELECT c, b, a FROM t2"              {}
  15    "CREATE TABLE t1 AS SELECT count(*), max(b), min(a) FROM t2"  {}
}

# EVIDENCE-OF: R-24369-11919 -- syntax diagram foreign-key-clause
#
#   1:         Explicit parent-key columns.
#   2:         Implicit child-key columns.
#
#   1:         MATCH FULL
#   2:         MATCH PARTIAL
#   3:         MATCH SIMPLE







<
<







 







|







 







|







 







|







 







|







 







<







54
55
56
57
58
59
60


61
62
63
64
65
66
67
..
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
...
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
...
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
167
168
...
179
180
181
182
183
184
185

186
187
188
189
190
191
192
      db eval "SELECT DISTINCT tbl_name FROM $master ORDER BY tbl_name"
    ]
  }
  set res
}




do_createtable_tests 0.1.1 -repair {
  drop_all_tables
} {
  1   "CREATE TABLE t1(c1 one)"                        {}
  2   "CREATE TABLE t1(c1 one two)"                    {}
  3   "CREATE TABLE t1(c1 one two three)"              {}
  4   "CREATE TABLE t1(c1 one two three four)"         {}
................................................................................
do_createtable_tests 0.1.2 -error {
  near "%s": syntax error
} {
  1   "CREATE TABLE t1(c1 one(number))"                {number}
}


# syntax diagram column-constraint
#
do_createtable_tests 0.2.1 -repair {
  drop_all_tables 
  execsql { CREATE TABLE t2(x PRIMARY KEY) }
} {
  1.1   "CREATE TABLE t1(c1 text PRIMARY KEY)"                         {}
  1.2   "CREATE TABLE t1(c1 text PRIMARY KEY ASC)"                     {}
................................................................................
  8.2   {
    CREATE TABLE t1(c1 
      REFERENCES t1 DEFAULT 123 CHECK(c1 IS 'ten') UNIQUE NOT NULL PRIMARY KEY 
    );
  } {}
}

# -- syntax diagram table-constraint
#
do_createtable_tests 0.3.1 -repair {
  drop_all_tables 
  execsql { CREATE TABLE t2(x PRIMARY KEY) }
} {
  1.1   "CREATE TABLE t1(c1, c2, PRIMARY KEY(c1))"                         {}
  1.2   "CREATE TABLE t1(c1, c2, PRIMARY KEY(c1, c2))"                     {}
................................................................................
  2.3   "CREATE TABLE t1(c1, c2, UNIQUE(c1, c2) ON CONFLICT IGNORE)"       {}

  3.1   "CREATE TABLE t1(c1, c2, CHECK(c1 IS NOT c2))"                     {}

  4.1   "CREATE TABLE t1(c1, c2, FOREIGN KEY(c1) REFERENCES t2)"           {}
}

# -- syntax diagram column-def
#
do_createtable_tests 0.4.1 -repair {
  drop_all_tables 
} {
  1     {CREATE TABLE t1(
           col1,
           col2 TEXT,
................................................................................
           col3 INTEGER UNIQUE,
           col4 VARCHAR(10, 10) PRIMARY KEY,
           "name with spaces" REFERENCES t1
         );
        } {}
}

# -- syntax diagram create-table-stmt
#
do_createtable_tests 0.5.1 -repair {
  drop_all_tables 
  execsql { CREATE TABLE t2(a, b, c) }
} {
  1     "CREATE TABLE t1(a, b, c)"                                    {}
  2     "CREATE TEMP TABLE t1(a, b, c)"                               {}
................................................................................
  12    "CREATE TEMPORARY TABLE IF NOT EXISTS temp.t1(a, b, c)"       {}

  13    "CREATE TABLE t1 AS SELECT * FROM t2"                         {}
  14    "CREATE TEMP TABLE t1 AS SELECT c, b, a FROM t2"              {}
  15    "CREATE TABLE t1 AS SELECT count(*), max(b), min(a) FROM t2"  {}
}


#
#   1:         Explicit parent-key columns.
#   2:         Implicit child-key columns.
#
#   1:         MATCH FULL
#   2:         MATCH PARTIAL
#   3:         MATCH SIMPLE

Changes to test/e_delete.test.

25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
...
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
}

do_execsql_test e_delete-0.0 {
  CREATE TABLE t1(a, b);
  CREATE INDEX i1 ON t1(a);
} {}

# EVIDENCE-OF: R-62077-19799 -- syntax diagram delete-stmt
#
# EVIDENCE-OF: R-60796-31013 -- syntax diagram qualified-table-name
#
do_delete_tests e_delete-0.1 {
  1  "DELETE FROM t1"                              {}
  2  "DELETE FROM t1 INDEXED BY i1"                {}
  3  "DELETE FROM t1 NOT INDEXED"                  {}
  4  "DELETE FROM main.t1"                         {}
  5  "DELETE FROM main.t1 INDEXED BY i1"           {}
................................................................................
}

# EVIDENCE-OF: R-40026-10531 If SQLite is compiled with the
# SQLITE_ENABLE_UPDATE_DELETE_LIMIT compile-time option, then the syntax
# of the DELETE statement is extended by the addition of optional ORDER
# BY and LIMIT clauses:
#
# EVIDENCE-OF: R-52694-53361 -- syntax diagram delete-stmt-limited
#
do_delete_tests e_delete-3.1 {
  1   "DELETE FROM t1 LIMIT 5"                                    {}
  2   "DELETE FROM t1 LIMIT 5-1 OFFSET 2+2"                       {}
  3   "DELETE FROM t1 LIMIT 2+2, 16/4"                            {}
  4   "DELETE FROM t1 ORDER BY x LIMIT 5"                         {}
  5   "DELETE FROM t1 ORDER BY x LIMIT 5-1 OFFSET 2+2"            {}







|
<
|







 







|







25
26
27
28
29
30
31
32

33
34
35
36
37
38
39
40
...
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
}

do_execsql_test e_delete-0.0 {
  CREATE TABLE t1(a, b);
  CREATE INDEX i1 ON t1(a);
} {}

# -- syntax diagram delete-stmt

# -- syntax diagram qualified-table-name
#
do_delete_tests e_delete-0.1 {
  1  "DELETE FROM t1"                              {}
  2  "DELETE FROM t1 INDEXED BY i1"                {}
  3  "DELETE FROM t1 NOT INDEXED"                  {}
  4  "DELETE FROM main.t1"                         {}
  5  "DELETE FROM main.t1 INDEXED BY i1"           {}
................................................................................
}

# EVIDENCE-OF: R-40026-10531 If SQLite is compiled with the
# SQLITE_ENABLE_UPDATE_DELETE_LIMIT compile-time option, then the syntax
# of the DELETE statement is extended by the addition of optional ORDER
# BY and LIMIT clauses:
#
# -- syntax diagram delete-stmt-limited
#
do_delete_tests e_delete-3.1 {
  1   "DELETE FROM t1 LIMIT 5"                                    {}
  2   "DELETE FROM t1 LIMIT 5-1 OFFSET 2+2"                       {}
  3   "DELETE FROM t1 LIMIT 2+2, 16/4"                            {}
  4   "DELETE FROM t1 ORDER BY x LIMIT 5"                         {}
  5   "DELETE FROM t1 ORDER BY x LIMIT 5-1 OFFSET 2+2"            {}

Changes to test/e_droptrigger.test.

65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
    CREATE TRIGGER aux.tr1 BEFORE $event ON t3 BEGIN SELECT r('aux.tr1') ; END;
    CREATE TRIGGER aux.tr2 AFTER  $event ON t3 BEGIN SELECT r('aux.tr2') ; END;
    CREATE TRIGGER aux.tr3 AFTER  $event ON t3 BEGIN SELECT r('aux.tr3') ; END;
  "
}


# EVIDENCE-OF: R-27975-10951 -- syntax diagram drop-trigger-stmt
#
do_droptrigger_tests 1.1 -repair {
  droptrigger_reopen_db
} -tclquery {
  list_all_triggers 
} {
  1   "DROP TRIGGER main.tr1"            







|







65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
    CREATE TRIGGER aux.tr1 BEFORE $event ON t3 BEGIN SELECT r('aux.tr1') ; END;
    CREATE TRIGGER aux.tr2 AFTER  $event ON t3 BEGIN SELECT r('aux.tr2') ; END;
    CREATE TRIGGER aux.tr3 AFTER  $event ON t3 BEGIN SELECT r('aux.tr3') ; END;
  "
}


# -- syntax diagram drop-trigger-stmt
#
do_droptrigger_tests 1.1 -repair {
  droptrigger_reopen_db
} -tclquery {
  list_all_triggers 
} {
  1   "DROP TRIGGER main.tr1"            

Changes to test/e_dropview.test.

66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
  set res
}

proc do_dropview_tests {nm args} {
  uplevel do_select_tests $nm $args
}

# EVIDENCE-OF: R-53136-36436 -- syntax diagram drop-view-stmt
#
# All paths in the syntax diagram for DROP VIEW are tested by tests 1.*.
#
do_dropview_tests 1 -repair {
  dropview_reopen_db
} -tclquery {
  list_all_views







|







66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
  set res
}

proc do_dropview_tests {nm args} {
  uplevel do_select_tests $nm $args
}

# -- syntax diagram drop-view-stmt
#
# All paths in the syntax diagram for DROP VIEW are tested by tests 1.*.
#
do_dropview_tests 1 -repair {
  dropview_reopen_db
} -tclquery {
  list_all_views

Changes to test/e_expr.test.

362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
...
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
...
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
...
812
813
814
815
816
817
818
819
820
821
822
823
824
825
826
  string compare [reverse_str $zLeft] [reverse_str $zRight]
}
db collate reverse reverse_collate

# EVIDENCE-OF: R-59577-33471 The COLLATE operator is a unary postfix
# operator that assigns a collating sequence to an expression.
#
# EVIDENCE-OF: R-23441-22541 The COLLATE operator has a higher
# precedence (binds more tightly) than any prefix unary operator or any
# binary operator.
#
do_execsql_test e_expr-9.1 { SELECT  'abcd' < 'bbbb'    COLLATE reverse } 0
do_execsql_test e_expr-9.2 { SELECT ('abcd' < 'bbbb')   COLLATE reverse } 1
do_execsql_test e_expr-9.3 { SELECT  'abcd' <= 'bbbb'   COLLATE reverse } 0
do_execsql_test e_expr-9.4 { SELECT ('abcd' <= 'bbbb')  COLLATE reverse } 1

do_execsql_test e_expr-9.5 { SELECT  'abcd' > 'bbbb'    COLLATE reverse } 1
................................................................................
       [sqlite3_column_type $stmt 3] 
} {NULL NULL NULL NULL}
do_test e_expr-11.7.1 { sqlite3_finalize $stmt } SQLITE_OK

#-------------------------------------------------------------------------
# "Test" the syntax diagrams in lang_expr.html.
#
# EVIDENCE-OF: R-02989-21050 -- syntax diagram signed-number
#
do_execsql_test e_expr-12.1.1 { SELECT 0, +0, -0 } {0 0 0}
do_execsql_test e_expr-12.1.2 { SELECT 1, +1, -1 } {1 1 -1}
do_execsql_test e_expr-12.1.3 { SELECT 2, +2, -2 } {2 2 -2}
do_execsql_test e_expr-12.1.4 { 
  SELECT 1.4, +1.4, -1.4 
} {1.4 1.4 -1.4}
................................................................................
do_execsql_test e_expr-12.1.5 { 
  SELECT 1.5e+5, +1.5e+5, -1.5e+5 
} {150000.0 150000.0 -150000.0}
do_execsql_test e_expr-12.1.6 { 
  SELECT 0.0001, +0.0001, -0.0001 
} {0.0001 0.0001 -0.0001}

# EVIDENCE-OF: R-43188-60852 -- syntax diagram literal-value
#
set sqlite_current_time 1
do_execsql_test e_expr-12.2.1 {SELECT 123}               {123}
do_execsql_test e_expr-12.2.2 {SELECT 123.4e05}          {12340000.0}
do_execsql_test e_expr-12.2.3 {SELECT 'abcde'}           {abcde}
do_execsql_test e_expr-12.2.4 {SELECT X'414243'}         {ABC}
do_execsql_test e_expr-12.2.5 {SELECT NULL}              {{}}
do_execsql_test e_expr-12.2.6 {SELECT CURRENT_TIME}      {00:00:01}
do_execsql_test e_expr-12.2.7 {SELECT CURRENT_DATE}      {1970-01-01}
do_execsql_test e_expr-12.2.8 {SELECT CURRENT_TIMESTAMP} {{1970-01-01 00:00:01}}
set sqlite_current_time 0

# EVIDENCE-OF: R-50544-32159 -- syntax diagram expr
#
forcedelete test.db2
execsql {
  ATTACH 'test.db2' AS dbname;
  CREATE TABLE dbname.tblname(cname);
}

................................................................................
    incr x
    do_test e_expr-12.3.$tn.$x { 
      set rc [catch { execsql "SELECT $e FROM tblname" } msg]
    } {0}
  }
}

# EVIDENCE-OF: R-39820-63916 -- syntax diagram raise-function
#
foreach {tn raiseexpr} {
  1 "RAISE(IGNORE)"
  2 "RAISE(ROLLBACK, 'error message')"
  3 "RAISE(ABORT, 'error message')"
  4 "RAISE(FAIL, 'error message')"
} {







|
|
|







 







|







 







|












|







 







|







362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
...
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
...
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
...
812
813
814
815
816
817
818
819
820
821
822
823
824
825
826
  string compare [reverse_str $zLeft] [reverse_str $zRight]
}
db collate reverse reverse_collate

# EVIDENCE-OF: R-59577-33471 The COLLATE operator is a unary postfix
# operator that assigns a collating sequence to an expression.
#
# EVIDENCE-OF: R-36231-30731 The COLLATE operator has a higher
# precedence (binds more tightly) than any binary operator and any unary
# prefix operator except "~".
#
do_execsql_test e_expr-9.1 { SELECT  'abcd' < 'bbbb'    COLLATE reverse } 0
do_execsql_test e_expr-9.2 { SELECT ('abcd' < 'bbbb')   COLLATE reverse } 1
do_execsql_test e_expr-9.3 { SELECT  'abcd' <= 'bbbb'   COLLATE reverse } 0
do_execsql_test e_expr-9.4 { SELECT ('abcd' <= 'bbbb')  COLLATE reverse } 1

do_execsql_test e_expr-9.5 { SELECT  'abcd' > 'bbbb'    COLLATE reverse } 1
................................................................................
       [sqlite3_column_type $stmt 3] 
} {NULL NULL NULL NULL}
do_test e_expr-11.7.1 { sqlite3_finalize $stmt } SQLITE_OK

#-------------------------------------------------------------------------
# "Test" the syntax diagrams in lang_expr.html.
#
# -- syntax diagram signed-number
#
do_execsql_test e_expr-12.1.1 { SELECT 0, +0, -0 } {0 0 0}
do_execsql_test e_expr-12.1.2 { SELECT 1, +1, -1 } {1 1 -1}
do_execsql_test e_expr-12.1.3 { SELECT 2, +2, -2 } {2 2 -2}
do_execsql_test e_expr-12.1.4 { 
  SELECT 1.4, +1.4, -1.4 
} {1.4 1.4 -1.4}
................................................................................
do_execsql_test e_expr-12.1.5 { 
  SELECT 1.5e+5, +1.5e+5, -1.5e+5 
} {150000.0 150000.0 -150000.0}
do_execsql_test e_expr-12.1.6 { 
  SELECT 0.0001, +0.0001, -0.0001 
} {0.0001 0.0001 -0.0001}

# -- syntax diagram literal-value
#
set sqlite_current_time 1
do_execsql_test e_expr-12.2.1 {SELECT 123}               {123}
do_execsql_test e_expr-12.2.2 {SELECT 123.4e05}          {12340000.0}
do_execsql_test e_expr-12.2.3 {SELECT 'abcde'}           {abcde}
do_execsql_test e_expr-12.2.4 {SELECT X'414243'}         {ABC}
do_execsql_test e_expr-12.2.5 {SELECT NULL}              {{}}
do_execsql_test e_expr-12.2.6 {SELECT CURRENT_TIME}      {00:00:01}
do_execsql_test e_expr-12.2.7 {SELECT CURRENT_DATE}      {1970-01-01}
do_execsql_test e_expr-12.2.8 {SELECT CURRENT_TIMESTAMP} {{1970-01-01 00:00:01}}
set sqlite_current_time 0

# -- syntax diagram expr
#
forcedelete test.db2
execsql {
  ATTACH 'test.db2' AS dbname;
  CREATE TABLE dbname.tblname(cname);
}

................................................................................
    incr x
    do_test e_expr-12.3.$tn.$x { 
      set rc [catch { execsql "SELECT $e FROM tblname" } msg]
    } {0}
  }
}

# -- syntax diagram raise-function
#
foreach {tn raiseexpr} {
  1 "RAISE(IGNORE)"
  2 "RAISE(ROLLBACK, 'error message')"
  3 "RAISE(ABORT, 'error message')"
  4 "RAISE(FAIL, 'error message')"
} {

Changes to test/e_insert.test.

46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
  CREATE TABLE a4(c UNIQUE, d);
} {}

proc do_insert_tests {args} {
  uplevel do_select_tests $args
}

# EVIDENCE-OF: R-21350-31508 -- syntax diagram insert-stmt
#
do_insert_tests e_insert-0 {
     1  "INSERT             INTO a1 DEFAULT VALUES"                   {}
     2  "INSERT             INTO main.a1 DEFAULT VALUES"              {}
     3  "INSERT OR ROLLBACK INTO main.a1 DEFAULT VALUES"              {}
     4  "INSERT OR ROLLBACK INTO a1 DEFAULT VALUES"                   {}
     5  "INSERT OR ABORT    INTO main.a1 DEFAULT VALUES"              {}







|







46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
  CREATE TABLE a4(c UNIQUE, d);
} {}

proc do_insert_tests {args} {
  uplevel do_select_tests $args
}

# -- syntax diagram insert-stmt
#
do_insert_tests e_insert-0 {
     1  "INSERT             INTO a1 DEFAULT VALUES"                   {}
     2  "INSERT             INTO main.a1 DEFAULT VALUES"              {}
     3  "INSERT OR ROLLBACK INTO main.a1 DEFAULT VALUES"              {}
     4  "INSERT OR ROLLBACK INTO a1 DEFAULT VALUES"                   {}
     5  "INSERT OR ABORT    INTO main.a1 DEFAULT VALUES"              {}

Changes to test/e_reindex.test.

22
23
24
25
26
27
28
29
30
31
32
33
34
35
36

do_execsql_test e_reindex-0.0 {
  CREATE TABLE t1(a, b);
  CREATE INDEX i1 ON t1(a, b);
  CREATE INDEX i2 ON t1(b, a);
} {}

# EVIDENCE-OF: R-51477-38549 -- syntax diagram reindex-stmt
#
do_reindex_tests e_reindex-0.1 {
  1   "REINDEX"           {}
  2   "REINDEX nocase"    {}
  3   "REINDEX binary"    {}
  4   "REINDEX t1"        {}
  5   "REINDEX main.t1"   {}







|







22
23
24
25
26
27
28
29
30
31
32
33
34
35
36

do_execsql_test e_reindex-0.0 {
  CREATE TABLE t1(a, b);
  CREATE INDEX i1 ON t1(a, b);
  CREATE INDEX i2 ON t1(b, a);
} {}

#  -- syntax diagram reindex-stmt
#
do_reindex_tests e_reindex-0.1 {
  1   "REINDEX"           {}
  2   "REINDEX nocase"    {}
  3   "REINDEX binary"    {}
  4   "REINDEX t1"        {}
  5   "REINDEX main.t1"   {}

Changes to test/e_select.test.

79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
..
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
...
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
...
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
...
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
....
1222
1223
1224
1225
1226
1227
1228
1229
1230
1231
1232
1233
1234
1235
1236
  }
}

#-------------------------------------------------------------------------
# The following tests check that all paths on the syntax diagrams on
# the lang_select.html page may be taken.
#
# EVIDENCE-OF: R-11353-33501 -- syntax diagram join-constraint
#
do_join_test e_select-0.1.1 {
  SELECT count(*) FROM t1 %JOIN% t2 ON (t1.a=t2.a)
} {3}
do_join_test e_select-0.1.2 {
  SELECT count(*) FROM t1 %JOIN% t2 USING (a)
} {3}
................................................................................
do_catchsql_test e_select-0.1.4 {
  SELECT count(*) FROM t1, t2 ON (t1.a=t2.a) USING (a)
} {1 {cannot have both ON and USING clauses in the same join}}
do_catchsql_test e_select-0.1.5 {
  SELECT count(*) FROM t1, t2 USING (a) ON (t1.a=t2.a)
} {1 {near "ON": syntax error}}

# EVIDENCE-OF: R-40919-40941 -- syntax diagram select-core
#
#   0: SELECT ...
#   1: SELECT DISTINCT ...
#   2: SELECT ALL ...
#
#   0: No FROM clause
#   1: Has FROM clause
................................................................................
    1 a 1 c
  }
  2112.2  "SELECT ALL count(*), max(a) FROM t1 
           WHERE 0 GROUP BY b HAVING count(*)=2" { }
}


# EVIDENCE-OF: R-41378-26734 -- syntax diagram result-column
#
do_select_tests e_select-0.3 {
  1  "SELECT * FROM t1" {a one b two c three}
  2  "SELECT t1.* FROM t1" {a one b two c three}
  3  "SELECT 'x'||a||'x' FROM t1" {xax xbx xcx}
  4  "SELECT 'x'||a||'x' alias FROM t1" {xax xbx xcx}
  5  "SELECT 'x'||a||'x' AS alias FROM t1" {xax xbx xcx}
}

# EVIDENCE-OF: R-43129-35648 -- syntax diagram join-source
#
# EVIDENCE-OF: R-36683-37460 -- syntax diagram join-op
#
do_select_tests e_select-0.4 {
  1  "SELECT t1.rowid FROM t1" {1 2 3}
  2  "SELECT t1.rowid FROM t1,t2" {1 1 1 2 2 2 3 3 3}
  3  "SELECT t1.rowid FROM t1,t2,t3" {1 1 1 1 1 1 2 2 2 2 2 2 3 3 3 3 3 3}

  4  "SELECT t1.rowid FROM t1" {1 2 3}
................................................................................
  12 "SELECT t1.rowid FROM t1 JOIN t3" {1 1 2 2 3 3}
  13 "SELECT t1.rowid FROM t1 LEFT OUTER JOIN t3" {1 1 2 2 3 3}
  14 "SELECT t1.rowid FROM t1 LEFT JOIN t3" {1 1 2 2 3 3}
  15 "SELECT t1.rowid FROM t1 INNER JOIN t3" {1 1 2 2 3 3}
  16 "SELECT t1.rowid FROM t1 CROSS JOIN t3" {1 1 2 2 3 3}
}

# EVIDENCE-OF: R-28308-37813 -- syntax diagram compound-operator
#
do_select_tests e_select-0.5 {
  1  "SELECT rowid FROM t1 UNION ALL SELECT rowid+2 FROM t4" {1 2 3 3 4}
  2  "SELECT rowid FROM t1 UNION     SELECT rowid+2 FROM t4" {1 2 3 4}
  3  "SELECT rowid FROM t1 INTERSECT SELECT rowid+2 FROM t4" {3}
  4  "SELECT rowid FROM t1 EXCEPT    SELECT rowid+2 FROM t4" {1 2}
}

# EVIDENCE-OF: R-06480-34950 -- syntax diagram ordering-term
#
do_select_tests e_select-0.6 {
  1  "SELECT b||a FROM t1 ORDER BY b||a"                  {onea threec twob}
  2  "SELECT b||a FROM t1 ORDER BY (b||a) COLLATE nocase" {onea threec twob}
  3  "SELECT b||a FROM t1 ORDER BY (b||a) ASC"            {onea threec twob}
  4  "SELECT b||a FROM t1 ORDER BY (b||a) DESC"           {twob threec onea}
}

# EVIDENCE-OF: R-23926-36668 -- syntax diagram select-stmt
#
do_select_tests e_select-0.7 {
  1  "SELECT * FROM t1" {a one b two c three}
  2  "SELECT * FROM t1 ORDER BY b" {a one c three b two}
  3  "SELECT * FROM t1 ORDER BY b, a" {a one c three b two}

  4  "SELECT * FROM t1 LIMIT 10" {a one b two c three}
................................................................................
#    The tests are built on this assertion. Really, they test that the output
#    of a CROSS JOIN, JOIN, INNER JOIN or "," join matches the expected result
#    of calculating the cartesian product of the left and right-hand datasets. 
#
# EVIDENCE-OF: R-46256-57243 There is no difference between the "INNER
# JOIN", "JOIN" and "," join operators.
#
# EVIDENCE-OF: R-07544-24155 The "CROSS JOIN" join operator produces the
# same data as the "INNER JOIN", "JOIN" and "," operators
#
#    All tests are run 4 times, with the only difference in each run being
#    which of the 4 equivalent cartesian product join operators are used.
#    Since the output data is the same in all cases, we consider that this
#    qualifies as testing the two statements above.
#
do_execsql_test e_select-1.4.0 {
................................................................................
  1   "SELECT ALL a FROM h1"      {1 1 1 4 4 4}
  2   "SELECT DISTINCT a FROM h1" {1 4}
}

# EVIDENCE-OF: R-08861-34280 If the simple SELECT is a SELECT ALL, then
# the entire set of result rows are returned by the SELECT.
#
# EVIDENCE-OF: R-47911-02086 If neither ALL or DISTINCT are present,
# then the behavior is as if ALL were specified.
#
# EVIDENCE-OF: R-14442-41305 If the simple SELECT is a SELECT DISTINCT,
# then duplicate rows are removed from the set of result rows before it
# is returned.
#
#   The three testable statements above are tested by e_select-5.2.*,







|







 







|







 







|









|

|







 







|








|








|







 







|
|







 







|







79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
..
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
...
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
...
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
...
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
....
1222
1223
1224
1225
1226
1227
1228
1229
1230
1231
1232
1233
1234
1235
1236
  }
}

#-------------------------------------------------------------------------
# The following tests check that all paths on the syntax diagrams on
# the lang_select.html page may be taken.
#
# -- syntax diagram join-constraint
#
do_join_test e_select-0.1.1 {
  SELECT count(*) FROM t1 %JOIN% t2 ON (t1.a=t2.a)
} {3}
do_join_test e_select-0.1.2 {
  SELECT count(*) FROM t1 %JOIN% t2 USING (a)
} {3}
................................................................................
do_catchsql_test e_select-0.1.4 {
  SELECT count(*) FROM t1, t2 ON (t1.a=t2.a) USING (a)
} {1 {cannot have both ON and USING clauses in the same join}}
do_catchsql_test e_select-0.1.5 {
  SELECT count(*) FROM t1, t2 USING (a) ON (t1.a=t2.a)
} {1 {near "ON": syntax error}}

# -- syntax diagram select-core
#
#   0: SELECT ...
#   1: SELECT DISTINCT ...
#   2: SELECT ALL ...
#
#   0: No FROM clause
#   1: Has FROM clause
................................................................................
    1 a 1 c
  }
  2112.2  "SELECT ALL count(*), max(a) FROM t1 
           WHERE 0 GROUP BY b HAVING count(*)=2" { }
}


# -- syntax diagram result-column
#
do_select_tests e_select-0.3 {
  1  "SELECT * FROM t1" {a one b two c three}
  2  "SELECT t1.* FROM t1" {a one b two c three}
  3  "SELECT 'x'||a||'x' FROM t1" {xax xbx xcx}
  4  "SELECT 'x'||a||'x' alias FROM t1" {xax xbx xcx}
  5  "SELECT 'x'||a||'x' AS alias FROM t1" {xax xbx xcx}
}

# -- syntax diagram join-source
#
# -- syntax diagram join-op
#
do_select_tests e_select-0.4 {
  1  "SELECT t1.rowid FROM t1" {1 2 3}
  2  "SELECT t1.rowid FROM t1,t2" {1 1 1 2 2 2 3 3 3}
  3  "SELECT t1.rowid FROM t1,t2,t3" {1 1 1 1 1 1 2 2 2 2 2 2 3 3 3 3 3 3}

  4  "SELECT t1.rowid FROM t1" {1 2 3}
................................................................................
  12 "SELECT t1.rowid FROM t1 JOIN t3" {1 1 2 2 3 3}
  13 "SELECT t1.rowid FROM t1 LEFT OUTER JOIN t3" {1 1 2 2 3 3}
  14 "SELECT t1.rowid FROM t1 LEFT JOIN t3" {1 1 2 2 3 3}
  15 "SELECT t1.rowid FROM t1 INNER JOIN t3" {1 1 2 2 3 3}
  16 "SELECT t1.rowid FROM t1 CROSS JOIN t3" {1 1 2 2 3 3}
}

# -- syntax diagram compound-operator
#
do_select_tests e_select-0.5 {
  1  "SELECT rowid FROM t1 UNION ALL SELECT rowid+2 FROM t4" {1 2 3 3 4}
  2  "SELECT rowid FROM t1 UNION     SELECT rowid+2 FROM t4" {1 2 3 4}
  3  "SELECT rowid FROM t1 INTERSECT SELECT rowid+2 FROM t4" {3}
  4  "SELECT rowid FROM t1 EXCEPT    SELECT rowid+2 FROM t4" {1 2}
}

# -- syntax diagram ordering-term
#
do_select_tests e_select-0.6 {
  1  "SELECT b||a FROM t1 ORDER BY b||a"                  {onea threec twob}
  2  "SELECT b||a FROM t1 ORDER BY (b||a) COLLATE nocase" {onea threec twob}
  3  "SELECT b||a FROM t1 ORDER BY (b||a) ASC"            {onea threec twob}
  4  "SELECT b||a FROM t1 ORDER BY (b||a) DESC"           {twob threec onea}
}

# -- syntax diagram select-stmt
#
do_select_tests e_select-0.7 {
  1  "SELECT * FROM t1" {a one b two c three}
  2  "SELECT * FROM t1 ORDER BY b" {a one c three b two}
  3  "SELECT * FROM t1 ORDER BY b, a" {a one c three b two}

  4  "SELECT * FROM t1 LIMIT 10" {a one b two c three}
................................................................................
#    The tests are built on this assertion. Really, they test that the output
#    of a CROSS JOIN, JOIN, INNER JOIN or "," join matches the expected result
#    of calculating the cartesian product of the left and right-hand datasets. 
#
# EVIDENCE-OF: R-46256-57243 There is no difference between the "INNER
# JOIN", "JOIN" and "," join operators.
#
# EVIDENCE-OF: R-25071-21202 The "CROSS JOIN" join operator produces the
# same result as the "INNER JOIN", "JOIN" and "," operators
#
#    All tests are run 4 times, with the only difference in each run being
#    which of the 4 equivalent cartesian product join operators are used.
#    Since the output data is the same in all cases, we consider that this
#    qualifies as testing the two statements above.
#
do_execsql_test e_select-1.4.0 {
................................................................................
  1   "SELECT ALL a FROM h1"      {1 1 1 4 4 4}
  2   "SELECT DISTINCT a FROM h1" {1 4}
}

# EVIDENCE-OF: R-08861-34280 If the simple SELECT is a SELECT ALL, then
# the entire set of result rows are returned by the SELECT.
#
# EVIDENCE-OF: R-01256-01950 If neither ALL or DISTINCT are present,
# then the behavior is as if ALL were specified.
#
# EVIDENCE-OF: R-14442-41305 If the simple SELECT is a SELECT DISTINCT,
# then duplicate rows are removed from the set of result rows before it
# is returned.
#
#   The three testable statements above are tested by e_select-5.2.*,

Changes to test/e_select2.test.

348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
  # JOIN", "JOIN" or a comma (",") and there is no ON or USING clause,
  # then the result of the join is simply the cartesian product of the
  # left and right-hand datasets.
  #
  # EVIDENCE-OF: R-46256-57243 There is no difference between the "INNER
  # JOIN", "JOIN" and "," join operators.
  #
  # EVIDENCE-OF: R-07544-24155 The "CROSS JOIN" join operator produces the
  # same data as the "INNER JOIN", "JOIN" and "," operators
  #
  test_join $tn.1.1  "t1, t2"                {t1 t2}
  test_join $tn.1.2  "t1 INNER JOIN t2"      {t1 t2}
  test_join $tn.1.3  "t1 CROSS JOIN t2"      {t1 t2}
  test_join $tn.1.4  "t1 JOIN t2"            {t1 t2}
  test_join $tn.1.5  "t2, t3"                {t2 t3}
  test_join $tn.1.6  "t2 INNER JOIN t3"      {t2 t3}







|
|







348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
  # JOIN", "JOIN" or a comma (",") and there is no ON or USING clause,
  # then the result of the join is simply the cartesian product of the
  # left and right-hand datasets.
  #
  # EVIDENCE-OF: R-46256-57243 There is no difference between the "INNER
  # JOIN", "JOIN" and "," join operators.
  #
  # EVIDENCE-OF: R-25071-21202 The "CROSS JOIN" join operator produces the
  # same result as the "INNER JOIN", "JOIN" and "," operators
  #
  test_join $tn.1.1  "t1, t2"                {t1 t2}
  test_join $tn.1.2  "t1 INNER JOIN t2"      {t1 t2}
  test_join $tn.1.3  "t1 CROSS JOIN t2"      {t1 t2}
  test_join $tn.1.4  "t1 JOIN t2"            {t1 t2}
  test_join $tn.1.5  "t2, t3"                {t2 t3}
  test_join $tn.1.6  "t2 INNER JOIN t3"      {t2 t3}

Changes to test/e_update.test.

45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
...
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
  CREATE TABLE aux.t5(a, b);
} {}

proc do_update_tests {args} {
  uplevel do_select_tests $args
}

# EVIDENCE-OF: R-62337-45828 -- syntax diagram update-stmt
#
do_update_tests e_update-0 {
  1    "UPDATE t1 SET a=10" {}
  2    "UPDATE t1 SET a=10, b=5" {}
  3    "UPDATE t1 SET a=10 WHERE b=5" {}
  4    "UPDATE t1 SET b=5,a=10 WHERE 1" {}
  5    "UPDATE main.t1 SET a=10" {}
................................................................................
}

# EVIDENCE-OF: R-59581-44104 If SQLite is built with the
# SQLITE_ENABLE_UPDATE_DELETE_LIMIT compile-time option then the syntax
# of the UPDATE statement is extended with optional ORDER BY and LIMIT
# clauses
#
# EVIDENCE-OF: R-45169-39597 -- syntax diagram update-stmt-limited
#
do_update_tests e_update-3.0 {
  1   "UPDATE t1 SET a=b LIMIT 5"                                    {}
  2   "UPDATE t1 SET a=b LIMIT 5-1 OFFSET 2+2"                       {}
  3   "UPDATE t1 SET a=b LIMIT 2+2, 16/4"                            {}
  4   "UPDATE t1 SET a=b ORDER BY a LIMIT 5"                         {}
  5   "UPDATE t1 SET a=b ORDER BY a LIMIT 5-1 OFFSET 2+2"            {}







|







 







|







45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
...
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
  CREATE TABLE aux.t5(a, b);
} {}

proc do_update_tests {args} {
  uplevel do_select_tests $args
}

# -- syntax diagram update-stmt
#
do_update_tests e_update-0 {
  1    "UPDATE t1 SET a=10" {}
  2    "UPDATE t1 SET a=10, b=5" {}
  3    "UPDATE t1 SET a=10 WHERE b=5" {}
  4    "UPDATE t1 SET b=5,a=10 WHERE 1" {}
  5    "UPDATE main.t1 SET a=10" {}
................................................................................
}

# EVIDENCE-OF: R-59581-44104 If SQLite is built with the
# SQLITE_ENABLE_UPDATE_DELETE_LIMIT compile-time option then the syntax
# of the UPDATE statement is extended with optional ORDER BY and LIMIT
# clauses
#
# -- syntax diagram update-stmt-limited
#
do_update_tests e_update-3.0 {
  1   "UPDATE t1 SET a=b LIMIT 5"                                    {}
  2   "UPDATE t1 SET a=b LIMIT 5-1 OFFSET 2+2"                       {}
  3   "UPDATE t1 SET a=b LIMIT 2+2, 16/4"                            {}
  4   "UPDATE t1 SET a=b ORDER BY a LIMIT 5"                         {}
  5   "UPDATE t1 SET a=b ORDER BY a LIMIT 5-1 OFFSET 2+2"            {}

Changes to test/e_uri.test.

355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
# EVIDENCE-OF: R-23027-03515 Setting it to "shared" is equivalent to
# setting the SQLITE_OPEN_SHAREDCACHE bit in the flags argument passed
# to sqlite3_open_v2().
#
# EVIDENCE-OF: R-49793-28525 Setting the cache parameter to "private" is
# equivalent to setting the SQLITE_OPEN_PRIVATECACHE bit.
#
# EVIDENCE-OF: R-19510-48080 If sqlite3_open_v2() is used and the
# "cache" parameter is present in a URI filename, its value overrides
# any behavior requested by setting SQLITE_OPEN_PRIVATECACHE or
# SQLITE_OPEN_SHAREDCACHE flag.
#
set orig [sqlite3_enable_shared_cache]
foreach {tn uri flags shared_default isshared} {
  1.1   "file:test.db"                  ""         0    0







|







355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
# EVIDENCE-OF: R-23027-03515 Setting it to "shared" is equivalent to
# setting the SQLITE_OPEN_SHAREDCACHE bit in the flags argument passed
# to sqlite3_open_v2().
#
# EVIDENCE-OF: R-49793-28525 Setting the cache parameter to "private" is
# equivalent to setting the SQLITE_OPEN_PRIVATECACHE bit.
#
# EVIDENCE-OF: R-31773-41793 If sqlite3_open_v2() is used and the
# "cache" parameter is present in a URI filename, its value overrides
# any behavior requested by setting SQLITE_OPEN_PRIVATECACHE or
# SQLITE_OPEN_SHAREDCACHE flag.
#
set orig [sqlite3_enable_shared_cache]
foreach {tn uri flags shared_default isshared} {
  1.1   "file:test.db"                  ""         0    0

Changes to test/e_vacuum.test.

61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
    set prevpageno $pageno
  }
  execsql { DROP TABLE temp.stat }
  set nFrag
}


# EVIDENCE-OF: R-45173-45977 -- syntax diagram vacuum-stmt
#
do_execsql_test e_vacuum-0.1 { VACUUM } {}

# EVIDENCE-OF: R-51469-36013 Unless SQLite is running in
# "auto_vacuum=FULL" mode, when a large amount of data is deleted from
# the database file it leaves behind empty space, or "free" database
# pages.







|







61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
    set prevpageno $pageno
  }
  execsql { DROP TABLE temp.stat }
  set nFrag
}


# -- syntax diagram vacuum-stmt
#
do_execsql_test e_vacuum-0.1 { VACUUM } {}

# EVIDENCE-OF: R-51469-36013 Unless SQLite is running in
# "auto_vacuum=FULL" mode, when a large amount of data is deleted from
# the database file it leaves behind empty space, or "free" database
# pages.

Changes to test/eqp.test.

366
367
368
369
370
371
372
373
374


375
376
377
378
379
380
381
382

383
384
385
386
387
388
389
390

391
392
393
394
395
396
397
398

399
400
401
402
403
404
405
406
407
408

409
410
411
412
413
414
415
416
417
418

419
420
421
422
423
424
425

426
427

428
429
430
431
432
433
434
435

436
437
438
439
440
441
442

443
444
445
446

447
448
449
450
451
452
453
454
455
456
457
458
459

460

461
462
463
464
465
466
467
468
469

470
471

472
473
474
475
476
477

478
479
480

481
482
483
484
485
486
487
488
489
490
491

492
493
494
495
496
497
498

#-------------------------------------------------------------------------
# This next block of tests verifies that the examples on the 
# lang_explain.html page are correct.
#
drop_all_tables

# EVIDENCE-OF: R-64208-08323 sqlite> EXPLAIN QUERY PLAN SELECT a, b
# FROM t1 WHERE a=1; 0|0|0|SCAN TABLE t1


do_execsql_test 5.1.0 { CREATE TABLE t1(a, b) }
det 5.1.1 "SELECT a, b FROM t1 WHERE a=1" {
  0 0 0 {SCAN TABLE t1}
}

# EVIDENCE-OF: R-09022-44606 sqlite> CREATE INDEX i1 ON t1(a);
# sqlite> EXPLAIN QUERY PLAN SELECT a, b FROM t1 WHERE a=1;
# 0|0|0|SEARCH TABLE t1 USING INDEX i1 (a=?)

do_execsql_test 5.2.0 { CREATE INDEX i1 ON t1(a) }
det 5.2.1 "SELECT a, b FROM t1 WHERE a=1" {
  0 0 0 {SEARCH TABLE t1 USING INDEX i1 (a=?)}
}

# EVIDENCE-OF: R-62228-34103 sqlite> CREATE INDEX i2 ON t1(a, b);
# sqlite> EXPLAIN QUERY PLAN SELECT a, b FROM t1 WHERE a=1;
# 0|0|0|SEARCH TABLE t1 USING COVERING INDEX i2 (a=?)

do_execsql_test 5.3.0 { CREATE INDEX i2 ON t1(a, b) }
det 5.3.1 "SELECT a, b FROM t1 WHERE a=1" {
  0 0 0 {SEARCH TABLE t1 USING COVERING INDEX i2 (a=?)}
}

# EVIDENCE-OF: R-22253-05302 sqlite> EXPLAIN QUERY PLAN SELECT t1.*,
# t2.* FROM t1, t2 WHERE t1.a=1 AND t1.b>2; 0|0|0|SEARCH TABLE t1
# USING COVERING INDEX i2 (a=? AND b>?) 0|1|1|SCAN TABLE t2

#
do_execsql_test 5.4.0 {CREATE TABLE t2(c, d)}
det 5.4.1 "SELECT t1.*, t2.* FROM t1, t2 WHERE t1.a=1 AND t1.b>2" {
  0 0 0 {SEARCH TABLE t1 USING COVERING INDEX i2 (a=? AND b>?)}
  0 1 1 {SCAN TABLE t2}
}

# EVIDENCE-OF: R-21040-07025 sqlite> EXPLAIN QUERY PLAN SELECT t1.*,
# t2.* FROM t2, t1 WHERE t1.a=1 AND t1.b>2; 0|0|1|SEARCH TABLE t1
# USING COVERING INDEX i2 (a=? AND b>?) 0|1|0|SCAN TABLE t2

#
det 5.5 "SELECT t1.*, t2.* FROM t2, t1 WHERE t1.a=1 AND t1.b>2" {
  0 0 1 {SEARCH TABLE t1 USING COVERING INDEX i2 (a=? AND b>?)}
  0 1 0 {SCAN TABLE t2}
}

# EVIDENCE-OF: R-39007-61103 sqlite> CREATE INDEX i3 ON t1(b);
# sqlite> EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE a=1 OR b=2;
# 0|0|0|SEARCH TABLE t1 USING COVERING INDEX i2 (a=?)
# 0|0|0|SEARCH TABLE t1 USING INDEX i3 (b=?)

do_execsql_test 5.5.0 {CREATE INDEX i3 ON t1(b)}
det 5.6.1 "SELECT * FROM t1 WHERE a=1 OR b=2" {
  0 0 0 {SEARCH TABLE t1 USING COVERING INDEX i2 (a=?)}
  0 0 0 {SEARCH TABLE t1 USING INDEX i3 (b=?)}
}

# EVIDENCE-OF: R-33025-54904 sqlite> EXPLAIN QUERY PLAN SELECT c, d

# FROM t2 ORDER BY c; 0|0|0|SCAN TABLE t2 0|0|0|USE TEMP
# B-TREE FOR ORDER BY

det 5.7 "SELECT c, d FROM t2 ORDER BY c" {
  0 0 0 {SCAN TABLE t2}
  0 0 0 {USE TEMP B-TREE FOR ORDER BY}
}

# EVIDENCE-OF: R-38854-22809 sqlite> CREATE INDEX i4 ON t2(c);
# sqlite> EXPLAIN QUERY PLAN SELECT c, d FROM t2 ORDER BY c;
# 0|0|0|SCAN TABLE t2 USING INDEX i4

do_execsql_test 5.8.0 {CREATE INDEX i4 ON t2(c)}
det 5.8.1 "SELECT c, d FROM t2 ORDER BY c" {
  0 0 0 {SCAN TABLE t2 USING INDEX i4}
}

# EVIDENCE-OF: R-29884-43993 sqlite> EXPLAIN QUERY PLAN SELECT
# (SELECT b FROM t1 WHERE a=0), (SELECT a FROM t1 WHERE b=t2.c) FROM t2;

# 0|0|0|SCAN TABLE t2 0|0|0|EXECUTE SCALAR SUBQUERY 1
# 1|0|0|SEARCH TABLE t1 USING COVERING INDEX i2 (a=?)
# 0|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 2 2|0|0|SEARCH TABLE t1 USING
# INDEX i3 (b=?)

det 5.9 {
  SELECT (SELECT b FROM t1 WHERE a=0), (SELECT a FROM t1 WHERE b=t2.c) FROM t2
} {
  0 0 0 {SCAN TABLE t2 USING COVERING INDEX i4}
  0 0 0 {EXECUTE SCALAR SUBQUERY 1}
  1 0 0 {SEARCH TABLE t1 USING COVERING INDEX i2 (a=?)}
  0 0 0 {EXECUTE CORRELATED SCALAR SUBQUERY 2}
  2 0 0 {SEARCH TABLE t1 USING INDEX i3 (b=?)}
}

# EVIDENCE-OF: R-17911-16445 sqlite> EXPLAIN QUERY PLAN SELECT
# count(*) FROM (SELECT max(b) AS x FROM t1 GROUP BY a) GROUP BY x;
# 1|0|0|SCAN TABLE t1 USING COVERING INDEX i2 0|0|0|SCAN

# SUBQUERY 1 0|0|0|USE TEMP B-TREE FOR GROUP BY

det 5.10 {
  SELECT count(*) FROM (SELECT max(b) AS x FROM t1 GROUP BY a) GROUP BY x
} {
  1 0 0 {SCAN TABLE t1 USING COVERING INDEX i2}
  0 0 0 {SCAN SUBQUERY 1}
  0 0 0 {USE TEMP B-TREE FOR GROUP BY}
}

# EVIDENCE-OF: R-18544-33103 sqlite> EXPLAIN QUERY PLAN SELECT * FROM

# (SELECT * FROM t2 WHERE c=1), t1; 0|0|0|SEARCH TABLE t2 USING INDEX i4
# (c=?) 0|1|1|SCAN TABLE t1

det 5.11 "SELECT * FROM (SELECT * FROM t2 WHERE c=1), t1" {
  0 0 0 {SEARCH TABLE t2 USING INDEX i4 (c=?)}
  0 1 1 {SCAN TABLE t1 USING COVERING INDEX i2}
}

# EVIDENCE-OF: R-40701-42164 sqlite> EXPLAIN QUERY PLAN SELECT a FROM

# t1 UNION SELECT c FROM t2; 1|0|0|SCAN TABLE t1
# 2|0|0|SCAN TABLE t2 0|0|0|COMPOUND SUBQUERIES 1 AND 2
# USING TEMP B-TREE (UNION)

det 5.12 "SELECT a FROM t1 UNION SELECT c FROM t2" {
  1 0 0 {SCAN TABLE t1 USING COVERING INDEX i2}
  2 0 0 {SCAN TABLE t2 USING COVERING INDEX i4}
  0 0 0 {COMPOUND SUBQUERIES 1 AND 2 USING TEMP B-TREE (UNION)}
}

# EVIDENCE-OF: R-61538-24748 sqlite> EXPLAIN QUERY PLAN SELECT a FROM
# t1 EXCEPT SELECT d FROM t2 ORDER BY 1; 1|0|0|SCAN TABLE t1 USING
# COVERING INDEX i2 2|0|0|SCAN TABLE t2
# 2|0|0|USE TEMP B-TREE FOR ORDER BY 0|0|0|COMPOUND SUBQUERIES 1 AND 2
# (EXCEPT)

det 5.13 "SELECT a FROM t1 EXCEPT SELECT d FROM t2 ORDER BY 1" {
  1 0 0 {SCAN TABLE t1 USING COVERING INDEX i2}
  2 0 0 {SCAN TABLE t2}
  2 0 0 {USE TEMP B-TREE FOR ORDER BY}
  0 0 0 {COMPOUND SUBQUERIES 1 AND 2 (EXCEPT)}
}








|
|
>
>





|

|
>





|


>





|
|
|
>







|
|
|
>






|



>






|
>
|
|
>





|


>





|

>
|

|
|
>










|
|
|
>
|
>








|
>
|
|
>





|
>
|
|
|
>






|
|
|
|
|
>







366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517

#-------------------------------------------------------------------------
# This next block of tests verifies that the examples on the 
# lang_explain.html page are correct.
#
drop_all_tables

# EVIDENCE-OF: R-47779-47605 sqlite> EXPLAIN QUERY PLAN SELECT a, b
# FROM t1 WHERE a=1;
# 0|0|0|SCAN TABLE t1
#
do_execsql_test 5.1.0 { CREATE TABLE t1(a, b) }
det 5.1.1 "SELECT a, b FROM t1 WHERE a=1" {
  0 0 0 {SCAN TABLE t1}
}

# EVIDENCE-OF: R-55852-17599 sqlite> CREATE INDEX i1 ON t1(a);
# sqlite> EXPLAIN QUERY PLAN SELECT a, b FROM t1 WHERE a=1;
# 0|0|0|SEARCH TABLE t1 USING INDEX i1
#
do_execsql_test 5.2.0 { CREATE INDEX i1 ON t1(a) }
det 5.2.1 "SELECT a, b FROM t1 WHERE a=1" {
  0 0 0 {SEARCH TABLE t1 USING INDEX i1 (a=?)}
}

# EVIDENCE-OF: R-21179-11011 sqlite> CREATE INDEX i2 ON t1(a, b);
# sqlite> EXPLAIN QUERY PLAN SELECT a, b FROM t1 WHERE a=1;
# 0|0|0|SEARCH TABLE t1 USING COVERING INDEX i2 (a=?)
#
do_execsql_test 5.3.0 { CREATE INDEX i2 ON t1(a, b) }
det 5.3.1 "SELECT a, b FROM t1 WHERE a=1" {
  0 0 0 {SEARCH TABLE t1 USING COVERING INDEX i2 (a=?)}
}

# EVIDENCE-OF: R-09991-48941 sqlite> EXPLAIN QUERY PLAN
# SELECT t1.*, t2.* FROM t1, t2 WHERE t1.a=1 AND t1.b>2;
# 0|0|0|SEARCH TABLE t1 USING COVERING INDEX i2 (a=? AND b>?)
# 0|1|1|SCAN TABLE t2
#
do_execsql_test 5.4.0 {CREATE TABLE t2(c, d)}
det 5.4.1 "SELECT t1.*, t2.* FROM t1, t2 WHERE t1.a=1 AND t1.b>2" {
  0 0 0 {SEARCH TABLE t1 USING COVERING INDEX i2 (a=? AND b>?)}
  0 1 1 {SCAN TABLE t2}
}

# EVIDENCE-OF: R-33626-61085 sqlite> EXPLAIN QUERY PLAN
# SELECT t1.*, t2.* FROM t2, t1 WHERE t1.a=1 AND t1.b>2;
# 0|0|1|SEARCH TABLE t1 USING COVERING INDEX i2 (a=? AND b>?)
# 0|1|0|SCAN TABLE t2
#
det 5.5 "SELECT t1.*, t2.* FROM t2, t1 WHERE t1.a=1 AND t1.b>2" {
  0 0 1 {SEARCH TABLE t1 USING COVERING INDEX i2 (a=? AND b>?)}
  0 1 0 {SCAN TABLE t2}
}

# EVIDENCE-OF: R-04002-25654 sqlite> CREATE INDEX i3 ON t1(b);
# sqlite> EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE a=1 OR b=2;
# 0|0|0|SEARCH TABLE t1 USING COVERING INDEX i2 (a=?)
# 0|0|0|SEARCH TABLE t1 USING INDEX i3 (b=?)
#
do_execsql_test 5.5.0 {CREATE INDEX i3 ON t1(b)}
det 5.6.1 "SELECT * FROM t1 WHERE a=1 OR b=2" {
  0 0 0 {SEARCH TABLE t1 USING COVERING INDEX i2 (a=?)}
  0 0 0 {SEARCH TABLE t1 USING INDEX i3 (b=?)}
}

# EVIDENCE-OF: R-24577-38891 sqlite> EXPLAIN QUERY PLAN
# SELECT c, d FROM t2 ORDER BY c;
# 0|0|0|SCAN TABLE t2
# 0|0|0|USE TEMP B-TREE FOR ORDER BY
#
det 5.7 "SELECT c, d FROM t2 ORDER BY c" {
  0 0 0 {SCAN TABLE t2}
  0 0 0 {USE TEMP B-TREE FOR ORDER BY}
}

# EVIDENCE-OF: R-58157-12355 sqlite> CREATE INDEX i4 ON t2(c);
# sqlite> EXPLAIN QUERY PLAN SELECT c, d FROM t2 ORDER BY c;
# 0|0|0|SCAN TABLE t2 USING INDEX i4
#
do_execsql_test 5.8.0 {CREATE INDEX i4 ON t2(c)}
det 5.8.1 "SELECT c, d FROM t2 ORDER BY c" {
  0 0 0 {SCAN TABLE t2 USING INDEX i4}
}

# EVIDENCE-OF: R-13931-10421 sqlite> EXPLAIN QUERY PLAN SELECT
# (SELECT b FROM t1 WHERE a=0), (SELECT a FROM t1 WHERE b=t2.c) FROM t2;
# 0|0|0|SCAN TABLE t2
# 0|0|0|EXECUTE SCALAR SUBQUERY 1
# 1|0|0|SEARCH TABLE t1 USING COVERING INDEX i2 (a=?)
# 0|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 2
# 2|0|0|SEARCH TABLE t1 USING INDEX i3 (b=?)
#
det 5.9 {
  SELECT (SELECT b FROM t1 WHERE a=0), (SELECT a FROM t1 WHERE b=t2.c) FROM t2
} {
  0 0 0 {SCAN TABLE t2 USING COVERING INDEX i4}
  0 0 0 {EXECUTE SCALAR SUBQUERY 1}
  1 0 0 {SEARCH TABLE t1 USING COVERING INDEX i2 (a=?)}
  0 0 0 {EXECUTE CORRELATED SCALAR SUBQUERY 2}
  2 0 0 {SEARCH TABLE t1 USING INDEX i3 (b=?)}
}

# EVIDENCE-OF: R-50892-45943 sqlite> EXPLAIN QUERY PLAN
# SELECT count(*) FROM (SELECT max(b) AS x FROM t1 GROUP BY a) GROUP BY x;
# 1|0|0|SCAN TABLE t1 USING COVERING INDEX i2
# 0|0|0|SCAN SUBQUERY 1
# 0|0|0|USE TEMP B-TREE FOR GROUP BY
#
det 5.10 {
  SELECT count(*) FROM (SELECT max(b) AS x FROM t1 GROUP BY a) GROUP BY x
} {
  1 0 0 {SCAN TABLE t1 USING COVERING INDEX i2}
  0 0 0 {SCAN SUBQUERY 1}
  0 0 0 {USE TEMP B-TREE FOR GROUP BY}
}

# EVIDENCE-OF: R-46219-33846 sqlite> EXPLAIN QUERY PLAN
# SELECT * FROM (SELECT * FROM t2 WHERE c=1), t1;
# 0|0|0|SEARCH TABLE t2 USING INDEX i4 (c=?)
# 0|1|1|SCAN TABLE t1
#
det 5.11 "SELECT * FROM (SELECT * FROM t2 WHERE c=1), t1" {
  0 0 0 {SEARCH TABLE t2 USING INDEX i4 (c=?)}
  0 1 1 {SCAN TABLE t1 USING COVERING INDEX i2}
}

# EVIDENCE-OF: R-37879-39987 sqlite> EXPLAIN QUERY PLAN
# SELECT a FROM t1 UNION SELECT c FROM t2;
# 1|0|0|SCAN TABLE t1
# 2|0|0|SCAN TABLE t2
# 0|0|0|COMPOUND SUBQUERIES 1 AND 2 USING TEMP B-TREE (UNION)
#
det 5.12 "SELECT a FROM t1 UNION SELECT c FROM t2" {
  1 0 0 {SCAN TABLE t1 USING COVERING INDEX i2}
  2 0 0 {SCAN TABLE t2 USING COVERING INDEX i4}
  0 0 0 {COMPOUND SUBQUERIES 1 AND 2 USING TEMP B-TREE (UNION)}
}

# EVIDENCE-OF: R-44864-63011 sqlite> EXPLAIN QUERY PLAN
# SELECT a FROM t1 EXCEPT SELECT d FROM t2 ORDER BY 1;
# 1|0|0|SCAN TABLE t1 USING COVERING INDEX i2
# 2|0|0|SCAN TABLE t2 2|0|0|USE TEMP B-TREE FOR ORDER BY
# 0|0|0|COMPOUND SUBQUERIES 1 AND 2 (EXCEPT)
#
det 5.13 "SELECT a FROM t1 EXCEPT SELECT d FROM t2 ORDER BY 1" {
  1 0 0 {SCAN TABLE t1 USING COVERING INDEX i2}
  2 0 0 {SCAN TABLE t2}
  2 0 0 {USE TEMP B-TREE FOR ORDER BY}
  0 0 0 {COMPOUND SUBQUERIES 1 AND 2 (EXCEPT)}
}