/ 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 Unified Diffs Show Whitespace Changes Patch

Changes to VERSION.

1
2.4.5
|
1
2.4.6

Changes to src/where.c.

9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
..
28
29
30
31
32
33
34

35
36
37
38
39
40
41
...
126
127
128
129
130
131
132

133
134
135
136
137
138
139
...
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
**    May you share freely, never taking more than you give.
**
*************************************************************************
** This module contains C code that generates VDBE code used to process
** the WHERE clause of SQL statements.  Also found here are subroutines
** to generate VDBE code to evaluate expressions.
**
** $Id: where.c,v 1.39 2002/04/02 01:58:58 drh Exp $
*/
#include "sqliteInt.h"

/*
** The query generator uses an array of instances of this structure to
** help it analyze the subexpressions of the WHERE clause.  Each WHERE
** clause subexpression is separated from the others by an AND operator.
................................................................................
  int indexable;          /* True if this subexprssion is usable by an index */
  int idxLeft;            /* p->pLeft is a column in this table number. -1 if
                          ** p->pLeft is not the column of any table */
  int idxRight;           /* p->pRight is a column in this table number. -1 if
                          ** p->pRight is not the column of any table */
  unsigned prereqLeft;    /* Tables referenced by p->pLeft */
  unsigned prereqRight;   /* Tables referenced by p->pRight */

};

/*
** Determine the number of elements in an array.
*/
#define ARRAYSIZE(X)  (sizeof(X)/sizeof(X[0]))

