/ Check-in [cabf2187]
Login

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

Overview
Comment:Code refactoring to try to shift FROM-clause subquery manifesting until after the query planner runs. Except this does not currently work because the query planner needs an estimated of the number of rows in the manifested table. Work in progress.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | view-optimization
Files: files | file ages | folders
SHA1: cabf218716e3ba584dc27781ba5e2f9f00eab74c
User & Date: drh 2015-06-08 22:59:36
Context
2015-06-10
17:20
Resolve FROM-clause subqueries after query planning instead of before. Greatly reduce the estimated cost of automatic indexes for VIEWs and ephemeral tables since performance problems there cannot be mitigated via a CREATE INDEX. check-in: a1eaf171 user: drh tags: view-optimization
2015-06-08
22:59
Code refactoring to try to shift FROM-clause subquery manifesting until after the query planner runs. Except this does not currently work because the query planner needs an estimated of the number of rows in the manifested table. Work in progress. check-in: cabf2187 user: drh tags: view-optimization
15:08
Factor out the TreeView parse tree printing module into a separate file. check-in: c32ce54c user: drh tags: view-optimization
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/delete.c.

393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
      addrEphOpen = sqlite3VdbeAddOp2(v, OP_OpenEphemeral, iEphCur, nPk);
      sqlite3VdbeSetP4KeyInfo(pParse, pPk);
    }
  
    /* Construct a query to find the rowid or primary key for every row
    ** to be deleted, based on the WHERE clause.
    */
    pWInfo = sqlite3WhereBegin(pParse, pTabList, pWhere, 0, 0, 
                               WHERE_ONEPASS_DESIRED|WHERE_DUPLICATES_OK,
                               iTabCur+1);
    if( pWInfo==0 ) goto delete_from_cleanup;
    okOnePass = sqlite3WhereOkOnePass(pWInfo, aiCurOnePass);
  
    /* Keep track of the number of rows to be deleted */
    if( db->flags & SQLITE_CountRows ){







|







393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
      addrEphOpen = sqlite3VdbeAddOp2(v, OP_OpenEphemeral, iEphCur, nPk);
      sqlite3VdbeSetP4KeyInfo(pParse, pPk);
    }
  
    /* Construct a query to find the rowid or primary key for every row
    ** to be deleted, based on the WHERE clause.
    */
    pWInfo = sqlite3WhereBegin(pParse, 0, pTabList, pWhere, 0, 0, 
                               WHERE_ONEPASS_DESIRED|WHERE_DUPLICATES_OK,
                               iTabCur+1);
    if( pWInfo==0 ) goto delete_from_cleanup;
    okOnePass = sqlite3WhereOkOnePass(pWInfo, aiCurOnePass);
  
    /* Keep track of the number of rows to be deleted */
    if( db->flags & SQLITE_CountRows ){

Changes to src/fkey.c.

627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
  sNameContext.pSrcList = pSrc;
  sNameContext.pParse = pParse;
  sqlite3ResolveExprNames(&sNameContext, pWhere);

  /* Create VDBE to loop through the entries in pSrc that match the WHERE
  ** clause. For each row found, increment either the deferred or immediate
  ** foreign key constraint counter. */
  pWInfo = sqlite3WhereBegin(pParse, pSrc, pWhere, 0, 0, 0, 0);
  sqlite3VdbeAddOp2(v, OP_FkCounter, pFKey->isDeferred, nIncr);
  if( pWInfo ){
    sqlite3WhereEnd(pWInfo);
  }

  /* Clean up the WHERE clause constructed above. */
  sqlite3ExprDelete(db, pWhere);







|







627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
  sNameContext.pSrcList = pSrc;
  sNameContext.pParse = pParse;
  sqlite3ResolveExprNames(&sNameContext, pWhere);

  /* Create VDBE to loop through the entries in pSrc that match the WHERE
  ** clause. For each row found, increment either the deferred or immediate
  ** foreign key constraint counter. */
  pWInfo = sqlite3WhereBegin(pParse, 0, pSrc, pWhere, 0, 0, 0, 0);
  sqlite3VdbeAddOp2(v, OP_FkCounter, pFKey->isDeferred, nIncr);
  if( pWInfo ){
    sqlite3WhereEnd(pWInfo);
  }

  /* Clean up the WHERE clause constructed above. */
  sqlite3ExprDelete(db, pWhere);

Changes to src/select.c.

3784
3785
3786
3787
3788
3789
3790



















































































































3791
3792
3793
3794
3795
3796
3797
....
4880
4881
4882
4883
4884
4885
4886
4887
4888
4889
4890
4891
4892
4893
4894
4895
4896
4897
4898
4899
4900
4901
4902
4903
4904
4905
4906
4907
4908
4909
4910
4911
4912
4913
4914
4915
4916
4917
4918
4919
4920
4921
4922
4923
4924
4925
4926
4927
4928
4929
4930
4931
4932
4933
4934
4935
4936
4937
4938
4939
4940
4941
4942
4943
4944
4945
4946
4947
4948
4949
4950
4951
4952
4953
4954
4955
4956
4957
4958
4959
4960
4961
4962
4963
4964
4965
4966
4967
4968
4969
4970
4971
4972
4973
4974
4975
4976
4977
4978
4979
4980
4981
4982
4983
4984
4985
4986




4987
4988
4989
4990
4991
4992
4993
4994
4995
4996
....
5082
5083
5084
5085
5086
5087
5088
5089
5090
5091
5092
5093
5094
5095
5096
....
5238
5239
5240
5241
5242
5243
5244
5245
5246
5247
5248
5249
5250
5251
5252
....
5533
5534
5535
5536
5537
5538
5539
5540
5541
5542
5543
5544
5545
5546
5547
      pSubq = pSubq->pPrior;
    }
  }
  return nChng;
}
#endif /* !defined(SQLITE_OMIT_SUBQUERY) || !defined(SQLITE_OMIT_VIEW) */




















































































































