Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Do not transform a WHERE clause of the form "a = ? OR a = ?" to "a IN (?, ?)" if "a" is a column of a virtual table. Ticket #3871. (CVS 6671) |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA1: |
fad88e71cf195e703f7b56b13f0c1818 |
User & Date: | danielk1977 2009-05-22 15:43:27.000 |
References
2013-02-08
| ||
16:04 | Allow the "a=?1 OR a=?2" to "a IN (?1,?2)" transformation to work on virtual tables again. This was formerly restricted because virtual tables could not optimize IN terms. (See check-in [fad88e71cf195e].) But IN terms are now used by virtual tables (as of check-in [3d65c70343]) so the restriction can now be removed. (check-in: a917c1f092 user: drh tags: IN-with-ORDERBY) | |
Context
2009-05-24
| ||
11:07 | Correct and clarify the documentation on the third parameter to sqlite3_create_function(). (CVS 6672) (check-in: 6b7929ed36 user: drh tags: trunk) | |
2009-05-22
| ||
15:43 | Do not transform a WHERE clause of the form "a = ? OR a = ?" to "a IN (?, ?)" if "a" is a column of a virtual table. Ticket #3871. (CVS 6671) (check-in: fad88e71cf user: danielk1977 tags: trunk) | |
11:12 | Add an assert() to pcache1.c to double-check that page cache buffer memory is never allocated if pcache1 is not enabled. Ticket #3872 (CVS 6670) (check-in: 93369d91ab 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 responsible 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 responsible 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.397 2009/05/22 15:43:27 danielk1977 Exp $ */ #include "sqliteInt.h" /* ** Trace output macros */ #if defined(SQLITE_TEST) || defined(SQLITE_DEBUG) |
︙ | ︙ | |||
123 124 125 126 127 128 129 130 131 132 133 134 135 136 | /* ** An instance of the following structure holds all information about a ** WHERE clause. Mostly this is a container for one or more WhereTerms. */ struct WhereClause { Parse *pParse; /* The parser context */ WhereMaskSet *pMaskSet; /* Mapping of table cursor numbers to bitmasks */ u8 op; /* Split operator. TK_AND or TK_OR */ int nTerm; /* Number of terms */ int nSlot; /* Number of entries in a[] */ WhereTerm *a; /* Each a[] describes a term of the WHERE cluase */ WhereTerm aStatic[4]; /* Initial static space for a[] */ }; | > | 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 | /* ** An instance of the following structure holds all information about a ** WHERE clause. Mostly this is a container for one or more WhereTerms. */ struct WhereClause { Parse *pParse; /* The parser context */ WhereMaskSet *pMaskSet; /* Mapping of table cursor numbers to bitmasks */ Bitmask vmask; /* Bitmask identifying virtual table cursors */ u8 op; /* Split operator. TK_AND or TK_OR */ int nTerm; /* Number of terms */ int nSlot; /* Number of entries in a[] */ WhereTerm *a; /* Each a[] describes a term of the WHERE cluase */ WhereTerm aStatic[4]; /* Initial static space for a[] */ }; |
︙ | ︙ | |||
250 251 252 253 254 255 256 257 258 259 260 261 262 263 | WhereMaskSet *pMaskSet /* Mapping from table cursor numbers to bitmasks */ ){ pWC->pParse = pParse; pWC->pMaskSet = pMaskSet; pWC->nTerm = 0; pWC->nSlot = ArraySize(pWC->aStatic); pWC->a = pWC->aStatic; } /* Forward reference */ static void whereClauseClear(WhereClause*); /* ** Deallocate all memory associated with a WhereOrInfo object. | > | 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 | WhereMaskSet *pMaskSet /* Mapping from table cursor numbers to bitmasks */ ){ pWC->pParse = pParse; pWC->pMaskSet = pMaskSet; pWC->nTerm = 0; pWC->nSlot = ArraySize(pWC->aStatic); pWC->a = pWC->aStatic; pWC->vmask = 0; } /* Forward reference */ static void whereClauseClear(WhereClause*); /* ** Deallocate all memory associated with a WhereOrInfo object. |
︙ | ︙ | |||
825 826 827 828 829 830 831 | exprAnalyzeAll(pSrc, pOrWc); if( db->mallocFailed ) return; assert( pOrWc->nTerm>=2 ); /* ** Compute the set of tables that might satisfy cases 1 or 2. */ | | > | 827 828 829 830 831 832 833 834 835 836 837 838 839 840 841 842 | exprAnalyzeAll(pSrc, pOrWc); if( db->mallocFailed ) return; assert( pOrWc->nTerm>=2 ); /* ** Compute the set of tables that might satisfy cases 1 or 2. */ indexable = ~(Bitmask)0; chngToIN = ~(pWC->vmask); for(i=pOrWc->nTerm-1, pOrTerm=pOrWc->a; i>=0 && indexable; i--, pOrTerm++){ if( (pOrTerm->eOperator & WO_SINGLE)==0 ){ WhereAndInfo *pAndInfo; assert( pOrTerm->eOperator==0 ); assert( (pOrTerm->wtFlags & (TERM_ANDINFO|TERM_ORINFO))==0 ); chngToIN = 0; pAndInfo = sqlite3DbMallocRaw(db, sizeof(*pAndInfo)); |
︙ | ︙ | |||
3162 3163 3164 3165 3166 3167 3168 3169 3170 3171 3172 3173 3174 3175 3176 3177 3178 | ** the case that if X is the bitmask for the N-th FROM clause term then ** the bitmask for all FROM clause terms to the left of the N-th term ** is (X-1). An expression from the ON clause of a LEFT JOIN can use ** its Expr.iRightJoinTable value to find the bitmask of the right table ** of the join. Subtracting one from the right table bitmask gives a ** bitmask for all tables to the left of the join. Knowing the bitmask ** for all tables to the left of a left join is important. Ticket #3015. */ for(i=0; i<pTabList->nSrc; i++){ createMask(pMaskSet, pTabList->a[i].iCursor); } #ifndef NDEBUG { Bitmask toTheLeft = 0; for(i=0; i<pTabList->nSrc; i++){ Bitmask m = getMask(pMaskSet, pTabList->a[i].iCursor); assert( (m-1)==toTheLeft ); | > > > > > > > > > | 3165 3166 3167 3168 3169 3170 3171 3172 3173 3174 3175 3176 3177 3178 3179 3180 3181 3182 3183 3184 3185 3186 3187 3188 3189 3190 | ** the case that if X is the bitmask for the N-th FROM clause term then ** the bitmask for all FROM clause terms to the left of the N-th term ** is (X-1). An expression from the ON clause of a LEFT JOIN can use ** its Expr.iRightJoinTable value to find the bitmask of the right table ** of the join. Subtracting one from the right table bitmask gives a ** bitmask for all tables to the left of the join. Knowing the bitmask ** for all tables to the left of a left join is important. Ticket #3015. ** ** Configure the WhereClause.vmask variable so that bits that correspond ** to virtual table cursors are set. This is used to selectively disable ** the OR-to-IN transformation in exprAnalyzeOrTerm(). It is not helpful ** with virtual tables. */ assert( pWC->vmask==0 && pMaskSet->n==0 ); for(i=0; i<pTabList->nSrc; i++){ createMask(pMaskSet, pTabList->a[i].iCursor); if( pTabList->a[i].pTab && IsVirtual(pTabList->a[i].pTab) ){ pWC->vmask |= ((Bitmask)1 << i); } } #ifndef NDEBUG { Bitmask toTheLeft = 0; for(i=0; i<pTabList->nSrc; i++){ Bitmask m = getMask(pMaskSet, pTabList->a[i].iCursor); assert( (m-1)==toTheLeft ); |
︙ | ︙ |
Added test/tkt3871.test.
> > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 | set testdir [file dirname $argv0] source $testdir/tester.tcl ifcapable !vtab { finish_test return } register_echo_module [sqlite3_connection_pointer db] do_test tkt3871-1.1 { execsql { BEGIN; CREATE TABLE t1(a PRIMARY KEY, b UNIQUE); } for {set i 0} {$i < 500} {incr i} { execsql { INSERT INTO t1 VALUES($i, $i*$i) } } execsql COMMIT execsql { CREATE VIRTUAL TABLE e USING echo(t1); SELECT count(*) FROM e; } } {500} do_test tkt3871-1.2 { execsql { SELECT * FROM e WHERE a = 1 OR a = 2 } } {1 1 2 4} do_test tkt3871-1.3 { set echo_module "" execsql { SELECT * FROM e WHERE a = 1 OR a = 2 } set echo_module } [list \ xFilter {SELECT rowid, * FROM 't1' WHERE a = ?} 1 \ xFilter {SELECT rowid, * FROM 't1' WHERE a = ?} 2 \ ] do_test tkt3871-1.4 { execsql { SELECT * FROM e WHERE a = 1 OR a = 2 OR b = 9 } } {1 1 2 4 3 9} do_test tkt3871-1.5 { set echo_module "" execsql { SELECT * FROM e WHERE a = 1 OR a = 2 OR b = 9 } set echo_module } [list \ xFilter {SELECT rowid, * FROM 't1' WHERE a = ?} 1 \ xFilter {SELECT rowid, * FROM 't1' WHERE a = ?} 2 \ xFilter {SELECT rowid, * FROM 't1' WHERE b = ?} 9 ] finish_test |