SQLite

Check-in [7153552bac]
Login

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

Overview
Comment:Improvements to EXPLAIN QUERY PLAN formatting. The MULTI-INDEX OR now shows a separate "INDEX" subtree for each index. SCALAR SUBQUERY entries provide a subquery number that is related back to the .selecttrace output.
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | reuse-subqueries
Files: files | file ages | folders
SHA3-256: 7153552bac51295c56a1c42ca79d57195851e232509f9e9610375692f48c7e86
User & Date: drh 2018-12-24 20:21:06.021
Context
2018-12-25
00:15
Increase the number of parameters to sqlite3WhereCodeOneLoopStart() to encourage the compiler to inline that routine within sqlite3WhereBegin(). This gives a performance improvement. (check-in: 3c2ad0e915 user: drh tags: reuse-subqueries)
2018-12-24
20:21
Improvements to EXPLAIN QUERY PLAN formatting. The MULTI-INDEX OR now shows a separate "INDEX" subtree for each index. SCALAR SUBQUERY entries provide a subquery number that is related back to the .selecttrace output. (check-in: 7153552bac user: drh tags: reuse-subqueries)
14:30
Restore a line of code that was previously commented out for debugging. (check-in: e53781f5bd user: drh tags: reuse-subqueries)
Changes
Unified Diff Ignore Whitespace Patch
Changes to ext/expert/expert1.test.
239
240
241
242
243
244
245

246

247
248
249
250
251
252
253
254
  CREATE TABLE t7(a, b);
} {
  SELECT * FROM t7 WHERE a=? OR b=?
} {
  CREATE INDEX t7_idx_00000062 ON t7(b);
  CREATE INDEX t7_idx_00000061 ON t7(a);
  MULTI-INDEX OR

    SEARCH TABLE t7 USING INDEX t7_idx_00000061 (a=?) 

    SEARCH TABLE t7 USING INDEX t7_idx_00000062 (b=?)
}

# rowid terms.
#
do_setup_rec_test $tn.13.1 {
  CREATE TABLE t8(a, b);
} {







>
|
>
|







239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
  CREATE TABLE t7(a, b);
} {
  SELECT * FROM t7 WHERE a=? OR b=?
} {
  CREATE INDEX t7_idx_00000062 ON t7(b);
  CREATE INDEX t7_idx_00000061 ON t7(a);
  MULTI-INDEX OR
    INDEX 1
      SEARCH TABLE t7 USING INDEX t7_idx_00000061 (a=?) 
    INDEX 2
      SEARCH TABLE t7 USING INDEX t7_idx_00000062 (b=?)
}

