/ Check-in [cbbeb9de]
Login

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

Overview
Comment:Make sure left joins still work even when the OR clause optimization fires. Ticket #1537. (CVS 2788)
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: cbbeb9de0019a0b81318158711590078fcb7e98a
User & Date: drh 2005-11-26 14:08:08
Context
2005-11-26
14:24
Disable the OR-clause optimization if it does not result in an index being used that would not have been used otherwise. In other words, do not convert OR clauses into an IN statement if it does not help the optimizer. (CVS 2789) check-in: 7e7cfce0 user: drh tags: trunk
14:08
Make sure left joins still work even when the OR clause optimization fires. Ticket #1537. (CVS 2788) check-in: cbbeb9de user: drh tags: trunk
03:51
Remove some vestiges of the old OS_TEST driver. (CVS 2787) check-in: 008f676f user: drh 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.183 2005/11/21 12:48:24 drh Exp $
           19  +** $Id: where.c,v 1.184 2005/11/26 14:08:08 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)
................................................................................
   527    527       return 0;
   528    528     }
   529    529     *pisComplete = z[cnt]==wc[0] && z[cnt+1]==0;
   530    530     *pnPattern = cnt;
   531    531     return 1;
   532    532   }
   533    533   #endif /* SQLITE_OMIT_LIKE_OPTIMIZATION */
          534  +
          535  +/*
          536  +** If the pBase expression originated in the ON or USING clause of
          537  +** a join, then transfer the appropriate markings over to derived.
          538  +*/
          539  +static void transferJoinMarkings(Expr *pDerived, Expr *pBase){
          540  +  pDerived->flags |= pBase->flags & EP_FromJoin;
          541  +  pDerived->iRightJoinTable = pBase->iRightJoinTable;
          542  +}
          543  +
   534    544   
   535    545   /*
   536    546   ** The input to this routine is an WhereTerm structure with only the
   537    547   ** "pExpr" field filled in.  The job of this routine is to analyze the
   538    548   ** subexpression and populate all the other fields of the WhereTerm
   539    549   ** structure.
   540    550   **
................................................................................
   686    696         pDup = sqlite3Expr(TK_COLUMN, 0, 0, 0);
   687    697         if( pDup ){
   688    698           pDup->iTable = iCursor;
   689    699           pDup->iColumn = iColumn;
   690    700         }
   691    701         pNew = sqlite3Expr(TK_IN, pDup, 0, 0);
   692    702         if( pNew ){
          703  +        transferJoinMarkings(pNew, pExpr);
   693    704           pNew->pList = pList;
   694    705         }else{
   695    706           sqlite3ExprListDelete(pList);
   696    707         }
   697    708         pTerm->pExpr = pNew;
   698    709         pTerm->flags |= TERM_DYNAMIC;
   699    710         exprAnalyze(pSrc, pMaskSet, pWC, idxTerm);

Added test/tkt1537.test.

            1  +# 2005 November 26
            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  +# This file implements regression tests for SQLite library.
           12  +#
           13  +# This file implements tests to verify that ticket #1537 is
           14  +# fixed.  
           15  +#
           16  +
           17  +set testdir [file dirname $argv0]
           18  +source $testdir/tester.tcl
           19  +
           20  +do_test tkt1537-1.1 {
           21  +  execsql {
           22  +    CREATE TABLE t1(id, a1, a2);
           23  +    INSERT INTO t1 VALUES(1, NULL, NULL);
           24  +    INSERT INTO t1 VALUES(2, 1, 3);
           25  +    CREATE TABLE t2(id, b);
           26  +    INSERT INTO t2 VALUES(3, 1);
           27  +    INSERT INTO t2 VALUES(4, NULL);
           28  +    SELECT * FROM t1 LEFT JOIN t2 ON a1=b OR a2=+b;
           29  +  }
           30  +} {1 {} {} {} {} 2 1 3 3 1}
           31  +do_test tkt1537-1.2 {
           32  +  execsql {
           33  +    SELECT * FROM t1 LEFT JOIN t2 ON a1=b OR a2=b;
           34  +  }
           35  +} {1 {} {} {} {} 2 1 3 3 1}
           36  +do_test tkt1537-1.3 {
           37  +  execsql {
           38  +    SELECT * FROM t2 LEFT JOIN t1 ON a1=b OR a2=b;
           39  +  }
           40  +} {3 1 2 1 3 4 {} {} {} {}}
           41  +do_test tkt1537-1.4 {
           42  +  execsql {
           43  +    SELECT * FROM t1 LEFT JOIN t2 ON b IN (a1,a2);
           44  +  }
           45  +} {1 {} {} {} {} 2 1 3 3 1}
           46  +do_test tkt1537-1.5 {
           47  +  execsql {
           48  +    SELECT * FROM t2 LEFT JOIN t1 ON b IN (a2,a1);
           49  +  }
           50  +} {3 1 2 1 3 4 {} {} {} {}}
           51  +do_test tkt1537-1.6 {
           52  +  execsql {
           53  +    CREATE INDEX t1a1 ON t1(a1);
           54  +    CREATE INDEX t1a2 ON t1(a2);
           55  +    CREATE INDEX t2b ON t2(b);
           56  +    SELECT * FROM t1 LEFT JOIN t2 ON a1=b OR a2=b;
           57  +  }
           58  +} {1 {} {} {} {} 2 1 3 3 1}
           59  +do_test tkt1537-1.7 {
           60  +  execsql {
           61  +    SELECT * FROM t2 LEFT JOIN t1 ON a1=b OR a2=b;
           62  +  }
           63  +} {3 1 2 1 3 4 {} {} {} {}}
           64  +do_test tkt1537-1.8 {
           65  +  execsql {
           66  +    SELECT * FROM t1 LEFT JOIN t2 ON b IN (a1,a2);
           67  +  }
           68  +} {1 {} {} {} {} 2 1 3 3 1}
           69  +do_test tkt1537-1.9 {
           70  +  execsql {
           71  +    SELECT * FROM t2 LEFT JOIN t1 ON b IN (a2,a1);
           72  +  }
           73  +} {3 1 2 1 3 4 {} {} {} {}}
           74  +
           75  +execsql {
           76  +  DROP INDEX t1a1;
           77  +  DROP INDEX t1a2;
           78  +  DROP INDEX t2b;
           79  +}
           80  +
           81  +do_test tkt1537-2.1 {
           82  +  execsql {
           83  +    SELECT * FROM t1 LEFT JOIN t2 ON b BETWEEN a1 AND a2;
           84  +  }
           85  +} {1 {} {} {} {} 2 1 3 3 1}
           86  +do_test tkt1537-2.2 {
           87  +  execsql {
           88  +    CREATE INDEX t2b ON t2(b);
           89  +    SELECT * FROM t1 LEFT JOIN t2 ON b BETWEEN a1 AND a2;
           90  +  }
           91  +} {1 {} {} {} {} 2 1 3 3 1}
           92  +do_test tkt1537-2.3 {
           93  +  execsql {
           94  +    SELECT * FROM t2 LEFT JOIN t1 ON b BETWEEN a1 AND a2;
           95  +  }
           96  +} {3 1 2 1 3 4 {} {} {} {}}
           97  +do_test tkt1537-2.4 {
           98  +  execsql {
           99  +    CREATE INDEX t1a1 ON t1(a1);
          100  +    CREATE INDEX t1a2 ON t1(a2);
          101  +    SELECT * FROM t2 LEFT JOIN t1 ON b BETWEEN a1 AND a2;
          102  +  }
          103  +} {3 1 2 1 3 4 {} {} {} {}}
          104  +
          105  +do_test tkt1537-3.1 {
          106  +  execsql {
          107  +    SELECT * FROM t1 LEFT JOIN t2 ON b GLOB 'abc*' WHERE t1.id=1;
          108  +  }
          109  +} {1 {} {} {} {}}
          110  +do_test tkt1537-3.2 {
          111  +  execsql { 
          112  +    SELECT * FROM t2 LEFT JOIN t1 ON a1 GLOB 'abc*' WHERE t2.id=3;
          113  +  }
          114  +} {3 1 {} {} {}}
          115  +
          116  +
          117  +finish_test