/ Check-in [00fb8468]
Login

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

Overview
Comment:Add a row of EXPLAIN QUERY PLAN output for each composite select operation (UNION, EXCEPT etc.) in the query.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | experimental
Files: files | file ages | folders
SHA1: 00fb8468b5f2c48a3c91b86803bf306a0331496f
User & Date: dan 2010-11-11 16:46:40
Context
2010-11-11
17:48
Use "COMPOUND" instead of "COMPOSITE" in the EXPLAIN QUERY PLAN output to describe UNION, UNION ALL, EXCEPT and INTERSECT operations. check-in: 28643b85 user: dan tags: experimental
16:46
Add a row of EXPLAIN QUERY PLAN output for each composite select operation (UNION, EXCEPT etc.) in the query. check-in: 00fb8468 user: dan tags: experimental
11:43
Fix a bug in the EXPLAIN QUERY PLAN code. check-in: 7ae06895 user: dan tags: experimental
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/select.c.

   767    767         pInfo->aColl[i] = pColl;
   768    768         pInfo->aSortOrder[i] = pItem->sortOrder;
   769    769       }
   770    770     }
   771    771     return pInfo;
   772    772   }
   773    773   
          774  +#ifndef SQLITE_OMIT_COMPOUND_SELECT
          775  +/*
          776  +** Name of the connection operator, used for error messages.
          777  +*/
          778  +static const char *selectOpName(int id){
          779  +  char *z;
          780  +  switch( id ){
          781  +    case TK_ALL:       z = "UNION ALL";   break;
          782  +    case TK_INTERSECT: z = "INTERSECT";   break;
          783  +    case TK_EXCEPT:    z = "EXCEPT";      break;
          784  +    default:           z = "UNION";       break;
          785  +  }
          786  +  return z;
          787  +}
          788  +#endif /* SQLITE_OMIT_COMPOUND_SELECT */
          789  +
   774    790   #ifndef SQLITE_OMIT_EXPLAIN
   775    791   /*
   776    792   ** Unless an "EXPLAIN QUERY PLAN" command is being processed, this function
   777    793   ** is a no-op. Otherwise, it adds a single row of output to the EQP result,
   778    794   ** where the caption is of the form:
   779    795   **
   780    796   **   "USE TEMP B-TREE FOR xxx"
................................................................................
   785    801   static void explainTempTable(Parse *pParse, const char *zUsage){
   786    802     if( pParse->explain==2 ){
   787    803       Vdbe *v = pParse->pVdbe;
   788    804       char *zMsg = sqlite3MPrintf(pParse->db, "USE TEMP B-TREE FOR %s", zUsage);
   789    805       sqlite3VdbeAddOp4(v, OP_Explain, pParse->iSelectId, 0, 0, zMsg, P4_DYNAMIC);
   790    806     }
   791    807   }
          808  +
          809  +/*
          810  +** Unless an "EXPLAIN QUERY PLAN" command is being processed, this function
          811  +** is a no-op. Otherwise, it adds a single row of output to the EQP result,
          812  +** where the caption is of one of the two forms:
          813  +**
          814  +**   "COMPOSITE SUBQUERIES iSub1 and iSub2 (op)"
          815  +**   "COMPOSITE SUBQUERIES iSub1 and iSub2 USING TEMP B-TREE (op)"
          816  +**
          817  +** where iSub1 and iSub2 are the integers passed as the corresponding
          818  +** function parameters, and op is the text representation of the parameter
          819  +** of the same name. The parameter "op" must be one of TK_UNION, TK_EXCEPT,
          820  +** TK_INTERSECT or TK_ALL. The first form is used if argument bUseTmp is 
          821  +** false, or the second form if it is true.
          822  +*/
          823  +static void explainComposite(
          824  +  Parse *pParse,                  /* Parse context */
          825  +  int op,                         /* One of TK_UNION, TK_EXCEPT etc. */
          826  +  int iSub1,                      /* Subquery id 1 */
          827  +  int iSub2,                      /* Subquery id 2 */
          828  +  int bUseTmp                     /* True if a temp table was used */
          829  +){
          830  +  assert( op==TK_UNION || op==TK_EXCEPT || op==TK_INTERSECT || op==TK_ALL );
          831  +  if( pParse->explain==2 ){
          832  +    Vdbe *v = pParse->pVdbe;
          833  +    char *zMsg = sqlite3MPrintf(
          834  +        pParse->db, "COMPOSITE SUBQUERIES %d AND %d %s(%s)", iSub1, iSub2,
          835  +        bUseTmp?"USING TEMP B-TREE ":"", selectOpName(op)
          836  +    );
          837  +    sqlite3VdbeAddOp4(v, OP_Explain, pParse->iSelectId, 0, 0, zMsg, P4_DYNAMIC);
          838  +  }
          839  +}
   792    840   
   793    841   /*
   794    842   ** Assign expression b to lvalue a. A second, no-op, version of this macro
   795    843   ** is provided when SQLITE_OMIT_EXPLAIN is defined. This allows the code
   796    844   ** in sqlite3Select() to assign values to structure member variables that
   797    845   ** only exist if SQLITE_OMIT_EXPLAIN is not defined without polluting the
   798    846   ** code with #ifndef directives.
   799    847   */
   800    848   # define explainSetInteger(a, b) a = b
   801    849   
   802    850   #else
   803    851   /* No-op versions of the explainXXX() functions and macros. */
   804    852   # define explainTempTable(y,z)
          853  +# define explainComposite(v,w,x,y,z)
   805    854   # define explainSetInteger(y,z)
   806    855   #endif
   807    856   
   808    857   /*
   809    858   ** If the inner loop was generated using a non-null pOrderBy argument,
   810    859   ** then the results were placed in a sorter.  After the loop is terminated
   811    860   ** we need to run the sorter and output the results.  The following
................................................................................
  1147   1196         sqlite3VdbeSetColName(v, i, COLNAME_NAME, 
  1148   1197             sqlite3DbStrDup(db, pEList->a[i].zSpan), SQLITE_DYNAMIC);
  1149   1198       }
  1150   1199     }
  1151   1200     generateColumnTypes(pParse, pTabList, pEList);
  1152   1201   }
  1153   1202   
  1154         -#ifndef SQLITE_OMIT_COMPOUND_SELECT
  1155         -/*
  1156         -** Name of the connection operator, used for error messages.
  1157         -*/
  1158         -static const char *selectOpName(int id){
  1159         -  char *z;
  1160         -  switch( id ){
  1161         -    case TK_ALL:       z = "UNION ALL";   break;
  1162         -    case TK_INTERSECT: z = "INTERSECT";   break;
  1163         -    case TK_EXCEPT:    z = "EXCEPT";      break;
  1164         -    default:           z = "UNION";       break;
  1165         -  }
  1166         -  return z;
  1167         -}
  1168         -#endif /* SQLITE_OMIT_COMPOUND_SELECT */
  1169         -
  1170   1203   /*
  1171   1204   ** Given a an expression list (which is really the list of expressions
  1172   1205   ** that form the result set of a SELECT statement) compute appropriate
  1173   1206   ** column names for a table that would hold the expression list.
  1174   1207   **
  1175   1208   ** All column names will be unique.
  1176   1209   **
................................................................................
  1496   1529   ){
  1497   1530     int rc = SQLITE_OK;   /* Success code from a subroutine */
  1498   1531     Select *pPrior;       /* Another SELECT immediately to our left */
  1499   1532     Vdbe *v;              /* Generate code to this VDBE */
  1500   1533     SelectDest dest;      /* Alternative data destination */
  1501   1534     Select *pDelete = 0;  /* Chain of simple selects to delete */
  1502   1535     sqlite3 *db;          /* Database connection */
         1536  +#ifndef SQLITE_OMIT_EXPLAIN
         1537  +  int iSub1;            /* EQP id of left-hand query */
         1538  +  int iSub2;            /* EQP id of right-hand query */
         1539  +#endif
  1503   1540   
  1504   1541     /* Make sure there is no ORDER BY or LIMIT clause on prior SELECTs.  Only
  1505   1542     ** the last (right-most) SELECT in the series may have an ORDER BY or LIMIT.
  1506   1543     */
  1507   1544     assert( p && p->pPrior );  /* Calling function guarantees this much */
  1508   1545     db = pParse->db;
  1509   1546     pPrior = p->pPrior;
