/ 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 Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/where.c.

     9      9   **    May you share freely, never taking more than you give.
    10     10   **
    11     11   *************************************************************************
    12     12   ** This module contains C code that generates VDBE code used to process
    13     13   ** the WHERE clause of SQL statements.  Also found here are subroutines
    14     14   ** to generate VDBE code to evaluate expressions.
    15     15   **
    16         -** $Id: where.c,v 1.49 2002/06/08 23:25:10 drh Exp $
           16  +** $Id: where.c,v 1.50 2002/06/09 01:55:20 drh Exp $
    17     17   */
    18     18   #include "sqliteInt.h"
    19     19   
    20     20   /*
    21     21   ** The query generator uses an array of instances of this structure to
    22     22   ** help it analyze the subexpressions of the WHERE clause.  Each WHERE
    23     23   ** clause subexpression is separated from the others by an AND operator.
................................................................................
   334    334       **
   335    335       ** The IN operator as in "<expr> IN (...)" is treated the same as
   336    336       ** an equality comparison.
   337    337       */
   338    338       for(pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext){
   339    339         int eqMask = 0;  /* Index columns covered by an x=... constraint */
   340    340         int ltMask = 0;  /* Index columns covered by an x<... constraint */
   341         -      int gtMask = 0;  /* Index columns covered by an x>... constraing */
          341  +      int gtMask = 0;  /* Index columns covered by an x>... constraint */
          342  +      int inMask = 0;  /* Index columns covered by an x IN .. constraint */
   342    343         int nEq, m, score;
   343    344   
   344    345         if( pIdx->isDropped ) continue;   /* Ignore dropped indices */
   345    346         if( pIdx->nColumn>32 ) continue;  /* Ignore indices too many columns */
   346    347         for(j=0; j<nExpr; j++){
   347    348           if( aExpr[j].idxLeft==idx 
   348    349                && (aExpr[j].prereqRight & loopMask)==aExpr[j].prereqRight ){
   349    350             int iColumn = aExpr[j].p->pLeft->iColumn;
   350    351             int k;
   351    352             for(k=0; k<pIdx->nColumn; k++){
   352    353               if( pIdx->aiColumn[k]==iColumn ){
   353    354                 switch( aExpr[j].p->op ){
   354         -                case TK_IN:
          355  +                case TK_IN: {
          356  +                  if( k==0 ) inMask |= 1;
          357  +                  break;
          358  +                }
   355    359                   case TK_EQ: {
   356    360                     eqMask |= 1<<k;
   357    361                     break;
   358    362                   }
   359    363                   case TK_LE:
   360    364                   case TK_LT: {
   361    365                     ltMask |= 1<<k;
................................................................................
   412    416           m = (1<<(nEq+1))-1;
   413    417           if( (m & eqMask)!=m ) break;
   414    418         }
   415    419         score = nEq*4;
   416    420         m = 1<<nEq;
   417    421         if( m & ltMask ) score++;
   418    422         if( m & gtMask ) score+=2;
          423  +      if( score==0 && inMask ) score = 4;
   419    424         if( score>bestScore ){
   420    425           pBestIdx = pIdx;
   421    426           bestScore = score;
   422    427         }
   423    428       }
   424    429       pWInfo->a[i].pIdx = pBestIdx;
   425    430       pWInfo->a[i].score = bestScore;

Changes to test/where.test.

     7      7   #    May you find forgiveness for yourself and forgive others.
     8      8   #    May you share freely, never taking more than you give.
     9      9   #
    10     10   #***********************************************************************
    11     11   # This file implements regression tests for SQLite library.  The
    12     12   # focus of this file is testing the use of indices in WHERE clases.
    13     13   #
    14         -# $Id: where.test,v 1.6 2002/04/30 19:20:29 drh Exp $
           14  +# $Id: where.test,v 1.7 2002/06/09 01:55:20 drh Exp $
    15     15   
    16     16   set testdir [file dirname $argv0]
    17     17   source $testdir/tester.tcl
    18     18   
    19     19   # Build some test data
    20     20   #
    21     21   do_test where-1.0 {
................................................................................
   256    256     }
   257    257   } {}
   258    258   do_test where-4.4 {
   259    259     execsql {
   260    260       SELECT 99 WHERE 1
   261    261     }
   262    262   } {99}
          263  +
          264  +# Verify that IN operators in a WHERE clause are handled correctly.
          265  +#
          266  +do_test where-5.1 {
          267  +  count {
          268  +    SELECT * FROM t1 WHERE rowid IN (1,2,3,1234) order by 1;
          269  +  }
          270  +} {1 0 4 2 1 9 3 1 16 0}
          271  +do_test where-5.2 {
          272  +  count {
          273  +    SELECT * FROM t1 WHERE rowid+0 IN (1,2,3,1234) order by 1;
          274  +  }
          275  +} {1 0 4 2 1 9 3 1 16 99}
          276  +do_test where-5.3 {
          277  +  count {
          278  +    SELECT * FROM t1 WHERE w IN (-1,1,2,3) order by 1;
          279  +  }
          280  +} {1 0 4 2 1 9 3 1 16 10}
          281  +do_test where-5.4 {
          282  +  count {
          283  +    SELECT * FROM t1 WHERE w+0 IN (-1,1,2,3) order by 1;
          284  +  }
          285  +} {1 0 4 2 1 9 3 1 16 99}
          286  +do_test where-5.5 {
          287  +  count {
          288  +    SELECT * FROM t1 WHERE rowid IN 
          289  +       (select rowid from t1 where rowid IN (-1,2,4))
          290  +    ORDER BY 1;
          291  +  }
          292  +} {2 1 9 4 2 25 1}
          293  +do_test where-5.6 {
          294  +  count {
          295  +    SELECT * FROM t1 WHERE rowid+0 IN 
          296  +       (select rowid from t1 where rowid IN (-1,2,4))
          297  +    ORDER BY 1;
          298  +  }
          299  +} {2 1 9 4 2 25 99}
          300  +do_test where-5.7 {
          301  +  count {
          302  +    SELECT * FROM t1 WHERE w IN 
          303  +       (select rowid from t1 where rowid IN (-1,2,4))
          304  +    ORDER BY 1;
          305  +  }
          306  +} {2 1 9 4 2 25 7}
          307  +do_test where-5.8 {
          308  +  count {
          309  +    SELECT * FROM t1 WHERE w+0 IN 
          310  +       (select rowid from t1 where rowid IN (-1,2,4))
          311  +    ORDER BY 1;
          312  +  }
          313  +} {2 1 9 4 2 25 99}
          314  +do_test where-5.9 {
          315  +  count {
          316  +    SELECT * FROM t1 WHERE x IN (1,7) ORDER BY 1;
          317  +  }
          318  +} {2 1 9 3 1 16 6}
          319  +do_test where-5.10 {
          320  +  count {
          321  +    SELECT * FROM t1 WHERE x+0 IN (1,7) ORDER BY 1;
          322  +  }
          323  +} {2 1 9 3 1 16 99}
          324  +do_test where-5.11 {
          325  +  count {
          326  +    SELECT * FROM t1 WHERE y IN (6400,8100) ORDER BY 1;
          327  +  }
          328  +} {79 6 6400 89 6 8100 99}
          329  +do_test where-5.12 {
          330  +  count {
          331  +    SELECT * FROM t1 WHERE x=6 AND y IN (6400,8100) ORDER BY 1;
          332  +  }
          333  +} {79 6 6400 89 6 8100 74}
          334  +do_test where-5.13 {
          335  +  count {
          336  +    SELECT * FROM t1 WHERE x IN (1,7) AND y NOT IN (6400,8100) ORDER BY 1;
          337  +  }
          338  +} {2 1 9 3 1 16 6}
          339  +do_test where-5.14 {
          340  +  count {
          341  +    SELECT * FROM t1 WHERE x IN (1,7) AND y IN (9,10) ORDER BY 1;
          342  +  }
          343  +} {2 1 9 6}
          344  +
   263    345   
   264    346   
   265    347   finish_test