SQLite

Check-in [7c31e257e2]
Login

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

Overview
Comment:Bug fixes in where.c. The where.test test works again. (CVS 1406)
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 7c31e257e2d109bfedf63dc307b422e1abd46d0e
User & Date: drh 2004-05-19 13:13:08.000
Context
2004-05-19
14:56
Change opcode names and comments to better describe the operation of the incrKey flag. OP_MoveTo becomes OP_MoveGe. (CVS 1407) (check-in: 8f249c45cb user: drh tags: trunk)
13:13
Bug fixes in where.c. The where.test test works again. (CVS 1406) (check-in: 7c31e257e2 user: drh tags: trunk)
11:31
Really remove the OP_StrEq opcode this time - appearently I didn't save the file out of the editor before doing the check-in (1397). (CVS 1405) (check-in: 821b0b297c user: drh tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Changes to src/vdbe.c.
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
**
** Various scripts scan this source file in order to generate HTML
** documentation, headers files, or other derived files.  The formatting
** of the code in this file is, therefore, important.  See other comments
** in this file for details.  If in doubt, do not deviate from existing
** commenting and indentation practices when changing or adding code.
**
** $Id: vdbe.c,v 1.302 2004/05/19 11:31:13 drh Exp $
*/
#include "sqliteInt.h"
#include "os.h"
#include <ctype.h>
#include "vdbeInt.h"

/*







|







39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
**
** Various scripts scan this source file in order to generate HTML
** documentation, headers files, or other derived files.  The formatting
** of the code in this file is, therefore, important.  See other comments
** in this file for details.  If in doubt, do not deviate from existing
** commenting and indentation practices when changing or adding code.
**
** $Id: vdbe.c,v 1.303 2004/05/19 13:13:08 drh Exp $
*/
#include "sqliteInt.h"
#include "os.h"
#include <ctype.h>
#include "vdbeInt.h"

/*
2762
2763
2764
2765
2766
2767
2768
2769


2770

2771
2772
2773
2774
2775
2776
2777
2778
2779
2780
2781
2782
2783
2784
2785
2786


2787
2788
2789
2790
2791
2792
2793
2794
** Pop the top of the stack and use its value as a key.  Reposition
** cursor P1 so that it points to an entry with a matching key.  If
** the table contains no record with a matching key, then the cursor
** is left pointing at the first record that is greater than the key.
** If there are no records greater than the key and P2 is not zero,
** then an immediate jump to P2 is made.
**
** If P3 is not NULL, then the cursor is left pointing at the first


** record that is greater than the key of which the key is not a prefix.

** This is the same effect that executing OP_IncrKey on the key value
** before OP_MoveTo used to have.
**
** See also: Found, NotFound, Distinct, MoveLt
*/
/* Opcode: MoveLt P1 P2 *
**
** Pop the top of the stack and use its value as a key.  Reposition
** cursor P1 so that it points to the entry with the largest key that is
** less than the key popped from the stack.
** If there are no records less than than the key and P2
** is not zero then an immediate jump to P2 is made.
**
** If P3 is not NULL, and keys exist in the index of which the stack key
** is a prefix, leave the cursor pointing at the largest of these.
** This is the same effect that executing OP_IncrKey on the key value


** before OP_MoveLt used to have.
**
** See also: MoveTo
*/
case OP_MoveLt:
case OP_MoveTo: {
  int i = pOp->p1;
  Cursor *pC;







|
>
>
|
>
|
<











|
|
|
>
>
|







2762
2763
2764
2765
2766
2767
2768
2769
2770
2771
2772
2773
2774

2775
2776
2777
2778
2779
2780
2781
2782
2783
2784
2785
2786
2787
2788
2789
2790
2791
2792
2793
2794
2795
2796
2797
2798
** Pop the top of the stack and use its value as a key.  Reposition
** cursor P1 so that it points to an entry with a matching key.  If
** the table contains no record with a matching key, then the cursor
** is left pointing at the first record that is greater than the key.
** If there are no records greater than the key and P2 is not zero,
** then an immediate jump to P2 is made.
**
** If P3 is the "+" string (or any other non-NULL string) then the
** index taken from the top of the stack is temporarily increased by
** an epsilon prior to the move.  This causes the P1 cursor to move
** to the first entry which is greater than the index on the top of
** the stack rather than the first entry that is equal to the top of
** the stack.

**
** See also: Found, NotFound, Distinct, MoveLt
*/
/* Opcode: MoveLt P1 P2 *
**
** Pop the top of the stack and use its value as a key.  Reposition
** cursor P1 so that it points to the entry with the largest key that is
** less than the key popped from the stack.
** If there are no records less than than the key and P2
** is not zero then an immediate jump to P2 is made.
**
** If P3 is the "+" string (or any other non-NULL string) then the
** index taken from the top of the stack is temporarily increased by
** an epsilon prior to the move.  This causes the P1 cursor to move
** to the smallest entry that is greater than or equal to the top of
** the stack rather than the largest entry that is less than the top
** of the stack.
**
** See also: MoveTo
*/
case OP_MoveLt:
case OP_MoveTo: {
  int i = pOp->p1;
  Cursor *pC;
3794
3795
3796
3797
3798
3799
3800
3801
3802
3803
3804
3805
3806
3807




3808
3809
3810
3811
3812
3813
3814
3815
3816




3817
3818
3819
3820
3821
3822
3823
3824




3825
3826
3827
3828
3829
3830
3831
#endif
  }else{
    pTos->flags = MEM_Null;
  }
  break;
}

