/ Check-in [73c93f5a]
Login

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

Overview
Comment:Further enhancements and fixes for explain query plan.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | experimental
Files: files | file ages | folders
SHA1: 73c93f5a2a32ee8c5d07c9ba33b2641e72626627
User & Date: dan 2010-11-09 14:49:00
Context
2010-11-09
17:35
Add missing comments and fix other issues with routines used by new EQP features. check-in: 925f35c5 user: dan tags: experimental
14:49
Further enhancements and fixes for explain query plan. check-in: 73c93f5a user: dan tags: experimental
2010-11-08
19:01
Experimental changes to EXPLAIN QUERY PLAN. check-in: f4747eb8 user: dan tags: experimental
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/expr.c.

  1595   1595     */
  1596   1596     if( !ExprHasAnyProperty(pExpr, EP_VarSelect) && !pParse->pTriggerTab ){
  1597   1597       int mem = ++pParse->nMem;
  1598   1598       sqlite3VdbeAddOp1(v, OP_If, mem);
  1599   1599       testAddr = sqlite3VdbeAddOp2(v, OP_Integer, 1, mem);
  1600   1600       assert( testAddr>0 || pParse->db->mallocFailed );
  1601   1601     }
         1602  +
         1603  +#ifndef SQLITE_OMIT_EXPLAIN
         1604  +  if( pParse->explain==2 ){
         1605  +    char *zMsg = sqlite3MPrintf(
         1606  +        pParse->db, "EXECUTE %s%s SUBQUERY %d", testAddr?"":"CORRELATED ",
         1607  +        pExpr->op==TK_IN?"LIST":"SCALAR", pParse->iNextSelectId
         1608  +    );
         1609  +    sqlite3VdbeAddOp4(v, OP_Explain, pParse->iSelectId, 0, 0, zMsg, P4_DYNAMIC);
         1610  +  }
         1611  +#endif
  1602   1612   
  1603   1613     switch( pExpr->op ){
  1604   1614       case TK_IN: {
  1605   1615         char affinity;              /* Affinity of the LHS of the IN */
  1606   1616         KeyInfo keyInfo;            /* Keyinfo for the generated table */
  1607   1617         int addr;                   /* Address of OP_OpenEphemeral instruction */
  1608   1618         Expr *pLeft = pExpr->pLeft; /* the LHS of the IN operator */

Changes to src/select.c.

   776    776     if( pParse->explain==2 ){
   777    777       Vdbe *v = pParse->pVdbe;
   778    778       char *zMsg = sqlite3MPrintf(pParse->db, "USE TEMP B-TREE FOR %s", zUsage);
   779    779       sqlite3VdbeAddOp4(v, OP_Explain, pParse->iSelectId, 0, 0, zMsg, P4_DYNAMIC);
   780    780     }
   781    781   }
   782    782   # define explainRestoreSelectId() pParse->iSelectId = iRestoreSelectId
          783  +# define explainAssignSelectId(pItem, id) pItem->iSelectId = id
   783    784   #else
   784    785   # define explainRestoreSelectId()
   785    786   # define explainTempTable(y,z)
          787  +# define explainAssignSelectId(y,z)
   786    788   #endif
   787    789   
   788    790   /*
   789    791   ** If the inner loop was generated using a non-null pOrderBy argument,
   790    792   ** then the results were placed in a sorter.  After the loop is terminated
   791    793   ** we need to run the sorter and output the results.  The following
   792    794   ** routine generates the code needed to do that.
................................................................................
  3675   3677           isAgg = 1;
  3676   3678           p->selFlags |= SF_Aggregate;
  3677   3679         }
  3678   3680         i = -1;
  3679   3681       }else{
  3680   3682         sqlite3SelectDestInit(&dest, SRT_EphemTab, pItem->iCursor);
  3681   3683         assert( pItem->isPopulated==0 );
         3684  +      explainAssignSelectId(pItem, pParse->iNextSelectId);
  3682   3685         sqlite3Select(pParse, pSub, &dest);
  3683   3686         pItem->isPopulated = 1;
  3684   3687       }
  3685   3688       if( /*pParse->nErr ||*/ db->mallocFailed ){
  3686   3689         goto select_end;
  3687   3690       }
  3688   3691       pParse->nHeight -= sqlite3SelectExprHeight(p);

