/ Check-in [358dd82d]
Login

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

Overview
Comment:Fix an additional problem with the IS NULL optimization on LEFT JOINs. Ticket #2189. See also ticket #2177. (CVS 3602)
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1:358dd82d3a921228155e2cf9e22aedd2b651ca9c
User & Date: drh 2007-01-25 16:56:07
Context
2007-01-26
00:51
Work around issues with certain C++ compilers by adding a typedef to sqlite3.h. Ticket #2191. (CVS 3603) check-in: 31e8cf7f user: drh tags: trunk
2007-01-25
16:56
Fix an additional problem with the IS NULL optimization on LEFT JOINs. Ticket #2189. See also ticket #2177. (CVS 3602) check-in: 358dd82d user: drh tags: trunk
2007-01-24
03:46
Do not run the fts2i.test unless the FTS2 module is available. (CVS 3601) check-in: 310f6858 user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/where.c.

12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
...
156
157
158
159
160
161
162
163








164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
....
1482
1483
1484
1485
1486
1487
1488
1489
1490
1491
1492
1493
1494
1495
1496
....
1647
1648
1649
1650
1651
1652
1653
1654
1655
1656
1657
1658
1659
1660
1661
** This module contains C code that generates VDBE code used to process
** the WHERE clause of SQL statements.  This module is reponsible for
** generating the code that loops through a table looking for applicable
** rows.  Indices are selected and used to speed the search when doing
** so is applicable.  Because this module is responsible for selecting
** indices, you might also think of this module as the "query optimizer".
**
** $Id: where.c,v 1.235 2007/01/19 01:06:03 drh Exp $
*/
#include "sqliteInt.h"

/*
** The number of bits in a Bitmask.  "BMS" means "BitMask Size".
*/
#define BMS  (sizeof(Bitmask)*8)
................................................................................
#define WO_LE     (WO_EQ<<(TK_LE-TK_EQ))
#define WO_GT     (WO_EQ<<(TK_GT-TK_EQ))
#define WO_GE     (WO_EQ<<(TK_GE-TK_EQ))
#define WO_MATCH  64
#define WO_ISNULL 128

/*
** Value for flags returned by bestIndex()








*/
#define WHERE_ROWID_EQ       0x0001   /* rowid=EXPR or rowid IN (...) */
#define WHERE_ROWID_RANGE    0x0002   /* rowid<EXPR and/or rowid>EXPR */
#define WHERE_COLUMN_EQ      0x0010   /* x=EXPR or x IN (...) */
#define WHERE_COLUMN_RANGE   0x0020   /* x<EXPR and/or x>EXPR */
#define WHERE_COLUMN_IN      0x0040   /* x IN (...) */
#define WHERE_TOP_LIMIT      0x0100   /* x<EXPR or x<=EXPR constraint */
#define WHERE_BTM_LIMIT      0x0200   /* x>EXPR or x>=EXPR constraint */
#define WHERE_IDX_ONLY       0x0800   /* Use index only - omit table */
#define WHERE_ORDERBY        0x1000   /* Output will appear in correct order */
#define WHERE_REVERSE        0x2000   /* Scan in reverse order */
#define WHERE_UNIQUE         0x4000   /* Selects no more than one row */
#define WHERE_VIRTUALTABLE   0x8000   /* Use virtual-table processing */

/*
** Initialize a preallocated WhereClause structure.
*/
static void whereClauseInit(WhereClause *pWC, Parse *pParse){
  pWC->pParse = pParse;
  pWC->nTerm = 0;
................................................................................
  }

  /* Report the best result
  */
  *ppIndex = bestIdx;
  TRACE(("best index is %s, cost=%.9g, flags=%x, nEq=%d\n",
        bestIdx ? bestIdx->zName : "(none)", lowestCost, bestFlags, bestNEq));
  *pFlags = bestFlags;
  *pnEq = bestNEq;
  return lowestCost;
}


