SQLite

Check-in [98ca5580f5]
Login

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

Overview
Comment:Allow INDEXED BY and NOT INDEXED clauses in SELECT statements. (CVS 5766)
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 98ca5580f5acd2e7b3ce512520ec0527f221505e
User & Date: danielk1977 2008-10-06 05:32:19.000
Context
2008-10-06
11:29
Add a test to indexedby.test to check that automatic indexes (sqlite_autoindex_xxx) can be used with the INDEXED BY syntax. (CVS 5767) (check-in: bb51c34506 user: danielk1977 tags: trunk)
05:32
Allow INDEXED BY and NOT INDEXED clauses in SELECT statements. (CVS 5766) (check-in: 98ca5580f5 user: danielk1977 tags: trunk)
2008-10-03
09:10
Modifications to bind.test to account for different values of SQLITE_MAX_VARIABLE_NUMBER. Ticket #3409. (CVS 5765) (check-in: 1a91f3fd58 user: danielk1977 tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Changes to src/build.c.
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
**     CREATE INDEX
**     DROP INDEX
**     creating ID lists
**     BEGIN TRANSACTION
**     COMMIT
**     ROLLBACK
**
** $Id: build.c,v 1.496 2008/08/20 16:35:10 drh Exp $
*/
#include "sqliteInt.h"
#include <ctype.h>

/*
** This routine is called when a new SQL statement is beginning to
** be parsed.  Initialize the pParse structure as needed.







|







18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
**     CREATE INDEX
**     DROP INDEX
**     creating ID lists
**     BEGIN TRANSACTION
**     COMMIT
**     ROLLBACK
**
** $Id: build.c,v 1.497 2008/10/06 05:32:19 danielk1977 Exp $
*/
#include "sqliteInt.h"
#include <ctype.h>

/*
** This routine is called when a new SQL statement is beginning to
** be parsed.  Initialize the pParse structure as needed.
3048
3049
3050
3051
3052
3053
3054
3055
3056
3057
3058
3059
3060
3061
3062
    Token *pTemp = pDatabase;
    pDatabase = pTable;
    pTable = pTemp;
  }
  pItem->zName = sqlite3NameFromToken(db, pTable);
  pItem->zDatabase = sqlite3NameFromToken(db, pDatabase);
  pItem->iCursor = -1;
  pItem->isPopulated = 0;
  pList->nSrc++;
  return pList;
}

/*
** Assign cursors to all tables in a SrcList
*/







<







3048
3049
3050
3051
3052
3053
3054

3055
3056
3057
3058
3059
3060
3061
    Token *pTemp = pDatabase;
    pDatabase = pTable;
    pTable = pTemp;
  }
  pItem->zName = sqlite3NameFromToken(db, pTable);
  pItem->zDatabase = sqlite3NameFromToken(db, pDatabase);
  pItem->iCursor = -1;

  pList->nSrc++;
  return pList;
}

/*
** Assign cursors to all tables in a SrcList
*/
3082
3083
3084
3085
3086
3087
3088

3089
3090
3091
3092
3093
3094
3095
  int i;
  struct SrcList_item *pItem;
  if( pList==0 ) return;
  for(pItem=pList->a, i=0; i<pList->nSrc; i++, pItem++){
    sqlite3DbFree(db, pItem->zDatabase);
    sqlite3DbFree(db, pItem->zName);
    sqlite3DbFree(db, pItem->zAlias);

    sqlite3DeleteTable(pItem->pTab);
    sqlite3SelectDelete(db, pItem->pSelect);
    sqlite3ExprDelete(db, pItem->pOn);
    sqlite3IdListDelete(db, pItem->pUsing);
  }
  sqlite3DbFree(db, pList);
}







>







3081
3082
3083
3084
3085
3086
3087
3088
3089
3090
3091
3092
3093
3094
3095
  int i;
  struct SrcList_item *pItem;
  if( pList==0 ) return;
  for(pItem=pList->a, i=0; i<pList->nSrc; i++, pItem++){
    sqlite3DbFree(db, pItem->zDatabase);
    sqlite3DbFree(db, pItem->zName);
    sqlite3DbFree(db, pItem->zAlias);
    sqlite3DbFree(db, pItem->zIndex);
    sqlite3DeleteTable(pItem->pTab);
    sqlite3SelectDelete(db, pItem->pSelect);
    sqlite3ExprDelete(db, pItem->pOn);
    sqlite3IdListDelete(db, pItem->pUsing);
  }
  sqlite3DbFree(db, pList);
}
3112
3113
3114
3115
3116
3117
3118

3119
3120
3121
3122
3123
3124
3125
3126
3127
3128
3129
3130
3131
3132
3133
3134







3135
3136
3137
3138
3139
3140
3141
*/
SrcList *sqlite3SrcListAppendFromTerm(
  Parse *pParse,          /* Parsing context */
  SrcList *p,             /* The left part of the FROM clause already seen */
  Token *pTable,          /* Name of the table to add to the FROM clause */
  Token *pDatabase,       /* Name of the database containing pTable */
  Token *pAlias,          /* The right-hand side of the AS subexpression */

  Select *pSubquery,      /* A subquery used in place of a table name */
  Expr *pOn,              /* The ON clause of a join */
  IdList *pUsing          /* The USING clause of a join */
){
  struct SrcList_item *pItem;
  sqlite3 *db = pParse->db;
  p = sqlite3SrcListAppend(db, p, pTable, pDatabase);
  if( p==0 || p->nSrc==0 ){
    sqlite3ExprDelete(db, pOn);
    sqlite3IdListDelete(db, pUsing);
    sqlite3SelectDelete(db, pSubquery);
    return p;
  }
  pItem = &p->a[p->nSrc-1];
  if( pAlias && pAlias->n ){
    pItem->zAlias = sqlite3NameFromToken(db, pAlias);







  }
  pItem->pSelect = pSubquery;
  pItem->pOn = pOn;
  pItem->pUsing = pUsing;
  return p;
}








>
















>
>
>
>
>
>
>







3112
3113
3114
3115
3116
3117
3118
3119
3120
3121
3122
3123
3124
3125
3126
3127
3128
3129
3130
3131
3132
3133
3134
3135
3136
3137
3138
3139
3140
3141
3142
3143
3144
3145
3146
3147
3148
3149
*/
SrcList *sqlite3SrcListAppendFromTerm(
  Parse *pParse,          /* Parsing context */
  SrcList *p,             /* The left part of the FROM clause already seen */
  Token *pTable,          /* Name of the table to add to the FROM clause */
  Token *pDatabase,       /* Name of the database containing pTable */
  Token *pAlias,          /* The right-hand side of the AS subexpression */
  Token *pIndexedBy,      /* Token from INDEXED BY clause, if any */
  Select *pSubquery,      /* A subquery used in place of a table name */
  Expr *pOn,              /* The ON clause of a join */
  IdList *pUsing          /* The USING clause of a join */
){
  struct SrcList_item *pItem;
  sqlite3 *db = pParse->db;
  p = sqlite3SrcListAppend(db, p, pTable, pDatabase);
  if( p==0 || p->nSrc==0 ){
    sqlite3ExprDelete(db, pOn);
    sqlite3IdListDelete(db, pUsing);
    sqlite3SelectDelete(db, pSubquery);
    return p;
  }
  pItem = &p->a[p->nSrc-1];
  if( pAlias && pAlias->n ){
    pItem->zAlias = sqlite3NameFromToken(db, pAlias);
  }
  if( pIndexedBy && pIndexedBy->n==1 && !pIndexedBy->z ){
    /* A "NOT INDEXED" clause was supplied. See parse.y 
    ** construct "indexed_opt" for details. */
    pItem->notIndexed = 1;
  }else{
    pItem->zIndex = sqlite3NameFromToken(db, pIndexedBy);
  }
  pItem->pSelect = pSubquery;
  pItem->pOn = pOn;
  pItem->pUsing = pUsing;
  return p;
}

