SQLite

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 Unified Diffs Ignore Whitespace Patch

Changes to src/where.c.

4541
4542
4543
4544
4545
4546
4547
4548
4549
4550
4551
4552
4553
4554
4555
4556
4557
4558
4559
4560
4561
4562

4563
4564
4565
4566
4567
4568
4569
4570
4571
4572
4573
4574
4575
4576
4577
4578
4579
4580
4581
4582
4583
4584
/*
** Return True if it is possible that pIndex might be useful in
** implementing the ORDER BY clause in pBuilder.
**
** Return False if pBuilder does not contain an ORDER BY clause or
** if there is no way for pIndex to be useful in implementing that
** ORDER BY clause.
*/
static int indexMightHelpWithOrderBy(
  WhereLoopBuilder *pBuilder,
  Index *pIndex,
  int iCursor
){
  ExprList *pOB;
  int ii, jj;

  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;
}

/*
** Return a bitmask where 1s indicate that the corresponding column of
** the table is used by an index.  Only the first 63 columns are considered.
*/
static Bitmask columnsInIndex(Index *pIdx){
  Bitmask m = 0;
  int j;
  for(j=pIdx->nColumn-1; j>=0; j--){
    int x = pIdx->aiColumn[j];
    if( x>=0 ){
      testcase( x==BMS-1 );
      testcase( x==BMS-2 );
      if( x<BMS-1 ) m |= MASKBIT(x);
    }






















>






















4541
4542
4543
4544
4545
4546
4547
4548
4549
4550
4551
4552
4553
4554
4555
4556
4557
4558
4559
4560
4561
4562
4563
4564
4565
4566
4567
4568
4569
4570
4571
4572
4573
4574
4575
4576
4577
4578
4579
4580
4581
4582
4583
4584
4585
/*
** Return True if it is possible that pIndex might be useful in
** implementing the ORDER BY clause in pBuilder.
**
** Return False if pBuilder does not contain an ORDER BY clause or
** if there is no way for pIndex to be useful in implementing that
** ORDER BY clause.
*/
static int indexMightHelpWithOrderBy(
  WhereLoopBuilder *pBuilder,
  Index *pIndex,
  int iCursor
){
  ExprList *pOB;
  int ii, jj;

  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;
}

/*
** Return a bitmask where 1s indicate that the corresponding column of
** the table is used by an index.  Only the first 63 columns are considered.
*/
static Bitmask columnsInIndex(Index *pIdx){
  Bitmask m = 0;
  int j;
  for(j=pIdx->nColumn-1; j>=0; j--){
    int x = pIdx->aiColumn[j];
    if( x>=0 ){
      testcase( x==BMS-1 );
      testcase( x==BMS-2 );
      if( x<BMS-1 ) m |= MASKBIT(x);
    }

Changes to test/orderby1.test.

461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482














483
484
485
  EXPLAIN QUERY PLAN SELECT 5 UNION ALL SELECT 3 ORDER BY 1
} {~/B-TREE/}
do_execsql_test 5.2 {
  SELECT 5 UNION ALL SELECT 3 ORDER BY 1
} {3 5}

# The following test (originally derived from a single test within fuzz.test)
# verifies that a PseudoTable cursor is not closed prematurely in a deeply
# nested query.  This test caused a segfault on 3.8.5 beta.
#
do_execsql_test 6.0 {
  CREATE TABLE abc(a, b, c);
  INSERT INTO abc VALUES(1, 2, 3);
  INSERT INTO abc VALUES(4, 5, 6);
  INSERT INTO abc VALUES(7, 8, 9);
  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






















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



461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
  EXPLAIN QUERY PLAN SELECT 5 UNION ALL SELECT 3 ORDER BY 1
} {~/B-TREE/}
do_execsql_test 5.2 {
  SELECT 5 UNION ALL SELECT 3 ORDER BY 1
} {3 5}

# The following test (originally derived from a single test within fuzz.test)
# verifies that a PseudoTable cursor is not closed prematurely in a deeply
# nested query.  This test caused a segfault on 3.8.5 beta.
#
do_execsql_test 6.0 {
  CREATE TABLE abc(a, b, c);
  INSERT INTO abc VALUES(1, 2, 3);
  INSERT INTO abc VALUES(4, 5, 6);
  INSERT INTO abc VALUES(7, 8, 9);
  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