/ Check-in [9d2b0af2]
Login

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

Overview
Comment:Fix problems in the backport, reducing the number of errors in the TCL tests to just a few dozen. Most of the remaining errors seem to be real and desirable changes of behavior.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | branch-3.7.2
Files: files | file ages | folders
SHA1:9d2b0af266b85f10823e54ca6417e76950c1d531
User & Date: drh 2011-02-12 05:34:43
Context
2011-02-12
14:23
Fix the expected output on tests so that it corresponds to the new query planner results. All of veryquick.test is now passing with SQLITE_ENABLE_STAT2. check-in: f2a8b5cc user: drh tags: branch-3.7.2
05:34
Fix problems in the backport, reducing the number of errors in the TCL tests to just a few dozen. Most of the remaining errors seem to be real and desirable changes of behavior. check-in: 9d2b0af2 user: drh tags: branch-3.7.2
01:59
This is the beginning of an attempt to backport recent query planner enhancements to version 3.7.2. The code in this version builds and runs and seems to give correct answers, but it generates suboptimal query plans and hence many of the test cases fail. The test script gives up after 1000 errors. check-in: e72cf118 user: drh tags: branch-3.7.2
Changes
Hide Diffs Unified Diffs Show Whitespace Changes Patch

Changes to src/build.c.

798
799
800
801
802
803
804

805
806
807
808
809
810
811
....
2828
2829
2830
2831
2832
2833
2834
2835
2836
2837
2838
2839
2840
2841
2842
2843
2844
2845
2846
2847
2848
2849
    pParse->nErr++;
    goto begin_table_error;
  }
  pTable->zName = zName;
  pTable->iPKey = -1;
  pTable->pSchema = db->aDb[iDb].pSchema;
  pTable->nRef = 1;

  assert( pParse->pNewTable==0 );
  pParse->pNewTable = pTable;

  /* If this is the magic sqlite_sequence table used by autoincrement,
  ** then record a pointer to this table in the main database structure
  ** so that INSERT can find the table easily.
  */
................................................................................
** Apart from that, we have little to go on besides intuition as to
** how aiRowEst[] should be initialized.  The numbers generated here
** are based on typical values found in actual indices.
*/
void sqlite3DefaultRowEst(Index *pIdx){
  unsigned *a = pIdx->aiRowEst;
  int i;
  assert( a!=0 );
  a[0] = 1000000;
  for(i=pIdx->nColumn; i>=5; i--){
    a[i] = 5;
  }
  while( i>=1 ){
    a[i] = 11 - i;
    i--;
  }
  if( pIdx->onError!=OE_None ){
    a[pIdx->nColumn] = 1;
  }
}

/*







>







 







|
|
|
|
|
|
|
|







798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
....
2829
2830
2831
2832
2833
2834
2835
2836
2837
2838
2839
2840
2841
2842
2843
2844
2845
2846
2847
2848
2849
2850
    pParse->nErr++;
    goto begin_table_error;
  }
  pTable->zName = zName;
  pTable->iPKey = -1;
  pTable->pSchema = db->aDb[iDb].pSchema;
  pTable->nRef = 1;
  pTable->nRowEst = 1000000;
  assert( pParse->pNewTable==0 );
  pParse->pNewTable = pTable;

  /* If this is the magic sqlite_sequence table used by autoincrement,
  ** then record a pointer to this table in the main database structure
  ** so that INSERT can find the table easily.
  */
................................................................................
** Apart from that, we have little to go on besides intuition as to
** how aiRowEst[] should be initialized.  The numbers generated here
** are based on typical values found in actual indices.
*/
void sqlite3DefaultRowEst(Index *pIdx){
  unsigned *a = pIdx->aiRowEst;
  int i;
  unsigned n;
  assert( a!=0 );
  a[0] = pIdx->pTable->nRowEst;
  if( a[0]<10 ) a[0] = 10;
  n = 10;
  for(i=1; i<=pIdx->nColumn; i++){
    a[i] = n;
    if( n>5 ) n--;
  }
  if( pIdx->onError!=OE_None ){
    a[pIdx->nColumn] = 1;
  }
}

/*

Changes to src/prepare.c.

624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
  }
  rc = pParse->rc;

#ifndef SQLITE_OMIT_EXPLAIN
  if( rc==SQLITE_OK && pParse->pVdbe && pParse->explain ){
    static const char * const azColName[] = {
       "addr", "opcode", "p1", "p2", "p3", "p4", "p5", "comment",
       "order", "from", "detail"
    };
    int iFirst, mx;
    if( pParse->explain==2 ){
      sqlite3VdbeSetNumCols(pParse->pVdbe, 3);
      iFirst = 8;
      mx = 11;
    }else{
      sqlite3VdbeSetNumCols(pParse->pVdbe, 8);
      iFirst = 0;
      mx = 8;
    }
    for(i=iFirst; i<mx; i++){
      sqlite3VdbeSetColName(pParse->pVdbe, i-iFirst, COLNAME_NAME,







|



|

|







624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
  }
  rc = pParse->rc;

#ifndef SQLITE_OMIT_EXPLAIN
  if( rc==SQLITE_OK && pParse->pVdbe && pParse->explain ){
    static const char * const azColName[] = {
       "addr", "opcode", "p1", "p2", "p3", "p4", "p5", "comment",
       "selectid", "order", "from", "detail"
    };
    int iFirst, mx;
    if( pParse->explain==2 ){
      sqlite3VdbeSetNumCols(pParse->pVdbe, 4);
      iFirst = 8;
      mx = 12;
    }else{
      sqlite3VdbeSetNumCols(pParse->pVdbe, 8);
      iFirst = 0;
      mx = 8;
    }
    for(i=iFirst; i<mx; i++){
      sqlite3VdbeSetColName(pParse->pVdbe, i-iFirst, COLNAME_NAME,

Changes to src/select.c.

1298
1299
1300
1301
1302
1303
1304

1305
1306
1307
1308
1309
1310
1311
....
1368
1369
1370
1371
1372
1373
1374


1375
1376
1377
1378
1379
1380
1381
....
1524
1525
1526
1527
1528
1529
1530

1531
1532
1533
1534
1535
1536
1537
....
1544
1545
1546
1547
1548
1549
1550







1551
1552
1553
1554
1555
1556
1557
....
1614
1615
1616
1617
1618
1619
1620

1621
1622
1623
1624
1625
1626
1627
....
1691
1692
1693
1694
1695
1696
1697

1698
1699
1700
1701
1702
1703
1704
....
2270
2271
2272
2273
2274
2275
2276

2277
2278
2279
2280
2281
2282
2283

2284
2285
2286
2287
2288
2289
2290
....
3097
3098
3099
3100
3101
3102
3103

3104
3105
3106
3107
3108
3109
3110
....
3654
3655
3656
3657
3658
3659
3660

3661
3662
3663
3664
3665
3666
3667
....
3754
3755
3756
3757
3758
3759
3760

3761
3762
3763
3764
3765
3766
3767
....
3776
3777
3778
3779
3780
3781
3782

3783
3784
3785
3786
3787
3788
3789
....
3820
3821
3822
3823
3824
3825
3826



3827
3828
3829
3830
3831
3832
3833
    return 0;
  }
  /* The sqlite3ResultSetOfSelect() is only used n contexts where lookaside
  ** is disabled */
  assert( db->lookaside.bEnabled==0 );
  pTab->nRef = 1;
  pTab->zName = 0;

  selectColumnsFromExprList(pParse, pSelect->pEList, &pTab->nCol, &pTab->aCol);
  selectAddColumnTypeAndCollation(pParse, pTab->nCol, pTab->aCol, pSelect);
  pTab->iPKey = -1;
  if( db->mallocFailed ){
    sqlite3DeleteTable(db, pTab);
    return 0;
  }
................................................................................
    v = sqlite3GetVdbe(pParse);
    if( NEVER(v==0) ) return;  /* VDBE should have already been allocated */
    if( sqlite3ExprIsInteger(p->pLimit, &n) ){
      sqlite3VdbeAddOp2(v, OP_Integer, n, iLimit);
      VdbeComment((v, "LIMIT counter"));
      if( n==0 ){
        sqlite3VdbeAddOp2(v, OP_Goto, 0, iBreak);


      }
    }else{
      sqlite3ExprCode(pParse, p->pLimit, iLimit);
      sqlite3VdbeAddOp1(v, OP_MustBeInt, iLimit);
      VdbeComment((v, "LIMIT counter"));
      sqlite3VdbeAddOp2(v, OP_IfZero, iLimit, iBreak);
    }