/* Opcode: IdxGT P1 P2 *
**
** Compare the top of the stack against the key on the index entry that
** cursor P1 is currently pointing to.  Ignore the last 4 bytes of the
** index entry.  If the index entry is greater than the top of the stack
** then jump to P2.  Otherwise fall through to the next instruction.
** In either case, the stack is popped once.




*/
/* Opcode: IdxGE P1 P2 *
**
** Compare the top of the stack against the key on the index entry that
** cursor P1 is currently pointing to.  Ignore the last 4 bytes of the
** index entry.  If the index entry is greater than or equal to 
** the top of the stack
** then jump to P2.  Otherwise fall through to the next instruction.
** In either case, the stack is popped once.




*/
/* Opcode: IdxLT P1 P2 *
**
** Compare the top of the stack against the key on the index entry that
** cursor P1 is currently pointing to.  Ignore the last 4 bytes of the
** index entry.  If the index entry is less than the top of the stack
** then jump to P2.  Otherwise fall through to the next instruction.
** In either case, the stack is popped once.




*/
case OP_IdxLT:
case OP_IdxGT:
case OP_IdxGE: {
  int i= pOp->p1;
  BtCursor *pCrsr;
  Cursor *pC;







|


|



>
>
>
>




|




>
>
>
>




|



>
>
>
>







3798
3799
3800
3801
3802
3803
3804
3805
3806
3807
3808
3809
3810
3811
3812
3813
3814
3815
3816
3817
3818
3819
3820
3821
3822
3823
3824
3825
3826
3827
3828
3829
3830
3831
3832
3833
3834
3835
3836
3837
3838
3839
3840
3841
3842
3843
3844
3845
3846
3847
#endif
  }else{
    pTos->flags = MEM_Null;
  }
  break;
}