# rowid terms.
#
do_setup_rec_test $tn.13.1 {
  CREATE TABLE t8(a, b);
} {
Changes to src/expr.c.
2687
2688
2689
2690
2691
2692
2693
2694




2695
2696
2697

2698
2699
2700
2701
2702
2703
2704
  */
  if( !ExprHasProperty(pExpr, EP_VarSelect) && pParse->iSelfTab==0 ){
    /* Reuse of the RHS is allowed */
    /* If this routine has already been coded, but the previous code
    ** might not have been invoked yet, so invoke it now as a subroutine. 
    */
    if( ExprHasProperty(pExpr, EP_Subrtn) ){
      sqlite3VdbeAddOp2(v, OP_Once, 0, sqlite3VdbeCurrentAddr(v)+3);




      sqlite3VdbeAddOp2(v, OP_Gosub, pExpr->y.sub.regReturn,
                        pExpr->y.sub.iAddr);
      sqlite3VdbeAddOp2(v, OP_OpenDup, iTab, pExpr->iTable);

      return;
    }

    /* Begin coding the subroutine */
    ExprSetProperty(pExpr, EP_Subrtn);
    pExpr->y.sub.regReturn = ++pParse->nMem;
    pExpr->y.sub.iAddr =







|
>
>
>
>



>







2687
2688
2689
2690
2691
2692
2693
2694
2695
2696
2697
2698
2699
2700
2701
2702
2703
2704
2705
2706
2707
2708
2709
  */
  if( !ExprHasProperty(pExpr, EP_VarSelect) && pParse->iSelfTab==0 ){
    /* Reuse of the RHS is allowed */
    /* If this routine has already been coded, but the previous code
    ** might not have been invoked yet, so invoke it now as a subroutine. 
    */
    if( ExprHasProperty(pExpr, EP_Subrtn) ){
      int addr = sqlite3VdbeAddOp0(v, OP_Once); VdbeCoverage(v);
      if( ExprHasProperty(pExpr, EP_xIsSelect) ){
        ExplainQueryPlan((pParse, 0, "REUSE LIST SUBQUERY %d",
              pExpr->x.pSelect->selId));
      }
      sqlite3VdbeAddOp2(v, OP_Gosub, pExpr->y.sub.regReturn,
                        pExpr->y.sub.iAddr);
      sqlite3VdbeAddOp2(v, OP_OpenDup, iTab, pExpr->iTable);
      sqlite3VdbeJumpHere(v, addr);
      return;
    }

    /* Begin coding the subroutine */
    ExprSetProperty(pExpr, EP_Subrtn);
    pExpr->y.sub.regReturn = ++pParse->nMem;
    pExpr->y.sub.iAddr =
2742
2743
2744
2745
2746
2747
2748
2749
2750
2751
2752
2753
2754
2755
2756
    ));
    assert( !isRowid );
    /* If the LHS and RHS of the IN operator do not match, that
    ** error will have been caught long before we reach this point. */
    if( ALWAYS(pEList->nExpr==nVal) ){
      SelectDest dest;
      int i;
      sqlite3SelectDestInit(&dest, SRT_Set, pExpr->iTable);
      dest.zAffSdst = exprINAffinity(pParse, pExpr);
      pSelect->iLimit = 0;
      testcase( pSelect->selFlags & SF_Distinct );
      testcase( pKeyInfo==0 ); /* Caused by OOM in sqlite3KeyInfoAlloc() */
      if( sqlite3Select(pParse, pSelect, &dest) ){
        sqlite3DbFree(pParse->db, dest.zAffSdst);
        sqlite3KeyInfoUnref(pKeyInfo);







|







2747
2748
2749
2750
2751
2752
2753
2754
2755
2756
2757
2758
2759
2760
2761
    ));
    assert( !isRowid );
    /* If the LHS and RHS of the IN operator do not match, that
    ** error will have been caught long before we reach this point. */
    if( ALWAYS(pEList->nExpr==nVal) ){
      SelectDest dest;
      int i;
      sqlite3SelectDestInit(&dest, SRT_Set, iTab);
      dest.zAffSdst = exprINAffinity(pParse, pExpr);
      pSelect->iLimit = 0;
      testcase( pSelect->selFlags & SF_Distinct );
      testcase( pKeyInfo==0 ); /* Caused by OOM in sqlite3KeyInfoAlloc() */
      if( sqlite3Select(pParse, pSelect, &dest) ){
        sqlite3DbFree(pParse->db, dest.zAffSdst);
        sqlite3KeyInfoUnref(pKeyInfo);
2806
2807
2808
2809
2810
2811
2812
2813
2814
2815
2816
2817
2818
2819
2820
2821
2822
2823
2824
2825
2826
2827
2828
2829
2830
      if( addrOnce && !sqlite3ExprIsConstant(pE2) ){
        sqlite3VdbeChangeToNoop(v, addrOnce);
        addrOnce = 0;
      }

      /* Evaluate the expression and insert it into the temp table */
      if( isRowid && sqlite3ExprIsInteger(pE2, &iValToIns) ){
        sqlite3VdbeAddOp3(v, OP_InsertInt, pExpr->iTable, r2, iValToIns);
      }else{
        r3 = sqlite3ExprCodeTarget(pParse, pE2, r1);
        if( isRowid ){
          sqlite3VdbeAddOp2(v, OP_MustBeInt, r3,
                            sqlite3VdbeCurrentAddr(v)+2);
          VdbeCoverage(v);
          sqlite3VdbeAddOp3(v, OP_Insert, pExpr->iTable, r2, r3);
        }else{
          sqlite3VdbeAddOp4(v, OP_MakeRecord, r3, 1, r2, &affinity, 1);
          sqlite3VdbeAddOp4Int(v, OP_IdxInsert, pExpr->iTable, r2, r3, 1);
        }
      }
    }
    sqlite3ReleaseTempReg(pParse, r1);
    sqlite3ReleaseTempReg(pParse, r2);
  }
  if( pKeyInfo ){







|






|


|







2811
2812
2813
2814
2815
2816
2817
2818
2819
2820
2821
2822
2823
2824
2825
2826
2827
2828
2829
2830
2831
2832
2833
2834
2835
      if( addrOnce && !sqlite3ExprIsConstant(pE2) ){
        sqlite3VdbeChangeToNoop(v, addrOnce);
        addrOnce = 0;
      }

      /* Evaluate the expression and insert it into the temp table */
      if( isRowid && sqlite3ExprIsInteger(pE2, &iValToIns) ){
        sqlite3VdbeAddOp3(v, OP_InsertInt, iTab, r2, iValToIns);
      }else{
        r3 = sqlite3ExprCodeTarget(pParse, pE2, r1);
        if( isRowid ){
          sqlite3VdbeAddOp2(v, OP_MustBeInt, r3,
                            sqlite3VdbeCurrentAddr(v)+2);
          VdbeCoverage(v);
          sqlite3VdbeAddOp3(v, OP_Insert, iTab, r2, r3);
        }else{
          sqlite3VdbeAddOp4(v, OP_MakeRecord, r3, 1, r2, &affinity, 1);
          sqlite3VdbeAddOp4Int(v, OP_IdxInsert, iTab, r2, r3, 1);
        }
      }
    }
    sqlite3ReleaseTempReg(pParse, r1);
    sqlite3ReleaseTempReg(pParse, r2);
  }
  if( pKeyInfo ){
2860
2861
2862
2863
2864
2865
2866





2867
2868
2869
2870
2871
2872
2873
2874
2875
2876
2877
2878
2879
2880
2881

2882
2883
2884
2885
2886
2887
2888
  Select *pSel;               /* SELECT statement to encode */
  SelectDest dest;            /* How to deal with SELECT result */
  int nReg;                   /* Registers to allocate */
  Expr *pLimit;               /* New limit expression */

  Vdbe *v = pParse->pVdbe;
  assert( v!=0 );






  /* The evaluation of the EXISTS/SELECT must be repeated every time it
  ** is encountered if any of the following is true:
  **
  **    *  The right-hand side is a correlated subquery
  **    *  The right-hand side is an expression list containing variables
  **    *  We are inside a trigger
  **
  ** If all of the above are false, then we can run this code just once
  ** save the results, and reuse the same result on subsequent invocations.
  */
  if( !ExprHasProperty(pExpr, EP_VarSelect) ){
    /* If this routine has already been coded, then invoke it as a
    ** subroutine. */
    if( ExprHasProperty(pExpr, EP_Subrtn) ){

      sqlite3VdbeAddOp2(v, OP_Gosub, pExpr->y.sub.regReturn,
                        pExpr->y.sub.iAddr);
      return pExpr->iTable;
    }

    /* Begin coding the subroutine */
    ExprSetProperty(pExpr, EP_Subrtn);







>
>
>
>
>















>







2865
2866
2867
2868
2869
2870
2871
2872
2873
2874
2875
2876
2877
2878
2879
2880
2881
2882
2883
2884
2885
2886
2887
2888
2889
2890
2891
2892
2893
2894
2895
2896
2897
2898
2899
  Select *pSel;               /* SELECT statement to encode */
  SelectDest dest;            /* How to deal with SELECT result */
  int nReg;                   /* Registers to allocate */
  Expr *pLimit;               /* New limit expression */

  Vdbe *v = pParse->pVdbe;
  assert( v!=0 );
  testcase( pExpr->op==TK_EXISTS );
  testcase( pExpr->op==TK_SELECT );
  assert( pExpr->op==TK_EXISTS || pExpr->op==TK_SELECT );
  assert( ExprHasProperty(pExpr, EP_xIsSelect) );
  pSel = pExpr->x.pSelect;

  /* The evaluation of the EXISTS/SELECT must be repeated every time it
  ** is encountered if any of the following is true:
  **
  **    *  The right-hand side is a correlated subquery
  **    *  The right-hand side is an expression list containing variables
  **    *  We are inside a trigger
  **
  ** If all of the above are false, then we can run this code just once
  ** save the results, and reuse the same result on subsequent invocations.
  */
  if( !ExprHasProperty(pExpr, EP_VarSelect) ){
    /* If this routine has already been coded, then invoke it as a
    ** subroutine. */
    if( ExprHasProperty(pExpr, EP_Subrtn) ){
      ExplainQueryPlan((pParse, 0, "REUSE SUBQUERY %d", pSel->selId));
      sqlite3VdbeAddOp2(v, OP_Gosub, pExpr->y.sub.regReturn,
                        pExpr->y.sub.iAddr);
      return pExpr->iTable;
    }

    /* Begin coding the subroutine */
    ExprSetProperty(pExpr, EP_Subrtn);
2900
2901
2902
2903
2904
2905
2906
2907
2908
2909
2910
2911
2912
2913
2914
2915
2916
2917
2918
2919
2920
2921
  **
  ** If this is an EXISTS, write an integer 0 (not exists) or 1 (exists)
  ** into a register and return that register number.
  **
  ** In both cases, the query is augmented with "LIMIT 1".  Any 
  ** preexisting limit is discarded in place of the new LIMIT 1.
  */
  testcase( pExpr->op==TK_EXISTS );
  testcase( pExpr->op==TK_SELECT );
  assert( pExpr->op==TK_EXISTS || pExpr->op==TK_SELECT );
  assert( ExprHasProperty(pExpr, EP_xIsSelect) );

  pSel = pExpr->x.pSelect;
  ExplainQueryPlan((pParse, 1, "%sSCALAR SUBQUERY",
        addrOnce?"":"CORRELATED "));
  nReg = pExpr->op==TK_SELECT ? pSel->pEList->nExpr : 1;
  sqlite3SelectDestInit(&dest, 0, pParse->nMem+1);
  pParse->nMem += nReg;
  if( pExpr->op==TK_SELECT ){
    dest.eDest = SRT_Mem;
    dest.iSdst = dest.iSDParm;
    dest.nSdst = nReg;







<
<
<
<
<
<
|
|







2911
2912
2913
2914
2915
2916
2917






2918
2919
2920
2921
2922
2923
2924
2925
2926
  **
  ** If this is an EXISTS, write an integer 0 (not exists) or 1 (exists)
  ** into a register and return that register number.
  **
  ** In both cases, the query is augmented with "LIMIT 1".  Any 
  ** preexisting limit is discarded in place of the new LIMIT 1.
  */






  ExplainQueryPlan((pParse, 1, "%sSCALAR SUBQUERY %d",
        addrOnce?"":"CORRELATED ", pSel->selId));
  nReg = pExpr->op==TK_SELECT ? pSel->pEList->nExpr : 1;
  sqlite3SelectDestInit(&dest, 0, pParse->nMem+1);
  pParse->nMem += nReg;
  if( pExpr->op==TK_SELECT ){
    dest.eDest = SRT_Mem;
    dest.iSdst = dest.iSDParm;
    dest.nSdst = nReg;
Changes to src/vdbe.h.
204
205
206
207
208
209
210

211
212
213
214
215
216
217






218
219
220
221
222
223
224
# define sqlite3VdbeVerifyAbortable(A,B)
#endif
VdbeOp *sqlite3VdbeAddOpList(Vdbe*, int nOp, VdbeOpList const *aOp,int iLineno);
#ifndef SQLITE_OMIT_EXPLAIN
  void sqlite3VdbeExplain(Parse*,u8,const char*,...);
  void sqlite3VdbeExplainPop(Parse*);
  int sqlite3VdbeExplainParent(Parse*);

# define ExplainQueryPlan(P)        sqlite3VdbeExplain P
# define ExplainQueryPlanPop(P)     sqlite3VdbeExplainPop(P)
# define ExplainQueryPlanParent(P)  sqlite3VdbeExplainParent(P)
#else
# define ExplainQueryPlan(P)
# define ExplainQueryPlanPop(P)
# define ExplainQueryPlanParent(P) 0






#endif
void sqlite3VdbeAddParseSchemaOp(Vdbe*,int,char*);
void sqlite3VdbeChangeOpcode(Vdbe*, u32 addr, u8);
void sqlite3VdbeChangeP1(Vdbe*, u32 addr, int P1);
void sqlite3VdbeChangeP2(Vdbe*, u32 addr, int P2);
void sqlite3VdbeChangeP3(Vdbe*, u32 addr, int P3);
void sqlite3VdbeChangeP5(Vdbe*, u16 P5);







>







>
>
>
>
>
>







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
# define sqlite3VdbeVerifyAbortable(A,B)
#endif
VdbeOp *sqlite3VdbeAddOpList(Vdbe*, int nOp, VdbeOpList const *aOp,int iLineno);
#ifndef SQLITE_OMIT_EXPLAIN
  void sqlite3VdbeExplain(Parse*,u8,const char*,...);
  void sqlite3VdbeExplainPop(Parse*);
  int sqlite3VdbeExplainParent(Parse*);
  void sqlite3ExplainBreakpoint(const char*,const char*);
# define ExplainQueryPlan(P)        sqlite3VdbeExplain P
# define ExplainQueryPlanPop(P)     sqlite3VdbeExplainPop(P)
# define ExplainQueryPlanParent(P)  sqlite3VdbeExplainParent(P)
#else
# define ExplainQueryPlan(P)
# define ExplainQueryPlanPop(P)
# define ExplainQueryPlanParent(P) 0
# define sqlite3ExplainBreakpoint(A,B) /*no-op*/
#endif
#if defined(SQLITE_DEBUG) && !defined(SQLITE_OMIT_EXPLAIN)
  void sqlite3ExplainBreakpoint(const char*,const char*);
#else
# define sqlite3ExplainBreakpoint(A,B) /*no-op*/
#endif
void sqlite3VdbeAddParseSchemaOp(Vdbe*,int,char*);
void sqlite3VdbeChangeOpcode(Vdbe*, u32 addr, u8);
void sqlite3VdbeChangeP1(Vdbe*, u32 addr, int P1);
void sqlite3VdbeChangeP2(Vdbe*, u32 addr, int P2);
void sqlite3VdbeChangeP3(Vdbe*, u32 addr, int P3);
void sqlite3VdbeChangeP5(Vdbe*, u16 P5);
Changes to src/vdbeaux.c.
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
  VdbeOp *pOp;
  if( pParse->addrExplain==0 ) return 0;
  pOp = sqlite3VdbeGetOp(pParse->pVdbe, pParse->addrExplain);
  return pOp->p2;
}

/*











** Add a new OP_Explain opcode.
**
** If the bPush flag is true, then make this opcode the parent for
** subsequent Explains until sqlite3VdbeExplainPop() is called.
*/
void sqlite3VdbeExplain(Parse *pParse, u8 bPush, const char *zFmt, ...){

  if( pParse->explain==2 ){


    char *zMsg;
    Vdbe *v;
    va_list ap;
    int iThis;
    va_start(ap, zFmt);
    zMsg = sqlite3VMPrintf(pParse->db, zFmt, ap);
    va_end(ap);
    v = pParse->pVdbe;
    iThis = v->nOp;
    sqlite3VdbeAddOp4(v, OP_Explain, iThis, pParse->addrExplain, 0,
                      zMsg, P4_DYNAMIC);


    if( bPush) pParse->addrExplain = iThis;

  }
}

/*
** Pop the EXPLAIN QUERY PLAN stack one level.
*/
void sqlite3VdbeExplainPop(Parse *pParse){

  pParse->addrExplain = sqlite3VdbeExplainParent(pParse);
}
#endif /* SQLITE_OMIT_EXPLAIN */

/*
** Add an OP_ParseSchema opcode.  This routine is broken out from
** sqlite3VdbeAddOp4() since it needs to also needs to mark all btrees







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





>
|
>
>











>
>
|
>







>







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
  VdbeOp *pOp;
  if( pParse->addrExplain==0 ) return 0;
  pOp = sqlite3VdbeGetOp(pParse->pVdbe, pParse->addrExplain);
  return pOp->p2;
}

/*
** Set a debugger breakpoint on the following routine in order to
** monitor the EXPLAIN QUERY PLAN code generation.
*/
#if defined(SQLITE_DEBUG)
void sqlite3ExplainBreakpoint(const char *z1, const char *z2){
  (void)z1;
  (void)z2;
}
#endif

/*
** Add a new OP_ opcode.
**
** If the bPush flag is true, then make this opcode the parent for
** subsequent Explains until sqlite3VdbeExplainPop() is called.
*/
void sqlite3VdbeExplain(Parse *pParse, u8 bPush, const char *zFmt, ...){
#if !defined(SQLITE_DEBUG)
  if( pParse->explain==2 )
#endif
  {
    char *zMsg;
    Vdbe *v;
    va_list ap;
    int iThis;
    va_start(ap, zFmt);
    zMsg = sqlite3VMPrintf(pParse->db, zFmt, ap);
    va_end(ap);
    v = pParse->pVdbe;
    iThis = v->nOp;
    sqlite3VdbeAddOp4(v, OP_Explain, iThis, pParse->addrExplain, 0,
                      zMsg, P4_DYNAMIC);
    sqlite3ExplainBreakpoint(bPush?"PUSH":"", sqlite3VdbeGetOp(v,-1)->p4.z);
    if( bPush){
      pParse->addrExplain = iThis;
    }
  }
}

/*
** Pop the EXPLAIN QUERY PLAN stack one level.
*/
void sqlite3VdbeExplainPop(Parse *pParse){
  sqlite3ExplainBreakpoint("POP", 0);
  pParse->addrExplain = sqlite3VdbeExplainParent(pParse);
}
#endif /* SQLITE_OMIT_EXPLAIN */

/*
** Add an OP_ParseSchema opcode.  This routine is broken out from
** sqlite3VdbeAddOp4() since it needs to also needs to mark all btrees
Changes to src/wherecode.c.
209
210
211
212
213
214
215

216
217
218
219
220
221
222
      sqlite3_str_appendf(&str, " (~%llu rows)",
             sqlite3LogEstToInt(pLoop->nOut));
    }else{
      sqlite3_str_append(&str, " (~1 row)", 9);
    }
#endif
    zMsg = sqlite3StrAccumFinish(&str);

    ret = sqlite3VdbeAddOp4(v, OP_Explain, sqlite3VdbeCurrentAddr(v),
                            pParse->addrExplain, 0, zMsg,P4_DYNAMIC);
  }
  return ret;
}
#endif /* SQLITE_OMIT_EXPLAIN */








>







209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
      sqlite3_str_appendf(&str, " (~%llu rows)",
             sqlite3LogEstToInt(pLoop->nOut));
    }else{
      sqlite3_str_append(&str, " (~1 row)", 9);
    }
#endif
    zMsg = sqlite3StrAccumFinish(&str);
    sqlite3ExplainBreakpoint("",zMsg);
    ret = sqlite3VdbeAddOp4(v, OP_Explain, sqlite3VdbeCurrentAddr(v),
                            pParse->addrExplain, 0, zMsg,P4_DYNAMIC);
  }
  return ret;
}
#endif /* SQLITE_OMIT_EXPLAIN */

1947
1948
1949
1950
1951
1952
1953

1954
1955
1956
1957
1958
1959
1960
             || ExprHasProperty(pOrExpr, EP_FromJoin) 
        );
        if( pAndExpr ){
          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(







>







1948
1949
1950
1951
1952
1953
1954
1955
1956
1957
1958
1959
1960
1961
1962
             || ExprHasProperty(pOrExpr, EP_FromJoin) 
        );
        if( pAndExpr ){
          pAndExpr->pLeft = pOrExpr;
          pOrExpr = pAndExpr;
        }
        /* Loop through table entries that match term pOrTerm. */
        ExplainQueryPlan((pParse, 1, "INDEX %d", ii+1));
        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(
2050
2051
2052
2053
2054
2055
2056

2057
2058
2059
2060
2061
2062
2063
            pCov = pSubLoop->u.btree.pIndex;
          }else{
            pCov = 0;
          }

          /* Finish the loop through table entries that match term pOrTerm. */
          sqlite3WhereEnd(pSubWInfo);

        }
      }
    }
    ExplainQueryPlanPop(pParse);
    pLevel->u.pCovidx = pCov;
    if( pCov ) pLevel->iIdxCur = iCovCur;
    if( pAndExpr ){







>







2052
2053
2054
2055
2056
2057
2058
2059
2060
2061
2062
2063
2064
2065
2066
            pCov = pSubLoop->u.btree.pIndex;
          }else{
            pCov = 0;
          }

          /* Finish the loop through table entries that match term pOrTerm. */
          sqlite3WhereEnd(pSubWInfo);
          ExplainQueryPlanPop(pParse);
        }
      }
    }
    ExplainQueryPlanPop(pParse);
    pLevel->u.pCovidx = pCov;
    if( pCov ) pLevel->iIdxCur = iCovCur;
    if( pAndExpr ){
Changes to test/autoindex1.test.
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
   WHERE y.sheep_no IS NULL
   ORDER BY x.registering_flock;
} {
  QUERY PLAN
  |--MATERIALIZE xxxxxx
  |  |--SCAN TABLE sheep AS s
  |  |--SEARCH TABLE flock_owner AS prev USING INDEX sqlite_autoindex_flock_owner_1 (flock_no=? AND owner_change_date<?)
  |  `--CORRELATED SCALAR SUBQUERY
  |     `--SEARCH TABLE flock_owner AS later USING COVERING INDEX sqlite_autoindex_flock_owner_1 (flock_no=? AND owner_change_date>? AND owner_change_date<?)
  |--SCAN TABLE sheep AS x USING INDEX sheep_reg_flock_index
  `--SEARCH SUBQUERY xxxxxx AS y USING AUTOMATIC COVERING INDEX (sheep_no=?)
}


do_execsql_test autoindex1-700 {







|







276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
   WHERE y.sheep_no IS NULL
   ORDER BY x.registering_flock;
} {
  QUERY PLAN
  |--MATERIALIZE xxxxxx
  |  |--SCAN TABLE sheep AS s
  |  |--SEARCH TABLE flock_owner AS prev USING INDEX sqlite_autoindex_flock_owner_1 (flock_no=? AND owner_change_date<?)
  |  `--CORRELATED SCALAR SUBQUERY xxxxxx
  |     `--SEARCH TABLE flock_owner AS later USING COVERING INDEX sqlite_autoindex_flock_owner_1 (flock_no=? AND owner_change_date>? AND owner_change_date<?)
  |--SCAN TABLE sheep AS x USING INDEX sheep_reg_flock_index
  `--SEARCH SUBQUERY xxxxxx AS y USING AUTOMATIC COVERING INDEX (sheep_no=?)
}


