SQLite

Check-in [72727b68cd]
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
Timelines: family | ancestors | descendants | both | Cplusplus-comment
Files: files | file ages | folders
SHA1: 72727b68cd07969165f1f0943cc7e1a265436653
User & Date: drh 2014-09-19 02:01:37.043
Context
2014-09-19
04:42
Add the sqlite3VdbeMemClearAndResize() function. Fix a sorting-index prefilter problem. (check-in: 987a7a2119 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: 72727b68cd 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: 5b9b898779 user: drh tags: Cplusplus-comment)
Changes
Unified Diff Show Whitespace Changes Patch
Changes to src/where.c.
4556
4557
4558
4559
4560
4561
4562

4563
4564
4565
4566
4567
4568
4569

  if( pIndex->bUnordered ) return 0;
  if( (pOB = pBuilder->pWInfo->pOrderBy)==0 ) return 0;
  for(ii=0; ii<pOB->nExpr; ii++){
    Expr *pExpr = sqlite3ExprSkipCollate(pOB->a[ii].pExpr);
    if( pExpr->op!=TK_COLUMN ) return 0;
    if( pExpr->iTable==iCursor ){

      for(jj=0; jj<pIndex->nKeyCol; jj++){
        if( pExpr->iColumn==pIndex->aiColumn[jj] ) return 1;
      }
    }
  }
  return 0;
}







>







4556
4557
4558
4559
4560
4561
4562
4563
4564
4565
4566
4567
4568
4569
4570

  if( pIndex->bUnordered ) return 0;
  if( (pOB = pBuilder->pWInfo->pOrderBy)==0 ) return 0;
  for(ii=0; ii<pOB->nExpr; ii++){
    Expr *pExpr = sqlite3ExprSkipCollate(pOB->a[ii].pExpr);
    if( pExpr->op!=TK_COLUMN ) return 0;
    if( pExpr->iTable==iCursor ){
      if( pExpr->iColumn<0 ) return 1;
      for(jj=0; jj<pIndex->nKeyCol; jj++){
        if( pExpr->iColumn==pIndex->aiColumn[jj] ) return 1;
      }
    }
  }
  return 0;
}
Changes to test/orderby1.test.
476
477
478
479
480
481
482














483
484
485
  SELECT (
    SELECT 'hardware' FROM ( 
      SELECT 'software' ORDER BY 'firmware' ASC, 'sportswear' DESC 
    ) GROUP BY 1 HAVING length(b)
  )
  FROM abc;
} {hardware hardware hardware}
















finish_test







>
>
>
>
>
>
>
>
>
>
>
>
>
>



476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
  SELECT (
    SELECT 'hardware' FROM ( 
      SELECT 'software' ORDER BY 'firmware' ASC, 'sportswear' DESC 
    ) GROUP BY 1 HAVING length(b)
  )
  FROM abc;
} {hardware hardware hardware}

# Here is a test for a query-planner problem reported on the SQLite
# mailing list on 2014-09-18 by "Merike".  Beginning with version 3.8.0,
# a separate sort was being used rather than using the single-column
# index.  This was due to an oversight in the indexMightHelpWithOrderby()
# routine in where.c.
#
do_execsql_test 7.0 {
  CREATE TABLE t7(a,b);
  CREATE INDEX t7a ON t7(a);
  CREATE INDEX t7ab ON t7(a,b);
  EXPLAIN QUERY PLAN
  SELECT * FROM t7 WHERE a=?1 ORDER BY rowid;
} {~/ORDER BY/}


finish_test