Changes to src/delete.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 file contains C code routines that are called by the parser
** in order to generate code for DELETE FROM statements.
**
** $Id: delete.c,v 1.175 2008/09/01 21:59:43 shane Exp $
*/
#include "sqliteInt.h"

/*
** Look up every table that is named in pSrc.  If any table is not found,
** add an error message to pParse->zErrMsg and return NULL.  If all tables
** are found, return a pointer to the last table.







|







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 file contains C code routines that are called by the parser
** in order to generate code for DELETE FROM statements.
**
** $Id: delete.c,v 1.176 2008/10/06 05:32:19 danielk1977 Exp $
*/
#include "sqliteInt.h"

/*
** Look up every table that is named in pSrc.  If any table is not found,
** add an error message to pParse->zErrMsg and return NULL.  If all tables
** are found, return a pointer to the last table.
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
  if( pWhere ){
    SrcList *pFrom;
    Token viewName;
    
    pWhere = sqlite3ExprDup(db, pWhere);
    viewName.z = (u8*)pView->zName;
    viewName.n = (unsigned int)strlen((const char*)viewName.z);
    pFrom = sqlite3SrcListAppendFromTerm(pParse, 0, 0, 0, &viewName, pDup, 0,0);
    pDup = sqlite3SelectNew(pParse, 0, pFrom, pWhere, 0, 0, 0, 0, 0, 0);
  }
  sqlite3SelectDestInit(&dest, SRT_EphemTab, iCur);
  sqlite3Select(pParse, pDup, &dest);
  sqlite3SelectDelete(db, pDup);
}
#endif /* !defined(SQLITE_OMIT_VIEW) && !defined(SQLITE_OMIT_TRIGGER) */







|







102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
  if( pWhere ){
    SrcList *pFrom;
    Token viewName;
    
    pWhere = sqlite3ExprDup(db, pWhere);
    viewName.z = (u8*)pView->zName;
    viewName.n = (unsigned int)strlen((const char*)viewName.z);
    pFrom = sqlite3SrcListAppendFromTerm(pParse, 0, 0, 0, &viewName,0,pDup,0,0);
    pDup = sqlite3SelectNew(pParse, 0, pFrom, pWhere, 0, 0, 0, 0, 0, 0);
  }
  sqlite3SelectDestInit(&dest, SRT_EphemTab, iCur);
  sqlite3Select(pParse, pDup, &dest);
  sqlite3SelectDelete(db, pDup);
}
#endif /* !defined(SQLITE_OMIT_VIEW) && !defined(SQLITE_OMIT_TRIGGER) */
Changes to src/expr.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 file contains routines used for analyzing expressions and
** for generating VDBE code that evaluates expressions in SQLite.
**
** $Id: expr.c,v 1.396 2008/10/02 16:42:07 danielk1977 Exp $
*/
#include "sqliteInt.h"
#include <ctype.h>

/*
** Return the 'affinity' of the expression pExpr if any.
**







|







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 file contains routines used for analyzing expressions and
** for generating VDBE code that evaluates expressions in SQLite.
**
** $Id: expr.c,v 1.397 2008/10/06 05:32:19 danielk1977 Exp $
*/
#include "sqliteInt.h"
#include <ctype.h>

/*
** Return the 'affinity' of the expression pExpr if any.
**
729
730
731
732
733
734
735



736
737
738
739
740
741
742
    Table *pTab;
    pNewItem->zDatabase = sqlite3DbStrDup(db, pOldItem->zDatabase);
    pNewItem->zName = sqlite3DbStrDup(db, pOldItem->zName);
    pNewItem->zAlias = sqlite3DbStrDup(db, pOldItem->zAlias);
    pNewItem->jointype = pOldItem->jointype;
    pNewItem->iCursor = pOldItem->iCursor;
    pNewItem->isPopulated = pOldItem->isPopulated;



    pTab = pNewItem->pTab = pOldItem->pTab;
    if( pTab ){
      pTab->nRef++;
    }
    pNewItem->pSelect = sqlite3SelectDup(db, pOldItem->pSelect);
    pNewItem->pOn = sqlite3ExprDup(db, pOldItem->pOn);
    pNewItem->pUsing = sqlite3IdListDup(db, pOldItem->pUsing);







>
>
>







729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
    Table *pTab;
    pNewItem->zDatabase = sqlite3DbStrDup(db, pOldItem->zDatabase);
    pNewItem->zName = sqlite3DbStrDup(db, pOldItem->zName);
    pNewItem->zAlias = sqlite3DbStrDup(db, pOldItem->zAlias);
    pNewItem->jointype = pOldItem->jointype;
    pNewItem->iCursor = pOldItem->iCursor;
    pNewItem->isPopulated = pOldItem->isPopulated;
    pNewItem->zIndex = sqlite3DbStrDup(db, pOldItem->zIndex);
    pNewItem->notIndexed = pOldItem->notIndexed;
    pNewItem->pIndex = pOldItem->pIndex;
    pTab = pNewItem->pTab = pOldItem->pTab;
    if( pTab ){
      pTab->nRef++;
    }
    pNewItem->pSelect = sqlite3SelectDup(db, pOldItem->pSelect);
    pNewItem->pOn = sqlite3ExprDup(db, pOldItem->pOn);
    pNewItem->pUsing = sqlite3IdListDup(db, pOldItem->pUsing);
Changes to src/parse.y.
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
**
*************************************************************************
** This file contains SQLite's grammar for SQL.  Process this file
** using the lemon parser generator to generate C code that runs
** the parser.  Lemon will also generate a header file containing
** numeric codes for all of the tokens.
**
** @(#) $Id: parse.y,v 1.252 2008/08/20 16:35:10 drh Exp $
*/

// All token codes are small integers with #defines that begin with "TK_"
%token_prefix TK_

// The type of the data attached to each token is Token.  This is also the
// default type for non-terminals.







|







