/ Check-in [abb12259]
Login

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

Overview
Comment:Fix for bug #8: Correctly handle terms of a WHERE clause in a join where the term does not use a comparison operator. (CVS 515)
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: abb12259a09418eb6e3cf573ea718ac58c91ac7b
User & Date: drh 2002-04-02 13:26:10
Context
2002-04-02
13:27
Version 2.4.6 (CVS 516) check-in: 5ae7efd8 user: drh tags: trunk
13:26
Fix for bug #8: Correctly handle terms of a WHERE clause in a join where the term does not use a comparison operator. (CVS 515) check-in: abb12259 user: drh tags: trunk
02:00
Version 2.4.5 (CVS 514) check-in: b18a7b77 user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to VERSION.

     1         -2.4.5
            1  +2.4.6

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.39 2002/04/02 01:58:58 drh Exp $
           16  +** $Id: where.c,v 1.40 2002/04/02 13:26:11 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.
................................................................................
    28     28     int indexable;          /* True if this subexprssion is usable by an index */
    29     29     int idxLeft;            /* p->pLeft is a column in this table number. -1 if
    30     30                             ** p->pLeft is not the column of any table */
    31     31     int idxRight;           /* p->pRight is a column in this table number. -1 if
    32     32                             ** p->pRight is not the column of any table */
    33     33     unsigned prereqLeft;    /* Tables referenced by p->pLeft */
    34     34     unsigned prereqRight;   /* Tables referenced by p->pRight */
           35  +  unsigned prereqAll;     /* Tables referenced by this expression in any way */
    35     36   };
    36     37   
    37     38   /*
    38     39   ** Determine the number of elements in an array.
    39     40   */
    40     41   #define ARRAYSIZE(X)  (sizeof(X)/sizeof(X[0]))
    41     42   
................................................................................
   126    127   ** "base" is the cursor number (the value of the iTable field) that
   127    128   ** corresponds to the first entry in the table list.
   128    129   */
   129    130   static void exprAnalyze(int base, ExprInfo *pInfo){
   130    131     Expr *pExpr = pInfo->p;
   131    132     pInfo->prereqLeft = exprTableUsage(base, pExpr->pLeft);
   132    133     pInfo->prereqRight = exprTableUsage(base, pExpr->pRight);
          134  +  pInfo->prereqAll = exprTableUsage(base, pExpr);
   133    135     pInfo->indexable = 0;
   134    136     pInfo->idxLeft = -1;
   135    137     pInfo->idxRight = -1;
   136    138     if( allowedOp(pExpr->op) && (pInfo->prereqRight & pInfo->prereqLeft)==0 ){
   137    139       if( pExpr->pRight->op==TK_COLUMN ){
   138    140         pInfo->idxRight = pExpr->pRight->iTable - base;
   139    141         pInfo->indexable = 1;
................................................................................
   747    749       loopMask |= 1<<idx;
   748    750   
   749    751       /* Insert code to test every subexpression that can be completely
   750    752       ** computed using the current set of tables.
   751    753       */
   752    754       for(j=0; j<nExpr; j++){
   753    755         if( aExpr[j].p==0 ) continue;
   754         -      if( (aExpr[j].prereqRight & loopMask)!=aExpr[j].prereqRight ) continue;
   755         -      if( (aExpr[j].prereqLeft & loopMask)!=aExpr[j].prereqLeft ) continue;
          756  +      if( (aExpr[j].prereqAll & loopMask)!=aExpr[j].prereqAll ) continue;
   756    757         if( haveKey ){
   757    758           haveKey = 0;
   758    759           sqliteVdbeAddOp(v, OP_MoveTo, base+idx, 0);
   759    760         }
   760    761         sqliteExprIfFalse(pParse, aExpr[j].p, cont);
   761    762         aExpr[j].p = 0;
   762    763       }

Changes to test/select2.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 SELECT statement.
    13     13   #
    14         -# $Id: select2.test,v 1.17 2002/04/02 01:58:58 drh Exp $
           14  +# $Id: select2.test,v 1.18 2002/04/02 13:26:11 drh Exp $
    15     15   
    16     16   set testdir [file dirname $argv0]
    17     17   source $testdir/tester.tcl
    18     18   
    19     19   # Create a table with some data
    20     20   #
    21     21   execsql {CREATE TABLE tbl1(f1 int, f2 int)}
................................................................................
   123    123       INSERT INTO aa VALUES(1);
   124    124       INSERT INTO aa VALUES(3);
   125    125       INSERT INTO bb VALUES(2);
   126    126       INSERT INTO bb VALUES(4);
   127    127       SELECT * FROM aa, bb WHERE max(a,b)>2;
   128    128     }
   129    129   } {1 4 3 2 3 4}
          130  +do_test select2-4.2 {
          131  +  execsql {
          132  +    INSERT INTO bb VALUES(0);
          133  +    SELECT * FROM aa, bb WHERE b;
          134  +  }
          135  +} {1 2 1 4 3 2 3 4}
          136  +do_test select2-4.3 {
          137  +  execsql {
          138  +    SELECT * FROM aa, bb WHERE NOT b;
          139  +  }
          140  +} {1 0 3 0}
          141  +do_test select2-4.4 {
          142  +  execsql {
          143  +    SELECT * FROM aa, bb WHERE min(a,b);
          144  +  }
          145  +} {1 2 1 4 3 2 3 4}
          146  +do_test select2-4.5 {
          147  +  execsql {
          148  +    SELECT * FROM aa, bb WHERE NOT min(a,b);
          149  +  }
          150  +} {1 0 3 0}
          151  +do_test select2-4.6 {
          152  +  execsql {
          153  +    SELECT * FROM aa, bb WHERE CASE WHEN a=b-1 THEN 1 END;
          154  +  }
          155  +} {1 2 3 4}
          156  +do_test select2-4.7 {
          157  +  execsql {
          158  +    SELECT * FROM aa, bb WHERE CASE WHEN a=b-1 THEN 0 ELSE 1 END;
          159  +  }
          160  +} {1 4 1 0 3 2 3 0}
   130    161   
   131    162   finish_test

Changes to www/changes.tcl.

    20     20   }
    21     21   
    22     22   
    23     23   proc chng {date desc} {
    24     24     puts "<DT><B>$date</B></DT>"
    25     25     puts "<DD><P><UL>$desc</UL></P></DD>"
    26     26   }
           27  +
           28  +chng {2002 Apr 02 (2.4.6)} {
           29  +<li>Bug fix: Correctly handle terms in the WHERE clause of a join that
           30  +    do not contain a comparison operator.</li>
           31  +}
    27     32   
    28     33   chng {2002 Apr 01 (2.4.5)} {
    29     34   <li>Bug fix: Correctly handle functions that appear in the WHERE clause
    30         -    of a SELECT.</li>
           35  +    of a join.</li>
    31     36   <li>When the PRAGMA vdbe_trace=ON is set, correctly print the P3 operand
    32     37       value when it is a pointer to a structure rather than a pointer to
    33     38       a string.</li>
    34     39   <li>When inserting an explicit NULL into an INTEGER PRIMARY KEY, convert
    35     40       the NULL value into a unique key automatically.</li>
    36     41   }
    37     42