................................................................................
  }

  /* Generate code for the left and right SELECT statements.
  */
  switch( p->op ){
    case TK_ALL: {
      int addr = 0;

      assert( !pPrior->pLimit );
      pPrior->pLimit = p->pLimit;
      pPrior->pOffset = p->pOffset;
      rc = sqlite3Select(pParse, pPrior, &dest);
      p->pLimit = 0;
      p->pOffset = 0;
      if( rc ){
................................................................................
        addr = sqlite3VdbeAddOp1(v, OP_IfZero, p->iLimit);
        VdbeComment((v, "Jump ahead if LIMIT reached"));
      }
      rc = sqlite3Select(pParse, p, &dest);
      testcase( rc!=SQLITE_OK );
      pDelete = p->pPrior;
      p->pPrior = pPrior;







      if( addr ){
        sqlite3VdbeJumpHere(v, addr);
      }
      break;
    }
    case TK_EXCEPT:
    case TK_UNION: {
................................................................................
      testcase( rc!=SQLITE_OK );
      /* Query flattening in sqlite3Select() might refill p->pOrderBy.
      ** Be sure to delete p->pOrderBy, therefore, to avoid a memory leak. */
      sqlite3ExprListDelete(db, p->pOrderBy);
      pDelete = p->pPrior;
      p->pPrior = pPrior;
      p->pOrderBy = 0;

      sqlite3ExprDelete(db, p->pLimit);
      p->pLimit = pLimit;
      p->pOffset = pOffset;
      p->iLimit = 0;
      p->iOffset = 0;

      /* Convert the data in the temporary table into whatever form
................................................................................
      pOffset = p->pOffset;
      p->pOffset = 0;
      intersectdest.iParm = tab2;
      rc = sqlite3Select(pParse, p, &intersectdest);
      testcase( rc!=SQLITE_OK );
      pDelete = p->pPrior;
      p->pPrior = pPrior;

      sqlite3ExprDelete(db, p->pLimit);
      p->pLimit = pLimit;
      p->pOffset = pOffset;

      /* Generate code to take the intersection of the two temporary
      ** tables.
      */
................................................................................
  if( op==TK_EXCEPT || op==TK_INTERSECT ){
    addrEofA = sqlite3VdbeAddOp2(v, OP_Goto, 0, labelEnd);
  }else{  
    addrEofA = sqlite3VdbeAddOp2(v, OP_If, regEofB, labelEnd);
    sqlite3VdbeAddOp2(v, OP_Gosub, regOutB, addrOutB);
    sqlite3VdbeAddOp1(v, OP_Yield, regAddrB);
    sqlite3VdbeAddOp2(v, OP_Goto, 0, addrEofA);

  }

  /* Generate a subroutine to run when the results from select B
  ** are exhausted and only data in select A remains.
  */
  if( op==TK_INTERSECT ){
    addrEofB = addrEofA;

  }else{  
    VdbeNoopComment((v, "eof-B subroutine"));
    addrEofB = sqlite3VdbeAddOp2(v, OP_If, regEofA, labelEnd);
    sqlite3VdbeAddOp2(v, OP_Gosub, regOutA, addrOutA);
    sqlite3VdbeAddOp1(v, OP_Yield, regAddrA);
    sqlite3VdbeAddOp2(v, OP_Goto, 0, addrEofB);
  }
................................................................................
      pFrom->pTab = pTab = sqlite3DbMallocZero(db, sizeof(Table));
      if( pTab==0 ) return WRC_Abort;
      pTab->nRef = 1;
      pTab->zName = sqlite3MPrintf(db, "sqlite_subquery_%p_", (void*)pTab);
      while( pSel->pPrior ){ pSel = pSel->pPrior; }
      selectColumnsFromExprList(pParse, pSel->pEList, &pTab->nCol, &pTab->aCol);
      pTab->iPKey = -1;

      pTab->tabFlags |= TF_Ephemeral;
#endif
    }else{
      /* An ordinary table or view name in the FROM clause */
      assert( pFrom->pTab==0 );
      pFrom->pTab = pTab = 
        sqlite3LocateTable(pParse,0,pFrom->zName,pFrom->zDatabase);
................................................................................
      }
      i = -1;
    }else{
      sqlite3SelectDestInit(&dest, SRT_EphemTab, pItem->iCursor);
      assert( pItem->isPopulated==0 );
      sqlite3Select(pParse, pSub, &dest);
      pItem->isPopulated = 1;

    }
    if( /*pParse->nErr ||*/ db->mallocFailed ){
      goto select_end;
    }
    pParse->nHeight -= sqlite3SelectExprHeight(p);
    pTabList = p->pSrc;
    if( !IgnorableOrderby(pDest) ){
................................................................................
  if( pDest->eDest==SRT_EphemTab ){
    sqlite3VdbeAddOp2(v, OP_OpenEphemeral, pDest->iParm, pEList->nExpr);
  }

  /* Set the limiter.
  */
  iEnd = sqlite3VdbeMakeLabel(v);

  computeLimitRegisters(pParse, p, iEnd);

  /* Open a virtual index to use for the distinct set.
  */
  if( isDistinct ){
    KeyInfo *pKeyInfo;
    assert( isAgg || pGroupBy );
................................................................................
  /* Aggregate and non-aggregate queries are handled differently */
  if( !isAgg && pGroupBy==0 ){
    /* This case is for non-aggregate queries
    ** Begin the database scan
    */
    pWInfo = sqlite3WhereBegin(pParse, pTabList, pWhere, &pOrderBy, 0);
    if( pWInfo==0 ) goto select_end;


    /* If sorting index that was created by a prior OP_OpenEphemeral 
    ** instruction ended up not being needed, then change the OP_OpenEphemeral
    ** into an OP_Noop.
    */
    if( addrSortIndex>=0 && pOrderBy==0 ){
      sqlite3VdbeChangeToNoop(v, addrSortIndex, 1);
................................................................................

      for(k=p->pEList->nExpr, pItem=p->pEList->a; k>0; k--, pItem++){
        pItem->iAlias = 0;
      }
      for(k=pGroupBy->nExpr, pItem=pGroupBy->a; k>0; k--, pItem++){
        pItem->iAlias = 0;
      }



    }

 
    /* Create a label to jump to when we want to abort the query */
    addrEnd = sqlite3VdbeMakeLabel(v);

    /* Convert TK_COLUMN nodes into TK_AGG_COLUMN and make entries in







>







 







>
>







 







>







 







>
>
>
>
>
>
>







 







>







 







>







 







>







>







 







>







 







>







 







>







 







>







 







>
>
>







1298
1299
1300
1301
1302
1303
1304
1305
1306
1307
1308
1309
1310
1311
1312
....
1369
1370
1371
1372
1373
1374
1375
1376
1377
1378
1379
1380
1381
1382
1383
1384
....
1527
1528
1529
1530
1531
1532
1533
1534
1535
1536
1537
1538
1539
1540
1541
....
1548
1549
1550
1551
1552
1553
1554
1555
1556
1557
1558
1559
1560
1561
1562
1563
1564
1565
1566
1567
1568
....
1625
1626
1627
1628
1629
1630
1631
1632
1633
1634
1635
1636
1637
1638
1639
....
1703
1704
1705
1706
1707
1708
1709
1710
1711
1712
1713
1714
1715
1716
1717
....
2283
2284
2285
2286
2287
2288
2289
2290
2291
2292
2293
2294
2295
2296
2297
2298
2299
2300
2301
2302
2303
2304
2305
....
3112
3113
3114
3115
3116
3117
3118
3119
3120
3121
3122
3123
3124
3125
3126
....
3670
3671
3672
3673
3674
3675
3676
3677
3678
3679
3680
3681
3682
3683
3684
....
3771
3772
3773
3774
3775
3776
3777
3778
3779
3780
3781
3782
3783
3784
3785
....
3794
3795
3796
3797
3798
3799
3800
3801
3802
3803
3804
3805
3806
3807
3808
....
3839
3840
3841
3842
3843
3844
3845
3846
3847
3848
3849
3850
3851
3852
3853
3854
3855
    return 0;
  }
  /* The sqlite3ResultSetOfSelect() is only used n contexts where lookaside
  ** is disabled */
  assert( db->lookaside.bEnabled==0 );
  pTab->nRef = 1;
  pTab->zName = 0;
  pTab->nRowEst = 1000000;
  selectColumnsFromExprList(pParse, pSelect->pEList, &pTab->nCol, &pTab->aCol);
  selectAddColumnTypeAndCollation(pParse, pTab->nCol, pTab->aCol, pSelect);
  pTab->iPKey = -1;
  if( db->mallocFailed ){
    sqlite3DeleteTable(db, pTab);
    return 0;
  }
................................................................................
    v = sqlite3GetVdbe(pParse);
    if( NEVER(v==0) ) return;  /* VDBE should have already been allocated */
    if( sqlite3ExprIsInteger(p->pLimit, &n) ){
      sqlite3VdbeAddOp2(v, OP_Integer, n, iLimit);
      VdbeComment((v, "LIMIT counter"));
      if( n==0 ){
        sqlite3VdbeAddOp2(v, OP_Goto, 0, iBreak);
      }else{
        if( p->nSelectRow > (double)n ) p->nSelectRow = (double)n;
      }
    }else{
      sqlite3ExprCode(pParse, p->pLimit, iLimit);
      sqlite3VdbeAddOp1(v, OP_MustBeInt, iLimit);
      VdbeComment((v, "LIMIT counter"));
      sqlite3VdbeAddOp2(v, OP_IfZero, iLimit, iBreak);
    }
................................................................................
  }

  /* Generate code for the left and right SELECT statements.
  */
  switch( p->op ){
    case TK_ALL: {
      int addr = 0;
      int nLimit;
      assert( !pPrior->pLimit );
      pPrior->pLimit = p->pLimit;
      pPrior->pOffset = p->pOffset;
      rc = sqlite3Select(pParse, pPrior, &dest);
      p->pLimit = 0;
      p->pOffset = 0;
      if( rc ){
................................................................................
        addr = sqlite3VdbeAddOp1(v, OP_IfZero, p->iLimit);
        VdbeComment((v, "Jump ahead if LIMIT reached"));
      }
      rc = sqlite3Select(pParse, p, &dest);
      testcase( rc!=SQLITE_OK );
      pDelete = p->pPrior;
      p->pPrior = pPrior;
      p->nSelectRow += pPrior->nSelectRow;
      if( pPrior->pLimit
       && sqlite3ExprIsInteger(pPrior->pLimit, &nLimit)
       && p->nSelectRow > (double)nLimit 
      ){
        p->nSelectRow = (double)nLimit;
      }
      if( addr ){
        sqlite3VdbeJumpHere(v, addr);
      }
      break;
    }
    case TK_EXCEPT:
    case TK_UNION: {
................................................................................
      testcase( rc!=SQLITE_OK );
      /* Query flattening in sqlite3Select() might refill p->pOrderBy.
      ** Be sure to delete p->pOrderBy, therefore, to avoid a memory leak. */
      sqlite3ExprListDelete(db, p->pOrderBy);
      pDelete = p->pPrior;
      p->pPrior = pPrior;
      p->pOrderBy = 0;
      if( p->op==TK_UNION ) p->nSelectRow += pPrior->nSelectRow;
      sqlite3ExprDelete(db, p->pLimit);
      p->pLimit = pLimit;
      p->pOffset = pOffset;
      p->iLimit = 0;
      p->iOffset = 0;

      /* Convert the data in the temporary table into whatever form
................................................................................
      pOffset = p->pOffset;
      p->pOffset = 0;
      intersectdest.iParm = tab2;
      rc = sqlite3Select(pParse, p, &intersectdest);
      testcase( rc!=SQLITE_OK );
      pDelete = p->pPrior;
      p->pPrior = pPrior;
      if( p->nSelectRow>pPrior->nSelectRow ) p->nSelectRow = pPrior->nSelectRow;
      sqlite3ExprDelete(db, p->pLimit);
      p->pLimit = pLimit;
      p->pOffset = pOffset;

      /* Generate code to take the intersection of the two temporary
      ** tables.
      */
................................................................................
  if( op==TK_EXCEPT || op==TK_INTERSECT ){
    addrEofA = sqlite3VdbeAddOp2(v, OP_Goto, 0, labelEnd);
  }else{  
    addrEofA = sqlite3VdbeAddOp2(v, OP_If, regEofB, labelEnd);
    sqlite3VdbeAddOp2(v, OP_Gosub, regOutB, addrOutB);
    sqlite3VdbeAddOp1(v, OP_Yield, regAddrB);
    sqlite3VdbeAddOp2(v, OP_Goto, 0, addrEofA);
    p->nSelectRow += pPrior->nSelectRow;
  }

  /* Generate a subroutine to run when the results from select B
  ** are exhausted and only data in select A remains.
  */
  if( op==TK_INTERSECT ){
    addrEofB = addrEofA;
    if( p->nSelectRow > pPrior->nSelectRow ) p->nSelectRow = pPrior->nSelectRow;
  }else{  
    VdbeNoopComment((v, "eof-B subroutine"));
    addrEofB = sqlite3VdbeAddOp2(v, OP_If, regEofA, labelEnd);
    sqlite3VdbeAddOp2(v, OP_Gosub, regOutA, addrOutA);
    sqlite3VdbeAddOp1(v, OP_Yield, regAddrA);
    sqlite3VdbeAddOp2(v, OP_Goto, 0, addrEofB);
  }
................................................................................
      pFrom->pTab = pTab = sqlite3DbMallocZero(db, sizeof(Table));
      if( pTab==0 ) return WRC_Abort;
      pTab->nRef = 1;
      pTab->zName = sqlite3MPrintf(db, "sqlite_subquery_%p_", (void*)pTab);
      while( pSel->pPrior ){ pSel = pSel->pPrior; }
      selectColumnsFromExprList(pParse, pSel->pEList, &pTab->nCol, &pTab->aCol);
      pTab->iPKey = -1;
      pTab->nRowEst = 1000000;
      pTab->tabFlags |= TF_Ephemeral;
#endif
    }else{
      /* An ordinary table or view name in the FROM clause */
      assert( pFrom->pTab==0 );
      pFrom->pTab = pTab = 
        sqlite3LocateTable(pParse,0,pFrom->zName,pFrom->zDatabase);
................................................................................
      }
      i = -1;
    }else{
      sqlite3SelectDestInit(&dest, SRT_EphemTab, pItem->iCursor);
      assert( pItem->isPopulated==0 );
      sqlite3Select(pParse, pSub, &dest);
      pItem->isPopulated = 1;
      pItem->pTab->nRowEst = (unsigned)pSub->nSelectRow;
    }
    if( /*pParse->nErr ||*/ db->mallocFailed ){
      goto select_end;
    }
    pParse->nHeight -= sqlite3SelectExprHeight(p);
    pTabList = p->pSrc;
    if( !IgnorableOrderby(pDest) ){
................................................................................
  if( pDest->eDest==SRT_EphemTab ){
    sqlite3VdbeAddOp2(v, OP_OpenEphemeral, pDest->iParm, pEList->nExpr);
  }

  /* Set the limiter.
  */
  iEnd = sqlite3VdbeMakeLabel(v);
  p->nSelectRow = (double)LARGEST_INT64;
  computeLimitRegisters(pParse, p, iEnd);

  /* Open a virtual index to use for the distinct set.
  */
  if( isDistinct ){
    KeyInfo *pKeyInfo;
    assert( isAgg || pGroupBy );
................................................................................
  /* Aggregate and non-aggregate queries are handled differently */
  if( !isAgg && pGroupBy==0 ){
    /* This case is for non-aggregate queries
    ** Begin the database scan
    */
    pWInfo = sqlite3WhereBegin(pParse, pTabList, pWhere, &pOrderBy, 0);
    if( pWInfo==0 ) goto select_end;
    if( pWInfo->nRowOut < p->nSelectRow ) p->nSelectRow = pWInfo->nRowOut;

    /* If sorting index that was created by a prior OP_OpenEphemeral 
    ** instruction ended up not being needed, then change the OP_OpenEphemeral
    ** into an OP_Noop.
    */
    if( addrSortIndex>=0 && pOrderBy==0 ){
      sqlite3VdbeChangeToNoop(v, addrSortIndex, 1);
................................................................................

      for(k=p->pEList->nExpr, pItem=p->pEList->a; k>0; k--, pItem++){
        pItem->iAlias = 0;
      }
      for(k=pGroupBy->nExpr, pItem=pGroupBy->a; k>0; k--, pItem++){
        pItem->iAlias = 0;
      }
      if( p->nSelectRow>(double)100 ) p->nSelectRow = (double)100;
    }else{
      p->nSelectRow = (double)1;
    }

 
    /* Create a label to jump to when we want to abort the query */
    addrEnd = sqlite3VdbeMakeLabel(v);

    /* Convert TK_COLUMN nodes into TK_AGG_COLUMN and make entries in

Changes to src/sqliteInt.h.

1991
1992
1993
1994
1995
1996
1997

1998
1999
2000
2001
2002
2003
2004
  Select *pPrior;        /* Prior select in a compound select statement */
  Select *pNext;         /* Next select to the left in a compound */
  Select *pRightmost;    /* Right-most select in a compound select statement */
  Expr *pLimit;          /* LIMIT expression. NULL means not used. */
  Expr *pOffset;         /* OFFSET expression. NULL means not used. */
  int iLimit, iOffset;   /* Memory registers holding LIMIT & OFFSET counters */
  int addrOpenEphm[3];   /* OP_OpenEphem opcodes related to this select */

};

/*
** Allowed values for Select.selFlags.  The "SF" prefix stands for
** "Select Flag".
*/
#define SF_Distinct        0x0001  /* Output should be DISTINCT */







>







1991
1992
1993
1994
1995
1996
1997
1998
1999
2000
2001
2002
2003
2004
2005
  Select *pPrior;        /* Prior select in a compound select statement */
  Select *pNext;         /* Next select to the left in a compound */
  Select *pRightmost;    /* Right-most select in a compound select statement */
  Expr *pLimit;          /* LIMIT expression. NULL means not used. */
  Expr *pOffset;         /* OFFSET expression. NULL means not used. */
  int iLimit, iOffset;   /* Memory registers holding LIMIT & OFFSET counters */
  int addrOpenEphm[3];   /* OP_OpenEphem opcodes related to this select */
  double nSelectRow;     /* Estimated number of result rows */
};

/*
** Allowed values for Select.selFlags.  The "SF" prefix stands for
** "Select Flag".
*/
#define SF_Distinct        0x0001  /* Output should be DISTINCT */

Changes to src/vdbeaux.c.

1178
1179
1180
1181
1182
1183
1184
1185
1186
1187
1188
1189
1190
1191
1192
1193
1194
1195
1196
1197
....
1228
1229
1230
1231
1232
1233
1234
1235
1236
1237
1238
1239
1240
1241
1242
    pMem++;

    pMem->flags = MEM_Int;
    pMem->u.i = pOp->p2;                          /* P2 */
    pMem->type = SQLITE_INTEGER;
    pMem++;

    if( p->explain==1 ){
      pMem->flags = MEM_Int;
      pMem->u.i = pOp->p3;                          /* P3 */
      pMem->type = SQLITE_INTEGER;
      pMem++;
    }

    if( sqlite3VdbeMemGrow(pMem, 32, 0) ){            /* P4 */
      assert( p->db->mallocFailed );
      return SQLITE_ERROR;
    }
    pMem->flags = MEM_Dyn|MEM_Str|MEM_Term;
    z = displayP4(pOp, pMem->z, 32);
................................................................................
#endif
      {
        pMem->flags = MEM_Null;                       /* Comment */
        pMem->type = SQLITE_NULL;
      }
    }

    p->nResColumn = 8 - 5*(p->explain-1);
    p->rc = SQLITE_OK;
    rc = SQLITE_ROW;
  }
  return rc;
}
#endif /* SQLITE_OMIT_EXPLAIN */








