/ Check-in [24e4cf73]
Login

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

Overview
Comment:Fix for ticket #31: Do not attempt the flattening optimization if the subselect does not contain a FROM clause. Handle the special case where a WHERE clause is constant. (CVS 548)
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1:24e4cf73d22bb41d26bf3c833f1854a9c90923e8
User & Date: drh 2002-04-30 19:20:28
Context
2002-05-03
00:17
Update the change log prior to releasing version 2.4.10. (CVS 549) check-in: 67838bbc user: drh tags: trunk
2002-04-30
19:20
Fix for ticket #31: Do not attempt the flattening optimization if the subselect does not contain a FROM clause. Handle the special case where a WHERE clause is constant. (CVS 548) check-in: 24e4cf73 user: drh tags: trunk
2002-04-26
09:47
Fix for ticket #28: Export the "sqlite_changes" function in Windows DLLs. (CVS 547) check-in: f7cea463 user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to VERSION.

     1         -2.4.9
            1  +2.4.10

Changes to src/select.c.

     8      8   **    May you find forgiveness for yourself and forgive others.
     9      9   **    May you share freely, never taking more than you give.
    10     10   **
    11     11   *************************************************************************
    12     12   ** This file contains C code routines that are called by the parser
    13     13   ** to handle SELECT statements in SQLite.
    14     14   **
    15         -** $Id: select.c,v 1.79 2002/04/23 17:10:18 drh Exp $
           15  +** $Id: select.c,v 1.80 2002/04/30 19:20:29 drh Exp $
    16     16   */
    17     17   #include "sqliteInt.h"
    18     18   
    19     19   /*
    20     20   ** Allocate a new Select structure and return a pointer to that
    21     21   ** structure.
    22     22   */
................................................................................
   913    913   **   (4)  The subquery is not DISTINCT or the outer query is not a join.
   914    914   **
   915    915   **   (5)  The subquery is not DISTINCT or the outer query does not use
   916    916   **        aggregates.
   917    917   **
   918    918   **   (6)  The subquery does not use aggregates or the outer query is not
   919    919   **        DISTINCT.
          920  +**
          921  +**   (7)  The subquery has a FROM clause.
   920    922   **
   921    923   ** In this routine, the "p" parameter is a pointer to the outer query.
   922    924   ** The subquery is p->pSrc->a[iFrom].  isAgg is true if the outer query
   923    925   ** uses aggregates and subqueryIsAgg is true if the subquery uses aggregates.
   924    926   **
   925    927   ** If flattening is not attempted, this routine is a no-op and return 0.
   926    928   ** If flattening is attempted this routine returns 1.
................................................................................
   943    945     assert( pSrc && iFrom>=0 && iFrom<pSrc->nId );
   944    946     pSub = pSrc->a[iFrom].pSelect;
   945    947     assert( pSub!=0 );
   946    948     if( isAgg && subqueryIsAgg ) return 0;
   947    949     if( subqueryIsAgg && pSrc->nId>1 ) return 0;
   948    950     pSubSrc = pSub->pSrc;
   949    951     assert( pSubSrc );
   950         -  if( pSubSrc->nId>1 ) return 0;
          952  +  if( pSubSrc->nId!=1 ) return 0;
   951    953     if( pSub->isDistinct && pSrc->nId>1 ) return 0;
   952    954     if( pSub->isDistinct && isAgg ) return 0;
   953    955     if( p->isDistinct && subqueryIsAgg ) return 0;
   954    956   
   955    957     /* If we reach this point, it means flatting is permitted for the
   956    958     ** i-th entry of the FROM clause in the outer query.
   957    959     */

