/ Check-in [9c9c2a1d]
Login

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

Overview
Comment:In the query optimizer, make sure table dependencies from all terms of a compound SELECT statement are recognized so that subqueries in a WHERE clause are not evaluated too early. Fix for ticket #2640. (CVS 4422)
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1:9c9c2a1da2b6235b3b0541d1f55a02a1f350567f
User & Date: drh 2007-09-12 15:41:01
Context
2007-09-12
17:01
Fixes for compilation/testing when the various OMIT macros are defined. (CVS 4423) check-in: c8405b15 user: danielk1977 tags: trunk
15:41
In the query optimizer, make sure table dependencies from all terms of a compound SELECT statement are recognized so that subqueries in a WHERE clause are not evaluated too early. Fix for ticket #2640. (CVS 4422) check-in: 9c9c2a1d user: drh tags: trunk
14:09
Add a missing semi-colon to os_win.c. Ticket #2642. (CVS 4421) check-in: c6ee5929 user: danielk1977 tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/where.c.

    12     12   ** This module contains C code that generates VDBE code used to process
    13     13   ** the WHERE clause of SQL statements.  This module is reponsible for
    14     14   ** generating the code that loops through a table looking for applicable
    15     15   ** rows.  Indices are selected and used to speed the search when doing
    16     16   ** so is applicable.  Because this module is responsible for selecting
    17     17   ** indices, you might also think of this module as the "query optimizer".
    18     18   **
    19         -** $Id: where.c,v 1.259 2007/08/29 14:06:23 danielk1977 Exp $
           19  +** $Id: where.c,v 1.260 2007/09/12 15:41:01 drh Exp $
    20     20   */
    21     21   #include "sqliteInt.h"
    22     22   
    23     23   /*
    24     24   ** The number of bits in a Bitmask.  "BMS" means "BitMask Size".
    25     25   */
    26     26   #define BMS  (sizeof(Bitmask)*8)