<




<







 







|







1178
1179
1180
1181
1182
1183
1184

1185
1186
1187
1188

1189
1190
1191
1192
1193
1194
1195
....
1226
1227
1228
1229
1230
1231
1232
1233
1234
1235
1236
1237
1238
1239
1240
    pMem++;

    pMem->flags = MEM_Int;
    pMem->u.i = pOp->p2;                          /* P2 */
    pMem->type = SQLITE_INTEGER;
    pMem++;


    pMem->flags = MEM_Int;
    pMem->u.i = pOp->p3;                          /* P3 */
    pMem->type = SQLITE_INTEGER;
    pMem++;


    if( sqlite3VdbeMemGrow(pMem, 32, 0) ){            /* P4 */
      assert( p->db->mallocFailed );
      return SQLITE_ERROR;
    }
    pMem->flags = MEM_Dyn|MEM_Str|MEM_Term;
    z = displayP4(pOp, pMem->z, 32);
................................................................................
#endif
      {
        pMem->flags = MEM_Null;                       /* Comment */
        pMem->type = SQLITE_NULL;
      }
    }

    p->nResColumn = 8 - 4*(p->explain-1);
    p->rc = SQLITE_OK;
    rc = SQLITE_ROW;
  }
  return rc;
}
#endif /* SQLITE_OMIT_EXPLAIN */

Changes to test/analyze2.test.

17
18
19
20
21
22
23


24
25
26
27
28
29
30
...
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
140




141
142
143




144
145
146




147
148
149




150
151



152
153
154
155
156
157
158
...
173
174
175
176
177
178
179
180
181
182
183


184
185
186




187
188
189




190
191
192




193
194



195
196
197
198
199
200
201
...
206
207
208
209
210
211
212

213
214
215
216

217
218
219
220
221
222
223
224
225
226
227
228
229
230
231


232
233



234
235
236
237
238
239
240
...
256
257
258
259
260
261
262
263
264
265
266
267
268
269



270
271




272
273
274
275
276
277
278
...
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
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
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
...
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
set testdir [file dirname $argv0]
source $testdir/tester.tcl

ifcapable !stat2 {
  finish_test
  return
}



# Do not use a codec for tests in this file, as the database file is
# manipulated directly using tcl scripts (using the [hexio_write] command).
#
do_not_use_codec

#--------------------------------------------------------------------
................................................................................
  }
  for {set i 0} {$i < 1000} {incr i} {
    execsql { INSERT INTO t1 VALUES($i, $i) }
  }
  execsql COMMIT
  execsql ANALYZE
} {}
do_test analyze2-2.2 {
  eqp "SELECT * FROM t1 WHERE x>500 AND y>700"
} {0 0 {TABLE t1 WITH INDEX t1_y}}
do_test analyze2-2.3 {


  eqp "SELECT * FROM t1 WHERE x>700 AND y>500"
} {0 0 {TABLE t1 WITH INDEX t1_x}}
do_test analyze2-2.3 {




  eqp "SELECT * FROM t1 WHERE y>700 AND x>500"
} {0 0 {TABLE t1 WITH INDEX t1_y}}
do_test analyze2-2.4 {




  eqp "SELECT * FROM t1 WHERE y>500 AND x>700"
} {0 0 {TABLE t1 WITH INDEX t1_x}}
do_test analyze2-2.5 {




  eqp "SELECT * FROM t1 WHERE x BETWEEN 100 AND 200 AND y BETWEEN 400 AND 700"
} {0 0 {TABLE t1 WITH INDEX t1_x}}
do_test analyze2-2.6 {




  eqp "SELECT * FROM t1 WHERE x BETWEEN 100 AND 500 AND y BETWEEN 400 AND 700"
} {0 0 {TABLE t1 WITH INDEX t1_y}}
do_test analyze2-2.7 {




  eqp "SELECT * FROM t1 WHERE x BETWEEN -400 AND -300 AND y BETWEEN 100 AND 300"
} {0 0 {TABLE t1 WITH INDEX t1_x}}
do_test analyze2-2.8 {




  eqp "SELECT * FROM t1 WHERE x BETWEEN 100 AND 300 AND y BETWEEN -400 AND -300"
} {0 0 {TABLE t1 WITH INDEX t1_y}}
do_test analyze2-2.9 {




  eqp "SELECT * FROM t1 WHERE x BETWEEN 500 AND 100 AND y BETWEEN 100 AND 300"
} {0 0 {TABLE t1 WITH INDEX t1_x}}
do_test analyze2-2.10 {




  eqp "SELECT * FROM t1 WHERE x BETWEEN 100 AND 300 AND y BETWEEN 500 AND 100"
} {0 0 {TABLE t1 WITH INDEX t1_y}}




