Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
| 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: |
73c93f5a2a32ee8c5d07c9ba33b2641e |
| User & Date: | dan 2010-11-09 14:49:00 |
|
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 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