................................................................................
  1556   1593     */
  1557   1594     switch( p->op ){
  1558   1595       case TK_ALL: {
  1559   1596         int addr = 0;
  1560   1597         assert( !pPrior->pLimit );
  1561   1598         pPrior->pLimit = p->pLimit;
  1562   1599         pPrior->pOffset = p->pOffset;
         1600  +      explainSetInteger(iSub1, pParse->iNextSelectId);
  1563   1601         rc = sqlite3Select(pParse, pPrior, &dest);
  1564   1602         p->pLimit = 0;
  1565   1603         p->pOffset = 0;
  1566   1604         if( rc ){
  1567   1605           goto multi_select_end;
  1568   1606         }
  1569   1607         p->pPrior = 0;
  1570   1608         p->iLimit = pPrior->iLimit;
  1571   1609         p->iOffset = pPrior->iOffset;
  1572   1610         if( p->iLimit ){
  1573   1611           addr = sqlite3VdbeAddOp1(v, OP_IfZero, p->iLimit);
  1574   1612           VdbeComment((v, "Jump ahead if LIMIT reached"));
  1575   1613         }
         1614  +      explainSetInteger(iSub2, pParse->iNextSelectId);
  1576   1615         rc = sqlite3Select(pParse, p, &dest);
  1577   1616         testcase( rc!=SQLITE_OK );
  1578   1617         pDelete = p->pPrior;
  1579   1618         p->pPrior = pPrior;
  1580   1619         if( addr ){
  1581   1620           sqlite3VdbeJumpHere(v, addr);
  1582   1621         }
................................................................................
  1616   1655           assert( p->pEList );
  1617   1656         }
  1618   1657   
  1619   1658         /* Code the SELECT statements to our left
  1620   1659         */
  1621   1660         assert( !pPrior->pOrderBy );
  1622   1661         sqlite3SelectDestInit(&uniondest, priorOp, unionTab);
         1662  +      explainSetInteger(iSub1, pParse->iNextSelectId);
  1623   1663         rc = sqlite3Select(pParse, pPrior, &uniondest);
  1624   1664         if( rc ){
  1625   1665           goto multi_select_end;
  1626   1666         }
  1627   1667   
  1628   1668         /* Code the current SELECT statement
  1629   1669         */
................................................................................
  1635   1675         }
  1636   1676         p->pPrior = 0;
  1637   1677         pLimit = p->pLimit;
  1638   1678         p->pLimit = 0;
  1639   1679         pOffset = p->pOffset;
  1640   1680         p->pOffset = 0;
  1641   1681         uniondest.eDest = op;
         1682  +      explainSetInteger(iSub2, pParse->iNextSelectId);
  1642   1683         rc = sqlite3Select(pParse, p, &uniondest);
  1643   1684         testcase( rc!=SQLITE_OK );
  1644   1685         /* Query flattening in sqlite3Select() might refill p->pOrderBy.
  1645   1686         ** Be sure to delete p->pOrderBy, therefore, to avoid a memory leak. */
  1646   1687         sqlite3ExprListDelete(db, p->pOrderBy);
  1647   1688         pDelete = p->pPrior;
  1648   1689         p->pPrior = pPrior;