10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
**
*************************************************************************
** This file contains SQLite's grammar for SQL.  Process this file
** using the lemon parser generator to generate C code that runs
** the parser.  Lemon will also generate a header file containing
** numeric codes for all of the tokens.
**
** @(#) $Id: parse.y,v 1.253 2008/10/06 05:32:19 danielk1977 Exp $
*/

// All token codes are small integers with #defines that begin with "TK_"
%token_prefix TK_

// The type of the data attached to each token is Token.  This is also the
// default type for non-terminals.
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
// in a SELECT statement.  "stl_prefix" is a prefix of this list.
//
stl_prefix(A) ::= seltablist(X) joinop(Y).    {
   A = X;
   if( A && A->nSrc>0 ) A->a[A->nSrc-1].jointype = Y;
}
stl_prefix(A) ::= .                           {A = 0;}
seltablist(A) ::= stl_prefix(X) nm(Y) dbnm(D) as(Z) on_opt(N) using_opt(U). {
  A = sqlite3SrcListAppendFromTerm(pParse,X,&Y,&D,&Z,0,N,U);
}
%ifndef SQLITE_OMIT_SUBQUERY
  seltablist(A) ::= stl_prefix(X) LP seltablist_paren(S) RP
                    as(Z) on_opt(N) using_opt(U). {
    A = sqlite3SrcListAppendFromTerm(pParse,X,0,0,&Z,S,N,U);
  }
  
  // A seltablist_paren nonterminal represents anything in a FROM that
  // is contained inside parentheses.  This can be either a subquery or
  // a grouping of table and subqueries.
  //
  %type seltablist_paren {Select*}







|
|




|







452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
// in a SELECT statement.  "stl_prefix" is a prefix of this list.
//
stl_prefix(A) ::= seltablist(X) joinop(Y).    {
   A = X;
   if( A && A->nSrc>0 ) A->a[A->nSrc-1].jointype = Y;
}
stl_prefix(A) ::= .                           {A = 0;}
seltablist(A) ::= stl_prefix(X) nm(Y) dbnm(D) as(Z) indexed_opt(I) on_opt(N) using_opt(U). {
  A = sqlite3SrcListAppendFromTerm(pParse,X,&Y,&D,&Z,&I,0,N,U);
}
%ifndef SQLITE_OMIT_SUBQUERY
  seltablist(A) ::= stl_prefix(X) LP seltablist_paren(S) RP
                    as(Z) on_opt(N) using_opt(U). {
    A = sqlite3SrcListAppendFromTerm(pParse,X,0,0,&Z,0,S,N,U);
  }
  
  // A seltablist_paren nonterminal represents anything in a FROM that
  // is contained inside parentheses.  This can be either a subquery or
  // a grouping of table and subqueries.
  //
  %type seltablist_paren {Select*}
494
495
496
497
498
499
500















501
502
503
504
505
506
507
joinop(X) ::= JOIN_KW(A) nm(B) nm(C) JOIN.
                                       { X = sqlite3JoinType(pParse,&A,&B,&C); }

%type on_opt {Expr*}
%destructor on_opt {sqlite3ExprDelete(pParse->db, $$);}
on_opt(N) ::= ON expr(E).   {N = E;}
on_opt(N) ::= .             {N = 0;}
















%type using_opt {IdList*}
%destructor using_opt {sqlite3IdListDelete(pParse->db, $$);}
using_opt(U) ::= USING LP inscollist(L) RP.  {U = L;}
using_opt(U) ::= .                        {U = 0;}









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







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
joinop(X) ::= JOIN_KW(A) nm(B) nm(C) JOIN.
                                       { X = sqlite3JoinType(pParse,&A,&B,&C); }

%type on_opt {Expr*}
%destructor on_opt {sqlite3ExprDelete(pParse->db, $$);}
on_opt(N) ::= ON expr(E).   {N = E;}
on_opt(N) ::= .             {N = 0;}

// Note that this block abuses the Token type just a little. If there is
// no "INDEXED BY" clause, the returned token is empty (z==0 && n==0). If
// there is an INDEXED BY clause, then the token is populated as per normal,
// with z pointing to the token data and n containing the number of bytes
// in the token.
//
// If there is a "NOT INDEXED" clause, then (z==0 && n==1), which is 
// normally illegal. The sqlite3SrcListAppendFromTerm() function 
// recognizes and interprets this as a special case.
//
%type indexed_opt {Token}
indexed_opt(A) ::= .                 {A.z=0; A.n=0;}
indexed_opt(A) ::= INDEXED BY nm(X). {A = X;}
indexed_opt(A) ::= NOT INDEXED.      {A.z=0; A.n=1;}

%type using_opt {IdList*}
%destructor using_opt {sqlite3IdListDelete(pParse->db, $$);}
using_opt(U) ::= USING LP inscollist(L) RP.  {U = L;}
using_opt(U) ::= .                        {U = 0;}


Changes to src/select.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 file contains C code routines that are called by the parser
** to handle SELECT statements in SQLite.
**
** $Id: select.c,v 1.476 2008/09/23 09:36:10 drh Exp $
*/
#include "sqliteInt.h"


/*
** Delete all the content of a Select structure but do not deallocate
** the select structure itself.







|







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 file contains C code routines that are called by the parser
** to handle SELECT statements in SQLite.
**
** $Id: select.c,v 1.477 2008/10/06 05:32:19 danielk1977 Exp $
*/
#include "sqliteInt.h"


/*
** Delete all the content of a Select structure but do not deallocate
** the select structure itself.
2978
2979
2980
2981
2982
2983
2984















2985
2986
2987
2988
2989
2990
2991
        if( pFrom->pSelect==0 ){
          pFrom->pSelect = sqlite3SelectDup(db, pTab->pSelect);
          sqlite3WalkSelect(pWalker, pFrom->pSelect);
        }
      }
#endif
    }















  }

  /* Process NATURAL keywords, and ON and USING clauses of joins.
  */
  if( db->mallocFailed || sqliteProcessJoin(pParse, p) ){
    return WRC_Abort;
  }







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







2978
2979
2980
2981
2982
2983
2984
2985
2986
2987
2988
2989
2990
2991
2992
2993
2994
2995
2996
2997
2998
2999
3000
3001
3002
3003
3004
3005
3006
        if( pFrom->pSelect==0 ){
          pFrom->pSelect = sqlite3SelectDup(db, pTab->pSelect);
          sqlite3WalkSelect(pWalker, pFrom->pSelect);
        }
      }
#endif
    }

    /* Locate the index named by the INDEXED BY clause, if any. */
    if( pFrom->zIndex ){
      char *zIndex = pFrom->zIndex;
      Index *pIdx;
      for(pIdx=pTab->pIndex; 
          pIdx && sqlite3StrICmp(pIdx->zName, zIndex); 
          pIdx=pIdx->pNext
      );
      if( !pIdx ){
        sqlite3ErrorMsg(pParse, "no such index: %s", zIndex, 0);
        return WRC_Abort;
      }
      pFrom->pIndex = pIdx;
    }
  }

  /* Process NATURAL keywords, and ON and USING clauses of joins.
  */
  if( db->mallocFailed || sqliteProcessJoin(pParse, p) ){
    return WRC_Abort;
  }
Changes to src/sqliteInt.h.
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.
**
*************************************************************************
** Internal interface definitions for SQLite.
**
** @(#) $Id: sqliteInt.h,v 1.773 2008/10/02 13:50:56 danielk1977 Exp $
*/
#ifndef _SQLITEINT_H_
#define _SQLITEINT_H_

/*
** Include the configuration header output by 'configure' if we're using the
** autoconf-based build













|







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.
**
*************************************************************************
** Internal interface definitions for SQLite.
**
** @(#) $Id: sqliteInt.h,v 1.774 2008/10/06 05:32:19 danielk1977 Exp $
*/
#ifndef _SQLITEINT_H_
#define _SQLITEINT_H_

/*
** Include the configuration header output by 'configure' if we're using the
** autoconf-based build
1444
1445
1446
1447
1448
1449
1450



1451
1452
1453
1454
1455
1456
1457
    Select *pSelect;  /* A SELECT statement used in place of a table name */
    u8 isPopulated;   /* Temporary table associated with SELECT is populated */
    u8 jointype;      /* Type of join between this able and the previous */
    int iCursor;      /* The VDBE cursor number used to access this table */
    Expr *pOn;        /* The ON clause of a join */
    IdList *pUsing;   /* The USING clause of a join */
    Bitmask colUsed;  /* Bit N (1<<N) set if column N or pTab is used */



  } a[1];             /* One entry for each identifier on the list */
};

/*
** Permitted values of the SrcList.a.jointype field
*/
#define JT_INNER     0x0001    /* Any kind of inner or cross join */







>
>
>







