Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Improved processing of DISTINCT. |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | nextgen-query-plan-exp |
Files: | files | file ages | folders |
SHA1: |
ba897100fed291d2025f68d09334f998 |
User & Date: | drh 2013-06-11 18:59:38.240 |
Context
2013-06-12
| ||
03:48 | Continue refining the NGQP (check-in: 40567fddd4 user: drh tags: nextgen-query-plan-exp) | |
2013-06-11
| ||
18:59 | Improved processing of DISTINCT. (check-in: ba897100fe user: drh tags: nextgen-query-plan-exp) | |
13:30 | Fix the Parse.nQueryLoop state variable to work with NGQP. (check-in: f1cac24f06 user: drh tags: nextgen-query-plan-exp) | |
Changes
Changes to ext/misc/fuzzer.c.
︙ | |||
1075 1076 1077 1078 1079 1080 1081 | 1075 1076 1077 1078 1079 1080 1081 1082 1083 1084 1085 1086 1087 1088 1089 1090 1091 1092 1093 1094 1095 1096 1097 1098 1099 1100 1101 1102 1103 1104 1105 | - + - + | static int fuzzerBestIndex(sqlite3_vtab *tab, sqlite3_index_info *pIdxInfo){ int iPlan = 0; int iDistTerm = -1; int iRulesetTerm = -1; int i; int seenMatch = 0; const struct sqlite3_index_constraint *pConstraint; |
︙ | |||
1128 1129 1130 1131 1132 1133 1134 | 1128 1129 1130 1131 1132 1133 1134 1135 1136 1137 1138 1139 1140 1141 1142 | - + | pIdxInfo->idxNum = iPlan; if( pIdxInfo->nOrderBy==1 && pIdxInfo->aOrderBy[0].iColumn==1 && pIdxInfo->aOrderBy[0].desc==0 ){ pIdxInfo->orderByConsumed = 1; } |
︙ |
Changes to src/sqliteInt.h.
︙ | |||
1963 1964 1965 1966 1967 1968 1969 1970 | 1963 1964 1965 1966 1967 1968 1969 1970 1971 1972 1973 1974 1975 1976 1977 1978 1979 1980 | + - + + | #define WHERE_ONEPASS_DESIRED 0x0004 /* Want to do one-pass UPDATE/DELETE */ #define WHERE_DUPLICATES_OK 0x0008 /* Ok to return a row more than once */ #define WHERE_OMIT_OPEN_CLOSE 0x0010 /* Table cursors are already open */ #define WHERE_FORCE_TABLE 0x0020 /* Do not use an index-only search */ #define WHERE_ONETABLE_ONLY 0x0040 /* Only code the 1st table in pTabList */ #define WHERE_AND_ONLY 0x0080 /* Don't use indices for OR terms */ #define WHERE_GROUPBY 0x0100 /* pOrderBy is really a GROUP BY */ #define WHERE_DISTINCTBY 0x0200 /* pOrderby is really a DISTINCT clause */ |
︙ | |||
2222 2223 2224 2225 2226 2227 2228 | 2224 2225 2226 2227 2228 2229 2230 2231 2232 2233 2234 2235 2236 2237 2238 | - + | TableLock *aTableLock; /* Required table locks for shared-cache mode */ #endif AutoincInfo *pAinc; /* Information about AUTOINCREMENT counters */ /* Information used while coding trigger programs. */ Parse *pToplevel; /* Parse structure for main program (or NULL) */ Table *pTriggerTab; /* Table triggers are being coded for */ |
︙ |
Changes to src/where.c.
︙ | |||
341 342 343 344 345 346 347 348 | 341 342 343 344 345 346 347 348 349 350 351 352 353 354 355 356 357 358 359 360 361 362 363 364 365 366 367 368 369 | + - + + + - - - | ** into the second half to give some continuity. */ struct WhereInfo { Parse *pParse; /* Parsing and code generating context */ SrcList *pTabList; /* List of tables in the join */ ExprList *pOrderBy; /* The ORDER BY clause or NULL */ ExprList *pDistinct; /* DISTINCT ON values, or NULL */ WhereLoop *pLoops; /* List of all WhereLoop objects */ Bitmask revMask; /* Mask of ORDER BY terms that need reversing */ |
︙ | |||
437 438 439 440 441 442 443 | 437 438 439 440 441 442 443 444 445 446 447 448 449 450 451 | - + | } /* ** Return TRUE if the WHERE clause returns rows in ORDER BY order. ** Return FALSE if the output needs to be sorted. */ int sqlite3WhereIsOrdered(WhereInfo *pWInfo){ |
︙ | |||
1772 1773 1774 1775 1776 1777 1778 | 1772 1773 1774 1775 1776 1777 1778 1779 1780 1781 1782 1783 1784 1785 1786 1787 1788 1789 1790 1791 1792 1793 1794 1795 | + + - - + + - - - - - + + + + | } return -1; } /* ** Return true if the DISTINCT expression-list passed as the third argument ** is redundant. ** |
︙ | |||
3414 3415 3416 3417 3418 3419 3420 | 3415 3416 3417 3418 3419 3420 3421 3422 3423 3424 3425 3426 3427 3428 3429 | - + | ** query, then the caller will only allow the loop to run for ** a single iteration. This means that the first row returned ** should not have a NULL value stored in 'x'. If column 'x' is ** the first one after the nEq equality constraints in the index, ** this requires some special handling. */ if( (pWInfo->wctrlFlags&WHERE_ORDERBY_MIN)!=0 |
︙ | |||
4489 4490 4491 4492 4493 4494 4495 | 4490 4491 4492 4493 4494 4495 4496 4497 4498 4499 4500 4501 4502 4503 4504 4505 4506 | - + + + | && pProbe->bUnordered==0 && (pWInfo->wctrlFlags & WHERE_ONEPASS_DESIRED)==0 && sqlite3GlobalConfig.bUseCis && OptimizationEnabled(pWInfo->pParse->db, SQLITE_CoverIdxScan) ){ pNew->iSortIdx = b ? iSortIdx : 0; pNew->nOut = rSize; |
︙ | |||
4800 4801 4802 4803 4804 4805 4806 4807 | 4803 4804 4805 4806 4807 4808 4809 4810 4811 4812 4813 4814 4815 4816 4817 4818 4819 4820 4821 4822 4823 4824 4825 4826 4827 4828 4829 4830 4831 4832 4833 4834 4835 4836 4837 | + + - - + + - + - | ** 0: ORDER BY is not satisfied. Sorting required ** 1: ORDER BY is satisfied. Omit sorting ** -1: Unknown at this time ** */ static int wherePathSatisfiesOrderBy( WhereInfo *pWInfo, /* The WHERE clause */ ExprList *pOrderBy, /* ORDER BY or GROUP BY or DISTINCT clause to check */ WherePath *pPath, /* The WherePath to check */ u16 wctrlFlags, /* Might contain WHERE_GROUPBY or WHERE_DISTINCTBY */ |
︙ | |||
4956 4957 4958 4959 4960 4961 4962 | 4960 4961 4962 4963 4964 4965 4966 4967 4968 4969 4970 4971 4972 4973 4974 | - + | ** of the index and and mark that ORDER BY term off */ bOnce = 1; isMatch = 0; for(i=0; bOnce && i<nOrderBy; i++){ if( MASKBIT(i) & obSat ) continue; pOBExpr = sqlite3ExprSkipCollate(pOrderBy->a[i].pExpr); |
︙ | |||
5107 5108 5109 5110 5111 5112 5113 | 5111 5112 5113 5114 5115 5116 5117 5118 5119 5120 5121 5122 5123 5124 5125 5126 5127 | - - + + + | if( (pWLoop->maskSelf & pFrom->maskLoop)!=0 ) continue; /* At this point, pWLoop is a candidate to be the next loop. ** Compute its cost */ rCost = whereCostAdd(pWLoop->rSetup,pWLoop->rRun + pFrom->nRow); rCost = whereCostAdd(rCost, pFrom->rCost); maskNew = pFrom->maskLoop | pWLoop->maskSelf; if( !isOrderedValid ){ |
︙ | |||
5245 5246 5247 5248 5249 5250 5251 5252 | 5250 5251 5252 5253 5254 5255 5256 5257 5258 5259 5260 5261 5262 5263 5264 5265 5266 5267 5268 5269 5270 5271 5272 5273 5274 5275 5276 5277 5278 5279 | + + + + + + + + + + + + - - + + + | /* Load the lowest cost path into pWInfo */ for(iLoop=0; iLoop<nLoop; iLoop++){ WhereLevel *pLevel = pWInfo->a + iLoop; pLevel->pWLoop = pWLoop = pFrom->aLoop[iLoop]; pLevel->iFrom = pWLoop->iTab; /* FIXME: Omit the iFrom field */ pLevel->iTabCur = pWInfo->pTabList->a[pLevel->iFrom].iCursor; } if( (pWInfo->wctrlFlags & WHERE_DISTINCTBY)==0 && pWInfo->pDistinct && nRowEst ){ Bitmask notUsed; int rc = wherePathSatisfiesOrderBy(pWInfo, pWInfo->pDistinct, pFrom, WHERE_DISTINCTBY, nLoop-1, 1, pFrom->aLoop[nLoop-1], ¬Used); if( rc==1 ) pWInfo->eDistinct = WHERE_DISTINCT_ORDERED; } if( pFrom->isOrdered ){ if( pWInfo->wctrlFlags & WHERE_DISTINCTBY ){ pWInfo->eDistinct = WHERE_DISTINCT_ORDERED; }else{ |
︙ | |||
5323 5324 5325 5326 5327 5328 5329 | 5341 5342 5343 5344 5345 5346 5347 5348 5349 5350 5351 5352 5353 5354 5355 5356 | - + + | } if( pLoop->wsFlags ){ pLoop->nOut = (WhereCost)1; pWInfo->a[0].pWLoop = pLoop; pLoop->maskSelf = getMask(&pWInfo->sMaskSet, iCur); pWInfo->a[0].iTabCur = iCur; pWInfo->nRowOut = 1; |
︙ | |||
5410 5411 5412 5413 5414 5415 5416 | 5429 5430 5431 5432 5433 5434 5435 5436 5437 5438 5439 5440 5441 5442 | - - - - - - - - - | ** end ** ** ORDER BY CLAUSE PROCESSING ** ** pOrderBy is a pointer to the ORDER BY clause of a SELECT statement, ** if there is one. If there is no ORDER BY clause or if this routine ** is called from an UPDATE or DELETE statement, then pOrderBy is NULL. |
︙ | |||
5553 5554 5555 5556 5557 5558 5559 5560 5561 5562 5563 5564 | 5563 5564 5565 5566 5567 5568 5569 5570 5571 5572 5573 5574 5575 5576 5577 5578 5579 5580 5581 5582 5583 5584 5585 5586 5587 5588 5589 5590 5591 5592 5593 5594 5595 5596 5597 5598 5599 5600 5601 5602 5603 | + + + + + + + + + + + + + + + - - - + + + + + + + | ** want to analyze these virtual terms, so start analyzing at the end ** and work forward so that the added virtual terms are never processed. */ exprAnalyzeAll(pTabList, &pWInfo->sWC); if( db->mallocFailed ){ goto whereBeginError; } /* If the ORDER BY (or GROUP BY) clause contains references to general ** expressions, then we won't be able to satisfy it using indices, so ** go ahead and disable it now. */ if( pOrderBy ){ for(ii=0; ii<pOrderBy->nExpr; ii++){ Expr *pExpr = sqlite3ExprSkipCollate(pOrderBy->a[ii].pExpr); if( pExpr->op!=TK_COLUMN ){ pWInfo->pOrderBy = pOrderBy = 0; break; } } } /* Check if the DISTINCT qualifier, if there is one, is redundant. ** If it is, then set pDistinct to NULL and WhereInfo.eDistinct to ** WHERE_DISTINCT_UNIQUE to tell the caller to ignore the DISTINCT. */ if( pDistinct ){ |
︙ | |||
5583 5584 5585 5586 5587 5588 5589 | 5612 5613 5614 5615 5616 5617 5618 5619 5620 5621 5622 5623 5624 5625 5626 5627 5628 5629 5630 5631 5632 5633 5634 5635 5636 5637 5638 5639 5640 5641 5642 5643 5644 5645 5646 5647 5648 5649 5650 5651 5652 5653 5654 5655 5656 5657 5658 5659 5660 | - + - - - - - - + + + + + + + + + + + + + + + + + + + | for(p=pWInfo->pLoops; p; p=p->pNextLoop){ p->cId = zLabel[(i++)%sizeof(zLabel)]; whereLoopPrint(p, pTabList); } } #endif |
︙ |