/ Check-in [61b2a7be]
Login
SQLite training in Houston TX on 2019-11-05 (details)
Part of the 2019 Tcl Conference

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Do not suppress the ORDER BY clause on a virtual table query if an IN constraint is used. Fix for ticket [f69b96e3076e]. Testing done on TH3 using cov1/where37.test.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 61b2a7be3b9c04bf45bffa93a7d3a480fc5c947a
User & Date: drh 2013-04-18 02:55:54
Context
2013-04-18
03:10
Fix a compiler warning in sqlite3_compileoption_used(). check-in: e9f9d84b user: drh tags: trunk
02:55
Do not suppress the ORDER BY clause on a virtual table query if an IN constraint is used. Fix for ticket [f69b96e3076e]. Testing done on TH3 using cov1/where37.test. check-in: 61b2a7be user: drh tags: trunk
2013-04-17
19:42
Fix the --match command in mptester so that it checks the entire result, not just a prefix of the result. check-in: 3e3ecad2 user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Show Whitespace Changes Patch

Changes to src/where.c.

  2271   2271     WhereClause *pWC = p->pWC;      /* The WHERE clause */
  2272   2272     struct SrcList_item *pSrc = p->pSrc; /* The FROM clause term to search */
  2273   2273     Table *pTab = pSrc->pTab;
  2274   2274     sqlite3_index_info *pIdxInfo;
  2275   2275     struct sqlite3_index_constraint *pIdxCons;
  2276   2276     struct sqlite3_index_constraint_usage *pUsage;
  2277   2277     WhereTerm *pTerm;
  2278         -  int i, j, k;
         2278  +  int i, j;
  2279   2279     int nOrderBy;
  2280         -  int sortOrder;                  /* Sort order for IN clauses */
  2281   2280     int bAllowIN;                   /* Allow IN optimizations */
  2282   2281     double rCost;
  2283   2282   
  2284   2283     /* Make sure wsFlags is initialized to some sane value. Otherwise, if the 
  2285   2284     ** malloc in allocateIndexInfo() fails and this function returns leaving
  2286   2285     ** wsFlags in an uninitialized state, the caller may behave unpredictably.
  2287   2286     */
................................................................................
  2372   2371         pIdxInfo->nOrderBy = 0;
  2373   2372       }
  2374   2373     
  2375   2374       if( vtabBestIndex(pParse, pTab, pIdxInfo) ){
  2376   2375         return;
  2377   2376       }
  2378   2377     
  2379         -    sortOrder = SQLITE_SO_ASC;
  2380   2378       pIdxCons = *(struct sqlite3_index_constraint**)&pIdxInfo->aConstraint;
  2381   2379       for(i=0; i<pIdxInfo->nConstraint; i++, pIdxCons++){
  2382   2380         if( pUsage[i].argvIndex>0 ){
  2383   2381           j = pIdxCons->iTermOffset;
  2384   2382           pTerm = &pWC->a[j];
  2385   2383           p->cost.used |= pTerm->prereqRight;
  2386   2384           if( (pTerm->eOperator & WO_IN)!=0 ){
................................................................................
  2387   2385             if( pUsage[i].omit==0 ){
  2388   2386               /* Do not attempt to use an IN constraint if the virtual table
  2389   2387               ** says that the equivalent EQ constraint cannot be safely omitted.
  2390   2388               ** If we do attempt to use such a constraint, some rows might be
  2391   2389               ** repeated in the output. */
  2392   2390               break;
  2393   2391             }
  2394         -          for(k=0; k<pIdxInfo->nOrderBy; k++){
  2395         -            if( pIdxInfo->aOrderBy[k].iColumn==pIdxCons->iColumn ){
  2396         -              sortOrder = pIdxInfo->aOrderBy[k].desc;
  2397         -              break;
  2398         -            }
  2399         -          }
         2392  +          /* A virtual table that is constrained by an IN clause may not
         2393  +          ** consume the ORDER BY clause because (1) the order of IN terms
         2394  +          ** is not necessarily related to the order of output terms and
         2395  +          ** (2) Multiple outputs from a single IN value will not merge
         2396  +          ** together.  */
         2397  +          pIdxInfo->orderByConsumed = 0;
  2400   2398           }
  2401   2399         }
  2402   2400       }
  2403   2401       if( i>=pIdxInfo->nConstraint ) break;
  2404   2402     }
  2405   2403     
  2406   2404     /* If there is an ORDER BY clause, and the selected virtual table index
................................................................................
  2422   2420     if( (SQLITE_BIG_DBL/((double)2))<rCost ){
  2423   2421       p->cost.rCost = (SQLITE_BIG_DBL/((double)2));
  2424   2422     }else{
  2425   2423       p->cost.rCost = rCost;
  2426   2424     }
  2427   2425     p->cost.plan.u.pVtabIdx = pIdxInfo;
  2428   2426     if( pIdxInfo->orderByConsumed ){
  2429         -    assert( sortOrder==0 || sortOrder==1 );
  2430         -    p->cost.plan.wsFlags |= WHERE_ORDERED + sortOrder*WHERE_REVERSE;
         2427  +    p->cost.plan.wsFlags |= WHERE_ORDERED;
  2431   2428       p->cost.plan.nOBSat = nOrderBy;
  2432   2429     }else{
  2433   2430       p->cost.plan.nOBSat = p->i ? p->aLevel[p->i-1].plan.nOBSat : 0;
  2434   2431     }
  2435   2432     p->cost.plan.nEq = 0;
  2436   2433     pIdxInfo->nOrderBy = nOrderBy;
  2437   2434