do_execsql_test autoindex1-700 {
Changes to test/bestindex3.test.
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
} {SCAN TABLE t1 VIRTUAL TABLE INDEX 0:a EQ ?}

do_eqp_test 1.3 {
  SELECT * FROM t1 WHERE a = 'abc' OR b = 'def';
} {
  QUERY PLAN
  `--MULTI-INDEX OR

     |--SCAN TABLE t1 VIRTUAL TABLE INDEX 0:a EQ ?

     `--SCAN TABLE t1 VIRTUAL TABLE INDEX 0:b EQ ?
}

do_eqp_test 1.4 {
  SELECT * FROM t1 WHERE a LIKE 'abc%' OR b = 'def';
} {
  QUERY PLAN
  `--MULTI-INDEX OR

     |--SCAN TABLE t1 VIRTUAL TABLE INDEX 0:a LIKE ?

     `--SCAN TABLE t1 VIRTUAL TABLE INDEX 0:b EQ ?
}

do_execsql_test 1.5 {
  CREATE TABLE ttt(a, b, c);

  INSERT INTO ttt VALUES(1, 'two',   'three');
  INSERT INTO ttt VALUES(2, 'one',   'two');







>
|
>
|







>
|
>
|







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
} {SCAN TABLE t1 VIRTUAL TABLE INDEX 0:a EQ ?}

