/ Check-in [2a710e18]
Login

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

Overview
Comment:Added tests for the new IN operator optimizer and fixed a bug that the new tests found. This completes the implementation of enhancement #63. (CVS 612)
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1:2a710e18176c486525f0abb06644a511a2cd1d7a
User & Date: drh 2002-06-09 01:55:20
Context
2002-06-09
10:14
Fix the spelling of sqliteRegisterBuiltinFunctions(). (CVS 613) check-in: 74d297d9 user: drh tags: trunk
01:55
Added tests for the new IN operator optimizer and fixed a bug that the new tests found. This completes the implementation of enhancement #63. (CVS 612) check-in: 2a710e18 user: drh tags: trunk
01:16
Fix for ticket #65: If an integer value is too big to be represented as a 32-bit integer, then treat it as a string. (CVS 611) check-in: ad962479 user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Show Whitespace Changes Patch

Changes to src/where.c.

9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
...
334
335
336
337
338
339
340
341

342
343
344
345
346
347
348
349
350
351
352
353
354



355
356
357
358
359
360
361
...
412
413
414
415
416
417
418

419
420
421
422
423
424
425
**    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.49 2002/06/08 23:25:10 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.
................................................................................
    **
    ** The IN operator as in "<expr> IN (...)" is treated the same as
    ** an equality comparison.
    */
    for(pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext){
      int eqMask = 0;  /* Index columns covered by an x=... constraint */
      int ltMask = 0;  /* Index columns covered by an x<... constraint */
      int gtMask = 0;  /* Index columns covered by an x>... constraing */

      int nEq, m, score;

      if( pIdx->isDropped ) continue;   /* Ignore dropped indices */
      if( pIdx->nColumn>32 ) continue;  /* Ignore indices too many columns */
      for(j=0; j<nExpr; j++){
        if( aExpr[j].idxLeft==idx 
             && (aExpr[j].prereqRight & loopMask)==aExpr[j].prereqRight ){
          int iColumn = aExpr[j].p->pLeft->iColumn;
          int k;
          for(k=0; k<pIdx->nColumn; k++){
            if( pIdx->aiColumn[k]==iColumn ){
              switch( aExpr[j].p->op ){
                case TK_IN:



                case TK_EQ: {
                  eqMask |= 1<<k;
                  break;
                }
                case TK_LE:
                case TK_LT: {
                  ltMask |= 1<<k;
................................................................................
        m = (1<<(nEq+1))-1;
        if( (m & eqMask)!=m ) break;
      }
      score = nEq*4;
      m = 1<<nEq;
      if( m & ltMask ) score++;
      if( m & gtMask ) score+=2;

      if( score>bestScore ){
        pBestIdx = pIdx;
        bestScore = score;
      }
    }
    pWInfo->a[i].pIdx = pBestIdx;
    pWInfo->a[i].score = bestScore;







|







 







|
>












|
>
>
>







 







>







9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
...
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
...
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
**    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.50 2002/06/09 01:55:20 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.
................................................................................
    **
    ** The IN operator as in "<expr> IN (...)" is treated the same as
    ** an equality comparison.
    */
    for(pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext){
      int eqMask = 0;  /* Index columns covered by an x=... constraint */
      int ltMask = 0;  /* Index columns covered by an x<... constraint */
      int gtMask = 0;  /* Index columns covered by an x>... constraint */
      int inMask = 0;  /* Index columns covered by an x IN .. constraint */
      int nEq, m, score;

      if( pIdx->isDropped ) continue;   /* Ignore dropped indices */
      if( pIdx->nColumn>32 ) continue;  /* Ignore indices too many columns */
      for(j=0; j<nExpr; j++){
        if( aExpr[j].idxLeft==idx 
             && (aExpr[j].prereqRight & loopMask)==aExpr[j].prereqRight ){
          int iColumn = aExpr[j].p->pLeft->iColumn;
          int k;
          for(k=0; k<pIdx->nColumn; k++){
            if( pIdx->aiColumn[k]==iColumn ){
              switch( aExpr[j].p->op ){
                case TK_IN: {
                  if( k==0 ) inMask |= 1;
                  break;
                }
                case TK_EQ: {
                  eqMask |= 1<<k;
                  break;
                }
                case TK_LE:
                case TK_LT: {
                  ltMask |= 1<<k;
................................................................................
        m = (1<<(nEq+1))-1;
        if( (m & eqMask)!=m ) break;
      }
      score = nEq*4;
      m = 1<<nEq;
      if( m & ltMask ) score++;
      if( m & gtMask ) score+=2;
      if( score==0 && inMask ) score = 4;
      if( score>bestScore ){
        pBestIdx = pIdx;
        bestScore = score;
      }
    }
    pWInfo->a[i].pIdx = pBestIdx;
    pWInfo->a[i].score = bestScore;

Changes to test/where.test.

7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
...
256
257
258
259
260
261
262
263

264

















































































265
#    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 use of indices in WHERE clases.
#
# $Id: where.test,v 1.6 2002/04/30 19:20:29 drh Exp $

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

# Build some test data
#
do_test where-1.0 {
................................................................................
  }
} {}
do_test where-4.4 {
  execsql {
    SELECT 99 WHERE 1
  }
} {99}




















































































finish_test







|







 








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

7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
...
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
#    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 use of indices in WHERE clases.
#
# $Id: where.test,v 1.7 2002/06/09 01:55:20 drh Exp $

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

# Build some test data
#
do_test where-1.0 {
................................................................................
  }
} {}
do_test where-4.4 {
  execsql {
    SELECT 99 WHERE 1
  }
} {99}

# Verify that IN operators in a WHERE clause are handled correctly.
#
do_test where-5.1 {
  count {
    SELECT * FROM t1 WHERE rowid IN (1,2,3,1234) order by 1;
  }
} {1 0 4 2 1 9 3 1 16 0}
do_test where-5.2 {
  count {
    SELECT * FROM t1 WHERE rowid+0 IN (1,2,3,1234) order by 1;
  }
} {1 0 4 2 1 9 3 1 16 99}
do_test where-5.3 {
  count {
    SELECT * FROM t1 WHERE w IN (-1,1,2,3) order by 1;
  }
} {1 0 4 2 1 9 3 1 16 10}
do_test where-5.4 {
  count {
    SELECT * FROM t1 WHERE w+0 IN (-1,1,2,3) order by 1;
  }
} {1 0 4 2 1 9 3 1 16 99}
do_test where-5.5 {
  count {
    SELECT * FROM t1 WHERE rowid IN 
       (select rowid from t1 where rowid IN (-1,2,4))
    ORDER BY 1;
  }
} {2 1 9 4 2 25 1}
do_test where-5.6 {
  count {
    SELECT * FROM t1 WHERE rowid+0 IN 
       (select rowid from t1 where rowid IN (-1,2,4))
    ORDER BY 1;
  }
} {2 1 9 4 2 25 99}
do_test where-5.7 {
  count {
    SELECT * FROM t1 WHERE w IN 
       (select rowid from t1 where rowid IN (-1,2,4))
    ORDER BY 1;
  }
} {2 1 9 4 2 25 7}
do_test where-5.8 {
  count {
    SELECT * FROM t1 WHERE w+0 IN 
       (select rowid from t1 where rowid IN (-1,2,4))
    ORDER BY 1;
  }
} {2 1 9 4 2 25 99}
do_test where-5.9 {
  count {
    SELECT * FROM t1 WHERE x IN (1,7) ORDER BY 1;
  }
} {2 1 9 3 1 16 6}
do_test where-5.10 {
  count {
    SELECT * FROM t1 WHERE x+0 IN (1,7) ORDER BY 1;
  }
} {2 1 9 3 1 16 99}
do_test where-5.11 {
  count {
    SELECT * FROM t1 WHERE y IN (6400,8100) ORDER BY 1;
  }
} {79 6 6400 89 6 8100 99}
do_test where-5.12 {
  count {
    SELECT * FROM t1 WHERE x=6 AND y IN (6400,8100) ORDER BY 1;
  }
} {79 6 6400 89 6 8100 74}
do_test where-5.13 {
  count {
    SELECT * FROM t1 WHERE x IN (1,7) AND y NOT IN (6400,8100) ORDER BY 1;
  }
} {2 1 9 3 1 16 6}
do_test where-5.14 {
  count {
    SELECT * FROM t1 WHERE x IN (1,7) AND y IN (9,10) ORDER BY 1;
  }
} {2 1 9 6}



finish_test