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 |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA1: |
358dd82d3a921228155e2cf9e22aedd2 |
User & Date: | drh 2007-01-25 16:56:07.000 |
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: 31e8cf7f30 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: 358dd82d3a 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: 310f685851 user: drh tags: trunk) | |
Changes
Changes to src/where.c.
︙ | ︙ | |||
12 13 14 15 16 17 18 | ** 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". ** | | | 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 | ** 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) |
︙ | ︙ | |||
156 157 158 159 160 161 162 | #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 /* | | > > > > > > > > | | | | | | | | | | | | | 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 | #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; |
︙ | ︙ | |||
1482 1483 1484 1485 1486 1487 1488 | } /* 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)); | | | 1490 1491 1492 1493 1494 1495 1496 1497 1498 1499 1500 1501 1502 1503 1504 | } /* 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 |
︙ | ︙ | |||
1647 1648 1649 1650 1651 1652 1653 | } /* Evaluate the equality constraints */ assert( pIdx->nColumn>=nEq ); for(j=0; j<nEq; j++){ int k = pIdx->aiColumn[j]; | | | 1655 1656 1657 1658 1659 1660 1661 1662 1663 1664 1665 1666 1667 1668 1669 | } /* 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 | # 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. # | | | 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 | # 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 { |
︙ | ︙ | |||
134 135 136 137 138 139 140 141 142 143 144 | } } {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 | > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 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 | } } {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 |