/* Opcode: IdxGT P1 P2 P3
**
** Compare the top of the stack against the key on the index entry that
** cursor P1 is currently pointing to.  Ignore the ROWID of the
** index entry.  If the index entry is greater than the top of the stack
** then jump to P2.  Otherwise fall through to the next instruction.
** In either case, the stack is popped once.
**
** If P3 is the "+" string (or any other non-NULL string) then the
** index taken from the top of the stack is temporarily increased by
** an epsilon prior to the comparison.
*/
/* Opcode: IdxGE P1 P2 *
**
** Compare the top of the stack against the key on the index entry that
** cursor P1 is currently pointing to.  Ignore the ROWID of the
** index entry.  If the index entry is greater than or equal to 
** the top of the stack
** then jump to P2.  Otherwise fall through to the next instruction.
** In either case, the stack is popped once.
**
** If P3 is the "+" string (or any other non-NULL string) then the
** index taken from the top of the stack is temporarily increased by
** an epsilon prior to the comparison.
*/
/* Opcode: IdxLT P1 P2 *
**
** Compare the top of the stack against the key on the index entry that
** cursor P1 is currently pointing to.  Ignore the ROWID of the
** index entry.  If the index entry is less than the top of the stack
** then jump to P2.  Otherwise fall through to the next instruction.
** In either case, the stack is popped once.
**
** If P3 is the "+" string (or any other non-NULL string) then the
** index taken from the top of the stack is temporarily increased by
** an epsilon prior to the comparison.
*/
case OP_IdxLT:
case OP_IdxGT:
case OP_IdxGE: {
  int i= pOp->p1;
  BtCursor *pCrsr;
  Cursor *pC;
5088
5089
5090
5091
5092
5093
5094
5095
5096
5097
5098
5099
5100
5101
5102
            zBuf[1] = 's';
          }
          zBuf[2] = '[';
          k = 3;
          for(j=0; j<20 && j<pTos[i].n; j++){
            int c = pTos[i].z[j];
            if( c==0 && j==pTos[i].n-1 ) break;
            if( isprint(c) && !isspace(c) ){
              zBuf[k++] = c;
            }else{
              zBuf[k++] = '.';
            }
          }
          zBuf[k++] = ']';
          zBuf[k++] = 0;







|







5104
5105
5106
5107
5108
5109
5110
5111
5112
5113
5114
5115
5116
5117
5118
            zBuf[1] = 's';
          }
          zBuf[2] = '[';
          k = 3;
          for(j=0; j<20 && j<pTos[i].n; j++){
            int c = pTos[i].z[j];
            if( c==0 && j==pTos[i].n-1 ) break;
            if( c>=0x20 && c<0x7f ){
              zBuf[k++] = c;
            }else{
              zBuf[k++] = '.';
            }
          }
          zBuf[k++] = ']';
          zBuf[k++] = 0;
Changes to src/where.c.
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
**    May you find forgiveness for yourself and forgive others.
**    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.
**
** $Id: where.c,v 1.96 2004/05/18 01:23:39 danielk1977 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.







|







