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 | SQL archive |
| Timelines: | family | ancestors | descendants | both | trunk |
| Files: | files | file ages | folders |
| SHA1: |
56bbc539246a6dc9f1ae1edb898db7a4 |
| User & Date: | drh 2010-11-16 02:49:16 |
Context
|
2010-11-16
| ||
| 23:10 | Adding the sqlite3_stmt_readonly() interface. (check-in: fd5b2f23 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: 7e3fc2c8 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: 56bbc539 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: d52b5939 user: drh tags: trunk) | |
Changes
Changes to src/select.c.
1429 1430 1431 1432 1433 1434 1435 1436 1437 1438 1439 1440 1441 1442 .... 1590 1591 1592 1593 1594 1595 1596 1597 1598 1599 1600 1601 1602 1603 .... 1612 1613 1614 1615 1616 1617 1618 1619 1620 1621 1622 1623 1624 1625 .... 1684 1685 1686 1687 1688 1689 1690 1691 1692 1693 1694 1695 1696 1697 .... 1763 1764 1765 1766 1767 1768 1769 1770 1771 1772 1773 1774 1775 1776 .... 2349 2350 2351 2352 2353 2354 2355 2356 2357 2358 2359 2360 2361 2362 2363 2364 2365 2366 2367 2368 2369 .... 3750 3751 3752 3753 3754 3755 3756 3757 3758 3759 3760 3761 3762 3763 .... 3842 3843 3844 3845 3846 3847 3848 3849 3850 3851 3852 3853 3854 3855 .... 3865 3866 3867 3868 3869 3870 3871 3872 3873 3874 3875 3876 3877 3878 .... 3909 3910 3911 3912 3913 3914 3915 3916 3917 3918 3919 3920 3921 3922 |
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);
}
................................................................................
}
/* 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;
................................................................................
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: {
................................................................................
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
................................................................................
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.
*/
................................................................................
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);
}
................................................................................
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) ){
................................................................................
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 );
................................................................................
/* 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);
................................................................................
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
|
> > > > > > > > > > > > > > > > > > > > |
1429 1430 1431 1432 1433 1434 1435 1436 1437 1438 1439 1440 1441 1442 1443 1444 .... 1592 1593 1594 1595 1596 1597 1598 1599 1600 1601 1602 1603 1604 1605 1606 .... 1615 1616 1617 1618 1619 1620 1621 1622 1623 1624 1625 1626 1627 1628 1629 1630 1631 1632 1633 1634 1635 .... 1694 1695 1696 1697 1698 1699 1700 1701 1702 1703 1704 1705 1706 1707 1708 .... 1774 1775 1776 1777 1778 1779 1780 1781 1782 1783 1784 1785 1786 1787 1788 .... 2361 2362 2363 2364 2365 2366 2367 2368 2369 2370 2371 2372 2373 2374 2375 2376 2377 2378 2379 2380 2381 2382 2383 .... 3764 3765 3766 3767 3768 3769 3770 3771 3772 3773 3774 3775 3776 3777 3778 .... 3857 3858 3859 3860 3861 3862 3863 3864 3865 3866 3867 3868 3869 3870 3871 .... 3881 3882 3883 3884 3885 3886 3887 3888 3889 3890 3891 3892 3893 3894 3895 .... 3926 3927 3928 3929 3930 3931 3932 3933 3934 3935 3936 3937 3938 3939 3940 3941 3942 |
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);
}
................................................................................
}
/* 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;
................................................................................
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: {
................................................................................
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
................................................................................
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.
*/
................................................................................
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);
}
................................................................................
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) ){
................................................................................
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 );
................................................................................
/* 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);
................................................................................
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
....
2018
2019
2020
2021
2022
2023
2024
2025
2026
2027
2028
2029
2030
2031
|
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
................................................................................
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 */
|
>
>
|
1943
1944
1945
1946
1947
1948
1949
1950
1951
1952
1953
1954
1955
1956
1957
....
2019
2020
2021
2022
2023
2024
2025
2026
2027
2028
2029
2030
2031
2032
2033
|
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 ................................................................................ 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 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=?) (~7 rows)}
}
finish_test
|
| |
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 ... 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 ... 412 413 414 415 416 417 418 419 420 421 422 423 424 425 426 |
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);
................................................................................
}
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 (~1000000 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 (~1000000 rows)}
0 1 1 {SCAN SUBQUERY 2 AS x2 (~1000000 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)}
................................................................................
# 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 (~1000000 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" {
|
> > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | | | | |
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 ... 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 ... 451 452 453 454 455 456 457 458 459 460 461 462 463 464 465 |
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);
................................................................................
}
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)}
................................................................................
# 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" {
|