/ Check-in [262bcd17]
Login

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

Overview
Comment:The new code for taking advantage of inequalities in WHERE clauses is in place. It appears to work. (CVS 305)
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1:262bcd17df19f45def6144b5a7e0602ca5b03deb
User & Date: drh 2001-11-08 00:45:21
Context
2001-11-09
13:41
Bug fixes. (CVS 306) check-in: 84997fda user: drh tags: trunk
2001-11-08
00:45
The new code for taking advantage of inequalities in WHERE clauses is in place. It appears to work. (CVS 305) check-in: 262bcd17 user: drh tags: trunk
2001-11-07
16:48
New Next opcode and indexing style implemented. (CVS 304) check-in: decbeb91 user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/expr.c.

8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
...
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
...
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
...
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
**    May you find forgiveness for yourself and forgive others.
**    May you share freely, never taking more than you give.
**
*************************************************************************
** This file contains routines used for analyzing expressions and
** for generating VDBE code that evaluates expressions in SQLite.
**
** $Id: expr.c,v 1.32 2001/10/22 02:58:10 drh Exp $
*/
#include "sqliteInt.h"

/*
** Walk an expression tree.  Return 1 if the expression is constant
** and 0 if it involves variables.
*/
................................................................................
     int id;
  } aFunc[] = {
     { "count",  5, FN_Count  },
     { "min",    3, FN_Min    },
     { "max",    3, FN_Max    },
     { "sum",    3, FN_Sum    },
     { "avg",    3, FN_Avg    },
     { "fcnt",   4, FN_Fcnt   },  /* Used for testing only */
     { "length", 6, FN_Length },
     { "substr", 6, FN_Substr },
     { "abs",    3, FN_Abs    },
     { "round",  5, FN_Round  },
  };
  int i;
  for(i=0; i<ArraySize(aFunc); i++){
................................................................................
          break;
        }
        case FN_Substr: {
          too_few_args = n<3;
          too_many_args = n>3;
          break;
        }
        /* The "fcnt(*)" function always returns the number of OP_MoveTo
        ** operations that have occurred so far while processing the
        ** SQL statement.  This information can be used by test procedures
        ** to verify that indices are being used properly to minimize
        ** searching.  All arguments to fcnt() are ignored.  fcnt() has
        ** no use (other than testing) that we are aware of.
        */
        case FN_Fcnt: {
          n = 0;
          break;
        }
      
        default: break;
      }
      if( no_such_func ){
        sqliteSetNString(&pParse->zErrMsg, "no such function: ", -1,
           pExpr->token.z, pExpr->token.n, 0);
        pParse->nErr++;
        nErr++;
................................................................................
    }
    case TK_FUNCTION: {
      int id = pExpr->iColumn;
      int op;
      int i;
      ExprList *pList = pExpr->pList;
      switch( id ){
        case FN_Fcnt: {
          sqliteVdbeAddOp(v, OP_Fcnt, 0, 0);
          break;
        }
        case FN_Min: 
        case FN_Max: {
          op = id==FN_Min ? OP_Min : OP_Max;
          for(i=0; i<pList->nExpr; i++){
            sqliteExprCode(pParse, pList->a[i].pExpr);
            if( i>0 ){
              sqliteVdbeAddOp(v, op, 0, 0);







|







 







<







 







<
<
<
<
<
<
<
<
<
<
<
<







 







<
<
<
<







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

343
344
345
346
347
348
349
...
414
415
416
417
418
419
420












421
422
423
424
425
426
427
...
618
619
620
621
622
623
624




625
626
627
628
629
630
631
**    May you find forgiveness for yourself and forgive others.
**    May you share freely, never taking more than you give.
**
*************************************************************************
** This file contains routines used for analyzing expressions and
** for generating VDBE code that evaluates expressions in SQLite.
**
** $Id: expr.c,v 1.33 2001/11/08 00:45:21 drh Exp $
*/
#include "sqliteInt.h"

/*
** Walk an expression tree.  Return 1 if the expression is constant
** and 0 if it involves variables.
*/
................................................................................
     int id;
  } aFunc[] = {
     { "count",  5, FN_Count  },
     { "min",    3, FN_Min    },
     { "max",    3, FN_Max    },
     { "sum",    3, FN_Sum    },
     { "avg",    3, FN_Avg    },

     { "length", 6, FN_Length },
     { "substr", 6, FN_Substr },
     { "abs",    3, FN_Abs    },
     { "round",  5, FN_Round  },
  };
  int i;
  for(i=0; i<ArraySize(aFunc); i++){
................................................................................
          break;
        }
        case FN_Substr: {
          too_few_args = n<3;
          too_many_args = n>3;
          break;
        }












        default: break;
      }
      if( no_such_func ){
        sqliteSetNString(&pParse->zErrMsg, "no such function: ", -1,
           pExpr->token.z, pExpr->token.n, 0);
        pParse->nErr++;
        nErr++;
................................................................................
    }
    case TK_FUNCTION: {
      int id = pExpr->iColumn;
      int op;
      int i;
      ExprList *pList = pExpr->pList;
      switch( id ){




        case FN_Min: 
        case FN_Max: {
          op = id==FN_Min ? OP_Min : OP_Max;
          for(i=0; i<pList->nExpr; i++){
            sqliteExprCode(pParse, pList->a[i].pExpr);
            if( i>0 ){
              sqliteVdbeAddOp(v, op, 0, 0);

Changes to src/sqliteInt.h.

7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
...
305
306
307
308
309
310
311

312
313
314
315
316
317
318
**    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.
**
*************************************************************************
** Internal interface definitions for SQLite.
**
** @(#) $Id: sqliteInt.h,v 1.68 2001/11/07 16:48:27 drh Exp $
*/
#include "sqlite.h"
#include "hash.h"
#include "vdbe.h"
#include "parse.h"
#include "btree.h"
#include <stdio.h>
................................................................................
** is intended to be private the the where.c module and should not be
** access or modified by other modules.
*/
struct WhereLevel {
  int iMem;            /* Memory cell used by this level */
  Index *pIdx;         /* Index used */
  int iCur;            /* Cursor number used for this index */

  int brk;             /* Jump here to break out of the loop */
  int cont;            /* Jump here to continue with the next loop cycle */
  int op, p1, p2;      /* Opcode used to terminate the loop */
};

/*
** The WHERE clause processing routine has two halves.  The







|







 







>







7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
...
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
**    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.
**
*************************************************************************
** Internal interface definitions for SQLite.
**
** @(#) $Id: sqliteInt.h,v 1.69 2001/11/08 00:45:21 drh Exp $
*/
#include "sqlite.h"
#include "hash.h"
#include "vdbe.h"
#include "parse.h"
#include "btree.h"
#include <stdio.h>
................................................................................
** is intended to be private the the where.c module and should not be
** access or modified by other modules.
*/
struct WhereLevel {
  int iMem;            /* Memory cell used by this level */
  Index *pIdx;         /* Index used */
  int iCur;            /* Cursor number used for this index */
  int score;           /* How well this indexed scored */
  int brk;             /* Jump here to break out of the loop */
  int cont;            /* Jump here to continue with the next loop cycle */
  int op, p1, p2;      /* Opcode used to terminate the loop */
};

/*
** The WHERE clause processing routine has two halves.  The

Changes to src/test1.c.

9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
...
299
300
301
302
303
304
305

306
307
308
309
310
311
312
313


314
315
316
317
318
319
**    May you share freely, never taking more than you give.
**
*************************************************************************
** Code for testing the printf() interface to SQLite.  This code
** is not included in the SQLite library.  It is used for automated
** testing of the SQLite library.
**
** $Id: test1.c,v 1.4 2001/09/24 03:12:40 drh Exp $
*/
#include "sqliteInt.h"
#include "tcl.h"
#include <stdlib.h>
#include <string.h>

/*
................................................................................
}
#endif

/*
** Register commands with the TCL interpreter.
*/
int Sqlitetest1_Init(Tcl_Interp *interp){

  Tcl_CreateCommand(interp, "sqlite_mprintf_int", sqlite_mprintf_int, 0, 0);
  Tcl_CreateCommand(interp, "sqlite_mprintf_str", sqlite_mprintf_str, 0, 0);
  Tcl_CreateCommand(interp, "sqlite_mprintf_double", sqlite_mprintf_double,0,0);
  Tcl_CreateCommand(interp, "sqlite_open", sqlite_test_open, 0, 0);
  Tcl_CreateCommand(interp, "sqlite_exec_printf", test_exec_printf, 0, 0);
  Tcl_CreateCommand(interp, "sqlite_get_table_printf", test_get_table_printf,
      0, 0);
  Tcl_CreateCommand(interp, "sqlite_close", sqlite_test_close, 0, 0);


#ifdef MEMORY_DEBUG
  Tcl_CreateCommand(interp, "sqlite_malloc_fail", sqlite_malloc_fail, 0, 0);
  Tcl_CreateCommand(interp, "sqlite_malloc_stat", sqlite_malloc_stat, 0, 0);
#endif
  return TCL_OK;
}







|







 







>








>
>






9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
...
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
**    May you share freely, never taking more than you give.
**
*************************************************************************
** Code for testing the printf() interface to SQLite.  This code
** is not included in the SQLite library.  It is used for automated
** testing of the SQLite library.
**
** $Id: test1.c,v 1.5 2001/11/08 00:45:21 drh Exp $
*/
#include "sqliteInt.h"
#include "tcl.h"
#include <stdlib.h>
#include <string.h>

/*
................................................................................
}
#endif

/*
** Register commands with the TCL interpreter.
*/
int Sqlitetest1_Init(Tcl_Interp *interp){
  extern int sqlite_search_count;
  Tcl_CreateCommand(interp, "sqlite_mprintf_int", sqlite_mprintf_int, 0, 0);
  Tcl_CreateCommand(interp, "sqlite_mprintf_str", sqlite_mprintf_str, 0, 0);
  Tcl_CreateCommand(interp, "sqlite_mprintf_double", sqlite_mprintf_double,0,0);
  Tcl_CreateCommand(interp, "sqlite_open", sqlite_test_open, 0, 0);
  Tcl_CreateCommand(interp, "sqlite_exec_printf", test_exec_printf, 0, 0);
  Tcl_CreateCommand(interp, "sqlite_get_table_printf", test_get_table_printf,
      0, 0);
  Tcl_CreateCommand(interp, "sqlite_close", sqlite_test_close, 0, 0);
  Tcl_LinkVar(interp, "sqlite_search_count", 
      (char*)&sqlite_search_count, TCL_LINK_INT);
#ifdef MEMORY_DEBUG
  Tcl_CreateCommand(interp, "sqlite_malloc_fail", sqlite_malloc_fail, 0, 0);
  Tcl_CreateCommand(interp, "sqlite_malloc_stat", sqlite_malloc_stat, 0, 0);
#endif
  return TCL_OK;
}

Changes to src/vdbe.c.

26
27
28
29
30
31
32
33
34
35
36
37








38
39
40
41
42
43
44
...
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
...
833
834
835
836
837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852
853
854
855
856
857
858
859
860
861
862
863
864
865
866
867
868
869
870
871
....
2520
2521
2522
2523
2524
2525
2526
2527
2528
2529
2530
2531
2532
2533
2534
2535
2536
2537
2538
2539
2540
2541
2542
2543
2544
2545
2546
2547
2548
2549
2550
2551
2552
2553
2554
2555
2556
2557
2558
2559
2560
2561
2562
2563
....
2936
2937
2938
2939
2940
2941
2942
2943
2944
2945
2946
2947
2948
2949
2950
** type to the other occurs as necessary.
** 
** Most of the code in this file is taken up by the sqliteVdbeExec()
** function which does the work of interpreting a VDBE program.
** But other routines are also provided to help in building up
** a program instruction by instruction.
**
** $Id: vdbe.c,v 1.95 2001/11/07 16:48:27 drh Exp $
*/
#include "sqliteInt.h"
#include <ctype.h>









/*
** SQL is translated into a sequence of instructions to be
** executed by a virtual machine.  Each instruction is an instance
** of the following structure.
*/
typedef struct VdbeOp Op;

................................................................................
  char *zLine;        /* A single line from the input file */
  int nLineAlloc;     /* Number of spaces allocated for zLine */
  int nMem;           /* Number of memory locations currently allocated */
  Mem *aMem;          /* The memory locations */
  Agg agg;            /* Aggregate information */
  int nSet;           /* Number of sets allocated */
  Set *aSet;          /* An array of sets */
  int nFetch;         /* Number of OP_Fetch instructions executed */
  int nCallback;      /* Number of callbacks invoked so far */
  int iLimit;         /* Limit on the number of callbacks remaining */
  int iOffset;        /* Offset before beginning to do callbacks */
};

/*
** Create a new virtual database engine.
................................................................................
** "opNames.awk" awk script which is part of the source tree to regenerate
** this array, then copy and paste it into this file, if you want.
*/
static char *zOpName[] = { 0,
  "Transaction",       "Commit",            "Rollback",          "ReadCookie",
  "SetCookie",         "VerifyCookie",      "Open",              "OpenTemp",
  "OpenWrite",         "OpenAux",           "OpenWrAux",         "Close",
  "MoveTo",            "Fcnt",              "NewRecno",          "Put",
  "Distinct",          "Found",             "NotFound",          "Delete",
  "Column",            "KeyAsData",         "Recno",             "FullKey",
  "Rewind",            "Next",              "Destroy",           "Clear",
  "CreateIndex",       "CreateTable",       "Reorganize",        "IdxPut",
  "IdxDelete",         "IdxRecno",          "IdxGT",             "IdxGE",
  "MemLoad",           "MemStore",          "ListWrite",         "ListRewind",
  "ListRead",          "ListReset",         "SortPut",           "SortMakeRec",
  "SortMakeKey",       "Sort",              "SortNext",          "SortCallback",
  "SortReset",         "FileOpen",          "FileRead",          "FileColumn",
  "AggReset",          "AggFocus",          "AggIncr",           "AggNext",
  "AggSet",            "AggGet",            "SetInsert",         "SetFound",
  "SetNotFound",       "MakeRecord",        "MakeKey",           "MakeIdxKey",
  "IncrKey",           "Goto",              "If",                "Halt",
  "ColumnCount",       "ColumnName",        "Callback",          "NullCallback",
  "Integer",           "String",            "Pop",               "Dup",
  "Pull",              "Add",               "AddImm",            "Subtract",
  "Multiply",          "Divide",            "Remainder",         "BitAnd",
  "BitOr",             "BitNot",            "ShiftLeft",         "ShiftRight",
  "AbsValue",          "Precision",         "Min",               "Max",
  "Like",              "Glob",              "Eq",                "Ne",
  "Lt",                "Le",                "Gt",                "Ge",
  "IsNull",            "NotNull",           "Negative",          "And",
  "Or",                "Not",               "Concat",            "Noop",
  "Strlen",            "Substr",            "Limit",           
};

/*
** Given the name of an opcode, return its number.  Return 0 if
** there is no match.
**
** This routine is used for testing and debugging.
................................................................................
      pC->lastRecno = aStack[tos].i;
      pC->recnoIsValid = 1;
    }else{
      if( Stringify(p, tos) ) goto no_mem;
      sqliteBtreeMoveto(pC->pCursor, zStack[tos], aStack[tos].n, &res);
      pC->recnoIsValid = 0;
    }
    p->nFetch++;
    if( res<0 ){
      sqliteBtreeNext(pC->pCursor, &res);
      pC->recnoIsValid = 0;
      if( res && pOp->p2>0 ){
        pc = pOp->p2 - 1;
      }
    }
  }
  POPSTACK;
  break;
}

/* Opcode: Fcnt * * *
**
** Push an integer onto the stack which is the total number of
** MoveTo opcodes that have been executed by this virtual machine.
**
** This instruction is used to implement the special fcnt() function
** in the SQL dialect that SQLite understands.  fcnt() is used for
** testing purposes.
*/
case OP_Fcnt: {
  int i = ++p->tos;
  VERIFY( if( NeedStack(p, p->tos) ) goto no_mem; )
  aStack[i].i = p->nFetch;
  aStack[i].flags = STK_Int;
  break;
}

/* Opcode: Distinct P1 P2 *
**
** Use the top of the stack as a key.  If a record with that key does
** not exist in the table of cursor P1, then jump to P2.  If the record
** does already exist, then fall thru.  The cursor is left pointing
** at the record if it exists. The key is not popped from the stack.
**
................................................................................
  BtCursor *pCrsr;

  if( VERIFY( i>=0 && i<p->nCursor && ) (pCrsr = p->aCsr[i].pCursor)!=0 ){
    int res;
    rc = sqliteBtreeNext(pCrsr, &res);
    if( res==0 ){
      pc = pOp->p2 - 1;
      p->nFetch++;
    }
    p->aCsr[i].recnoIsValid = 0;
  }
  break;
}

/* Opcode: IdxPut P1 P2 P3







|




>
>
>
>
>
>
>
>







 







<







 







|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|







 







|












<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<







 







|







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
...
210
211
212
213
214
215
216

217
218
219
220
221
222
223
...
840
841
842
843
844
845
846
847
848
849
850
851
852
853
854
855
856
857
858
859
860
861
862
863
864
865
866
867
868
869
870
871
872
873
874
875
876
877
878
....
2527
2528
2529
2530
2531
2532
2533
2534
2535
2536
2537
2538
2539
2540
2541
2542
2543
2544
2545
2546

















2547
2548
2549
2550
2551
2552
2553
....
2926
2927
2928
2929
2930
2931
2932
2933
2934
2935
2936
2937
2938
2939
2940
** type to the other occurs as necessary.
** 
** Most of the code in this file is taken up by the sqliteVdbeExec()
** function which does the work of interpreting a VDBE program.
** But other routines are also provided to help in building up
** a program instruction by instruction.
**
** $Id: vdbe.c,v 1.96 2001/11/08 00:45:22 drh Exp $
*/
#include "sqliteInt.h"
#include <ctype.h>

/*
** The following global variable is incremented every time a cursor
** moves, either by the OP_MoveTo or the OP_Next opcode.  The test
** procedures use this information to make sure that indices are
** working correctly.
*/
int sqlite_search_count = 0;

/*
** SQL is translated into a sequence of instructions to be
** executed by a virtual machine.  Each instruction is an instance
** of the following structure.
*/
typedef struct VdbeOp Op;

................................................................................
  char *zLine;        /* A single line from the input file */
  int nLineAlloc;     /* Number of spaces allocated for zLine */
  int nMem;           /* Number of memory locations currently allocated */
  Mem *aMem;          /* The memory locations */
  Agg agg;            /* Aggregate information */
  int nSet;           /* Number of sets allocated */
  Set *aSet;          /* An array of sets */

  int nCallback;      /* Number of callbacks invoked so far */
  int iLimit;         /* Limit on the number of callbacks remaining */
  int iOffset;        /* Offset before beginning to do callbacks */
};

/*
** Create a new virtual database engine.
................................................................................
** "opNames.awk" awk script which is part of the source tree to regenerate
** this array, then copy and paste it into this file, if you want.
*/
static char *zOpName[] = { 0,
  "Transaction",       "Commit",            "Rollback",          "ReadCookie",
  "SetCookie",         "VerifyCookie",      "Open",              "OpenTemp",
  "OpenWrite",         "OpenAux",           "OpenWrAux",         "Close",
  "MoveTo",            "NewRecno",          "Put",               "Distinct",
  "Found",             "NotFound",          "Delete",            "Column",
  "KeyAsData",         "Recno",             "FullKey",           "Rewind",
  "Next",              "Destroy",           "Clear",             "CreateIndex",
  "CreateTable",       "Reorganize",        "IdxPut",            "IdxDelete",
  "IdxRecno",          "IdxGT",             "IdxGE",             "MemLoad",
  "MemStore",          "ListWrite",         "ListRewind",        "ListRead",
  "ListReset",         "SortPut",           "SortMakeRec",       "SortMakeKey",
  "Sort",              "SortNext",          "SortCallback",      "SortReset",
  "FileOpen",          "FileRead",          "FileColumn",        "AggReset",
  "AggFocus",          "AggIncr",           "AggNext",           "AggSet",
  "AggGet",            "SetInsert",         "SetFound",          "SetNotFound",
  "MakeRecord",        "MakeKey",           "MakeIdxKey",        "IncrKey",
  "Goto",              "If",                "Halt",              "ColumnCount",
  "ColumnName",        "Callback",          "NullCallback",      "Integer",
  "String",            "Pop",               "Dup",               "Pull",
  "Add",               "AddImm",            "Subtract",          "Multiply",
  "Divide",            "Remainder",         "BitAnd",            "BitOr",
  "BitNot",            "ShiftLeft",         "ShiftRight",        "AbsValue",
  "Precision",         "Min",               "Max",               "Like",
  "Glob",              "Eq",                "Ne",                "Lt",
  "Le",                "Gt",                "Ge",                "IsNull",
  "NotNull",           "Negative",          "And",               "Or",
  "Not",               "Concat",            "Noop",              "Strlen",
  "Substr",            "Limit",           
};

/*
** Given the name of an opcode, return its number.  Return 0 if
** there is no match.
**
** This routine is used for testing and debugging.
................................................................................
      pC->lastRecno = aStack[tos].i;
      pC->recnoIsValid = 1;
    }else{
      if( Stringify(p, tos) ) goto no_mem;
      sqliteBtreeMoveto(pC->pCursor, zStack[tos], aStack[tos].n, &res);
      pC->recnoIsValid = 0;
    }
    sqlite_search_count++;
    if( res<0 ){
      sqliteBtreeNext(pC->pCursor, &res);
      pC->recnoIsValid = 0;
      if( res && pOp->p2>0 ){
        pc = pOp->p2 - 1;
      }
    }
  }
  POPSTACK;
  break;
}


















/* Opcode: Distinct P1 P2 *
**
** Use the top of the stack as a key.  If a record with that key does
** not exist in the table of cursor P1, then jump to P2.  If the record
** does already exist, then fall thru.  The cursor is left pointing
** at the record if it exists. The key is not popped from the stack.
**
................................................................................
  BtCursor *pCrsr;

  if( VERIFY( i>=0 && i<p->nCursor && ) (pCrsr = p->aCsr[i].pCursor)!=0 ){
    int res;
    rc = sqliteBtreeNext(pCrsr, &res);
    if( res==0 ){
      pc = pOp->p2 - 1;
      sqlite_search_count++;
    }
    p->aCsr[i].recnoIsValid = 0;
  }
  break;
}

/* Opcode: IdxPut P1 P2 P3

Changes to src/vdbe.h.

11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
..
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
*************************************************************************
** Header file for the Virtual DataBase Engine (VDBE)
**
** This header defines the interface to the virtual database engine
** or VDBE.  The VDBE implements an abstract machine that runs a
** simple program to access and modify the underlying database.
**
** $Id: vdbe.h,v 1.35 2001/11/07 16:48:28 drh Exp $
*/
#ifndef _SQLITE_VDBE_H_
#define _SQLITE_VDBE_H_
#include <stdio.h>

/*
** A single VDBE is an opaque structure named "Vdbe".  Only routines
................................................................................
#define OP_Open                7
#define OP_OpenTemp            8
#define OP_OpenWrite           9
#define OP_OpenAux            10
#define OP_OpenWrAux          11
#define OP_Close              12
#define OP_MoveTo             13
#define OP_Fcnt               14
#define OP_NewRecno           15
#define OP_Put                16
#define OP_Distinct           17
#define OP_Found              18
#define OP_NotFound           19
#define OP_Delete             20
#define OP_Column             21
#define OP_KeyAsData          22
#define OP_Recno              23
#define OP_FullKey            24
#define OP_Rewind             25
#define OP_Next               26

#define OP_Destroy            27
#define OP_Clear              28
#define OP_CreateIndex        29
#define OP_CreateTable        30
#define OP_Reorganize         31

#define OP_IdxPut             32
#define OP_IdxDelete          33
#define OP_IdxRecno           34
#define OP_IdxGT              35
#define OP_IdxGE              36

#define OP_MemLoad            37
#define OP_MemStore           38

#define OP_ListWrite          39
#define OP_ListRewind         40
#define OP_ListRead           41
#define OP_ListReset          42

#define OP_SortPut            43
#define OP_SortMakeRec        44
#define OP_SortMakeKey        45
#define OP_Sort               46
#define OP_SortNext           47
#define OP_SortCallback       48
#define OP_SortReset          49

#define OP_FileOpen           50
#define OP_FileRead           51
#define OP_FileColumn         52

#define OP_AggReset           53
#define OP_AggFocus           54
#define OP_AggIncr            55
#define OP_AggNext            56
#define OP_AggSet             57
#define OP_AggGet             58

#define OP_SetInsert          59
#define OP_SetFound           60
#define OP_SetNotFound        61

#define OP_MakeRecord         62
#define OP_MakeKey            63
#define OP_MakeIdxKey         64
#define OP_IncrKey            65

#define OP_Goto               66
#define OP_If                 67
#define OP_Halt               68

#define OP_ColumnCount        69
#define OP_ColumnName         70
#define OP_Callback           71
#define OP_NullCallback       72

#define OP_Integer            73
#define OP_String             74
#define OP_Pop                75
#define OP_Dup                76
#define OP_Pull               77

#define OP_Add                78
#define OP_AddImm             79
#define OP_Subtract           80
#define OP_Multiply           81
#define OP_Divide             82
#define OP_Remainder          83
#define OP_BitAnd             84
#define OP_BitOr              85
#define OP_BitNot             86
#define OP_ShiftLeft          87
#define OP_ShiftRight         88
#define OP_AbsValue           89
#define OP_Precision          90
#define OP_Min                91
#define OP_Max                92
#define OP_Like               93
#define OP_Glob               94
#define OP_Eq                 95
#define OP_Ne                 96
#define OP_Lt                 97
#define OP_Le                 98
#define OP_Gt                 99
#define OP_Ge                100
#define OP_IsNull            101
#define OP_NotNull           102
#define OP_Negative          103
#define OP_And               104
#define OP_Or                105
#define OP_Not               106
#define OP_Concat            107
#define OP_Noop              108

#define OP_Strlen            109
#define OP_Substr            110

#define OP_Limit             111

#define OP_MAX               111

/*
** Prototypes for the VDBE interface.  See comments on the implementation
** for a description of what each of these routines does.
*/
Vdbe *sqliteVdbeCreate(sqlite*);
void sqliteVdbeCreateCallback(Vdbe*, int*);







|







 







<
|
|
|
|
|
|
|
|
|
|
|
|

|
|
|
|
|

|
|
|
|
|

|
|

|
|
|
|

|
|
|
|
|
|
|

|
|
|

|
|
|
|
|
|

|
|
|

|
|
|
|

|
|
|

|
|
|
|

|
|
|
|
|

|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|

|
|

|

|







11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
..
79
80
81
82
83
84
85

86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
*************************************************************************
** Header file for the Virtual DataBase Engine (VDBE)
**
** This header defines the interface to the virtual database engine
** or VDBE.  The VDBE implements an abstract machine that runs a
** simple program to access and modify the underlying database.
**
** $Id: vdbe.h,v 1.36 2001/11/08 00:45:22 drh Exp $
*/
#ifndef _SQLITE_VDBE_H_
#define _SQLITE_VDBE_H_
#include <stdio.h>

/*
** A single VDBE is an opaque structure named "Vdbe".  Only routines
................................................................................
#define OP_Open                7
#define OP_OpenTemp            8
#define OP_OpenWrite           9
#define OP_OpenAux            10
#define OP_OpenWrAux          11
#define OP_Close              12
#define OP_MoveTo             13

#define OP_NewRecno           14
#define OP_Put                15
#define OP_Distinct           16
#define OP_Found              17
#define OP_NotFound           18
#define OP_Delete             19
#define OP_Column             20
#define OP_KeyAsData          21
#define OP_Recno              22
#define OP_FullKey            23
#define OP_Rewind             24
#define OP_Next               25

#define OP_Destroy            26
#define OP_Clear              27
#define OP_CreateIndex        28
#define OP_CreateTable        29
#define OP_Reorganize         30

#define OP_IdxPut             31
#define OP_IdxDelete          32
#define OP_IdxRecno           33
#define OP_IdxGT              34
#define OP_IdxGE              35

#define OP_MemLoad            36
#define OP_MemStore           37

#define OP_ListWrite          38
#define OP_ListRewind         39
#define OP_ListRead           40
#define OP_ListReset          41

#define OP_SortPut            42
#define OP_SortMakeRec        43
#define OP_SortMakeKey        44
#define OP_Sort               45
#define OP_SortNext           46
#define OP_SortCallback       47
#define OP_SortReset          48

#define OP_FileOpen           49
#define OP_FileRead           50
#define OP_FileColumn         51

#define OP_AggReset           52
#define OP_AggFocus           53
#define OP_AggIncr            54
#define OP_AggNext            55
#define OP_AggSet             56
#define OP_AggGet             57

#define OP_SetInsert          58
#define OP_SetFound           59
#define OP_SetNotFound        60

#define OP_MakeRecord         61
#define OP_MakeKey            62
#define OP_MakeIdxKey         63
#define OP_IncrKey            64

#define OP_Goto               65
#define OP_If                 66
#define OP_Halt               67

#define OP_ColumnCount        68
#define OP_ColumnName         69
#define OP_Callback           70
#define OP_NullCallback       71

#define OP_Integer            72
#define OP_String             73
#define OP_Pop                74
#define OP_Dup                75
#define OP_Pull               76

#define OP_Add                77
#define OP_AddImm             78
#define OP_Subtract           79
#define OP_Multiply           80
#define OP_Divide             81
#define OP_Remainder          82
#define OP_BitAnd             83
#define OP_BitOr              84
#define OP_BitNot             85
#define OP_ShiftLeft          86
#define OP_ShiftRight         87
#define OP_AbsValue           88
#define OP_Precision          89
#define OP_Min                90
#define OP_Max                91
#define OP_Like               92
#define OP_Glob               93
#define OP_Eq                 94
#define OP_Ne                 95
#define OP_Lt                 96
#define OP_Le                 97
#define OP_Gt                 98
#define OP_Ge                 99
#define OP_IsNull            100
#define OP_NotNull           101
#define OP_Negative          102
#define OP_And               103
#define OP_Or                104
#define OP_Not               105
#define OP_Concat            106
#define OP_Noop              107

#define OP_Strlen            108
#define OP_Substr            109

#define OP_Limit             110

#define OP_MAX               110

/*
** Prototypes for the VDBE interface.  See comments on the implementation
** for a description of what each of these routines does.
*/
Vdbe *sqliteVdbeCreate(sqlite*);
void sqliteVdbeCreateCallback(Vdbe*, int*);

Changes to src/where.c.

9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
..
89
90
91
92
93
94
95


















96
97
98
99
100
101
102
...
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
...
209
210
211
212
213
214
215

216
217
218
219
220
221
222
...
234
235
236
237
238
239
240
241

242
243
244
245




246






247
248
249




250
251
252
253
254
255
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
...
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379


380
381
382
383

384
385
386
387
388
389
390
391

392
393
394
395
396
397
398
399
400
401
402
403
404

405







406
407





























































































408

























































409
410
411
412
413
414
415



416
417
418
419
420
421
422
**    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.25 2001/11/07 16:48:28 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.
................................................................................
    mask = exprTableUsage(base, p->pRight);
  }
  if( p->pLeft ){
    mask |= exprTableUsage(base, p->pLeft);
  }
  return mask;
}



















/*
** The input to this routine is an ExprInfo structure with only the
** "p" field filled in.  The job of this routine is to analyze the
** subexpression and populate all the other fields of the ExprInfo
** structure.
**
................................................................................
static void exprAnalyze(int base, ExprInfo *pInfo){
  Expr *pExpr = pInfo->p;
  pInfo->prereqLeft = exprTableUsage(base, pExpr->pLeft);
  pInfo->prereqRight = exprTableUsage(base, pExpr->pRight);
  pInfo->indexable = 0;
  pInfo->idxLeft = -1;
  pInfo->idxRight = -1;
  if( pExpr->op==TK_EQ && (pInfo->prereqRight & pInfo->prereqLeft)==0 ){
    if( pExpr->pRight->op==TK_COLUMN ){
      pInfo->idxRight = pExpr->pRight->iTable - base;
      pInfo->indexable = 1;
    }
    if( pExpr->pLeft->op==TK_COLUMN ){
      pInfo->idxLeft = pExpr->pLeft->iTable - base;
      pInfo->indexable = 1;
................................................................................
  loopMask = 0;
  for(i=0; i<pTabList->nId && i<ARRAYSIZE(aDirect); i++){
    int j;
    int idx = aOrder[i];
    Table *pTab = pTabList->a[idx].pTab;
    Index *pIdx;
    Index *pBestIdx = 0;


    /* Check to see if there is an expression that uses only the
    ** ROWID field of this table.  If so, set aDirect[i] to 1.
    ** If not, set aDirect[i] to 0.
    */
    aDirect[i] = 0;
    for(j=0; j<nExpr; j++){
................................................................................
    if( aDirect[i] ){
      loopMask |= 1<<idx;
      pWInfo->a[i].pIdx = 0;
      continue;
    }

    /* Do a search for usable indices.  Leave pBestIdx pointing to
    ** the most specific usable index.

    **
    ** "Most specific" means that pBestIdx is the usable index that
    ** has the largest value for nColumn.  A usable index is one for
    ** which there are subexpressions to compute every column of the




    ** index.






    */
    for(pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext){
      int columnMask = 0;





      if( pIdx->nColumn>32 ) continue;
      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 ){


              columnMask |= 1<<k;


















              break;
            }
          }
        }
        if( aExpr[j].idxRight==idx 
             && (aExpr[j].prereqLeft & loopMask)==aExpr[j].prereqLeft ){
          int iColumn = aExpr[j].p->pRight->iColumn;
          int k;
          for(k=0; k<pIdx->nColumn; k++){
            if( pIdx->aiColumn[k]==iColumn ){


              columnMask |= 1<<k;


















              break;
            }
          }
        }
      }
      if( columnMask + 1 == (1<<pIdx->nColumn) ){
        if( pBestIdx==0 || pBestIdx->nColumn<pIdx->nColumn ){
          pBestIdx = pIdx;
        }







      }
    }
    pWInfo->a[i].pIdx = pBestIdx;

    loopMask |= 1<<idx;
    if( pBestIdx ){
      pWInfo->a[i].iCur = nCur++;
    }
  }

  /* Open all tables in the pTabList and all indices used by those tables.
................................................................................
      }else{
        sqliteVdbeAddOp(v, OP_NotFound, base+idx, brk);
        haveKey = 0;
      }
      pLevel->op = OP_Noop;
    }else if( pIdx==0 ){
      /* Case 2:  There was no usable index.  We must do a complete
      ** scan of the table.
      */
      int start;

      brk = pLevel->brk = sqliteVdbeMakeLabel(v);
      cont = pLevel->cont = sqliteVdbeMakeLabel(v);
      sqliteVdbeAddOp(v, OP_Rewind, base+idx, brk);
      start = sqliteVdbeCurrentAddr(v);
      pLevel->op = OP_Next;
      pLevel->p1 = base+idx;
      pLevel->p2 = start;
      haveKey = 0;
    }else{
      /* Case 3:  We do have a usable index in pIdx.
      */
      int start;


      for(j=0; j<pIdx->nColumn; j++){
        for(k=0; k<nExpr; k++){
          if( aExpr[k].p==0 ) continue;
          if( aExpr[k].idxLeft==idx 

             && (aExpr[k].prereqRight & loopMask)==aExpr[k].prereqRight 
             && aExpr[k].p->pLeft->iColumn==pIdx->aiColumn[j]
          ){
            sqliteExprCode(pParse, aExpr[k].p->pRight);
            aExpr[k].p = 0;
            break;
          }
          if( aExpr[k].idxRight==idx 

             && (aExpr[k].prereqLeft & loopMask)==aExpr[k].prereqLeft
             && aExpr[k].p->pRight->iColumn==pIdx->aiColumn[j]
          ){
            sqliteExprCode(pParse, aExpr[k].p->pLeft);
            aExpr[k].p = 0;
            break;
          }
        }
      }
      pLevel->iMem = pParse->nMem++;
      brk = pLevel->brk = sqliteVdbeMakeLabel(v);
      cont = pLevel->cont = sqliteVdbeMakeLabel(v);
      sqliteVdbeAddOp(v, OP_MakeKey, pIdx->nColumn, 0);

      sqliteVdbeAddOp(v, OP_MemStore, pLevel->iMem, 0);







      sqliteVdbeAddOp(v, OP_MoveTo, pLevel->iCur, brk);
      start = sqliteVdbeAddOp(v, OP_MemLoad, pLevel->iMem, 0);





























































































      sqliteVdbeAddOp(v, OP_IdxGT, pLevel->iCur, brk);

























































      sqliteVdbeAddOp(v, OP_IdxRecno, pLevel->iCur, 0);
      if( i==pTabList->nId-1 && pushKey ){
        haveKey = 1;
      }else{
        sqliteVdbeAddOp(v, OP_MoveTo, base+idx, 0);
        haveKey = 0;
      }



      pLevel->op = OP_Next;
      pLevel->p1 = pLevel->iCur;
      pLevel->p2 = start;
    }
    loopMask |= 1<<idx;

    /* Insert code to test every subexpression that can be completely







|







 







>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>







 







|







 







>







 







|
>

<
|
|
>
>
>
>
|
>
>
>
>
>
>


<
>
>
>
>

|







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










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





|
|
|
|
>
>
>
>
>
>
>



>







 







|











|
|


>
>
|



>








>












|
>
|
>
>
>
>
>
>
>


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







>
>
>







9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
..
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
...
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
...
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
...
253
254
255
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
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
...
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
**    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.26 2001/11/08 00:45:22 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.
................................................................................
    mask = exprTableUsage(base, p->pRight);
  }
  if( p->pLeft ){
    mask |= exprTableUsage(base, p->pLeft);
  }
  return mask;
}

/*
** Return TRUE if the given operator is one of the operators that is
** allowed for an indexable WHERE clause.  The allowed operators are
** "=", "<", ">", "<=", and ">=".
*/
static int allowedOp(int op){
  switch( op ){
    case TK_LT:
    case TK_LE:
    case TK_GT:
    case TK_GE:
    case TK_EQ:
      return 1;
    default:
      return 0;
  }
}