/*
** Disable a term in the WHERE clause.  Except, do not disable the term
................................................................................
  }

  /* Evaluate the equality constraints
  */
  assert( pIdx->nColumn>=nEq );
  for(j=0; j<nEq; j++){
    int k = pIdx->aiColumn[j];
    pTerm = findTerm(pWC, iCur, k, notReady, WO_EQ|WO_IN|WO_ISNULL, pIdx);
    if( pTerm==0 ) break;
    assert( (pTerm->flags & TERM_CODED)==0 );
    codeEqualityTerm(pParse, pTerm, brk, pLevel);
    if( (pTerm->eOperator & WO_ISNULL)==0 ){
      sqlite3VdbeAddOp(v, OP_IsNull, termsInMem ? -1 : -(j+1), brk);
    }
    if( termsInMem ){







|







 







|
>
>
>
>
>
>
>
>

|
|
|
|
|
|
|
|
|
|
|
|







 







|







 







|







12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
...
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
....
1490
1491
1492
1493
1494
1495
1496
1497
1498
1499
1500
1501
1502
1503
1504
....
1655
1656
1657
1658
1659
1660
1661
1662
1663
1664
1665
1666
1667
1668
1669
** This module contains C code that generates VDBE code used to process
** the WHERE clause of SQL statements.  This module is reponsible for
** generating the code that loops through a table looking for applicable
** rows.  Indices are selected and used to speed the search when doing
** so is applicable.  Because this module is responsible for selecting
** indices, you might also think of this module as the "query optimizer".
**
** $Id: where.c,v 1.236 2007/01/25 16:56:07 drh Exp $
*/
#include "sqliteInt.h"

/*
** The number of bits in a Bitmask.  "BMS" means "BitMask Size".
*/
#define BMS  (sizeof(Bitmask)*8)
................................................................................
#define WO_LE     (WO_EQ<<(TK_LE-TK_EQ))
#define WO_GT     (WO_EQ<<(TK_GT-TK_EQ))
#define WO_GE     (WO_EQ<<(TK_GE-TK_EQ))
#define WO_MATCH  64
#define WO_ISNULL 128

/*
** Value for flags returned by bestIndex().  
**
** The least significant byte is reserved as a mask for WO_ values above.
** The WhereLevel.flags field is usually set to WO_IN|WO_EQ|WO_ISNULL.
** But if the table is the right table of a left join, WhereLevel.flags
** is set to WO_IN|WO_EQ.  The WhereLevel.flags field can then be used as
** the "op" parameter to findTerm when we are resolving equality constraints.
** ISNULL constraints will then not be used on the right table of a left
** join.  Tickets #2177 and #2189.
*/
#define WHERE_ROWID_EQ     0x000100   /* rowid=EXPR or rowid IN (...) */
#define WHERE_ROWID_RANGE  0x000200   /* rowid<EXPR and/or rowid>EXPR */
#define WHERE_COLUMN_EQ    0x001000   /* x=EXPR or x IN (...) */
#define WHERE_COLUMN_RANGE 0x002000   /* x<EXPR and/or x>EXPR */
#define WHERE_COLUMN_IN    0x004000   /* x IN (...) */
#define WHERE_TOP_LIMIT    0x010000   /* x<EXPR or x<=EXPR constraint */
#define WHERE_BTM_LIMIT    0x020000   /* x>EXPR or x>=EXPR constraint */
#define WHERE_IDX_ONLY     0x080000   /* Use index only - omit table */
#define WHERE_ORDERBY      0x100000   /* Output will appear in correct order */
#define WHERE_REVERSE      0x200000   /* Scan in reverse order */
#define WHERE_UNIQUE       0x400000   /* Selects no more than one row */
#define WHERE_VIRTUALTABLE 0x800000   /* Use virtual-table processing */

/*
** Initialize a preallocated WhereClause structure.
*/
static void whereClauseInit(WhereClause *pWC, Parse *pParse){
  pWC->pParse = pParse;
  pWC->nTerm = 0;
................................................................................
  }

  /* Report the best result
  */
  *ppIndex = bestIdx;
  TRACE(("best index is %s, cost=%.9g, flags=%x, nEq=%d\n",
        bestIdx ? bestIdx->zName : "(none)", lowestCost, bestFlags, bestNEq));
  *pFlags = bestFlags | eqTermMask;
  *pnEq = bestNEq;
  return lowestCost;
}


/*
** Disable a term in the WHERE clause.  Except, do not disable the term
................................................................................
  }

  /* Evaluate the equality constraints
  */
  assert( pIdx->nColumn>=nEq );
  for(j=0; j<nEq; j++){
    int k = pIdx->aiColumn[j];
    pTerm = findTerm(pWC, iCur, k, notReady, pLevel->flags, pIdx);
    if( pTerm==0 ) break;
    assert( (pTerm->flags & TERM_CODED)==0 );
    codeEqualityTerm(pParse, pTerm, brk, pLevel);
    if( (pTerm->eOperator & WO_ISNULL)==0 ){
      sqlite3VdbeAddOp(v, OP_IsNull, termsInMem ? -1 : -(j+1), brk);
    }
    if( termsInMem ){

Changes to test/where4.test.

11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
...
134
135
136
137
138
139
140
141




































142
143
144
# This file implements regression tests for SQLite library.  The
# focus of this file is testing the use of indices in WHERE clauses.
# This file was created when support for optimizing IS NULL phrases
# was added.  And so the principle purpose of this file is to test
# that IS NULL phrases are correctly optimized.  But you can never
# have too many tests, so some other tests are thrown in as well.
#
# $Id: where4.test,v 1.1 2007/01/19 01:06:03 drh Exp $

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

# Build some test data
#
do_test where4-1.0 {
................................................................................
  }
} {2 2 {} 3 {} {}}
do_test where4-3.2 {
  execsql {
    SELECT * FROM t2 LEFT JOIN t3 ON a=x WHERE y IS NULL;
  }
} {2 2 {} 3 {} {}}





































integrity_check {where4-99.0}

finish_test







|







 








>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>



11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
...
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
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
# This file implements regression tests for SQLite library.  The
# focus of this file is testing the use of indices in WHERE clauses.
# This file was created when support for optimizing IS NULL phrases
# was added.  And so the principle purpose of this file is to test
# that IS NULL phrases are correctly optimized.  But you can never
# have too many tests, so some other tests are thrown in as well.
#
# $Id: where4.test,v 1.2 2007/01/25 16:56:08 drh Exp $

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

# Build some test data
#
do_test where4-1.0 {
................................................................................
  }
} {2 2 {} 3 {} {}}
do_test where4-3.2 {
  execsql {
    SELECT * FROM t2 LEFT JOIN t3 ON a=x WHERE y IS NULL;
  }
} {2 2 {} 3 {} {}}

# Ticket #2189.  Probably the same bug as #2177.
#
do_test where4-4.1 {
  execsql {
    CREATE TABLE test(col1 TEXT PRIMARY KEY);
    INSERT INTO test(col1) values('a');
    INSERT INTO test(col1) values('b');
    INSERT INTO test(col1) values('c');
    CREATE TABLE test2(col1 TEXT PRIMARY KEY);
    INSERT INTO test2(col1) values('a');
    INSERT INTO test2(col1) values('b');
    INSERT INTO test2(col1) values('c');
    SELECT * FROM test t1 LEFT OUTER JOIN test2 t2 ON t1.col1 = t2.col1
      WHERE +t2.col1 IS NULL;
  }
} {}
do_test where4-4.2 {
  execsql {
    SELECT * FROM test t1 LEFT OUTER JOIN test2 t2 ON t1.col1 = t2.col1
      WHERE t2.col1 IS NULL;
  }
} {}
do_test where4-4.3 {
  execsql {
    SELECT * FROM test t1 LEFT OUTER JOIN test2 t2 ON t1.col1 = t2.col1
      WHERE +t1.col1 IS NULL;
  }
} {}
do_test where4-4.4 {
  execsql {
    SELECT * FROM test t1 LEFT OUTER JOIN test2 t2 ON t1.col1 = t2.col1
      WHERE t1.col1 IS NULL;
  }
} {}
    

integrity_check {where4-99.0}

finish_test