/*
** Based on the contents of the AggInfo structure indicated by the first
** argument, this function checks if the following are true:
**
**    * the query contains just a single aggregate function,
**    * the aggregate function is either min() or max(), and
**    * the argument to the aggregate function is a column value.
................................................................................
    SELECTTRACE(1,pParse,p,("end compound-select processing\n"));
    pParse->nSelectIndent--;
#endif
    return rc;
  }
#endif

  /* Generate code for all sub-queries in the FROM clause
  */
#if !defined(SQLITE_OMIT_SUBQUERY) || !defined(SQLITE_OMIT_VIEW)
  for(i=0; i<pTabList->nSrc; i++){
    struct SrcList_item *pItem = &pTabList->a[i];
    SelectDest dest;
    Select *pSub = pItem->pSelect;
    if( pSub==0 ) continue;

    /* Sometimes the code for a subquery will be generated more than
    ** once, if the subquery is part of the WHERE clause in a LEFT JOIN,
    ** for example.  In that case, do not regenerate the code to manifest
    ** a view or the co-routine to implement a view.  The first instance
    ** is sufficient, though the subroutine to manifest the view does need
    ** to be invoked again. */
    if( pItem->addrFillSub ){
      if( pItem->viaCoroutine==0 ){
        sqlite3VdbeAddOp2(v, OP_Gosub, pItem->regReturn, pItem->addrFillSub);
      }
      continue;
    }

    /* Increment Parse.nHeight by the height of the largest expression
    ** tree referred to by this, the parent select. The child select
    ** may contain expression trees of at most
    ** (SQLITE_MAX_EXPR_DEPTH-Parse.nHeight) height. This is a bit
    ** more conservative than necessary, but much easier than enforcing
    ** an exact limit.
    */
    pParse->nHeight += sqlite3SelectExprHeight(p);

    /* Make copies of constant WHERE-clause terms in the outer query down
    ** inside the subquery.  This can help the subquery to run more efficiently.
    */
    if( (pItem->jointype & JT_OUTER)==0
     && pushDownWhereTerms(db, pSub, p->pWhere, pItem->iCursor)
    ){
#if SELECTTRACE_ENABLED
      if( sqlite3SelectTrace & 0x100 ){
        SELECTTRACE(0x100,pParse,p,("After WHERE-clause push-down:\n"));
        sqlite3TreeViewSelect(0, p, 0);
      }
#endif
    }

    /* Generate code to implement the subquery
    */
    if( pTabList->nSrc==1
     && (p->selFlags & SF_All)==0
     && OptimizationEnabled(db, SQLITE_SubqCoroutine)
    ){
      /* Implement a co-routine that will return a single row of the result
      ** set on each invocation.
      */
      int addrTop = sqlite3VdbeCurrentAddr(v)+1;
      pItem->regReturn = ++pParse->nMem;
      sqlite3VdbeAddOp3(v, OP_InitCoroutine, pItem->regReturn, 0, addrTop);
      VdbeComment((v, "%s", pItem->pTab->zName));
      pItem->addrFillSub = addrTop;
      sqlite3SelectDestInit(&dest, SRT_Coroutine, pItem->regReturn);
      explainSetInteger(pItem->iSelectId, (u8)pParse->iNextSelectId);
      sqlite3Select(pParse, pSub, &dest);
      pItem->pTab->nRowLogEst = sqlite3LogEst(pSub->nSelectRow);
      pItem->viaCoroutine = 1;
      pItem->regResult = dest.iSdst;
      sqlite3VdbeAddOp1(v, OP_EndCoroutine, pItem->regReturn);
      sqlite3VdbeJumpHere(v, addrTop-1);
      sqlite3ClearTempRegCache(pParse);
    }else{
      /* Generate a subroutine that will fill an ephemeral table with
      ** the content of this subquery.  pItem->addrFillSub will point
      ** to the address of the generated subroutine.  pItem->regReturn
      ** is a register allocated to hold the subroutine return address
      */
      int topAddr;
      int onceAddr = 0;
      int retAddr;
      assert( pItem->addrFillSub==0 );
      pItem->regReturn = ++pParse->nMem;
      topAddr = sqlite3VdbeAddOp2(v, OP_Integer, 0, pItem->regReturn);
      pItem->addrFillSub = topAddr+1;
      if( pItem->isCorrelated==0 ){
        /* If the subquery is not correlated and if we are not inside of
        ** a trigger, then we only need to compute the value of the subquery
        ** once. */
        onceAddr = sqlite3CodeOnce(pParse); VdbeCoverage(v);
        VdbeComment((v, "materialize \"%s\"", pItem->pTab->zName));
      }else{
        VdbeNoopComment((v, "materialize \"%s\"", pItem->pTab->zName));
      }
      sqlite3SelectDestInit(&dest, SRT_EphemTab, pItem->iCursor);
      explainSetInteger(pItem->iSelectId, (u8)pParse->iNextSelectId);
      sqlite3Select(pParse, pSub, &dest);
      pItem->pTab->nRowLogEst = sqlite3LogEst(pSub->nSelectRow);
      if( onceAddr ) sqlite3VdbeJumpHere(v, onceAddr);
      retAddr = sqlite3VdbeAddOp1(v, OP_Return, pItem->regReturn);
      VdbeComment((v, "end %s", pItem->pTab->zName));
      sqlite3VdbeChangeP1(v, topAddr, retAddr);
      sqlite3ClearTempRegCache(pParse);
    }




    if( db->mallocFailed ) goto select_end;
    pParse->nHeight -= sqlite3SelectExprHeight(p);
  }