................................................................................
  1700   1741         p->addrOpenEphm[0] = addr;
  1701   1742         p->pRightmost->selFlags |= SF_UsesEphemeral;
  1702   1743         assert( p->pEList );
  1703   1744   
  1704   1745         /* Code the SELECTs to our left into temporary table "tab1".
  1705   1746         */
  1706   1747         sqlite3SelectDestInit(&intersectdest, SRT_Union, tab1);
         1748  +      explainSetInteger(iSub1, pParse->iNextSelectId);
  1707   1749         rc = sqlite3Select(pParse, pPrior, &intersectdest);
  1708   1750         if( rc ){
  1709   1751           goto multi_select_end;
  1710   1752         }
  1711   1753   
  1712   1754         /* Code the current SELECT into temporary table "tab2"
  1713   1755         */
................................................................................
  1716   1758         p->addrOpenEphm[1] = addr;
  1717   1759         p->pPrior = 0;
  1718   1760         pLimit = p->pLimit;
  1719   1761         p->pLimit = 0;
  1720   1762         pOffset = p->pOffset;
  1721   1763         p->pOffset = 0;
  1722   1764         intersectdest.iParm = tab2;
         1765  +      explainSetInteger(iSub2, pParse->iNextSelectId);
  1723   1766         rc = sqlite3Select(pParse, p, &intersectdest);
  1724   1767         testcase( rc!=SQLITE_OK );
  1725   1768         pDelete = p->pPrior;
  1726   1769         p->pPrior = pPrior;
  1727   1770         sqlite3ExprDelete(db, p->pLimit);
  1728   1771         p->pLimit = pLimit;
  1729   1772         p->pOffset = pOffset;