Changes to src/sqliteInt.h.

  1823   1823       u8 notIndexed;    /* True if there is a NOT INDEXED clause */
  1824   1824       int iCursor;      /* The VDBE cursor number used to access this table */
  1825   1825       Expr *pOn;        /* The ON clause of a join */
  1826   1826       IdList *pUsing;   /* The USING clause of a join */
  1827   1827       Bitmask colUsed;  /* Bit N (1<<N) set if column N of pTab is used */
  1828   1828       char *zIndex;     /* Identifier from "INDEXED BY <zIndex>" clause */
  1829   1829       Index *pIndex;    /* Index structure corresponding to zIndex, if any */
         1830  +#ifndef SQLITE_OMIT_EXPLAIN
         1831  +    int iSelectId;    /* If pSelect!=0, the id of the sub-select in EQP */
         1832  +#endif
  1830   1833     } a[1];             /* One entry for each identifier on the list */
  1831   1834   };
  1832   1835   
  1833   1836   /*
  1834   1837   ** Permitted values of the SrcList.a.jointype field
  1835   1838   */
  1836   1839   #define JT_INNER     0x0001    /* Any kind of inner or cross join */

Changes to src/where.c.

  3161   3161   }
  3162   3162   
  3163   3163   static void codeOneLoopExplain(
  3164   3164     Parse *pParse,                  /* Parse context */
  3165   3165     SrcList *pTabList,              /* Table list this loop refers to */
  3166   3166     WhereLevel *pLevel,             /* Scan to write OP_Explain opcode for */
  3167   3167     int iLevel,                     /* Value for "level" column of output */
  3168         -  int iFrom                       /* Value for "from" column of output */
         3168  +  int iFrom,                      /* Value for "from" column of output */
         3169  +  u16 wctrlFlags                  /* Flags passed to sqlite3WhereBegin() */
  3169   3170   ){
  3170   3171     if( pParse->explain==2 ){
  3171   3172       u32 flags = pLevel->plan.wsFlags;
  3172   3173       struct SrcList_item *pItem = &pTabList->a[pLevel->iFrom];
  3173   3174       Vdbe *v = pParse->pVdbe;
  3174   3175       sqlite3 *db = pParse->db;
  3175   3176       char *zMsg;
         3177  +    sqlite3_int64 nRow;           /* Expected number of rows visited by scan */
         3178  +    int iId = pParse->iSelectId;  /* Select id (left-most output column) */
  3176   3179   
  3177         -    if( flags & WHERE_MULTI_OR ) return;
         3180  +    if( (flags&WHERE_MULTI_OR) || (wctrlFlags&WHERE_ONETABLE_ONLY) ) return;
  3178   3181   
  3179         -    zMsg = sqlite3MPrintf(db, "TABLE %s", pItem->zName);
         3182  +    if( pItem->pSelect ){
         3183  +      zMsg = sqlite3MPrintf(db, "SCAN SUBQUERY %d", pItem->iSelectId);
         3184  +    }else{
         3185  +      zMsg = sqlite3MPrintf(db, "SCAN TABLE %s", pItem->zName);
         3186  +    }
         3187  +
  3180   3188       if( pItem->zAlias ){
  3181   3189         zMsg = sqlite3MAppendf(db, zMsg, "%s AS %s", zMsg, pItem->zAlias);
  3182   3190       }
  3183   3191       if( (flags & WHERE_INDEXED)!=0 ){
  3184   3192         char *zWhere = indexRangeText(db, pLevel, pItem->pTab);
  3185         -      zMsg = sqlite3MAppendf(db, zMsg, "%s WITH %s%sINDEX%s%s%s", zMsg, 
         3193  +      zMsg = sqlite3MAppendf(db, zMsg, "%s BY %s%sINDEX%s%s%s", zMsg, 
  3186   3194             ((flags & WHERE_TEMP_INDEX)?"AUTOMATIC ":""),
  3187   3195             ((flags & WHERE_IDX_ONLY)?"COVERING ":""),
  3188   3196             ((flags & WHERE_TEMP_INDEX)?"":" "),
  3189   3197             ((flags & WHERE_TEMP_INDEX)?"": pLevel->plan.u.pIdx->zName),
  3190   3198             zWhere
  3191   3199         );
  3192   3200         sqlite3DbFree(db, zWhere);
  3193   3201       }else if( flags & (WHERE_ROWID_EQ|WHERE_ROWID_RANGE) ){
  3194         -      zMsg = sqlite3MAppendf(db, zMsg, "%s USING INTEGER PRIMARY KEY", zMsg);
         3202  +      zMsg = sqlite3MAppendf(db, zMsg, "%s BY INTEGER PRIMARY KEY", zMsg);
  3195   3203   
  3196   3204         if( flags&WHERE_ROWID_EQ ){
  3197   3205           zMsg = sqlite3MAppendf(db, zMsg, "%s (rowid=?)", zMsg);
  3198   3206         }else if( flags&WHERE_BTM_LIMIT && flags&WHERE_TOP_LIMIT ){
  3199   3207           zMsg = sqlite3MAppendf(db, zMsg, "%s (rowid>? AND rowid<?)", zMsg);
  3200   3208         }else if( flags&WHERE_BTM_LIMIT ){
  3201   3209           zMsg = sqlite3MAppendf(db, zMsg, "%s (rowid>?)", zMsg);
................................................................................
  3206   3214   #ifndef SQLITE_OMIT_VIRTUALTABLE
  3207   3215       else if( (flags & WHERE_VIRTUALTABLE)!=0 ){
  3208   3216         sqlite3_index_info *pVtabIdx = pLevel->plan.u.pVtabIdx;
  3209   3217         zMsg = sqlite3MAppendf(db, zMsg, "%s VIRTUAL TABLE INDEX %d:%s", zMsg,
  3210   3218                     pVtabIdx->idxNum, pVtabIdx->idxStr);
  3211   3219       }
  3212   3220   #endif
  3213         -    zMsg = sqlite3MAppendf(db, zMsg, 
  3214         -        "%s (~%lld rows)", zMsg, (sqlite3_int64)(pLevel->plan.nRow)
  3215         -    );
  3216         -    sqlite3VdbeAddOp4(
  3217         -        v, OP_Explain, pParse->iSelectId, iLevel, iFrom, zMsg, P4_DYNAMIC);
         3221  +    if( wctrlFlags&(WHERE_ORDERBY_MIN|WHERE_ORDERBY_MAX) ){
         3222  +      nRow = 1;
         3223  +    }else{
         3224  +      nRow = (sqlite3_int64)pLevel->plan.nRow;
         3225  +    }
         3226  +    zMsg = sqlite3MAppendf(db, zMsg, "%s (~%lld rows)", zMsg, nRow);
         3227  +    sqlite3VdbeAddOp4(v, OP_Explain, iId, iLevel, iFrom, zMsg, P4_DYNAMIC);
  3218   3228     }
  3219   3229   }
  3220   3230   #else
  3221         -# define codeOneLoopExplain(w,x,y.z)
         3231  +# define codeOneLoopExplain(u,v,w,x,y,z)
  3222   3232   #endif /* SQLITE_OMIT_EXPLAIN */
  3223   3233   
  3224   3234   
  3225   3235   /*
  3226   3236   ** Generate code for the start of the iLevel-th loop in the WHERE clause
  3227   3237   ** implementation described by pWInfo.
  3228   3238   */
................................................................................
  3760   3770           WhereInfo *pSubWInfo;          /* Info for single OR-term scan */
  3761   3771           /* Loop through table entries that match term pOrTerm. */
  3762   3772           pSubWInfo = sqlite3WhereBegin(pParse, pOrTab, pOrTerm->pExpr, 0,
  3763   3773                           WHERE_OMIT_OPEN | WHERE_OMIT_CLOSE |
  3764   3774                           WHERE_FORCE_TABLE | WHERE_ONETABLE_ONLY);
  3765   3775           if( pSubWInfo ){
  3766   3776             codeOneLoopExplain(
  3767         -              pParse, pOrTab, &pSubWInfo->a[0], iLevel, pLevel->iFrom
         3777  +              pParse, pOrTab, &pSubWInfo->a[0], iLevel, pLevel->iFrom, 0
  3768   3778             );
  3769   3779             if( (wctrlFlags & WHERE_DUPLICATES_OK)==0 ){
  3770   3780               int iSet = ((ii==pOrWc->nTerm-1)?-1:ii);
  3771   3781               int r;
  3772   3782               r = sqlite3ExprCodeGetColumn(pParse, pTabItem->pTab, -1, iCur, 
  3773   3783                                            regRowid);
  3774   3784               sqlite3VdbeAddOp4Int(v, OP_RowSetTest, regRowset,
................................................................................
  4417   4427   
  4418   4428     /* Generate the code to do the search.  Each iteration of the for
  4419   4429     ** loop below generates code for a single nested loop of the VM
  4420   4430     ** program.
  4421   4431     */
  4422   4432     notReady = ~(Bitmask)0;
  4423   4433     for(i=0; i<nTabList; i++){
  4424         -    if( (wctrlFlags&WHERE_ONETABLE_ONLY)==0 ){
  4425         -      codeOneLoopExplain(pParse, pTabList, &pWInfo->a[i],i,pWInfo->a[i].iFrom);
  4426         -    }
         4434  +    WhereLevel *pLevel = &pWInfo->a[i];
         4435  +    codeOneLoopExplain(pParse, pTabList, pLevel, i, pLevel->iFrom, wctrlFlags);
  4427   4436       notReady = codeOneLoopStart(pWInfo, i, wctrlFlags, notReady);
  4428         -    pWInfo->iContinue = pWInfo->a[i].addrCont;
         4437  +    pWInfo->iContinue = pLevel->addrCont;
  4429   4438     }
  4430   4439   
  4431   4440   #ifdef SQLITE_TEST  /* For testing and debugging use only */
  4432   4441     /* Record in the query plan information about the current table
  4433   4442     ** and the index used to access it (if any).  If the table itself
  4434   4443     ** is not used, its name is just '{}'.  If no index is used
  4435   4444     ** the index is listed as "{}".  If the primary key is used the

Changes to test/eqp.test.

    23     23   # eqp-4.*:        Compound select statements.
    24     24   #
    25     25   
    26     26   proc do_eqp_test {name sql res} {
    27     27     set res [list {*}$res]
    28     28     uplevel do_execsql_test $name [list "EXPLAIN QUERY PLAN $sql"] [list $res]
    29     29   }
           30  +proc det {args} { uplevel do_eqp_test $args }
    30     31   
    31     32   do_execsql_test 1.1 {
    32     33     CREATE TABLE t1(a, b);
    33     34     CREATE INDEX i1 ON t1(a);
    34     35     CREATE INDEX i2 ON t1(b);
    35     36     CREATE TABLE t2(a, b);
    36     37     CREATE TABLE t3(a, b);
    37     38   }
    38     39   
    39     40   do_eqp_test 1.2 {
    40     41     SELECT * FROM t2, t1 WHERE t1.a=1 OR t1.b=2;
    41     42   } {
    42         -  0 0 1 {TABLE t1 WITH INDEX i1 (a=?) (~10 rows)} 
    43         -  0 0 1 {TABLE t1 WITH INDEX i2 (b=?) (~10 rows)} 
    44         -  0 1 0 {TABLE t2 (~1000000 rows)}
           43  +  0 0 1 {SCAN TABLE t1 BY INDEX i1 (a=?) (~10 rows)} 
           44  +  0 0 1 {SCAN TABLE t1 BY INDEX i2 (b=?) (~10 rows)} 
           45  +  0 1 0 {SCAN TABLE t2 (~1000000 rows)}
    45     46   }
    46     47   do_eqp_test 1.3 {
    47     48     SELECT * FROM t2 CROSS JOIN t1 WHERE t1.a=1 OR t1.b=2;
    48     49   } {
    49         -  0 0 0 {TABLE t2 (~1000000 rows)}
    50         -  0 1 1 {TABLE t1 WITH INDEX i1 (a=?) (~10 rows)} 
    51         -  0 1 1 {TABLE t1 WITH INDEX i2 (b=?) (~10 rows)} 
           50  +  0 0 0 {SCAN TABLE t2 (~1000000 rows)}
           51  +  0 1 1 {SCAN TABLE t1 BY INDEX i1 (a=?) (~10 rows)} 
           52  +  0 1 1 {SCAN TABLE t1 BY INDEX i2 (b=?) (~10 rows)} 
    52     53   }
    53     54   do_eqp_test 1.3 {
    54     55     SELECT a FROM t1 ORDER BY a
    55     56   } {
    56         -  0 0 0 {TABLE t1 WITH COVERING INDEX i1 (~1000000 rows)}
           57  +  0 0 0 {SCAN TABLE t1 BY COVERING INDEX i1 (~1000000 rows)}
    57     58   }
    58     59   do_eqp_test 1.4 {
    59     60     SELECT a FROM t1 ORDER BY +a
    60     61   } {
    61         -  0 0 0 {TABLE t1 (~1000000 rows)}
           62  +  0 0 0 {SCAN TABLE t1 (~1000000 rows)}
    62     63     0 0 0 {USE TEMP B-TREE FOR ORDER BY}
    63     64   }
    64     65   do_eqp_test 1.5 {
    65     66     SELECT a FROM t1 WHERE a=4
    66     67   } {
    67         -  0 0 0 {TABLE t1 WITH COVERING INDEX i1 (a=?) (~10 rows)}
           68  +  0 0 0 {SCAN TABLE t1 BY COVERING INDEX i1 (a=?) (~10 rows)}
    68     69   }
    69     70   do_eqp_test 1.6 {
    70     71     SELECT DISTINCT count(*) FROM t3 GROUP BY a;
    71     72   } {
    72         -  0 0 0 {TABLE t3 (~1000000 rows)}
           73  +  0 0 0 {SCAN TABLE t3 (~1000000 rows)}
    73     74     0 0 0 {USE TEMP B-TREE FOR GROUP BY}
    74     75     0 0 0 {USE TEMP B-TREE FOR DISTINCT}
    75     76   }
    76     77   
    77     78   #-------------------------------------------------------------------------
    78     79   # Test cases eqp-2.* - tests for single select statements.
    79     80   #
................................................................................
    81     82   do_execsql_test 2.1 {
    82     83     CREATE TABLE t1(x, y);
    83     84   
    84     85     CREATE TABLE t2(x, y);
    85     86     CREATE INDEX t2i1 ON t2(x);
    86     87   }
    87     88   
    88         -do_eqp_test 2.2.1 {
    89         -  SELECT DISTINCT min(x), max(x) FROM t1 GROUP BY x ORDER BY 1
    90         -} {
    91         -  0 0 0 {TABLE t1 (~1000000 rows)}
           89  +det 2.2.1 "SELECT DISTINCT min(x), max(x) FROM t1 GROUP BY x ORDER BY 1" {
           90  +  0 0 0 {SCAN TABLE t1 (~1000000 rows)}
    92     91     0 0 0 {USE TEMP B-TREE FOR GROUP BY}
    93     92     0 0 0 {USE TEMP B-TREE FOR DISTINCT}
    94     93     0 0 0 {USE TEMP B-TREE FOR ORDER BY}
    95     94   }
    96         -
    97         -do_eqp_test 2.2.2 {
    98         -  SELECT DISTINCT min(x), max(x) FROM t2 GROUP BY x ORDER BY 1
    99         -} {
   100         -  0 0 0 {TABLE t2 WITH COVERING INDEX t2i1 (~1000000 rows)}
           95  +det 2.2.2 "SELECT DISTINCT min(x), max(x) FROM t2 GROUP BY x ORDER BY 1" {
           96  +  0 0 0 {SCAN TABLE t2 BY COVERING INDEX t2i1 (~1000000 rows)}
           97  +  0 0 0 {USE TEMP B-TREE FOR DISTINCT}
           98  +  0 0 0 {USE TEMP B-TREE FOR ORDER BY}
           99  +}
          100  +det 2.2.3 "SELECT DISTINCT * FROM t1" {
          101  +  0 0 0 {SCAN TABLE t1 (~1000000 rows)}
          102  +  0 0 0 {USE TEMP B-TREE FOR DISTINCT}
          103  +}
          104  +det 2.2.4 "SELECT DISTINCT * FROM t1, t2" {
          105  +  0 0 0 {SCAN TABLE t1 (~1000000 rows)}
          106  +  0 1 1 {SCAN TABLE t2 (~1000000 rows)}
          107  +  0 0 0 {USE TEMP B-TREE FOR DISTINCT}
          108  +}
          109  +det 2.2.5 "SELECT DISTINCT * FROM t1, t2 ORDER BY t1.x" {
          110  +  0 0 0 {SCAN TABLE t1 (~1000000 rows)}
          111  +  0 1 1 {SCAN TABLE t2 (~1000000 rows)}
   101    112     0 0 0 {USE TEMP B-TREE FOR DISTINCT}
   102    113     0 0 0 {USE TEMP B-TREE FOR ORDER BY}
   103    114   }
          115  +det 2.2.6 "SELECT DISTINCT t2.x FROM t1, t2 ORDER BY t2.x" {
          116  +  0 0 1 {SCAN TABLE t2 BY COVERING INDEX t2i1 (~1000000 rows)}
          117  +  0 1 0 {SCAN TABLE t1 (~1000000 rows)}
          118  +}
          119  +
          120  +det 2.3.1 "SELECT max(x) FROM t2" {
          121  +  0 0 0 {SCAN TABLE t2 BY COVERING INDEX t2i1 (~1 rows)}
          122  +}
          123  +det 2.3.2 "SELECT min(x) FROM t2" {
          124  +  0 0 0 {SCAN TABLE t2 BY COVERING INDEX t2i1 (~1 rows)}
          125  +}
          126  +det 2.3.3 "SELECT min(x), max(x) FROM t2" {
          127  +  0 0 0 {SCAN TABLE t2 (~1000000 rows)}
          128  +}
          129  +
          130  +det 2.4.1 "SELECT * FROM t1 WHERE rowid=?" {
          131  +  0 0 0 {SCAN TABLE t1 BY INTEGER PRIMARY KEY (rowid=?) (~1 rows)}
          132  +}
          133  +
          134  +
   104    135   
   105    136   #-------------------------------------------------------------------------
   106    137   # Test cases eqp-3.* - tests for select statements that use sub-selects.
   107    138   #
   108    139   do_eqp_test 3.1.1 {
   109    140     SELECT (SELECT x FROM t1 AS sub) FROM t1;
   110    141   } {
   111         -  0 0 0 {TABLE t1 (~1000000 rows)} 
   112         -  1 0 0 {TABLE t1 AS sub (~1000000 rows)}
          142  +  0 0 0 {SCAN TABLE t1 (~1000000 rows)}
          143  +  0 0 0 {EXECUTE SCALAR SUBQUERY 1}
          144  +  1 0 0 {SCAN TABLE t1 AS sub (~1000000 rows)}
          145  +}
          146  +do_eqp_test 3.1.2 {
          147  +  SELECT * FROM t1 WHERE (SELECT x FROM t1 AS sub);
          148  +} {
          149  +  0 0 0 {SCAN TABLE t1 (~1000000 rows)}
          150  +  0 0 0 {EXECUTE SCALAR SUBQUERY 1}
          151  +  1 0 0 {SCAN TABLE t1 AS sub (~1000000 rows)}
          152  +}
          153  +do_eqp_test 3.1.3 {
          154  +  SELECT * FROM t1 WHERE (SELECT x FROM t1 AS sub ORDER BY y);
          155  +} {
          156  +  0 0 0 {SCAN TABLE t1 (~1000000 rows)}
          157  +  0 0 0 {EXECUTE SCALAR SUBQUERY 1}
          158  +  1 0 0 {SCAN TABLE t1 AS sub (~1000000 rows)}
          159  +  1 0 0 {USE TEMP B-TREE FOR ORDER BY}
          160  +}
          161  +do_eqp_test 3.1.4 {
          162  +  SELECT * FROM t1 WHERE (SELECT x FROM t2 ORDER BY x);
          163  +} {
          164  +  0 0 0 {SCAN TABLE t1 (~1000000 rows)}
          165  +  0 0 0 {EXECUTE SCALAR SUBQUERY 1}
          166  +  1 0 0 {SCAN TABLE t2 BY COVERING INDEX t2i1 (~1000000 rows)}
          167  +}
          168  +
          169  +det 3.2.1 {
          170  +  SELECT * FROM (SELECT * FROM t1 ORDER BY x LIMIT 10) ORDER BY y LIMIT 5
          171  +} {
          172  +  1 0 0 {SCAN TABLE t1 (~1000000 rows)} 
          173  +  1 0 0 {USE TEMP B-TREE FOR ORDER BY} 
          174  +  0 0 0 {SCAN SUBQUERY 1 (~1000000 rows)} 
          175  +  0 0 0 {USE TEMP B-TREE FOR ORDER BY}
          176  +}
          177  +det 3.2.2 {
          178  +  SELECT * FROM 
          179  +    (SELECT * FROM t1 ORDER BY x LIMIT 10) AS x1,
          180  +    (SELECT * FROM t2 ORDER BY x LIMIT 10) AS x2
          181  +  ORDER BY x2.y LIMIT 5
          182  +} {
          183  +  1 0 0 {SCAN TABLE t1 (~1000000 rows)} 
          184  +  1 0 0 {USE TEMP B-TREE FOR ORDER BY} 
          185  +  2 0 0 {SCAN TABLE t2 BY INDEX t2i1 (~1000000 rows)} 
          186  +  0 0 0 {SCAN SUBQUERY 1 AS x1 (~1000000 rows)} 
          187  +  0 1 1 {SCAN SUBQUERY 2 AS x2 (~1000000 rows)} 
          188  +  0 0 0 {USE TEMP B-TREE FOR ORDER BY}
   113    189   }
   114    190   
   115    191   #-------------------------------------------------------------------------
   116         -# Test cases eqp-4.* - tests for select statements that use sub-selects.
          192  +# Test cases eqp-4.* - tests for composite select statements.
   117    193   #
   118    194   do_eqp_test 4.1.1 {
   119    195     SELECT * FROM t1 UNION ALL SELECT * FROM t2
   120    196   } {
   121         -  1 0 0 {TABLE t1 (~1000000 rows)} 
   122         -  2 0 0 {TABLE t2 (~1000000 rows)} 
          197  +  1 0 0 {SCAN TABLE t1 (~1000000 rows)} 
          198  +  2 0 0 {SCAN TABLE t2 (~1000000 rows)} 
   123    199   }
   124    200   do_eqp_test 4.1.2 {
   125    201     SELECT * FROM t1 UNION ALL SELECT * FROM t2 ORDER BY 2
   126    202   } {
   127         -  1 0 0 {TABLE t1 (~1000000 rows)} 
          203  +  1 0 0 {SCAN TABLE t1 (~1000000 rows)} 
   128    204     1 0 0 {USE TEMP B-TREE FOR ORDER BY}
   129         -  2 0 0 {TABLE t2 (~1000000 rows)} 
          205  +  2 0 0 {SCAN TABLE t2 (~1000000 rows)} 
   130    206     2 0 0 {USE TEMP B-TREE FOR ORDER BY}
   131    207   }
   132    208   do_eqp_test 4.1.3 {
   133    209     SELECT * FROM t1 UNION SELECT * FROM t2 ORDER BY 2
   134    210   } {
   135         -  1 0 0 {TABLE t1 (~1000000 rows)} 
          211  +  1 0 0 {SCAN TABLE t1 (~1000000 rows)} 
   136    212     1 0 0 {USE TEMP B-TREE FOR ORDER BY}
   137         -  2 0 0 {TABLE t2 (~1000000 rows)} 
          213  +  2 0 0 {SCAN TABLE t2 (~1000000 rows)} 
   138    214     2 0 0 {USE TEMP B-TREE FOR ORDER BY}
   139    215   }
   140    216   do_eqp_test 4.1.4 {
   141    217     SELECT * FROM t1 INTERSECT SELECT * FROM t2 ORDER BY 2
   142    218   } {
   143         -  1 0 0 {TABLE t1 (~1000000 rows)} 
          219  +  1 0 0 {SCAN TABLE t1 (~1000000 rows)} 
   144    220     1 0 0 {USE TEMP B-TREE FOR ORDER BY}
   145         -  2 0 0 {TABLE t2 (~1000000 rows)} 
          221  +  2 0 0 {SCAN TABLE t2 (~1000000 rows)} 
   146    222     2 0 0 {USE TEMP B-TREE FOR ORDER BY}
   147    223   }
   148    224   do_eqp_test 4.1.5 {
   149    225     SELECT * FROM t1 EXCEPT SELECT * FROM t2 ORDER BY 2
   150    226   } {
   151         -  1 0 0 {TABLE t1 (~1000000 rows)} 
          227  +  1 0 0 {SCAN TABLE t1 (~1000000 rows)} 
   152    228     1 0 0 {USE TEMP B-TREE FOR ORDER BY}
   153         -  2 0 0 {TABLE t2 (~1000000 rows)} 
          229  +  2 0 0 {SCAN TABLE t2 (~1000000 rows)} 
   154    230     2 0 0 {USE TEMP B-TREE FOR ORDER BY}
   155    231   }
   156    232   
   157    233   do_eqp_test 4.2.2 {
   158    234     SELECT * FROM t1 UNION ALL SELECT * FROM t2 ORDER BY 1
   159    235   } {
   160         -  1 0 0 {TABLE t1 (~1000000 rows)} 
          236  +  1 0 0 {SCAN TABLE t1 (~1000000 rows)} 
   161    237     1 0 0 {USE TEMP B-TREE FOR ORDER BY}
   162         -  2 0 0 {TABLE t2 WITH INDEX t2i1 (~1000000 rows)} 
          238  +  2 0 0 {SCAN TABLE t2 BY INDEX t2i1 (~1000000 rows)} 
   163    239   }
   164         -
   165         -# Todo: Why are the following not the same as the UNION ALL case above?
   166    240   do_eqp_test 4.2.3 {
   167    241     SELECT * FROM t1 UNION SELECT * FROM t2 ORDER BY 1
   168    242   } {
   169         -  1 0 0 {TABLE t1 (~1000000 rows)} 
          243  +  1 0 0 {SCAN TABLE t1 (~1000000 rows)} 
   170    244     1 0 0 {USE TEMP B-TREE FOR ORDER BY}
   171         -  2 0 0 {TABLE t2 (~1000000 rows)} 
          245  +  2 0 0 {SCAN TABLE t2 (~1000000 rows)} 
   172    246     2 0 0 {USE TEMP B-TREE FOR ORDER BY}
   173    247   }
   174    248   do_eqp_test 4.2.4 {
   175    249     SELECT * FROM t1 INTERSECT SELECT * FROM t2 ORDER BY 1
   176    250   } {
   177         -  1 0 0 {TABLE t1 (~1000000 rows)} 
          251  +  1 0 0 {SCAN TABLE t1 (~1000000 rows)} 
   178    252     1 0 0 {USE TEMP B-TREE FOR ORDER BY}
   179         -  2 0 0 {TABLE t2 (~1000000 rows)} 
          253  +  2 0 0 {SCAN TABLE t2 (~1000000 rows)} 
   180    254     2 0 0 {USE TEMP B-TREE FOR ORDER BY}
   181    255   }
   182    256   do_eqp_test 4.2.5 {
   183    257     SELECT * FROM t1 EXCEPT SELECT * FROM t2 ORDER BY 1
   184    258   } {
   185         -  1 0 0 {TABLE t1 (~1000000 rows)} 
          259  +  1 0 0 {SCAN TABLE t1 (~1000000 rows)} 
   186    260     1 0 0 {USE TEMP B-TREE FOR ORDER BY}
   187         -  2 0 0 {TABLE t2 (~1000000 rows)} 
          261  +  2 0 0 {SCAN TABLE t2 (~1000000 rows)} 
   188    262     2 0 0 {USE TEMP B-TREE FOR ORDER BY}
   189    263   }
   190    264   
          265  +do_eqp_test 4.3.1 {
          266  +  SELECT x FROM t1 UNION SELECT x FROM t2
          267  +} {
          268  +  1 0 0 {SCAN TABLE t1 (~1000000 rows)} 
          269  +  2 0 0 {SCAN TABLE t2 (~1000000 rows)} 
          270  +}
   191    271   
   192    272   finish_test
   193    273