do_eqp_test 1.3 {
  SELECT * FROM t1 WHERE a = 'abc' OR b = 'def';
} {
  QUERY PLAN
  `--MULTI-INDEX OR
     |--INDEX 1
     |  `--SCAN TABLE t1 VIRTUAL TABLE INDEX 0:a EQ ?
     `--INDEX 2
        `--SCAN TABLE t1 VIRTUAL TABLE INDEX 0:b EQ ?
}

do_eqp_test 1.4 {
  SELECT * FROM t1 WHERE a LIKE 'abc%' OR b = 'def';
} {
  QUERY PLAN
  `--MULTI-INDEX OR
     |--INDEX 1
     |  `--SCAN TABLE t1 VIRTUAL TABLE INDEX 0:a LIKE ?
     `--INDEX 2
        `--SCAN TABLE t1 VIRTUAL TABLE INDEX 0:b EQ ?
}

do_execsql_test 1.5 {
  CREATE TABLE ttt(a, b, c);

  INSERT INTO ttt VALUES(1, 'two',   'three');
  INSERT INTO ttt VALUES(2, 'one',   'two');
146
147
148
149
150
151
152

153

154
155
156
157
158
159
160
161
  }

  do_eqp_test 2.2 {
    SELECT * FROM t2 WHERE x LIKE 'abc%' OR y = 'def'
  } [string map {"\n  " \n} {
    QUERY PLAN
    `--MULTI-INDEX OR

       |--SEARCH TABLE t2 USING INDEX t2x (x>? AND x<?)

       `--SEARCH TABLE t2 USING INDEX t2y (y=?)
  }]
}