8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
**    May you find forgiveness for yourself and forgive others.
**    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.
**
** $Id: where.c,v 1.97 2004/05/19 13:13:08 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.
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
    sqlite3VdbeAddOp(v, OP_Integer, pTab->iDb, 0);
    sqlite3VdbeOp3(v, OP_OpenRead, pTabList->a[i].iCursor, pTab->tnum,
                     pTab->zName, P3_STATIC);
    sqlite3VdbeAddOp(v, OP_SetNumColumns, pTabList->a[i].iCursor, pTab->nCol);
    sqlite3CodeVerifySchema(pParse, pTab->iDb);
    if( (pIx = pWInfo->a[i].pIdx)!=0 ){
      sqlite3VdbeAddOp(v, OP_Integer, pIx->iDb, 0);
      sqlite3VdbeOp3(v, OP_OpenRead, pWInfo->a[i].iCur, pIx->tnum, pIx->zName,0);
    }
  }

  /* Generate the code to do the search
  */
  loopMask = 0;
  for(i=0; i<pTabList->nSrc; i++){







|







680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
    sqlite3VdbeAddOp(v, OP_Integer, pTab->iDb, 0);
    sqlite3VdbeOp3(v, OP_OpenRead, pTabList->a[i].iCursor, pTab->tnum,
                     pTab->zName, P3_STATIC);
    sqlite3VdbeAddOp(v, OP_SetNumColumns, pTabList->a[i].iCursor, pTab->nCol);
    sqlite3CodeVerifySchema(pParse, pTab->iDb);
    if( (pIx = pWInfo->a[i].pIdx)!=0 ){
      sqlite3VdbeAddOp(v, OP_Integer, pIx->iDb, 0);
      sqlite3VdbeOp3(v, OP_OpenRead, pWInfo->a[i].iCur, pIx->tnum,pIx->zName,0);
    }
  }

  /* Generate the code to do the search
  */
  loopMask = 0;
  for(i=0; i<pTabList->nSrc; i++){
745
746
747
748
749
750
751




752
753
754
755
756
757
758
      /* Case 2:  There is an index and all terms of the WHERE clause that
      **          refer to the index use the "==" or "IN" operators.
      */
      int start;
      int testOp;
      int nColumn = (pLevel->score+4)/8;
      brk = pLevel->brk = sqlite3VdbeMakeLabel(v);




      for(j=0; j<nColumn; j++){
        for(k=0; k<nExpr; k++){
          Expr *pX = aExpr[k].p;
          if( pX==0 ) continue;
          if( aExpr[k].idxLeft==iCur
             && (aExpr[k].prereqRight & loopMask)==aExpr[k].prereqRight 
             && pX->pLeft->iColumn==pIdx->aiColumn[j]







>
>
>
>







745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
      /* Case 2:  There is an index and all terms of the WHERE clause that
      **          refer to the index use the "==" or "IN" operators.
      */
      int start;
      int testOp;
      int nColumn = (pLevel->score+4)/8;
      brk = pLevel->brk = sqlite3VdbeMakeLabel(v);

      /* For each column of the index, find the term of the WHERE clause that
      ** constraints that column.  If the WHERE clause term is X=expr, then
      ** evaluation expr and leave the result on the stack */
      for(j=0; j<nColumn; j++){
        for(k=0; k<nExpr; k++){
          Expr *pX = aExpr[k].p;
          if( pX==0 ) continue;
          if( aExpr[k].idxLeft==iCur
             && (aExpr[k].prereqRight & loopMask)==aExpr[k].prereqRight 
             && pX->pLeft->iColumn==pIdx->aiColumn[j]
787
788
789
790
791
792
793





794
795
796


797
798
799




800
801
802
803
804






805
806
807
808
809
810
811
              break;
            }
          }
        }
      }
      pLevel->iMem = pParse->nMem++;
      cont = pLevel->cont = sqlite3VdbeMakeLabel(v);





      sqlite3VdbeAddOp(v, OP_NotNull, -nColumn, sqlite3VdbeCurrentAddr(v)+3);
      sqlite3VdbeAddOp(v, OP_Pop, nColumn, 0);
      sqlite3VdbeAddOp(v, OP_Goto, 0, brk);


      sqlite3VdbeAddOp(v, OP_MakeKey, nColumn, 0);
      sqlite3IndexAffinityStr(v, pIdx);
      sqlite3VdbeAddOp(v, OP_MemStore, pLevel->iMem, 0);




      if( nColumn==pIdx->nColumn || pLevel->bRev ){
        testOp = OP_IdxGT;
      }else{
        testOp = OP_IdxGE;
      }






      if( pLevel->bRev ){
        /* Scan in reverse order */
        sqlite3VdbeAddOp(v, OP_MoveLt, pLevel->iCur, brk);
        sqlite3VdbeChangeP3(v, -1, "+", P3_STATIC);
        start = sqlite3VdbeAddOp(v, OP_MemLoad, pLevel->iMem, 0);
        sqlite3VdbeAddOp(v, OP_IdxLT, pLevel->iCur, brk);
        pLevel->op = OP_Prev;







>
>
>
>
>



>
>



>
>
>
>





>
>
>
>
>
>







791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
821
822
823
824
825
826
827
828
829
830
831
832
              break;
            }
          }
        }
      }
      pLevel->iMem = pParse->nMem++;
      cont = pLevel->cont = sqlite3VdbeMakeLabel(v);

      /* At this point, the top nColumn elements of the stack are the
      ** values of columns in the index we are using.  Check to see if
      ** any of these values are NULL.  If they are, they will not match any
      ** index entries, so skip immediately to the next iteration of the loop */
      sqlite3VdbeAddOp(v, OP_NotNull, -nColumn, sqlite3VdbeCurrentAddr(v)+3);
      sqlite3VdbeAddOp(v, OP_Pop, nColumn, 0);
      sqlite3VdbeAddOp(v, OP_Goto, 0, brk);

      /* Generate an index key from the top nColumn elements of the stack */
      sqlite3VdbeAddOp(v, OP_MakeKey, nColumn, 0);
      sqlite3IndexAffinityStr(v, pIdx);
      sqlite3VdbeAddOp(v, OP_MemStore, pLevel->iMem, 0);

      /* Choose a testOp that will determine when we have moved past the
      ** last element of the table that matches the index key we just
      ** created */
      if( nColumn==pIdx->nColumn || pLevel->bRev ){
        testOp = OP_IdxGT;
      }else{
        testOp = OP_IdxGE;
      }

      /* Generate code (1) to move to the first matching element of the table.
      ** Then generate code (2) that jumps to "brk" after the cursor is past
      ** the last matching element of the table.  The code (1) is executed
      ** once to initialize the search, the code (2) is executed before each
      ** iteration of the scan to see if the scan has finished. */
      if( pLevel->bRev ){
        /* Scan in reverse order */
        sqlite3VdbeAddOp(v, OP_MoveLt, pLevel->iCur, brk);
        sqlite3VdbeChangeP3(v, -1, "+", P3_STATIC);
        start = sqlite3VdbeAddOp(v, OP_MemLoad, pLevel->iMem, 0);
        sqlite3VdbeAddOp(v, OP_IdxLT, pLevel->iCur, brk);
        pLevel->op = OP_Prev;
999
1000
1001
1002
1003
1004
1005
1006
1007
1008
1009
1010
1011
1012
1013
        sqlite3VdbeAddOp(v, OP_NotNull, -nCol, sqlite3VdbeCurrentAddr(v)+3);
        sqlite3VdbeAddOp(v, OP_Pop, nCol, 0);
        sqlite3VdbeAddOp(v, OP_Goto, 0, brk);
        sqlite3VdbeAddOp(v, OP_MakeKey, nCol, 0);
        sqlite3IndexAffinityStr(v, pIdx);
        if( pLevel->bRev ){
          sqlite3VdbeAddOp(v, OP_MoveLt, pLevel->iCur, brk);
          if( !geFlag ){
            sqlite3VdbeChangeP3(v, -1, "+", P3_STATIC);
          }
        }else{
          sqlite3VdbeAddOp(v, OP_MemStore, pLevel->iMem, 1);
        }
      }else if( pLevel->bRev ){
        sqlite3VdbeAddOp(v, OP_Last, pLevel->iCur, brk);







|







1020
1021
1022
1023
1024
1025
1026
1027
1028
1029
1030
1031
1032
1033
1034
        sqlite3VdbeAddOp(v, OP_NotNull, -nCol, sqlite3VdbeCurrentAddr(v)+3);
        sqlite3VdbeAddOp(v, OP_Pop, nCol, 0);
        sqlite3VdbeAddOp(v, OP_Goto, 0, brk);
        sqlite3VdbeAddOp(v, OP_MakeKey, nCol, 0);
        sqlite3IndexAffinityStr(v, pIdx);
        if( pLevel->bRev ){
          sqlite3VdbeAddOp(v, OP_MoveLt, pLevel->iCur, brk);
          if( leFlag ){
            sqlite3VdbeChangeP3(v, -1, "+", P3_STATIC);
          }
        }else{
          sqlite3VdbeAddOp(v, OP_MemStore, pLevel->iMem, 1);
        }
      }else if( pLevel->bRev ){
        sqlite3VdbeAddOp(v, OP_Last, pLevel->iCur, brk);
1198
1199
1200
1201
1202
1203
1204
1205
1206
1207
  if( pWInfo->pParse->nTab==pWInfo->peakNTab ){
    pWInfo->pParse->nTab = pWInfo->savedNTab;
  }
#endif
  sqliteFree(pWInfo);
  return;
}










