SQLite: Check-in [9f18b303]

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

Overview

Comment:	Fix the query planner so that it once again knows that queries without a FROM clause will never return more than one row and hence do not require sorting.
Downloads:	Tarball \| ZIP archive \| SQL archive
Timelines:	family \| ancestors \| descendants \| both \| trunk
Files:	files \| file ages \| folders
SHA1:	9f18b303cd1bc5779d82669884f802c7889b4947
User & Date:	drh 2014-06-02 11:26:33

Context

2014-06-02
18:24		Fix a typo in a requirements mark comment in a test script. No changes to code. (check-in: 75ff459b user: drh tags: trunk)
11:26		Fix the query planner so that it once again knows that queries without a FROM clause will never return more than one row and hence do not require sorting. (check-in: 9f18b303 user: drh tags: trunk)
09:39		Add the "valgrind-nolookaside" permutation to permutations.test. (check-in: 8e8472d9 user: dan tags: trunk)

Changes

Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/where.c.


  pParse = pWInfo->pParse;
  db = pParse->db;
  nLoop = pWInfo->nLevel;
  /* TUNING: For simple queries, only the best path is tracked.
  ** For 2-way joins, the 5 best paths are followed.
  ** For joins of 3 or more tables, track the 10 best paths */
  mxChoice = (nLoop==1) ? 1 : (nLoop==2 ? 5 : 10);
  assert( nLoop<=pWInfo->pTabList->nSrc );
  WHERETRACE(0x002, ("---- begin solver\n"));

  /* Allocate and initialize space for aTo and aFrom */
  ii = (sizeof(WherePath)+sizeof(WhereLoop*)*nLoop)*mxChoice*2;
  pSpace = sqlite3DbMallocRaw(db, ii);
  if( pSpace==0 ) return SQLITE_NOMEM;
................................................................................

  /* Precompute the cost of sorting the final result set, if the caller
  ** to sqlite3WhereBegin() was concerned about sorting */
  if( pWInfo->pOrderBy==0 || nRowEst==0 ){
    aFrom[0].isOrdered = 0;
    nOrderBy = 0;
  }else{
    aFrom[0].isOrdered = -1;
    nOrderBy = pWInfo->pOrderBy->nExpr;
  }

  /* Compute successively longer WherePaths using the previous generation
  ** of WherePaths as the basis for the next.  Keep track of the mxChoice
  ** best paths at each generation */
  for(iLoop=0; iLoop<nLoop; iLoop++){


  pParse = pWInfo->pParse;
  db = pParse->db;
  nLoop = pWInfo->nLevel;
  /* TUNING: For simple queries, only the best path is tracked.
  ** For 2-way joins, the 5 best paths are followed.
  ** For joins of 3 or more tables, track the 10 best paths */
  mxChoice = (nLoop<=1) ? 1 : (nLoop==2 ? 5 : 10);
  assert( nLoop<=pWInfo->pTabList->nSrc );
  WHERETRACE(0x002, ("---- begin solver\n"));

  /* Allocate and initialize space for aTo and aFrom */
  ii = (sizeof(WherePath)+sizeof(WhereLoop*)*nLoop)*mxChoice*2;
  pSpace = sqlite3DbMallocRaw(db, ii);
  if( pSpace==0 ) return SQLITE_NOMEM;
................................................................................

  /* Precompute the cost of sorting the final result set, if the caller
  ** to sqlite3WhereBegin() was concerned about sorting */
  if( pWInfo->pOrderBy==0 || nRowEst==0 ){
    aFrom[0].isOrdered = 0;
    nOrderBy = 0;
  }else{
    aFrom[0].isOrdered = nLoop>0 ? -1 : 1;
    nOrderBy = pWInfo->pOrderBy->nExpr;
  }

  /* Compute successively longer WherePaths using the previous generation
  ** of WherePaths as the basis for the next.  Keep track of the mxChoice
  ** best paths at each generation */
  for(iLoop=0; iLoop<nLoop; iLoop++){

Changes to test/orderby1.test.

    INSERT INTO t41 VALUES(1,1),(3,1);
    INSERT INTO t42 VALUES(1,13),(1,15),(3,14),(3,16);
    
    SELECT b, y FROM t41 CROSS JOIN t42 ON x=a ORDER BY b, y;
  }
} {1 13 1 14 1 15 1 16}













finish_test








>
|
>
>
>
>
>
>
>
>
>

    INSERT INTO t41 VALUES(1,1),(3,1);
    INSERT INTO t42 VALUES(1,13),(1,15),(3,14),(3,16);
    
    SELECT b, y FROM t41 CROSS JOIN t42 ON x=a ORDER BY b, y;
  }
} {1 13 1 14 1 15 1 16}

# No sorting of queries that omit the FROM clause.
#
do_execsql_test 5.0 {
  EXPLAIN QUERY PLAN SELECT 5 ORDER BY 1
} {}
do_execsql_test 5.1 {
  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}

finish_test