................................................................................
   346    346       for(i=0; i<pList->nExpr; i++){
   347    347         mask |= exprTableUsage(pMaskSet, pList->a[i].pExpr);
   348    348       }
   349    349     }
   350    350     return mask;
   351    351   }
   352    352   static Bitmask exprSelectTableUsage(ExprMaskSet *pMaskSet, Select *pS){
   353         -  Bitmask mask;
   354         -  if( pS==0 ){
   355         -    mask = 0;
   356         -  }else{
   357         -    mask = exprListTableUsage(pMaskSet, pS->pEList);
          353  +  Bitmask mask = 0;
          354  +  while( pS ){
          355  +    mask |= exprListTableUsage(pMaskSet, pS->pEList);
   358    356       mask |= exprListTableUsage(pMaskSet, pS->pGroupBy);
   359    357       mask |= exprListTableUsage(pMaskSet, pS->pOrderBy);
   360    358       mask |= exprTableUsage(pMaskSet, pS->pWhere);
   361    359       mask |= exprTableUsage(pMaskSet, pS->pHaving);
          360  +    pS = pS->pPrior;
   362    361     }
   363    362     return mask;
   364    363   }
   365    364   
   366    365   /*
   367    366   ** Return TRUE if the given operator is one of the operators that is
   368    367   ** allowed for an indexable WHERE clause term.  The allowed operators are

Added test/tkt2640.test.

            1  +# 2007 Sep 12
            2  +#
            3  +# The author disclaims copyright to this source code. In place of
            4  +# a legal notice, here is a blessing:
            5  +#
            6  +#    May you do good and not evil.
            7  +#    May you find forgiveness for yourself and forgive others.
            8  +#    May you share freely, never taking more than you give.
            9  +#
           10  +#***********************************************************************
           11  +#
           12  +# This file is to test that ticket #2640 has been fixed.
           13  +#
           14  +# $Id: tkt2640.test,v 1.1 2007/09/12 15:41:01 drh Exp $
           15  +#
           16  +
           17  +# The problem in ticket #2640 was that the query optimizer was 
           18  +# not recognizing all uses of tables within subqueries in the
           19  +# WHERE clause.  If the subquery contained a compound SELECT,
           20  +# then tables that were used by terms of the compound other than
           21  +# the last term would not be recognized as dependencies.
           22  +# So if one of the SELECT statements within a compound made
           23  +# use of a table that occurs later in a join, the query
           24  +# optimizer would not recognize this and would try to evaluate
           25  +# the subquery too early, before that tables value had been
           26  +# established.
           27  +
           28  +set testdir [file dirname $argv0]
           29  +source $testdir/tester.tcl
           30  +
           31  +do_test tkt2640-1.1 {
           32  +  execsql {
           33  +    CREATE TABLE persons(person_id, name);
           34  +    INSERT INTO persons VALUES(1,'fred');
           35  +    INSERT INTO persons VALUES(2,'barney');
           36  +    INSERT INTO persons VALUES(3,'wilma');
           37  +    INSERT INTO persons VALUES(4,'pebbles');
           38  +    INSERT INTO persons VALUES(5,'bambam');
           39  +    CREATE TABLE directors(person_id);
           40  +    INSERT INTO directors VALUES(5);
           41  +    INSERT INTO directors VALUES(3);
           42  +    CREATE TABLE writers(person_id);
           43  +    INSERT INTO writers VALUES(2);
           44  +    INSERT INTO writers VALUES(3);
           45  +    INSERT INTO writers VALUES(4);
           46  +    SELECT DISTINCT p.name
           47  +      FROM persons p, directors d
           48  +     WHERE d.person_id=p.person_id
           49  +       AND NOT EXISTS (
           50  +             SELECT person_id FROM directors d1 WHERE d1.person_id=p.person_id
           51  +             EXCEPT
           52  +             SELECT person_id FROM writers w
           53  +           );
           54  +  }
           55  +} {wilma}
           56  +do_test tkt2640-1.2 {
           57  +  execsql {
           58  +    SELECT DISTINCT p.name
           59  +      FROM persons p CROSS JOIN directors d
           60  +     WHERE d.person_id=p.person_id
           61  +       AND NOT EXISTS (
           62  +             SELECT person_id FROM directors d1 WHERE d1.person_id=p.person_id
           63  +             EXCEPT
           64  +             SELECT person_id FROM writers w
           65  +           );
           66  +  }
           67  +} {wilma}
           68  +do_test tkt2640-1.3 {
           69  +  execsql {
           70  +    SELECT DISTINCT p.name
           71  +      FROM directors d CROSS JOIN persons p
           72  +     WHERE d.person_id=p.person_id
           73  +       AND NOT EXISTS (
           74  +             SELECT person_id FROM directors d1 WHERE d1.person_id=p.person_id
           75  +             EXCEPT
           76  +             SELECT person_id FROM writers w
           77  +           );
           78  +  }
           79  +} {wilma}
           80  +do_test tkt2640-1.4 {
           81  +  execsql {
           82  +    SELECT DISTINCT p.name
           83  +      FROM persons p, directors d
           84  +     WHERE d.person_id=p.person_id
           85  +       AND NOT EXISTS (
           86  +             SELECT person_id FROM directors d1 WHERE d1.person_id=d.person_id
           87  +             EXCEPT
           88  +             SELECT person_id FROM writers w
           89  +           );
           90  +  }
           91  +} {wilma}
           92  +do_test tkt2640-1.5 {
           93  +  execsql {
           94  +    SELECT DISTINCT p.name
           95  +      FROM persons p CROSS JOIN directors d
           96  +     WHERE d.person_id=p.person_id
           97  +       AND NOT EXISTS (
           98  +             SELECT person_id FROM directors d1 WHERE d1.person_id=d.person_id
           99  +             EXCEPT
          100  +             SELECT person_id FROM writers w
          101  +           );
          102  +  }
          103  +} {wilma}
          104  +do_test tkt2640-1.6 {
          105  +  execsql {
          106  +    SELECT DISTINCT p.name
          107  +      FROM directors d CROSS JOIN persons p
          108  +     WHERE d.person_id=p.person_id
          109  +       AND NOT EXISTS (
          110  +             SELECT person_id FROM directors d1 WHERE d1.person_id=d.person_id
          111  +             EXCEPT
          112  +             SELECT person_id FROM writers w
          113  +           );
          114  +  }
          115  +} {wilma}
          116  +
          117  +
          118  +
          119  +finish_test