#-------------------------------------------------------------------------
# Test that any PRIMARY KEY within a sqlite3_decl_vtab() CREATE TABLE 
# statement is currently ignored.
#







>
|
>
|







150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
  }

  do_eqp_test 2.2 {
    SELECT * FROM t2 WHERE x LIKE 'abc%' OR y = 'def'
  } [string map {"\n  " \n} {
    QUERY PLAN
    `--MULTI-INDEX OR
       |--INDEX 1
       |  `--SEARCH TABLE t2 USING INDEX t2x (x>? AND x<?)
       `--INDEX 2
          `--SEARCH TABLE t2 USING INDEX t2y (y=?)
  }]
}

#-------------------------------------------------------------------------
# Test that any PRIMARY KEY within a sqlite3_decl_vtab() CREATE TABLE 
# statement is currently ignored.
#
Changes to test/cost.test.
54
55
56
57
58
59
60

61

62

63
64
65
66
67
68
69
70
do_eqp_test 3.2 {
  SELECT a FROM t5 
  WHERE b IS NULL OR c IS NULL OR d IS NULL 
  ORDER BY a;
} {
  QUERY PLAN
  |--MULTI-INDEX OR

  |  |--SEARCH TABLE t5 USING INDEX t5b (b=?)

  |  |--SEARCH TABLE t5 USING INDEX t5c (c=?)

  |  `--SEARCH TABLE t5 USING INDEX t5d (d=?)
  `--USE TEMP B-TREE FOR ORDER BY
}

#-------------------------------------------------------------------------
# If there is no likelihood() or stat3 data, SQLite assumes that a closed
# range scan (e.g. one constrained by "col BETWEEN ? AND ?" constraint)
# visits 1/64 of the rows in a table.







>
|
>
|
>
|







54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
do_eqp_test 3.2 {
  SELECT a FROM t5 
  WHERE b IS NULL OR c IS NULL OR d IS NULL 
  ORDER BY a;
} {
  QUERY PLAN
  |--MULTI-INDEX OR
  |  |--INDEX 1
  |  |  `--SEARCH TABLE t5 USING INDEX t5b (b=?)
  |  |--INDEX 2
  |  |  `--SEARCH TABLE t5 USING INDEX t5c (c=?)
  |  `--INDEX 3
  |     `--SEARCH TABLE t5 USING INDEX t5d (d=?)
  `--USE TEMP B-TREE FOR ORDER BY
}

#-------------------------------------------------------------------------
# If there is no likelihood() or stat3 data, SQLite assumes that a closed
# range scan (e.g. one constrained by "col BETWEEN ? AND ?" constraint)
# visits 1/64 of the rows in a table.
120
121
122
123
124
125
126

127

128
129
130
131
132
133
134
135
}

do_eqp_test 6.2 {
  SELECT a FROM t3 WHERE (b BETWEEN 2 AND 4) OR c=100 ORDER BY a
} {
  QUERY PLAN
  |--MULTI-INDEX OR

  |  |--SEARCH TABLE t3 USING INDEX t3i1 (b>? AND b<?)

  |  `--SEARCH TABLE t3 USING INDEX t3i2 (c=?)
  `--USE TEMP B-TREE FOR ORDER BY
}

#-------------------------------------------------------------------------
#
reset_db
do_execsql_test 7.1 {







>
|
>
|







123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
}

do_eqp_test 6.2 {
  SELECT a FROM t3 WHERE (b BETWEEN 2 AND 4) OR c=100 ORDER BY a
} {
  QUERY PLAN
  |--MULTI-INDEX OR
  |  |--INDEX 1
  |  |  `--SEARCH TABLE t3 USING INDEX t3i1 (b>? AND b<?)
  |  `--INDEX 2
  |     `--SEARCH TABLE t3 USING INDEX t3i2 (c=?)
  `--USE TEMP B-TREE FOR ORDER BY
}

#-------------------------------------------------------------------------
#
reset_db
do_execsql_test 7.1 {
145
146
147
148
149
150
151

152

153
154
155
156
157
158
159
160
do_eqp_test 7.2 {
  SELECT a FROM t1
     WHERE (b>=950 AND b<=1010) OR (b IS NULL AND c NOT NULL)
  ORDER BY a
} {
  QUERY PLAN
  |--MULTI-INDEX OR

  |  |--SEARCH TABLE t1 USING INDEX t1b (b>? AND b<?)

  |  `--SEARCH TABLE t1 USING INDEX t1b (b=?)
  `--USE TEMP B-TREE FOR ORDER BY
}

do_eqp_test 7.3 {
  SELECT rowid FROM t1
  WHERE (+b IS NULL AND c NOT NULL AND d NOT NULL)
        OR (b NOT NULL AND c IS NULL AND d NOT NULL)







>
|
>
|







150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
do_eqp_test 7.2 {
  SELECT a FROM t1
     WHERE (b>=950 AND b<=1010) OR (b IS NULL AND c NOT NULL)
  ORDER BY a
} {
  QUERY PLAN
  |--MULTI-INDEX OR
  |  |--INDEX 1
  |  |  `--SEARCH TABLE t1 USING INDEX t1b (b>? AND b<?)
  |  `--INDEX 2
  |     `--SEARCH TABLE t1 USING INDEX t1b (b=?)
  `--USE TEMP B-TREE FOR ORDER BY
}

do_eqp_test 7.3 {
  SELECT rowid FROM t1
  WHERE (+b IS NULL AND c NOT NULL AND d NOT NULL)
        OR (b NOT NULL AND c IS NULL AND d NOT NULL)
Changes to test/eqp.test.
41
42
43
44
45
46
47

48

49
50
51
52
53
54
55
56
57

58

59
60
61
62
63
64
65
66
}

do_eqp_test 1.2 {
  SELECT * FROM t2, t1 WHERE t1.a=1 OR t1.b=2;
} {
  QUERY PLAN
  |--MULTI-INDEX OR

  |  |--SEARCH TABLE t1 USING INDEX i1 (a=?)

  |  `--SEARCH TABLE t1 USING INDEX i2 (b=?)
  `--SCAN TABLE t2
}
do_eqp_test 1.3 {
  SELECT * FROM t2 CROSS JOIN t1 WHERE t1.a=1 OR t1.b=2;
} {
  QUERY PLAN
  |--SCAN TABLE t2
  `--MULTI-INDEX OR

     |--SEARCH TABLE t1 USING INDEX i1 (a=?)

     `--SEARCH TABLE t1 USING INDEX i2 (b=?)
}
do_eqp_test 1.3 {
  SELECT a FROM t1 ORDER BY a
} {
  QUERY PLAN
  `--SCAN TABLE t1 USING COVERING INDEX i1
}







