Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Use the estimated number of rows computed for subqueries in the cost computations for outer queries. |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA1: |
56bbc539246a6dc9f1ae1edb898db7a4 |
User & Date: | drh 2010-11-16 02:49:16.000 |
Context
2010-11-16
| ||
23:10 | Adding the sqlite3_stmt_readonly() interface. (check-in: fd5b2f23dd user: drh tags: trunk) | |
18:56 | Add experimental command "PRAGMA wal_blocking_checkpoint", which uses the busy-handler to block until all readers have finished in order to ensure the next writer will be able to wrap around to the start of the log file. (check-in: 7e3fc2c833 user: dan tags: blocking-checkpoint) | |
02:49 | Use the estimated number of rows computed for subqueries in the cost computations for outer queries. (check-in: 56bbc53924 user: drh tags: trunk) | |
2010-11-15
| ||
21:50 | Change the EQP output for the min/max optimization from "SCAN" to "SEARCH". Other changes in where.c in support of full branch coverage testing. (check-in: d52b593978 user: drh tags: trunk) | |
Changes
Changes to src/select.c.
︙ | ︙ | |||
1429 1430 1431 1432 1433 1434 1435 1436 1437 1438 1439 1440 1441 1442 | v = sqlite3GetVdbe(pParse); if( NEVER(v==0) ) return; /* VDBE should have already been allocated */ if( sqlite3ExprIsInteger(p->pLimit, &n) ){ sqlite3VdbeAddOp2(v, OP_Integer, n, iLimit); VdbeComment((v, "LIMIT counter")); if( n==0 ){ sqlite3VdbeAddOp2(v, OP_Goto, 0, iBreak); } }else{ sqlite3ExprCode(pParse, p->pLimit, iLimit); sqlite3VdbeAddOp1(v, OP_MustBeInt, iLimit); VdbeComment((v, "LIMIT counter")); sqlite3VdbeAddOp2(v, OP_IfZero, iLimit, iBreak); } | > > | 1429 1430 1431 1432 1433 1434 1435 1436 1437 1438 1439 1440 1441 1442 1443 1444 | v = sqlite3GetVdbe(pParse); if( NEVER(v==0) ) return; /* VDBE should have already been allocated */ if( sqlite3ExprIsInteger(p->pLimit, &n) ){ sqlite3VdbeAddOp2(v, OP_Integer, n, iLimit); VdbeComment((v, "LIMIT counter")); if( n==0 ){ sqlite3VdbeAddOp2(v, OP_Goto, 0, iBreak); }else{ if( p->nSelectRow > (double)n ) p->nSelectRow = (double)n; } }else{ sqlite3ExprCode(pParse, p->pLimit, iLimit); sqlite3VdbeAddOp1(v, OP_MustBeInt, iLimit); VdbeComment((v, "LIMIT counter")); sqlite3VdbeAddOp2(v, OP_IfZero, iLimit, iBreak); } |
︙ | ︙ | |||
1590 1591 1592 1593 1594 1595 1596 1597 1598 1599 1600 1601 1602 1603 | } /* Generate code for the left and right SELECT statements. */ switch( p->op ){ case TK_ALL: { int addr = 0; assert( !pPrior->pLimit ); pPrior->pLimit = p->pLimit; pPrior->pOffset = p->pOffset; explainSetInteger(iSub1, pParse->iNextSelectId); rc = sqlite3Select(pParse, pPrior, &dest); p->pLimit = 0; p->pOffset = 0; | > | 1592 1593 1594 1595 1596 1597 1598 1599 1600 1601 1602 1603 1604 1605 1606 | } /* Generate code for the left and right SELECT statements. */ switch( p->op ){ case TK_ALL: { int addr = 0; int nLimit; assert( !pPrior->pLimit ); pPrior->pLimit = p->pLimit; pPrior->pOffset = p->pOffset; explainSetInteger(iSub1, pParse->iNextSelectId); rc = sqlite3Select(pParse, pPrior, &dest); p->pLimit = 0; p->pOffset = 0; |
︙ | ︙ | |||
1612 1613 1614 1615 1616 1617 1618 1619 1620 1621 1622 1623 1624 1625 | VdbeComment((v, "Jump ahead if LIMIT reached")); } explainSetInteger(iSub2, pParse->iNextSelectId); rc = sqlite3Select(pParse, p, &dest); testcase( rc!=SQLITE_OK ); pDelete = p->pPrior; p->pPrior = pPrior; if( addr ){ sqlite3VdbeJumpHere(v, addr); } break; } case TK_EXCEPT: case TK_UNION: { | > > > > > > > | 1615 1616 1617 1618 1619 1620 1621 1622 1623 1624 1625 1626 1627 1628 1629 1630 1631 1632 1633 1634 1635 | VdbeComment((v, "Jump ahead if LIMIT reached")); } explainSetInteger(iSub2, pParse->iNextSelectId); rc = sqlite3Select(pParse, p, &dest); testcase( rc!=SQLITE_OK ); pDelete = p->pPrior; p->pPrior = pPrior; p->nSelectRow += pPrior->nSelectRow; if( pPrior->pLimit && sqlite3ExprIsInteger(pPrior->pLimit, &nLimit) && p->nSelectRow > (double)nLimit ){ p->nSelectRow = (double)nLimit; } if( addr ){ sqlite3VdbeJumpHere(v, addr); } break; } case TK_EXCEPT: case TK_UNION: { |
︙ | ︙ | |||
1684 1685 1686 1687 1688 1689 1690 1691 1692 1693 1694 1695 1696 1697 | testcase( rc!=SQLITE_OK ); /* Query flattening in sqlite3Select() might refill p->pOrderBy. ** Be sure to delete p->pOrderBy, therefore, to avoid a memory leak. */ sqlite3ExprListDelete(db, p->pOrderBy); pDelete = p->pPrior; p->pPrior = pPrior; p->pOrderBy = 0; sqlite3ExprDelete(db, p->pLimit); p->pLimit = pLimit; p->pOffset = pOffset; p->iLimit = 0; p->iOffset = 0; /* Convert the data in the temporary table into whatever form | > | 1694 1695 1696 1697 1698 1699 1700 1701 1702 1703 1704 1705 1706 1707 1708 | testcase( rc!=SQLITE_OK ); /* Query flattening in sqlite3Select() might refill p->pOrderBy. ** Be sure to delete p->pOrderBy, therefore, to avoid a memory leak. */ sqlite3ExprListDelete(db, p->pOrderBy); pDelete = p->pPrior; p->pPrior = pPrior; p->pOrderBy = 0; if( p->op==TK_UNION ) p->nSelectRow += pPrior->nSelectRow; sqlite3ExprDelete(db, p->pLimit); p->pLimit = pLimit; p->pOffset = pOffset; p->iLimit = 0; p->iOffset = 0; /* Convert the data in the temporary table into whatever form |
︙ | ︙ | |||
1763 1764 1765 1766 1767 1768 1769 1770 1771 1772 1773 1774 1775 1776 | p->pOffset = 0; intersectdest.iParm = tab2; explainSetInteger(iSub2, pParse->iNextSelectId); rc = sqlite3Select(pParse, p, &intersectdest); testcase( rc!=SQLITE_OK ); pDelete = p->pPrior; p->pPrior = pPrior; sqlite3ExprDelete(db, p->pLimit); p->pLimit = pLimit; p->pOffset = pOffset; /* Generate code to take the intersection of the two temporary ** tables. */ | > | 1774 1775 1776 1777 1778 1779 1780 1781 1782 1783 1784 1785 1786 1787 1788 | p->pOffset = 0; intersectdest.iParm = tab2; explainSetInteger(iSub2, pParse->iNextSelectId); rc = sqlite3Select(pParse, p, &intersectdest); testcase( rc!=SQLITE_OK ); pDelete = p->pPrior; p->pPrior = pPrior; if( p->nSelectRow>pPrior->nSelectRow ) p->nSelectRow = pPrior->nSelectRow; sqlite3ExprDelete(db, p->pLimit); p->pLimit = pLimit; p->pOffset = pOffset; /* Generate code to take the intersection of the two temporary ** tables. */ |
︙ | ︙ | |||
2349 2350 2351 2352 2353 2354 2355 2356 2357 2358 2359 2360 2361 2362 2363 2364 2365 2366 2367 2368 2369 | if( op==TK_EXCEPT || op==TK_INTERSECT ){ addrEofA = sqlite3VdbeAddOp2(v, OP_Goto, 0, labelEnd); }else{ addrEofA = sqlite3VdbeAddOp2(v, OP_If, regEofB, labelEnd); sqlite3VdbeAddOp2(v, OP_Gosub, regOutB, addrOutB); sqlite3VdbeAddOp1(v, OP_Yield, regAddrB); sqlite3VdbeAddOp2(v, OP_Goto, 0, addrEofA); } /* Generate a subroutine to run when the results from select B ** are exhausted and only data in select A remains. */ if( op==TK_INTERSECT ){ addrEofB = addrEofA; }else{ VdbeNoopComment((v, "eof-B subroutine")); addrEofB = sqlite3VdbeAddOp2(v, OP_If, regEofA, labelEnd); sqlite3VdbeAddOp2(v, OP_Gosub, regOutA, addrOutA); sqlite3VdbeAddOp1(v, OP_Yield, regAddrA); sqlite3VdbeAddOp2(v, OP_Goto, 0, addrEofB); } | > > | 2361 2362 2363 2364 2365 2366 2367 2368 2369 2370 2371 2372 2373 2374 2375 2376 2377 2378 2379 2380 2381 2382 2383 | if( op==TK_EXCEPT || op==TK_INTERSECT ){ addrEofA = sqlite3VdbeAddOp2(v, OP_Goto, 0, labelEnd); }else{ addrEofA = sqlite3VdbeAddOp2(v, OP_If, regEofB, labelEnd); sqlite3VdbeAddOp2(v, OP_Gosub, regOutB, addrOutB); sqlite3VdbeAddOp1(v, OP_Yield, regAddrB); sqlite3VdbeAddOp2(v, OP_Goto, 0, addrEofA); p->nSelectRow += pPrior->nSelectRow; } /* Generate a subroutine to run when the results from select B ** are exhausted and only data in select A remains. */ if( op==TK_INTERSECT ){ addrEofB = addrEofA; if( p->nSelectRow > pPrior->nSelectRow ) p->nSelectRow = pPrior->nSelectRow; }else{ VdbeNoopComment((v, "eof-B subroutine")); addrEofB = sqlite3VdbeAddOp2(v, OP_If, regEofA, labelEnd); sqlite3VdbeAddOp2(v, OP_Gosub, regOutA, addrOutA); sqlite3VdbeAddOp1(v, OP_Yield, regAddrA); sqlite3VdbeAddOp2(v, OP_Goto, 0, addrEofB); } |
︙ | ︙ | |||
3750 3751 3752 3753 3754 3755 3756 3757 3758 3759 3760 3761 3762 3763 | i = -1; }else{ sqlite3SelectDestInit(&dest, SRT_EphemTab, pItem->iCursor); assert( pItem->isPopulated==0 ); explainSetInteger(pItem->iSelectId, pParse->iNextSelectId); sqlite3Select(pParse, pSub, &dest); pItem->isPopulated = 1; } if( /*pParse->nErr ||*/ db->mallocFailed ){ goto select_end; } pParse->nHeight -= sqlite3SelectExprHeight(p); pTabList = p->pSrc; if( !IgnorableOrderby(pDest) ){ | > | 3764 3765 3766 3767 3768 3769 3770 3771 3772 3773 3774 3775 3776 3777 3778 | i = -1; }else{ sqlite3SelectDestInit(&dest, SRT_EphemTab, pItem->iCursor); assert( pItem->isPopulated==0 ); explainSetInteger(pItem->iSelectId, pParse->iNextSelectId); sqlite3Select(pParse, pSub, &dest); pItem->isPopulated = 1; pItem->pTab->nRowEst = (unsigned)pSub->nSelectRow; } if( /*pParse->nErr ||*/ db->mallocFailed ){ goto select_end; } pParse->nHeight -= sqlite3SelectExprHeight(p); pTabList = p->pSrc; if( !IgnorableOrderby(pDest) ){ |
︙ | ︙ | |||
3842 3843 3844 3845 3846 3847 3848 3849 3850 3851 3852 3853 3854 3855 | if( pDest->eDest==SRT_EphemTab ){ sqlite3VdbeAddOp2(v, OP_OpenEphemeral, pDest->iParm, pEList->nExpr); } /* Set the limiter. */ iEnd = sqlite3VdbeMakeLabel(v); computeLimitRegisters(pParse, p, iEnd); /* Open a virtual index to use for the distinct set. */ if( p->selFlags & SF_Distinct ){ KeyInfo *pKeyInfo; assert( isAgg || pGroupBy ); | > | 3857 3858 3859 3860 3861 3862 3863 3864 3865 3866 3867 3868 3869 3870 3871 | if( pDest->eDest==SRT_EphemTab ){ sqlite3VdbeAddOp2(v, OP_OpenEphemeral, pDest->iParm, pEList->nExpr); } /* Set the limiter. */ iEnd = sqlite3VdbeMakeLabel(v); p->nSelectRow = (double)LARGEST_INT64; computeLimitRegisters(pParse, p, iEnd); /* Open a virtual index to use for the distinct set. */ if( p->selFlags & SF_Distinct ){ KeyInfo *pKeyInfo; assert( isAgg || pGroupBy ); |
︙ | ︙ | |||
3865 3866 3867 3868 3869 3870 3871 3872 3873 3874 3875 3876 3877 3878 | /* Aggregate and non-aggregate queries are handled differently */ if( !isAgg && pGroupBy==0 ){ /* This case is for non-aggregate queries ** Begin the database scan */ pWInfo = sqlite3WhereBegin(pParse, pTabList, pWhere, &pOrderBy, 0); if( pWInfo==0 ) goto select_end; /* If sorting index that was created by a prior OP_OpenEphemeral ** instruction ended up not being needed, then change the OP_OpenEphemeral ** into an OP_Noop. */ if( addrSortIndex>=0 && pOrderBy==0 ){ sqlite3VdbeChangeToNoop(v, addrSortIndex, 1); | > | 3881 3882 3883 3884 3885 3886 3887 3888 3889 3890 3891 3892 3893 3894 3895 | /* Aggregate and non-aggregate queries are handled differently */ if( !isAgg && pGroupBy==0 ){ /* This case is for non-aggregate queries ** Begin the database scan */ pWInfo = sqlite3WhereBegin(pParse, pTabList, pWhere, &pOrderBy, 0); if( pWInfo==0 ) goto select_end; if( pWInfo->nRowOut < p->nSelectRow ) p->nSelectRow = pWInfo->nRowOut; /* If sorting index that was created by a prior OP_OpenEphemeral ** instruction ended up not being needed, then change the OP_OpenEphemeral ** into an OP_Noop. */ if( addrSortIndex>=0 && pOrderBy==0 ){ sqlite3VdbeChangeToNoop(v, addrSortIndex, 1); |
︙ | ︙ | |||
3909 3910 3911 3912 3913 3914 3915 3916 3917 3918 3919 3920 3921 3922 | for(k=p->pEList->nExpr, pItem=p->pEList->a; k>0; k--, pItem++){ pItem->iAlias = 0; } for(k=pGroupBy->nExpr, pItem=pGroupBy->a; k>0; k--, pItem++){ pItem->iAlias = 0; } } /* Create a label to jump to when we want to abort the query */ addrEnd = sqlite3VdbeMakeLabel(v); /* Convert TK_COLUMN nodes into TK_AGG_COLUMN and make entries in | > > > | 3926 3927 3928 3929 3930 3931 3932 3933 3934 3935 3936 3937 3938 3939 3940 3941 3942 | for(k=p->pEList->nExpr, pItem=p->pEList->a; k>0; k--, pItem++){ pItem->iAlias = 0; } for(k=pGroupBy->nExpr, pItem=pGroupBy->a; k>0; k--, pItem++){ pItem->iAlias = 0; } if( p->nSelectRow>(double)100 ) p->nSelectRow = (double)100; }else{ p->nSelectRow = (double)1; } /* Create a label to jump to when we want to abort the query */ addrEnd = sqlite3VdbeMakeLabel(v); /* Convert TK_COLUMN nodes into TK_AGG_COLUMN and make entries in |
︙ | ︙ |
Changes to src/sqliteInt.h.
︙ | ︙ | |||
1943 1944 1945 1946 1947 1948 1949 1950 1951 1952 1953 1954 1955 1956 | SrcList *pTabList; /* List of tables in the join */ int iTop; /* The very beginning of the WHERE loop */ int iContinue; /* Jump here to continue with next record */ int iBreak; /* Jump here to break out of the loop */ int nLevel; /* Number of nested loop */ struct WhereClause *pWC; /* Decomposition of the WHERE clause */ double savedNQueryLoop; /* pParse->nQueryLoop outside the WHERE loop */ WhereLevel a[1]; /* Information about each nest loop in WHERE */ }; /* ** A NameContext defines a context in which to resolve table and column ** names. The context consists of a list of tables (the pSrcList) field and ** a list of named expression (pEList). The named expression list may | > | 1943 1944 1945 1946 1947 1948 1949 1950 1951 1952 1953 1954 1955 1956 1957 | SrcList *pTabList; /* List of tables in the join */ int iTop; /* The very beginning of the WHERE loop */ int iContinue; /* Jump here to continue with next record */ int iBreak; /* Jump here to break out of the loop */ int nLevel; /* Number of nested loop */ struct WhereClause *pWC; /* Decomposition of the WHERE clause */ double savedNQueryLoop; /* pParse->nQueryLoop outside the WHERE loop */ double nRowOut; /* Estimated number of output rows */ WhereLevel a[1]; /* Information about each nest loop in WHERE */ }; /* ** A NameContext defines a context in which to resolve table and column ** names. The context consists of a list of tables (the pSrcList) field and ** a list of named expression (pEList). The named expression list may |
︙ | ︙ | |||
2018 2019 2020 2021 2022 2023 2024 2025 2026 2027 2028 2029 2030 2031 | Select *pPrior; /* Prior select in a compound select statement */ Select *pNext; /* Next select to the left in a compound */ Select *pRightmost; /* Right-most select in a compound select statement */ Expr *pLimit; /* LIMIT expression. NULL means not used. */ Expr *pOffset; /* OFFSET expression. NULL means not used. */ int iLimit, iOffset; /* Memory registers holding LIMIT & OFFSET counters */ int addrOpenEphm[3]; /* OP_OpenEphem opcodes related to this select */ }; /* ** Allowed values for Select.selFlags. The "SF" prefix stands for ** "Select Flag". */ #define SF_Distinct 0x0001 /* Output should be DISTINCT */ | > | 2019 2020 2021 2022 2023 2024 2025 2026 2027 2028 2029 2030 2031 2032 2033 | Select *pPrior; /* Prior select in a compound select statement */ Select *pNext; /* Next select to the left in a compound */ Select *pRightmost; /* Right-most select in a compound select statement */ Expr *pLimit; /* LIMIT expression. NULL means not used. */ Expr *pOffset; /* OFFSET expression. NULL means not used. */ int iLimit, iOffset; /* Memory registers holding LIMIT & OFFSET counters */ int addrOpenEphm[3]; /* OP_OpenEphem opcodes related to this select */ double nSelectRow; /* Estimated number of result rows */ }; /* ** Allowed values for Select.selFlags. The "SF" prefix stands for ** "Select Flag". */ #define SF_Distinct 0x0001 /* Output should be DISTINCT */ |
︙ | ︙ |
Changes to src/where.c.
︙ | ︙ | |||
4419 4420 4421 4422 4423 4424 4425 4426 4427 4428 4429 4430 4431 4432 4433 4434 4435 4436 4437 4438 4439 | } /* Open all tables in the pTabList and any indices selected for ** searching those tables. */ sqlite3CodeVerifySchema(pParse, -1); /* Insert the cookie verifier Goto */ notReady = ~(Bitmask)0; for(i=0, pLevel=pWInfo->a; i<nTabList; i++, pLevel++){ Table *pTab; /* Table to open */ int iDb; /* Index of database containing table/index */ pTabItem = &pTabList->a[pLevel->iFrom]; pTab = pTabItem->pTab; pLevel->iTabCur = pTabItem->iCursor; iDb = sqlite3SchemaToIndex(db, pTab->pSchema); if( (pTab->tabFlags & TF_Ephemeral)!=0 || pTab->pSelect ){ /* Do nothing */ }else #ifndef SQLITE_OMIT_VIRTUALTABLE if( (pLevel->plan.wsFlags & WHERE_VIRTUALTABLE)!=0 ){ const char *pVTab = (const char *)sqlite3GetVTable(db, pTab); | > > | 4419 4420 4421 4422 4423 4424 4425 4426 4427 4428 4429 4430 4431 4432 4433 4434 4435 4436 4437 4438 4439 4440 4441 | } /* Open all tables in the pTabList and any indices selected for ** searching those tables. */ sqlite3CodeVerifySchema(pParse, -1); /* Insert the cookie verifier Goto */ notReady = ~(Bitmask)0; pWInfo->nRowOut = (double)1; for(i=0, pLevel=pWInfo->a; i<nTabList; i++, pLevel++){ Table *pTab; /* Table to open */ int iDb; /* Index of database containing table/index */ pTabItem = &pTabList->a[pLevel->iFrom]; pTab = pTabItem->pTab; pLevel->iTabCur = pTabItem->iCursor; pWInfo->nRowOut *= pLevel->plan.nRow; iDb = sqlite3SchemaToIndex(db, pTab->pSchema); if( (pTab->tabFlags & TF_Ephemeral)!=0 || pTab->pSelect ){ /* Do nothing */ }else #ifndef SQLITE_OMIT_VIRTUALTABLE if( (pLevel->plan.wsFlags & WHERE_VIRTUALTABLE)!=0 ){ const char *pVTab = (const char *)sqlite3GetVTable(db, pTab); |
︙ | ︙ |
Changes to test/autoindex1.test.
︙ | ︙ | |||
241 242 243 244 245 246 247 | ORDER BY x.registering_flock; } { 1 0 0 {SCAN TABLE sheep AS s (~1000000 rows)} 1 1 1 {SEARCH TABLE flock_owner AS prev USING INDEX sqlite_autoindex_flock_owner_1 (flock_no=? AND owner_change_date<?) (~2 rows)} 1 0 0 {EXECUTE CORRELATED SCALAR SUBQUERY 2} 2 0 0 {SEARCH TABLE flock_owner AS later USING COVERING INDEX sqlite_autoindex_flock_owner_1 (flock_no=? AND owner_change_date>? AND owner_change_date<?) (~1 rows)} 0 0 0 {SCAN TABLE sheep AS x USING INDEX sheep_reg_flock_index (~1000000 rows)} | | | 241 242 243 244 245 246 247 248 249 250 251 | ORDER BY x.registering_flock; } { 1 0 0 {SCAN TABLE sheep AS s (~1000000 rows)} 1 1 1 {SEARCH TABLE flock_owner AS prev USING INDEX sqlite_autoindex_flock_owner_1 (flock_no=? AND owner_change_date<?) (~2 rows)} 1 0 0 {EXECUTE CORRELATED SCALAR SUBQUERY 2} 2 0 0 {SEARCH TABLE flock_owner AS later USING COVERING INDEX sqlite_autoindex_flock_owner_1 (flock_no=? AND owner_change_date>? AND owner_change_date<?) (~1 rows)} 0 0 0 {SCAN TABLE sheep AS x USING INDEX sheep_reg_flock_index (~1000000 rows)} 0 1 1 {SEARCH SUBQUERY 1 AS y USING AUTOMATIC COVERING INDEX (sheep_no=?) (~8 rows)} } finish_test |
Changes to test/eqp.test.
︙ | ︙ | |||
66 67 68 69 70 71 72 73 74 75 76 77 78 79 | do_eqp_test 1.6 { SELECT DISTINCT count(*) FROM t3 GROUP BY a; } { 0 0 0 {SCAN TABLE t3 (~1000000 rows)} 0 0 0 {USE TEMP B-TREE FOR GROUP BY} 0 0 0 {USE TEMP B-TREE FOR DISTINCT} } #------------------------------------------------------------------------- # Test cases eqp-2.* - tests for single select statements. # drop_all_tables do_execsql_test 2.1 { CREATE TABLE t1(x, y); | > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 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 115 116 117 118 | do_eqp_test 1.6 { SELECT DISTINCT count(*) FROM t3 GROUP BY a; } { 0 0 0 {SCAN TABLE t3 (~1000000 rows)} 0 0 0 {USE TEMP B-TREE FOR GROUP BY} 0 0 0 {USE TEMP B-TREE FOR DISTINCT} } do_eqp_test 1.7 { SELECT * FROM t3 JOIN (SELECT 1) } { 0 0 1 {SCAN SUBQUERY 1 (~1 rows)} 0 1 0 {SCAN TABLE t3 (~1000000 rows)} } do_eqp_test 1.8 { SELECT * FROM t3 JOIN (SELECT 1 UNION SELECT 2) } { 1 0 0 {COMPOUND SUBQUERIES 2 AND 3 USING TEMP B-TREE (UNION)} 0 0 1 {SCAN SUBQUERY 1 (~2 rows)} 0 1 0 {SCAN TABLE t3 (~1000000 rows)} } do_eqp_test 1.9 { SELECT * FROM t3 JOIN (SELECT 1 EXCEPT SELECT a FROM t3 LIMIT 17) } { 3 0 0 {SCAN TABLE t3 (~1000000 rows)} 1 0 0 {COMPOUND SUBQUERIES 2 AND 3 USING TEMP B-TREE (EXCEPT)} 0 0 1 {SCAN SUBQUERY 1 (~17 rows)} 0 1 0 {SCAN TABLE t3 (~1000000 rows)} } do_eqp_test 1.10 { SELECT * FROM t3 JOIN (SELECT 1 INTERSECT SELECT a FROM t3 LIMIT 17) } { 3 0 0 {SCAN TABLE t3 (~1000000 rows)} 1 0 0 {COMPOUND SUBQUERIES 2 AND 3 USING TEMP B-TREE (INTERSECT)} 0 0 1 {SCAN SUBQUERY 1 (~1 rows)} 0 1 0 {SCAN TABLE t3 (~1000000 rows)} } do_eqp_test 1.11 { SELECT * FROM t3 JOIN (SELECT 1 UNION ALL SELECT a FROM t3 LIMIT 17) } { 3 0 0 {SCAN TABLE t3 (~1000000 rows)} 1 0 0 {COMPOUND SUBQUERIES 2 AND 3 (UNION ALL)} 0 0 1 {SCAN SUBQUERY 1 (~17 rows)} 0 1 0 {SCAN TABLE t3 (~1000000 rows)} } #------------------------------------------------------------------------- # Test cases eqp-2.* - tests for single select statements. # drop_all_tables do_execsql_test 2.1 { CREATE TABLE t1(x, y); |
︙ | ︙ | |||
163 164 165 166 167 168 169 | } det 3.2.1 { SELECT * FROM (SELECT * FROM t1 ORDER BY x LIMIT 10) ORDER BY y LIMIT 5 } { 1 0 0 {SCAN TABLE t1 (~1000000 rows)} 1 0 0 {USE TEMP B-TREE FOR ORDER BY} | | | | | 202 203 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 | } det 3.2.1 { SELECT * FROM (SELECT * FROM t1 ORDER BY x LIMIT 10) ORDER BY y LIMIT 5 } { 1 0 0 {SCAN TABLE t1 (~1000000 rows)} 1 0 0 {USE TEMP B-TREE FOR ORDER BY} 0 0 0 {SCAN SUBQUERY 1 (~10 rows)} 0 0 0 {USE TEMP B-TREE FOR ORDER BY} } det 3.2.2 { SELECT * FROM (SELECT * FROM t1 ORDER BY x LIMIT 10) AS x1, (SELECT * FROM t2 ORDER BY x LIMIT 10) AS x2 ORDER BY x2.y LIMIT 5 } { 1 0 0 {SCAN TABLE t1 (~1000000 rows)} 1 0 0 {USE TEMP B-TREE FOR ORDER BY} 2 0 0 {SCAN TABLE t2 USING INDEX t2i1 (~1000000 rows)} 0 0 0 {SCAN SUBQUERY 1 AS x1 (~10 rows)} 0 1 1 {SCAN SUBQUERY 2 AS x2 (~10 rows)} 0 0 0 {USE TEMP B-TREE FOR ORDER BY} } det 3.3.1 { SELECT * FROM t1 WHERE y IN (SELECT y FROM t2) } { 0 0 0 {SCAN TABLE t1 (~100000 rows)} |
︙ | ︙ | |||
412 413 414 415 416 417 418 | # count(*) FROM (SELECT max(b) AS x FROM t1 GROUP BY a) GROUP BY x; # 1|0|0|SCAN TABLE t1 USING COVERING INDEX i2 (~1000000 rows) 0|0|0|SCAN # SUBQUERY 1 (~1000000 rows) 0|0|0|USE TEMP B-TREE FOR GROUP BY det 5.10 { SELECT count(*) FROM (SELECT max(b) AS x FROM t1 GROUP BY a) GROUP BY x } { 1 0 0 {SCAN TABLE t1 USING COVERING INDEX i2 (~1000000 rows)} | | | 451 452 453 454 455 456 457 458 459 460 461 462 463 464 465 | # count(*) FROM (SELECT max(b) AS x FROM t1 GROUP BY a) GROUP BY x; # 1|0|0|SCAN TABLE t1 USING COVERING INDEX i2 (~1000000 rows) 0|0|0|SCAN # SUBQUERY 1 (~1000000 rows) 0|0|0|USE TEMP B-TREE FOR GROUP BY det 5.10 { SELECT count(*) FROM (SELECT max(b) AS x FROM t1 GROUP BY a) GROUP BY x } { 1 0 0 {SCAN TABLE t1 USING COVERING INDEX i2 (~1000000 rows)} 0 0 0 {SCAN SUBQUERY 1 (~100 rows)} 0 0 0 {USE TEMP B-TREE FOR GROUP BY} } # EVIDENCE-OF: R-18544-33103 sqlite> EXPLAIN QUERY PLAN SELECT * FROM # (SELECT * FROM t2 WHERE c=1), t1; 0|0|0|SEARCH TABLE t2 USING INDEX i4 # (c=?) (~10 rows) 0|1|1|SCAN TABLE t1 (~1000000 rows) det 5.11 "SELECT * FROM (SELECT * FROM t2 WHERE c=1), t1" { |
︙ | ︙ |