SQLite: Check-in [b016b754]

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 \| SQL archive
Timelines:	family \| ancestors \| descendants \| both \| IN-with-ORDERBY
Files:	files \| file ages \| folders
SHA1:	b016b7546d6fbfba06019398b9ac239b0cbe9086
User & Date:	drh 2013-02-08 20:39:02

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: 71b6c260 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: b016b754 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: f78395c8 user: drh tags: IN-with-ORDERBY)

Changes

Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/where.c.

  2266   2266     WhereClause *pWC = p->pWC;      /* The WHERE clause */
  2267   2267     struct SrcList_item *pSrc = p->pSrc; /* The FROM clause term to search */
  2268   2268     Table *pTab = pSrc->pTab;
  2269   2269     sqlite3_index_info *pIdxInfo;
  2270   2270     struct sqlite3_index_constraint *pIdxCons;
  2271   2271     struct sqlite3_index_constraint_usage *pUsage;
  2272   2272     WhereTerm *pTerm;
  2273         -  int i, j;
         2273  +  int i, j, k;
  2274   2274     int nOrderBy;
         2275  +  int sortOrder;                  /* Sort order for IN clauses */
  2275   2276     int bAllowIN;                   /* Allow IN optimizations */
  2276   2277     double rCost;
  2277   2278   
  2278   2279     /* Make sure wsFlags is initialized to some sane value. Otherwise, if the 
  2279   2280     ** malloc in allocateIndexInfo() fails and this function returns leaving
  2280   2281     ** wsFlags in an uninitialized state, the caller may behave unpredictably.
  2281   2282     */
................................................................................
  2366   2367         pIdxInfo->nOrderBy = 0;
  2367   2368       }
  2368   2369     
  2369   2370       if( vtabBestIndex(pParse, pTab, pIdxInfo) ){
  2370   2371         return;
  2371   2372       }
  2372   2373     
         2374  +    sortOrder = SQLITE_SO_ASC;
  2373   2375       pIdxCons = *(struct sqlite3_index_constraint**)&pIdxInfo->aConstraint;
  2374   2376       for(i=0; i<pIdxInfo->nConstraint; i++, pIdxCons++){
  2375   2377         if( pUsage[i].argvIndex>0 ){
  2376   2378           j = pIdxCons->iTermOffset;
  2377   2379           pTerm = &pWC->a[j];
  2378   2380           p->cost.used |= pTerm->prereqRight;
  2379         -        if( (pTerm->eOperator & WO_IN)!=0 && pUsage[i].omit==0 ){
  2380         -          /* Do not attempt to use an IN constraint if the virtual table
  2381         -          ** says that the equivalent EQ constraint cannot be safely omitted.
  2382         -          ** If we do attempt to use such a constraint, some rows might be
  2383         -          ** repeated in the output. */
  2384         -          break;
         2381  +        if( (pTerm->eOperator & WO_IN)!=0 ){
         2382  +          if( pUsage[i].omit==0 ){
         2383  +            /* Do not attempt to use an IN constraint if the virtual table
         2384  +            ** says that the equivalent EQ constraint cannot be safely omitted.
         2385  +            ** If we do attempt to use such a constraint, some rows might be
         2386  +            ** repeated in the output. */
         2387  +            break;
         2388  +          }
         2389  +          for(k=0; k<pIdxInfo->nOrderBy; k++){
         2390  +            if( pIdxInfo->aOrderBy[k].iColumn==pIdxCons->iColumn ){
         2391  +              sortOrder = pIdxInfo->aOrderBy[k].desc;
         2392  +              break;
         2393  +            }
         2394  +          }
  2385   2395           }
  2386   2396         }
  2387   2397       }
  2388   2398       if( i>=pIdxInfo->nConstraint ) break;
  2389   2399     }
  2390   2400     
  2391   2401     /* If there is an ORDER BY clause, and the selected virtual table index
................................................................................
  2407   2417     if( (SQLITE_BIG_DBL/((double)2))<rCost ){
  2408   2418       p->cost.rCost = (SQLITE_BIG_DBL/((double)2));
  2409   2419     }else{
  2410   2420       p->cost.rCost = rCost;
  2411   2421     }
  2412   2422     p->cost.plan.u.pVtabIdx = pIdxInfo;
  2413   2423     if( pIdxInfo->orderByConsumed ){
  2414         -    p->cost.plan.wsFlags |= WHERE_ORDERED;
         2424  +    assert( sortOrder==0 || sortOrder==1 );
         2425  +    p->cost.plan.wsFlags |= WHERE_ORDERED + sortOrder*WHERE_REVERSE;
  2415   2426       p->cost.plan.nOBSat = nOrderBy;
  2416   2427     }else{
  2417   2428       p->cost.plan.nOBSat = p->i ? p->aLevel[p->i-1].plan.nOBSat : 0;
  2418   2429     }
  2419   2430     p->cost.plan.nEq = 0;
  2420   2431     pIdxInfo->nOrderBy = nOrderBy;
  2421   2432