Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Make sure the virtual tables that take advantage of IN operators sort the RHS of the IN operator in the correct order according to the ORDER BY clause. |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | IN-with-ORDERBY |
Files: | files | file ages | folders |
SHA1: |
b016b7546d6fbfba06019398b9ac239b |
User & Date: | drh 2013-02-08 20:39:02.391 |
Context
2013-02-08
| ||
23:18 | Fix a potential NULL-pointer dereference following an OOM error in the query planner logic for virtual tables with OR-connected terms. (Closed-Leaf check-in: 71b6c26053 user: drh tags: IN-with-ORDERBY) | |
20:39 | Make sure the virtual tables that take advantage of IN operators sort the RHS of the IN operator in the correct order according to the ORDER BY clause. (check-in: b016b7546d user: drh tags: IN-with-ORDERBY) | |
18:48 | Loop through the elements on the RHS of an IN operator in reverse order when the ORDER BY clauses specifies DESC. (check-in: f78395c889 user: drh tags: IN-with-ORDERBY) | |
Changes
Changes to src/where.c.
︙ | ︙ | |||
2266 2267 2268 2269 2270 2271 2272 | WhereClause *pWC = p->pWC; /* The WHERE clause */ struct SrcList_item *pSrc = p->pSrc; /* The FROM clause term to search */ Table *pTab = pSrc->pTab; sqlite3_index_info *pIdxInfo; struct sqlite3_index_constraint *pIdxCons; struct sqlite3_index_constraint_usage *pUsage; WhereTerm *pTerm; | | > | 2266 2267 2268 2269 2270 2271 2272 2273 2274 2275 2276 2277 2278 2279 2280 2281 2282 | WhereClause *pWC = p->pWC; /* The WHERE clause */ struct SrcList_item *pSrc = p->pSrc; /* The FROM clause term to search */ Table *pTab = pSrc->pTab; sqlite3_index_info *pIdxInfo; struct sqlite3_index_constraint *pIdxCons; struct sqlite3_index_constraint_usage *pUsage; WhereTerm *pTerm; int i, j, k; int nOrderBy; int sortOrder; /* Sort order for IN clauses */ int bAllowIN; /* Allow IN optimizations */ double rCost; /* Make sure wsFlags is initialized to some sane value. Otherwise, if the ** malloc in allocateIndexInfo() fails and this function returns leaving ** wsFlags in an uninitialized state, the caller may behave unpredictably. */ |
︙ | ︙ | |||
2366 2367 2368 2369 2370 2371 2372 2373 2374 2375 2376 2377 2378 | pIdxInfo->nOrderBy = 0; } if( vtabBestIndex(pParse, pTab, pIdxInfo) ){ return; } pIdxCons = *(struct sqlite3_index_constraint**)&pIdxInfo->aConstraint; for(i=0; i<pIdxInfo->nConstraint; i++, pIdxCons++){ if( pUsage[i].argvIndex>0 ){ j = pIdxCons->iTermOffset; pTerm = &pWC->a[j]; p->cost.used |= pTerm->prereqRight; | > | > | | | | | > > > > > > > | 2367 2368 2369 2370 2371 2372 2373 2374 2375 2376 2377 2378 2379 2380 2381 2382 2383 2384 2385 2386 2387 2388 2389 2390 2391 2392 2393 2394 2395 2396 2397 2398 2399 2400 2401 | pIdxInfo->nOrderBy = 0; } if( vtabBestIndex(pParse, pTab, pIdxInfo) ){ return; } sortOrder = SQLITE_SO_ASC; pIdxCons = *(struct sqlite3_index_constraint**)&pIdxInfo->aConstraint; for(i=0; i<pIdxInfo->nConstraint; i++, pIdxCons++){ if( pUsage[i].argvIndex>0 ){ j = pIdxCons->iTermOffset; pTerm = &pWC->a[j]; p->cost.used |= pTerm->prereqRight; if( (pTerm->eOperator & WO_IN)!=0 ){ if( pUsage[i].omit==0 ){ /* Do not attempt to use an IN constraint if the virtual table ** says that the equivalent EQ constraint cannot be safely omitted. ** If we do attempt to use such a constraint, some rows might be ** repeated in the output. */ break; } for(k=0; k<pIdxInfo->nOrderBy; k++){ if( pIdxInfo->aOrderBy[k].iColumn==pIdxCons->iColumn ){ sortOrder = pIdxInfo->aOrderBy[k].desc; break; } } } } } if( i>=pIdxInfo->nConstraint ) break; } /* If there is an ORDER BY clause, and the selected virtual table index |
︙ | ︙ | |||
2407 2408 2409 2410 2411 2412 2413 | if( (SQLITE_BIG_DBL/((double)2))<rCost ){ p->cost.rCost = (SQLITE_BIG_DBL/((double)2)); }else{ p->cost.rCost = rCost; } p->cost.plan.u.pVtabIdx = pIdxInfo; if( pIdxInfo->orderByConsumed ){ | > | | 2417 2418 2419 2420 2421 2422 2423 2424 2425 2426 2427 2428 2429 2430 2431 2432 | if( (SQLITE_BIG_DBL/((double)2))<rCost ){ p->cost.rCost = (SQLITE_BIG_DBL/((double)2)); }else{ p->cost.rCost = rCost; } p->cost.plan.u.pVtabIdx = pIdxInfo; if( pIdxInfo->orderByConsumed ){ assert( sortOrder==0 || sortOrder==1 ); p->cost.plan.wsFlags |= WHERE_ORDERED + sortOrder*WHERE_REVERSE; p->cost.plan.nOBSat = nOrderBy; }else{ p->cost.plan.nOBSat = p->i ? p->aLevel[p->i-1].plan.nOBSat : 0; } p->cost.plan.nEq = 0; pIdxInfo->nOrderBy = nOrderBy; |
︙ | ︙ |