1444
1445
1446
1447
1448
1449
1450
1451
1452
1453
1454
1455
1456
1457
1458
1459
1460
    Select *pSelect;  /* A SELECT statement used in place of a table name */
    u8 isPopulated;   /* Temporary table associated with SELECT is populated */
    u8 jointype;      /* Type of join between this able and the previous */
    int iCursor;      /* The VDBE cursor number used to access this table */
    Expr *pOn;        /* The ON clause of a join */
    IdList *pUsing;   /* The USING clause of a join */
    Bitmask colUsed;  /* Bit N (1<<N) set if column N or pTab is used */
    u8 notIndexed;    /* True if there is a NOT INDEXED clause */
    char *zIndex;     /* Identifier from "INDEXED BY <zIndex>" clause */
    Index *pIndex;    /* Index structure corresponding to zIndex, if any */
  } a[1];             /* One entry for each identifier on the list */
};

/*
** Permitted values of the SrcList.a.jointype field
*/
#define JT_INNER     0x0001    /* Any kind of inner or cross join */
2135
2136
2137
2138
2139
2140
2141
2142
2143
2144
2145
2146
2147
2148
2149
void sqlite3DeleteTable(Table*);
void sqlite3Insert(Parse*, SrcList*, ExprList*, Select*, IdList*, int);
void *sqlite3ArrayAllocate(sqlite3*,void*,int,int,int*,int*,int*);
IdList *sqlite3IdListAppend(sqlite3*, IdList*, Token*);
int sqlite3IdListIndex(IdList*,const char*);
SrcList *sqlite3SrcListAppend(sqlite3*, SrcList*, Token*, Token*);
SrcList *sqlite3SrcListAppendFromTerm(Parse*, SrcList*, Token*, Token*, Token*,
                                      Select*, Expr*, IdList*);
void sqlite3SrcListShiftJoinType(SrcList*);
void sqlite3SrcListAssignCursors(Parse*, SrcList*);
void sqlite3IdListDelete(sqlite3*, IdList*);
void sqlite3SrcListDelete(sqlite3*, SrcList*);
void sqlite3CreateIndex(Parse*,Token*,Token*,SrcList*,ExprList*,int,Token*,
                        Token*, int, int);
void sqlite3DropIndex(Parse*, SrcList*, int);







|







2138
2139
2140
2141
2142
2143
2144
2145
2146
2147
2148
2149
2150
2151
2152
void sqlite3DeleteTable(Table*);
void sqlite3Insert(Parse*, SrcList*, ExprList*, Select*, IdList*, int);
void *sqlite3ArrayAllocate(sqlite3*,void*,int,int,int*,int*,int*);
IdList *sqlite3IdListAppend(sqlite3*, IdList*, Token*);
int sqlite3IdListIndex(IdList*,const char*);
SrcList *sqlite3SrcListAppend(sqlite3*, SrcList*, Token*, Token*);
SrcList *sqlite3SrcListAppendFromTerm(Parse*, SrcList*, Token*, Token*, Token*,
                                      Token*, Select*, Expr*, IdList*);
void sqlite3SrcListShiftJoinType(SrcList*);
void sqlite3SrcListAssignCursors(Parse*, SrcList*);
void sqlite3IdListDelete(sqlite3*, IdList*);
void sqlite3SrcListDelete(sqlite3*, SrcList*);
void sqlite3CreateIndex(Parse*,Token*,Token*,SrcList*,ExprList*,int,Token*,
                        Token*, int, int);
void sqlite3DropIndex(Parse*, SrcList*, int);
Changes to src/where.c.
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
** This module contains C code that generates VDBE code used to process
** the WHERE clause of SQL statements.  This module is responsible for
** generating the code that loops through a table looking for applicable
** rows.  Indices are selected and used to speed the search when doing
** so is applicable.  Because this module is responsible for selecting
** indices, you might also think of this module as the "query optimizer".
**
** $Id: where.c,v 1.323 2008/10/01 08:43:03 danielk1977 Exp $
*/
#include "sqliteInt.h"

/*
** The number of bits in a Bitmask.  "BMS" means "BitMask Size".
*/
#define BMS  (sizeof(Bitmask)*8)







|







12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
** This module contains C code that generates VDBE code used to process
** the WHERE clause of SQL statements.  This module is responsible for
** generating the code that loops through a table looking for applicable
** rows.  Indices are selected and used to speed the search when doing
** so is applicable.  Because this module is responsible for selecting
** indices, you might also think of this module as the "query optimizer".
**
** $Id: where.c,v 1.324 2008/10/06 05:32:19 danielk1977 Exp $
*/
#include "sqliteInt.h"

/*
** The number of bits in a Bitmask.  "BMS" means "BitMask Size".
*/
#define BMS  (sizeof(Bitmask)*8)
1469
1470
1471
1472
1473
1474
1475










1476
1477
1478
1479
1480
1481
1482
**       fewer the better.)
**
**    *  Whether or not sorting must occur.
**
**    *  Whether or not there must be separate lookups in the
**       index and in the main table.
**