>
|
>
|








>
|
>
|







41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
}

do_eqp_test 1.2 {
  SELECT * FROM t2, t1 WHERE t1.a=1 OR t1.b=2;
} {
  QUERY PLAN
  |--MULTI-INDEX OR
  |  |--INDEX 1
  |  |  `--SEARCH TABLE t1 USING INDEX i1 (a=?)
  |  `--INDEX 2
  |     `--SEARCH TABLE t1 USING INDEX i2 (b=?)
  `--SCAN TABLE t2
}
do_eqp_test 1.3 {
  SELECT * FROM t2 CROSS JOIN t1 WHERE t1.a=1 OR t1.b=2;
} {
  QUERY PLAN
  |--SCAN TABLE t2
  `--MULTI-INDEX OR
     |--INDEX 1
     |  `--SEARCH TABLE t1 USING INDEX i1 (a=?)
     `--INDEX 2
        `--SEARCH TABLE t1 USING INDEX i2 (b=?)
}
do_eqp_test 1.3 {
  SELECT a FROM t1 ORDER BY a
} {
  QUERY PLAN
  `--SCAN TABLE t1 USING COVERING INDEX i1
}
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
# Test cases eqp-3.* - tests for select statements that use sub-selects.
#
do_eqp_test 3.1.1 {
  SELECT (SELECT x FROM t1 AS sub) FROM t1;
} {
  QUERY PLAN
  |--SCAN TABLE t1
  `--SCALAR SUBQUERY
     `--SCAN TABLE t1 AS sub
}
do_eqp_test 3.1.2 {
  SELECT * FROM t1 WHERE (SELECT x FROM t1 AS sub);
} {
  QUERY PLAN
  |--SCAN TABLE t1
  `--SCALAR SUBQUERY
     `--SCAN TABLE t1 AS sub
}
do_eqp_test 3.1.3 {
  SELECT * FROM t1 WHERE (SELECT x FROM t1 AS sub ORDER BY y);
} {
  QUERY PLAN
  |--SCAN TABLE t1
  `--SCALAR SUBQUERY
     |--SCAN TABLE t1 AS sub
     `--USE TEMP B-TREE FOR ORDER BY
}
do_eqp_test 3.1.4 {
  SELECT * FROM t1 WHERE (SELECT x FROM t2 ORDER BY x);
} {
  QUERY PLAN
  |--SCAN TABLE t1
  `--SCALAR SUBQUERY
     `--SCAN TABLE t2 USING COVERING INDEX t2i1
}

det 3.2.1 {
  SELECT * FROM (SELECT * FROM t1 ORDER BY x LIMIT 10) ORDER BY y LIMIT 5
} {
  QUERY PLAN







|







|







|








|







225
226
227
228
229
230
231
232
233
234
235
236
237
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
# Test cases eqp-3.* - tests for select statements that use sub-selects.
#
do_eqp_test 3.1.1 {
  SELECT (SELECT x FROM t1 AS sub) FROM t1;
} {
  QUERY PLAN
  |--SCAN TABLE t1
  `--SCALAR SUBQUERY xxxxxx
     `--SCAN TABLE t1 AS sub
}
do_eqp_test 3.1.2 {
  SELECT * FROM t1 WHERE (SELECT x FROM t1 AS sub);
} {
  QUERY PLAN
  |--SCAN TABLE t1
  `--SCALAR SUBQUERY xxxxxx
     `--SCAN TABLE t1 AS sub
}
do_eqp_test 3.1.3 {
  SELECT * FROM t1 WHERE (SELECT x FROM t1 AS sub ORDER BY y);
} {
  QUERY PLAN
  |--SCAN TABLE t1
  `--SCALAR SUBQUERY xxxxxx
     |--SCAN TABLE t1 AS sub
     `--USE TEMP B-TREE FOR ORDER BY
}
do_eqp_test 3.1.4 {
  SELECT * FROM t1 WHERE (SELECT x FROM t2 ORDER BY x);
} {
  QUERY PLAN
  |--SCAN TABLE t1
  `--SCALAR SUBQUERY xxxxxx
     `--SCAN TABLE t2 USING COVERING INDEX t2i1
}

det 3.2.1 {
  SELECT * FROM (SELECT * FROM t1 ORDER BY x LIMIT 10) ORDER BY y LIMIT 5
} {
  QUERY PLAN
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
     `--SCAN TABLE t2
}
det 3.3.3 {
  SELECT * FROM t1 WHERE EXISTS (SELECT y FROM t2 WHERE t1.x!=t2.x)
} {
  QUERY PLAN
  |--SCAN TABLE t1
  `--CORRELATED SCALAR SUBQUERY
     `--SCAN TABLE t2
}

#-------------------------------------------------------------------------
# Test cases eqp-4.* - tests for composite select statements.
#
do_eqp_test 4.1.1 {







|







302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
     `--SCAN TABLE t2
}
det 3.3.3 {
  SELECT * FROM t1 WHERE EXISTS (SELECT y FROM t2 WHERE t1.x!=t2.x)
} {
  QUERY PLAN
  |--SCAN TABLE t1
  `--CORRELATED SCALAR SUBQUERY xxxxxx
     `--SCAN TABLE t2
}

#-------------------------------------------------------------------------
# Test cases eqp-4.* - tests for composite select statements.
#
do_eqp_test 4.1.1 {
809
810
811
812
813
814
815
816
817
818
819
820
821
822
823
824
825
826
    AND event.objid=thread.last
  ORDER BY 1;
} {
  QUERY PLAN
  |--MATERIALIZE xxxxxx
  |  |--SCAN TABLE forumpost AS x USING INDEX forumthread
  |  |--USING ROWID SEARCH ON TABLE private FOR IN-OPERATOR
  |  |--CORRELATED SCALAR SUBQUERY
  |  |  |--SEARCH TABLE forumpost USING COVERING INDEX forumthread (froot=?)
  |  |  `--USING ROWID SEARCH ON TABLE private FOR IN-OPERATOR
  |  `--USE TEMP B-TREE FOR ORDER BY
  |--SCAN SUBQUERY xxxxxx
  |--SEARCH TABLE blob USING INTEGER PRIMARY KEY (rowid=?)
  |--SEARCH TABLE event USING INTEGER PRIMARY KEY (rowid=?)
  `--USE TEMP B-TREE FOR ORDER BY
}