<
<
<
1219
1220
1221
1222
1223
1224
1225



  if( pWInfo->pParse->nTab==pWInfo->peakNTab ){
    pWInfo->pParse->nTab = pWInfo->savedNTab;
  }
#endif
  sqliteFree(pWInfo);
  return;
}



Changes to test/quick.test.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
# 2001 September 15
#
# The author disclaims copyright to this source code.  In place of
# a legal notice, here is a blessing:
#
#    May you do good and not evil.
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file runs all tests.
#
# $Id: quick.test,v 1.12 2004/05/18 10:06:26 danielk1977 Exp $

set testdir [file dirname $argv0]
source $testdir/tester.tcl
rename finish_test really_finish_test
proc finish_test {} {}
set ISQUICK 1













|







1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
# 2001 September 15
#
# The author disclaims copyright to this source code.  In place of
# a legal notice, here is a blessing:
#
#    May you do good and not evil.
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file runs all tests.
#
# $Id: quick.test,v 1.13 2004/05/19 13:13:08 drh Exp $

set testdir [file dirname $argv0]
source $testdir/tester.tcl
rename finish_test really_finish_test
proc finish_test {} {}
set ISQUICK 1

41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
lappend EXCLUDE tableapi.test     ;# sqlite3_XX vs sqlite_XX problem
lappend EXCLUDE version.test      ;# uses the btree_meta API (not updated)