do_test analyze2-3.1 {
  set alphabet [list a b c d e f g h i j]
  execsql BEGIN
  for {set i 0} {$i < 1000} {incr i} {
    set str    [lindex $alphabet [expr ($i/100)%10]] 
    append str [lindex $alphabet [expr ($i/ 10)%10]]
................................................................................
    SELECT tbl,idx,group_concat(sample,' ') 
    FROM sqlite_stat2 
    WHERE idx = 't1_y' 
    GROUP BY tbl,idx
  }
} {t1 t1_y {100 299 499 699 899 ajj cjj ejj gjj ijj}}

do_test analyze2-3.3 {
  eqp "SELECT * FROM t1 WHERE x BETWEEN 100 AND 500 AND y BETWEEN 'a' AND 'b'"
} {0 0 {TABLE t1 WITH INDEX t1_y}}
do_test analyze2-3.4 {


  eqp "SELECT * FROM t1 WHERE x BETWEEN 100 AND 400 AND y BETWEEN 'a' AND 'h'"
} {0 0 {TABLE t1 WITH INDEX t1_x}}
do_test analyze2-3.5 {




  eqp "SELECT * FROM t1 WHERE x<'a' AND y>'h'"
} {0 0 {TABLE t1 WITH INDEX t1_y}}
do_test analyze2-3.6 {




  eqp "SELECT * FROM t1 WHERE x<444 AND y>'h'"
} {0 0 {TABLE t1 WITH INDEX t1_y}}
do_test analyze2-3.7 {




  eqp "SELECT * FROM t1 WHERE x<221 AND y>'g'"
} {0 0 {TABLE t1 WITH INDEX t1_x}}




do_test analyze2-4.1 {
  execsql { CREATE TABLE t3(a COLLATE nocase, b) }
  execsql { CREATE INDEX t3a ON t3(a) }
  execsql { CREATE INDEX t3b ON t3(b) }
  set alphabet [list A b C d E f G h I j]
  execsql BEGIN
................................................................................
    execsql { INSERT INTO t3 VALUES($str, $str) }
  }
  execsql COMMIT
  execsql ANALYZE
} {}
do_test analyze2-4.2 {
  execsql { 

    SELECT tbl,idx,group_concat(sample,' ') 
    FROM sqlite_stat2 
    WHERE idx = 't3a' 
    GROUP BY tbl,idx

  }
} {t3 t3a {AfA bEj CEj dEj EEj fEj GEj hEj IEj jEj}}
do_test analyze2-4.3 {
  execsql { 
    SELECT tbl,idx,group_concat(sample,' ') 
    FROM sqlite_stat2 
    WHERE idx = 't3b' 
    GROUP BY tbl,idx
  }
} {t3 t3b {AbA CIj EIj GIj IIj bIj dIj fIj hIj jIj}}

do_test analyze2-4.4 {
  eqp "SELECT * FROM t3 WHERE a > 'A' AND a < 'C' AND b > 'A' AND b < 'C'"
} {0 0 {TABLE t3 WITH INDEX t3b}}
do_test analyze2-4.5 {


  eqp "SELECT * FROM t3 WHERE a > 'A' AND a < 'c' AND b > 'A' AND b < 'c'"
} {0 0 {TABLE t3 WITH INDEX t3a}}




ifcapable utf16 {
  proc test_collate {enc lhs rhs} {
    # puts $enc
    return [string compare $lhs $rhs]
  }
  do_test analyze2-5.1 {
................................................................................
    execsql { 
      SELECT tbl,idx,group_concat(sample,' ') 
      FROM sqlite_stat2 
      WHERE tbl = 't4' 
      GROUP BY tbl,idx
    }
  } {t4 t4x {afa bej cej dej eej fej gej hej iej jej}}
  do_test analyze2-5.3 {
    eqp "SELECT * FROM t4 WHERE x>'ccc'"
  } {0 0 {TABLE t4 WITH INDEX t4x}}
  do_test analyze2-5.4 {
    eqp "SELECT * FROM t4 AS t41, t4 AS t42 WHERE t41.x>'ccc' AND t42.x>'ggg'"
  } {0 1 {TABLE t4 AS t42 WITH INDEX t4x} 1 0 {TABLE t4 AS t41 WITH INDEX t4x}}
  do_test analyze2-5.5 {



    eqp "SELECT * FROM t4 AS t41, t4 AS t42 WHERE t41.x>'ddd' AND t42.x>'ccc'"
  } {0 0 {TABLE t4 AS t41 WITH INDEX t4x} 1 1 {TABLE t4 AS t42 WITH INDEX t4x}}




}

#--------------------------------------------------------------------
# These tests, analyze2-6.*, verify that the library behaves correctly
# when one of the sqlite_stat1 and sqlite_stat2 tables is missing.
#
# If the sqlite_stat1 table is not present, then the sqlite_stat2
................................................................................
} {}

do_test analyze2-6.1.1 {
  eqp {SELECT * FROM t5,t6 WHERE t5.rowid=t6.rowid AND 
       t5.a = 1 AND
       t6.a = 1 AND t6.b = 1
  }
} {0 1 {TABLE t6 WITH INDEX t6i} 1 0 {TABLE t5 USING PRIMARY KEY}}
do_test analyze2-6.1.2 {
  db cache flush
  execsql ANALYZE
  eqp {SELECT * FROM t5,t6 WHERE t5.rowid=t6.rowid AND 
       t5.a = 1 AND
       t6.a = 1 AND t6.b = 1
  }
} {0 0 {TABLE t5 WITH INDEX t5i} 1 1 {TABLE t6 USING PRIMARY KEY}}
do_test analyze2-6.1.3 {
  sqlite3 db test.db
  eqp { SELECT * FROM t5,t6 WHERE t5.rowid=t6.rowid AND 
       t5.a = 1 AND
       t6.a = 1 AND t6.b = 1
  }
} {0 0 {TABLE t5 WITH INDEX t5i} 1 1 {TABLE t6 USING PRIMARY KEY}}
do_test analyze2-6.1.4 {
  execsql { 
    PRAGMA writable_schema = 1;
    DELETE FROM sqlite_master WHERE tbl_name = 'sqlite_stat2';
  }
  sqlite3 db test.db
  eqp { SELECT * FROM t5,t6 WHERE t5.rowid=t6.rowid AND 
       t5.a = 1 AND
       t6.a = 1 AND t6.b = 1
  }
} {0 0 {TABLE t5 WITH INDEX t5i} 1 1 {TABLE t6 USING PRIMARY KEY}}
do_test analyze2-6.1.5 {
  execsql { 
    PRAGMA writable_schema = 1;
    DELETE FROM sqlite_master WHERE tbl_name = 'sqlite_stat1';
  }
  sqlite3 db test.db
  eqp { SELECT * FROM t5,t6 WHERE t5.rowid=t6.rowid AND 
       t5.a = 1 AND
       t6.a = 1 AND t6.b = 1
  }
} {0 1 {TABLE t6 WITH INDEX t6i} 1 0 {TABLE t5 USING PRIMARY KEY}}
do_test analyze2-6.1.6 {
  execsql { 
    PRAGMA writable_schema = 1;
    INSERT INTO sqlite_master SELECT * FROM master;
  }
  sqlite3 db test.db
  eqp { SELECT * FROM t5,t6 WHERE t5.rowid=t6.rowid AND 
       t5.a = 1 AND
       t6.a = 1 AND t6.b = 1
  }
} {0 0 {TABLE t5 WITH INDEX t5i} 1 1 {TABLE t6 USING PRIMARY KEY}}

do_test analyze2-6.2.1 {
  execsql { 
    DELETE FROM sqlite_stat1;
    DELETE FROM sqlite_stat2;
  }
  sqlite3 db test.db
  eqp { SELECT * FROM t5,t6 WHERE t5.rowid=t6.rowid AND 
        t5.a>1 AND t5.a<15 AND
        t6.a>1
  }
} {0 0 {TABLE t5 WITH INDEX t5i} 1 1 {TABLE t6 USING PRIMARY KEY}}
do_test analyze2-6.2.2 {
  db cache flush
  execsql ANALYZE
  eqp { SELECT * FROM t5,t6 WHERE t5.rowid=t6.rowid AND 
        t5.a>1 AND t5.a<15 AND
        t6.a>1
  }
} {0 1 {TABLE t6 WITH INDEX t6i} 1 0 {TABLE t5 USING PRIMARY KEY}}
do_test analyze2-6.2.3 {
  sqlite3 db test.db
  eqp { SELECT * FROM t5,t6 WHERE t5.rowid=t6.rowid AND 
        t5.a>1 AND t5.a<15 AND
        t6.a>1
  }
} {0 1 {TABLE t6 WITH INDEX t6i} 1 0 {TABLE t5 USING PRIMARY KEY}}
do_test analyze2-6.2.4 {
  execsql { 
    PRAGMA writable_schema = 1;
    DELETE FROM sqlite_master WHERE tbl_name = 'sqlite_stat1';
  }
  sqlite3 db test.db
  eqp { SELECT * FROM t5,t6 WHERE t5.rowid=t6.rowid AND 
        t5.a>1 AND t5.a<15 AND
        t6.a>1
  }
} {0 0 {TABLE t5 WITH INDEX t5i} 1 1 {TABLE t6 USING PRIMARY KEY}}
do_test analyze2-6.2.5 {
  execsql { 
    PRAGMA writable_schema = 1;
    DELETE FROM sqlite_master WHERE tbl_name = 'sqlite_stat2';
  }
  sqlite3 db test.db
  eqp { SELECT * FROM t5,t6 WHERE t5.rowid=t6.rowid AND 
        t5.a>1 AND t5.a<15 AND
        t6.a>1
  }
} {0 0 {TABLE t5 WITH INDEX t5i} 1 1 {TABLE t6 USING PRIMARY KEY}}
do_test analyze2-6.2.6 {
  execsql { 
    PRAGMA writable_schema = 1;
    INSERT INTO sqlite_master SELECT * FROM master;
  }
  sqlite3 db test.db
  execsql ANALYZE
  eqp { SELECT * FROM t5,t6 WHERE t5.rowid=t6.rowid AND 
        t5.a>1 AND t5.a<15 AND
        t6.a>1
  }
} {0 1 {TABLE t6 WITH INDEX t6i} 1 0 {TABLE t5 USING PRIMARY KEY}}