/*
** The input to this routine is an ExprInfo structure with only the
** "p" field filled in.  The job of this routine is to analyze the
** subexpression and populate all the other fields of the ExprInfo
** structure.
**
................................................................................
static void exprAnalyze(int base, ExprInfo *pInfo){
  Expr *pExpr = pInfo->p;
  pInfo->prereqLeft = exprTableUsage(base, pExpr->pLeft);
  pInfo->prereqRight = exprTableUsage(base, pExpr->pRight);
  pInfo->indexable = 0;
  pInfo->idxLeft = -1;
  pInfo->idxRight = -1;
  if( allowedOp(pExpr->op) && (pInfo->prereqRight & pInfo->prereqLeft)==0 ){
    if( pExpr->pRight->op==TK_COLUMN ){
      pInfo->idxRight = pExpr->pRight->iTable - base;
      pInfo->indexable = 1;
    }
    if( pExpr->pLeft->op==TK_COLUMN ){
      pInfo->idxLeft = pExpr->pLeft->iTable - base;
      pInfo->indexable = 1;
................................................................................
  loopMask = 0;
  for(i=0; i<pTabList->nId && i<ARRAYSIZE(aDirect); i++){
    int j;
    int idx = aOrder[i];
    Table *pTab = pTabList->a[idx].pTab;
    Index *pIdx;
    Index *pBestIdx = 0;
    int bestScore = 0;

    /* Check to see if there is an expression that uses only the
    ** ROWID field of this table.  If so, set aDirect[i] to 1.
    ** If not, set aDirect[i] to 0.
    */
    aDirect[i] = 0;
    for(j=0; j<nExpr; j++){
................................................................................
    if( aDirect[i] ){
      loopMask |= 1<<idx;
      pWInfo->a[i].pIdx = 0;
      continue;
    }

    /* Do a search for usable indices.  Leave pBestIdx pointing to
    ** the "best" index.  pBestIdx is left set to NULL if no indices
    ** are usable.
    **

    ** The best index is determined as follows.  For each of the
    ** left-most terms that is fixed by an equality operator, add
    ** 4 to the score.  The right-most term of the index may be
    ** constrained by an inequality.  Add 1 if for an "x<..." constraint
    ** and add 2 for an "x>..." constraint.  Chose the index that
    ** gives the best score.
    **
    ** This scoring system is designed so that the score can later be
    ** used to determine how the index is used.  If the score&3 is 0
    ** then all constraints are equalities.  If score&1 is not 0 then
    ** there is an inequality used as a termination key.  (ex: "x<...")
    ** If score&2 is not 0 then there is an inequality used as the
    ** start key.  (ex: "x>...");
    */
    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->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_EQ: {
                  eqMask |= 1<<k;
                  break;
                }
                case TK_LE:
                case TK_LT: {
                  ltMask |= 1<<k;
                  break;
                }
                case TK_GE:
                case TK_GT: {
                  gtMask |= 1<<k;
                  break;
                }
                default: {
                  /* CANT_HAPPEN */
                  assert( 0 );
                  break;
                }
              }
              break;
            }
          }
        }
        if( aExpr[j].idxRight==idx 
             && (aExpr[j].prereqLeft & loopMask)==aExpr[j].prereqLeft ){
          int iColumn = aExpr[j].p->pRight->iColumn;
          int k;
          for(k=0; k<pIdx->nColumn; k++){
            if( pIdx->aiColumn[k]==iColumn ){
              switch( aExpr[j].p->op ){
                case TK_EQ: {
                  eqMask |= 1<<k;
                  break;
                }
                case TK_LE:
                case TK_LT: {
                  gtMask |= 1<<k;
                  break;
                }
                case TK_GE:
                case TK_GT: {
                  ltMask |= 1<<k;
                  break;
                }
                default: {
                  /* CANT_HAPPEN */
                  assert( 0 );
                  break;
                }
              }
              break;
            }
          }
        }
      }
      for(nEq=0; nEq<pIdx->nColumn; nEq++){
        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;
    loopMask |= 1<<idx;
    if( pBestIdx ){
      pWInfo->a[i].iCur = nCur++;
    }
  }

  /* Open all tables in the pTabList and all indices used by those tables.
................................................................................
      }else{
        sqliteVdbeAddOp(v, OP_NotFound, base+idx, brk);
        haveKey = 0;
      }
      pLevel->op = OP_Noop;
    }else if( pIdx==0 ){
      /* Case 2:  There was no usable index.  We must do a complete
      **          scan of the entire database table.
      */
      int start;

      brk = pLevel->brk = sqliteVdbeMakeLabel(v);
      cont = pLevel->cont = sqliteVdbeMakeLabel(v);
      sqliteVdbeAddOp(v, OP_Rewind, base+idx, brk);
      start = sqliteVdbeCurrentAddr(v);
      pLevel->op = OP_Next;
      pLevel->p1 = base+idx;
      pLevel->p2 = start;
      haveKey = 0;
    }else if( pLevel->score%4==0 ){
      /* Case 3:  All index constraints are equality operators.
      */
      int start;
      int testOp;
      int nColumn = pLevel->score/4;
      for(j=0; j<nColumn; j++){
        for(k=0; k<nExpr; k++){
          if( aExpr[k].p==0 ) continue;
          if( aExpr[k].idxLeft==idx 
             && aExpr[k].p->op==TK_EQ
             && (aExpr[k].prereqRight & loopMask)==aExpr[k].prereqRight 
             && aExpr[k].p->pLeft->iColumn==pIdx->aiColumn[j]
          ){
            sqliteExprCode(pParse, aExpr[k].p->pRight);
            aExpr[k].p = 0;
            break;
          }
          if( aExpr[k].idxRight==idx 
             && aExpr[k].p->op==TK_EQ
             && (aExpr[k].prereqLeft & loopMask)==aExpr[k].prereqLeft
             && aExpr[k].p->pRight->iColumn==pIdx->aiColumn[j]
          ){
            sqliteExprCode(pParse, aExpr[k].p->pLeft);
            aExpr[k].p = 0;
            break;
          }
        }
      }
      pLevel->iMem = pParse->nMem++;
      brk = pLevel->brk = sqliteVdbeMakeLabel(v);
      cont = pLevel->cont = sqliteVdbeMakeLabel(v);
      sqliteVdbeAddOp(v, OP_MakeKey, nColumn, 0);
      if( nColumn==pIdx->nColumn ){
        sqliteVdbeAddOp(v, OP_MemStore, pLevel->iMem, 0);
        testOp = OP_IdxGT;
      }else{
        sqliteVdbeAddOp(v, OP_Dup, 0, 0);
        sqliteVdbeAddOp(v, OP_IncrKey, 0, 0);
        sqliteVdbeAddOp(v, OP_MemStore, pLevel->iMem, 1);
        testOp = OP_IdxGE;
      }
      sqliteVdbeAddOp(v, OP_MoveTo, pLevel->iCur, brk);
      start = sqliteVdbeAddOp(v, OP_MemLoad, pLevel->iMem, 0);
      sqliteVdbeAddOp(v, testOp, pLevel->iCur, brk);
      sqliteVdbeAddOp(v, OP_IdxRecno, pLevel->iCur, 0);
      if( i==pTabList->nId-1 && pushKey ){
        haveKey = 1;
      }else{
        sqliteVdbeAddOp(v, OP_MoveTo, base+idx, 0);
        haveKey = 0;
      }
      pLevel->op = OP_Next;
      pLevel->p1 = pLevel->iCur;
      pLevel->p2 = start;
    }else{
      /* Case 4: The contraints on the right-most index field are
      **         inequalities.
      */
      int score = pLevel->score;
      int nEqColumn = score/4;
      int start;
      int leFlag, geFlag;
      int testOp;

      /* Evaluate the equality constraints
      */
      for(j=0; j<nEqColumn; j++){
        for(k=0; k<nExpr; k++){
          if( aExpr[k].p==0 ) continue;
          if( aExpr[k].idxLeft==idx 
             && aExpr[k].p->op==TK_EQ
             && (aExpr[k].prereqRight & loopMask)==aExpr[k].prereqRight 
             && aExpr[k].p->pLeft->iColumn==pIdx->aiColumn[j]
          ){
            sqliteExprCode(pParse, aExpr[k].p->pRight);
            aExpr[k].p = 0;
            break;
          }
          if( aExpr[k].idxRight==idx 
             && aExpr[k].p->op==TK_EQ
             && (aExpr[k].prereqLeft & loopMask)==aExpr[k].prereqLeft
             && aExpr[k].p->pRight->iColumn==pIdx->aiColumn[j]
          ){
            sqliteExprCode(pParse, aExpr[k].p->pLeft);
            aExpr[k].p = 0;
            break;
          }
        }
      }

      /* Duplicate the equality contraint values because they will all be
      ** used twice: once to make the termination key and once to make the
      ** start key.
      */
      for(j=0; j<nEqColumn; j++){
        sqliteVdbeAddOp(v, OP_Dup, nEqColumn-1, 0);
      }

      /* Generate the termination key.  This is the key value that
      ** will end the search.  There is no termination key if there
      ** are no equality contraints and no "X<..." constraint.
      */
      if( (score & 1)!=0 ){
        for(k=0; k<nExpr; k++){
          Expr *pExpr = aExpr[k].p;
          if( pExpr==0 ) continue;
          if( aExpr[k].idxLeft==idx 
             && (pExpr->op==TK_LT || pExpr->op==TK_LE)
             && (aExpr[k].prereqRight & loopMask)==aExpr[k].prereqRight 
             && pExpr->pLeft->iColumn==pIdx->aiColumn[j]
          ){
            sqliteExprCode(pParse, pExpr->pRight);
            leFlag = pExpr->op==TK_LE;
            aExpr[k].p = 0;
            break;
          }
          if( aExpr[k].idxRight==idx 
             && (pExpr->op==TK_GT || pExpr->op==TK_GE)
             && (aExpr[k].prereqLeft & loopMask)==aExpr[k].prereqLeft
             && pExpr->pRight->iColumn==pIdx->aiColumn[j]
          ){
            sqliteExprCode(pParse, pExpr->pLeft);
            leFlag = pExpr->op==TK_GE;
            aExpr[k].p = 0;
            break;
          }
        }
        testOp = OP_IdxGE;
      }else{
        testOp = nEqColumn>0 ? OP_IdxGE : OP_Noop;
        leFlag = 1;
      }
      if( testOp!=OP_Noop ){
        pLevel->iMem = pParse->nMem++;
        sqliteVdbeAddOp(v, OP_MakeKey, nEqColumn + (score & 1), 0);
        if( leFlag ){
          sqliteVdbeAddOp(v, OP_IncrKey, 0, 0);
        }
        sqliteVdbeAddOp(v, OP_MemStore, pLevel->iMem, 1);
      }

      /* Generate the start key.  This is the key that defines the lower
      ** bound on the search.  There is no start key if there are not
      ** equality constraints and if there is no "X>..." constraint.  In
      ** that case, generate a "Rewind" instruction in place of the
      ** start key search.
      */
      if( (score & 2)!=0 ){
        for(k=0; k<nExpr; k++){
          Expr *pExpr = aExpr[k].p;
          if( pExpr==0 ) continue;
          if( aExpr[k].idxLeft==idx 
             && (pExpr->op==TK_GT || pExpr->op==TK_GE)
             && (aExpr[k].prereqRight & loopMask)==aExpr[k].prereqRight 
             && pExpr->pLeft->iColumn==pIdx->aiColumn[j]
          ){
            sqliteExprCode(pParse, pExpr->pRight);
            geFlag = pExpr->op==TK_GE;
            aExpr[k].p = 0;
            break;
          }
          if( aExpr[k].idxRight==idx 
             && (pExpr->op==TK_LT || pExpr->op==TK_LE)
             && (aExpr[k].prereqLeft & loopMask)==aExpr[k].prereqLeft
             && pExpr->pRight->iColumn==pIdx->aiColumn[j]
          ){
            sqliteExprCode(pParse, pExpr->pLeft);
            geFlag = pExpr->op==TK_LE;
            aExpr[k].p = 0;
            break;
          }
        }
      }
      brk = pLevel->brk = sqliteVdbeMakeLabel(v);
      cont = pLevel->cont = sqliteVdbeMakeLabel(v);
      if( nEqColumn>0 || (score&2)!=0 ){
        sqliteVdbeAddOp(v, OP_MakeKey, nEqColumn + ((score&2)!=0), 0);
        if( !geFlag ){
          sqliteVdbeAddOp(v, OP_IncrKey, 0, 0);
        }
        sqliteVdbeAddOp(v, OP_MoveTo, pLevel->iCur, brk);
      }else{
        sqliteVdbeAddOp(v, OP_Rewind, pLevel->iCur, brk);
      }

      /* Generate the the top of the loop.  If there is a termination
      ** key we have to test for that key and abort at the top of the
      ** loop.
      */
      start = sqliteVdbeCurrentAddr(v);
      if( testOp!=OP_Noop ){
        sqliteVdbeAddOp(v, OP_MemLoad, pLevel->iMem, 0);
        sqliteVdbeAddOp(v, testOp, pLevel->iCur, brk);
      }
      sqliteVdbeAddOp(v, OP_IdxRecno, pLevel->iCur, 0);
      if( i==pTabList->nId-1 && pushKey ){
        haveKey = 1;
      }else{
        sqliteVdbeAddOp(v, OP_MoveTo, base+idx, 0);
        haveKey = 0;
      }

      /* Record the instruction used to terminate the loop.
      */
      pLevel->op = OP_Next;
      pLevel->p1 = pLevel->iCur;
      pLevel->p2 = start;
    }
    loopMask |= 1<<idx;

    /* Insert code to test every subexpression that can be completely

Changes to test/index.test.

7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
...
313
314
315
316
317
318
319
320

321
322
323
#    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 CREATE INDEX statement.
#
# $Id: index.test,v 1.14 2001/09/27 15:11:55 drh Exp $

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

# Create a basic index and verify it is added to sqlite_master
#
do_test index-1.1 {
................................................................................
      c float,
      PRIMARY KEY(b)
    );
  }
  for {set i 1} {$i<=50} {incr i} {
    execsql "INSERT INTO t3 VALUES('x${i}x',$i,0.$i)"
  }
  execsql {SELECT c, fcnt() FROM t3 WHERE b==10}

} {0.10 1}

finish_test







|







 







|
>
|


7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
...
313
314
315
316
317
318
319
320
321
322
323
324
#    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 CREATE INDEX statement.
#
# $Id: index.test,v 1.15 2001/11/08 00:45:22 drh Exp $

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

# Create a basic index and verify it is added to sqlite_master
#
do_test index-1.1 {
................................................................................
      c float,
      PRIMARY KEY(b)
    );
  }
  for {set i 1} {$i<=50} {incr i} {
    execsql "INSERT INTO t3 VALUES('x${i}x',$i,0.$i)"
  }
  set sqlite_search_count 0
  concat [execsql {SELECT c FROM t3 WHERE b==10}] $sqlite_search_count
} {0.10 3}

finish_test

Changes to test/rowid.test.

8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
...
234
235
236
237
238
239
240

241
242
243
244

245
246

247
248
249
250

251
252
253
254
255
256
257
258
259
260
261
262
263
264
#    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 magic ROWID column that is
# found on all tables.
#
# $Id: rowid.test,v 1.5 2001/09/18 02:02:23 drh Exp $

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

# Basic ROWID functionality tests.
#
do_test rowid-1.1 {
................................................................................
  execsql {SELECT t2.y FROM t2, t1 WHERE t1.x==4 AND t1._rowid_==t2.rowid}
} {256}
do_test rowid-4.4.2 {
  execsql {SELECT t2.y FROM t2, t1 WHERE t2.rowid==t1.oid AND 4==t1.x}
} {256}
do_test rowid-4.5 {
  execsql {CREATE INDEX idxt2 ON t2(y)}

  execsql {
    SELECT t1.x, fcnt() FROM t2, t1 
    WHERE t2.y==256 AND t1.rowid==t2.rowid
  }

} {4 1}
do_test rowid-4.5.1 {

  execsql {
    SELECT t1.x, fcnt() FROM t2, t1 
    WHERE t1.OID==t2.rowid AND t2.y==81
  }

} {3 1}
do_test rowid-4.6 {
  execsql {
    SELECT t1.x FROM t1, t2
    WHERE t2.y==256 AND t1.rowid==t2.rowid
  }
} {4}

do_test rowid-5.1 {
  execsql {DELETE FROM t1 WHERE _rowid_ IN (SELECT oid FROM t1 WHERE x>8)}
  execsql {SELECT max(x) FROM t1}
} {8}

finish_test







|







 







>
|
|

<
>
|

>
|
|

<
>
|













8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
...
234
235
236
237
238
239
240
241
242
243
244

245
246
247
248
249
250
251

252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
#    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 magic ROWID column that is
# found on all tables.
#
# $Id: rowid.test,v 1.6 2001/11/08 00:45:22 drh Exp $

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

# Basic ROWID functionality tests.
#
do_test rowid-1.1 {
................................................................................
  execsql {SELECT t2.y FROM t2, t1 WHERE t1.x==4 AND t1._rowid_==t2.rowid}
} {256}
do_test rowid-4.4.2 {
  execsql {SELECT t2.y FROM t2, t1 WHERE t2.rowid==t1.oid AND 4==t1.x}
} {256}
do_test rowid-4.5 {
  execsql {CREATE INDEX idxt2 ON t2(y)}
  set sqlite_search_count 0
  concat [execsql {
    SELECT t1.x FROM t2, t1 
    WHERE t2.y==256 AND t1.rowid==t2.rowid

  }] $sqlite_search_count
} {4 3}
do_test rowid-4.5.1 {
  set sqlite_search_count 0
  concat [execsql {
    SELECT t1.x FROM t2, t1 
    WHERE t1.OID==t2.rowid AND t2.y==81

  }] $sqlite_search_count
} {3 3}
do_test rowid-4.6 {
  execsql {
    SELECT t1.x FROM t1, t2
    WHERE t2.y==256 AND t1.rowid==t2.rowid
  }
} {4}

do_test rowid-5.1 {
  execsql {DELETE FROM t1 WHERE _rowid_ IN (SELECT oid FROM t1 WHERE x>8)}
  execsql {SELECT max(x) FROM t1}
} {8}

finish_test

Changes to test/select2.test.

7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
..
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114

115
116
117
118
119
120
121
122
#    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 SELECT statement.
#
# $Id: select2.test,v 1.14 2001/09/16 00:13:28 drh Exp $

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

# Create a table with some data
#
execsql {CREATE TABLE tbl1(f1 int, f2 int)}
................................................................................
do_test select2-3.2b {
  execsql {SELECT f1 FROM tbl2 WHERE 1000=f2}
} {500}
do_test select2-3.2c {
  execsql {SELECT f1 FROM tbl2 WHERE f2=1000}
} {500}
do_test select2-3.2d {
  execsql {SELECT fcnt() FROM tbl2 WHERE 1000=f2}
} {1}
do_test select2-3.2e {
  execsql {SELECT fcnt() FROM tbl2 WHERE f2=1000}
} {1}

# omit the time-dependent tests
#
do_probtest select2-3.2f {
  set t1 [lindex [time {execsql {SELECT f1 FROM tbl2 WHERE 1000=f2}} 1] 0]
  set t2 [lindex [time {execsql {SELECT f1 FROM tbl2 WHERE f2=1000}} 1] 0]
  expr {$t1*0.7<$t2 && $t2*0.7<$t1}
} {1}

# Make sure queries run faster with an index than without
#
do_probtest select2-3.3 {
  set t1 [lindex [time {execsql {SELECT f1 from tbl2 WHERE f2==2000}} 1] 0]
  execsql {DROP INDEX idx1}

  set t2 [lindex [time {execsql {SELECT f1 FROM tbl2 WHERE f2==2000}} 1] 0]
  expr {$t1*10 < $t2}
} {1}
do_probtest select2-3.4 {
  expr {[execsql {SELECT fcnt() FROM tbl2 WHERE f2==2000}]>10}
} {1}

finish_test







|







 







|
|
|
|
|
|
|
|
|
<
<
<
<



|
<

>
|
|
|
<
<
<


7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
..
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104




105
106
107
108

109
110
111
112
113



114
115
#    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 SELECT statement.
#
# $Id: select2.test,v 1.15 2001/11/08 00:45:22 drh Exp $

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

# Create a table with some data
#
execsql {CREATE TABLE tbl1(f1 int, f2 int)}
................................................................................
do_test select2-3.2b {
  execsql {SELECT f1 FROM tbl2 WHERE 1000=f2}
} {500}
do_test select2-3.2c {
  execsql {SELECT f1 FROM tbl2 WHERE f2=1000}
} {500}
do_test select2-3.2d {
  set sqlite_search_count 0
  execsql {SELECT * FROM tbl2 WHERE 1000=f2}
  set sqlite_search_count
} {3}
do_test select2-3.2e {
  set sqlite_search_count 0
  execsql {SELECT * FROM tbl2 WHERE f2=1000}
  set sqlite_search_count
} {3}





# Make sure queries run faster with an index than without
#
do_test select2-3.3 {

  execsql {DROP INDEX idx1}
  set sqlite_search_count 0
  execsql {SELECT f1 FROM tbl2 WHERE f2==2000}
  set sqlite_search_count
} {29999}




finish_test

Changes to test/where.test.

7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
..
35
36
37
38
39
40
41







42
43
44
45
46



47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81






















































































82
83
84
85

86
87
88
89
90
91

92
93
94
95
96
97

98
99
100
101
102
103

104
105
106
107
108
109

110
111
112
113
114
115

116
117
118
119
120
121

122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
#    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.3 2001/09/16 00:13:28 drh Exp $

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

# Build some test data
#
do_test where-1.0 {
................................................................................
    CREATE INDEX i1xy ON t1(x,y);
    CREATE INDEX i2p ON t2(p);
    CREATE INDEX i2r ON t2(r);
    CREATE INDEX i2qs ON t2(q, s);
  }
} {}








# Verify that queries use an index.  We are using the special "fcnt(*)"
# function to verify the results.  fcnt(*) returns the number of Fetch
# operations that have occurred up to the point where fcnt(*) is invoked.
# By verifing that fcnt(*) returns a small number we know that an index
# was used instead of an exhaustive search.



#
do_test where-1.1 {
  execsql {SELECT x, y, fcnt(*) FROM t1 WHERE w=10}
} {3 121 1}
do_test where-1.2 {
  execsql {SELECT x, y, fcnt(*) FROM t1 WHERE w=11}
} {3 144 1}
do_test where-1.3 {
  execsql {SELECT x, y, fcnt(*) FROM t1 WHERE 11=w}
} {3 144 1}
do_test where-1.4 {
  execsql {SELECT x, y, fcnt(*) FROM t1 WHERE 11=w AND x>2}
} {3 144 1}
do_test where-1.5 {
  execsql {SELECT x, y, fcnt(*) FROM t1 WHERE y<200 AND w=11 AND x>2}
} {3 144 1}
do_test where-1.6 {
  execsql {SELECT x, y, fcnt(*) FROM t1 WHERE y<200 AND x>2 AND w=11}
} {3 144 1}
do_test where-1.7 {
  execsql {SELECT x, y, fcnt(*) FROM t1 WHERE w=11 AND y<200 AND x>2}
} {3 144 1}
do_test where-1.8 {
  execsql {SELECT x, y, fcnt(*) FROM t1 WHERE w>10 AND y=144 AND x=3}
} {3 144 1}
do_test where-1.9 {
  execsql {SELECT x, y, fcnt(*) FROM t1 WHERE y=144 AND w>10 AND x=3}
} {3 144 1}
do_test where-1.10 {
  execsql {SELECT x, y, fcnt(*) FROM t1 WHERE x=3 AND w>=10 AND y=121}
} {3 121 1}
do_test where-1.11 {
  execsql {SELECT x, y, fcnt(*) FROM t1 WHERE x=3 AND y=100 AND w<10}
} {3 100 1}























































































# Do the same kind of thing except use a join as the data source.
#
do_test where-2.1 {
  execsql {

    SELECT w, p, fcnt(*) FROM t2, t1
    WHERE x=q AND y=s AND r=8977
  }
} {34 67 2}
do_test where-2.2 {
  execsql {

    SELECT w, p, fcnt(*) FROM t2, t1
    WHERE x=q AND s=y AND r=8977
  }
} {34 67 2}
do_test where-2.3 {
  execsql {

    SELECT w, p, fcnt(*) FROM t2, t1
    WHERE x=q AND s=y AND r=8977 AND w>10
  }
} {34 67 2}
do_test where-2.4 {
  execsql {

    SELECT w, p, fcnt(*) FROM t2, t1
    WHERE p<80 AND x=q AND s=y AND r=8977 AND w>10
  }
} {34 67 2}
do_test where-2.5 {
  execsql {

    SELECT w, p, fcnt(*) FROM t2, t1
    WHERE p<80 AND x=q AND 8977=r AND s=y AND w>10
  }
} {34 67 2}
do_test where-2.6 {
  execsql {

    SELECT w, p, fcnt(*) FROM t2, t1
    WHERE x=q AND p=77 AND s=y AND w>5
  }
} {24 77 2}
do_test where-2.7 {
  execsql {

    SELECT w, p, fcnt(*) FROM t1, t2
    WHERE x=q AND p>77 AND s=y AND w=5
  }
} {5 96 2}

# Lets do a 3-way join.
#
do_test where-3.1 {
  execsql {
    SELECT A.w, B.p, C.w, fcnt(*) FROM t1 as A, t2 as B, t1 as C
    WHERE C.w=101-B.p AND B.r=10202-A.y AND A.w=11
  }
} {11 90 11 3}
do_test where-3.2 {
  execsql {
    SELECT A.w, B.p, C.w, fcnt(*) FROM t1 as A, t2 as B, t1 as C
    WHERE C.w=101-B.p AND B.r=10202-A.y AND A.w=12
  }
} {12 89 12 3}
do_test where-3.3 {
  execsql {
    SELECT A.w, B.p, C.w, fcnt(*) FROM t1 as A, t2 as B, t1 as C
    WHERE A.w=15 AND B.p=C.w AND B.r=10202-A.y
  }
} {15 86 86 3}

finish_test







|







 







>
>
>
>
>
>
>
|
<
<
<
<
>
>
>


|
|

|
|

|
|

|
|

|
|

|
|

|
|

|
|

|
|

|
|

|
|

>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>



<
>
|


|

<
>
|


|

<
>
|


|

<
>
|


|

<
>
|


|

<
>
|


|

<
>
|


|




|
|


|

|
|


|

|
|


|


7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
..
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49




50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176

177
178
179
180
181
182

183
184
185
186
187
188

189
190
191
192
193
194

195
196
197
198
199
200

201
202
203
204
205
206

207
208
209
210
211
212

213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
#    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.4 2001/11/08 00:45:22 drh Exp $

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

# Build some test data
#
do_test where-1.0 {
................................................................................
    CREATE INDEX i1xy ON t1(x,y);
    CREATE INDEX i2p ON t2(p);
    CREATE INDEX i2r ON t2(r);
    CREATE INDEX i2qs ON t2(q, s);
  }
} {}

# Do an SQL statement.  Append the search count to the end of the result.
#
proc count sql {
  set ::sqlite_search_count 0
  return [concat [execsql $sql] $::sqlite_search_count]
}

# Verify that queries use an index.  We are using the special variable




# "sqlite_search_count" which tallys the number of executions of MoveTo
# and Next operators in the VDBE.  By verifing that the search count is
# small we can be assured that indices are being used properly.
#
do_test where-1.1 {
  count {SELECT x, y FROM t1 WHERE w=10}
} {3 121 3}
do_test where-1.2 {
  count {SELECT x, y FROM t1 WHERE w=11}
} {3 144 3}
do_test where-1.3 {
  count {SELECT x, y FROM t1 WHERE 11=w}
} {3 144 3}
do_test where-1.4 {
  count {SELECT x, y FROM t1 WHERE 11=w AND x>2}
} {3 144 3}
do_test where-1.5 {
  count {SELECT x, y FROM t1 WHERE y<200 AND w=11 AND x>2}
} {3 144 3}
do_test where-1.6 {
  count {SELECT x, y FROM t1 WHERE y<200 AND x>2 AND w=11}
} {3 144 3}
do_test where-1.7 {
  count {SELECT x, y FROM t1 WHERE w=11 AND y<200 AND x>2}
} {3 144 3}
do_test where-1.8 {
  count {SELECT x, y FROM t1 WHERE w>10 AND y=144 AND x=3}
} {3 144 3}
do_test where-1.9 {
  count {SELECT x, y FROM t1 WHERE y=144 AND w>10 AND x=3}
} {3 144 3}
do_test where-1.10 {
  count {SELECT x, y FROM t1 WHERE x=3 AND w>=10 AND y=121}
} {3 121 3}
do_test where-1.11 {
  count {SELECT x, y FROM t1 WHERE x=3 AND y=100 AND w<10}
} {3 100 3}

# New for SQLite version 2.1: Verify that that inequality constraints
# are used correctly.
#
do_test where-1.12 {
  count {SELECT w FROM t1 WHERE x=3 AND y<100}
} {8 3}
do_test where-1.13 {
  count {SELECT w FROM t1 WHERE x=3 AND 100>y}
} {8 3}
do_test where-1.14 {
  count {SELECT w FROM t1 WHERE 3=x AND y<100}
} {8 3}
do_test where-1.15 {
  count {SELECT w FROM t1 WHERE 3=x AND 100>y}
} {8 3}
do_test where-1.16 {
  count {SELECT w FROM t1 WHERE x=3 AND y<=100}
} {8 9 5}
do_test where-1.17 {
  count {SELECT w FROM t1 WHERE x=3 AND 100>=y}
} {8 9 5}
do_test where-1.18 {
  count {SELECT w FROM t1 WHERE x=3 AND y>225}
} {15 3}
do_test where-1.19 {
  count {SELECT w FROM t1 WHERE x=3 AND 225<y}
} {15 3}
do_test where-1.20 {
  count {SELECT w FROM t1 WHERE x=3 AND y>=225}
} {14 15 5}
do_test where-1.21 {
  count {SELECT w FROM t1 WHERE x=3 AND 225<=y}
} {14 15 5}
do_test where-1.22 {
  count {SELECT w FROM t1 WHERE x=3 AND y>121 AND y<196}
} {11 12 5}
do_test where-1.23 {
  count {SELECT w FROM t1 WHERE x=3 AND y>=121 AND y<=196}
} {10 11 12 13 9}
do_test where-1.24 {
  count {SELECT w FROM t1 WHERE x=3 AND 121<y AND 196>y}
} {11 12 5}
do_test where-1.25 {
  count {SELECT w FROM t1 WHERE x=3 AND 121<=y AND 196>=y}
} {10 11 12 13 9}

# Need to work on optimizing the BETWEEN operator.  
#
# do_test where-1.26 {
#   count {SELECT w FROM t1 WHERE x=3 AND y BETWEEN 121 AND 196}
# } {10 11 12 13 9}

do_test where-1.27 {
  count {SELECT w FROM t1 WHERE x=3 AND y+1==122}
} {10 17}
do_test where-1.28 {
  count {SELECT w FROM t1 WHERE x+1=4 AND y+1==122}
} {10 99}
do_test where-1.29 {
  count {SELECT w FROM t1 WHERE y==121}
} {10 99}


do_test where-1.30 {
  count {SELECT w FROM t1 WHERE w>97}
} {98 99 100 6}
do_test where-1.31 {
  count {SELECT w FROM t1 WHERE w>=97}
} {97 98 99 100 8}
do_test where-1.33 {
  count {SELECT w FROM t1 WHERE w==97}
} {97 3}
do_test where-1.34 {
  count {SELECT w FROM t1 WHERE w+1==98}
} {97 99}
do_test where-1.35 {
  count {SELECT w FROM t1 WHERE w<3}
} {1 2 4}
do_test where-1.36 {
  count {SELECT w FROM t1 WHERE w<=3}
} {1 2 3 6}
do_test where-1.37 {
  count {SELECT w FROM t1 WHERE w+1<=4}
} {1 2 3 99}


# Do the same kind of thing except use a join as the data source.
#
do_test where-2.1 {

  count {
    SELECT w, p FROM t2, t1
    WHERE x=q AND y=s AND r=8977
  }
} {34 67 6}
do_test where-2.2 {

  count {
    SELECT w, p FROM t2, t1
    WHERE x=q AND s=y AND r=8977
  }
} {34 67 6}
do_test where-2.3 {

  count {
    SELECT w, p FROM t2, t1
    WHERE x=q AND s=y AND r=8977 AND w>10
  }
} {34 67 6}
do_test where-2.4 {

  count {
    SELECT w, p FROM t2, t1
    WHERE p<80 AND x=q AND s=y AND r=8977 AND w>10
  }
} {34 67 6}
do_test where-2.5 {

  count {
    SELECT w, p FROM t2, t1
    WHERE p<80 AND x=q AND 8977=r AND s=y AND w>10
  }
} {34 67 6}
do_test where-2.6 {

  count {
    SELECT w, p FROM t2, t1
    WHERE x=q AND p=77 AND s=y AND w>5
  }
} {24 77 6}
do_test where-2.7 {

  count {
    SELECT w, p FROM t1, t2
    WHERE x=q AND p>77 AND s=y AND w=5
  }
} {5 96 6}

# Lets do a 3-way join.
#
do_test where-3.1 {
  count {
    SELECT A.w, B.p, C.w FROM t1 as A, t2 as B, t1 as C
    WHERE C.w=101-B.p AND B.r=10202-A.y AND A.w=11
  }
} {11 90 11 9}
do_test where-3.2 {
  count {
    SELECT A.w, B.p, C.w FROM t1 as A, t2 as B, t1 as C
    WHERE C.w=101-B.p AND B.r=10202-A.y AND A.w=12
  }
} {12 89 12 9}
do_test where-3.3 {
  count {
    SELECT A.w, B.p, C.w FROM t1 as A, t2 as B, t1 as C
    WHERE A.w=15 AND B.p=C.w AND B.r=10202-A.y
  }
} {15 86 86 9}

finish_test