/ Check-in [fad88e71]
Login

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 | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: fad88e71cf195e703f7b56b13f0c1818fd0dac84
User & Date: danielk1977 2009-05-22 15:43:27
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: a917c1f0 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: 6b7929ed 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: fad88e71 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: 93369d91 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
...
123
124
125
126
127
128
129

130
131
132
133
134
135
136
...
250
251
252
253
254
255
256

257
258
259
260
261
262
263
...
825
826
827
828
829
830
831
832

833
834
835
836
837
838
839
....
3162
3163
3164
3165
3166
3167
3168





3169

3170
3171



3172
3173
3174
3175
3176
3177
3178
** 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.396 2009/05/06 19:03:14 drh Exp $
*/
#include "sqliteInt.h"

/*
** Trace output macros
*/
#if defined(SQLITE_TEST) || defined(SQLITE_DEBUG)
................................................................................
/*
** 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[] */
};

................................................................................
  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.
................................................................................
  exprAnalyzeAll(pSrc, pOrWc);
  if( db->mallocFailed ) return;
  assert( pOrWc->nTerm>=2 );

  /*
  ** Compute the set of tables that might satisfy cases 1 or 2.
  */
  indexable = chngToIN = ~(Bitmask)0;

  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));
................................................................................
  ** 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 );







|







 







>







 







>







 







|
>







 







>
>
>
>
>

>


>
>
>







12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
...
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
...
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
...
827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842
....
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
** 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)
................................................................................
/*
** 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[] */
};

................................................................................
  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.
................................................................................
  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));
................................................................................
  ** 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