# Some tests fail in these file as a result of the partial manifest types
# implementation.
lappend EXCLUDE misc1.test 
lappend EXCLUDE capi2.test 
lappend EXCLUDE where.test


if {[sqlite -has-codec]} {
  lappend EXCLUDE \
    attach.test \
    attach2.test \
    auth.test \







<







41
42
43
44
45
46
47

48
49
50
51
52
53
54
lappend EXCLUDE tableapi.test     ;# sqlite3_XX vs sqlite_XX problem
lappend EXCLUDE version.test      ;# uses the btree_meta API (not updated)

# Some tests fail in these file as a result of the partial manifest types
# implementation.
lappend EXCLUDE misc1.test 
lappend EXCLUDE capi2.test 



if {[sqlite -has-codec]} {
  lappend EXCLUDE \
    attach.test \
    attach2.test \
    auth.test \
Changes to test/where.test.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
# 2001 September 15
#
# The author disclaims copyright to this source code.  In place of
# a legal notice, here is a blessing:
#
#    May you do good and not evil.
#    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.17 2003/06/15 23:42:25 drh Exp $

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

# Build some test data
#
do_test where-1.0 {













|







1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
# 2001 September 15
#
# The author disclaims copyright to this source code.  In place of
# a legal notice, here is a blessing:
#
#    May you do good and not evil.
#    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.18 2004/05/19 13:13:08 drh Exp $

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

# Build some test data
#
do_test where-1.0 {
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
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363

# 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 199}
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 199}
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 199}
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 199}
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 199}
do_test where-5.11 {
  count {
    SELECT * FROM t1 WHERE y IN (6400,8100) ORDER BY 1;
  }
} {79 6 6400 89 6 8100 199}
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}

# This procedure executes the SQL.  Then it checks the generated program
# for the SQL and appends a "nosort" to the result if the program contains the
# SortCallback opcode.  If the program does not contain the SortCallback
# opcode it appends "sort"
#
proc cksort {sql} {







|









|











|






|






|






|




|



















|




|







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
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363

# 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 3}
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 199}
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 13}
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 199}
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 3}
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 201}
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 9}
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 201}
do_test where-5.9 {
  count {
    SELECT * FROM t1 WHERE x IN (1,7) ORDER BY 1;
  }
} {2 1 9 3 1 16 7}
do_test where-5.10 {
  count {
    SELECT * FROM t1 WHERE x+0 IN (1,7) ORDER BY 1;
  }
} {2 1 9 3 1 16 199}
do_test where-5.11 {
  count {
    SELECT * FROM t1 WHERE y IN (6400,8100) ORDER BY 1;
  }
} {79 6 6400 89 6 8100 199}
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 7}
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 7}

# This procedure executes the SQL.  Then it checks the generated program
# for the SQL and appends a "nosort" to the result if the program contains the
# SortCallback opcode.  If the program does not contain the SortCallback
# opcode it appends "sort"
#
proc cksort {sql} {