*/
static double bestIndex(
  Parse *pParse,              /* The parsing context */
  WhereClause *pWC,           /* The WHERE clause */
  struct SrcList_item *pSrc,  /* The FROM clause term to search */
  Bitmask notReady,           /* Mask of cursors that are not available */
  ExprList *pOrderBy,         /* The order by clause */







>
>
>
>
>
>
>
>
>
>







1469
1470
1471
1472
1473
1474
1475
1476
1477
1478
1479
1480
1481
1482
1483
1484
1485
1486
1487
1488
1489
1490
1491
1492
**       fewer the better.)
**
**    *  Whether or not sorting must occur.
**
**    *  Whether or not there must be separate lookups in the
**       index and in the main table.
**
** If there was an INDEXED BY clause attached to the table in the SELECT
** statement, then this function only considers strategies using the 
** named index. If one cannot be found, then the returned cost is
** SQLITE_BIG_DBL. If a strategy can be found that uses the named index, 
** then the cost is calculated in the usual way.
**
** If a NOT INDEXED clause was attached to the table in the SELECT 
** statement, then no indexes are considered. However, the selected 
** stategy may still take advantage of the tables built-in rowid
** index.
*/
static double bestIndex(
  Parse *pParse,              /* The parsing context */
  WhereClause *pWC,           /* The WHERE clause */
  struct SrcList_item *pSrc,  /* The FROM clause term to search */
  Bitmask notReady,           /* Mask of cursors that are not available */
  ExprList *pOrderBy,         /* The order by clause */
1496
1497
1498
1499
1500
1501
1502



1503
1504
1505
1506
1507
1508
1509
1510
1511
1512
1513
1514
1515
1516
1517
1518
1519

1520

1521
1522
1523
1524
1525
1526
1527
1528
1529
1530
1531
1532
1533
1534
1535
1536
1537
1538
1539
1540
1541
1542
1543
1544
1545
1546
1547
1548
1549
1550
1551
1552
1553
1554
1555
1556
1557
1558
1559
1560
1561
1562
1563
1564
1565
1566
1567
1568
1569
1570
1571
1572
1573
1574
1575
1576
1577
1578
1579
1580
1581
1582
1583
1584
1585
1586

1587
1588
1589
1590
1591
1592
1593
1594
1595
1596
1597
1598
1599
1600
1601



1602
1603
1604
1605
1606
1607
1608
1609
  int nEq;                    /* Number of == or IN constraints */
  int eqTermMask;             /* Mask of valid equality operators */
  double cost;                /* Cost of using pProbe */

  WHERETRACE(("bestIndex: tbl=%s notReady=%llx\n", pSrc->pTab->zName, notReady));
  lowestCost = SQLITE_BIG_DBL;
  pProbe = pSrc->pTab->pIndex;




  /* If the table has no indices and there are no terms in the where
  ** clause that refer to the ROWID, then we will never be able to do
  ** anything other than a full table scan on this table.  We might as
  ** well put it first in the join order.  That way, perhaps it can be
  ** referenced by other tables in the join.
  */
  if( pProbe==0 &&
     findTerm(pWC, iCur, -1, 0, WO_EQ|WO_IN|WO_LT|WO_LE|WO_GT|WO_GE,0)==0 &&
     (pOrderBy==0 || !sortableByRowid(iCur, pOrderBy, pWC->pMaskSet, &rev)) ){
    *pFlags = 0;
    *ppIndex = 0;
    *pnEq = 0;
    return 0.0;
  }

  /* Check for a rowid=EXPR or rowid IN (...) constraints

  */

  pTerm = findTerm(pWC, iCur, -1, notReady, WO_EQ|WO_IN, 0);
  if( pTerm ){
    Expr *pExpr;
    *ppIndex = 0;
    bestFlags = WHERE_ROWID_EQ;
    if( pTerm->eOperator & WO_EQ ){
      /* Rowid== is always the best pick.  Look no further.  Because only
      ** a single row is generated, output is always in sorted order */
      *pFlags = WHERE_ROWID_EQ | WHERE_UNIQUE;
      *pnEq = 1;
      WHERETRACE(("... best is rowid\n"));
      return 0.0;
    }else if( (pExpr = pTerm->pExpr)->pList!=0 ){
      /* Rowid IN (LIST): cost is NlogN where N is the number of list
      ** elements.  */
      lowestCost = pExpr->pList->nExpr;
      lowestCost *= estLog(lowestCost);
    }else{
      /* Rowid IN (SELECT): cost is NlogN where N is the number of rows
      ** in the result of the inner select.  We have no way to estimate
      ** that value so make a wild guess. */
      lowestCost = 200;
    }
    WHERETRACE(("... rowid IN cost: %.9g\n", lowestCost));
  }

  /* Estimate the cost of a table scan.  If we do not know how many
  ** entries are in the table, use 1 million as a guess.
  */
  cost = pProbe ? pProbe->aiRowEst[0] : 1000000;
  WHERETRACE(("... table scan base cost: %.9g\n", cost));
  flags = WHERE_ROWID_RANGE;

  /* Check for constraints on a range of rowids in a table scan.
  */
  pTerm = findTerm(pWC, iCur, -1, notReady, WO_LT|WO_LE|WO_GT|WO_GE, 0);
  if( pTerm ){
    if( findTerm(pWC, iCur, -1, notReady, WO_LT|WO_LE, 0) ){
      flags |= WHERE_TOP_LIMIT;
      cost /= 3;  /* Guess that rowid<EXPR eliminates two-thirds or rows */
    }
    if( findTerm(pWC, iCur, -1, notReady, WO_GT|WO_GE, 0) ){
      flags |= WHERE_BTM_LIMIT;
      cost /= 3;  /* Guess that rowid>EXPR eliminates two-thirds of rows */
    }
    WHERETRACE(("... rowid range reduces cost to %.9g\n", cost));
  }else{
    flags = 0;
  }

  /* If the table scan does not satisfy the ORDER BY clause, increase
  ** the cost by NlogN to cover the expense of sorting. */
  if( pOrderBy ){
    if( sortableByRowid(iCur, pOrderBy, pWC->pMaskSet, &rev) ){
      flags |= WHERE_ORDERBY|WHERE_ROWID_RANGE;
      if( rev ){
        flags |= WHERE_REVERSE;
      }
    }else{
      cost += cost*estLog(cost);
      WHERETRACE(("... sorting increases cost to %.9g\n", cost));
    }
  }
  if( cost<lowestCost ){
    lowestCost = cost;
    bestFlags = flags;

  }

  /* If the pSrc table is the right table of a LEFT JOIN then we may not
  ** use an index to satisfy IS NULL constraints on that table.  This is
  ** because columns might end up being NULL if the table does not match -
  ** a circumstance which the index cannot help us discover.  Ticket #2177.
  */
  if( (pSrc->jointype & JT_LEFT)!=0 ){
    eqTermMask = WO_EQ|WO_IN;
  }else{
    eqTermMask = WO_EQ|WO_IN|WO_ISNULL;
  }

  /* Look at each index.
  */



  for(; pProbe; pProbe=pProbe->pNext){
    int i;                       /* Loop counter */
    double inMultiplier = 1;

    WHERETRACE(("... index %s:\n", pProbe->zName));

    /* Count the number of columns in the index that are satisfied
    ** by x=EXPR constraints or x IN (...) constraints.







>
>
>
















|
>

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















>
>
>
|







1506
1507
1508
1509
1510
1511
1512
1513
1514
1515
1516
1517
1518
1519
1520
1521
1522
1523
1524
1525
1526
1527
1528
1529
1530
1531
1532
1533
1534
1535
1536
1537
1538
1539
1540
1541
1542
1543
1544
1545
1546
1547
1548
1549
1550
1551
1552
1553
1554
1555
1556
1557
1558
1559
1560
1561
1562
1563
1564
1565
1566
1567
1568
1569
1570
1571
1572
1573
1574
1575
1576
1577
1578
1579
1580
1581
1582
1583
1584
1585
1586
1587
1588
1589
1590
1591
1592
1593
1594
1595
1596
1597
1598
1599
1600
1601
1602
1603
1604
1605
1606
1607
1608
1609
1610
1611
1612
1613
1614
1615
1616
1617
1618
1619
1620
1621
1622
1623
1624
1625
1626
1627
1628
  int nEq;                    /* Number of == or IN constraints */
  int eqTermMask;             /* Mask of valid equality operators */
  double cost;                /* Cost of using pProbe */

  WHERETRACE(("bestIndex: tbl=%s notReady=%llx\n", pSrc->pTab->zName, notReady));
  lowestCost = SQLITE_BIG_DBL;
  pProbe = pSrc->pTab->pIndex;
  if( pSrc->notIndexed ){
    pProbe = 0;
  }

  /* If the table has no indices and there are no terms in the where
  ** clause that refer to the ROWID, then we will never be able to do
  ** anything other than a full table scan on this table.  We might as
  ** well put it first in the join order.  That way, perhaps it can be
  ** referenced by other tables in the join.
  */
  if( pProbe==0 &&
     findTerm(pWC, iCur, -1, 0, WO_EQ|WO_IN|WO_LT|WO_LE|WO_GT|WO_GE,0)==0 &&
     (pOrderBy==0 || !sortableByRowid(iCur, pOrderBy, pWC->pMaskSet, &rev)) ){
    *pFlags = 0;
    *ppIndex = 0;
    *pnEq = 0;
    return 0.0;
  }

  /* Check for a rowid=EXPR or rowid IN (...) constraints. If there was
  ** an INDEXED BY clause attached to this table, skip this step.
  */
  if( !pSrc->pIndex ){
    pTerm = findTerm(pWC, iCur, -1, notReady, WO_EQ|WO_IN, 0);
    if( pTerm ){
      Expr *pExpr;
      *ppIndex = 0;
      bestFlags = WHERE_ROWID_EQ;
      if( pTerm->eOperator & WO_EQ ){
        /* Rowid== is always the best pick.  Look no further.  Because only
        ** a single row is generated, output is always in sorted order */
        *pFlags = WHERE_ROWID_EQ | WHERE_UNIQUE;
        *pnEq = 1;
        WHERETRACE(("... best is rowid\n"));
        return 0.0;
      }else if( (pExpr = pTerm->pExpr)->pList!=0 ){
        /* Rowid IN (LIST): cost is NlogN where N is the number of list
        ** elements.  */
        lowestCost = pExpr->pList->nExpr;
        lowestCost *= estLog(lowestCost);
      }else{
        /* Rowid IN (SELECT): cost is NlogN where N is the number of rows
        ** in the result of the inner select.  We have no way to estimate
        ** that value so make a wild guess. */
        lowestCost = 200;
      }
      WHERETRACE(("... rowid IN cost: %.9g\n", lowestCost));
    }
  
    /* Estimate the cost of a table scan.  If we do not know how many
    ** entries are in the table, use 1 million as a guess.
    */
    cost = pProbe ? pProbe->aiRowEst[0] : 1000000;
    WHERETRACE(("... table scan base cost: %.9g\n", cost));
    flags = WHERE_ROWID_RANGE;
  
    /* Check for constraints on a range of rowids in a table scan.
    */
    pTerm = findTerm(pWC, iCur, -1, notReady, WO_LT|WO_LE|WO_GT|WO_GE, 0);
    if( pTerm ){
      if( findTerm(pWC, iCur, -1, notReady, WO_LT|WO_LE, 0) ){
        flags |= WHERE_TOP_LIMIT;
        cost /= 3;  /* Guess that rowid<EXPR eliminates two-thirds or rows */
      }
      if( findTerm(pWC, iCur, -1, notReady, WO_GT|WO_GE, 0) ){
        flags |= WHERE_BTM_LIMIT;
        cost /= 3;  /* Guess that rowid>EXPR eliminates two-thirds of rows */
      }
      WHERETRACE(("... rowid range reduces cost to %.9g\n", cost));
    }else{
      flags = 0;
    }
  
    /* If the table scan does not satisfy the ORDER BY clause, increase
    ** the cost by NlogN to cover the expense of sorting. */
    if( pOrderBy ){
      if( sortableByRowid(iCur, pOrderBy, pWC->pMaskSet, &rev) ){
        flags |= WHERE_ORDERBY|WHERE_ROWID_RANGE;
        if( rev ){
          flags |= WHERE_REVERSE;
        }
      }else{
        cost += cost*estLog(cost);
        WHERETRACE(("... sorting increases cost to %.9g\n", cost));
      }
    }
    if( cost<lowestCost ){
      lowestCost = cost;
      bestFlags = flags;
    }
  }

  /* If the pSrc table is the right table of a LEFT JOIN then we may not
  ** use an index to satisfy IS NULL constraints on that table.  This is
  ** because columns might end up being NULL if the table does not match -
  ** a circumstance which the index cannot help us discover.  Ticket #2177.
  */
  if( (pSrc->jointype & JT_LEFT)!=0 ){
    eqTermMask = WO_EQ|WO_IN;
  }else{
    eqTermMask = WO_EQ|WO_IN|WO_ISNULL;
  }

  /* Look at each index.
  */
  if( pSrc->pIndex ){
    pProbe = pSrc->pIndex;
  }
  for(; pProbe; pProbe=(pSrc->pIndex ? 0 : pProbe->pNext)){
    int i;                       /* Loop counter */
    double inMultiplier = 1;

    WHERETRACE(("... index %s:\n", pProbe->zName));

    /* Count the number of columns in the index that are satisfied
    ** by x=EXPR constraints or x IN (...) constraints.
2061
2062
2063
2064
2065
2066
2067
2068
2069
2070
2071
2072
2073
2074
2075
  /* Allocate and initialize the WhereInfo structure that will become the
  ** return value.
  */
  db = pParse->db;
  pWInfo = sqlite3DbMallocZero(db,  
                      sizeof(WhereInfo) + pTabList->nSrc*sizeof(WhereLevel));
  if( db->mallocFailed ){
    goto whereBeginNoMem;
  }
  pWInfo->nLevel = pTabList->nSrc;
  pWInfo->pParse = pParse;
  pWInfo->pTabList = pTabList;
  pWInfo->iBreak = sqlite3VdbeMakeLabel(v);

  /* Special case: a WHERE clause that is constant.  Evaluate the







|







2080
2081
2082
2083
2084
2085
2086
2087
2088
2089
2090
2091
2092
2093
2094
  /* Allocate and initialize the WhereInfo structure that will become the
  ** return value.
  */
  db = pParse->db;
  pWInfo = sqlite3DbMallocZero(db,  
                      sizeof(WhereInfo) + pTabList->nSrc*sizeof(WhereLevel));
  if( db->mallocFailed ){
    goto whereBeginError;
  }
  pWInfo->nLevel = pTabList->nSrc;
  pWInfo->pParse = pParse;
  pWInfo->pTabList = pTabList;
  pWInfo->iBreak = sqlite3VdbeMakeLabel(v);

  /* Special case: a WHERE clause that is constant.  Evaluate the
2108
2109
2110
2111
2112
2113
2114
2115
2116
2117
2118
2119
2120
2121
2122
2123
2124
2125
2126
2127
2128
2129
2130
2131
2132
  /* Analyze all of the subexpressions.  Note that exprAnalyze() might
  ** add new virtual terms onto the end of the WHERE clause.  We do not
  ** want to analyze these virtual terms, so start analyzing at the end
  ** and work forward so that the added virtual terms are never processed.
  */
  exprAnalyzeAll(pTabList, &wc);
  if( db->mallocFailed ){
    goto whereBeginNoMem;
  }

  /* Chose the best index to use for each table in the FROM clause.
  **
  ** This loop fills in the following fields:
  **
  **   pWInfo->a[].pIdx      The index to use for this level of the loop.
  **   pWInfo->a[].flags     WHERE_xxx flags associated with pIdx
  **   pWInfo->a[].nEq       The number of == and IN constraints
  **   pWInfo->a[].iFrom     When term of the FROM clause is being coded
  **   pWInfo->a[].iTabCur   The VDBE cursor for the database table
  **   pWInfo->a[].iIdxCur   The VDBE cursor for the index
  **
  ** This loop also figures out the nesting order of tables in the FROM
  ** clause.
  */
  notReady = ~(Bitmask)0;







|









|







2127
2128
2129
2130
2131
2132
2133
2134
2135
2136
2137
2138
2139
2140
2141
2142
2143
2144
2145
2146
2147
2148
2149
2150
2151
  /* Analyze all of the subexpressions.  Note that exprAnalyze() might
  ** add new virtual terms onto the end of the WHERE clause.  We do not
  ** want to analyze these virtual terms, so start analyzing at the end
  ** and work forward so that the added virtual terms are never processed.
  */
  exprAnalyzeAll(pTabList, &wc);
  if( db->mallocFailed ){
    goto whereBeginError;
  }

  /* Chose the best index to use for each table in the FROM clause.
  **
  ** This loop fills in the following fields:
  **
  **   pWInfo->a[].pIdx      The index to use for this level of the loop.
  **   pWInfo->a[].flags     WHERE_xxx flags associated with pIdx
  **   pWInfo->a[].nEq       The number of == and IN constraints
  **   pWInfo->a[].iFrom     Which term of the FROM clause is being coded
  **   pWInfo->a[].iTabCur   The VDBE cursor for the database table
  **   pWInfo->a[].iIdxCur   The VDBE cursor for the index
  **
  ** This loop also figures out the nesting order of tables in the FROM
  ** clause.
  */
  notReady = ~(Bitmask)0;
2215
2216
2217
2218
2219
2220
2221












2222
2223
2224
2225
2226
2227
2228
    if( pBest ){
      pLevel->iIdxCur = pParse->nTab++;
    }else{
      pLevel->iIdxCur = -1;
    }
    notReady &= ~getMask(&maskSet, pTabList->a[bestJ].iCursor);
    pLevel->iFrom = bestJ;












  }
  WHERETRACE(("*** Optimizer Finished ***\n"));

  /* If the total query only selects a single row, then the ORDER BY
  ** clause is irrelevant.
  */
  if( (andFlags & WHERE_UNIQUE)!=0 && ppOrderBy ){







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







2234
2235
2236
2237
2238
2239
2240
2241
2242
2243
2244
2245
2246
2247
2248
2249
2250
2251
2252
2253
2254
2255
2256
2257
2258
2259
    if( pBest ){
      pLevel->iIdxCur = pParse->nTab++;
    }else{
      pLevel->iIdxCur = -1;
    }
    notReady &= ~getMask(&maskSet, pTabList->a[bestJ].iCursor);
    pLevel->iFrom = bestJ;

    /* Check that if the table scanned by this loop iteration had an
    ** INDEXED BY clause attached to it, that the named index is being
    ** used for the scan. If not, then query compilation has failed.
    ** Return an error.
    */
    pIdx = pTabList->a[bestJ].pIndex;
    assert( !pIdx || !pBest || pIdx==pBest );
    if( pIdx && pBest!=pIdx ){
      sqlite3ErrorMsg(pParse, "cannot use index: %s", pIdx->zName);
      goto whereBeginError;
    }
  }
  WHERETRACE(("*** Optimizer Finished ***\n"));

  /* If the total query only selects a single row, then the ORDER BY
  ** clause is irrelevant.
  */
  if( (andFlags & WHERE_UNIQUE)!=0 && ppOrderBy ){
2774
2775
2776
2777
2778
2779
2780
2781
2782
2783
2784
2785
2786
2787
2788
  ** clean up and return.
  */
  pWInfo->iContinue = cont;
  whereClauseClear(&wc);
  return pWInfo;

  /* Jump here if malloc fails */
whereBeginNoMem:
  whereClauseClear(&wc);
  whereInfoFree(pWInfo);
  return 0;
}

/*
** Generate the end of the WHERE loop.  See comments on 







|







2805
2806
2807
2808
2809
2810
2811
2812
2813
2814
2815
2816
2817
2818
2819
  ** clean up and return.
  */
  pWInfo->iContinue = cont;
  whereClauseClear(&wc);
  return pWInfo;

  /* Jump here if malloc fails */
whereBeginError:
  whereClauseClear(&wc);
  whereInfoFree(pWInfo);
  return 0;
}

/*
** Generate the end of the WHERE loop.  See comments on 
Added test/indexedby.test.














































































































































































































































































































>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
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
# 2008 October 4
#
# 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.
#
#***********************************************************************
#
# $Id: indexedby.test,v 1.1 2008/10/06 05:32:19 danielk1977 Exp $

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

# Create a schema with some indexes.
#
do_test indexedby-1.1 {
  execsql {
    CREATE TABLE t1(a, b);
    CREATE INDEX i1 ON t1(a);
    CREATE INDEX i2 ON t1(b);

    CREATE TABLE t2(c, d);
    CREATE INDEX i3 ON t2(c);
    CREATE INDEX i4 ON t2(d);

    CREATE VIEW v1 AS SELECT * FROM t1;
  }
} {}

# Explain Query Plan
#
proc EQP {sql} {
  uplevel "execsql {EXPLAIN QUERY PLAN $sql}"
}

# These tests are to check that "EXPLAIN QUERY PLAN" is working as expected.
#
do_test indexedby-1.2 {
  EQP { select * from t1 WHERE a = 10; }
} {0 0 {TABLE t1 WITH INDEX i1}}
do_test indexedby-1.3 {
  EQP { select * from t1 ; }
} {0 0 {TABLE t1}}
do_test indexedby-1.4 {
  EQP { select * from t1, t2 WHERE c = 10; }
} {0 1 {TABLE t2 WITH INDEX i3} 1 0 {TABLE t1}}

# Parser tests. Test that an INDEXED BY or NOT INDEX clause can be 
# attached to a table in the FROM clause, but not to a sub-select or
# SQL view. Also test that specifying an index that does not exist or
# is attached to a different table is detected as an error.
# 
do_test indexedby-2.1 {
  execsql { SELECT * FROM t1 NOT INDEXED WHERE a = 'one' AND b = 'two'}
} {}
do_test indexedby-2.2 {
  execsql { SELECT * FROM t1 INDEXED BY i1 WHERE a = 'one' AND b = 'two'}
} {}
do_test indexedby-2.3 {
  execsql { SELECT * FROM t1 INDEXED BY i2 WHERE a = 'one' AND b = 'two'}
} {}

do_test indexedby-2.4 {
  catchsql { SELECT * FROM t1 INDEXED BY i3 WHERE a = 'one' AND b = 'two'}
} {1 {no such index: i3}}
do_test indexedby-2.5 {
  catchsql { SELECT * FROM t1 INDEXED BY i5 WHERE a = 'one' AND b = 'two'}
} {1 {no such index: i5}}
do_test indexedby-2.6 {
  catchsql { SELECT * FROM t1 INDEXED BY WHERE a = 'one' AND b = 'two'}
} {1 {near "WHERE": syntax error}}
do_test indexedby-2.7 {
  catchsql { SELECT * FROM v1 INDEXED BY i1 WHERE a = 'one' }
} {1 {no such index: i1}}

# Tests for single table cases.
#
do_test indexedby-3.1 {
  EQP { SELECT * FROM t1 NOT INDEXED WHERE a = 'one' AND b = 'two'}
} {0 0 {TABLE t1}}
do_test indexedby-3.2 {
  EQP { SELECT * FROM t1 INDEXED BY i1 WHERE a = 'one' AND b = 'two'}
} {0 0 {TABLE t1 WITH INDEX i1}}
do_test indexedby-3.3 {
  EQP { SELECT * FROM t1 INDEXED BY i2 WHERE a = 'one' AND b = 'two'}
} {0 0 {TABLE t1 WITH INDEX i2}}
do_test indexedby-3.4 {
  catchsql { SELECT * FROM t1 INDEXED BY i2 WHERE a = 'one' }
} {1 {cannot use index: i2}}
do_test indexedby-3.5 {
  catchsql { SELECT * FROM t1 INDEXED BY i2 ORDER BY a }
} {1 {cannot use index: i2}}
do_test indexedby-3.6 {
  catchsql { SELECT * FROM t1 INDEXED BY i1 WHERE a = 'one' }
} {0 {}}
do_test indexedby-3.7 {
  catchsql { SELECT * FROM t1 INDEXED BY i1 ORDER BY a }
} {0 {}}

# Tests for multiple table cases.
#
do_test indexedby-4.1 {
  EQP { SELECT * FROM t1, t2 WHERE a = c }
} {0 0 {TABLE t1} 1 1 {TABLE t2 WITH INDEX i3}}
do_test indexedby-4.2 {
  EQP { SELECT * FROM t1 INDEXED BY i1, t2 WHERE a = c }
} {0 1 {TABLE t2} 1 0 {TABLE t1 WITH INDEX i1}}

# Test embedding an INDEXED BY in a CREATE VIEW statement. This block
# also tests that nothing bad happens if an index refered to by
# a CREATE VIEW statement is dropped and recreated.
#
do_test indexedby-5.1 {
  execsql {
    CREATE VIEW v2 AS SELECT * FROM t1 INDEXED BY i1 WHERE a > 5;
  }
  EQP { SELECT * FROM v2 }
} {0 0 {TABLE t1 WITH INDEX i1}}
do_test indexedby-5.2 {
  EQP { SELECT * FROM v2 WHERE b = 10 }
} {0 0 {TABLE t1 WITH INDEX i1}}
do_test indexedby-5.3 {
  execsql { DROP INDEX i1 }
  catchsql { SELECT * FROM v2 }
} {1 {no such index: i1}}
do_test indexedby-5.4 {
  # Recreate index i1 in such a way as it cannot be used by the view query.
  execsql { CREATE INDEX i1 ON t1(b) }
  catchsql { SELECT * FROM v2 }
} {1 {cannot use index: i1}}
do_test indexedby-5.5 {
  # Drop and recreate index i1 again. This time, create it so that it can
  # be used by the query.
  execsql { DROP INDEX i1 ; CREATE INDEX i1 ON t1(a) }
  catchsql { SELECT * FROM v2 }
} {0 {}}

# Test that "NOT INDEXED" may use the rowid index, but not others.
# 
do_test indexedby-6.1 {
  EQP { SELECT * FROM t1 WHERE b = 10 ORDER BY rowid }
} {0 0 {TABLE t1 WITH INDEX i2 ORDER BY}}
do_test indexedby-6.2 {
  EQP { SELECT * FROM t1 NOT INDEXED WHERE b = 10 ORDER BY rowid }
} {0 0 {TABLE t1 USING PRIMARY KEY ORDER BY}}

finish_test

Changes to test/malloc.test.
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
# This file attempts to check the behavior of the SQLite library in 
# an out-of-memory situation. When compiled with -DSQLITE_DEBUG=1, 
# the SQLite library accepts a special command (sqlite3_memdebug_fail N C)
# which causes the N-th malloc to fail.  This special feature is used
# to see what happens in the library if a malloc were to really fail
# due to an out-of-memory situation.
#
# $Id: malloc.test,v 1.67 2008/09/23 16:41:30 danielk1977 Exp $

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


# Only run these tests if memory debugging is turned on.
#







|







12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
# This file attempts to check the behavior of the SQLite library in 
# an out-of-memory situation. When compiled with -DSQLITE_DEBUG=1, 
# the SQLite library accepts a special command (sqlite3_memdebug_fail N C)
# which causes the N-th malloc to fail.  This special feature is used
# to see what happens in the library if a malloc were to really fail
# due to an out-of-memory situation.
#
# $Id: malloc.test,v 1.68 2008/10/06 05:32:19 danielk1977 Exp $

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


# Only run these tests if memory debugging is turned on.
#
655
656
657
658
659
660
661











662
663
664
665
666
667
668
669
670
  sqlite3_shutdown
} -tclbody {
  set rc [sqlite3_initialize]
  if {$rc == "SQLITE_NOMEM"} {
    error "out of memory"
  }
}












# Ensure that no file descriptors were leaked.
do_test malloc-99.X {
  catch {db close}
  set sqlite_open_file_count
} {0}

puts open-file-count=$sqlite_open_file_count
finish_test







>
>
>
>
>
>
>
>
>
>
>









655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
  sqlite3_shutdown
} -tclbody {
  set rc [sqlite3_initialize]
  if {$rc == "SQLITE_NOMEM"} {
    error "out of memory"
  }
}

# Test that malloc failures that occur while processing INDEXED BY
# clauses are handled correctly.
do_malloc_test 28 -sqlprep {
  CREATE TABLE t1(a, b);
  CREATE INDEX i1 ON t1(a);
  CREATE VIEW v1 AS SELECT * FROM t1 INDEXED BY i1 WHERE a = 10;
} -sqlbody {
  SELECT * FROM t1 INDEXED BY i1 ORDER BY a;
  SELECT * FROM v1;
}

# Ensure that no file descriptors were leaked.
do_test malloc-99.X {
  catch {db close}
  set sqlite_open_file_count
} {0}

puts open-file-count=$sqlite_open_file_count
finish_test
Changes to tool/mkkeywordhash.c.
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
** A header comment placed at the beginning of generated code.
*/
static const char zHdr[] = 
  "/***** This file contains automatically generated code ******\n"
  "**\n"
  "** The code in this file has been automatically generated by\n"
  "**\n"
  "**     $Header: /home/drh/sqlite/trans/cvs/sqlite/sqlite/tool/mkkeywordhash.c,v 1.31 2007/07/30 18:26:20 rse Exp $\n"
  "**\n"
  "** The code in this file implements a function that determines whether\n"
  "** or not a given identifier is really an SQL keyword.  The same thing\n"
  "** might be implemented more directly using a hand-written hash table.\n"
  "** But by using this automatically generated code, the size of the code\n"
  "** is substantially reduced.  This is important for embedded applications\n"
  "** on platforms with limited memory.\n"







|







11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
** A header comment placed at the beginning of generated code.
*/
static const char zHdr[] = 
  "/***** This file contains automatically generated code ******\n"
  "**\n"
  "** The code in this file has been automatically generated by\n"
  "**\n"
  "**     $Header: /home/drh/sqlite/trans/cvs/sqlite/sqlite/tool/mkkeywordhash.c,v 1.32 2008/10/06 05:32:19 danielk1977 Exp $\n"
  "**\n"
  "** The code in this file implements a function that determines whether\n"
  "** or not a given identifier is really an SQL keyword.  The same thing\n"
  "** might be implemented more directly using a hand-written hash table.\n"
  "** But by using this automatically generated code, the size of the code\n"
  "** is substantially reduced.  This is important for embedded applications\n"
  "** on platforms with limited memory.\n"
196
197
198
199
200
201
202

203
204
205
206
207
208
209
  { "GROUP",            "TK_GROUP",        ALWAYS                 },
  { "HAVING",           "TK_HAVING",       ALWAYS                 },
  { "IF",               "TK_IF",           ALWAYS                 },
  { "IGNORE",           "TK_IGNORE",       CONFLICT|TRIGGER       },
  { "IMMEDIATE",        "TK_IMMEDIATE",    ALWAYS                 },
  { "IN",               "TK_IN",           ALWAYS                 },
  { "INDEX",            "TK_INDEX",        ALWAYS                 },

  { "INITIALLY",        "TK_INITIALLY",    FKEY                   },
  { "INNER",            "TK_JOIN_KW",      ALWAYS                 },
  { "INSERT",           "TK_INSERT",       ALWAYS                 },
  { "INSTEAD",          "TK_INSTEAD",      TRIGGER                },
  { "INTERSECT",        "TK_INTERSECT",    COMPOUND               },
  { "INTO",             "TK_INTO",         ALWAYS                 },
  { "IS",               "TK_IS",           ALWAYS                 },







>







196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
  { "GROUP",            "TK_GROUP",        ALWAYS                 },
  { "HAVING",           "TK_HAVING",       ALWAYS                 },
  { "IF",               "TK_IF",           ALWAYS                 },
  { "IGNORE",           "TK_IGNORE",       CONFLICT|TRIGGER       },
  { "IMMEDIATE",        "TK_IMMEDIATE",    ALWAYS                 },
  { "IN",               "TK_IN",           ALWAYS                 },
  { "INDEX",            "TK_INDEX",        ALWAYS                 },
  { "INDEXED",          "TK_INDEXED",      ALWAYS                 },
  { "INITIALLY",        "TK_INITIALLY",    FKEY                   },
  { "INNER",            "TK_JOIN_KW",      ALWAYS                 },
  { "INSERT",           "TK_INSERT",       ALWAYS                 },
  { "INSTEAD",          "TK_INSTEAD",      TRIGGER                },
  { "INTERSECT",        "TK_INTERSECT",    COMPOUND               },
  { "INTO",             "TK_INTO",         ALWAYS                 },
  { "IS",               "TK_IS",           ALWAYS                 },