................................................................................
  1751   1794         sqlite3VdbeAddOp2(v, OP_Next, tab1, iStart);
  1752   1795         sqlite3VdbeResolveLabel(v, iBreak);
  1753   1796         sqlite3VdbeAddOp2(v, OP_Close, tab2, 0);
  1754   1797         sqlite3VdbeAddOp2(v, OP_Close, tab1, 0);
  1755   1798         break;
  1756   1799       }
  1757   1800     }
         1801  +
         1802  +  explainComposite(pParse, p->op, iSub1, iSub2, p->op!=TK_ALL);
  1758   1803   
  1759   1804     /* Compute collating sequences used by 
  1760   1805     ** temporary tables needed to implement the compound select.
  1761   1806     ** Attach the KeyInfo structure to all temporary tables.
  1762   1807     **
  1763   1808     ** This section is run by the right-most SELECT statement only.
  1764   1809     ** SELECT statements to the left always skip this part.  The right-most
................................................................................
  2095   2140     int op;               /* One of TK_ALL, TK_UNION, TK_EXCEPT, TK_INTERSECT */
  2096   2141     KeyInfo *pKeyDup = 0; /* Comparison information for duplicate removal */
  2097   2142     KeyInfo *pKeyMerge;   /* Comparison information for merging rows */
  2098   2143     sqlite3 *db;          /* Database connection */
  2099   2144     ExprList *pOrderBy;   /* The ORDER BY clause */
  2100   2145     int nOrderBy;         /* Number of terms in the ORDER BY clause */
  2101   2146     int *aPermute;        /* Mapping from ORDER BY terms to result set columns */
         2147  +#ifndef SQLITE_OMIT_EXPLAIN
         2148  +  int iSub1;            /* EQP id of left-hand query */
         2149  +  int iSub2;            /* EQP id of right-hand query */
         2150  +#endif
  2102   2151   
  2103   2152     assert( p->pOrderBy!=0 );
  2104   2153     assert( pKeyDup==0 ); /* "Managed" code needs this.  Ticket #3382. */
  2105   2154     db = pParse->db;
  2106   2155     v = pParse->pVdbe;
  2107   2156     assert( v!=0 );       /* Already thrown the error if VDBE alloc failed */
  2108   2157     labelEnd = sqlite3VdbeMakeLabel(v);