................................................................................
** "base" is the cursor number (the value of the iTable field) that
** corresponds to the first entry in the table list.
*/
static void exprAnalyze(int base, ExprInfo *pInfo){
  Expr *pExpr = pInfo->p;
  pInfo->prereqLeft = exprTableUsage(base, pExpr->pLeft);
  pInfo->prereqRight = exprTableUsage(base, pExpr->pRight);

  pInfo->indexable = 0;
  pInfo->idxLeft = -1;
  pInfo->idxRight = -1;
  if( allowedOp(pExpr->op) && (pInfo->prereqRight & pInfo->prereqLeft)==0 ){
    if( pExpr->pRight->op==TK_COLUMN ){
      pInfo->idxRight = pExpr->pRight->iTable - base;
      pInfo->indexable = 1;
................................................................................
    loopMask |= 1<<idx;

    /* Insert code to test every subexpression that can be completely
    ** computed using the current set of tables.
    */
    for(j=0; j<nExpr; j++){
      if( aExpr[j].p==0 ) continue;
      if( (aExpr[j].prereqRight & loopMask)!=aExpr[j].prereqRight ) continue;
      if( (aExpr[j].prereqLeft & loopMask)!=aExpr[j].prereqLeft ) continue;
      if( haveKey ){
        haveKey = 0;
        sqliteVdbeAddOp(v, OP_MoveTo, base+idx, 0);
      }
      sqliteExprIfFalse(pParse, aExpr[j].p, cont);
      aExpr[j].p = 0;
    }







|







 







>







 







>







 







<
|







9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
..
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
...
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
...
749
750
751
752
753
754
755

756
757
758
759
760
761
762
763
**    May you share freely, never taking more than you give.
**
*************************************************************************
** This module contains C code that generates VDBE code used to process
** the WHERE clause of SQL statements.  Also found here are subroutines
** to generate VDBE code to evaluate expressions.
**
** $Id: where.c,v 1.40 2002/04/02 13:26:11 drh Exp $
*/
#include "sqliteInt.h"

/*
** The query generator uses an array of instances of this structure to
** help it analyze the subexpressions of the WHERE clause.  Each WHERE
** clause subexpression is separated from the others by an AND operator.
................................................................................
  int indexable;          /* True if this subexprssion is usable by an index */
  int idxLeft;            /* p->pLeft is a column in this table number. -1 if
                          ** p->pLeft is not the column of any table */
  int idxRight;           /* p->pRight is a column in this table number. -1 if
                          ** p->pRight is not the column of any table */
  unsigned prereqLeft;    /* Tables referenced by p->pLeft */
  unsigned prereqRight;   /* Tables referenced by p->pRight */
  unsigned prereqAll;     /* Tables referenced by this expression in any way */
};

/*
** Determine the number of elements in an array.
*/
#define ARRAYSIZE(X)  (sizeof(X)/sizeof(X[0]))

................................................................................
** "base" is the cursor number (the value of the iTable field) that
** corresponds to the first entry in the table list.
*/
static void exprAnalyze(int base, ExprInfo *pInfo){
  Expr *pExpr = pInfo->p;
  pInfo->prereqLeft = exprTableUsage(base, pExpr->pLeft);
  pInfo->prereqRight = exprTableUsage(base, pExpr->pRight);
  pInfo->prereqAll = exprTableUsage(base, pExpr);
  pInfo->indexable = 0;
  pInfo->idxLeft = -1;
  pInfo->idxRight = -1;
  if( allowedOp(pExpr->op) && (pInfo->prereqRight & pInfo->prereqLeft)==0 ){
    if( pExpr->pRight->op==TK_COLUMN ){
      pInfo->idxRight = pExpr->pRight->iTable - base;
      pInfo->indexable = 1;
................................................................................
    loopMask |= 1<<idx;

    /* Insert code to test every subexpression that can be completely
    ** computed using the current set of tables.
    */
    for(j=0; j<nExpr; j++){
      if( aExpr[j].p==0 ) continue;

      if( (aExpr[j].prereqAll & loopMask)!=aExpr[j].prereqAll ) continue;
      if( haveKey ){
        haveKey = 0;
        sqliteVdbeAddOp(v, OP_MoveTo, base+idx, 0);
      }
      sqliteExprIfFalse(pParse, aExpr[j].p, cont);
      aExpr[j].p = 0;
    }

Changes to test/select2.test.

7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
...
123
124
125
126
127
128
129




130



























131
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is testing the SELECT statement.
#
# $Id: select2.test,v 1.17 2002/04/02 01:58:58 drh Exp $

set testdir [file dirname $argv0]
source $testdir/tester.tcl

# Create a table with some data
#
execsql {CREATE TABLE tbl1(f1 int, f2 int)}
................................................................................
    INSERT INTO aa VALUES(1);
    INSERT INTO aa VALUES(3);
    INSERT INTO bb VALUES(2);
    INSERT INTO bb VALUES(4);
    SELECT * FROM aa, bb WHERE max(a,b)>2;
  }
} {1 4 3 2 3 4}
































finish_test







|







 







>
>
>
>
|
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>

7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
...
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is testing the SELECT statement.
#
# $Id: select2.test,v 1.18 2002/04/02 13:26:11 drh Exp $

set testdir [file dirname $argv0]
source $testdir/tester.tcl

# Create a table with some data
#
execsql {CREATE TABLE tbl1(f1 int, f2 int)}
................................................................................
    INSERT INTO aa VALUES(1);
    INSERT INTO aa VALUES(3);
    INSERT INTO bb VALUES(2);
    INSERT INTO bb VALUES(4);
    SELECT * FROM aa, bb WHERE max(a,b)>2;
  }
} {1 4 3 2 3 4}
do_test select2-4.2 {
  execsql {
    INSERT INTO bb VALUES(0);
    SELECT * FROM aa, bb WHERE b;
  }
} {1 2 1 4 3 2 3 4}
do_test select2-4.3 {
  execsql {
    SELECT * FROM aa, bb WHERE NOT b;
  }
} {1 0 3 0}
do_test select2-4.4 {
  execsql {
    SELECT * FROM aa, bb WHERE min(a,b);
  }
} {1 2 1 4 3 2 3 4}
do_test select2-4.5 {
  execsql {
    SELECT * FROM aa, bb WHERE NOT min(a,b);
  }
} {1 0 3 0}
do_test select2-4.6 {
  execsql {
    SELECT * FROM aa, bb WHERE CASE WHEN a=b-1 THEN 1 END;
  }
} {1 2 3 4}
do_test select2-4.7 {
  execsql {
    SELECT * FROM aa, bb WHERE CASE WHEN a=b-1 THEN 0 ELSE 1 END;
  }
} {1 4 1 0 3 2 3 0}

finish_test

Changes to www/changes.tcl.

20
21
22
23
24
25
26





27
28
29
30
31
32
33
34
35
36
37
}


proc chng {date desc} {
  puts "<DT><B>$date</B></DT>"
  puts "<DD><P><UL>$desc</UL></P></DD>"
}






chng {2002 Apr 01 (2.4.5)} {
<li>Bug fix: Correctly handle functions that appear in the WHERE clause
    of a SELECT.</li>
<li>When the PRAGMA vdbe_trace=ON is set, correctly print the P3 operand
    value when it is a pointer to a structure rather than a pointer to
    a string.</li>
<li>When inserting an explicit NULL into an INTEGER PRIMARY KEY, convert
    the NULL value into a unique key automatically.</li>
}








>
>
>
>
>



|







20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
}


proc chng {date desc} {
  puts "<DT><B>$date</B></DT>"
  puts "<DD><P><UL>$desc</UL></P></DD>"
}

chng {2002 Apr 02 (2.4.6)} {
<li>Bug fix: Correctly handle terms in the WHERE clause of a join that
    do not contain a comparison operator.</li>
}

chng {2002 Apr 01 (2.4.5)} {
<li>Bug fix: Correctly handle functions that appear in the WHERE clause
    of a join.</li>
<li>When the PRAGMA vdbe_trace=ON is set, correctly print the P3 operand
    value when it is a pointer to a structure rather than a pointer to
    a string.</li>
<li>When inserting an explicit NULL into an INTEGER PRIMARY KEY, convert
    the NULL value into a unique key automatically.</li>
}