Changes to src/where.c.

     9      9   **    May you share freely, never taking more than you give.
    10     10   **
    11     11   *************************************************************************
    12     12   ** This module contains C code that generates VDBE code used to process
    13     13   ** the WHERE clause of SQL statements.  Also found here are subroutines
    14     14   ** to generate VDBE code to evaluate expressions.
    15     15   **
    16         -** $Id: where.c,v 1.40 2002/04/02 13:26:11 drh Exp $
           16  +** $Id: where.c,v 1.41 2002/04/30 19:20:29 drh Exp $
    17     17   */
    18     18   #include "sqliteInt.h"
    19     19   
    20     20   /*
    21     21   ** The query generator uses an array of instances of this structure to
    22     22   ** help it analyze the subexpressions of the WHERE clause.  Each WHERE
    23     23   ** clause subexpression is separated from the others by an AND operator.
................................................................................
   189    189       sqliteFree(pWInfo);
   190    190       return 0;
   191    191     }
   192    192     pWInfo->pParse = pParse;
   193    193     pWInfo->pTabList = pTabList;
   194    194     pWInfo->base = base;
   195    195     pWInfo->peakNTab = pWInfo->savedNTab = pParse->nTab;
          196  +  pWInfo->iBreak = sqliteVdbeMakeLabel(v);
          197  +
          198  +  /* Special case: a WHERE clause that is constant.  Evaluate the
          199  +  ** expression and either jump over all of the code or fall thru.
          200  +  */
          201  +  if( pWhere && sqliteExprIsConstant(pWhere) ){
          202  +    sqliteExprIfFalse(pParse, pWhere, pWInfo->iBreak);
          203  +  }
   196    204   
   197    205     /* Split the WHERE clause into as many as 32 separate subexpressions
   198    206     ** where each subexpression is separated by an AND operator.  Any additional
   199    207     ** subexpressions are attached in the aExpr[32] and will not enter
   200    208     ** into the query optimizer computations.  32 is chosen as the cutoff
   201    209     ** since that is the number of bits in an integer that we use for an
   202    210     ** expression-used mask.  
................................................................................
   418    426         sqliteVdbeChangeP3(v, -1, pWInfo->a[i].pIdx->zName, P3_STATIC);
   419    427       }
   420    428     }
   421    429   
   422    430     /* Generate the code to do the search
   423    431     */
   424    432     loopMask = 0;
   425         -  pWInfo->iBreak = sqliteVdbeMakeLabel(v);
   426    433     for(i=0; i<pTabList->nId; i++){
   427    434       int j, k;
   428    435       int idx = aOrder[i];
   429    436       Index *pIdx;
   430    437       WhereLevel *pLevel = &pWInfo->a[i];
   431    438   
   432    439       pIdx = pLevel->pIdx;

Changes to test/select6.test.

     8      8   #    May you share freely, never taking more than you give.
     9      9   #
    10     10   #***********************************************************************
    11     11   # This file implements regression tests for SQLite library.  The
    12     12   # focus of this file is testing SELECT statements that contain
    13     13   # subqueries in their FROM clause.
    14     14   #
    15         -# $Id: select6.test,v 1.8 2002/03/23 00:31:29 drh Exp $
           15  +# $Id: select6.test,v 1.9 2002/04/30 19:20:29 drh Exp $
    16     16   
    17     17   set testdir [file dirname $argv0]
    18     18   source $testdir/tester.tcl
    19     19   
    20     20   do_test select6-1.0 {
    21     21     execsql {
    22     22       BEGIN;
................................................................................
   331    331   do_test select6-6.6 {
   332    332     execsql {
   333    333       SELECT * FROM (
   334    334         SELECT x AS 'a' FROM t1 EXCEPT SELECT x*2 AS 'a' FROM t1
   335    335       ) ORDER BY a;
   336    336     }
   337    337   } {1 3}
          338  +
          339  +# Subselects with no FROM clause
          340  +#
          341  +do_test select6-7.1 {
          342  +  execsql {
          343  +    SELECT * FROM (SELECT 1)
          344  +  }
          345  +} {1}
          346  +do_test select6-7.2 {
          347  +  execsql {
          348  +    SELECT c,b,a,* FROM (SELECT 1 AS 'a', 2 AS 'b', 'abc' AS 'c')
          349  +  }
          350  +} {abc 2 1 1 2 abc}
          351  +do_test select6-7.3 {
          352  +  execsql {
          353  +    SELECT c,b,a,* FROM (SELECT 1 AS 'a', 2 AS 'b', 'abc' AS 'c' WHERE 0)
          354  +  }
          355  +} {}
          356  +do_test select6-7.4 {
          357  +  execsql2 {
          358  +    SELECT c,b,a,* FROM (SELECT 1 AS 'a', 2 AS 'b', 'abc' AS 'c' WHERE 1)
          359  +  }
          360  +} {c abc b 2 a 1 a 1 b 2 c abc}
   338    361   
   339    362   finish_test

Changes to test/where.test.

     7      7   #    May you find forgiveness for yourself and forgive others.
     8      8   #    May you share freely, never taking more than you give.
     9      9   #
    10     10   #***********************************************************************
    11     11   # This file implements regression tests for SQLite library.  The
    12     12   # focus of this file is testing the use of indices in WHERE clases.
    13     13   #
    14         -# $Id: where.test,v 1.5 2001/12/22 14:49:26 drh Exp $
           14  +# $Id: where.test,v 1.6 2002/04/30 19:20:29 drh Exp $
    15     15   
    16     16   set testdir [file dirname $argv0]
    17     17   source $testdir/tester.tcl
    18     18   
    19     19   # Build some test data
    20     20   #
    21     21   do_test where-1.0 {
................................................................................
   232    232   } {12 89 12 9}
   233    233   do_test where-3.3 {
   234    234     count {
   235    235       SELECT A.w, B.p, C.w FROM t1 as A, t2 as B, t1 as C
   236    236       WHERE A.w=15 AND B.p=C.w AND B.r=10202-A.y
   237    237     }
   238    238   } {15 86 86 9}
          239  +
          240  +# Test to see that the special case of a constant WHERE clause is
          241  +# handled.
          242  +#
          243  +do_test where-4.1 {
          244  +  count {
          245  +    SELECT * FROM t1 WHERE 0
          246  +  }
          247  +} {0}
          248  +do_test where-4.2 {
          249  +  count {
          250  +    SELECT * FROM t1 WHERE 1 LIMIT 1
          251  +  }
          252  +} {1 0 4 0}
          253  +do_test where-4.3 {
          254  +  execsql {
          255  +    SELECT 99 WHERE 0
          256  +  }
          257  +} {}
          258  +do_test where-4.4 {
          259  +  execsql {
          260  +    SELECT 99 WHERE 1
          261  +  }
          262  +} {99}
          263  +
   239    264   
   240    265   finish_test