#--------------------------------------------------------------------
# These tests, analyze2-7.*, test that the sqlite_stat2 functionality
# works in shared-cache mode. Note that these tests reuse the database
# created for the analyze2-6.* tests.
#
ifcapable shared_cache {
................................................................................
  } {20}

  do_test analyze2-7.5 {
    eqp { SELECT * FROM t5,t6 WHERE t5.rowid=t6.rowid AND 
          t5.a>1 AND t5.a<15 AND
          t6.a>1
    } db1
  } {0 1 {TABLE t6 WITH INDEX t6i} 1 0 {TABLE t5 USING PRIMARY KEY}}
  do_test analyze2-7.6 {
    incr_schema_cookie test.db
    execsql { SELECT * FROM sqlite_master } db2
    eqp { SELECT * FROM t5,t6 WHERE t5.rowid=t6.rowid AND 
          t5.a>1 AND t5.a<15 AND
          t6.a>1
    } db2
  } {0 1 {TABLE t6 WITH INDEX t6i} 1 0 {TABLE t5 USING PRIMARY KEY}}
  do_test analyze2-7.7 {
    incr_schema_cookie test.db
    execsql { SELECT * FROM sqlite_master } db1
    eqp { SELECT * FROM t5,t6 WHERE t5.rowid=t6.rowid AND 
          t5.a>1 AND t5.a<15 AND
          t6.a>1
    } db1
  } {0 1 {TABLE t6 WITH INDEX t6i} 1 0 {TABLE t5 USING PRIMARY KEY}}

  do_test analyze2-7.8 {
    execsql { DELETE FROM sqlite_stat2 } db2
    execsql { SELECT * FROM sqlite_master } db1
    eqp { SELECT * FROM t5,t6 WHERE t5.rowid=t6.rowid AND 
          t5.a>1 AND t5.a<15 AND
          t6.a>1
    } db1
  } {0 1 {TABLE t6 WITH INDEX t6i} 1 0 {TABLE t5 USING PRIMARY KEY}}
  do_test analyze2-7.9 {
    execsql { SELECT * FROM sqlite_master } db2
    eqp { SELECT * FROM t5,t6 WHERE t5.rowid=t6.rowid AND 
          t5.a>1 AND t5.a<15 AND
          t6.a>1
    } db2
  } {0 1 {TABLE t6 WITH INDEX t6i} 1 0 {TABLE t5 USING PRIMARY KEY}}

  do_test analyze2-7.10 {
    incr_schema_cookie test.db
    execsql { SELECT * FROM sqlite_master } db1
    eqp { SELECT * FROM t5,t6 WHERE t5.rowid=t6.rowid AND 
          t5.a>1 AND t5.a<15 AND
          t6.a>1
    } db1
  } {0 0 {TABLE t5 WITH INDEX t5i} 1 1 {TABLE t6 USING PRIMARY KEY}}

  db1 close
  db2 close
  sqlite3_enable_shared_cache $::enable_shared_cache
}

finish_test







>
>







 







|
|
|
|
>
>
|
<
<
>
>
>
>
|
<
<
>
>
>
>
|
<
<
>
>
>
>
|
<
<
>
>
>
>
|
<
<
>
>
>
>
|
<
<
>
>
>
>
|
<
<
>
>
>
>
|
<
<
>
>
>
>
|
<
>
>
>







 







|
|
|
|
>
>
|
<
<
>
>
>
>
|
<
<
>
>
>
>
|
<
<
>
>
>
>
|
<
>
>
>







 







>



|
>











|
|
|
|
>
>
|
<
>
>
>







 







|
|
|
|
|
|
|
>
>
>
|
<
>
>
>
>







 







|







|






|










|










|










|











|







|






|










|










|











|







 







|







|







|








|






|








|







17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
...
117
118
119
120
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


151
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
179
180
...
195
196
197
198
199
200
201
202
203
204
205
206
207
208


209
210
211
212
213


214
215
216
217
218


219
220
221
222
223

224
225
226
227
228
229
230
231
232
233
...
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268

269
270
271
272
273
274
275
276
277
278
...
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
...
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
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
...
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
set testdir [file dirname $argv0]
source $testdir/tester.tcl

ifcapable !stat2 {
  finish_test
  return
}

set testprefix analyze2

# Do not use a codec for tests in this file, as the database file is
# manipulated directly using tcl scripts (using the [hexio_write] command).
#
do_not_use_codec

#--------------------------------------------------------------------
................................................................................
  }
  for {set i 0} {$i < 1000} {incr i} {
    execsql { INSERT INTO t1 VALUES($i, $i) }
  }
  execsql COMMIT
  execsql ANALYZE
} {}
do_eqp_test 2.2 {
  SELECT * FROM t1 WHERE x>500 AND y>700
} {
  0 0 0 {SEARCH TABLE t1 USING INDEX t1_y (y>?) (~100 rows)}
}
do_eqp_test 2.3 {
  SELECT * FROM t1 WHERE x>700 AND y>500


} {
  0 0 0 {SEARCH TABLE t1 USING INDEX t1_x (x>?) (~100 rows)}
}
do_eqp_test 2.3 {
  SELECT * FROM t1 WHERE y>700 AND x>500


} {
  0 0 0 {SEARCH TABLE t1 USING INDEX t1_y (y>?) (~100 rows)}
}
do_eqp_test 2.4 {
  SELECT * FROM t1 WHERE y>500 AND x>700


} {
  0 0 0 {SEARCH TABLE t1 USING INDEX t1_x (x>?) (~100 rows)}
}
do_eqp_test 2.5 {
  SELECT * FROM t1 WHERE x BETWEEN 100 AND 200 AND y BETWEEN 400 AND 700


} {
  0 0 0 {SEARCH TABLE t1 USING INDEX t1_x (x>? AND x<?) (~25 rows)}
}
do_eqp_test 2.6 {
  SELECT * FROM t1 WHERE x BETWEEN 100 AND 500 AND y BETWEEN 400 AND 700


} {
  0 0 0 {SEARCH TABLE t1 USING INDEX t1_y (y>? AND y<?) (~75 rows)}
}
do_eqp_test 2.7 {
  SELECT * FROM t1 WHERE x BETWEEN -400 AND -300 AND y BETWEEN 100 AND 300


} {
  0 0 0 {SEARCH TABLE t1 USING INDEX t1_x (x>? AND x<?) (~12 rows)}
}
do_eqp_test 2.8 {
  SELECT * FROM t1 WHERE x BETWEEN 100 AND 300 AND y BETWEEN -400 AND -300


} {
  0 0 0 {SEARCH TABLE t1 USING INDEX t1_y (y>? AND y<?) (~12 rows)}
}
do_eqp_test 2.9 {
  SELECT * FROM t1 WHERE x BETWEEN 500 AND 100 AND y BETWEEN 100 AND 300


} {
  0 0 0 {SEARCH TABLE t1 USING INDEX t1_x (x>? AND x<?) (~12 rows)}
}
do_eqp_test 2.10 {
  SELECT * FROM t1 WHERE x BETWEEN 100 AND 300 AND y BETWEEN 500 AND 100

} {
  0 0 0 {SEARCH TABLE t1 USING INDEX t1_y (y>? AND y<?) (~12 rows)}
}

do_test analyze2-3.1 {
  set alphabet [list a b c d e f g h i j]
  execsql BEGIN
  for {set i 0} {$i < 1000} {incr i} {
    set str    [lindex $alphabet [expr ($i/100)%10]] 
    append str [lindex $alphabet [expr ($i/ 10)%10]]
................................................................................
    SELECT tbl,idx,group_concat(sample,' ') 
    FROM sqlite_stat2 
    WHERE idx = 't1_y' 
    GROUP BY tbl,idx
  }
} {t1 t1_y {100 299 499 699 899 ajj cjj ejj gjj ijj}}

do_eqp_test 3.3 {
  SELECT * FROM t1 WHERE x BETWEEN 100 AND 500 AND y BETWEEN 'a' AND 'b'
} {
  0 0 0 {SEARCH TABLE t1 USING INDEX t1_y (y>? AND y<?) (~50 rows)}
}
do_eqp_test 3.4 {
  SELECT * FROM t1 WHERE x BETWEEN 100 AND 400 AND y BETWEEN 'a' AND 'h'


} {
  0 0 0 {SEARCH TABLE t1 USING INDEX t1_x (x>? AND x<?) (~100 rows)}
}
do_eqp_test 3.5 {
  SELECT * FROM t1 WHERE x<'a' AND y>'h'


} {
  0 0 0 {SEARCH TABLE t1 USING INDEX t1_y (y>?) (~66 rows)}
}
do_eqp_test 3.6 {
  SELECT * FROM t1 WHERE x<444 AND y>'h'


} {
  0 0 0 {SEARCH TABLE t1 USING INDEX t1_y (y>?) (~66 rows)}
}
do_eqp_test 3.7 {
  SELECT * FROM t1 WHERE x<221 AND y>'g'

} {
  0 0 0 {SEARCH TABLE t1 USING INDEX t1_x (x<?) (~66 rows)}
}

do_test analyze2-4.1 {
  execsql { CREATE TABLE t3(a COLLATE nocase, b) }
  execsql { CREATE INDEX t3a ON t3(a) }
  execsql { CREATE INDEX t3b ON t3(b) }
  set alphabet [list A b C d E f G h I j]
  execsql BEGIN
................................................................................
    execsql { INSERT INTO t3 VALUES($str, $str) }
  }
  execsql COMMIT
  execsql ANALYZE
} {}
do_test analyze2-4.2 {
  execsql { 
    PRAGMA automatic_index=OFF;
    SELECT tbl,idx,group_concat(sample,' ') 
    FROM sqlite_stat2 
    WHERE idx = 't3a' 
    GROUP BY tbl,idx;
    PRAGMA automatic_index=ON;
  }
} {t3 t3a {AfA bEj CEj dEj EEj fEj GEj hEj IEj jEj}}
do_test analyze2-4.3 {
  execsql { 
    SELECT tbl,idx,group_concat(sample,' ') 
    FROM sqlite_stat2 
    WHERE idx = 't3b' 
    GROUP BY tbl,idx
  }
} {t3 t3b {AbA CIj EIj GIj IIj bIj dIj fIj hIj jIj}}