finish_test







|










813
814
815
816
817
818
819
820
821
822
823
824
825
826
827
828
829
830
    AND event.objid=thread.last
  ORDER BY 1;
} {
  QUERY PLAN
  |--MATERIALIZE xxxxxx
  |  |--SCAN TABLE forumpost AS x USING INDEX forumthread
  |  |--USING ROWID SEARCH ON TABLE private FOR IN-OPERATOR
  |  |--CORRELATED SCALAR SUBQUERY xxxxxx
  |  |  |--SEARCH TABLE forumpost USING COVERING INDEX forumthread (froot=?)
  |  |  `--USING ROWID SEARCH ON TABLE private FOR IN-OPERATOR
  |  `--USE TEMP B-TREE FOR ORDER BY
  |--SCAN SUBQUERY xxxxxx
  |--SEARCH TABLE blob USING INTEGER PRIMARY KEY (rowid=?)
  |--SEARCH TABLE event USING INTEGER PRIMARY KEY (rowid=?)
  `--USE TEMP B-TREE FOR ORDER BY
}

finish_test
Changes to test/join5.test.
263
264
265
266
267
268
269

270

271
272
273
274
275
276
277
278
  SELECT * FROM t1 LEFT JOIN t2 ON (
    t2.x = t1.x AND (t2.y=? OR (t2.y=? AND t2.z IS NOT NULL))
  );
} {
  QUERY PLAN
  |--SCAN TABLE t1
  `--MULTI-INDEX OR

     |--SEARCH TABLE t2 USING INDEX t2xy (x=? AND y=?)

     `--SEARCH TABLE t2 USING INDEX t2xy (x=? AND y=?)
}

do_execsql_test 7.3 {
  CREATE TABLE t3(x);

  CREATE TABLE t4(x, y, z);
  CREATE INDEX t4xy ON t4(x, y);







>
|
>
|







263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
  SELECT * FROM t1 LEFT JOIN t2 ON (
    t2.x = t1.x AND (t2.y=? OR (t2.y=? AND t2.z IS NOT NULL))
  );
} {
  QUERY PLAN
  |--SCAN TABLE t1
  `--MULTI-INDEX OR
     |--INDEX 1
     |  `--SEARCH TABLE t2 USING INDEX t2xy (x=? AND y=?)
     `--INDEX 2
        `--SEARCH TABLE t2 USING INDEX t2xy (x=? AND y=?)
}

do_execsql_test 7.3 {
  CREATE TABLE t3(x);

  CREATE TABLE t4(x, y, z);
  CREATE INDEX t4xy ON t4(x, y);
Changes to test/tkt-80ba201079.test.
106
107
108
109
110
111
112


113
114
115
116
117
118
119
             AND entry_id IN (SELECT change_id
                              FROM object_changes
                               WHERE obj_context = 'exported_pools'));
  }
} {300 object_change 2048}
do_test tkt-80ba2-201 {
  db eval {


    CREATE INDEX timeline_entry_id_idx on timeline(entry_id);
    SELECT entry_type,
           entry_types.name,
           entry_id
      FROM timeline JOIN entry_types ON entry_type = entry_types.id
     WHERE (entry_types.name = 'cli_command' AND entry_id=2114)
        OR (entry_types.name = 'object_change'







>
>







106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
             AND entry_id IN (SELECT change_id
                              FROM object_changes
                               WHERE obj_context = 'exported_pools'));
  }
} {300 object_change 2048}
do_test tkt-80ba2-201 {
  db eval {
PRAGMA vdbe_debug=on;
PRAGMA vdbe_addoptrace=on;
    CREATE INDEX timeline_entry_id_idx on timeline(entry_id);
    SELECT entry_type,
           entry_types.name,
           entry_id
      FROM timeline JOIN entry_types ON entry_type = entry_types.id
     WHERE (entry_types.name = 'cli_command' AND entry_id=2114)
        OR (entry_types.name = 'object_change'
Changes to test/where7.test.
23349
23350
23351
23352
23353
23354
23355

23356

23357
23358
23359
23360
23361
23362
      AND t302.c3 > 1287603136
      AND (t301.c4 = 1407449685622784
           OR t301.c8 = 1407424651264000)
   ORDER BY t302.c5 LIMIT 200;
} {
  QUERY PLAN
  |--MULTI-INDEX OR

  |  |--SEARCH TABLE t301 USING COVERING INDEX t301_c4 (c4=?)

  |  `--SEARCH TABLE t301 USING INTEGER PRIMARY KEY (rowid=?)
  |--SEARCH TABLE t302 USING INDEX t302_c8_c3 (c8=? AND c3>?)
  `--USE TEMP B-TREE FOR ORDER BY
}

finish_test







>
|
>
|





23349
23350
23351
23352
23353
23354
23355
23356
23357
23358
23359
23360
23361
23362
23363
23364
      AND t302.c3 > 1287603136
      AND (t301.c4 = 1407449685622784
           OR t301.c8 = 1407424651264000)
   ORDER BY t302.c5 LIMIT 200;
} {
  QUERY PLAN
  |--MULTI-INDEX OR
  |  |--INDEX 1
  |  |  `--SEARCH TABLE t301 USING COVERING INDEX t301_c4 (c4=?)
  |  `--INDEX 2
  |     `--SEARCH TABLE t301 USING INTEGER PRIMARY KEY (rowid=?)
  |--SEARCH TABLE t302 USING INDEX t302_c8_c3 (c8=? AND c3>?)
  `--USE TEMP B-TREE FOR ORDER BY
}

finish_test
Changes to test/where9.test.
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
  do_eqp_test where9-3.1 {
    SELECT t2.a FROM t1, t2
    WHERE t1.a=80 AND ((t1.c=t2.c AND t1.d=t2.d) OR t1.f=t2.f)
  } [string map {"\n  " \n} {
    QUERY PLAN
    |--SEARCH TABLE t1 USING INTEGER PRIMARY KEY (rowid=?)
    `--MULTI-INDEX OR

       |--SEARCH TABLE t2 USING INDEX t2d (d=?)

       `--SEARCH TABLE t2 USING COVERING INDEX t2f (f=?)
  }]
  do_eqp_test where9-3.2 {
    SELECT coalesce(t2.a,9999)
    FROM t1 LEFT JOIN t2 ON (t1.c+1=t2.c AND t1.d=t2.d) OR (t1.f||'x')=t2.f
    WHERE t1.a=80
  } [string map {"\n  " \n} {
    QUERY PLAN
    |--SEARCH TABLE t1 USING INTEGER PRIMARY KEY (rowid=?)
    `--MULTI-INDEX OR

       |--SEARCH TABLE t2 USING INDEX t2d (d=?)

       `--SEARCH TABLE t2 USING COVERING INDEX t2f (f=?)
  }]
} 

# Make sure that INDEXED BY and multi-index OR clauses play well with
# one another.
#
do_test where9-4.1 {







>
|
>
|









>
|
>
|







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
  do_eqp_test where9-3.1 {
    SELECT t2.a FROM t1, t2
    WHERE t1.a=80 AND ((t1.c=t2.c AND t1.d=t2.d) OR t1.f=t2.f)
  } [string map {"\n  " \n} {
    QUERY PLAN
    |--SEARCH TABLE t1 USING INTEGER PRIMARY KEY (rowid=?)
    `--MULTI-INDEX OR
       |--INDEX 1
       |  `--SEARCH TABLE t2 USING INDEX t2d (d=?)
       `--INDEX 3
          `--SEARCH TABLE t2 USING COVERING INDEX t2f (f=?)
  }]
  do_eqp_test where9-3.2 {
    SELECT coalesce(t2.a,9999)
    FROM t1 LEFT JOIN t2 ON (t1.c+1=t2.c AND t1.d=t2.d) OR (t1.f||'x')=t2.f
    WHERE t1.a=80
  } [string map {"\n  " \n} {
    QUERY PLAN
    |--SEARCH TABLE t1 USING INTEGER PRIMARY KEY (rowid=?)
    `--MULTI-INDEX OR
       |--INDEX 1
       |  `--SEARCH TABLE t2 USING INDEX t2d (d=?)
       `--INDEX 2
          `--SEARCH TABLE t2 USING COVERING INDEX t2f (f=?)
  }]
} 

# Make sure that INDEXED BY and multi-index OR clauses play well with
# one another.
#
do_test where9-4.1 {
452
453
454
455
456
457
458

459

460
461
462
463
464
465
466
467
# the former is an equality test which is expected to return fewer rows.
#
do_eqp_test where9-5.1 {
  SELECT a FROM t1 WHERE b>1000 AND (c=31031 OR d IS NULL)
} {
  QUERY PLAN
  `--MULTI-INDEX OR

     |--SEARCH TABLE t1 USING INDEX t1c (c=?)

     `--SEARCH TABLE t1 USING INDEX t1d (d=?)
}

# In contrast, b=1000 is preferred over any OR-clause.
#
do_eqp_test where9-5.2 {
  SELECT a FROM t1 WHERE b=1000 AND (c=31031 OR d IS NULL)
} {SEARCH TABLE t1 USING INDEX t1b (b=?)}







>
|
>
|







456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
# the former is an equality test which is expected to return fewer rows.
#
do_eqp_test where9-5.1 {
  SELECT a FROM t1 WHERE b>1000 AND (c=31031 OR d IS NULL)
} {
  QUERY PLAN
  `--MULTI-INDEX OR
     |--INDEX 1
     |  `--SEARCH TABLE t1 USING INDEX t1c (c=?)
     `--INDEX 2
        `--SEARCH TABLE t1 USING INDEX t1d (d=?)
}

# In contrast, b=1000 is preferred over any OR-clause.
#
do_eqp_test where9-5.2 {
  SELECT a FROM t1 WHERE b=1000 AND (c=31031 OR d IS NULL)
} {SEARCH TABLE t1 USING INDEX t1b (b=?)}
Changes to test/whereI.test.
27
28
29
30
31
32
33

34

35
36
37
38
39
40
41
42
}

do_eqp_test 1.1 {
  SELECT a FROM t1 WHERE b='b' OR c='x'
} {
  QUERY PLAN
  `--MULTI-INDEX OR

     |--SEARCH TABLE t1 USING INDEX i1 (b=?)

     `--SEARCH TABLE t1 USING INDEX i2 (c=?)
}

do_execsql_test 1.2 {
  SELECT a FROM t1 WHERE b='b' OR c='x'
} {2 3}

do_execsql_test 1.3 {







>
|
>
|







27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
}

do_eqp_test 1.1 {
  SELECT a FROM t1 WHERE b='b' OR c='x'
} {
  QUERY PLAN
  `--MULTI-INDEX OR
     |--INDEX 1
     |  `--SEARCH TABLE t1 USING INDEX i1 (b=?)
     `--INDEX 2
        `--SEARCH TABLE t1 USING INDEX i2 (c=?)
}

do_execsql_test 1.2 {
  SELECT a FROM t1 WHERE b='b' OR c='x'
} {2 3}

do_execsql_test 1.3 {
57
58
59
60
61
62
63

64

65
66
67
68
69
70
71
72
}

do_eqp_test 2.1 {
  SELECT a FROM t2 WHERE b='b' OR c='x'
} {
  QUERY PLAN
  `--MULTI-INDEX OR

     |--SEARCH TABLE t2 USING INDEX i3 (b=?)

     `--SEARCH TABLE t2 USING INDEX i4 (c=?)
}

do_execsql_test 2.2 {
  SELECT a FROM t2 WHERE b='b' OR c='x'
} {ii iii}

do_execsql_test 2.3 {







>
|
>
|







59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
}

do_eqp_test 2.1 {
  SELECT a FROM t2 WHERE b='b' OR c='x'
} {
  QUERY PLAN
  `--MULTI-INDEX OR
     |--INDEX 1
     |  `--SEARCH TABLE t2 USING INDEX i3 (b=?)
     `--INDEX 2
        `--SEARCH TABLE t2 USING INDEX i4 (c=?)
}

do_execsql_test 2.2 {
  SELECT a FROM t2 WHERE b='b' OR c='x'
} {ii iii}

do_execsql_test 2.3 {
Changes to test/with3.test.
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
     SELECT * FROM c, w2, w1
     WHERE c.id=w2.pk AND c.id=w1.pk;
} {
  QUERY PLAN
  |--MATERIALIZE xxxxxx
  |  |--SETUP
  |  |  |--SCAN CONSTANT ROW
  |  |  `--SCALAR SUBQUERY
  |  |     `--SCAN TABLE w2
  |  `--RECURSIVE STEP
  |     |--SCAN TABLE w1
  |     `--SCAN TABLE c
  |--SCAN SUBQUERY xxxxxx
  |--SEARCH TABLE w2 USING INTEGER PRIMARY KEY (rowid=?)
  `--SEARCH TABLE w1 USING INTEGER PRIMARY KEY (rowid=?)







|







116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
     SELECT * FROM c, w2, w1
     WHERE c.id=w2.pk AND c.id=w1.pk;
} {
  QUERY PLAN
  |--MATERIALIZE xxxxxx
  |  |--SETUP
  |  |  |--SCAN CONSTANT ROW
  |  |  `--SCALAR SUBQUERY xxxxxx
  |  |     `--SCAN TABLE w2
  |  `--RECURSIVE STEP
  |     |--SCAN TABLE w1
  |     `--SCAN TABLE c
  |--SCAN SUBQUERY xxxxxx
  |--SEARCH TABLE w2 USING INTEGER PRIMARY KEY (rowid=?)
  `--SEARCH TABLE w1 USING INTEGER PRIMARY KEY (rowid=?)