/ Check-in [72727b68]
Login

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

Overview
Comment:Make sure that the sorting-index pre-filter recognizes that a rowid reference might be sortable. This fixes a performance regression.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | Cplusplus-comment
Files: files | file ages | folders
SHA1: 72727b68cd07969165f1f0943cc7e1a265436653
User & Date: drh 2014-09-19 02:01:37
Context
2014-09-19
04:42
Add the sqlite3VdbeMemClearAndResize() function. Fix a sorting-index prefilter problem. check-in: 987a7a21 user: drh tags: trunk
02:01
Make sure that the sorting-index pre-filter recognizes that a rowid reference might be sortable. This fixes a performance regression. Closed-Leaf check-in: 72727b68 user: drh tags: Cplusplus-comment
00:43
Add the sqlite3VdbeMemClearAndResize() interface to be used in place of sqlite3VdbeMemGrow(). A C++ style comment was left in this check-in by mistake, and so it has been moved into a branch to avoid problems in any future bisects on windows. check-in: 5b9b8987 user: drh tags: Cplusplus-comment
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/where.c.

  4556   4556   
  4557   4557     if( pIndex->bUnordered ) return 0;
  4558   4558     if( (pOB = pBuilder->pWInfo->pOrderBy)==0 ) return 0;
  4559   4559     for(ii=0; ii<pOB->nExpr; ii++){
  4560   4560       Expr *pExpr = sqlite3ExprSkipCollate(pOB->a[ii].pExpr);
  4561   4561       if( pExpr->op!=TK_COLUMN ) return 0;
  4562   4562       if( pExpr->iTable==iCursor ){
         4563  +      if( pExpr->iColumn<0 ) return 1;
  4563   4564         for(jj=0; jj<pIndex->nKeyCol; jj++){
  4564   4565           if( pExpr->iColumn==pIndex->aiColumn[jj] ) return 1;
  4565   4566         }
  4566   4567       }
  4567   4568     }
  4568   4569     return 0;
  4569   4570   }

Changes to test/orderby1.test.

   476    476     SELECT (
   477    477       SELECT 'hardware' FROM ( 
   478    478         SELECT 'software' ORDER BY 'firmware' ASC, 'sportswear' DESC 
   479    479       ) GROUP BY 1 HAVING length(b)
   480    480     )
   481    481     FROM abc;
   482    482   } {hardware hardware hardware}
          483  +
          484  +# Here is a test for a query-planner problem reported on the SQLite
          485  +# mailing list on 2014-09-18 by "Merike".  Beginning with version 3.8.0,
          486  +# a separate sort was being used rather than using the single-column
          487  +# index.  This was due to an oversight in the indexMightHelpWithOrderby()
          488  +# routine in where.c.
          489  +#
          490  +do_execsql_test 7.0 {
          491  +  CREATE TABLE t7(a,b);
          492  +  CREATE INDEX t7a ON t7(a);
          493  +  CREATE INDEX t7ab ON t7(a,b);
          494  +  EXPLAIN QUERY PLAN
          495  +  SELECT * FROM t7 WHERE a=?1 ORDER BY rowid;
          496  +} {~/ORDER BY/}
   483    497   
   484    498   
   485    499   finish_test