do_eqp_test 4.4 {
  SELECT * FROM t3 WHERE a > 'A' AND a < 'C' AND b > 'A' AND b < 'C'
} {
  0 0 0 {SEARCH TABLE t3 USING INDEX t3b (b>? AND b<?) (~11 rows)}
}
do_eqp_test 4.5 {
  SELECT * FROM t3 WHERE a > 'A' AND a < 'c' AND b > 'A' AND b < 'c'

} {
  0 0 0 {SEARCH TABLE t3 USING INDEX t3a (a>? AND a<?) (~22 rows)}
}

ifcapable utf16 {
  proc test_collate {enc lhs rhs} {
    # puts $enc
    return [string compare $lhs $rhs]
  }
  do_test analyze2-5.1 {
................................................................................
    execsql { 
      SELECT tbl,idx,group_concat(sample,' ') 
      FROM sqlite_stat2 
      WHERE tbl = 't4' 
      GROUP BY tbl,idx
    }
  } {t4 t4x {afa bej cej dej eej fej gej hej iej jej}}
  do_eqp_test 5.3 {
    SELECT * FROM t4 WHERE x>'ccc'
  } {0 0 0 {SEARCH TABLE t4 USING COVERING INDEX t4x (x>?) (~800 rows)}}
  do_eqp_test 5.4 {
    SELECT * FROM t4 AS t41, t4 AS t42 WHERE t41.x>'ccc' AND t42.x>'ggg'
  } {
    0 0 1 {SEARCH TABLE t4 AS t42 USING COVERING INDEX t4x (x>?) (~300 rows)} 
    0 1 0 {SEARCH TABLE t4 AS t41 USING COVERING INDEX t4x (x>?) (~800 rows)}
  }
  do_eqp_test 5.5 {
    SELECT * FROM t4 AS t41, t4 AS t42 WHERE t41.x>'ddd' AND t42.x>'ccc'

  } {
    0 0 0 {SEARCH TABLE t4 AS t41 USING COVERING INDEX t4x (x>?) (~700 rows)} 
    0 1 1 {SEARCH TABLE t4 AS t42 USING COVERING INDEX t4x (x>?) (~800 rows)}
  }
}

#--------------------------------------------------------------------
# These tests, analyze2-6.*, verify that the library behaves correctly
# when one of the sqlite_stat1 and sqlite_stat2 tables is missing.
#
# If the sqlite_stat1 table is not present, then the sqlite_stat2
................................................................................
} {}

do_test analyze2-6.1.1 {
  eqp {SELECT * FROM t5,t6 WHERE t5.rowid=t6.rowid AND 
       t5.a = 1 AND
       t6.a = 1 AND t6.b = 1
  }
} {0 0 1 {SEARCH TABLE t6 USING COVERING INDEX t6i (a=? AND b=?) (~9 rows)} 0 1 0 {SEARCH TABLE t5 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)}}
do_test analyze2-6.1.2 {
  db cache flush
  execsql ANALYZE
  eqp {SELECT * FROM t5,t6 WHERE t5.rowid=t6.rowid AND 
       t5.a = 1 AND
       t6.a = 1 AND t6.b = 1
  }
} {0 0 0 {SEARCH TABLE t5 USING COVERING INDEX t5i (a=?) (~1 rows)} 0 1 1 {SEARCH TABLE t6 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)}}
do_test analyze2-6.1.3 {
  sqlite3 db test.db
  eqp { SELECT * FROM t5,t6 WHERE t5.rowid=t6.rowid AND 
       t5.a = 1 AND
       t6.a = 1 AND t6.b = 1
  }
} {0 0 0 {SEARCH TABLE t5 USING COVERING INDEX t5i (a=?) (~1 rows)} 0 1 1 {SEARCH TABLE t6 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)}}
do_test analyze2-6.1.4 {
  execsql { 
    PRAGMA writable_schema = 1;
    DELETE FROM sqlite_master WHERE tbl_name = 'sqlite_stat2';
  }
  sqlite3 db test.db
  eqp { SELECT * FROM t5,t6 WHERE t5.rowid=t6.rowid AND 
       t5.a = 1 AND
       t6.a = 1 AND t6.b = 1
  }
} {0 0 0 {SEARCH TABLE t5 USING COVERING INDEX t5i (a=?) (~1 rows)} 0 1 1 {SEARCH TABLE t6 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)}}
do_test analyze2-6.1.5 {
  execsql { 
    PRAGMA writable_schema = 1;
    DELETE FROM sqlite_master WHERE tbl_name = 'sqlite_stat1';
  }
  sqlite3 db test.db
  eqp { SELECT * FROM t5,t6 WHERE t5.rowid=t6.rowid AND 
       t5.a = 1 AND
       t6.a = 1 AND t6.b = 1
  }
} {0 0 1 {SEARCH TABLE t6 USING COVERING INDEX t6i (a=? AND b=?) (~9 rows)} 0 1 0 {SEARCH TABLE t5 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)}}
do_test analyze2-6.1.6 {
  execsql { 
    PRAGMA writable_schema = 1;
    INSERT INTO sqlite_master SELECT * FROM master;
  }
  sqlite3 db test.db
  eqp { SELECT * FROM t5,t6 WHERE t5.rowid=t6.rowid AND 
       t5.a = 1 AND
       t6.a = 1 AND t6.b = 1
  }
} {0 0 0 {SEARCH TABLE t5 USING COVERING INDEX t5i (a=?) (~1 rows)} 0 1 1 {SEARCH TABLE t6 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)}}

do_test analyze2-6.2.1 {
  execsql { 
    DELETE FROM sqlite_stat1;
    DELETE FROM sqlite_stat2;
  }
  sqlite3 db test.db
  eqp { SELECT * FROM t5,t6 WHERE t5.rowid=t6.rowid AND 
        t5.a>1 AND t5.a<15 AND
        t6.a>1
  }
} {0 0 0 {SEARCH TABLE t5 USING COVERING INDEX t5i (a>? AND a<?) (~60000 rows)} 0 1 1 {SEARCH TABLE t6 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)}}
do_test analyze2-6.2.2 {
  db cache flush
  execsql ANALYZE
  eqp { SELECT * FROM t5,t6 WHERE t5.rowid=t6.rowid AND 
        t5.a>1 AND t5.a<15 AND
        t6.a>1
  }
} {0 0 1 {SEARCH TABLE t6 USING COVERING INDEX t6i (a>?) (~1 rows)} 0 1 0 {SEARCH TABLE t5 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)}}
do_test analyze2-6.2.3 {
  sqlite3 db test.db
  eqp { SELECT * FROM t5,t6 WHERE t5.rowid=t6.rowid AND 
        t5.a>1 AND t5.a<15 AND
        t6.a>1
  }
} {0 0 1 {SEARCH TABLE t6 USING COVERING INDEX t6i (a>?) (~1 rows)} 0 1 0 {SEARCH TABLE t5 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)}}
do_test analyze2-6.2.4 {
  execsql { 
    PRAGMA writable_schema = 1;
    DELETE FROM sqlite_master WHERE tbl_name = 'sqlite_stat1';
  }
  sqlite3 db test.db
  eqp { SELECT * FROM t5,t6 WHERE t5.rowid=t6.rowid AND 
        t5.a>1 AND t5.a<15 AND
        t6.a>1
  }
} {0 0 0 {SEARCH TABLE t5 USING COVERING INDEX t5i (a>? AND a<?) (~60000 rows)} 0 1 1 {SEARCH TABLE t6 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)}}
do_test analyze2-6.2.5 {
  execsql { 
    PRAGMA writable_schema = 1;
    DELETE FROM sqlite_master WHERE tbl_name = 'sqlite_stat2';
  }
  sqlite3 db test.db
  eqp { SELECT * FROM t5,t6 WHERE t5.rowid=t6.rowid AND 
        t5.a>1 AND t5.a<15 AND
        t6.a>1
  }
} {0 0 0 {SEARCH TABLE t5 USING COVERING INDEX t5i (a>? AND a<?) (~60000 rows)} 0 1 1 {SEARCH TABLE t6 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)}}
do_test analyze2-6.2.6 {
  execsql { 
    PRAGMA writable_schema = 1;
    INSERT INTO sqlite_master SELECT * FROM master;
  }
  sqlite3 db test.db
  execsql ANALYZE
  eqp { SELECT * FROM t5,t6 WHERE t5.rowid=t6.rowid AND 
        t5.a>1 AND t5.a<15 AND
        t6.a>1
  }
} {0 0 1 {SEARCH TABLE t6 USING COVERING INDEX t6i (a>?) (~1 rows)} 0 1 0 {SEARCH TABLE t5 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)}}

#--------------------------------------------------------------------
# These tests, analyze2-7.*, test that the sqlite_stat2 functionality
# works in shared-cache mode. Note that these tests reuse the database
# created for the analyze2-6.* tests.
#
ifcapable shared_cache {
................................................................................
  } {20}

  do_test analyze2-7.5 {
    eqp { SELECT * FROM t5,t6 WHERE t5.rowid=t6.rowid AND 
          t5.a>1 AND t5.a<15 AND
          t6.a>1
    } db1
  } {0 0 1 {SEARCH TABLE t6 USING COVERING INDEX t6i (a>?) (~1 rows)} 0 1 0 {SEARCH TABLE t5 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)}}
  do_test analyze2-7.6 {
    incr_schema_cookie test.db
    execsql { SELECT * FROM sqlite_master } db2
    eqp { SELECT * FROM t5,t6 WHERE t5.rowid=t6.rowid AND 
          t5.a>1 AND t5.a<15 AND
          t6.a>1
    } db2
  } {0 0 1 {SEARCH TABLE t6 USING COVERING INDEX t6i (a>?) (~1 rows)} 0 1 0 {SEARCH TABLE t5 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)}}
  do_test analyze2-7.7 {
    incr_schema_cookie test.db
    execsql { SELECT * FROM sqlite_master } db1
    eqp { SELECT * FROM t5,t6 WHERE t5.rowid=t6.rowid AND 
          t5.a>1 AND t5.a<15 AND
          t6.a>1
    } db1
  } {0 0 1 {SEARCH TABLE t6 USING COVERING INDEX t6i (a>?) (~1 rows)} 0 1 0 {SEARCH TABLE t5 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)}}

  do_test analyze2-7.8 {
    execsql { DELETE FROM sqlite_stat2 } db2
    execsql { SELECT * FROM sqlite_master } db1
    eqp { SELECT * FROM t5,t6 WHERE t5.rowid=t6.rowid AND 
          t5.a>1 AND t5.a<15 AND
          t6.a>1
    } db1
  } {0 0 1 {SEARCH TABLE t6 USING COVERING INDEX t6i (a>?) (~1 rows)} 0 1 0 {SEARCH TABLE t5 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)}}
  do_test analyze2-7.9 {
    execsql { SELECT * FROM sqlite_master } db2
    eqp { SELECT * FROM t5,t6 WHERE t5.rowid=t6.rowid AND 
          t5.a>1 AND t5.a<15 AND
          t6.a>1
    } db2
  } {0 0 1 {SEARCH TABLE t6 USING COVERING INDEX t6i (a>?) (~1 rows)} 0 1 0 {SEARCH TABLE t5 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)}}

  do_test analyze2-7.10 {
    incr_schema_cookie test.db
    execsql { SELECT * FROM sqlite_master } db1
    eqp { SELECT * FROM t5,t6 WHERE t5.rowid=t6.rowid AND 
          t5.a>1 AND t5.a<15 AND
          t6.a>1
    } db1
  } {0 0 0 {SEARCH TABLE t5 USING COVERING INDEX t5i (a>? AND a<?) (~1 rows)} 0 1 1 {SEARCH TABLE t6 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)}}

  db1 close
  db2 close
  sqlite3_enable_shared_cache $::enable_shared_cache
}