................................................................................
  2248   2297   
  2249   2298   
  2250   2299     /* Generate a coroutine to evaluate the SELECT statement to the
  2251   2300     ** left of the compound operator - the "A" select.
  2252   2301     */
  2253   2302     VdbeNoopComment((v, "Begin coroutine for left SELECT"));
  2254   2303     pPrior->iLimit = regLimitA;
         2304  +  explainSetInteger(iSub1, pParse->iNextSelectId);
  2255   2305     sqlite3Select(pParse, pPrior, &destA);
  2256   2306     sqlite3VdbeAddOp2(v, OP_Integer, 1, regEofA);
  2257   2307     sqlite3VdbeAddOp1(v, OP_Yield, regAddrA);
  2258   2308     VdbeNoopComment((v, "End coroutine for left SELECT"));
  2259   2309   
  2260   2310     /* Generate a coroutine to evaluate the SELECT statement on 
  2261   2311     ** the right - the "B" select
................................................................................
  2262   2312     */
  2263   2313     addrSelectB = sqlite3VdbeCurrentAddr(v);
  2264   2314     VdbeNoopComment((v, "Begin coroutine for right SELECT"));
  2265   2315     savedLimit = p->iLimit;
  2266   2316     savedOffset = p->iOffset;
  2267   2317     p->iLimit = regLimitB;
  2268   2318     p->iOffset = 0;  
         2319  +  explainSetInteger(iSub2, pParse->iNextSelectId);
  2269   2320     sqlite3Select(pParse, p, &destB);
  2270   2321     p->iLimit = savedLimit;
  2271   2322     p->iOffset = savedOffset;
  2272   2323     sqlite3VdbeAddOp2(v, OP_Integer, 1, regEofB);
  2273   2324     sqlite3VdbeAddOp1(v, OP_Yield, regAddrB);
  2274   2325     VdbeNoopComment((v, "End coroutine for right SELECT"));
  2275   2326   
................................................................................
  2392   2443     if( p->pPrior ){
  2393   2444       sqlite3SelectDelete(db, p->pPrior);
  2394   2445     }
  2395   2446     p->pPrior = pPrior;
  2396   2447   
  2397   2448     /*** TBD:  Insert subroutine calls to close cursors on incomplete
  2398   2449     **** subqueries ****/
         2450  +  explainComposite(pParse, p->op, iSub1, iSub2, 0);
  2399   2451     return SQLITE_OK;
  2400   2452   }
  2401   2453   #endif
  2402   2454   
  2403   2455   #if !defined(SQLITE_OMIT_SUBQUERY) || !defined(SQLITE_OMIT_VIEW)
  2404   2456   /* Forward Declarations */
  2405   2457   static void substExprList(sqlite3*, ExprList*, int, ExprList*);
................................................................................
  3734   3786         }
  3735   3787         mxSelect = db->aLimit[SQLITE_LIMIT_COMPOUND_SELECT];
  3736   3788         if( mxSelect && cnt>mxSelect ){
  3737   3789           sqlite3ErrorMsg(pParse, "too many terms in compound SELECT");
  3738   3790           goto select_end;
  3739   3791         }
  3740   3792       }
         3793  +    rc = multiSelect(pParse, p, pDest);
  3741   3794       explainSetInteger(pParse->iSelectId, iRestoreSelectId);
  3742         -    return multiSelect(pParse, p, pDest);
         3795  +    return rc;
  3743   3796     }
  3744   3797   #endif
  3745   3798   
  3746   3799     /* If possible, rewrite the query to use GROUP BY instead of DISTINCT.
  3747   3800     ** GROUP BY might use an index, DISTINCT never does.
  3748   3801     */
  3749   3802     assert( p->pGroupBy==0 || (p->selFlags & SF_Aggregate)!=0 );