#endif

  /* Various elements of the SELECT copied into local variables for
  ** convenience */
  pEList = p->pEList;
  pWhere = p->pWhere;
  pGroupBy = p->pGroupBy;
................................................................................
  }

  if( !isAgg && pGroupBy==0 ){
    /* No aggregate functions and no GROUP BY clause */
    u16 wctrlFlags = (sDistinct.isTnct ? WHERE_WANT_DISTINCT : 0);

    /* Begin the database scan. */
    pWInfo = sqlite3WhereBegin(pParse, pTabList, pWhere, sSort.pOrderBy,
                               p->pEList, wctrlFlags, 0);
    if( pWInfo==0 ) goto select_end;
    if( sqlite3WhereOutputRowCount(pWInfo) < p->nSelectRow ){
      p->nSelectRow = sqlite3WhereOutputRowCount(pWInfo);
    }
    if( sDistinct.isTnct && sqlite3WhereIsDistinct(pWInfo) ){
      sDistinct.eTnctType = sqlite3WhereIsDistinct(pWInfo);
................................................................................

      /* Begin a loop that will extract all source rows in GROUP BY order.
      ** This might involve two separate loops with an OP_Sort in between, or
      ** it might be a single loop that uses an index to extract information
      ** in the right order to begin with.
      */
      sqlite3VdbeAddOp2(v, OP_Gosub, regReset, addrReset);
      pWInfo = sqlite3WhereBegin(pParse, pTabList, pWhere, pGroupBy, 0,
          WHERE_GROUPBY | (orderByGrp ? WHERE_SORTBYGROUP : 0), 0
      );
      if( pWInfo==0 ) goto select_end;
      if( sqlite3WhereIsOrdered(pWInfo)==pGroupBy->nExpr ){
        /* The optimizer is able to deliver rows in group by order so
        ** we do not have to sort.  The OP_OpenEphemeral table will be
        ** cancelled later because we still need to use the pKeyInfo
................................................................................
        }
  
        /* This case runs if the aggregate has no GROUP BY clause.  The
        ** processing is much simpler since there is only a single row
        ** of output.
        */
        resetAccumulator(pParse, &sAggInfo);
        pWInfo = sqlite3WhereBegin(pParse, pTabList, pWhere, pMinMax,0,flag,0);
        if( pWInfo==0 ){
          sqlite3ExprListDelete(db, pDel);
          goto select_end;
        }
        updateAccumulator(pParse, &sAggInfo);
        assert( pMinMax==0 || pMinMax->nExpr==1 );
        if( sqlite3WhereIsOrdered(pWInfo)>0 ){







>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>







 







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







 







|







 







|







 







|







3784
3785
3786
3787
3788
3789
3790
3791
3792
3793
3794
3795
3796
3797
3798
3799
3800
3801
3802
3803
3804
3805
3806
3807
3808
3809
3810
3811
3812
3813
3814
3815
3816
3817
3818
3819
3820
3821
3822
3823
3824
3825
3826
3827
3828
3829
3830
3831
3832
3833
3834
3835
3836
3837
3838
3839
3840
3841
3842
3843
3844
3845
3846
3847
3848
3849
3850
3851
3852
3853
3854
3855
3856
3857
3858
3859
3860
3861
3862
3863
3864
3865
3866
3867
3868
3869
3870
3871
3872
3873
3874
3875
3876
3877
3878
3879
3880
3881
3882
3883
3884
3885
3886
3887
3888
3889
3890
3891
3892
3893
3894
3895
3896
3897
3898
3899
3900
3901
3902
3903
3904
3905
3906
3907
3908
3909
3910
3911
3912
....
4995
4996
4997
4998
4999
5000
5001










































5002

























































5003
5004
5005
5006
5007


5008
5009
5010
5011
5012
5013
5014
....
5100
5101
5102
5103
5104
5105
5106
5107
5108
5109
5110
5111
5112
5113
5114
....
5256
5257
5258
5259
5260
5261
5262
5263
5264
5265
5266
5267
5268
5269
5270
....
5551
5552
5553
5554
5555
5556
5557
5558
5559
5560
5561
5562
5563
5564
5565
      pSubq = pSubq->pPrior;
    }
  }
  return nChng;
}
#endif /* !defined(SQLITE_OMIT_SUBQUERY) || !defined(SQLITE_OMIT_VIEW) */

/*
** Generate code for all sub-queries in the FROM clause
*/
#if !defined(SQLITE_OMIT_SUBQUERY) || !defined(SQLITE_OMIT_VIEW)
void sqlite3ManifestSubqueries(
  Parse *pParse,         /* Parsing context */
  Select *p,             /* SELECT statement being generated */
  SrcList *pTabList      /* The FROM clause */
){
  int i;
  sqlite3 *db = pParse->db;
  Vdbe *v = pParse->pVdbe;
  for(i=0; i<pTabList->nSrc; i++){
    struct SrcList_item *pItem = &pTabList->a[i];
    SelectDest dest;
    Select *pSub = pItem->pSelect;
    if( pSub==0 ) continue;

    /* Sometimes the code for a subquery will be generated more than
    ** once, if the subquery is part of the WHERE clause in a LEFT JOIN,
    ** for example.  In that case, do not regenerate the code to manifest
    ** a view or the co-routine to implement a view.  The first instance
    ** is sufficient, though the subroutine to manifest the view does need
    ** to be invoked again. */
    if( pItem->addrFillSub ){
      if( pItem->viaCoroutine==0 ){
        sqlite3VdbeAddOp2(v, OP_Gosub, pItem->regReturn, pItem->addrFillSub);
      }
      continue;
    }

    /* Increment Parse.nHeight by the height of the largest expression
    ** tree referred to by this, the parent select. The child select
    ** may contain expression trees of at most
    ** (SQLITE_MAX_EXPR_DEPTH-Parse.nHeight) height. This is a bit
    ** more conservative than necessary, but much easier than enforcing
    ** an exact limit.
    */
    pParse->nHeight += sqlite3SelectExprHeight(p);

    /* Make copies of constant WHERE-clause terms in the outer query down
    ** inside the subquery.  This can help the subquery to run more efficiently.
    */
    if( (pItem->jointype & JT_OUTER)==0
     && pushDownWhereTerms(db, pSub, p->pWhere, pItem->iCursor)
    ){
#if SELECTTRACE_ENABLED
      if( sqlite3SelectTrace & 0x100 ){
        SELECTTRACE(0x100,pParse,p,("After WHERE-clause push-down:\n"));
        sqlite3TreeViewSelect(0, p, 0);
      }
#endif
    }

    /* Generate code to implement the subquery
    */
    if( pTabList->nSrc==1
     && (p->selFlags & SF_All)==0
     && OptimizationEnabled(db, SQLITE_SubqCoroutine)
    ){
      /* Implement a co-routine that will return a single row of the result
      ** set on each invocation.
      */
      int addrTop = sqlite3VdbeCurrentAddr(v)+1;
      pItem->regReturn = ++pParse->nMem;
      sqlite3VdbeAddOp3(v, OP_InitCoroutine, pItem->regReturn, 0, addrTop);
      VdbeComment((v, "%s", pItem->pTab->zName));
      pItem->addrFillSub = addrTop;
      sqlite3SelectDestInit(&dest, SRT_Coroutine, pItem->regReturn);
      explainSetInteger(pItem->iSelectId, (u8)pParse->iNextSelectId);
      sqlite3Select(pParse, pSub, &dest);
      pItem->pTab->nRowLogEst = sqlite3LogEst(pSub->nSelectRow);
      pItem->viaCoroutine = 1;
      pItem->regResult = dest.iSdst;
      sqlite3VdbeAddOp1(v, OP_EndCoroutine, pItem->regReturn);
      sqlite3VdbeJumpHere(v, addrTop-1);
      sqlite3ClearTempRegCache(pParse);
    }else{
      /* Generate a subroutine that will fill an ephemeral table with
      ** the content of this subquery.  pItem->addrFillSub will point
      ** to the address of the generated subroutine.  pItem->regReturn
      ** is a register allocated to hold the subroutine return address
      */
      int topAddr;
      int onceAddr = 0;
      int retAddr;
      assert( pItem->addrFillSub==0 );
      pItem->regReturn = ++pParse->nMem;
      topAddr = sqlite3VdbeAddOp2(v, OP_Integer, 0, pItem->regReturn);
      pItem->addrFillSub = topAddr+1;
      if( pItem->isCorrelated==0 ){
        /* If the subquery is not correlated and if we are not inside of
        ** a trigger, then we only need to compute the value of the subquery
        ** once. */
        onceAddr = sqlite3CodeOnce(pParse); VdbeCoverage(v);
        VdbeComment((v, "materialize \"%s\"", pItem->pTab->zName));
      }else{
        VdbeNoopComment((v, "materialize \"%s\"", pItem->pTab->zName));
      }
      sqlite3SelectDestInit(&dest, SRT_EphemTab, pItem->iCursor);
      explainSetInteger(pItem->iSelectId, (u8)pParse->iNextSelectId);
      sqlite3Select(pParse, pSub, &dest);
      pItem->pTab->nRowLogEst = sqlite3LogEst(pSub->nSelectRow);
      if( onceAddr ) sqlite3VdbeJumpHere(v, onceAddr);
      retAddr = sqlite3VdbeAddOp1(v, OP_Return, pItem->regReturn);
      VdbeComment((v, "end %s", pItem->pTab->zName));
      sqlite3VdbeChangeP1(v, topAddr, retAddr);
      sqlite3ClearTempRegCache(pParse);
    }
    if( db->mallocFailed ) break;
    pParse->nHeight -= sqlite3SelectExprHeight(p);
  }
}
#endif

/*
** Based on the contents of the AggInfo structure indicated by the first
** argument, this function checks if the following are true:
**
**    * the query contains just a single aggregate function,
**    * the aggregate function is either min() or max(), and
**    * the argument to the aggregate function is a column value.
................................................................................
    SELECTTRACE(1,pParse,p,("end compound-select processing\n"));
    pParse->nSelectIndent--;
#endif
    return rc;
  }
#endif











































#if 1

























































  /* Manifest the subqueries.  This needs to be done before calling
  ** sqlite3WhereBegin() so that the Table.nRowLogEst value can be set
  ** correctly for the subqueries. */
  sqlite3ManifestSubqueries(pParse, p, pTabList);
  if( db->mallocFailed ) goto select_end;


#endif

  /* Various elements of the SELECT copied into local variables for
  ** convenience */
  pEList = p->pEList;
  pWhere = p->pWhere;
  pGroupBy = p->pGroupBy;
................................................................................
  }

  if( !isAgg && pGroupBy==0 ){
    /* No aggregate functions and no GROUP BY clause */
    u16 wctrlFlags = (sDistinct.isTnct ? WHERE_WANT_DISTINCT : 0);

    /* Begin the database scan. */
    pWInfo = sqlite3WhereBegin(pParse, p, pTabList, pWhere, sSort.pOrderBy,
                               p->pEList, wctrlFlags, 0);
    if( pWInfo==0 ) goto select_end;
    if( sqlite3WhereOutputRowCount(pWInfo) < p->nSelectRow ){
      p->nSelectRow = sqlite3WhereOutputRowCount(pWInfo);
    }
    if( sDistinct.isTnct && sqlite3WhereIsDistinct(pWInfo) ){
      sDistinct.eTnctType = sqlite3WhereIsDistinct(pWInfo);
................................................................................

      /* Begin a loop that will extract all source rows in GROUP BY order.
      ** This might involve two separate loops with an OP_Sort in between, or
      ** it might be a single loop that uses an index to extract information
      ** in the right order to begin with.
      */
      sqlite3VdbeAddOp2(v, OP_Gosub, regReset, addrReset);
      pWInfo = sqlite3WhereBegin(pParse, p, pTabList, pWhere, pGroupBy, 0,
          WHERE_GROUPBY | (orderByGrp ? WHERE_SORTBYGROUP : 0), 0
      );
      if( pWInfo==0 ) goto select_end;
      if( sqlite3WhereIsOrdered(pWInfo)==pGroupBy->nExpr ){
        /* The optimizer is able to deliver rows in group by order so
        ** we do not have to sort.  The OP_OpenEphemeral table will be
        ** cancelled later because we still need to use the pKeyInfo
................................................................................
        }
  
        /* This case runs if the aggregate has no GROUP BY clause.  The
        ** processing is much simpler since there is only a single row
        ** of output.
        */
        resetAccumulator(pParse, &sAggInfo);
        pWInfo = sqlite3WhereBegin(pParse,p,pTabList, pWhere, pMinMax,0,flag,0);
        if( pWInfo==0 ){
          sqlite3ExprListDelete(db, pDel);
          goto select_end;
        }
        updateAccumulator(pParse, &sAggInfo);
        assert( pMinMax==0 || pMinMax->nExpr==1 );
        if( sqlite3WhereIsOrdered(pWInfo)>0 ){

Changes to src/sqliteInt.h.

3281
3282
3283
3284
3285
3286
3287

3288
3289
3290
3291
3292
3293
3294
3295
3296
3297
3298
3299
3300

3301
3302
3303
3304
3305
3306
3307
void sqlite3SrcListAssignCursors(Parse*, SrcList*);
void sqlite3IdListDelete(sqlite3*, IdList*);
void sqlite3SrcListDelete(sqlite3*, SrcList*);
Index *sqlite3AllocateIndexObject(sqlite3*,i16,int,char**);
Index *sqlite3CreateIndex(Parse*,Token*,Token*,SrcList*,ExprList*,int,Token*,
                          Expr*, int, int);
void sqlite3DropIndex(Parse*, SrcList*, int);

int sqlite3Select(Parse*, Select*, SelectDest*);
Select *sqlite3SelectNew(Parse*,ExprList*,SrcList*,Expr*,ExprList*,
                         Expr*,ExprList*,u16,Expr*,Expr*);
void sqlite3SelectDelete(sqlite3*, Select*);
Table *sqlite3SrcListLookup(Parse*, SrcList*);
int sqlite3IsReadOnly(Parse*, Table*, int);
void sqlite3OpenTable(Parse*, int iCur, int iDb, Table*, int);
#if defined(SQLITE_ENABLE_UPDATE_DELETE_LIMIT) && !defined(SQLITE_OMIT_SUBQUERY)
Expr *sqlite3LimitWhere(Parse*,SrcList*,Expr*,ExprList*,Expr*,Expr*,char*);
#endif
void sqlite3DeleteFrom(Parse*, SrcList*, Expr*);
void sqlite3Update(Parse*, SrcList*, ExprList*, Expr*, int);
WhereInfo *sqlite3WhereBegin(Parse*,SrcList*,Expr*,ExprList*,ExprList*,u16,int);

void sqlite3WhereEnd(WhereInfo*);
u64 sqlite3WhereOutputRowCount(WhereInfo*);
int sqlite3WhereIsDistinct(WhereInfo*);
int sqlite3WhereIsOrdered(WhereInfo*);
int sqlite3WhereIsSorted(WhereInfo*);
int sqlite3WhereContinueLabel(WhereInfo*);
int sqlite3WhereBreakLabel(WhereInfo*);







>












|
>







3281
3282
3283
3284
3285
3286
3287
3288
3289
3290
3291
3292
3293
3294
3295
3296
3297
3298
3299
3300
3301
3302
3303
3304
3305
3306
3307
3308
3309
void sqlite3SrcListAssignCursors(Parse*, SrcList*);
void sqlite3IdListDelete(sqlite3*, IdList*);
void sqlite3SrcListDelete(sqlite3*, SrcList*);
Index *sqlite3AllocateIndexObject(sqlite3*,i16,int,char**);
Index *sqlite3CreateIndex(Parse*,Token*,Token*,SrcList*,ExprList*,int,Token*,
                          Expr*, int, int);
void sqlite3DropIndex(Parse*, SrcList*, int);
void sqlite3ManifestSubqueries(Parse*, Select*, SrcList*);
int sqlite3Select(Parse*, Select*, SelectDest*);
Select *sqlite3SelectNew(Parse*,ExprList*,SrcList*,Expr*,ExprList*,
                         Expr*,ExprList*,u16,Expr*,Expr*);
void sqlite3SelectDelete(sqlite3*, Select*);
Table *sqlite3SrcListLookup(Parse*, SrcList*);
int sqlite3IsReadOnly(Parse*, Table*, int);
void sqlite3OpenTable(Parse*, int iCur, int iDb, Table*, int);
#if defined(SQLITE_ENABLE_UPDATE_DELETE_LIMIT) && !defined(SQLITE_OMIT_SUBQUERY)
Expr *sqlite3LimitWhere(Parse*,SrcList*,Expr*,ExprList*,Expr*,Expr*,char*);
#endif
void sqlite3DeleteFrom(Parse*, SrcList*, Expr*);
void sqlite3Update(Parse*, SrcList*, ExprList*, Expr*, int);
WhereInfo *sqlite3WhereBegin(Parse*,Select*,SrcList*,Expr*,ExprList*,
                             ExprList*,u16,int);
void sqlite3WhereEnd(WhereInfo*);
u64 sqlite3WhereOutputRowCount(WhereInfo*);
int sqlite3WhereIsDistinct(WhereInfo*);
int sqlite3WhereIsOrdered(WhereInfo*);
int sqlite3WhereIsSorted(WhereInfo*);
int sqlite3WhereContinueLabel(WhereInfo*);
int sqlite3WhereBreakLabel(WhereInfo*);

Changes to src/update.c.

343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
...
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
  }

  /* Begin the database scan
  */
  if( HasRowid(pTab) ){
    sqlite3VdbeAddOp3(v, OP_Null, 0, regRowSet, regOldRowid);
    pWInfo = sqlite3WhereBegin(
        pParse, pTabList, pWhere, 0, 0, WHERE_ONEPASS_DESIRED, iIdxCur
    );
    if( pWInfo==0 ) goto update_cleanup;
    okOnePass = sqlite3WhereOkOnePass(pWInfo, aiCurOnePass);
  
    /* Remember the rowid of every item to be updated.
    */
    sqlite3VdbeAddOp2(v, OP_Rowid, iDataCur, regOldRowid);
................................................................................
    iPk = pParse->nMem+1;
    pParse->nMem += nPk;
    regKey = ++pParse->nMem;
    iEph = pParse->nTab++;
    sqlite3VdbeAddOp2(v, OP_Null, 0, iPk);
    addrOpen = sqlite3VdbeAddOp2(v, OP_OpenEphemeral, iEph, nPk);
    sqlite3VdbeSetP4KeyInfo(pParse, pPk);
    pWInfo = sqlite3WhereBegin(pParse, pTabList, pWhere, 0, 0, 
                               WHERE_ONEPASS_DESIRED, iIdxCur);
    if( pWInfo==0 ) goto update_cleanup;
    okOnePass = sqlite3WhereOkOnePass(pWInfo, aiCurOnePass);
    for(i=0; i<nPk; i++){
      sqlite3ExprCodeGetColumnOfTable(v, pTab, iDataCur, pPk->aiColumn[i],
                                      iPk+i);
    }







|







 







|







343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
...
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
  }

  /* Begin the database scan
  */
  if( HasRowid(pTab) ){
    sqlite3VdbeAddOp3(v, OP_Null, 0, regRowSet, regOldRowid);
    pWInfo = sqlite3WhereBegin(
        pParse, 0, pTabList, pWhere, 0, 0, WHERE_ONEPASS_DESIRED, iIdxCur
    );
    if( pWInfo==0 ) goto update_cleanup;
    okOnePass = sqlite3WhereOkOnePass(pWInfo, aiCurOnePass);
  
    /* Remember the rowid of every item to be updated.
    */
    sqlite3VdbeAddOp2(v, OP_Rowid, iDataCur, regOldRowid);
................................................................................
    iPk = pParse->nMem+1;
    pParse->nMem += nPk;
    regKey = ++pParse->nMem;
    iEph = pParse->nTab++;
    sqlite3VdbeAddOp2(v, OP_Null, 0, iPk);
    addrOpen = sqlite3VdbeAddOp2(v, OP_OpenEphemeral, iEph, nPk);
    sqlite3VdbeSetP4KeyInfo(pParse, pPk);
    pWInfo = sqlite3WhereBegin(pParse, 0, pTabList, pWhere, 0, 0, 
                               WHERE_ONEPASS_DESIRED, iIdxCur);
    if( pWInfo==0 ) goto update_cleanup;
    okOnePass = sqlite3WhereOkOnePass(pWInfo, aiCurOnePass);
    for(i=0; i<nPk; i++){
      sqlite3ExprCodeGetColumnOfTable(v, pTab, iDataCur, pPk->aiColumn[i],
                                      iPk+i);
    }

Changes to src/where.c.

3852
3853
3854
3855
3856
3857
3858

3859
3860
3861
3862
3863
3864
3865
....
4126
4127
4128
4129
4130
4131
4132












4133
4134
4135
4136
4137
4138
4139
** specific cursor.  If WHERE_ONEPASS_DESIRED is set, then iIdxCur is
** the first cursor in an array of cursors for all indices.  iIdxCur should
** be used to compute the appropriate cursor depending on which index is
** used.
*/
WhereInfo *sqlite3WhereBegin(
  Parse *pParse,        /* The parser context */

  SrcList *pTabList,    /* FROM clause: A list of all tables to be scanned */
  Expr *pWhere,         /* The WHERE clause */
  ExprList *pOrderBy,   /* An ORDER BY (or GROUP BY) clause, or NULL */
  ExprList *pResultSet, /* Result set of the query */
  u16 wctrlFlags,       /* One of the WHERE_* flags defined in sqliteInt.h */
  int iIdxCur           /* If WHERE_ONETABLE_ONLY is set, index cursor number */
){
................................................................................
   && (pWInfo->a[0].pWLoop->wsFlags & WHERE_ONEROW)!=0 ){
    pWInfo->okOnePass = 1;
    if( HasRowid(pTabList->a[0].pTab) ){
      pWInfo->a[0].pWLoop->wsFlags &= ~WHERE_IDX_ONLY;
    }
  }













  /* Open all tables in the pTabList and any indices selected for
  ** searching those tables.
  */
  for(ii=0, pLevel=pWInfo->a; ii<nTabList; ii++, pLevel++){
    Table *pTab;     /* Table to open */
    int iDb;         /* Index of database containing table/index */
    struct SrcList_item *pTabItem;







>







 







>
>
>
>
>
>
>
>
>
>
>
>







3852
3853
3854
3855
3856
3857
3858
3859
3860
3861
3862
3863
3864
3865
3866
....
4127
4128
4129
4130
4131
4132
4133
4134
4135
4136
4137
4138
4139
4140
4141
4142
4143
4144
4145
4146
4147
4148
4149
4150
4151
4152
** specific cursor.  If WHERE_ONEPASS_DESIRED is set, then iIdxCur is
** the first cursor in an array of cursors for all indices.  iIdxCur should
** be used to compute the appropriate cursor depending on which index is
** used.
*/
WhereInfo *sqlite3WhereBegin(
  Parse *pParse,        /* The parser context */
  Select *pSelect,      /* SELECT stmt that owns this WHERE. Might be NULL */
  SrcList *pTabList,    /* FROM clause: A list of all tables to be scanned */
  Expr *pWhere,         /* The WHERE clause */
  ExprList *pOrderBy,   /* An ORDER BY (or GROUP BY) clause, or NULL */
  ExprList *pResultSet, /* Result set of the query */
  u16 wctrlFlags,       /* One of the WHERE_* flags defined in sqliteInt.h */
  int iIdxCur           /* If WHERE_ONETABLE_ONLY is set, index cursor number */
){
................................................................................
   && (pWInfo->a[0].pWLoop->wsFlags & WHERE_ONEROW)!=0 ){
    pWInfo->okOnePass = 1;
    if( HasRowid(pTabList->a[0].pTab) ){
      pWInfo->a[0].pWLoop->wsFlags &= ~WHERE_IDX_ONLY;
    }
  }

#if 0
  /* If this WHERE clause is part of a SELECT statement, then there
  ** might be subqueries in the FROM clause that need to be manifested.
  ** This works mostly - except the Table.nRowLogEst value is not set
  ** correctly for the subquery, resulting in a bad plan in some cases.
  */
  if( pSelect!=0 ){
    sqlite3ManifestSubqueries(pParse, pSelect, pTabList);
    if( db->mallocFailed ) goto whereBeginError;
  }
#endif

  /* Open all tables in the pTabList and any indices selected for
  ** searching those tables.
  */
  for(ii=0, pLevel=pWInfo->a; ii<nTabList; ii++, pLevel++){
    Table *pTab;     /* Table to open */
    int iDb;         /* Index of database containing table/index */
    struct SrcList_item *pTabItem;

Changes to src/wherecode.c.

1261
1262
1263
1264
1265
1266
1267
1268
1269
1270
1271
1272
1273
1274
1275
        int j1 = 0;                     /* Address of jump operation */
        if( pAndExpr && !ExprHasProperty(pOrExpr, EP_FromJoin) ){
          pAndExpr->pLeft = pOrExpr;
          pOrExpr = pAndExpr;
        }
        /* Loop through table entries that match term pOrTerm. */
        WHERETRACE(0xffff, ("Subplan for OR-clause:\n"));
        pSubWInfo = sqlite3WhereBegin(pParse, pOrTab, pOrExpr, 0, 0,
                                      wctrlFlags, iCovCur);
        assert( pSubWInfo || pParse->nErr || db->mallocFailed );
        if( pSubWInfo ){
          WhereLoop *pSubLoop;
          int addrExplain = sqlite3WhereExplainOneScan(
              pParse, pOrTab, &pSubWInfo->a[0], iLevel, pLevel->iFrom, 0
          );







|







1261
1262
1263
1264
1265
1266
1267
1268
1269
1270
1271
1272
1273
1274
1275
        int j1 = 0;                     /* Address of jump operation */
        if( pAndExpr && !ExprHasProperty(pOrExpr, EP_FromJoin) ){
          pAndExpr->pLeft = pOrExpr;
          pOrExpr = pAndExpr;
        }
        /* Loop through table entries that match term pOrTerm. */
        WHERETRACE(0xffff, ("Subplan for OR-clause:\n"));
        pSubWInfo = sqlite3WhereBegin(pParse, 0, pOrTab, pOrExpr, 0, 0,
                                      wctrlFlags, iCovCur);
        assert( pSubWInfo || pParse->nErr || db->mallocFailed );
        if( pSubWInfo ){
          WhereLoop *pSubLoop;
          int addrExplain = sqlite3WhereExplainOneScan(
              pParse, pOrTab, &pSubWInfo->a[0], iLevel, pLevel->iFrom, 0
          );