finish_test

Changes to test/indexedby.test.

36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51



52
53
54
55
56
57
58
..
76
77
78
79
80
81
82
83
84
85
86

87
88

89

90
91

92
93
94
95
96
97
98
99
100
101
102
103
104
105

106
107
108

109
110
111
112
113
114
115
116
117
118
119
120
121
122



123
124
125




126
127
128
129
130
131
132
...
134
135
136
137
138
139
140
141
142
143
144
145
146

147
148
149

150
151
152
153
154
155
156
...
161
162
163
164
165
166
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
194
195
196
197
198
199
200
201
202
203
204
205
206
207

208
209

210

211
212

213
214
215
216
217
218
219
#
proc EQP {sql} {
  uplevel "execsql {EXPLAIN QUERY PLAN $sql}"
}

# These tests are to check that "EXPLAIN QUERY PLAN" is working as expected.
#
do_test indexedby-1.2 {
  EQP { select * from t1 WHERE a = 10; }
} {0 0 {TABLE t1 WITH INDEX i1}}
do_test indexedby-1.3 {
  EQP { select * from t1 ; }
} {0 0 {TABLE t1}}
do_test indexedby-1.4 {
  EQP { select * from t1, t2 WHERE c = 10; }
} {0 1 {TABLE t2 WITH INDEX i3} 1 0 {TABLE t1}}




# Parser tests. Test that an INDEXED BY or NOT INDEX clause can be 
# attached to a table in the FROM clause, but not to a sub-select or
# SQL view. Also test that specifying an index that does not exist or
# is attached to a different table is detected as an error.
# 
do_test indexedby-2.1 {
................................................................................
} {1 {near "WHERE": syntax error}}
do_test indexedby-2.7 {
  catchsql { SELECT * FROM v1 INDEXED BY i1 WHERE a = 'one' }
} {1 {no such index: i1}}

# Tests for single table cases.
#
do_test indexedby-3.1 {
  EQP { SELECT * FROM t1 NOT INDEXED WHERE a = 'one' AND b = 'two'}
} {0 0 {TABLE t1}}
do_test indexedby-3.2 {

  EQP { SELECT * FROM t1 INDEXED BY i1 WHERE a = 'one' AND b = 'two'}
} {0 0 {TABLE t1 WITH INDEX i1}}

do_test indexedby-3.3 {

  EQP { SELECT * FROM t1 INDEXED BY i2 WHERE a = 'one' AND b = 'two'}
} {0 0 {TABLE t1 WITH INDEX i2}}

do_test indexedby-3.4 {
  catchsql { SELECT * FROM t1 INDEXED BY i2 WHERE a = 'one' }
} {1 {cannot use index: i2}}
do_test indexedby-3.5 {
  catchsql { SELECT * FROM t1 INDEXED BY i2 ORDER BY a }
} {1 {cannot use index: i2}}
do_test indexedby-3.6 {
  catchsql { SELECT * FROM t1 INDEXED BY i1 WHERE a = 'one' }
} {0 {}}
do_test indexedby-3.7 {
  catchsql { SELECT * FROM t1 INDEXED BY i1 ORDER BY a }
} {0 {}}

do_test indexedby-3.8 {

  EQP { SELECT * FROM t3 INDEXED BY sqlite_autoindex_t3_1 ORDER BY e }
} {0 0 {TABLE t3 WITH INDEX sqlite_autoindex_t3_1 ORDER BY}}
do_test indexedby-3.9 {

  EQP { SELECT * FROM t3 INDEXED BY sqlite_autoindex_t3_1 WHERE e = 10 }
} {0 0 {TABLE t3 WITH INDEX sqlite_autoindex_t3_1}}
do_test indexedby-3.10 {
  catchsql { SELECT * FROM t3 INDEXED BY sqlite_autoindex_t3_1 WHERE f = 10 }
} {1 {cannot use index: sqlite_autoindex_t3_1}}
do_test indexedby-3.11 {
  catchsql { SELECT * FROM t3 INDEXED BY sqlite_autoindex_t3_2 WHERE f = 10 }
} {1 {no such index: sqlite_autoindex_t3_2}}

# Tests for multiple table cases.
#
do_test indexedby-4.1 {
  EQP { SELECT * FROM t1, t2 WHERE a = c }
} {0 0 {TABLE t1} 1 1 {TABLE t2 WITH INDEX i3}}



do_test indexedby-4.2 {
  EQP { SELECT * FROM t1 INDEXED BY i1, t2 WHERE a = c }
} {0 1 {TABLE t2} 1 0 {TABLE t1 WITH INDEX i1}}




do_test indexedby-4.3 {
  catchsql {
    SELECT * FROM t1 INDEXED BY i1, t2 INDEXED BY i3 WHERE a=c
  }
} {1 {cannot use index: i1}}
do_test indexedby-4.4 {
  catchsql {
................................................................................
  }
} {1 {cannot use index: i3}}

# Test embedding an INDEXED BY in a CREATE VIEW statement. This block
# also tests that nothing bad happens if an index refered to by
# a CREATE VIEW statement is dropped and recreated.
#
do_test indexedby-5.1 {
  execsql {
    CREATE VIEW v2 AS SELECT * FROM t1 INDEXED BY i1 WHERE a > 5;
  }
  EQP { SELECT * FROM v2 }
} {0 0 {TABLE t1 WITH INDEX i1}}

do_test indexedby-5.2 {
  EQP { SELECT * FROM v2 WHERE b = 10 }
} {0 0 {TABLE t1 WITH INDEX i1}}

do_test indexedby-5.3 {
  execsql { DROP INDEX i1 }
  catchsql { SELECT * FROM v2 }
} {1 {no such index: i1}}
do_test indexedby-5.4 {
  # Recreate index i1 in such a way as it cannot be used by the view query.
  execsql { CREATE INDEX i1 ON t1(b) }
................................................................................
  # be used by the query.
  execsql { DROP INDEX i1 ; CREATE INDEX i1 ON t1(a) }
  catchsql { SELECT * FROM v2 }
} {0 {}}

# Test that "NOT INDEXED" may use the rowid index, but not others.
# 
do_test indexedby-6.1 {
  EQP { SELECT * FROM t1 WHERE b = 10 ORDER BY rowid }
} {0 0 {TABLE t1 WITH INDEX i2 ORDER BY}}
do_test indexedby-6.2 {
  EQP { SELECT * FROM t1 NOT INDEXED WHERE b = 10 ORDER BY rowid }
} {0 0 {TABLE t1 USING PRIMARY KEY ORDER BY}}

# Test that "INDEXED BY" can be used in a DELETE statement.
# 
do_test indexedby-7.1 {
  EQP { DELETE FROM t1 WHERE a = 5 }
} {0 0 {TABLE t1 WITH INDEX i1}}
do_test indexedby-7.2 {
  EQP { DELETE FROM t1 NOT INDEXED WHERE a = 5 }
} {0 0 {TABLE t1}}
do_test indexedby-7.3 {
  EQP { DELETE FROM t1 INDEXED BY i1 WHERE a = 5 }
} {0 0 {TABLE t1 WITH INDEX i1}}
do_test indexedby-7.4 {
  EQP { DELETE FROM t1 INDEXED BY i1 WHERE a = 5 AND b = 10}
} {0 0 {TABLE t1 WITH INDEX i1}}
do_test indexedby-7.5 {
  EQP { DELETE FROM t1 INDEXED BY i2 WHERE a = 5 AND b = 10}
} {0 0 {TABLE t1 WITH INDEX i2}}
do_test indexedby-7.6 {
  catchsql { DELETE FROM t1 INDEXED BY i2 WHERE a = 5}
} {1 {cannot use index: i2}}

# Test that "INDEXED BY" can be used in an UPDATE statement.
# 
do_test indexedby-8.1 {
  EQP { UPDATE t1 SET rowid=rowid+1 WHERE a = 5 }
} {0 0 {TABLE t1 WITH INDEX i1}}
do_test indexedby-8.2 {
  EQP { UPDATE t1 NOT INDEXED SET rowid=rowid+1 WHERE a = 5 }
} {0 0 {TABLE t1}}
do_test indexedby-8.3 {
  EQP { UPDATE t1 INDEXED BY i1 SET rowid=rowid+1 WHERE a = 5 }
} {0 0 {TABLE t1 WITH INDEX i1}}
do_test indexedby-8.4 {

  EQP { UPDATE t1 INDEXED BY i1 SET rowid=rowid+1 WHERE a = 5 AND b = 10}
} {0 0 {TABLE t1 WITH INDEX i1}}

do_test indexedby-8.5 {

  EQP { UPDATE t1 INDEXED BY i2 SET rowid=rowid+1 WHERE a = 5 AND b = 10}
} {0 0 {TABLE t1 WITH INDEX i2}}

do_test indexedby-8.6 {
  catchsql { UPDATE t1 INDEXED BY i2 SET rowid=rowid+1 WHERE a = 5}
} {1 {cannot use index: i2}}

# Test that bug #3560 is fixed.
#
do_test indexedby-9.1 {







|
|
|
|
|
|
|
|
|
>
>
>







 







|
|
|
|
>
|
<
>
|
>
|
<
>













|
>
|
|
|
>
|
|









|
|
|
>
>
>
|
|
<
>
>
>
>







 







|
<
|
<
|
<
>
|
|
<
>







 







|
|
|
|
|
|



|
|
|
|
|
|
|
|
|
|
|
|
|
|
|






|
|
|
|
|
|
|
|
|
|
>
|
<
>
|
>
|
<
>







36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
..
79
80
81
82
83
84
85
86
87
88
89
90
91

92
93
94
95

96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
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
140
141
142
143
144
145
...
147
148
149
150
151
152
153
154

155

156

157
158
159

160
161
162
163
164
165
166
167
...
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220

221
222
223
224

225
226
227
228
229
230
231
232
#
proc EQP {sql} {
  uplevel "execsql {EXPLAIN QUERY PLAN $sql}"
}

# These tests are to check that "EXPLAIN QUERY PLAN" is working as expected.
#
do_execsql_test indexedby-1.2 {
  EXPLAIN QUERY PLAN select * from t1 WHERE a = 10; 
} {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (a=?) (~10 rows)}}
do_execsql_test indexedby-1.3 {
  EXPLAIN QUERY PLAN select * from t1 ; 
} {0 0 0 {SCAN TABLE t1 (~1000000 rows)}}
do_execsql_test indexedby-1.4 {
  EXPLAIN QUERY PLAN select * from t1, t2 WHERE c = 10; 
} {
  0 0 1 {SEARCH TABLE t2 USING INDEX i3 (c=?) (~10 rows)} 
  0 1 0 {SCAN TABLE t1 (~1000000 rows)}
}