Changes to test/eqp.test.

    20     20   # eqp-1.*:        Assorted tests.
    21     21   # eqp-2.*:        Tests for single select statements.
    22     22   # eqp-3.*:        Select statements that execute sub-selects.
    23     23   # eqp-4.*:        Compound select statements.
    24     24   #
    25     25   
    26     26   proc do_eqp_test {name sql res} {
    27         -  set res [list {*}$res]
    28     27     uplevel do_execsql_test $name [list "EXPLAIN QUERY PLAN $sql"] [list $res]
    29     28   }
    30     29   proc det {args} { uplevel do_eqp_test $args }
    31     30   
    32     31   do_execsql_test 1.1 {
    33     32     CREATE TABLE t1(a, b);
    34     33     CREATE INDEX i1 ON t1(a);
................................................................................
   214    213   # Test cases eqp-4.* - tests for composite select statements.
   215    214   #
   216    215   do_eqp_test 4.1.1 {
   217    216     SELECT * FROM t1 UNION ALL SELECT * FROM t2
   218    217   } {
   219    218     1 0 0 {SCAN TABLE t1 (~1000000 rows)} 
   220    219     2 0 0 {SCAN TABLE t2 (~1000000 rows)} 
          220  +  0 0 0 {COMPOSITE SUBQUERIES 1 AND 2 (UNION ALL)} 
   221    221   }
   222    222   do_eqp_test 4.1.2 {
   223    223     SELECT * FROM t1 UNION ALL SELECT * FROM t2 ORDER BY 2
   224    224   } {
   225    225     1 0 0 {SCAN TABLE t1 (~1000000 rows)} 
   226    226     1 0 0 {USE TEMP B-TREE FOR ORDER BY}
   227    227     2 0 0 {SCAN TABLE t2 (~1000000 rows)} 
   228    228     2 0 0 {USE TEMP B-TREE FOR ORDER BY}
          229  +  0 0 0 {COMPOSITE SUBQUERIES 1 AND 2 (UNION ALL)} 
   229    230   }
   230    231   do_eqp_test 4.1.3 {
   231    232     SELECT * FROM t1 UNION SELECT * FROM t2 ORDER BY 2
   232    233   } {
   233    234     1 0 0 {SCAN TABLE t1 (~1000000 rows)} 
   234    235     1 0 0 {USE TEMP B-TREE FOR ORDER BY}
   235    236     2 0 0 {SCAN TABLE t2 (~1000000 rows)} 
   236    237     2 0 0 {USE TEMP B-TREE FOR ORDER BY}
          238  +  0 0 0 {COMPOSITE SUBQUERIES 1 AND 2 (UNION)} 
   237    239   }
   238    240   do_eqp_test 4.1.4 {
   239    241     SELECT * FROM t1 INTERSECT SELECT * FROM t2 ORDER BY 2
   240    242   } {
   241    243     1 0 0 {SCAN TABLE t1 (~1000000 rows)} 
   242    244     1 0 0 {USE TEMP B-TREE FOR ORDER BY}
   243    245     2 0 0 {SCAN TABLE t2 (~1000000 rows)} 
   244    246     2 0 0 {USE TEMP B-TREE FOR ORDER BY}
          247  +  0 0 0 {COMPOSITE SUBQUERIES 1 AND 2 (INTERSECT)} 
   245    248   }
   246    249   do_eqp_test 4.1.5 {
   247    250     SELECT * FROM t1 EXCEPT SELECT * FROM t2 ORDER BY 2
   248    251   } {
   249    252     1 0 0 {SCAN TABLE t1 (~1000000 rows)} 
   250    253     1 0 0 {USE TEMP B-TREE FOR ORDER BY}
   251    254     2 0 0 {SCAN TABLE t2 (~1000000 rows)} 
   252    255     2 0 0 {USE TEMP B-TREE FOR ORDER BY}
          256  +  0 0 0 {COMPOSITE SUBQUERIES 1 AND 2 (EXCEPT)} 
   253    257   }
   254    258   
   255    259   do_eqp_test 4.2.2 {
   256    260     SELECT * FROM t1 UNION ALL SELECT * FROM t2 ORDER BY 1
   257    261   } {
   258    262     1 0 0 {SCAN TABLE t1 (~1000000 rows)} 
   259    263     1 0 0 {USE TEMP B-TREE FOR ORDER BY}
   260    264     2 0 0 {SCAN TABLE t2 BY INDEX t2i1 (~1000000 rows)} 
          265  +  0 0 0 {COMPOSITE SUBQUERIES 1 AND 2 (UNION ALL)} 
   261    266   }
   262    267   do_eqp_test 4.2.3 {
   263    268     SELECT * FROM t1 UNION SELECT * FROM t2 ORDER BY 1
   264    269   } {
   265    270     1 0 0 {SCAN TABLE t1 (~1000000 rows)} 
   266    271     1 0 0 {USE TEMP B-TREE FOR ORDER BY}
   267    272     2 0 0 {SCAN TABLE t2 (~1000000 rows)} 
   268    273     2 0 0 {USE TEMP B-TREE FOR ORDER BY}
          274  +  0 0 0 {COMPOSITE SUBQUERIES 1 AND 2 (UNION)} 
   269    275   }
   270    276   do_eqp_test 4.2.4 {
   271    277     SELECT * FROM t1 INTERSECT SELECT * FROM t2 ORDER BY 1
   272    278   } {
   273    279     1 0 0 {SCAN TABLE t1 (~1000000 rows)} 
   274    280     1 0 0 {USE TEMP B-TREE FOR ORDER BY}
   275    281     2 0 0 {SCAN TABLE t2 (~1000000 rows)} 
   276    282     2 0 0 {USE TEMP B-TREE FOR ORDER BY}
          283  +  0 0 0 {COMPOSITE SUBQUERIES 1 AND 2 (INTERSECT)} 
   277    284   }
   278    285   do_eqp_test 4.2.5 {
   279    286     SELECT * FROM t1 EXCEPT SELECT * FROM t2 ORDER BY 1
   280    287   } {
   281    288     1 0 0 {SCAN TABLE t1 (~1000000 rows)} 
   282    289     1 0 0 {USE TEMP B-TREE FOR ORDER BY}
   283    290     2 0 0 {SCAN TABLE t2 (~1000000 rows)} 
   284    291     2 0 0 {USE TEMP B-TREE FOR ORDER BY}
          292  +  0 0 0 {COMPOSITE SUBQUERIES 1 AND 2 (EXCEPT)} 
   285    293   }
   286    294   
   287    295   do_eqp_test 4.3.1 {
   288    296     SELECT x FROM t1 UNION SELECT x FROM t2
   289    297   } {
   290    298     1 0 0 {SCAN TABLE t1 (~1000000 rows)} 
   291    299     2 0 0 {SCAN TABLE t2 (~1000000 rows)} 
          300  +  0 0 0 {COMPOSITE SUBQUERIES 1 AND 2 USING TEMP B-TREE (UNION)} 
          301  +}
          302  +
          303  +do_eqp_test 4.3.2 {
          304  +  SELECT x FROM t1 UNION SELECT x FROM t2 UNION SELECT x FROM t1
          305  +} {
          306  +  2 0 0 {SCAN TABLE t1 (~1000000 rows)} 
          307  +  3 0 0 {SCAN TABLE t2 (~1000000 rows)} 
          308  +  1 0 0 {COMPOSITE SUBQUERIES 2 AND 3 USING TEMP B-TREE (UNION)}
          309  +  4 0 0 {SCAN TABLE t1 (~1000000 rows)} 
          310  +  0 0 0 {COMPOSITE SUBQUERIES 1 AND 4 USING TEMP B-TREE (UNION)}
          311  +}
          312  +do_eqp_test 4.3.3 {
          313  +  SELECT x FROM t1 UNION SELECT x FROM t2 UNION SELECT x FROM t1 ORDER BY 1
          314  +} {
          315  +  2 0 0 {SCAN TABLE t1 (~1000000 rows)} 
          316  +  2 0 0 {USE TEMP B-TREE FOR ORDER BY} 
          317  +  3 0 0 {SCAN TABLE t2 BY COVERING INDEX t2i1 (~1000000 rows)} 
          318  +  1 0 0 {COMPOSITE SUBQUERIES 2 AND 3 (UNION)} 
          319  +  4 0 0 {SCAN TABLE t1 (~1000000 rows)} 
          320  +  4 0 0 {USE TEMP B-TREE FOR ORDER BY} 
          321  +  0 0 0 {COMPOSITE SUBQUERIES 1 AND 4 (UNION)}
   292    322   }
   293    323   
   294    324   finish_test
   295    325