# Parser tests. Test that an INDEXED BY or NOT INDEX clause can be 
# attached to a table in the FROM clause, but not to a sub-select or
# SQL view. Also test that specifying an index that does not exist or
# is attached to a different table is detected as an error.
# 
do_test indexedby-2.1 {
................................................................................
} {1 {near "WHERE": syntax error}}
do_test indexedby-2.7 {
  catchsql { SELECT * FROM v1 INDEXED BY i1 WHERE a = 'one' }
} {1 {no such index: i1}}

# Tests for single table cases.
#
do_execsql_test indexedby-3.1 {
  EXPLAIN QUERY PLAN SELECT * FROM t1 NOT INDEXED WHERE a = 'one' AND b = 'two'
} {0 0 0 {SCAN TABLE t1 (~10000 rows)}}
do_execsql_test indexedby-3.2 {
  EXPLAIN QUERY PLAN 
  SELECT * FROM t1 INDEXED BY i1 WHERE a = 'one' AND b = 'two'

} {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (a=?) (~2 rows)}}
do_execsql_test indexedby-3.3 {
  EXPLAIN QUERY PLAN 
  SELECT * FROM t1 INDEXED BY i2 WHERE a = 'one' AND b = 'two'

} {0 0 0 {SEARCH TABLE t1 USING INDEX i2 (b=?) (~2 rows)}}
do_test indexedby-3.4 {
  catchsql { SELECT * FROM t1 INDEXED BY i2 WHERE a = 'one' }
} {1 {cannot use index: i2}}
do_test indexedby-3.5 {
  catchsql { SELECT * FROM t1 INDEXED BY i2 ORDER BY a }
} {1 {cannot use index: i2}}
do_test indexedby-3.6 {
  catchsql { SELECT * FROM t1 INDEXED BY i1 WHERE a = 'one' }
} {0 {}}
do_test indexedby-3.7 {
  catchsql { SELECT * FROM t1 INDEXED BY i1 ORDER BY a }
} {0 {}}

do_execsql_test indexedby-3.8 {
  EXPLAIN QUERY PLAN 
  SELECT * FROM t3 INDEXED BY sqlite_autoindex_t3_1 ORDER BY e 
} {0 0 0 {SCAN TABLE t3 USING INDEX sqlite_autoindex_t3_1 (~1000000 rows)}}
do_execsql_test indexedby-3.9 {
  EXPLAIN QUERY PLAN 
  SELECT * FROM t3 INDEXED BY sqlite_autoindex_t3_1 WHERE e = 10 
} {0 0 0 {SEARCH TABLE t3 USING INDEX sqlite_autoindex_t3_1 (e=?) (~1 rows)}}
do_test indexedby-3.10 {
  catchsql { SELECT * FROM t3 INDEXED BY sqlite_autoindex_t3_1 WHERE f = 10 }
} {1 {cannot use index: sqlite_autoindex_t3_1}}
do_test indexedby-3.11 {
  catchsql { SELECT * FROM t3 INDEXED BY sqlite_autoindex_t3_2 WHERE f = 10 }
} {1 {no such index: sqlite_autoindex_t3_2}}

# Tests for multiple table cases.
#
do_execsql_test indexedby-4.1 {
  EXPLAIN QUERY PLAN SELECT * FROM t1, t2 WHERE a = c 
} {
  0 0 0 {SCAN TABLE t1 (~1000000 rows)} 
  0 1 1 {SEARCH TABLE t2 USING INDEX i3 (c=?) (~10 rows)}
}
do_execsql_test indexedby-4.2 {
  EXPLAIN QUERY PLAN SELECT * FROM t1 INDEXED BY i1, t2 WHERE a = c 

} {
  0 0 1 {SCAN TABLE t2 (~1000000 rows)} 
  0 1 0 {SEARCH TABLE t1 USING INDEX i1 (a=?) (~10 rows)}
}
do_test indexedby-4.3 {
  catchsql {
    SELECT * FROM t1 INDEXED BY i1, t2 INDEXED BY i3 WHERE a=c
  }
} {1 {cannot use index: i1}}
do_test indexedby-4.4 {
  catchsql {
................................................................................
  }
} {1 {cannot use index: i3}}

# Test embedding an INDEXED BY in a CREATE VIEW statement. This block
# also tests that nothing bad happens if an index refered to by
# a CREATE VIEW statement is dropped and recreated.
#
do_execsql_test indexedby-5.1 {

  CREATE VIEW v2 AS SELECT * FROM t1 INDEXED BY i1 WHERE a > 5;

  EXPLAIN QUERY PLAN SELECT * FROM v2 

} {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (a>?) (~250000 rows)}}
do_execsql_test indexedby-5.2 {
  EXPLAIN QUERY PLAN SELECT * FROM v2 WHERE b = 10 

} {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (a>?) (~25000 rows)}}
do_test indexedby-5.3 {
  execsql { DROP INDEX i1 }
  catchsql { SELECT * FROM v2 }
} {1 {no such index: i1}}
do_test indexedby-5.4 {
  # Recreate index i1 in such a way as it cannot be used by the view query.
  execsql { CREATE INDEX i1 ON t1(b) }
................................................................................
  # be used by the query.
  execsql { DROP INDEX i1 ; CREATE INDEX i1 ON t1(a) }
  catchsql { SELECT * FROM v2 }
} {0 {}}

# Test that "NOT INDEXED" may use the rowid index, but not others.
# 
do_execsql_test indexedby-6.1 {
  EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE b = 10 ORDER BY rowid 
} {0 0 0 {SEARCH TABLE t1 USING INDEX i2 (b=?) (~10 rows)}}
do_execsql_test indexedby-6.2 {
  EXPLAIN QUERY PLAN SELECT * FROM t1 NOT INDEXED WHERE b = 10 ORDER BY rowid 
} {0 0 0 {SCAN TABLE t1 USING INTEGER PRIMARY KEY (~100000 rows)}}

# Test that "INDEXED BY" can be used in a DELETE statement.
# 
do_execsql_test indexedby-7.1 {
  EXPLAIN QUERY PLAN DELETE FROM t1 WHERE a = 5 
} {0 0 0 {SEARCH TABLE t1 USING COVERING INDEX i1 (a=?) (~10 rows)}}
do_execsql_test indexedby-7.2 {
  EXPLAIN QUERY PLAN DELETE FROM t1 NOT INDEXED WHERE a = 5 
} {0 0 0 {SCAN TABLE t1 (~100000 rows)}}
do_execsql_test indexedby-7.3 {
  EXPLAIN QUERY PLAN DELETE FROM t1 INDEXED BY i1 WHERE a = 5 
} {0 0 0 {SEARCH TABLE t1 USING COVERING INDEX i1 (a=?) (~10 rows)}}
do_execsql_test indexedby-7.4 {
  EXPLAIN QUERY PLAN DELETE FROM t1 INDEXED BY i1 WHERE a = 5 AND b = 10
} {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (a=?) (~2 rows)}}
do_execsql_test indexedby-7.5 {
  EXPLAIN QUERY PLAN DELETE FROM t1 INDEXED BY i2 WHERE a = 5 AND b = 10
} {0 0 0 {SEARCH TABLE t1 USING INDEX i2 (b=?) (~2 rows)}}
do_test indexedby-7.6 {
  catchsql { DELETE FROM t1 INDEXED BY i2 WHERE a = 5}
} {1 {cannot use index: i2}}

# Test that "INDEXED BY" can be used in an UPDATE statement.
# 
do_execsql_test indexedby-8.1 {
  EXPLAIN QUERY PLAN UPDATE t1 SET rowid=rowid+1 WHERE a = 5 
} {0 0 0 {SEARCH TABLE t1 USING COVERING INDEX i1 (a=?) (~10 rows)}}
do_execsql_test indexedby-8.2 {
  EXPLAIN QUERY PLAN UPDATE t1 NOT INDEXED SET rowid=rowid+1 WHERE a = 5 
} {0 0 0 {SCAN TABLE t1 (~100000 rows)}}
do_execsql_test indexedby-8.3 {
  EXPLAIN QUERY PLAN UPDATE t1 INDEXED BY i1 SET rowid=rowid+1 WHERE a = 5 
} {0 0 0 {SEARCH TABLE t1 USING COVERING INDEX i1 (a=?) (~10 rows)}}
do_execsql_test indexedby-8.4 {
  EXPLAIN QUERY PLAN 
  UPDATE t1 INDEXED BY i1 SET rowid=rowid+1 WHERE a = 5 AND b = 10

} {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (a=?) (~2 rows)}}
do_execsql_test indexedby-8.5 {
  EXPLAIN QUERY PLAN 
  UPDATE t1 INDEXED BY i2 SET rowid=rowid+1 WHERE a = 5 AND b = 10

} {0 0 0 {SEARCH TABLE t1 USING INDEX i2 (b=?) (~2 rows)}}
do_test indexedby-8.6 {
  catchsql { UPDATE t1 INDEXED BY i2 SET rowid=rowid+1 WHERE a = 5}
} {1 {cannot use index: i2}}

# Test that bug #3560 is fixed.
#
do_test indexedby-9.1 {

Changes to test/tester.tcl.

333
334
335
336
337
338
339




340
341
342
343
344
345
346
}
    
proc do_execsql_test {testname sql result} {
  uplevel do_test $testname [list "execsql {$sql}"] [list $result]
}
proc do_catchsql_test {testname sql result} {
  uplevel do_test $testname [list "catchsql {$sql}"] [list $result]




}


# Run an SQL script.  
# Return the number of microseconds per statement.
#
proc speed_trial {name numstmt units sql} {







>
>
>
>







333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
}
    
proc do_execsql_test {testname sql result} {
  uplevel do_test $testname [list "execsql {$sql}"] [list $result]
}
proc do_catchsql_test {testname sql result} {
  uplevel do_test $testname [list "catchsql {$sql}"] [list $result]
}
proc do_eqp_test {name sql res} {
  uplevel do_execsql_test $name [list "EXPLAIN QUERY PLAN $sql"] \
     [list [string trim $res]]
}


# Run an SQL script.  
# Return the number of microseconds per statement.
#
proc speed_trial {name numstmt units sql} {