/ Check-in [be7aed20]
Login

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

Overview
Comment:VDBE cursors numbers for tables in a join do not have to be consecutive. This is one step on the road to fixing ticket #272. (CVS 947)
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: be7aed2011b4af868b6a0c370c3d41354ae0cdf4
User & Date: drh 2003-05-02 14:32:13
Context
2003-05-02
16:04
Enhance the query flattener to handle subqueries that are joins. All regressions pass but new tests need to be added before release. Ticket #272. (CVS 948) check-in: ad57693e user: drh tags: trunk
14:32
VDBE cursors numbers for tables in a join do not have to be consecutive. This is one step on the road to fixing ticket #272. (CVS 947) check-in: be7aed20 user: drh tags: trunk
2003-05-01
16:56
Correctly create an index that uses an INTEGER PRIMARY KEY as one of columns to be indexed. (CVS 946) check-in: 6d019e0b user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/auth.c.

10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
..
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
**
*************************************************************************
** This file contains code used to implement the sqlite_set_authorizer()
** API.  This facility is an optional feature of the library.  Embedded
** systems that do not need this facility may omit it by recompiling
** the library with -DSQLITE_OMIT_AUTHORIZATION=1
**
** $Id: auth.c,v 1.8 2003/04/25 17:52:11 drh Exp $
*/
#include "sqliteInt.h"

/*
** All of the code in this file may be omitted by defining a single
** macro.
*/
................................................................................
    "SQLITE_IGNORE, or SQLITE_DENY", 0);
  pParse->nErr++;
  pParse->rc = SQLITE_MISUSE;
}

/*
** The pExpr should be a TK_COLUMN expression.  The table referred to
** is in pTabList with an offset of base.  Check to see if it is OK to read
** this particular column.
**
** If the auth function returns SQLITE_IGNORE, change the TK_COLUMN 
** instruction into a TK_NULL.  If the auth function returns SQLITE_DENY,
** then generate an error.
*/
void sqliteAuthRead(
  Parse *pParse,        /* The parser context */
  Expr *pExpr,          /* The expression to check authorization on */
  SrcList *pTabList,    /* All table that pExpr might refer to */
  int base              /* Offset of pTabList relative to pExpr */
){
  sqlite *db = pParse->db;
  int rc;
  Table *pTab;          /* The table being read */
  const char *zCol;     /* Name of the column of the table */
  int iSrc;             /* Index in pTabList->a[] of table being read */
  const char *zDBase;   /* Name of database being accessed */

  if( db->xAuth==0 ) return;
  assert( pExpr->op==TK_COLUMN );
  iSrc = pExpr->iTable - base;


  if( iSrc>=0 && iSrc<pTabList->nSrc ){
    pTab = pTabList->a[iSrc].pTab;
  }else{
    /* This must be an attempt to read the NEW or OLD pseudo-tables
    ** of a trigger.
    */
    TriggerStack *pStack; /* The stack of current triggers */







|







 







|
|








|
<










|
>
>







10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
..
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
**
*************************************************************************
** This file contains code used to implement the sqlite_set_authorizer()
** API.  This facility is an optional feature of the library.  Embedded
** systems that do not need this facility may omit it by recompiling
** the library with -DSQLITE_OMIT_AUTHORIZATION=1
**
** $Id: auth.c,v 1.9 2003/05/02 14:32:13 drh Exp $
*/
#include "sqliteInt.h"

/*
** All of the code in this file may be omitted by defining a single
** macro.
*/
................................................................................
    "SQLITE_IGNORE, or SQLITE_DENY", 0);
  pParse->nErr++;
  pParse->rc = SQLITE_MISUSE;
}

/*
** The pExpr should be a TK_COLUMN expression.  The table referred to
** is in pTabList or else it is the NEW or OLD table of a trigger.  
** Check to see if it is OK to read this particular column.
**
** If the auth function returns SQLITE_IGNORE, change the TK_COLUMN 
** instruction into a TK_NULL.  If the auth function returns SQLITE_DENY,
** then generate an error.
*/
void sqliteAuthRead(
  Parse *pParse,        /* The parser context */
  Expr *pExpr,          /* The expression to check authorization on */
  SrcList *pTabList     /* All table that pExpr might refer to */

){
  sqlite *db = pParse->db;
  int rc;
  Table *pTab;          /* The table being read */
  const char *zCol;     /* Name of the column of the table */
  int iSrc;             /* Index in pTabList->a[] of table being read */
  const char *zDBase;   /* Name of database being accessed */

  if( db->xAuth==0 ) return;
  assert( pExpr->op==TK_COLUMN );
  for(iSrc=0; iSrc<pTabList->nSrc; iSrc++){
    if( pExpr->iTable==pTabList->a[iSrc].iCursor ) break;
  }
  if( iSrc>=0 && iSrc<pTabList->nSrc ){
    pTab = pTabList->a[iSrc].pTab;
  }else{
    /* This must be an attempt to read the NEW or OLD pseudo-tables
    ** of a trigger.
    */
    TriggerStack *pStack; /* The stack of current triggers */

Changes to src/build.c.

19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
....
1763
1764
1765
1766
1767
1768
1769
1770
1771
1772
1773
1774
1775
1776
1777
....
1989
1990
1991
1992
1993
1994
1995
1996
1997
1998
1999
2000
2001
2002
2003
**     DROP INDEX
**     creating ID lists
**     BEGIN TRANSACTION
**     COMMIT
**     ROLLBACK
**     PRAGMA
**
** $Id: build.c,v 1.151 2003/05/01 16:56:03 drh Exp $
*/
#include "sqliteInt.h"
#include <ctype.h>

/*
** This routine is called when a new SQL statement is beginning to
** be parsed.  Check to see if the schema for the database needs
................................................................................
      sqliteVdbeAddOp(v, OP_Rewind, 2, lbl2);
      lbl1 = sqliteVdbeAddOp(v, OP_Recno, 2, 0);
      for(i=0; i<pIndex->nColumn; i++){
        int iCol = pIndex->aiColumn[i];
        if( pTab->iPKey==iCol ){
          sqliteVdbeAddOp(v, OP_Dup, i, 0);
        }else{
          sqliteVdbeAddOp(v, OP_Column, 2, pIndex->aiColumn[i]);
        }
      }
      sqliteVdbeAddOp(v, OP_MakeIdxKey, pIndex->nColumn, 0);
      if( db->file_format>=4 ) sqliteAddIdxKeyType(v, pIndex);
      sqliteVdbeAddOp(v, OP_IdxPut, 1, pIndex->onError!=OE_None);
      sqliteVdbeChangeP3(v, -1, "indexed columns are not unique", P3_STATIC);
      sqliteVdbeAddOp(v, OP_Next, 2, lbl1);
................................................................................
/*
** Assign cursors to all tables in a SrcList
*/
void sqliteSrcListAssignCursors(Parse *pParse, SrcList *pList){
  int i;
  for(i=0; i<pList->nSrc; i++){
    if( pList->a[i].iCursor<0 ){
      pList->a[i].iCursor = ++pParse->nTab;
    }
  }
}

/*
** Add an alias to the last identifier on the given identifier list.
*/







|







 







|







 







|







19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
....
1763
1764
1765
1766
1767
1768
1769
1770
1771
1772
1773
1774
1775
1776
1777
....
1989
1990
1991
1992
1993
1994
1995
1996
1997
1998
1999
2000
2001
2002
2003
**     DROP INDEX
**     creating ID lists
**     BEGIN TRANSACTION
**     COMMIT
**     ROLLBACK
**     PRAGMA
**
** $Id: build.c,v 1.152 2003/05/02 14:32:13 drh Exp $
*/
#include "sqliteInt.h"
#include <ctype.h>

/*
** This routine is called when a new SQL statement is beginning to
** be parsed.  Check to see if the schema for the database needs
................................................................................
      sqliteVdbeAddOp(v, OP_Rewind, 2, lbl2);
      lbl1 = sqliteVdbeAddOp(v, OP_Recno, 2, 0);
      for(i=0; i<pIndex->nColumn; i++){
        int iCol = pIndex->aiColumn[i];
        if( pTab->iPKey==iCol ){
          sqliteVdbeAddOp(v, OP_Dup, i, 0);
        }else{
          sqliteVdbeAddOp(v, OP_Column, 2, iCol);
        }
      }
      sqliteVdbeAddOp(v, OP_MakeIdxKey, pIndex->nColumn, 0);
      if( db->file_format>=4 ) sqliteAddIdxKeyType(v, pIndex);
      sqliteVdbeAddOp(v, OP_IdxPut, 1, pIndex->onError!=OE_None);
      sqliteVdbeChangeP3(v, -1, "indexed columns are not unique", P3_STATIC);
      sqliteVdbeAddOp(v, OP_Next, 2, lbl1);
................................................................................
/*
** Assign cursors to all tables in a SrcList
*/
void sqliteSrcListAssignCursors(Parse *pParse, SrcList *pList){
  int i;
  for(i=0; i<pList->nSrc; i++){
    if( pList->a[i].iCursor<0 ){
      pList->a[i].iCursor = pParse->nTab++;
    }
  }
}

/*
** Add an alias to the last identifier on the given identifier list.
*/

Changes to src/delete.c.

8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
..
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
...
115
116
117
118
119
120
121

122
123
124
125
126
127
128
129
130
131
...
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
...
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
...
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
...
225
226
227
228
229
230
231
232
233
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
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
...
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
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
**    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 DELETE FROM statements.
**
** $Id: delete.c,v 1.55 2003/04/25 17:52:11 drh 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.
................................................................................
  Vdbe *v;               /* The virtual database engine */
  Table *pTab;           /* The table from which records will be deleted */
  const char *zDb;       /* Name of database holding pTab */
  int end, addr;         /* A couple addresses of generated code */
  int i;                 /* Loop counter */
  WhereInfo *pWInfo;     /* Information about the WHERE clause */
  Index *pIdx;           /* For looping over indices of the table */
  int base;              /* Index of the first available table cursor */
  sqlite *db;            /* Main database structure */
  int isView;            /* True if attempting to delete from a view */
  AuthContext sContext;  /* Authorization context */

  int row_triggers_exist = 0;  /* True if any triggers exist */
  int before_triggers;         /* True if there are BEFORE triggers */
  int after_triggers;          /* True if there are AFTER triggers */
................................................................................
  */
  if( row_triggers_exist ){ 
    oldIdx = pParse->nTab++;
  }

  /* Resolve the column names in all the expressions.
  */

  base = pParse->nTab++;
  if( pWhere ){
    if( sqliteExprResolveIds(pParse, base, pTabList, 0, pWhere) ){
      goto delete_from_cleanup;
    }
    if( sqliteExprCheck(pParse, pWhere, 0, 0) ){
      goto delete_from_cleanup;
    }
  }

................................................................................
       !row_triggers_exist && pTab->iDb==1);

  /* If we are trying to delete from a view, construct that view into
  ** a temporary table.
  */
  if( isView ){
    Select *pView = sqliteSelectDup(pTab->pSelect);
    sqliteSelect(pParse, pView, SRT_TempTable, base, 0, 0, 0);
    sqliteSelectDelete(pView);
  }

  /* Initialize the counter of the number of rows deleted, if
  ** we are counting rows.
  */
  if( db->flags & SQLITE_CountRows ){
................................................................................
    if( db->flags & SQLITE_CountRows ){
      /* If counting rows deleted, just count the total number of
      ** entries in the table. */
      int endOfLoop = sqliteVdbeMakeLabel(v);
      int addr;
      if( !isView ){
        sqliteVdbeAddOp(v, OP_Integer, pTab->iDb, 0);
        sqliteVdbeAddOp(v, OP_OpenRead, base, pTab->tnum);
      }
      sqliteVdbeAddOp(v, OP_Rewind, base, sqliteVdbeCurrentAddr(v)+2);
      addr = sqliteVdbeAddOp(v, OP_AddImm, 1, 0);
      sqliteVdbeAddOp(v, OP_Next, base, addr);
      sqliteVdbeResolveLabel(v, endOfLoop);
      sqliteVdbeAddOp(v, OP_Close, base, 0);
    }
    if( !isView ){
      sqliteVdbeAddOp(v, OP_Clear, pTab->tnum, pTab->iDb);
      for(pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext){
        sqliteVdbeAddOp(v, OP_Clear, pIdx->tnum, pIdx->iDb);
      }
    }
................................................................................

  /* The usual case: There is a WHERE clause so we have to scan through
  ** the table an pick which records to delete.
  */
  else{
    /* Begin the database scan
    */
    pWInfo = sqliteWhereBegin(pParse, base, pTabList, pWhere, 1, 0);
    if( pWInfo==0 ) goto delete_from_cleanup;

    /* Remember the key of every item to be deleted.
    */
    sqliteVdbeAddOp(v, OP_ListWrite, 0, 0);
    if( db->flags & SQLITE_CountRows ){
      sqliteVdbeAddOp(v, OP_AddImm, 1, 0);
................................................................................
    ** row triggers.
    */
    if( row_triggers_exist ){
      addr = sqliteVdbeAddOp(v, OP_ListRead, 0, end);
      sqliteVdbeAddOp(v, OP_Dup, 0, 0);
      if( !isView ){
        sqliteVdbeAddOp(v, OP_Integer, pTab->iDb, 0);
        sqliteVdbeAddOp(v, OP_OpenRead, base, pTab->tnum);
      }
      sqliteVdbeAddOp(v, OP_MoveTo, base, 0);

      sqliteVdbeAddOp(v, OP_Recno, base, 0);
      sqliteVdbeAddOp(v, OP_RowData, base, 0);
      sqliteVdbeAddOp(v, OP_PutIntKey, oldIdx, 0);
      if( !isView ){
        sqliteVdbeAddOp(v, OP_Close, base, 0);
      }

      sqliteCodeRowTrigger(pParse, TK_DELETE, 0, TK_BEFORE, pTab, -1, 
          oldIdx, (pParse->trigStack)?pParse->trigStack->orconf:OE_Default,
	  addr);
    }

................................................................................
    if( !isView ){
      /* Open cursors for the table we are deleting from and all its
      ** indices.  If there are row triggers, this happens inside the
      ** OP_ListRead loop because the cursor have to all be closed
      ** before the trigger fires.  If there are no row triggers, the
      ** cursors are opened only once on the outside the loop.
      */
      pParse->nTab = base + 1;
      sqliteVdbeAddOp(v, OP_Integer, pTab->iDb, 0);
      sqliteVdbeAddOp(v, OP_OpenWrite, base, pTab->tnum);
      for(i=1, pIdx=pTab->pIndex; pIdx; i++, pIdx=pIdx->pNext){
        sqliteVdbeAddOp(v, OP_Integer, pIdx->iDb, 0);
        sqliteVdbeAddOp(v, OP_OpenWrite, pParse->nTab++, pIdx->tnum);
      }

      /* This is the beginning of the delete loop when there are no
      ** row triggers */
      if( !row_triggers_exist ){ 
        addr = sqliteVdbeAddOp(v, OP_ListRead, 0, end);
      }

      /* Delete the row */
      sqliteGenerateRowDelete(db, v, pTab, base, pParse->trigStack==0);
    }

    /* If there are row triggers, close all cursors then invoke
    ** the AFTER triggers
    */
    if( row_triggers_exist ){
      if( !isView ){
        for(i=1, pIdx=pTab->pIndex; pIdx; i++, pIdx=pIdx->pNext){
          sqliteVdbeAddOp(v, OP_Close, base + i, pIdx->tnum);
        }
        sqliteVdbeAddOp(v, OP_Close, base, 0);
      }
      sqliteCodeRowTrigger(pParse, TK_DELETE, 0, TK_AFTER, pTab, -1, 
          oldIdx, (pParse->trigStack)?pParse->trigStack->orconf:OE_Default,
	  addr);
    }

    /* End of the delete loop */
................................................................................
    sqliteVdbeAddOp(v, OP_Goto, 0, addr);
    sqliteVdbeResolveLabel(v, end);
    sqliteVdbeAddOp(v, OP_ListReset, 0, 0);

    /* Close the cursors after the loop if there are no row triggers */
    if( !row_triggers_exist ){
      for(i=1, pIdx=pTab->pIndex; pIdx; i++, pIdx=pIdx->pNext){
        sqliteVdbeAddOp(v, OP_Close, base + i, pIdx->tnum);
      }
      sqliteVdbeAddOp(v, OP_Close, base, 0);
      pParse->nTab = base;
    }
  }
  sqliteEndWriteOperation(pParse);

  /*
  ** Return the number of rows that were deleted.
  */
................................................................................
** and then generates code to remove both the table record and all index
** entries that point to that record.
*/
void sqliteGenerateRowDelete(
  sqlite *db,        /* The database containing the index */
  Vdbe *v,           /* Generate code into this VDBE */
  Table *pTab,       /* Table containing the row to be deleted */
  int base,          /* Cursor number for the table */
  int count          /* Increment the row change counter */
){
  int addr;
  addr = sqliteVdbeAddOp(v, OP_NotExists, base, 0);
  sqliteGenerateRowIndexDelete(db, v, pTab, base, 0);
  sqliteVdbeAddOp(v, OP_Delete, base, count);
  sqliteVdbeChangeP2(v, addr, sqliteVdbeCurrentAddr(v));
}

/*
** This routine generates VDBE code that causes the deletion of all
** index entries associated with a single row of a single table.
**
** The VDBE must be in a particular state when this routine is called.
** These are the requirements:
**
**   1.  A read/write cursor pointing to pTab, the table containing the row
**       to be deleted, must be opened as cursor number "base".
**
**   2.  Read/write cursors for all indices of pTab must be open as
**       cursor number base+i for the i-th index.
**
**   3.  The "base" cursor must be pointing to the row that is to be
**       deleted.
*/
void sqliteGenerateRowIndexDelete(
  sqlite *db,        /* The database containing the index */
  Vdbe *v,           /* Generate code into this VDBE */
  Table *pTab,       /* Table containing the row to be deleted */
  int base,          /* Cursor number for the table */
  char *aIdxUsed     /* Only delete if aIdxUsed!=0 && aIdxUsed[i]!=0 */
){
  int i;
  Index *pIdx;

  for(i=1, pIdx=pTab->pIndex; pIdx; i++, pIdx=pIdx->pNext){
    int j;
    if( aIdxUsed!=0 && aIdxUsed[i-1]==0 ) continue;
    sqliteVdbeAddOp(v, OP_Recno, base, 0);
    for(j=0; j<pIdx->nColumn; j++){
      int idx = pIdx->aiColumn[j];
      if( idx==pTab->iPKey ){
        sqliteVdbeAddOp(v, OP_Dup, j, 0);
      }else{
        sqliteVdbeAddOp(v, OP_Column, base, idx);
      }
    }
    sqliteVdbeAddOp(v, OP_MakeIdxKey, pIdx->nColumn, 0);
    if( db->file_format>=4 ) sqliteAddIdxKeyType(v, pIdx);
    sqliteVdbeAddOp(v, OP_IdxDelete, base+i, 0);
  }
}







|







 







|







 







>
|

|







 







|







 







|

|

|

|







 







|







 







|

|

|
|


|







 







|

|












|








|

|







 







|

|
|







 







|



|
|
|











|


|

|






|








|





|




|


8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
..
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
...
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
...
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
...
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
...
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
...
226
227
228
229
230
231
232
233
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
...
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
...
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
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
**    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 DELETE FROM statements.
**
** $Id: delete.c,v 1.56 2003/05/02 14:32:13 drh 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.
................................................................................
  Vdbe *v;               /* The virtual database engine */
  Table *pTab;           /* The table from which records will be deleted */
  const char *zDb;       /* Name of database holding pTab */
  int end, addr;         /* A couple addresses of generated code */
  int i;                 /* Loop counter */
  WhereInfo *pWInfo;     /* Information about the WHERE clause */
  Index *pIdx;           /* For looping over indices of the table */
  int iCur;              /* VDBE Cursor number for pTab */
  sqlite *db;            /* Main database structure */
  int isView;            /* True if attempting to delete from a view */
  AuthContext sContext;  /* Authorization context */

  int row_triggers_exist = 0;  /* True if any triggers exist */
  int before_triggers;         /* True if there are BEFORE triggers */
  int after_triggers;          /* True if there are AFTER triggers */
................................................................................
  */
  if( row_triggers_exist ){ 
    oldIdx = pParse->nTab++;
  }

  /* Resolve the column names in all the expressions.
  */
  assert( pTabList->nSrc==1 );
  iCur = pTabList->a[0].iCursor = pParse->nTab++;
  if( pWhere ){
    if( sqliteExprResolveIds(pParse, pTabList, 0, pWhere) ){
      goto delete_from_cleanup;
    }
    if( sqliteExprCheck(pParse, pWhere, 0, 0) ){
      goto delete_from_cleanup;
    }
  }

................................................................................
       !row_triggers_exist && pTab->iDb==1);

  /* If we are trying to delete from a view, construct that view into
  ** a temporary table.
  */
  if( isView ){
    Select *pView = sqliteSelectDup(pTab->pSelect);
    sqliteSelect(pParse, pView, SRT_TempTable, iCur, 0, 0, 0);
    sqliteSelectDelete(pView);
  }

  /* Initialize the counter of the number of rows deleted, if
  ** we are counting rows.
  */
  if( db->flags & SQLITE_CountRows ){
................................................................................
    if( db->flags & SQLITE_CountRows ){
      /* If counting rows deleted, just count the total number of
      ** entries in the table. */
      int endOfLoop = sqliteVdbeMakeLabel(v);
      int addr;
      if( !isView ){
        sqliteVdbeAddOp(v, OP_Integer, pTab->iDb, 0);
        sqliteVdbeAddOp(v, OP_OpenRead, iCur, pTab->tnum);
      }
      sqliteVdbeAddOp(v, OP_Rewind, iCur, sqliteVdbeCurrentAddr(v)+2);
      addr = sqliteVdbeAddOp(v, OP_AddImm, 1, 0);
      sqliteVdbeAddOp(v, OP_Next, iCur, addr);
      sqliteVdbeResolveLabel(v, endOfLoop);
      sqliteVdbeAddOp(v, OP_Close, iCur, 0);
    }
    if( !isView ){
      sqliteVdbeAddOp(v, OP_Clear, pTab->tnum, pTab->iDb);
      for(pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext){
        sqliteVdbeAddOp(v, OP_Clear, pIdx->tnum, pIdx->iDb);
      }
    }
................................................................................

  /* The usual case: There is a WHERE clause so we have to scan through
  ** the table an pick which records to delete.
  */
  else{
    /* Begin the database scan
    */
    pWInfo = sqliteWhereBegin(pParse, pTabList, pWhere, 1, 0);
    if( pWInfo==0 ) goto delete_from_cleanup;

    /* Remember the key of every item to be deleted.
    */
    sqliteVdbeAddOp(v, OP_ListWrite, 0, 0);
    if( db->flags & SQLITE_CountRows ){
      sqliteVdbeAddOp(v, OP_AddImm, 1, 0);
................................................................................
    ** row triggers.
    */
    if( row_triggers_exist ){
      addr = sqliteVdbeAddOp(v, OP_ListRead, 0, end);
      sqliteVdbeAddOp(v, OP_Dup, 0, 0);
      if( !isView ){
        sqliteVdbeAddOp(v, OP_Integer, pTab->iDb, 0);
        sqliteVdbeAddOp(v, OP_OpenRead, iCur, pTab->tnum);
      }
      sqliteVdbeAddOp(v, OP_MoveTo, iCur, 0);

      sqliteVdbeAddOp(v, OP_Recno, iCur, 0);
      sqliteVdbeAddOp(v, OP_RowData, iCur, 0);
      sqliteVdbeAddOp(v, OP_PutIntKey, oldIdx, 0);
      if( !isView ){
        sqliteVdbeAddOp(v, OP_Close, iCur, 0);
      }

      sqliteCodeRowTrigger(pParse, TK_DELETE, 0, TK_BEFORE, pTab, -1, 
          oldIdx, (pParse->trigStack)?pParse->trigStack->orconf:OE_Default,
	  addr);
    }

................................................................................
    if( !isView ){
      /* Open cursors for the table we are deleting from and all its
      ** indices.  If there are row triggers, this happens inside the
      ** OP_ListRead loop because the cursor have to all be closed
      ** before the trigger fires.  If there are no row triggers, the
      ** cursors are opened only once on the outside the loop.
      */
      pParse->nTab = iCur + 1;
      sqliteVdbeAddOp(v, OP_Integer, pTab->iDb, 0);
      sqliteVdbeAddOp(v, OP_OpenWrite, iCur, pTab->tnum);
      for(i=1, pIdx=pTab->pIndex; pIdx; i++, pIdx=pIdx->pNext){
        sqliteVdbeAddOp(v, OP_Integer, pIdx->iDb, 0);
        sqliteVdbeAddOp(v, OP_OpenWrite, pParse->nTab++, pIdx->tnum);
      }

      /* This is the beginning of the delete loop when there are no
      ** row triggers */
      if( !row_triggers_exist ){ 
        addr = sqliteVdbeAddOp(v, OP_ListRead, 0, end);
      }

      /* Delete the row */
      sqliteGenerateRowDelete(db, v, pTab, iCur, pParse->trigStack==0);
    }

    /* If there are row triggers, close all cursors then invoke
    ** the AFTER triggers
    */
    if( row_triggers_exist ){
      if( !isView ){
        for(i=1, pIdx=pTab->pIndex; pIdx; i++, pIdx=pIdx->pNext){
          sqliteVdbeAddOp(v, OP_Close, iCur + i, pIdx->tnum);
        }
        sqliteVdbeAddOp(v, OP_Close, iCur, 0);
      }
      sqliteCodeRowTrigger(pParse, TK_DELETE, 0, TK_AFTER, pTab, -1, 
          oldIdx, (pParse->trigStack)?pParse->trigStack->orconf:OE_Default,
	  addr);
    }

    /* End of the delete loop */
................................................................................
    sqliteVdbeAddOp(v, OP_Goto, 0, addr);
    sqliteVdbeResolveLabel(v, end);
    sqliteVdbeAddOp(v, OP_ListReset, 0, 0);

    /* Close the cursors after the loop if there are no row triggers */
    if( !row_triggers_exist ){
      for(i=1, pIdx=pTab->pIndex; pIdx; i++, pIdx=pIdx->pNext){
        sqliteVdbeAddOp(v, OP_Close, iCur + i, pIdx->tnum);
      }
      sqliteVdbeAddOp(v, OP_Close, iCur, 0);
      pParse->nTab = iCur;
    }
  }
  sqliteEndWriteOperation(pParse);

  /*
  ** Return the number of rows that were deleted.
  */
................................................................................
** and then generates code to remove both the table record and all index
** entries that point to that record.
*/
void sqliteGenerateRowDelete(
  sqlite *db,        /* The database containing the index */
  Vdbe *v,           /* Generate code into this VDBE */
  Table *pTab,       /* Table containing the row to be deleted */
  int iCur,          /* Cursor number for the table */
  int count          /* Increment the row change counter */
){
  int addr;
  addr = sqliteVdbeAddOp(v, OP_NotExists, iCur, 0);
  sqliteGenerateRowIndexDelete(db, v, pTab, iCur, 0);
  sqliteVdbeAddOp(v, OP_Delete, iCur, count);
  sqliteVdbeChangeP2(v, addr, sqliteVdbeCurrentAddr(v));
}

/*
** This routine generates VDBE code that causes the deletion of all
** index entries associated with a single row of a single table.
**
** The VDBE must be in a particular state when this routine is called.
** These are the requirements:
**
**   1.  A read/write cursor pointing to pTab, the table containing the row
**       to be deleted, must be opened as cursor number "iCur".
**
**   2.  Read/write cursors for all indices of pTab must be open as
**       cursor number iCur+i for the i-th index.
**
**   3.  The "iCur" cursor must be pointing to the row that is to be
**       deleted.
*/
void sqliteGenerateRowIndexDelete(
  sqlite *db,        /* The database containing the index */
  Vdbe *v,           /* Generate code into this VDBE */
  Table *pTab,       /* Table containing the row to be deleted */
  int iCur,          /* Cursor number for the table */
  char *aIdxUsed     /* Only delete if aIdxUsed!=0 && aIdxUsed[i]!=0 */
){
  int i;
  Index *pIdx;

  for(i=1, pIdx=pTab->pIndex; pIdx; i++, pIdx=pIdx->pNext){
    int j;
    if( aIdxUsed!=0 && aIdxUsed[i-1]==0 ) continue;
    sqliteVdbeAddOp(v, OP_Recno, iCur, 0);
    for(j=0; j<pIdx->nColumn; j++){
      int idx = pIdx->aiColumn[j];
      if( idx==pTab->iPKey ){
        sqliteVdbeAddOp(v, OP_Dup, j, 0);
      }else{
        sqliteVdbeAddOp(v, OP_Column, iCur, idx);
      }
    }
    sqliteVdbeAddOp(v, OP_MakeIdxKey, pIdx->nColumn, 0);
    if( db->file_format>=4 ) sqliteAddIdxKeyType(v, pIdx);
    sqliteVdbeAddOp(v, OP_IdxDelete, iCur+i, 0);
  }
}

Changes to src/expr.c.

8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
...
190
191
192
193
194
195
196

197
198
199
200
201
202
203
...
398
399
400
401
402
403
404
405
406
407
408
409


410
411


412
413
414
415
416
417
418
...
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
...
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
...
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
...
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
...
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
...
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
**    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.94 2003/04/22 20:30:39 drh Exp $
*/
#include "sqliteInt.h"
#include <ctype.h>

/*
** Construct a new expression node and return a pointer to it.  Memory
** for this node is obtained from sqliteMalloc().  The calling function
................................................................................
  pNew = sqliteMalloc( nByte );
  if( pNew==0 ) return 0;
  pNew->nSrc = p->nSrc;
  for(i=0; i<p->nSrc; i++){
    pNew->a[i].zName = sqliteStrDup(p->a[i].zName);
    pNew->a[i].zAlias = sqliteStrDup(p->a[i].zAlias);
    pNew->a[i].jointype = p->a[i].jointype;

    pNew->a[i].pTab = 0;
    pNew->a[i].pSelect = sqliteSelectDup(p->a[i].pSelect);
    pNew->a[i].pOn = sqliteExprDup(p->a[i].pOn);
    pNew->a[i].pUsing = sqliteIdListDup(p->a[i].pUsing);
  }
  return pNew;
}
................................................................................
** into a memory cell.
**
** Unknown columns or tables provoke an error.  The function returns
** the number of errors seen and leaves an error message on pParse->zErrMsg.
*/
int sqliteExprResolveIds(
  Parse *pParse,     /* The parser context */
  int base,          /* VDBE cursor number for first entry in pTabList */
  SrcList *pTabList, /* List of tables used to resolve column names */
  ExprList *pEList,  /* List of expressions used to resolve "AS" */
  Expr *pExpr        /* The expression to be analyzed. */
){


  if( pExpr==0 || pTabList==0 ) return 0;
  assert( base+pTabList->nSrc<=pParse->nTab );


  switch( pExpr->op ){
    /* Double-quoted strings (ex: "abc") are used as identifiers if
    ** possible.  Otherwise they remain as strings.  Single-quoted
    ** strings (ex: 'abc') are always string literals.
    */
    case TK_STRING: {
      if( pExpr->token.z[0]=='\'' ) break;
................................................................................
    **         a SELECT statement.  (For example, match against 'x' in
    **         "SELECT a+b AS 'x' FROM t1".)
    **
    **     3.  One of the special names "ROWID", "OID", or "_ROWID_".
    */
    case TK_ID: {
      int cnt = 0;      /* Number of matches */
      int i;            /* Loop counter */
      char *z;
      int iDb = -1;

      assert( pExpr->token.z );
      z = sqliteStrNDup(pExpr->token.z, pExpr->token.n);
      sqliteDequote(z);
      if( z==0 ) return 1;
................................................................................
        Table *pTab = pTabList->a[i].pTab;
        if( pTab==0 ) continue;
        iDb = pTab->iDb;
        assert( pTab->nCol>0 );
        for(j=0; j<pTab->nCol; j++){
          if( sqliteStrICmp(pTab->aCol[j].zName, z)==0 ){
            cnt++;
            pExpr->iTable = i + base;
            pExpr->iDb = pTab->iDb;
            if( j==pTab->iPKey ){
              /* Substitute the record number for the INTEGER PRIMARY KEY */
              pExpr->iColumn = -1;
              pExpr->dataType = SQLITE_SO_NUM;
            }else{
              pExpr->iColumn = j;
................................................................................
            pExpr->iColumn = j;
            pExpr->pLeft = sqliteExprDup(pEList->a[j].pExpr);
          }
        } 
      }
      if( cnt==0 && iDb>=0 && sqliteIsRowid(z) ){
        pExpr->iColumn = -1;
        pExpr->iTable = base;
        pExpr->iDb = iDb;
        cnt = 1 + (pTabList->nSrc>1);
        pExpr->op = TK_COLUMN;
        pExpr->dataType = SQLITE_SO_NUM;
      }
      sqliteFree(z);
      if( cnt==0 && pExpr->token.z[0]!='"' ){
................................................................................
        sqliteErrorMsg(pParse, "no such column: %T", &pExpr->token);
        return 1;
      }else if( cnt>1 ){
        sqliteErrorMsg(pParse, "ambiguous column name: %T", &pExpr->token);
        return 1;
      }
      if( pExpr->op==TK_COLUMN ){
        sqliteAuthRead(pParse, pExpr, pTabList, base);
      }
      break; 
    }
  
    /* A table name and column name:     ID.ID
    ** Or a database, table and column:  ID.ID.ID
    */
................................................................................
          zTab = pTab->zName;
          if( zTab==0 || sqliteStrICmp(zTab, zLeft)!=0 ) continue;
          if( zDb!=0 && sqliteStrICmp(db->aDb[pTab->iDb].zName, zDb)!=0 ){
            continue;
          }
        }
        if( 0==(cntTab++) ){
          pExpr->iTable = i + base;
          pExpr->iDb = pTab->iDb;
        }
        for(j=0; j<pTab->nCol; j++){
          if( sqliteStrICmp(pTab->aCol[j].zName, zRight)==0 ){
            cnt++;
            pExpr->iTable = i + base;
            pExpr->iDb = pTab->iDb;
            /* Substitute the rowid (column -1) for the INTEGER PRIMARY KEY */
            pExpr->iColumn = j==pTab->iPKey ? -1 : j;
            pExpr->dataType = pTab->aCol[j].sortOrder & SQLITE_SO_TYPEMASK;
          }
        }
      }
................................................................................
        return 1;
      }
      sqliteExprDelete(pExpr->pLeft);
      pExpr->pLeft = 0;
      sqliteExprDelete(pExpr->pRight);
      pExpr->pRight = 0;
      pExpr->op = TK_COLUMN;
      sqliteAuthRead(pParse, pExpr, pTabList, base);
      break;
    }

    case TK_IN: {
      Vdbe *v = sqliteGetVdbe(pParse);
      if( v==0 ) return 1;
      if( sqliteExprResolveIds(pParse, base, pTabList, pEList, pExpr->pLeft) ){
        return 1;
      }
      if( pExpr->pSelect ){
        /* Case 1:     expr IN (SELECT ...)
        **
        ** Generate code to write the results of the select into a temporary
        ** table.  The cursor number of the temporary table has already
................................................................................
      }
      break;
    }

    /* For all else, just recursively walk the tree */
    default: {
      if( pExpr->pLeft
      && sqliteExprResolveIds(pParse, base, pTabList, pEList, pExpr->pLeft) ){
        return 1;
      }
      if( pExpr->pRight 
      && sqliteExprResolveIds(pParse, base, pTabList, pEList, pExpr->pRight) ){
        return 1;
      }
      if( pExpr->pList ){
        int i;
        ExprList *pList = pExpr->pList;
        for(i=0; i<pList->nExpr; i++){
          Expr *pArg = pList->a[i].pExpr;
          if( sqliteExprResolveIds(pParse, base, pTabList, pEList, pArg) ){
            return 1;
          }
        }
      }
    }
  }
  return 0;







|







 







>







 







<




>
>

|
>
>







 







<







 







|







 







|







 







|







 







|





|







 







|






|







 







|



|







|







8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
...
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
...
399
400
401
402
403
404
405

406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
...
430
431
432
433
434
435
436

437
438
439
440
441
442
443
...
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
...
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
...
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
...
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
...
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
**    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.95 2003/05/02 14:32:13 drh Exp $
*/
#include "sqliteInt.h"
#include <ctype.h>

/*
** Construct a new expression node and return a pointer to it.  Memory
** for this node is obtained from sqliteMalloc().  The calling function
................................................................................
  pNew = sqliteMalloc( nByte );
  if( pNew==0 ) return 0;
  pNew->nSrc = p->nSrc;
  for(i=0; i<p->nSrc; i++){
    pNew->a[i].zName = sqliteStrDup(p->a[i].zName);
    pNew->a[i].zAlias = sqliteStrDup(p->a[i].zAlias);
    pNew->a[i].jointype = p->a[i].jointype;
    pNew->a[i].iCursor = p->a[i].iCursor;
    pNew->a[i].pTab = 0;
    pNew->a[i].pSelect = sqliteSelectDup(p->a[i].pSelect);
    pNew->a[i].pOn = sqliteExprDup(p->a[i].pOn);
    pNew->a[i].pUsing = sqliteIdListDup(p->a[i].pUsing);
  }
  return pNew;
}
................................................................................
** into a memory cell.
**
** Unknown columns or tables provoke an error.  The function returns
** the number of errors seen and leaves an error message on pParse->zErrMsg.
*/
int sqliteExprResolveIds(
  Parse *pParse,     /* The parser context */

  SrcList *pTabList, /* List of tables used to resolve column names */
  ExprList *pEList,  /* List of expressions used to resolve "AS" */
  Expr *pExpr        /* The expression to be analyzed. */
){
  int i;

  if( pExpr==0 || pTabList==0 ) return 0;
  for(i=0; i<pTabList->nSrc; i++){
    assert( pTabList->a[i].iCursor>=0 && pTabList->a[i].iCursor<pParse->nTab );
  }
  switch( pExpr->op ){
    /* Double-quoted strings (ex: "abc") are used as identifiers if
    ** possible.  Otherwise they remain as strings.  Single-quoted
    ** strings (ex: 'abc') are always string literals.
    */
    case TK_STRING: {
      if( pExpr->token.z[0]=='\'' ) break;
................................................................................
    **         a SELECT statement.  (For example, match against 'x' in
    **         "SELECT a+b AS 'x' FROM t1".)
    **
    **     3.  One of the special names "ROWID", "OID", or "_ROWID_".
    */
    case TK_ID: {
      int cnt = 0;      /* Number of matches */

      char *z;
      int iDb = -1;

      assert( pExpr->token.z );
      z = sqliteStrNDup(pExpr->token.z, pExpr->token.n);
      sqliteDequote(z);
      if( z==0 ) return 1;
................................................................................
        Table *pTab = pTabList->a[i].pTab;
        if( pTab==0 ) continue;
        iDb = pTab->iDb;
        assert( pTab->nCol>0 );
        for(j=0; j<pTab->nCol; j++){
          if( sqliteStrICmp(pTab->aCol[j].zName, z)==0 ){
            cnt++;
            pExpr->iTable = pTabList->a[i].iCursor;
            pExpr->iDb = pTab->iDb;
            if( j==pTab->iPKey ){
              /* Substitute the record number for the INTEGER PRIMARY KEY */
              pExpr->iColumn = -1;
              pExpr->dataType = SQLITE_SO_NUM;
            }else{
              pExpr->iColumn = j;
................................................................................
            pExpr->iColumn = j;
            pExpr->pLeft = sqliteExprDup(pEList->a[j].pExpr);
          }
        } 
      }
      if( cnt==0 && iDb>=0 && sqliteIsRowid(z) ){
        pExpr->iColumn = -1;
        pExpr->iTable = pTabList->a[0].iCursor;
        pExpr->iDb = iDb;
        cnt = 1 + (pTabList->nSrc>1);
        pExpr->op = TK_COLUMN;
        pExpr->dataType = SQLITE_SO_NUM;
      }
      sqliteFree(z);
      if( cnt==0 && pExpr->token.z[0]!='"' ){
................................................................................
        sqliteErrorMsg(pParse, "no such column: %T", &pExpr->token);
        return 1;
      }else if( cnt>1 ){
        sqliteErrorMsg(pParse, "ambiguous column name: %T", &pExpr->token);
        return 1;
      }
      if( pExpr->op==TK_COLUMN ){
        sqliteAuthRead(pParse, pExpr, pTabList);
      }
      break; 
    }
  
    /* A table name and column name:     ID.ID
    ** Or a database, table and column:  ID.ID.ID
    */
................................................................................
          zTab = pTab->zName;
          if( zTab==0 || sqliteStrICmp(zTab, zLeft)!=0 ) continue;
          if( zDb!=0 && sqliteStrICmp(db->aDb[pTab->iDb].zName, zDb)!=0 ){
            continue;
          }
        }
        if( 0==(cntTab++) ){
          pExpr->iTable = pTabList->a[i].iCursor;
          pExpr->iDb = pTab->iDb;
        }
        for(j=0; j<pTab->nCol; j++){
          if( sqliteStrICmp(pTab->aCol[j].zName, zRight)==0 ){
            cnt++;
            pExpr->iTable = pTabList->a[i].iCursor;
            pExpr->iDb = pTab->iDb;
            /* Substitute the rowid (column -1) for the INTEGER PRIMARY KEY */
            pExpr->iColumn = j==pTab->iPKey ? -1 : j;
            pExpr->dataType = pTab->aCol[j].sortOrder & SQLITE_SO_TYPEMASK;
          }
        }
      }
................................................................................
        return 1;
      }
      sqliteExprDelete(pExpr->pLeft);
      pExpr->pLeft = 0;
      sqliteExprDelete(pExpr->pRight);
      pExpr->pRight = 0;
      pExpr->op = TK_COLUMN;
      sqliteAuthRead(pParse, pExpr, pTabList);
      break;
    }

    case TK_IN: {
      Vdbe *v = sqliteGetVdbe(pParse);
      if( v==0 ) return 1;
      if( sqliteExprResolveIds(pParse, pTabList, pEList, pExpr->pLeft) ){
        return 1;
      }
      if( pExpr->pSelect ){
        /* Case 1:     expr IN (SELECT ...)
        **
        ** Generate code to write the results of the select into a temporary
        ** table.  The cursor number of the temporary table has already
................................................................................
      }
      break;
    }

    /* For all else, just recursively walk the tree */
    default: {
      if( pExpr->pLeft
      && sqliteExprResolveIds(pParse, pTabList, pEList, pExpr->pLeft) ){
        return 1;
      }
      if( pExpr->pRight 
      && sqliteExprResolveIds(pParse, pTabList, pEList, pExpr->pRight) ){
        return 1;
      }
      if( pExpr->pList ){
        int i;
        ExprList *pList = pExpr->pList;
        for(i=0; i<pList->nExpr; i++){
          Expr *pArg = pList->a[i].pExpr;
          if( sqliteExprResolveIds(pParse, pTabList, pEList, pArg) ){
            return 1;
          }
        }
      }
    }
  }
  return 0;

Changes to src/insert.c.

8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
..
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
...
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
**    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 INSERT statements in SQLite.
**
** $Id: insert.c,v 1.82 2003/04/24 01:45:04 drh Exp $
*/
#include "sqliteInt.h"

/*
** This routine is call to handle SQL of the following forms:
**
**    insert into TABLE (IDLIST) values(EXPRLIST)
................................................................................
  Table *pTab;          /* The table to insert into */
  char *zTab;           /* Name of the table into which we are inserting */
  const char *zDb;      /* Name of the database holding this table */
  int i, j, idx;        /* Loop counters */
  Vdbe *v;              /* Generate code into this virtual machine */
  Index *pIdx;          /* For looping over indices of the table */
  int nColumn;          /* Number of columns in the data */
  int base;             /* First available cursor */
  int iCont, iBreak;    /* Beginning and end of the loop over srcTab */
  sqlite *db;           /* The main database structure */
  int keyColumn = -1;   /* Column that is the INTEGER PRIMARY KEY */
  int endOfLoop;        /* Label for the end of the insertion loop */
  int useTempTable;     /* Store SELECT results in intermediate table */
  int srcTab;           /* Data comes from this temporary cursor if >=0 */
  int iSelectLoop;      /* Address of code that implements the SELECT */
................................................................................
    assert( pList!=0 );
    srcTab = -1;
    useTempTable = 0;
    assert( pList );
    nColumn = pList->nExpr;
    dummy.nSrc = 0;
    for(i=0; i<nColumn; i++){
      if( sqliteExprResolveIds(pParse, 0, &dummy, 0, pList->a[i].pExpr) ){
        goto insert_cleanup;
      }
      if( sqliteExprCheck(pParse, pList->a[i].pExpr, 0, 0) ){
        goto insert_cleanup;
      }
    }
  }







|







 







|







 







|







8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
..
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
...
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
**    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 INSERT statements in SQLite.
**
** $Id: insert.c,v 1.83 2003/05/02 14:32:13 drh Exp $
*/
#include "sqliteInt.h"

/*
** This routine is call to handle SQL of the following forms:
**
**    insert into TABLE (IDLIST) values(EXPRLIST)
................................................................................
  Table *pTab;          /* The table to insert into */
  char *zTab;           /* Name of the table into which we are inserting */
  const char *zDb;      /* Name of the database holding this table */
  int i, j, idx;        /* Loop counters */
  Vdbe *v;              /* Generate code into this virtual machine */
  Index *pIdx;          /* For looping over indices of the table */
  int nColumn;          /* Number of columns in the data */
  int base;             /* VDBE Cursor number for pTab */
  int iCont, iBreak;    /* Beginning and end of the loop over srcTab */
  sqlite *db;           /* The main database structure */
  int keyColumn = -1;   /* Column that is the INTEGER PRIMARY KEY */
  int endOfLoop;        /* Label for the end of the insertion loop */
  int useTempTable;     /* Store SELECT results in intermediate table */
  int srcTab;           /* Data comes from this temporary cursor if >=0 */
  int iSelectLoop;      /* Address of code that implements the SELECT */
................................................................................
    assert( pList!=0 );
    srcTab = -1;
    useTempTable = 0;
    assert( pList );
    nColumn = pList->nExpr;
    dummy.nSrc = 0;
    for(i=0; i<nColumn; i++){
      if( sqliteExprResolveIds(pParse, &dummy, 0, pList->a[i].pExpr) ){
        goto insert_cleanup;
      }
      if( sqliteExprCheck(pParse, pList->a[i].pExpr, 0, 0) ){
        goto insert_cleanup;
      }
    }
  }

Changes to src/select.c.

8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
...
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
...
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705



706
707
708
709
710
711
712
...
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
...
747
748
749
750
751
752
753
754
755
756



757
758
759
760
761
762
763
...
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
....
1364
1365
1366
1367
1368
1369
1370
1371
1372
1373
1374
1375
1376
1377
1378
1379
....
1421
1422
1423
1424
1425
1426
1427
1428
1429
1430
1431
1432
1433
1434
1435
1436
....
1462
1463
1464
1465
1466
1467
1468
1469
1470
1471
1472
1473
1474
1475
1476
1477
1478
1479
1480
1481
1482
1483
1484
1485
1486
1487
1488
1489
1490
1491
1492
1493
1494
1495
1496
1497
1498
1499

1500
1501
1502
1503
1504
1505
1506
1507
1508
1509
1510
1511
1512
1513
1514
1515
1516
....
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
....
1617
1618
1619
1620
1621
1622
1623

1624
1625
1626
1627
1628
1629
1630
1631
1632
....
1642
1643
1644
1645
1646
1647
1648
1649
1650
1651
1652
1653
1654
1655
1656
1657
1658
1659
1660
1661
1662
1663
1664
1665
1666
1667
1668
1669
1670
1671
1672
1673
1674
1675
1676
1677
1678
1679
1680
1681
1682
1683
1684
1685
1686
1687
1688
1689
1690
1691
1692
1693
1694
1695
1696
1697
1698
1699
1700
1701
1702
1703
1704
1705
1706
1707
1708
1709
1710
....
1712
1713
1714
1715
1716
1717
1718
1719
1720
1721
1722
1723
1724
1725
1726
1727
1728
1729
1730
1731
1732
1733
1734
1735
1736
1737
1738
....
1813
1814
1815
1816
1817
1818
1819
1820
1821
1822
1823
1824
1825
1826
1827
1828
1829
1830
1831
1832
1833
1834
1835
1836
1837
1838
1839
....
1924
1925
1926
1927
1928
1929
1930
1931
1932
1933
1934
1935
1936
1937
1938
....
1945
1946
1947
1948
1949
1950
1951
1952
1953
1954
1955
1956
1957

1958
1959
1960
1961
1962
1963
1964
....
1996
1997
1998
1999
2000
2001
2002
2003
2004
2005
2006
2007
2008
2009
2010
2011
2012
2013
2014
2015
2016
2017
2018
2019
2020
2021
2022
2023
2024
2025
2026
2027
2028
2029
2030
2031
....
2032
2033
2034
2035
2036
2037
2038
2039
2040
2041
2042
2043
2044
2045
2046
....
2060
2061
2062
2063
2064
2065
2066
2067
2068
2069
2070
2071
2072
2073
2074
....
2098
2099
2100
2101
2102
2103
2104
2105
2106
2107
2108
2109
2110
2111
2112
....
2130
2131
2132
2133
2134
2135
2136
2137
2138
2139
2140
2141
2142
2143
2144
2145
....
2159
2160
2161
2162
2163
2164
2165
2166
2167
2168
2169
2170
2171
2172
2173
....
2233
2234
2235
2236
2237
2238
2239
2240
2241
2242
2243
2244
2245
2246
2247
**    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.135 2003/04/29 16:20:46 drh Exp $
*/
#include "sqliteInt.h"


/*
** Allocate a new Select structure and return a pointer to that
** structure.
................................................................................
** that uses that table becomes part of the ON clause for the join.
**
** It is important to enphasize that this is not exactly how oracle8
** works.  But it is close enough so that one can construct queries that
** will work correctly for both SQLite and Oracle8.
*/
static int sqliteOracle8JoinFixup(
  int base,         /* VDBE cursor number for first table in pSrc */
  SrcList *pSrc,    /* List of tables being joined */
  Expr *pWhere      /* The WHERE clause of the SELECT statement */
){
  int rc = 0;
  if( ExprHasProperty(pWhere, EP_Oracle8Join) && pWhere->op==TK_COLUMN ){
    int idx = pWhere->iTable - base;



    assert( idx>=0 && idx<pSrc->nSrc );
    if( idx>0 ){
      pSrc->a[idx-1].jointype &= ~JT_INNER;
      pSrc->a[idx-1].jointype |= JT_OUTER|JT_LEFT;
      return 1;
    }
  }
  if( pWhere->pRight ){
    rc = sqliteOracle8JoinFixup(base, pSrc, pWhere->pRight);
  }
  if( pWhere->pLeft ){
    rc |= sqliteOracle8JoinFixup(base, pSrc, pWhere->pLeft);
  }
  if( pWhere->pList ){
    int i;
    ExprList *pList = pWhere->pList;
    for(i=0; i<pList->nExpr && rc==0; i++){
      rc |= sqliteOracle8JoinFixup(base, pSrc, pList->a[i].pExpr);
    }
  }
  if( rc==1 && (pWhere->op==TK_AND || pWhere->op==TK_EQ) ){
    setJoinExpr(pWhere);
    rc = 0;
  }
  return rc;
................................................................................
** The "datatype" for a result that is a column of a type is the
** datatype definition extracted from the CREATE TABLE statement.
** The datatype for an expression is either TEXT or NUMERIC.  The
** datatype for a ROWID field is INTEGER.
*/
static void generateColumnTypes(
  Parse *pParse,      /* Parser context */
  int base,           /* VDBE cursor corresponding to first entry in pTabList */
  SrcList *pTabList,  /* List of tables */
  ExprList *pEList    /* Expressions defining the result set */
){
  Vdbe *v = pParse->pVdbe;
  int i;
  if( pParse->useCallback && (pParse->db->flags & SQLITE_ReportTypes)==0 ){
    return;
  }
  for(i=0; i<pEList->nExpr; i++){
    Expr *p = pEList->a[i].pExpr;
    char *zType = 0;
    if( p==0 ) continue;
    if( p->op==TK_COLUMN && pTabList ){
      Table *pTab = pTabList->a[p->iTable - base].pTab;
      int iCol = p->iColumn;



      if( iCol<0 ) iCol = pTab->iPKey;
      assert( iCol==-1 || (iCol>=0 && iCol<pTab->nCol) );
      if( iCol<0 ){
        zType = "INTEGER";
      }else{
        zType = pTab->aCol[iCol].zType;
      }
................................................................................
/*
** Generate code that will tell the VDBE the names of columns
** in the result set.  This information is used to provide the
** azCol[] vaolues in the callback.
*/
static void generateColumnNames(
  Parse *pParse,      /* Parser context */
  int base,           /* VDBE cursor corresponding to first entry in pTabList */
  SrcList *pTabList,  /* List of tables */
  ExprList *pEList    /* Expressions defining the result set */
){
  Vdbe *v = pParse->pVdbe;
  int i;
  if( pParse->colNamesSet || v==0 || sqlite_malloc_failed ) return;
  pParse->colNamesSet = 1;
  for(i=0; i<pEList->nExpr; i++){
    Expr *p;
    char *zType = 0;
    int showFullNames;
    p = pEList->a[i].pExpr;
................................................................................
      char *zName = pEList->a[i].zName;
      sqliteVdbeAddOp(v, OP_ColumnName, i, 0);
      sqliteVdbeChangeP3(v, -1, zName, strlen(zName));
      continue;
    }
    showFullNames = (pParse->db->flags & SQLITE_FullColNames)!=0;
    if( p->op==TK_COLUMN && pTabList ){
      Table *pTab = pTabList->a[p->iTable - base].pTab;
      char *zCol;
      int iCol = p->iColumn;



      if( iCol<0 ) iCol = pTab->iPKey;
      assert( iCol==-1 || (iCol>=0 && iCol<pTab->nCol) );
      if( iCol<0 ){
        zCol = "_ROWID_";
        zType = "INTEGER";
      }else{
        zCol = pTab->aCol[iCol].zName;
................................................................................
        int addr = sqliteVdbeAddOp(v,OP_ColumnName, i, 0);
        sqliteVdbeChangeP3(v, -1, p->span.z, p->span.n);
        sqliteVdbeCompressSpace(v, addr);
      }else if( pTabList->nSrc>1 || showFullNames ){
        char *zName = 0;
        char *zTab;
 
        zTab = pTabList->a[p->iTable - base].zAlias;
        if( showFullNames || zTab==0 ) zTab = pTab->zName;
        sqliteSetString(&zName, zTab, ".", zCol, 0);
        sqliteVdbeAddOp(v, OP_ColumnName, i, 0);
        sqliteVdbeChangeP3(v, -1, zName, strlen(zName));
        sqliteFree(zName);
      }else{
        sqliteVdbeAddOp(v, OP_ColumnName, i, 0);
................................................................................
      /* Convert the data in the temporary table into whatever form
      ** it is that we currently need.
      */      
      if( eDest!=priorOp || unionTab!=iParm ){
        int iCont, iBreak, iStart;
        assert( p->pEList );
        if( eDest==SRT_Callback ){
          generateColumnNames(pParse, p->base, 0, p->pEList);
          generateColumnTypes(pParse, p->base, p->pSrc, p->pEList);
        }
        iBreak = sqliteVdbeMakeLabel(v);
        iCont = sqliteVdbeMakeLabel(v);
        sqliteVdbeAddOp(v, OP_Rewind, unionTab, iBreak);
        iStart = sqliteVdbeCurrentAddr(v);
        multiSelectSortOrder(p, p->pOrderBy);
        rc = selectInnerLoop(pParse, p, p->pEList, unionTab, p->pEList->nExpr,
................................................................................
      if( rc ) return rc;

      /* Generate code to take the intersection of the two temporary
      ** tables.
      */
      assert( p->pEList );
      if( eDest==SRT_Callback ){
        generateColumnNames(pParse, p->base, 0, p->pEList);
        generateColumnTypes(pParse, p->base, p->pSrc, p->pEList);
      }
      iBreak = sqliteVdbeMakeLabel(v);
      iCont = sqliteVdbeMakeLabel(v);
      sqliteVdbeAddOp(v, OP_Rewind, tab1, iBreak);
      iStart = sqliteVdbeAddOp(v, OP_FullKey, tab1, 0);
      sqliteVdbeAddOp(v, OP_NotFound, tab2, iCont);
      multiSelectSortOrder(p, p->pOrderBy);
................................................................................
    (pParse->useCallback==0 || (pParse->db->flags & SQLITE_NullCallback)!=0)
  ){
    sqliteVdbeAddOp(v, OP_NullCallback, p->pEList->nExpr, 0);
  }
  return 0;
}

/*
** Recursively scan through an expression tree.  For every reference
** to a column in table number iFrom, change that reference to the
** same column in table number iTo.
*/
static void changeTablesInList(ExprList*, int, int);  /* Forward Declaration */
static void changeTables(Expr *pExpr, int iFrom, int iTo){
  if( pExpr==0 ) return;
  if( pExpr->op==TK_COLUMN && pExpr->iTable==iFrom ){
    pExpr->iTable = iTo;
  }else{
    changeTables(pExpr->pLeft, iFrom, iTo);
    changeTables(pExpr->pRight, iFrom, iTo);
    changeTablesInList(pExpr->pList, iFrom, iTo);
  }
}
static void changeTablesInList(ExprList *pList, int iFrom, int iTo){
  if( pList ){
    int i;
    for(i=0; i<pList->nExpr; i++){
      changeTables(pList->a[i].pExpr, iFrom, iTo);
    }
  }
}

/*
** Scan through the expression pExpr.  Replace every reference to
** a column in table number iTable with a copy of the corresponding
** entry in pEList.  (But leave references to the ROWID column 
** unchanged.)  When making a copy of an expression in pEList, change
** references to columns in table iSub into references to table iTable.

**
** This routine is part of the flattening procedure.  A subquery
** whose result set is defined by pEList appears as entry in the
** FROM clause of a SELECT such that the VDBE cursor assigned to that
** FORM clause entry is iTable.  This routine make the necessary 
** changes to pExpr so that it refers directly to the source table
** of the subquery rather the result set of the subquery.
*/
static void substExprList(ExprList*,int,ExprList*,int);  /* Forward Decl */
static void substExpr(Expr *pExpr, int iTable, ExprList *pEList, int iSub){
  if( pExpr==0 ) return;
  if( pExpr->op==TK_COLUMN && pExpr->iTable==iTable && pExpr->iColumn>=0 ){
    Expr *pNew;
    assert( pEList!=0 && pExpr->iColumn<pEList->nExpr );
    assert( pExpr->pLeft==0 && pExpr->pRight==0 && pExpr->pList==0 );
    pNew = pEList->a[pExpr->iColumn].pExpr;
    assert( pNew!=0 );
................................................................................
    assert( pExpr->pList==0 );
    pExpr->pList = sqliteExprListDup(pNew->pList);
    pExpr->iTable = pNew->iTable;
    pExpr->iColumn = pNew->iColumn;
    pExpr->iAgg = pNew->iAgg;
    sqliteTokenCopy(&pExpr->token, &pNew->token);
    sqliteTokenCopy(&pExpr->span, &pNew->span);
    if( iSub!=iTable ){
      changeTables(pExpr, iSub, iTable);
    }
  }else{
    substExpr(pExpr->pLeft, iTable, pEList, iSub);
    substExpr(pExpr->pRight, iTable, pEList, iSub);
    substExprList(pExpr->pList, iTable, pEList, iSub);
  }
}
static void 
substExprList(ExprList *pList, int iTable, ExprList *pEList, int iSub){
  int i;
  if( pList==0 ) return;
  for(i=0; i<pList->nExpr; i++){
    substExpr(pList->a[i].pExpr, iTable, pEList, iSub);
  }
}

/*
** This routine attempts to flatten subqueries in order to speed
** execution.  It returns 1 if it makes changes and 0 if no flattening
** occurs.
................................................................................
  int isAgg,           /* True if outer SELECT uses aggregate functions */
  int subqueryIsAgg    /* True if the subquery uses aggregate functions */
){
  Select *pSub;       /* The inner query or "subquery" */
  SrcList *pSrc;      /* The FROM clause of the outer query */
  SrcList *pSubSrc;   /* The FROM clause of the subquery */
  ExprList *pList;    /* The result set of the outer query */

  int i;
  int iParent, iSub;
  Expr *pWhere;

  /* Check to see if flattening is permitted.  Return 0 if not.
  */
  if( p==0 ) return 0;
  pSrc = p->pSrc;
  assert( pSrc && iFrom>=0 && iFrom<pSrc->nSrc );
................................................................................
  }
  if( (p->isDistinct || p->nLimit>=0) && subqueryIsAgg ) return 0;
  if( p->pOrderBy && pSub->pOrderBy ) return 0;

  /* If we reach this point, it means flattening is permitted for the
  ** iFrom-th entry of the FROM clause in the outer query.
  */
  iParent = p->base + iFrom;
  iSub = pSub->base;
  substExprList(p->pEList, iParent, pSub->pEList, iSub);
  pList = p->pEList;
  for(i=0; i<pList->nExpr; i++){
    Expr *pExpr;
    if( pList->a[i].zName==0 && (pExpr = pList->a[i].pExpr)->span.z!=0 ){
      pList->a[i].zName = sqliteStrNDup(pExpr->span.z, pExpr->span.n);
    }
  }
  if( isAgg ){
    substExprList(p->pGroupBy, iParent, pSub->pEList, iSub);
    substExpr(p->pHaving, iParent, pSub->pEList, iSub);
  }
  if( pSub->pOrderBy ){
    assert( p->pOrderBy==0 );
    p->pOrderBy = pSub->pOrderBy;
    pSub->pOrderBy = 0;
    changeTablesInList(p->pOrderBy, iSub, iParent);
  }else if( p->pOrderBy ){
    substExprList(p->pOrderBy, iParent, pSub->pEList, iSub);
  }
  if( pSub->pWhere ){
    pWhere = sqliteExprDup(pSub->pWhere);
    if( iParent!=iSub ){
      changeTables(pWhere, iSub, iParent);
    }
  }else{
    pWhere = 0;
  }
  if( subqueryIsAgg ){
    assert( p->pHaving==0 );
    p->pHaving = p->pWhere;
    p->pWhere = pWhere;
    substExpr(p->pHaving, iParent, pSub->pEList, iSub);
    if( pSub->pHaving ){
      Expr *pHaving = sqliteExprDup(pSub->pHaving);
      if( iParent!=iSub ){
        changeTables(pHaving, iSub, iParent);
      }
      if( p->pHaving ){
        p->pHaving = sqliteExpr(TK_AND, p->pHaving, pHaving, 0);
      }else{
        p->pHaving = pHaving;
      }
    }
    assert( p->pGroupBy==0 );
    p->pGroupBy = sqliteExprListDup(pSub->pGroupBy);
    if( iParent!=iSub ){
      changeTablesInList(p->pGroupBy, iSub, iParent);
    }
  }else if( p->pWhere==0 ){
    p->pWhere = pWhere;
  }else{
    substExpr(p->pWhere, iParent, pSub->pEList, iSub);
    if( pWhere ){
      p->pWhere = sqliteExpr(TK_AND, p->pWhere, pWhere, 0);
    }
  }
  p->isDistinct = p->isDistinct || pSub->isDistinct;

  if( pSub->nLimit>=0 ){
................................................................................
      p->nLimit = pSub->nLimit;
    }else if( p->nLimit+p->nOffset > pSub->nLimit+pSub->nOffset ){
      p->nLimit = pSub->nLimit + pSub->nOffset - p->nOffset;
    }
  }
  p->nOffset += pSub->nOffset;

  /* If the subquery contains subqueries of its own, that were not
  ** flattened, then code will have already been generated to put
  ** the results of those sub-subqueries into VDBE cursors relative
  ** to the subquery.  We must translate the cursor number into values
  ** suitable for use by the outer query.
  */
  for(i=0; i<pSubSrc->nSrc; i++){
    Vdbe *v;
    if( pSubSrc->a[i].pSelect==0 ) continue;
    v = sqliteGetVdbe(pParse);
    sqliteVdbeAddOp(v, OP_RenameCursor, pSub->base+i, p->base+i);
  }

  if( pSrc->a[iFrom].pTab && pSrc->a[iFrom].pTab->isTransient ){
    sqliteDeleteTable(0, pSrc->a[iFrom].pTab);
  }
  pSrc->a[iFrom].pTab = pSubSrc->a[0].pTab;
  pSubSrc->a[0].pTab = 0;
  assert( pSrc->a[iFrom].pSelect==pSub );
  pSrc->a[iFrom].pSelect = pSubSrc->a[0].pSelect;
................................................................................

  /* Identify column names if we will be using the callback.  This
  ** step is skipped if the output is going to a table or a memory cell.
  */
  v = sqliteGetVdbe(pParse);
  if( v==0 ) return 0;
  if( eDest==SRT_Callback ){
    generateColumnNames(pParse, p->base, p->pSrc, p->pEList);
    generateColumnTypes(pParse, p->base, p->pSrc, p->pEList);
  }

  /* Generating code to find the min or the max.  Basically all we have
  ** to do is find the first or the last entry in the chosen index.  If
  ** the min() or max() is on the INTEGER PRIMARY KEY, then find the first
  ** or last entry in the main table.
  */
  if( !pParse->schemaVerified && (pParse->db->flags & SQLITE_InTrans)==0 ){
    sqliteCodeVerifySchema(pParse);
  }
  base = p->base;
  sqliteVdbeAddOp(v, OP_Integer, pTab->iDb, 0);
  sqliteVdbeAddOp(v, OP_OpenRead, base, pTab->tnum);
  sqliteVdbeChangeP3(v, -1, pTab->zName, P3_STATIC);
  cont = sqliteVdbeMakeLabel(v);
  if( pIdx==0 ){
    sqliteVdbeAddOp(v, seekOp, base, 0);
  }else{
................................................................................
  SrcList *pTabList;     /* List of tables to select from */
  Expr *pWhere;          /* The WHERE clause.  May be NULL */
  ExprList *pOrderBy;    /* The ORDER BY clause.  May be NULL */
  ExprList *pGroupBy;    /* The GROUP BY clause.  May be NULL */
  Expr *pHaving;         /* The HAVING clause.  May be NULL */
  int isDistinct;        /* True if the DISTINCT keyword is present */
  int distinct;          /* Table to use for the distinct set */
  int base;              /* First cursor available for use */
  int rc = 1;            /* Value to return from this function */

  if( sqlite_malloc_failed || pParse->nErr || p==0 ) return 1;
  if( sqliteAuthCheck(pParse, SQLITE_SELECT, 0, 0, 0) ) return 1;

  /* If there is are a sequence of queries, do the earlier ones first.
  */
................................................................................
  pTabList = p->pSrc;
  pWhere = p->pWhere;
  pOrderBy = p->pOrderBy;
  pGroupBy = p->pGroupBy;
  pHaving = p->pHaving;
  isDistinct = p->isDistinct;

  /* Allocate a block of VDBE cursors, one for each table in the FROM clause.
  ** The WHERE processing requires that the cursors for the tables in the
  ** FROM clause be consecutive.
  */
  base = p->base = pParse->nTab;
  pParse->nTab += pTabList->nSrc;


  /* 
  ** Do not even attempt to generate any code if we have already seen
  ** errors before this routine starts.
  */
  if( pParse->nErr>0 ) goto select_end;

................................................................................

  /* At this point, we should have allocated all the cursors that we
  ** need to handle subquerys and temporary tables.  
  **
  ** Resolve the column names and do a semantics check on all the expressions.
  */
  for(i=0; i<pEList->nExpr; i++){
    if( sqliteExprResolveIds(pParse, base, pTabList, 0, pEList->a[i].pExpr) ){
      goto select_end;
    }
    if( sqliteExprCheck(pParse, pEList->a[i].pExpr, 1, &isAgg) ){
      goto select_end;
    }
  }
  if( pWhere ){
    if( sqliteExprResolveIds(pParse, base, pTabList, pEList, pWhere) ){
      goto select_end;
    }
    if( sqliteExprCheck(pParse, pWhere, 0, 0) ){
      goto select_end;
    }
    sqliteOracle8JoinFixup(base, pTabList, pWhere);
  }
  if( pHaving ){
    if( pGroupBy==0 ){
      sqliteErrorMsg(pParse, "a GROUP BY clause is required before HAVING");
      goto select_end;
    }
    if( sqliteExprResolveIds(pParse, base, pTabList, pEList, pHaving) ){
      goto select_end;
    }
    if( sqliteExprCheck(pParse, pHaving, 1, &isAgg) ){
      goto select_end;
    }
  }
  if( pOrderBy ){
................................................................................
    for(i=0; i<pOrderBy->nExpr; i++){
      int iCol;
      Expr *pE = pOrderBy->a[i].pExpr;
      if( sqliteExprIsInteger(pE, &iCol) && iCol>0 && iCol<=pEList->nExpr ){
        sqliteExprDelete(pE);
        pE = pOrderBy->a[i].pExpr = sqliteExprDup(pEList->a[iCol-1].pExpr);
      }
      if( sqliteExprResolveIds(pParse, base, pTabList, pEList, pE) ){
        goto select_end;
      }
      if( sqliteExprCheck(pParse, pE, isAgg, 0) ){
        goto select_end;
      }
      if( sqliteExprIsConstant(pE) ){
        if( sqliteExprIsInteger(pE, &iCol)==0 ){
................................................................................
    for(i=0; i<pGroupBy->nExpr; i++){
      int iCol;
      Expr *pE = pGroupBy->a[i].pExpr;
      if( sqliteExprIsInteger(pE, &iCol) && iCol>0 && iCol<=pEList->nExpr ){
        sqliteExprDelete(pE);
        pE = pGroupBy->a[i].pExpr = sqliteExprDup(pEList->a[iCol-1].pExpr);
      }
      if( sqliteExprResolveIds(pParse, base, pTabList, pEList, pE) ){
        goto select_end;
      }
      if( sqliteExprCheck(pParse, pE, isAgg, 0) ){
        goto select_end;
      }
      if( sqliteExprIsConstant(pE) ){
        if( sqliteExprIsInteger(pE, &iCol)==0 ){
................................................................................
  v = sqliteGetVdbe(pParse);
  if( v==0 ) goto select_end;

  /* Identify column names if we will be using them in a callback.  This
  ** step is skipped if the output is going to some other destination.
  */
  if( eDest==SRT_Callback ){
    generateColumnNames(pParse, p->base, pTabList, pEList);
  }

  /* Set the limiter
  */
  if( p->nLimit<=0 ){
    p->nLimit = -1;
    p->nOffset = 0;
................................................................................
  for(i=0; i<pTabList->nSrc; i++){
    const char *zSavedAuthContext;
    if( pTabList->a[i].pSelect==0 ) continue;
    if( pTabList->a[i].zName!=0 ){
      zSavedAuthContext = pParse->zAuthContext;
      pParse->zAuthContext = pTabList->a[i].zName;
    }
    sqliteSelect(pParse, pTabList->a[i].pSelect, SRT_TempTable, base+i,
                 p, i, &isAgg);
    if( pTabList->a[i].zName!=0 ){
      pParse->zAuthContext = zSavedAuthContext;
    }
    pTabList = p->pSrc;
    pWhere = p->pWhere;
    if( eDest==SRT_Callback ){
      pOrderBy = p->pOrderBy;
................................................................................
  }

  /* Identify column types if we will be using a callback.  This
  ** step is skipped if the output is going to a destination other
  ** than a callback.
  */
  if( eDest==SRT_Callback ){
    generateColumnTypes(pParse, p->base, pTabList, pEList);
  }

  /* If the output is destined for a temporary table, open that table.
  */
  if( eDest==SRT_TempTable ){
    sqliteVdbeAddOp(v, OP_OpenTemp, iParm, 0);
  }
................................................................................
    sqliteVdbeAddOp(v, OP_OpenTemp, distinct, 1);
  }else{
    distinct = -1;
  }

  /* Begin the database scan
  */
  pWInfo = sqliteWhereBegin(pParse, p->base, pTabList, pWhere, 0, 
                            pGroupBy ? 0 : &pOrderBy);
  if( pWInfo==0 ) goto select_end;

  /* Use the standard inner loop if we are not dealing with
  ** aggregates
  */
  if( !isAgg ){







|







 







<





|
>
>
>








|


|





|







 







<




|








|

>
>
>







 







<




|







 







|


>
>
>







 







|







 







|
|







 







|
|







 








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

|

<
<
>








|
|







 







<
<
<

|
|
|



|



|







 







>

<







 







|
|
|








|
|





<

|



<
<
<







|


<
<
<








<
<
<



|







 







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







 







|
|










|







 







<







 







|
<
<

<
<
>







 







|







|





|






|







 







|







 







|







 







|







 







|
|







 







|







 







|







8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
...
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
...
685
686
687
688
689
690
691

692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
...
729
730
731
732
733
734
735

736
737
738
739
740
741
742
743
744
745
746
747
...
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
...
773
774
775
776
777
778
779
780
781
782
783
784
785
786
787
....
1370
1371
1372
1373
1374
1375
1376
1377
1378
1379
1380
1381
1382
1383
1384
1385
....
1427
1428
1429
1430
1431
1432
1433
1434
1435
1436
1437
1438
1439
1440
1441
1442
....
1468
1469
1470
1471
1472
1473
1474
1475

























1476
1477
1478


1479
1480
1481
1482
1483
1484
1485
1486
1487
1488
1489
1490
1491
1492
1493
1494
1495
1496
....
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
....
1594
1595
1596
1597
1598
1599
1600
1601
1602

1603
1604
1605
1606
1607
1608
1609
....
1619
1620
1621
1622
1623
1624
1625
1626
1627
1628
1629
1630
1631
1632
1633
1634
1635
1636
1637
1638
1639
1640
1641
1642
1643

1644
1645
1646
1647
1648



1649
1650
1651
1652
1653
1654
1655
1656
1657
1658



1659
1660
1661
1662
1663
1664
1665
1666



1667
1668
1669
1670
1671
1672
1673
1674
1675
1676
1677
....
1679
1680
1681
1682
1683
1684
1685













1686
1687
1688
1689
1690
1691
1692
....
1767
1768
1769
1770
1771
1772
1773
1774
1775
1776
1777
1778
1779
1780
1781
1782
1783
1784
1785
1786
1787
1788
1789
1790
1791
1792
1793
....
1878
1879
1880
1881
1882
1883
1884

1885
1886
1887
1888
1889
1890
1891
....
1898
1899
1900
1901
1902
1903
1904
1905


1906


1907
1908
1909
1910
1911
1912
1913
1914
....
1946
1947
1948
1949
1950
1951
1952
1953
1954
1955
1956
1957
1958
1959
1960
1961
1962
1963
1964
1965
1966
1967
1968
1969
1970
1971
1972
1973
1974
1975
1976
1977
1978
1979
1980
1981
....
1982
1983
1984
1985
1986
1987
1988
1989
1990
1991
1992
1993
1994
1995
1996
....
2010
2011
2012
2013
2014
2015
2016
2017
2018
2019
2020
2021
2022
2023
2024
....
2048
2049
2050
2051
2052
2053
2054
2055
2056
2057
2058
2059
2060
2061
2062
....
2080
2081
2082
2083
2084
2085
2086
2087
2088
2089
2090
2091
2092
2093
2094
2095
....
2109
2110
2111
2112
2113
2114
2115
2116
2117
2118
2119
2120
2121
2122
2123
....
2183
2184
2185
2186
2187
2188
2189
2190
2191
2192
2193
2194
2195
2196
2197
**    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.136 2003/05/02 14:32:13 drh Exp $
*/
#include "sqliteInt.h"


/*
** Allocate a new Select structure and return a pointer to that
** structure.
................................................................................
** that uses that table becomes part of the ON clause for the join.
**
** It is important to enphasize that this is not exactly how oracle8
** works.  But it is close enough so that one can construct queries that
** will work correctly for both SQLite and Oracle8.
*/
static int sqliteOracle8JoinFixup(

  SrcList *pSrc,    /* List of tables being joined */
  Expr *pWhere      /* The WHERE clause of the SELECT statement */
){
  int rc = 0;
  if( ExprHasProperty(pWhere, EP_Oracle8Join) && pWhere->op==TK_COLUMN ){
    int idx;
    for(idx=0; idx<pSrc->nSrc; idx++){
      if( pSrc->a[idx].iCursor==pWhere->iTable ) break;
    }
    assert( idx>=0 && idx<pSrc->nSrc );
    if( idx>0 ){
      pSrc->a[idx-1].jointype &= ~JT_INNER;
      pSrc->a[idx-1].jointype |= JT_OUTER|JT_LEFT;
      return 1;
    }
  }
  if( pWhere->pRight ){
    rc = sqliteOracle8JoinFixup(pSrc, pWhere->pRight);
  }
  if( pWhere->pLeft ){
    rc |= sqliteOracle8JoinFixup(pSrc, pWhere->pLeft);
  }
  if( pWhere->pList ){
    int i;
    ExprList *pList = pWhere->pList;
    for(i=0; i<pList->nExpr && rc==0; i++){
      rc |= sqliteOracle8JoinFixup(pSrc, pList->a[i].pExpr);
    }
  }
  if( rc==1 && (pWhere->op==TK_AND || pWhere->op==TK_EQ) ){
    setJoinExpr(pWhere);
    rc = 0;
  }
  return rc;
................................................................................
** The "datatype" for a result that is a column of a type is the
** datatype definition extracted from the CREATE TABLE statement.
** The datatype for an expression is either TEXT or NUMERIC.  The
** datatype for a ROWID field is INTEGER.
*/
static void generateColumnTypes(
  Parse *pParse,      /* Parser context */

  SrcList *pTabList,  /* List of tables */
  ExprList *pEList    /* Expressions defining the result set */
){
  Vdbe *v = pParse->pVdbe;
  int i, j;
  if( pParse->useCallback && (pParse->db->flags & SQLITE_ReportTypes)==0 ){
    return;
  }
  for(i=0; i<pEList->nExpr; i++){
    Expr *p = pEList->a[i].pExpr;
    char *zType = 0;
    if( p==0 ) continue;
    if( p->op==TK_COLUMN && pTabList ){
      Table *pTab;
      int iCol = p->iColumn;
      for(j=0; j<pTabList->nSrc && pTabList->a[j].iCursor!=p->iTable; j++){}
      assert( j<pTabList->nSrc );
      pTab = pTabList->a[j].pTab;
      if( iCol<0 ) iCol = pTab->iPKey;
      assert( iCol==-1 || (iCol>=0 && iCol<pTab->nCol) );
      if( iCol<0 ){
        zType = "INTEGER";
      }else{
        zType = pTab->aCol[iCol].zType;
      }
................................................................................
/*
** Generate code that will tell the VDBE the names of columns
** in the result set.  This information is used to provide the
** azCol[] vaolues in the callback.
*/
static void generateColumnNames(
  Parse *pParse,      /* Parser context */

  SrcList *pTabList,  /* List of tables */
  ExprList *pEList    /* Expressions defining the result set */
){
  Vdbe *v = pParse->pVdbe;
  int i, j;
  if( pParse->colNamesSet || v==0 || sqlite_malloc_failed ) return;
  pParse->colNamesSet = 1;
  for(i=0; i<pEList->nExpr; i++){
    Expr *p;
    char *zType = 0;
    int showFullNames;
    p = pEList->a[i].pExpr;
................................................................................
      char *zName = pEList->a[i].zName;
      sqliteVdbeAddOp(v, OP_ColumnName, i, 0);
      sqliteVdbeChangeP3(v, -1, zName, strlen(zName));
      continue;
    }
    showFullNames = (pParse->db->flags & SQLITE_FullColNames)!=0;
    if( p->op==TK_COLUMN && pTabList ){
      Table *pTab;
      char *zCol;
      int iCol = p->iColumn;
      for(j=0; j<pTabList->nSrc && pTabList->a[j].iCursor!=p->iTable; j++){}
      assert( j<pTabList->nSrc );
      pTab = pTabList->a[j].pTab;
      if( iCol<0 ) iCol = pTab->iPKey;
      assert( iCol==-1 || (iCol>=0 && iCol<pTab->nCol) );
      if( iCol<0 ){
        zCol = "_ROWID_";
        zType = "INTEGER";
      }else{
        zCol = pTab->aCol[iCol].zName;
................................................................................
        int addr = sqliteVdbeAddOp(v,OP_ColumnName, i, 0);
        sqliteVdbeChangeP3(v, -1, p->span.z, p->span.n);
        sqliteVdbeCompressSpace(v, addr);
      }else if( pTabList->nSrc>1 || showFullNames ){
        char *zName = 0;
        char *zTab;
 
        zTab = pTabList->a[j].zAlias;
        if( showFullNames || zTab==0 ) zTab = pTab->zName;
        sqliteSetString(&zName, zTab, ".", zCol, 0);
        sqliteVdbeAddOp(v, OP_ColumnName, i, 0);
        sqliteVdbeChangeP3(v, -1, zName, strlen(zName));
        sqliteFree(zName);
      }else{
        sqliteVdbeAddOp(v, OP_ColumnName, i, 0);
................................................................................
      /* Convert the data in the temporary table into whatever form
      ** it is that we currently need.
      */      
      if( eDest!=priorOp || unionTab!=iParm ){
        int iCont, iBreak, iStart;
        assert( p->pEList );
        if( eDest==SRT_Callback ){
          generateColumnNames(pParse, 0, p->pEList);
          generateColumnTypes(pParse, p->pSrc, p->pEList);
        }
        iBreak = sqliteVdbeMakeLabel(v);
        iCont = sqliteVdbeMakeLabel(v);
        sqliteVdbeAddOp(v, OP_Rewind, unionTab, iBreak);
        iStart = sqliteVdbeCurrentAddr(v);
        multiSelectSortOrder(p, p->pOrderBy);
        rc = selectInnerLoop(pParse, p, p->pEList, unionTab, p->pEList->nExpr,
................................................................................
      if( rc ) return rc;

      /* Generate code to take the intersection of the two temporary
      ** tables.
      */
      assert( p->pEList );
      if( eDest==SRT_Callback ){
        generateColumnNames(pParse, 0, p->pEList);
        generateColumnTypes(pParse, p->pSrc, p->pEList);
      }
      iBreak = sqliteVdbeMakeLabel(v);
      iCont = sqliteVdbeMakeLabel(v);
      sqliteVdbeAddOp(v, OP_Rewind, tab1, iBreak);
      iStart = sqliteVdbeAddOp(v, OP_FullKey, tab1, 0);
      sqliteVdbeAddOp(v, OP_NotFound, tab2, iCont);
      multiSelectSortOrder(p, p->pOrderBy);
................................................................................
    (pParse->useCallback==0 || (pParse->db->flags & SQLITE_NullCallback)!=0)
  ){
    sqliteVdbeAddOp(v, OP_NullCallback, p->pEList->nExpr, 0);
  }
  return 0;
}

/*

























** Scan through the expression pExpr.  Replace every reference to
** a column in table number iTable with a copy of the iColumn-th
** entry in pEList.  (But leave references to the ROWID column 


** unchanged.)
**
** This routine is part of the flattening procedure.  A subquery
** whose result set is defined by pEList appears as entry in the
** FROM clause of a SELECT such that the VDBE cursor assigned to that
** FORM clause entry is iTable.  This routine make the necessary 
** changes to pExpr so that it refers directly to the source table
** of the subquery rather the result set of the subquery.
*/
static void substExprList(ExprList*,int,ExprList*);  /* Forward Decl */
static void substExpr(Expr *pExpr, int iTable, ExprList *pEList){
  if( pExpr==0 ) return;
  if( pExpr->op==TK_COLUMN && pExpr->iTable==iTable && pExpr->iColumn>=0 ){
    Expr *pNew;
    assert( pEList!=0 && pExpr->iColumn<pEList->nExpr );
    assert( pExpr->pLeft==0 && pExpr->pRight==0 && pExpr->pList==0 );
    pNew = pEList->a[pExpr->iColumn].pExpr;
    assert( pNew!=0 );
................................................................................
    assert( pExpr->pList==0 );
    pExpr->pList = sqliteExprListDup(pNew->pList);
    pExpr->iTable = pNew->iTable;
    pExpr->iColumn = pNew->iColumn;
    pExpr->iAgg = pNew->iAgg;
    sqliteTokenCopy(&pExpr->token, &pNew->token);
    sqliteTokenCopy(&pExpr->span, &pNew->span);



  }else{
    substExpr(pExpr->pLeft, iTable, pEList);
    substExpr(pExpr->pRight, iTable, pEList);
    substExprList(pExpr->pList, iTable, pEList);
  }
}
static void 
substExprList(ExprList *pList, int iTable, ExprList *pEList){
  int i;
  if( pList==0 ) return;
  for(i=0; i<pList->nExpr; i++){
    substExpr(pList->a[i].pExpr, iTable, pEList);
  }
}

/*
** This routine attempts to flatten subqueries in order to speed
** execution.  It returns 1 if it makes changes and 0 if no flattening
** occurs.
................................................................................
  int isAgg,           /* True if outer SELECT uses aggregate functions */
  int subqueryIsAgg    /* True if the subquery uses aggregate functions */
){
  Select *pSub;       /* The inner query or "subquery" */
  SrcList *pSrc;      /* The FROM clause of the outer query */
  SrcList *pSubSrc;   /* The FROM clause of the subquery */
  ExprList *pList;    /* The result set of the outer query */
  int iParent;        /* VDBE cursor number of the pSub result set temp table */
  int i;

  Expr *pWhere;

  /* Check to see if flattening is permitted.  Return 0 if not.
  */
  if( p==0 ) return 0;
  pSrc = p->pSrc;
  assert( pSrc && iFrom>=0 && iFrom<pSrc->nSrc );
................................................................................
  }
  if( (p->isDistinct || p->nLimit>=0) && subqueryIsAgg ) return 0;
  if( p->pOrderBy && pSub->pOrderBy ) return 0;

  /* If we reach this point, it means flattening is permitted for the
  ** iFrom-th entry of the FROM clause in the outer query.
  */
  iParent = pSrc->a[iFrom].iCursor;
  pSrc->a[iFrom].iCursor = pSubSrc->a[0].iCursor;
  substExprList(p->pEList, iParent, pSub->pEList);
  pList = p->pEList;
  for(i=0; i<pList->nExpr; i++){
    Expr *pExpr;
    if( pList->a[i].zName==0 && (pExpr = pList->a[i].pExpr)->span.z!=0 ){
      pList->a[i].zName = sqliteStrNDup(pExpr->span.z, pExpr->span.n);
    }
  }
  if( isAgg ){
    substExprList(p->pGroupBy, iParent, pSub->pEList);
    substExpr(p->pHaving, iParent, pSub->pEList);
  }
  if( pSub->pOrderBy ){
    assert( p->pOrderBy==0 );
    p->pOrderBy = pSub->pOrderBy;
    pSub->pOrderBy = 0;

  }else if( p->pOrderBy ){
    substExprList(p->pOrderBy, iParent, pSub->pEList);
  }
  if( pSub->pWhere ){
    pWhere = sqliteExprDup(pSub->pWhere);



  }else{
    pWhere = 0;
  }
  if( subqueryIsAgg ){
    assert( p->pHaving==0 );
    p->pHaving = p->pWhere;
    p->pWhere = pWhere;
    substExpr(p->pHaving, iParent, pSub->pEList);
    if( pSub->pHaving ){
      Expr *pHaving = sqliteExprDup(pSub->pHaving);



      if( p->pHaving ){
        p->pHaving = sqliteExpr(TK_AND, p->pHaving, pHaving, 0);
      }else{
        p->pHaving = pHaving;
      }
    }
    assert( p->pGroupBy==0 );
    p->pGroupBy = sqliteExprListDup(pSub->pGroupBy);



  }else if( p->pWhere==0 ){
    p->pWhere = pWhere;
  }else{
    substExpr(p->pWhere, iParent, pSub->pEList);
    if( pWhere ){
      p->pWhere = sqliteExpr(TK_AND, p->pWhere, pWhere, 0);
    }
  }
  p->isDistinct = p->isDistinct || pSub->isDistinct;

  if( pSub->nLimit>=0 ){
................................................................................
      p->nLimit = pSub->nLimit;
    }else if( p->nLimit+p->nOffset > pSub->nLimit+pSub->nOffset ){
      p->nLimit = pSub->nLimit + pSub->nOffset - p->nOffset;
    }
  }
  p->nOffset += pSub->nOffset;














  if( pSrc->a[iFrom].pTab && pSrc->a[iFrom].pTab->isTransient ){
    sqliteDeleteTable(0, pSrc->a[iFrom].pTab);
  }
  pSrc->a[iFrom].pTab = pSubSrc->a[0].pTab;
  pSubSrc->a[0].pTab = 0;
  assert( pSrc->a[iFrom].pSelect==pSub );
  pSrc->a[iFrom].pSelect = pSubSrc->a[0].pSelect;
................................................................................

  /* Identify column names if we will be using the callback.  This
  ** step is skipped if the output is going to a table or a memory cell.
  */
  v = sqliteGetVdbe(pParse);
  if( v==0 ) return 0;
  if( eDest==SRT_Callback ){
    generateColumnNames(pParse, p->pSrc, p->pEList);
    generateColumnTypes(pParse, p->pSrc, p->pEList);
  }

  /* Generating code to find the min or the max.  Basically all we have
  ** to do is find the first or the last entry in the chosen index.  If
  ** the min() or max() is on the INTEGER PRIMARY KEY, then find the first
  ** or last entry in the main table.
  */
  if( !pParse->schemaVerified && (pParse->db->flags & SQLITE_InTrans)==0 ){
    sqliteCodeVerifySchema(pParse);
  }
  base = p->pSrc->a[0].iCursor;
  sqliteVdbeAddOp(v, OP_Integer, pTab->iDb, 0);
  sqliteVdbeAddOp(v, OP_OpenRead, base, pTab->tnum);
  sqliteVdbeChangeP3(v, -1, pTab->zName, P3_STATIC);
  cont = sqliteVdbeMakeLabel(v);
  if( pIdx==0 ){
    sqliteVdbeAddOp(v, seekOp, base, 0);
  }else{
................................................................................
  SrcList *pTabList;     /* List of tables to select from */
  Expr *pWhere;          /* The WHERE clause.  May be NULL */
  ExprList *pOrderBy;    /* The ORDER BY clause.  May be NULL */
  ExprList *pGroupBy;    /* The GROUP BY clause.  May be NULL */
  Expr *pHaving;         /* The HAVING clause.  May be NULL */
  int isDistinct;        /* True if the DISTINCT keyword is present */
  int distinct;          /* Table to use for the distinct set */

  int rc = 1;            /* Value to return from this function */

  if( sqlite_malloc_failed || pParse->nErr || p==0 ) return 1;
  if( sqliteAuthCheck(pParse, SQLITE_SELECT, 0, 0, 0) ) return 1;

  /* If there is are a sequence of queries, do the earlier ones first.
  */
................................................................................
  pTabList = p->pSrc;
  pWhere = p->pWhere;
  pOrderBy = p->pOrderBy;
  pGroupBy = p->pGroupBy;
  pHaving = p->pHaving;
  isDistinct = p->isDistinct;

  /* Allocate VDBE cursors for each table in the FROM clause


  */


  sqliteSrcListAssignCursors(pParse, pTabList);

  /* 
  ** Do not even attempt to generate any code if we have already seen
  ** errors before this routine starts.
  */
  if( pParse->nErr>0 ) goto select_end;

................................................................................

  /* At this point, we should have allocated all the cursors that we
  ** need to handle subquerys and temporary tables.  
  **
  ** Resolve the column names and do a semantics check on all the expressions.
  */
  for(i=0; i<pEList->nExpr; i++){
    if( sqliteExprResolveIds(pParse, pTabList, 0, pEList->a[i].pExpr) ){
      goto select_end;
    }
    if( sqliteExprCheck(pParse, pEList->a[i].pExpr, 1, &isAgg) ){
      goto select_end;
    }
  }
  if( pWhere ){
    if( sqliteExprResolveIds(pParse, pTabList, pEList, pWhere) ){
      goto select_end;
    }
    if( sqliteExprCheck(pParse, pWhere, 0, 0) ){
      goto select_end;
    }
    sqliteOracle8JoinFixup(pTabList, pWhere);
  }
  if( pHaving ){
    if( pGroupBy==0 ){
      sqliteErrorMsg(pParse, "a GROUP BY clause is required before HAVING");
      goto select_end;
    }
    if( sqliteExprResolveIds(pParse, pTabList, pEList, pHaving) ){
      goto select_end;
    }
    if( sqliteExprCheck(pParse, pHaving, 1, &isAgg) ){
      goto select_end;
    }
  }
  if( pOrderBy ){
................................................................................
    for(i=0; i<pOrderBy->nExpr; i++){
      int iCol;
      Expr *pE = pOrderBy->a[i].pExpr;
      if( sqliteExprIsInteger(pE, &iCol) && iCol>0 && iCol<=pEList->nExpr ){
        sqliteExprDelete(pE);
        pE = pOrderBy->a[i].pExpr = sqliteExprDup(pEList->a[iCol-1].pExpr);
      }
      if( sqliteExprResolveIds(pParse, pTabList, pEList, pE) ){
        goto select_end;
      }
      if( sqliteExprCheck(pParse, pE, isAgg, 0) ){
        goto select_end;
      }
      if( sqliteExprIsConstant(pE) ){
        if( sqliteExprIsInteger(pE, &iCol)==0 ){
................................................................................
    for(i=0; i<pGroupBy->nExpr; i++){
      int iCol;
      Expr *pE = pGroupBy->a[i].pExpr;
      if( sqliteExprIsInteger(pE, &iCol) && iCol>0 && iCol<=pEList->nExpr ){
        sqliteExprDelete(pE);
        pE = pGroupBy->a[i].pExpr = sqliteExprDup(pEList->a[iCol-1].pExpr);
      }
      if( sqliteExprResolveIds(pParse, pTabList, pEList, pE) ){
        goto select_end;
      }
      if( sqliteExprCheck(pParse, pE, isAgg, 0) ){
        goto select_end;
      }
      if( sqliteExprIsConstant(pE) ){
        if( sqliteExprIsInteger(pE, &iCol)==0 ){
................................................................................
  v = sqliteGetVdbe(pParse);
  if( v==0 ) goto select_end;

  /* Identify column names if we will be using them in a callback.  This
  ** step is skipped if the output is going to some other destination.
  */
  if( eDest==SRT_Callback ){
    generateColumnNames(pParse, pTabList, pEList);
  }

  /* Set the limiter
  */
  if( p->nLimit<=0 ){
    p->nLimit = -1;
    p->nOffset = 0;
................................................................................
  for(i=0; i<pTabList->nSrc; i++){
    const char *zSavedAuthContext;
    if( pTabList->a[i].pSelect==0 ) continue;
    if( pTabList->a[i].zName!=0 ){
      zSavedAuthContext = pParse->zAuthContext;
      pParse->zAuthContext = pTabList->a[i].zName;
    }
    sqliteSelect(pParse, pTabList->a[i].pSelect, SRT_TempTable, 
                 pTabList->a[i].iCursor, p, i, &isAgg);
    if( pTabList->a[i].zName!=0 ){
      pParse->zAuthContext = zSavedAuthContext;
    }
    pTabList = p->pSrc;
    pWhere = p->pWhere;
    if( eDest==SRT_Callback ){
      pOrderBy = p->pOrderBy;
................................................................................
  }

  /* Identify column types if we will be using a callback.  This
  ** step is skipped if the output is going to a destination other
  ** than a callback.
  */
  if( eDest==SRT_Callback ){
    generateColumnTypes(pParse, pTabList, pEList);
  }

  /* If the output is destined for a temporary table, open that table.
  */
  if( eDest==SRT_TempTable ){
    sqliteVdbeAddOp(v, OP_OpenTemp, iParm, 0);
  }
................................................................................
    sqliteVdbeAddOp(v, OP_OpenTemp, distinct, 1);
  }else{
    distinct = -1;
  }

  /* Begin the database scan
  */
  pWInfo = sqliteWhereBegin(pParse, pTabList, pWhere, 0, 
                            pGroupBy ? 0 : &pOrderBy);
  if( pWInfo==0 ) goto select_end;

  /* Use the standard inner loop if we are not dealing with
  ** aggregates
  */
  if( !isAgg ){

Changes to src/sqliteInt.h.

7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
...
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
...
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
....
1035
1036
1037
1038
1039
1040
1041

1042
1043
1044
1045
1046
1047
1048
....
1050
1051
1052
1053
1054
1055
1056
1057
1058
1059
1060
1061
1062
1063
1064
....
1068
1069
1070
1071
1072
1073
1074
1075
1076
1077
1078
1079
1080
1081
1082
....
1116
1117
1118
1119
1120
1121
1122
1123
1124
1125
1126
1127
1128
1129
1130
1131
1132
1133
1134
1135
1136
**    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.182 2003/04/29 16:20:46 drh Exp $
*/
#include "config.h"
#include "sqlite.h"
#include "hash.h"
#include "vdbe.h"
#include "parse.h"
#include "btree.h"
................................................................................
** into the second half to give some continuity.
*/
struct WhereInfo {
  Parse *pParse;
  SrcList *pTabList;   /* List of tables in the join */
  int iContinue;       /* Jump here to continue with next record */
  int iBreak;          /* Jump here to break out of the loop */
  int base;            /* Index of first Open opcode */
  int nLevel;          /* Number of nested loop */
  int savedNTab;       /* Value of pParse->nTab before WhereBegin() */
  int peakNTab;        /* Value of pParse->nTab after WhereBegin() */
  WhereLevel a[1];     /* Information about each nest loop in the WHERE */
};

/*
................................................................................
  ExprList *pGroupBy;    /* The GROUP BY clause */
  Expr *pHaving;         /* The HAVING clause */
  ExprList *pOrderBy;    /* The ORDER BY clause */
  int op;                /* One of: TK_UNION TK_ALL TK_INTERSECT TK_EXCEPT */
  Select *pPrior;        /* Prior select in a compound select statement */
  int nLimit, nOffset;   /* LIMIT and OFFSET values.  -1 means not used */
  char *zSelect;         /* Complete text of the SELECT command */
  int base;              /* Index of VDBE cursor for left-most FROM table */
};

/*
** The results of a select can be distributed in several ways.
*/
#define SRT_Callback     1  /* Invoke a callback with each row of result */
#define SRT_Mem          2  /* Store result in a memory cell */
................................................................................
void sqliteDropTable(Parse*, Token*, int);
void sqliteDeleteTable(sqlite*, Table*);
void sqliteInsert(Parse*, SrcList*, ExprList*, Select*, IdList*, int);
IdList *sqliteIdListAppend(IdList*, Token*);
int sqliteIdListIndex(IdList*,const char*);
SrcList *sqliteSrcListAppend(SrcList*, Token*, Token*);
void sqliteSrcListAddAlias(SrcList*, Token*);

void sqliteIdListDelete(IdList*);
void sqliteSrcListDelete(SrcList*);
void sqliteCreateIndex(Parse*,Token*,SrcList*,IdList*,int,int,Token*,Token*);
void sqliteDropIndex(Parse*, SrcList*);
void sqliteAddKeyType(Vdbe*, ExprList*);
void sqliteAddIdxKeyType(Vdbe*, Index*);
int sqliteSelect(Parse*, Select*, int, int, Select*, int, int*);
................................................................................
                        int,int,int);
void sqliteSelectDelete(Select*);
void sqliteSelectUnbind(Select*);
Table *sqliteSrcListLookup(Parse*, SrcList*);
int sqliteIsReadOnly(Parse*, Table*, int);
void sqliteDeleteFrom(Parse*, SrcList*, Expr*);
void sqliteUpdate(Parse*, SrcList*, ExprList*, Expr*, int);
WhereInfo *sqliteWhereBegin(Parse*, int, SrcList*, Expr*, int, ExprList**);
void sqliteWhereEnd(WhereInfo*);
void sqliteExprCode(Parse*, Expr*);
void sqliteExprIfTrue(Parse*, Expr*, int, int);
void sqliteExprIfFalse(Parse*, Expr*, int, int);
Table *sqliteFindTable(sqlite*,const char*, const char*);
Table *sqliteLocateTable(Parse*,const char*, const char*);
Index *sqliteFindIndex(sqlite*,const char*, const char*);
................................................................................
int sqliteGlobCompare(const unsigned char*,const unsigned char*);
int sqliteLikeCompare(const unsigned char*,const unsigned char*);
char *sqliteTableNameFromToken(Token*);
int sqliteExprCheck(Parse*, Expr*, int, int*);
int sqliteExprType(Expr*);
int sqliteExprCompare(Expr*, Expr*);
int sqliteFuncId(Token*);
int sqliteExprResolveIds(Parse*, int, SrcList*, ExprList*, Expr*);
int sqliteExprAnalyzeAggregates(Parse*, Expr*);
Vdbe *sqliteGetVdbe(Parse*);
int sqliteRandomByte(void);
int sqliteRandomInteger(void);
void sqliteRollbackAll(sqlite*);
void sqliteCodeVerifySchema(Parse*);
void sqliteBeginTransaction(Parse*, int);
................................................................................
TriggerStep *sqliteTriggerUpdateStep(Token*, ExprList*, Expr*, int);
TriggerStep *sqliteTriggerDeleteStep(Token*, Expr*);
void sqliteDeleteTrigger(Trigger*);
int sqliteJoinType(Parse*, Token*, Token*, Token*);
void sqliteCreateForeignKey(Parse*, IdList*, Token*, IdList*, int);
void sqliteDeferForeignKey(Parse*, int);
#ifndef SQLITE_OMIT_AUTHORIZATION
  void sqliteAuthRead(Parse*,Expr*,SrcList*,int);
  int sqliteAuthCheck(Parse*,int, const char*, const char*, const char*);
  void sqliteAuthContextPush(Parse*, AuthContext*, const char*);
  void sqliteAuthContextPop(AuthContext*);
#else
# define sqliteAuthRead(a,b,c,d)
# define sqliteAuthCheck(a,b,c,d)    SQLITE_OK
# define sqliteAuthContextPush(a,b,c)
# define sqliteAuthContextPop(a)
#endif
void sqliteAttach(Parse*, Token*, Token*);
void sqliteDetach(Parse*, Token*);
int sqliteBtreeFactory(const sqlite *db, const char *zFilename,
                       int mode, int nPg, Btree **ppBtree);







|







 







<







 







<







 







>







 







|







 







|







 







|




|








7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
...
713
714
715
716
717
718
719

720
721
722
723
724
725
726
...
749
750
751
752
753
754
755

756
757
758
759
760
761
762
....
1033
1034
1035
1036
1037
1038
1039
1040
1041
1042
1043
1044
1045
1046
1047
....
1049
1050
1051
1052
1053
1054
1055
1056
1057
1058
1059
1060
1061
1062
1063
....
1067
1068
1069
1070
1071
1072
1073
1074
1075
1076
1077
1078
1079
1080
1081
....
1115
1116
1117
1118
1119
1120
1121
1122
1123
1124
1125
1126
1127
1128
1129
1130
1131
1132
1133
1134
1135
**    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.183 2003/05/02 14:32:14 drh Exp $
*/
#include "config.h"
#include "sqlite.h"
#include "hash.h"
#include "vdbe.h"
#include "parse.h"
#include "btree.h"
................................................................................
** into the second half to give some continuity.
*/
struct WhereInfo {
  Parse *pParse;
  SrcList *pTabList;   /* List of tables in the join */
  int iContinue;       /* Jump here to continue with next record */
  int iBreak;          /* Jump here to break out of the loop */

  int nLevel;          /* Number of nested loop */
  int savedNTab;       /* Value of pParse->nTab before WhereBegin() */
  int peakNTab;        /* Value of pParse->nTab after WhereBegin() */
  WhereLevel a[1];     /* Information about each nest loop in the WHERE */
};

/*
................................................................................
  ExprList *pGroupBy;    /* The GROUP BY clause */
  Expr *pHaving;         /* The HAVING clause */
  ExprList *pOrderBy;    /* The ORDER BY clause */
  int op;                /* One of: TK_UNION TK_ALL TK_INTERSECT TK_EXCEPT */
  Select *pPrior;        /* Prior select in a compound select statement */
  int nLimit, nOffset;   /* LIMIT and OFFSET values.  -1 means not used */
  char *zSelect;         /* Complete text of the SELECT command */

};

/*
** The results of a select can be distributed in several ways.
*/
#define SRT_Callback     1  /* Invoke a callback with each row of result */
#define SRT_Mem          2  /* Store result in a memory cell */
................................................................................
void sqliteDropTable(Parse*, Token*, int);
void sqliteDeleteTable(sqlite*, Table*);
void sqliteInsert(Parse*, SrcList*, ExprList*, Select*, IdList*, int);
IdList *sqliteIdListAppend(IdList*, Token*);
int sqliteIdListIndex(IdList*,const char*);
SrcList *sqliteSrcListAppend(SrcList*, Token*, Token*);
void sqliteSrcListAddAlias(SrcList*, Token*);
void sqliteSrcListAssignCursors(Parse*, SrcList*);
void sqliteIdListDelete(IdList*);
void sqliteSrcListDelete(SrcList*);
void sqliteCreateIndex(Parse*,Token*,SrcList*,IdList*,int,int,Token*,Token*);
void sqliteDropIndex(Parse*, SrcList*);
void sqliteAddKeyType(Vdbe*, ExprList*);
void sqliteAddIdxKeyType(Vdbe*, Index*);
int sqliteSelect(Parse*, Select*, int, int, Select*, int, int*);
................................................................................
                        int,int,int);
void sqliteSelectDelete(Select*);
void sqliteSelectUnbind(Select*);
Table *sqliteSrcListLookup(Parse*, SrcList*);
int sqliteIsReadOnly(Parse*, Table*, int);
void sqliteDeleteFrom(Parse*, SrcList*, Expr*);
void sqliteUpdate(Parse*, SrcList*, ExprList*, Expr*, int);
WhereInfo *sqliteWhereBegin(Parse*, SrcList*, Expr*, int, ExprList**);
void sqliteWhereEnd(WhereInfo*);
void sqliteExprCode(Parse*, Expr*);
void sqliteExprIfTrue(Parse*, Expr*, int, int);
void sqliteExprIfFalse(Parse*, Expr*, int, int);
Table *sqliteFindTable(sqlite*,const char*, const char*);
Table *sqliteLocateTable(Parse*,const char*, const char*);
Index *sqliteFindIndex(sqlite*,const char*, const char*);
................................................................................
int sqliteGlobCompare(const unsigned char*,const unsigned char*);
int sqliteLikeCompare(const unsigned char*,const unsigned char*);
char *sqliteTableNameFromToken(Token*);
int sqliteExprCheck(Parse*, Expr*, int, int*);
int sqliteExprType(Expr*);
int sqliteExprCompare(Expr*, Expr*);
int sqliteFuncId(Token*);
int sqliteExprResolveIds(Parse*, SrcList*, ExprList*, Expr*);
int sqliteExprAnalyzeAggregates(Parse*, Expr*);
Vdbe *sqliteGetVdbe(Parse*);
int sqliteRandomByte(void);
int sqliteRandomInteger(void);
void sqliteRollbackAll(sqlite*);
void sqliteCodeVerifySchema(Parse*);
void sqliteBeginTransaction(Parse*, int);
................................................................................
TriggerStep *sqliteTriggerUpdateStep(Token*, ExprList*, Expr*, int);
TriggerStep *sqliteTriggerDeleteStep(Token*, Expr*);
void sqliteDeleteTrigger(Trigger*);
int sqliteJoinType(Parse*, Token*, Token*, Token*);
void sqliteCreateForeignKey(Parse*, IdList*, Token*, IdList*, int);
void sqliteDeferForeignKey(Parse*, int);
#ifndef SQLITE_OMIT_AUTHORIZATION
  void sqliteAuthRead(Parse*,Expr*,SrcList*);
  int sqliteAuthCheck(Parse*,int, const char*, const char*, const char*);
  void sqliteAuthContextPush(Parse*, AuthContext*, const char*);
  void sqliteAuthContextPop(AuthContext*);
#else
# define sqliteAuthRead(a,b,c)
# define sqliteAuthCheck(a,b,c,d)    SQLITE_OK
# define sqliteAuthContextPush(a,b,c)
# define sqliteAuthContextPop(a)
#endif
void sqliteAttach(Parse*, Token*, Token*);
void sqliteDetach(Parse*, Token*);
int sqliteBtreeFactory(const sqlite *db, const char *zFilename,
                       int mode, int nPg, Btree **ppBtree);

Changes to src/trigger.c.

674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
      pTriggerStack->ignoreJump = ignoreJump;
      pParse->trigStack = pTriggerStack;
      sqliteAuthContextPush(pParse, &sContext, pTrigger->name);

      /* code the WHEN clause */
      endTrigger = sqliteVdbeMakeLabel(pParse->pVdbe);
      whenExpr = sqliteExprDup(pTrigger->pWhen);
      if( sqliteExprResolveIds(pParse, 0, &dummyTablist, 0, whenExpr) ){
        pParse->trigStack = pParse->trigStack->pNext;
        sqliteFree(pTriggerStack);
        sqliteExprDelete(whenExpr);
        return 1;
      }
      sqliteExprIfFalse(pParse, whenExpr, endTrigger, 1);
      sqliteExprDelete(whenExpr);







|







674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
      pTriggerStack->ignoreJump = ignoreJump;
      pParse->trigStack = pTriggerStack;
      sqliteAuthContextPush(pParse, &sContext, pTrigger->name);

      /* code the WHEN clause */
      endTrigger = sqliteVdbeMakeLabel(pParse->pVdbe);
      whenExpr = sqliteExprDup(pTrigger->pWhen);
      if( sqliteExprResolveIds(pParse, &dummyTablist, 0, whenExpr) ){
        pParse->trigStack = pParse->trigStack->pNext;
        sqliteFree(pTriggerStack);
        sqliteExprDelete(whenExpr);
        return 1;
      }
      sqliteExprIfFalse(pParse, whenExpr, endTrigger, 1);
      sqliteExprDelete(whenExpr);

Changes to src/update.c.

8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
..
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
..
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
...
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
...
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
...
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
290
291
292
293
294
...
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
...
350
351
352
353
354
355
356
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
...
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
...
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
**    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 UPDATE statements.
**
** $Id: update.c,v 1.64 2003/04/25 17:52:11 drh Exp $
*/
#include "sqliteInt.h"

/*
** Process an UPDATE statement.
**
**   UPDATE OR IGNORE table_wxyz SET a=b, c=d WHERE e<5 AND f NOT NULL;
................................................................................
  Table *pTab;           /* The table to be updated */
  int addr;              /* VDBE instruction address of the start of the loop */
  WhereInfo *pWInfo;     /* Information about the WHERE clause */
  Vdbe *v;               /* The virtual database engine */
  Index *pIdx;           /* For looping over indices */
  int nIdx;              /* Number of indices that need updating */
  int nIdxTotal;         /* Total number of indices */
  int base;              /* Index of first available table cursor */
  sqlite *db;            /* The database structure */
  Index **apIdx = 0;     /* An array of indices that need updating too */
  char *aIdxUsed = 0;    /* aIdxUsed[i]==1 if the i-th index is used */
  int *aXRef = 0;        /* aXRef[i] is the index in pChanges->a[] of the
                         ** an expression for the i-th column of the table.
                         ** aXRef[i]==-1 if the i-th column is not changed. */
  int chngRecno;         /* True if the record number is being changed */
................................................................................
  }

  /* Allocate a cursors for the main database table and for all indices.
  ** The index cursors might not be used, but if they are used they
  ** need to occur right after the database cursor.  So go ahead and
  ** allocate enough space, just in case.
  */
  base = pParse->nTab++;
  for(pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext){
    pParse->nTab++;
  }

  /* Resolve the column names in all the expressions of the
  ** of the UPDATE statement.  Also find the column index
  ** for each column to be updated in the pChanges array.  For each
  ** column to be updated, make sure we have authorization to change
  ** that column.
  */
  chngRecno = 0;
  for(i=0; i<pChanges->nExpr; i++){
    if( sqliteExprResolveIds(pParse, base, pTabList, 0, pChanges->a[i].pExpr) ){
      goto update_cleanup;
    }
    if( sqliteExprCheck(pParse, pChanges->a[i].pExpr, 0, 0) ){
      goto update_cleanup;
    }
    for(j=0; j<pTab->nCol; j++){
      if( sqliteStrICmp(pTab->aCol[j].zName, pChanges->a[i].zName)==0 ){
................................................................................
    }
  }

  /* Resolve the column names in all the expressions in the
  ** WHERE clause.
  */
  if( pWhere ){
    if( sqliteExprResolveIds(pParse, base, pTabList, 0, pWhere) ){
      goto update_cleanup;
    }
    if( sqliteExprCheck(pParse, pWhere, 0, 0) ){
      goto update_cleanup;
    }
  }

................................................................................

  /* If we are trying to update a view, construct that view into
  ** a temporary table.
  */
  if( isView ){
    Select *pView;
    pView = sqliteSelectDup(pTab->pSelect);
    sqliteSelect(pParse, pView, SRT_TempTable, base, 0, 0, 0);
    sqliteSelectDelete(pView);
  }

  /* Begin the database scan
  */
  pWInfo = sqliteWhereBegin(pParse, base, pTabList, pWhere, 1, 0);
  if( pWInfo==0 ) goto update_cleanup;

  /* Remember the index of every item to be updated.
  */
  sqliteVdbeAddOp(v, OP_ListWrite, 0, 0);

  /* End the database scan loop.
................................................................................

    /* Open a cursor and make it point to the record that is
    ** being updated.
    */
    sqliteVdbeAddOp(v, OP_Dup, 0, 0);
    if( !isView ){
      sqliteVdbeAddOp(v, OP_Integer, pTab->iDb, 0);
      sqliteVdbeAddOp(v, OP_OpenRead, base, pTab->tnum);
    }
    sqliteVdbeAddOp(v, OP_MoveTo, base, 0);

    /* Generate the OLD table
    */
    sqliteVdbeAddOp(v, OP_Recno, base, 0);
    sqliteVdbeAddOp(v, OP_RowData, base, 0);
    sqliteVdbeAddOp(v, OP_PutIntKey, oldIdx, 0);

    /* Generate the NEW table
    */
    if( chngRecno ){
      sqliteExprCode(pParse, pRecnoExpr);
    }else{
      sqliteVdbeAddOp(v, OP_Recno, base, 0);
    }
    for(i=0; i<pTab->nCol; i++){
      if( i==pTab->iPKey ){
        sqliteVdbeAddOp(v, OP_String, 0, 0);
        continue;
      }
      j = aXRef[i];
      if( j<0 ){
        sqliteVdbeAddOp(v, OP_Column, base, i);
      }else{
        sqliteExprCode(pParse, pChanges->a[j].pExpr);
      }
    }
    sqliteVdbeAddOp(v, OP_MakeRecord, pTab->nCol, 0);
    sqliteVdbeAddOp(v, OP_PutIntKey, newIdx, 0);
    if( !isView ){
      sqliteVdbeAddOp(v, OP_Close, base, 0);
    }

    /* Fire the BEFORE and INSTEAD OF triggers
    */
    if( sqliteCodeRowTrigger(pParse, TK_UPDATE, pChanges, TK_BEFORE, pTab, 
          newIdx, oldIdx, onError, addr) ){
      goto update_cleanup;
................................................................................
    /* 
    ** Open every index that needs updating.  Note that if any
    ** index could potentially invoke a REPLACE conflict resolution 
    ** action, then we need to open all indices because we might need
    ** to be deleting some records.
    */
    sqliteVdbeAddOp(v, OP_Integer, pTab->iDb, 0);
    sqliteVdbeAddOp(v, OP_OpenWrite, base, pTab->tnum);
    if( onError==OE_Replace ){
      openAll = 1;
    }else{
      openAll = 0;
      for(pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext){
        if( pIdx->onError==OE_Replace ){
          openAll = 1;
................................................................................
          break;
        }
      }
    }
    for(i=0, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, i++){
      if( openAll || aIdxUsed[i] ){
        sqliteVdbeAddOp(v, OP_Integer, pIdx->iDb, 0);
        sqliteVdbeAddOp(v, OP_OpenWrite, base+i+1, pIdx->tnum);
        assert( pParse->nTab>base+i+1 );
      }
    }

    /* Loop over every record that needs updating.  We have to load
    ** the old data for each record to be updated because some columns
    ** might not change and we will need to copy the old value.
    ** Also, the old data is needed to delete the old index entires.
................................................................................
    ** So make the cursor point at the old record.
    */
    if( !row_triggers_exist ){
      sqliteVdbeAddOp(v, OP_ListRewind, 0, 0);
      addr = sqliteVdbeAddOp(v, OP_ListRead, 0, 0);
      sqliteVdbeAddOp(v, OP_Dup, 0, 0);
    }
    sqliteVdbeAddOp(v, OP_NotExists, base, addr);

    /* If the record number will change, push the record number as it
    ** will be after the update. (The old record number is currently
    ** on top of the stack.)
    */
    if( chngRecno ){
      sqliteExprCode(pParse, pRecnoExpr);
................................................................................
    for(i=0; i<pTab->nCol; i++){
      if( i==pTab->iPKey ){
        sqliteVdbeAddOp(v, OP_String, 0, 0);
        continue;
      }
      j = aXRef[i];
      if( j<0 ){
        sqliteVdbeAddOp(v, OP_Column, base, i);
      }else{
        sqliteExprCode(pParse, pChanges->a[j].pExpr);
      }
    }

    /* Do constraint checks
    */
    sqliteGenerateConstraintChecks(pParse, pTab, base, aIdxUsed, chngRecno, 1,
                                   onError, addr);

    /* Delete the old indices for the current record.
    */
    sqliteGenerateRowIndexDelete(db, v, pTab, base, aIdxUsed);

    /* If changing the record number, delete the old record.
    */
    if( chngRecno ){
      sqliteVdbeAddOp(v, OP_Delete, base, 0);
    }

    /* Create the new index entries and the new record.
    */
    sqliteCompleteInsertion(pParse, pTab, base, aIdxUsed, chngRecno, 1, -1);
  }

  /* Increment the row counter 
  */
  if( db->flags & SQLITE_CountRows && !pParse->trigStack){
    sqliteVdbeAddOp(v, OP_AddImm, 1, 0);
  }
................................................................................
  /* If there are triggers, close all the cursors after each iteration
  ** through the loop.  The fire the after triggers.
  */
  if( row_triggers_exist ){
    if( !isView ){
      for(i=0, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, i++){
        if( openAll || aIdxUsed[i] )
          sqliteVdbeAddOp(v, OP_Close, base+i+1, 0);
      }
      sqliteVdbeAddOp(v, OP_Close, base, 0);
      pParse->nTab = base;
    }
    if( sqliteCodeRowTrigger(pParse, TK_UPDATE, pChanges, TK_AFTER, pTab, 
          newIdx, oldIdx, onError, addr) ){
      goto update_cleanup;
    }
  }

................................................................................
  sqliteVdbeChangeP2(v, addr, sqliteVdbeCurrentAddr(v));
  sqliteVdbeAddOp(v, OP_ListReset, 0, 0);

  /* Close all tables if there were no FOR EACH ROW triggers */
  if( !row_triggers_exist ){
    for(i=0, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, i++){
      if( openAll || aIdxUsed[i] ){
        sqliteVdbeAddOp(v, OP_Close, base+i+1, 0);
      }
    }
    sqliteVdbeAddOp(v, OP_Close, base, 0);
    pParse->nTab = base;
  }else{
    sqliteVdbeAddOp(v, OP_Close, newIdx, 0);
    sqliteVdbeAddOp(v, OP_Close, oldIdx, 0);
  }

  sqliteEndWriteOperation(pParse);








|







 







|







 







|












|







 







|







 







|





|







 







|

|



|
|







|








|







|







 







|







 







|
|







 







|







 







|







|




|




|




|







 







|

|
|







 







|


|
|







8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
..
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
..
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
...
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
...
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
...
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
290
291
292
293
294
...
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
...
350
351
352
353
354
355
356
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
...
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
...
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
**    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 UPDATE statements.
**
** $Id: update.c,v 1.65 2003/05/02 14:32:14 drh Exp $
*/
#include "sqliteInt.h"

/*
** Process an UPDATE statement.
**
**   UPDATE OR IGNORE table_wxyz SET a=b, c=d WHERE e<5 AND f NOT NULL;
................................................................................
  Table *pTab;           /* The table to be updated */
  int addr;              /* VDBE instruction address of the start of the loop */
  WhereInfo *pWInfo;     /* Information about the WHERE clause */
  Vdbe *v;               /* The virtual database engine */
  Index *pIdx;           /* For looping over indices */
  int nIdx;              /* Number of indices that need updating */
  int nIdxTotal;         /* Total number of indices */
  int iCur;              /* VDBE Cursor number of pTab */
  sqlite *db;            /* The database structure */
  Index **apIdx = 0;     /* An array of indices that need updating too */
  char *aIdxUsed = 0;    /* aIdxUsed[i]==1 if the i-th index is used */
  int *aXRef = 0;        /* aXRef[i] is the index in pChanges->a[] of the
                         ** an expression for the i-th column of the table.
                         ** aXRef[i]==-1 if the i-th column is not changed. */
  int chngRecno;         /* True if the record number is being changed */
................................................................................
  }

  /* Allocate a cursors for the main database table and for all indices.
  ** The index cursors might not be used, but if they are used they
  ** need to occur right after the database cursor.  So go ahead and
  ** allocate enough space, just in case.
  */
  pTabList->a[0].iCursor = iCur = pParse->nTab++;
  for(pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext){
    pParse->nTab++;
  }

  /* Resolve the column names in all the expressions of the
  ** of the UPDATE statement.  Also find the column index
  ** for each column to be updated in the pChanges array.  For each
  ** column to be updated, make sure we have authorization to change
  ** that column.
  */
  chngRecno = 0;
  for(i=0; i<pChanges->nExpr; i++){
    if( sqliteExprResolveIds(pParse, pTabList, 0, pChanges->a[i].pExpr) ){
      goto update_cleanup;
    }
    if( sqliteExprCheck(pParse, pChanges->a[i].pExpr, 0, 0) ){
      goto update_cleanup;
    }
    for(j=0; j<pTab->nCol; j++){
      if( sqliteStrICmp(pTab->aCol[j].zName, pChanges->a[i].zName)==0 ){
................................................................................
    }
  }

  /* Resolve the column names in all the expressions in the
  ** WHERE clause.
  */
  if( pWhere ){
    if( sqliteExprResolveIds(pParse, pTabList, 0, pWhere) ){
      goto update_cleanup;
    }
    if( sqliteExprCheck(pParse, pWhere, 0, 0) ){
      goto update_cleanup;
    }
  }

................................................................................

  /* If we are trying to update a view, construct that view into
  ** a temporary table.
  */
  if( isView ){
    Select *pView;
    pView = sqliteSelectDup(pTab->pSelect);
    sqliteSelect(pParse, pView, SRT_TempTable, iCur, 0, 0, 0);
    sqliteSelectDelete(pView);
  }

  /* Begin the database scan
  */
  pWInfo = sqliteWhereBegin(pParse, pTabList, pWhere, 1, 0);
  if( pWInfo==0 ) goto update_cleanup;

  /* Remember the index of every item to be updated.
  */
  sqliteVdbeAddOp(v, OP_ListWrite, 0, 0);

  /* End the database scan loop.
................................................................................

    /* Open a cursor and make it point to the record that is
    ** being updated.
    */
    sqliteVdbeAddOp(v, OP_Dup, 0, 0);
    if( !isView ){
      sqliteVdbeAddOp(v, OP_Integer, pTab->iDb, 0);
      sqliteVdbeAddOp(v, OP_OpenRead, iCur, pTab->tnum);
    }
    sqliteVdbeAddOp(v, OP_MoveTo, iCur, 0);

    /* Generate the OLD table
    */
    sqliteVdbeAddOp(v, OP_Recno, iCur, 0);
    sqliteVdbeAddOp(v, OP_RowData, iCur, 0);
    sqliteVdbeAddOp(v, OP_PutIntKey, oldIdx, 0);

    /* Generate the NEW table
    */
    if( chngRecno ){
      sqliteExprCode(pParse, pRecnoExpr);
    }else{
      sqliteVdbeAddOp(v, OP_Recno, iCur, 0);
    }
    for(i=0; i<pTab->nCol; i++){
      if( i==pTab->iPKey ){
        sqliteVdbeAddOp(v, OP_String, 0, 0);
        continue;
      }
      j = aXRef[i];
      if( j<0 ){
        sqliteVdbeAddOp(v, OP_Column, iCur, i);
      }else{
        sqliteExprCode(pParse, pChanges->a[j].pExpr);
      }
    }
    sqliteVdbeAddOp(v, OP_MakeRecord, pTab->nCol, 0);
    sqliteVdbeAddOp(v, OP_PutIntKey, newIdx, 0);
    if( !isView ){
      sqliteVdbeAddOp(v, OP_Close, iCur, 0);
    }

    /* Fire the BEFORE and INSTEAD OF triggers
    */
    if( sqliteCodeRowTrigger(pParse, TK_UPDATE, pChanges, TK_BEFORE, pTab, 
          newIdx, oldIdx, onError, addr) ){
      goto update_cleanup;
................................................................................
    /* 
    ** Open every index that needs updating.  Note that if any
    ** index could potentially invoke a REPLACE conflict resolution 
    ** action, then we need to open all indices because we might need
    ** to be deleting some records.
    */
    sqliteVdbeAddOp(v, OP_Integer, pTab->iDb, 0);
    sqliteVdbeAddOp(v, OP_OpenWrite, iCur, pTab->tnum);
    if( onError==OE_Replace ){
      openAll = 1;
    }else{
      openAll = 0;
      for(pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext){
        if( pIdx->onError==OE_Replace ){
          openAll = 1;
................................................................................
          break;
        }
      }
    }
    for(i=0, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, i++){
      if( openAll || aIdxUsed[i] ){
        sqliteVdbeAddOp(v, OP_Integer, pIdx->iDb, 0);
        sqliteVdbeAddOp(v, OP_OpenWrite, iCur+i+1, pIdx->tnum);
        assert( pParse->nTab>iCur+i+1 );
      }
    }

    /* Loop over every record that needs updating.  We have to load
    ** the old data for each record to be updated because some columns
    ** might not change and we will need to copy the old value.
    ** Also, the old data is needed to delete the old index entires.
................................................................................
    ** So make the cursor point at the old record.
    */
    if( !row_triggers_exist ){
      sqliteVdbeAddOp(v, OP_ListRewind, 0, 0);
      addr = sqliteVdbeAddOp(v, OP_ListRead, 0, 0);
      sqliteVdbeAddOp(v, OP_Dup, 0, 0);
    }
    sqliteVdbeAddOp(v, OP_NotExists, iCur, addr);

    /* If the record number will change, push the record number as it
    ** will be after the update. (The old record number is currently
    ** on top of the stack.)
    */
    if( chngRecno ){
      sqliteExprCode(pParse, pRecnoExpr);
................................................................................
    for(i=0; i<pTab->nCol; i++){
      if( i==pTab->iPKey ){
        sqliteVdbeAddOp(v, OP_String, 0, 0);
        continue;
      }
      j = aXRef[i];
      if( j<0 ){
        sqliteVdbeAddOp(v, OP_Column, iCur, i);
      }else{
        sqliteExprCode(pParse, pChanges->a[j].pExpr);
      }
    }

    /* Do constraint checks
    */
    sqliteGenerateConstraintChecks(pParse, pTab, iCur, aIdxUsed, chngRecno, 1,
                                   onError, addr);

    /* Delete the old indices for the current record.
    */
    sqliteGenerateRowIndexDelete(db, v, pTab, iCur, aIdxUsed);

    /* If changing the record number, delete the old record.
    */
    if( chngRecno ){
      sqliteVdbeAddOp(v, OP_Delete, iCur, 0);
    }

    /* Create the new index entries and the new record.
    */
    sqliteCompleteInsertion(pParse, pTab, iCur, aIdxUsed, chngRecno, 1, -1);
  }

  /* Increment the row counter 
  */
  if( db->flags & SQLITE_CountRows && !pParse->trigStack){
    sqliteVdbeAddOp(v, OP_AddImm, 1, 0);
  }
................................................................................
  /* If there are triggers, close all the cursors after each iteration
  ** through the loop.  The fire the after triggers.
  */
  if( row_triggers_exist ){
    if( !isView ){
      for(i=0, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, i++){
        if( openAll || aIdxUsed[i] )
          sqliteVdbeAddOp(v, OP_Close, iCur+i+1, 0);
      }
      sqliteVdbeAddOp(v, OP_Close, iCur, 0);
      pParse->nTab = iCur;
    }
    if( sqliteCodeRowTrigger(pParse, TK_UPDATE, pChanges, TK_AFTER, pTab, 
          newIdx, oldIdx, onError, addr) ){
      goto update_cleanup;
    }
  }

................................................................................
  sqliteVdbeChangeP2(v, addr, sqliteVdbeCurrentAddr(v));
  sqliteVdbeAddOp(v, OP_ListReset, 0, 0);

  /* Close all tables if there were no FOR EACH ROW triggers */
  if( !row_triggers_exist ){
    for(i=0, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, i++){
      if( openAll || aIdxUsed[i] ){
        sqliteVdbeAddOp(v, OP_Close, iCur+i+1, 0);
      }
    }
    sqliteVdbeAddOp(v, OP_Close, iCur, 0);
    pParse->nTab = iCur;
  }else{
    sqliteVdbeAddOp(v, OP_Close, newIdx, 0);
    sqliteVdbeAddOp(v, OP_Close, oldIdx, 0);
  }

  sqliteEndWriteOperation(pParse);

Changes to src/vdbe.c.

32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
....
3513
3514
3515
3516
3517
3518
3519
3520
3521
3522
3523
3524
3525
3526
3527
3528
3529
3530
3531
3532
3533
3534
3535
3536
3537
3538
3539
3540
3541
3542
3543
3544
3545
3546
3547
**
** Various scripts scan this source file in order to generate HTML
** documentation, headers files, or other derived files.  The formatting
** of the code in this file is, therefore, important.  See other comments
** in this file for details.  If in doubt, do not deviate from existing
** commenting and indentation practices when changing or adding code.
**
** $Id: vdbe.c,v 1.220 2003/04/25 15:37:58 drh Exp $
*/
#include "sqliteInt.h"
#include <ctype.h>

/*
** The makefile scans this source file and creates the following
** array of string constants which are the names of all VDBE opcodes.
................................................................................
  VERIFY( if( i<0 ) goto bad_instruction; )
  if( expandCursorArraySize(p, i) ) goto no_mem;
  pCx = &p->aCsr[i];
  cleanupCursor(pCx);
  memset(pCx, 0, sizeof(*pCx));
  pCx->nullRow = 1;
  pCx->pseudoTable = 1;
  break;
}

/*
** Opcode: RenameCursor P1 P2 *
**
** Rename cursor number P1 as cursor number P2.  If P2 was previously
** opened is is closed before the renaming occurs.
*/
case OP_RenameCursor: {
  int from = pOp->p1;
  int to = pOp->p2;
  VERIFY( if( from<0 || to<0 ) goto bad_instruction; )
  if( to<p->nCursor && p->aCsr[to].pCursor ){
    cleanupCursor(&p->aCsr[to]);
  }
  expandCursorArraySize(p, to);
  if( from<p->nCursor ){
    memcpy(&p->aCsr[to], &p->aCsr[from], sizeof(p->aCsr[0]));
    memset(&p->aCsr[from], 0, sizeof(p->aCsr[0]));
  }
  break;
}

/* Opcode: Close P1 * *
**
** Close a cursor previously opened as P1.  If P1 is not
** currently open, this instruction is a no-op.







|







 







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







32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
....
3513
3514
3515
3516
3517
3518
3519





















3520
3521
3522
3523
3524
3525
3526
**
** Various scripts scan this source file in order to generate HTML
** documentation, headers files, or other derived files.  The formatting
** of the code in this file is, therefore, important.  See other comments
** in this file for details.  If in doubt, do not deviate from existing
** commenting and indentation practices when changing or adding code.
**
** $Id: vdbe.c,v 1.221 2003/05/02 14:32:14 drh Exp $
*/
#include "sqliteInt.h"
#include <ctype.h>

/*
** The makefile scans this source file and creates the following
** array of string constants which are the names of all VDBE opcodes.
................................................................................
  VERIFY( if( i<0 ) goto bad_instruction; )
  if( expandCursorArraySize(p, i) ) goto no_mem;
  pCx = &p->aCsr[i];
  cleanupCursor(pCx);
  memset(pCx, 0, sizeof(*pCx));
  pCx->nullRow = 1;
  pCx->pseudoTable = 1;





















  break;
}

/* Opcode: Close P1 * *
**
** Close a cursor previously opened as P1.  If P1 is not
** currently open, this instruction is a no-op.

Changes to src/where.c.

8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
..
31
32
33
34
35
36
37















38
39
40
41
42
43
44
..
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
...
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
...
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
...
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
...
338
339
340
341
342
343
344

345
346
347
348
349
350
351
...
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
...
415
416
417
418
419
420
421
422

423
424
425
426
427
428
429
430
...
434
435
436
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
...
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
...
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
...
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
...
615
616
617
618
619
620
621

622
623
624
625
626
627
628
629
...
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
...
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
...
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
...
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
...
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
...
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
...
808
809
810
811
812
813
814
815
816
817
818
819
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842
...
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
...
890
891
892
893
894
895
896
897
898
899
900
901
902
903
904
905
906
907
908
909
910
911
912
913
...
935
936
937
938
939
940
941
942
943
944
945
946
947
948
949
950
951
952
953
954
955
956
957
958
959
...
990
991
992
993
994
995
996
997
998
999
1000
1001
1002
1003
1004
1005
1006
1007
1008
1009
1010
1011
1012
1013
1014
....
1046
1047
1048
1049
1050
1051
1052
1053
1054
1055
1056
1057
1058
1059
1060
1061
1062
1063
1064
1065
1066
1067
1068
1069
1070
1071
1072
1073
1074
1075
1076
1077
1078
1079
1080
1081
1082
1083
....
1092
1093
1094
1095
1096
1097
1098
1099
1100
1101
1102
1103
1104
1105
1106
1107
1108
1109

1110
1111
1112
1113
1114
1115
1116
1117
1118
1119
1120
1121
1122
1123
1124
1125
1126
1127
....
1131
1132
1133
1134
1135
1136
1137
1138
1139
1140
1141
1142
1143
1144
1145
1146
1147
1148
1149
1150
1151
1152
1153
1154
1155
1156
1157
1158
1159
1160
1161
1162
1163
**    May you find forgiveness for yourself and forgive others.
**    May you share freely, never taking more than you give.
**
*************************************************************************
** This module contains C code that generates VDBE code used to process
** the WHERE clause of SQL statements.
**
** $Id: where.c,v 1.77 2003/04/24 01:45:05 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.
................................................................................
                          ** p->pLeft is not the column of any table */
  short int idxRight;     /* p->pRight is a column in this table number. -1 if
                          ** p->pRight is not the column of any table */
  unsigned prereqLeft;    /* Bitmask of tables referenced by p->pLeft */
  unsigned prereqRight;   /* Bitmask of tables referenced by p->pRight */
  unsigned prereqAll;     /* Bitmask of tables referenced by p */
};
















/*
** Determine the number of elements in an array.
*/
#define ARRAYSIZE(X)  (sizeof(X)/sizeof(X[0]))

/*
................................................................................
  }else{
    cnt = exprSplit(nSlot, aSlot, pExpr->pLeft);
    cnt += exprSplit(nSlot-cnt, &aSlot[cnt], pExpr->pRight);
  }
  return cnt;
}

/*



























** This routine walks (recursively) an expression tree and generates
** a bitmask indicating which tables are used in that expression
** tree.  Bit 0 of the mask is set if table base+0 is used.  Bit 1
** is set if table base+1 is used.  And so forth.

**
** In order for this routine to work, the calling function must have
** previously invoked sqliteExprResolveIds() on the expression.  See
** the header comment on that routine for additional information.
**
** "base" is the cursor number (the value of the iTable field) that
** corresponds to the first entry in the list of tables that appear
** in the FROM clause of a SELECT.  For UPDATE and DELETE statements
** there is just a single table with "base" as the cursor number.
*/
static int exprTableUsage(int base, Expr *p){
  unsigned int mask = 0;
  if( p==0 ) return 0;
  if( p->op==TK_COLUMN ){
    return 1<< (p->iTable - base);
  }
  if( p->pRight ){
    mask = exprTableUsage(base, p->pRight);
  }
  if( p->pLeft ){
    mask |= exprTableUsage(base, p->pLeft);
  }
  if( p->pList ){
    int i;
    for(i=0; i<p->pList->nExpr; i++){
      mask |= exprTableUsage(base, p->pList->a[i].pExpr);
    }
  }
  return mask;
}

/*
** Return TRUE if the given operator is one of the operators that is
................................................................................
}

/*
** 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.
**
** "base" is the cursor number (the value of the iTable field) that
** corresponds to the first entry in the table list.
*/
static void exprAnalyze(int base, ExprInfo *pInfo){
  Expr *pExpr = pInfo->p;
  pInfo->prereqLeft = exprTableUsage(base, pExpr->pLeft);
  pInfo->prereqRight = exprTableUsage(base, pExpr->pRight);
  pInfo->prereqAll = exprTableUsage(base, pExpr);
  pInfo->indexable = 0;
  pInfo->idxLeft = -1;
  pInfo->idxRight = -1;
  if( allowedOp(pExpr->op) && (pInfo->prereqRight & pInfo->prereqLeft)==0 ){
    if( pExpr->pRight && 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;
    }
  }
}

/*
** pOrderBy is an ORDER BY clause from a SELECT statement.  pTab is the
................................................................................
**          foreach row3 in t3 do   /
**            ...
**          end                     \    Code generated
**        end                        |-- by sqliteWhereEnd()
**      end                         /
**
** There are Btree cursors associated with each table.  t1 uses cursor
** "base".  t2 uses cursor "base+1".  And so forth.  This routine generates
** the code to open those cursors.  sqliteWhereEnd() generates the code
** to close them.
**
** If the WHERE clause is empty, the foreach loops must each scan their
** entire tables.  Thus a three-way join is an O(N^3) operation.  But if
** the tables have indices and there are terms in the WHERE clause that
** refer to those indices, a complete table scan can be avoided and the
** code will run much faster.  Most of the work of this routine is checking
** to see if there are indices that can be used to speed up the loop.
................................................................................
** ORDER BY clause already exists.
**
** If the where clause loops cannot be arranged to provide the correct
** output order, then the *ppOrderBy is unchanged.
*/
WhereInfo *sqliteWhereBegin(
  Parse *pParse,       /* The parser context */
  int base,            /* VDBE cursor index for left-most table in pTabList */
  SrcList *pTabList,   /* A list of all tables to be scanned */
  Expr *pWhere,        /* The WHERE clause */
  int pushKey,         /* If TRUE, leave the table key on the stack */
  ExprList **ppOrderBy /* An ORDER BY clause, or NULL */
){
  int i;                     /* Loop counter */
  WhereInfo *pWInfo;         /* Will become the return value of this function */
  Vdbe *v = pParse->pVdbe;   /* The virtual database engine */
  int brk, cont;             /* Addresses used during code generation */
  int nExpr;           /* Number of subexpressions in the WHERE clause */
  int loopMask;        /* One bit set for each outer loop */
  int haveKey;         /* True if KEY is on the stack */

  int iDirectEq[32];   /* Term of the form ROWID==X for the N-th table */
  int iDirectLt[32];   /* Term of the form ROWID<X or ROWID<=X */
  int iDirectGt[32];   /* Term of the form ROWID>X or ROWID>=X */
  ExprInfo aExpr[101]; /* The WHERE clause is divided into these expressions */

  /* pushKey is only allowed if there is a single table (as in an INSERT or
  ** UPDATE statement)
................................................................................
  assert( pushKey==0 || pTabList->nSrc==1 );

  /* Split the WHERE clause into separate subexpressions where each
  ** subexpression is separated by an AND operator.  If the aExpr[]
  ** array fills up, the last entry might point to an expression which
  ** contains additional unfactored AND operators.
  */

  memset(aExpr, 0, sizeof(aExpr));
  nExpr = exprSplit(ARRAYSIZE(aExpr), aExpr, pWhere);
  if( nExpr==ARRAYSIZE(aExpr) ){
    char zBuf[50];
    sprintf(zBuf, "%d", (int)ARRAYSIZE(aExpr)-1);
    sqliteSetString(&pParse->zErrMsg, "WHERE clause too complex - no more "
       "than ", zBuf, " terms allowed", 0);
................................................................................
  pWInfo = sqliteMalloc( sizeof(WhereInfo) + pTabList->nSrc*sizeof(WhereLevel));
  if( sqlite_malloc_failed ){
    sqliteFree(pWInfo);
    return 0;
  }
  pWInfo->pParse = pParse;
  pWInfo->pTabList = pTabList;
  pWInfo->base = base;
  pWInfo->peakNTab = pWInfo->savedNTab = pParse->nTab;
  pWInfo->iBreak = sqliteVdbeMakeLabel(v);

  /* Special case: a WHERE clause that is constant.  Evaluate the
  ** expression and either jump over all of the code or fall thru.
  */
  if( pWhere && (pTabList->nSrc==0 || sqliteExprIsConstant(pWhere)) ){
................................................................................
    sqliteExprIfFalse(pParse, pWhere, pWInfo->iBreak, 1);
    pWhere = 0;
  }

  /* Analyze all of the subexpressions.
  */
  for(i=0; i<nExpr; i++){
    exprAnalyze(base, &aExpr[i]);

    /* If we are executing a trigger body, remove all references to
    ** new.* and old.* tables from the prerequisite masks.
    */
    if( pParse->trigStack ){
      int x;
      if( (x = pParse->trigStack->newIdx) >= 0 ){
        int mask = ~(1 << (x - base));
        aExpr[i].prereqRight &= mask;
        aExpr[i].prereqLeft &= mask;
        aExpr[i].prereqAll &= mask;
      }
      if( (x = pParse->trigStack->oldIdx) >= 0 ){
        int mask = ~(1 << (x - base));
        aExpr[i].prereqRight &= mask;
        aExpr[i].prereqLeft &= mask;
        aExpr[i].prereqAll &= mask;
      }
    }
  }

................................................................................
  ** Actually, if there are more than 32 tables in the join, only the
  ** first 32 tables are candidates for indices.  This is (again) due
  ** to the limit of 32 bits in an integer bitmask.
  */
  loopMask = 0;
  for(i=0; i<pTabList->nSrc && i<ARRAYSIZE(iDirectEq); i++){
    int j;
    int idx = 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.  For terms of the form ROWID==expr
    ** set iDirectEq[i] to the index of the term.  For terms of the
................................................................................
    ** (Added:) Treat ROWID IN expr like ROWID=expr.
    */
    pWInfo->a[i].iCur = -1;
    iDirectEq[i] = -1;
    iDirectLt[i] = -1;
    iDirectGt[i] = -1;
    for(j=0; j<nExpr; j++){
      if( aExpr[j].idxLeft==idx && aExpr[j].p->pLeft->iColumn<0
            && (aExpr[j].prereqRight & loopMask)==aExpr[j].prereqRight ){
        switch( aExpr[j].p->op ){
          case TK_IN:
          case TK_EQ: iDirectEq[i] = j; break;
          case TK_LE:
          case TK_LT: iDirectLt[i] = j; break;
          case TK_GE:
          case TK_GT: iDirectGt[i] = j;  break;
        }
      }
      if( aExpr[j].idxRight==idx && aExpr[j].p->pRight->iColumn<0
            && (aExpr[j].prereqLeft & loopMask)==aExpr[j].prereqLeft ){
        switch( aExpr[j].p->op ){
          case TK_EQ: iDirectEq[i] = j;  break;
          case TK_LE:
          case TK_LT: iDirectGt[i] = j;  break;
          case TK_GE:
          case TK_GT: iDirectLt[i] = j;  break;
        }
      }
    }
    if( iDirectEq[i]>=0 ){
      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.
................................................................................
      int ltMask = 0;  /* Index columns covered by an x<... term */
      int gtMask = 0;  /* Index columns covered by an x>... term */
      int inMask = 0;  /* Index columns covered by an x IN .. term */
      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_IN: {
................................................................................
                  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: {
................................................................................
        pBestIdx = pIdx;
        bestScore = score;
      }
    }
    pWInfo->a[i].pIdx = pBestIdx;
    pWInfo->a[i].score = bestScore;
    pWInfo->a[i].bRev = 0;
    loopMask |= 1<<idx;
    if( pBestIdx ){
      pWInfo->a[i].iCur = pParse->nTab++;
      pWInfo->peakNTab = pParse->nTab;
    }
  }

  /* Check to see if the ORDER BY clause is or can be satisfied by the
................................................................................
     }else if( iDirectEq[0]>=0 || iDirectLt[0]>=0 || iDirectGt[0]>=0 ){
       /* If the left-most column is accessed using its ROWID, then do
       ** not try to sort by index.
       */
       pSortIdx = 0;
     }else{
       int nEqCol = (pWInfo->a[0].score+4)/8;

       pSortIdx = findSortingIndex(pTab, base, *ppOrderBy, pIdx, nEqCol, &bRev);
     }
     if( pSortIdx && (pIdx==0 || pIdx==pSortIdx) ){
       if( pIdx==0 ){
         pWInfo->a[0].pIdx = pSortIdx;
         pWInfo->a[0].iCur = pParse->nTab++;
         pWInfo->peakNTab = pParse->nTab;
       }
................................................................................
  */
  for(i=0; i<pTabList->nSrc; i++){
    Table *pTab;

    pTab = pTabList->a[i].pTab;
    if( pTab->isTransient || pTab->pSelect ) continue;
    sqliteVdbeAddOp(v, OP_Integer, pTab->iDb, 0);
    sqliteVdbeAddOp(v, OP_OpenRead, base+i, pTab->tnum);
    sqliteVdbeChangeP3(v, -1, pTab->zName, P3_STATIC);
    if( i==0 && !pParse->schemaVerified &&
          (pParse->db->flags & SQLITE_InTrans)==0 ){
      sqliteCodeVerifySchema(pParse);
    }
    if( pWInfo->a[i].pIdx!=0 ){
      sqliteVdbeAddOp(v, OP_Integer, pWInfo->a[i].pIdx->iDb, 0);
................................................................................
  }

  /* Generate the code to do the search
  */
  loopMask = 0;
  for(i=0; i<pTabList->nSrc; i++){
    int j, k;
    int idx = i;
    Index *pIdx;
    WhereLevel *pLevel = &pWInfo->a[i];

    /* If this is the right table of a LEFT OUTER JOIN, allocate and
    ** initialize a memory cell that records if this table matches any
    ** row of the left table of the join.
    */
................................................................................
      **          equality comparison against the ROWID field.  Or
      **          we reference multiple rows using a "rowid IN (...)"
      **          construct.
      */
      k = iDirectEq[i];
      assert( k<nExpr );
      assert( aExpr[k].p!=0 );
      assert( aExpr[k].idxLeft==idx || aExpr[k].idxRight==idx );
      brk = pLevel->brk = sqliteVdbeMakeLabel(v);
      if( aExpr[k].idxLeft==idx ){
        Expr *pX = aExpr[k].p;
        if( pX->op!=TK_IN ){
          sqliteExprCode(pParse, aExpr[k].p->pRight);
        }else if( pX->pList ){
          sqliteVdbeAddOp(v, OP_SetFirst, pX->iTable, brk);
          pLevel->inOp = OP_SetNext;
          pLevel->inP1 = pX->iTable;
................................................................................
      }else{
        sqliteExprCode(pParse, aExpr[k].p->pLeft);
      }
      aExpr[k].p = 0;
      cont = pLevel->cont = sqliteVdbeMakeLabel(v);
      sqliteVdbeAddOp(v, OP_MustBeInt, 1, brk);
      haveKey = 0;
      sqliteVdbeAddOp(v, OP_NotExists, base+idx, brk);
      pLevel->op = OP_Noop;
    }else if( pIdx!=0 && pLevel->score>0 && pLevel->score%4==0 ){
      /* Case 2:  There is an index and all terms of the WHERE clause that
      **          refer to the index use the "==" or "IN" operators.
      */
      int start;
      int testOp;
      int nColumn = (pLevel->score+4)/8;
      brk = pLevel->brk = sqliteVdbeMakeLabel(v);
      for(j=0; j<nColumn; j++){
        for(k=0; k<nExpr; k++){
          Expr *pX = aExpr[k].p;
          if( pX==0 ) continue;
          if( aExpr[k].idxLeft==idx 
             && (aExpr[k].prereqRight & loopMask)==aExpr[k].prereqRight 
             && pX->pLeft->iColumn==pIdx->aiColumn[j]
          ){
            if( pX->op==TK_EQ ){
              sqliteExprCode(pParse, pX->pRight);
              aExpr[k].p = 0;
              break;
................................................................................
                pLevel->inOp = OP_Next;
                pLevel->inP1 = pX->iTable;
              }
              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;
................................................................................
        sqliteVdbeAddOp(v, testOp, pLevel->iCur, brk);
        sqliteVdbeAddOp(v, OP_IdxRecno, pLevel->iCur, 0);
        pLevel->op = OP_Next;
      }
      if( i==pTabList->nSrc-1 && pushKey ){
        haveKey = 1;
      }else{
        sqliteVdbeAddOp(v, OP_MoveTo, base+idx, 0);
        haveKey = 0;
      }
      pLevel->p1 = pLevel->iCur;
      pLevel->p2 = start;
    }else if( i<ARRAYSIZE(iDirectLt) && (iDirectLt[i]>=0 || iDirectGt[i]>=0) ){
      /* Case 3:  We have an inequality comparison against the ROWID field.
      */
................................................................................

      brk = pLevel->brk = sqliteVdbeMakeLabel(v);
      cont = pLevel->cont = sqliteVdbeMakeLabel(v);
      if( iDirectGt[i]>=0 ){
        k = iDirectGt[i];
        assert( k<nExpr );
        assert( aExpr[k].p!=0 );
        assert( aExpr[k].idxLeft==idx || aExpr[k].idxRight==idx );
        if( aExpr[k].idxLeft==idx ){
          sqliteExprCode(pParse, aExpr[k].p->pRight);
        }else{
          sqliteExprCode(pParse, aExpr[k].p->pLeft);
        }
        sqliteVdbeAddOp(v, OP_MustBeInt, 1, brk);
        if( aExpr[k].p->op==TK_LT || aExpr[k].p->op==TK_GT ){
          sqliteVdbeAddOp(v, OP_AddImm, 1, 0);
        }
        sqliteVdbeAddOp(v, OP_MoveTo, base+idx, brk);
        aExpr[k].p = 0;
      }else{
        sqliteVdbeAddOp(v, OP_Rewind, base+idx, brk);
      }
      if( iDirectLt[i]>=0 ){
        k = iDirectLt[i];
        assert( k<nExpr );
        assert( aExpr[k].p!=0 );
        assert( aExpr[k].idxLeft==idx || aExpr[k].idxRight==idx );
        if( aExpr[k].idxLeft==idx ){
          sqliteExprCode(pParse, aExpr[k].p->pRight);
        }else{
          sqliteExprCode(pParse, aExpr[k].p->pLeft);
        }
        sqliteVdbeAddOp(v, OP_MustBeInt, 1, sqliteVdbeCurrentAddr(v)+1);
        pLevel->iMem = pParse->nMem++;
        sqliteVdbeAddOp(v, OP_MemStore, pLevel->iMem, 0);
................................................................................
        }else{
          testOp = OP_Gt;
        }
        aExpr[k].p = 0;
      }
      start = sqliteVdbeCurrentAddr(v);
      pLevel->op = OP_Next;
      pLevel->p1 = base+idx;
      pLevel->p2 = start;
      if( testOp!=OP_Noop ){
        sqliteVdbeAddOp(v, OP_Recno, base+idx, 0);
        sqliteVdbeAddOp(v, OP_MemLoad, pLevel->iMem, 0);
        sqliteVdbeAddOp(v, testOp, 0, brk);
      }
      haveKey = 0;
    }else if( pIdx==0 ){
      /* Case 4:  There is 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{
      /* Case 5: The WHERE clause term that refers to the right-most
      **         column of the index is an inequality.  For example, if
      **         the index is on (x,y,z) and the WHERE clause is of the
      **         form "x=5 AND y<10" then this case is used.  Only the
................................................................................
      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;
................................................................................
      ** 2002-Dec-04: On a reverse-order scan, the so-called "termination"
      ** key computed here really ends up being the start key.
      */
      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;
................................................................................
      ** 2002-Dec-04: In the case of a reverse-order search, the so-called
      ** "start" key really ends up being used as the termination key.
      */
      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;
................................................................................
        sqliteVdbeAddOp(v, OP_MemLoad, pLevel->iMem, 0);
        sqliteVdbeAddOp(v, testOp, pLevel->iCur, brk);
      }
      sqliteVdbeAddOp(v, OP_IdxRecno, pLevel->iCur, 0);
      if( i==pTabList->nSrc-1 && pushKey ){
        haveKey = 1;
      }else{
        sqliteVdbeAddOp(v, OP_MoveTo, base+idx, 0);
        haveKey = 0;
      }

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

    /* Insert code to test every subexpression that can be completely
    ** computed using the current set of tables.
    */
    for(j=0; j<nExpr; j++){
      if( aExpr[j].p==0 ) continue;
      if( (aExpr[j].prereqAll & loopMask)!=aExpr[j].prereqAll ) continue;
      if( pLevel->iLeftJoin && !ExprHasProperty(aExpr[j].p,EP_FromJoin) ){
        continue;
      }
      if( haveKey ){
        haveKey = 0;
        sqliteVdbeAddOp(v, OP_MoveTo, base+idx, 0);
      }
      sqliteExprIfFalse(pParse, aExpr[j].p, cont, 1);
      aExpr[j].p = 0;
    }
    brk = cont;

    /* For a LEFT OUTER JOIN, generate code that will record the fact that
................................................................................
        if( (aExpr[j].prereqAll & loopMask)!=aExpr[j].prereqAll ) continue;
        if( haveKey ){
          /* Cannot happen.  "haveKey" can only be true if pushKey is true
          ** an pushKey can only be true for DELETE and UPDATE and there are
          ** no outer joins with DELETE and UPDATE.
          */
          haveKey = 0;
          sqliteVdbeAddOp(v, OP_MoveTo, base+idx, 0);
        }
        sqliteExprIfFalse(pParse, aExpr[j].p, cont, 1);
        aExpr[j].p = 0;
      }
    }
  }
  pWInfo->iContinue = cont;
  if( pushKey && !haveKey ){
    sqliteVdbeAddOp(v, OP_Recno, base, 0);
  }

  return pWInfo;
}

/*
** Generate the end of the WHERE loop.  See comments on 
** sqliteWhereBegin() for additional information.
*/
void sqliteWhereEnd(WhereInfo *pWInfo){
  Vdbe *v = pWInfo->pParse->pVdbe;
  int i;
  int base = pWInfo->base;
  WhereLevel *pLevel;
  SrcList *pTabList = pWInfo->pTabList;

  for(i=pTabList->nSrc-1; i>=0; i--){
    pLevel = &pWInfo->a[i];
    sqliteVdbeResolveLabel(v, pLevel->cont);
    if( pLevel->op!=OP_Noop ){
................................................................................
    if( pLevel->inOp!=OP_Noop ){
      sqliteVdbeAddOp(v, pLevel->inOp, pLevel->inP1, pLevel->inP2);
    }
    if( pLevel->iLeftJoin ){
      int addr;
      addr = sqliteVdbeAddOp(v, OP_MemLoad, pLevel->iLeftJoin, 0);
      sqliteVdbeAddOp(v, OP_NotNull, 1, addr+4 + (pLevel->iCur>=0));
      sqliteVdbeAddOp(v, OP_NullRow, base+i, 0);
      if( pLevel->iCur>=0 ){
        sqliteVdbeAddOp(v, OP_NullRow, pLevel->iCur, 0);
      }
      sqliteVdbeAddOp(v, OP_Goto, 0, pLevel->top);
    }
  }
  sqliteVdbeResolveLabel(v, pWInfo->iBreak);
  for(i=0; i<pTabList->nSrc; i++){
    Table *pTab = pTabList->a[i].pTab;
    assert( pTab!=0 );
    if( pTab->isTransient || pTab->pSelect ) continue;
    pLevel = &pWInfo->a[i];
    sqliteVdbeAddOp(v, OP_Close, base+i, 0);
    if( pLevel->pIdx!=0 ){
      sqliteVdbeAddOp(v, OP_Close, pLevel->iCur, 0);
    }
  }
#if 0  /* Never reuse a cursor */
  if( pWInfo->pParse->nTab==pWInfo->peakNTab ){
    pWInfo->pParse->nTab = pWInfo->savedNTab;
  }
#endif
  sqliteFree(pWInfo);
  return;
}







|







 







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







 








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


<
<
>




|
|
|
<
<

|



|


|


|




|







 







<
<
<

|

|
|
|





|



|







 







|
|
|







 







<












>







 







>







 







<







 







|







|





|







 







|
>
|







 







|










|











|







 







|







 







|







 







|







 







>
|







 







|







 







|







 







|

|







 







|













|







 







|







 







|







 







|
|








|


|





|
|







 







|


|












|


|







 







|








|







 







|









|







 







|









|







 







|









|












|







 







|








|

>










<







 







|












|












8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
..
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
..
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
...
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
...
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
...
346
347
348
349
350
351
352

353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
...
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
...
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
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
...
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
...
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
...
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
...
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
...
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
...
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
...
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
...
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
...
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
...
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
...
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
...
829
830
831
832
833
834
835
836
837
838
839
840
841
842
843
...
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
879
880
...
883
884
885
886
887
888
889
890
891
892
893
894
895
896
897
898
899
900
901
902
903
904
905
906
907
908
909
910
911
912
913
914
915
916
...
928
929
930
931
932
933
934
935
936
937
938
939
940
941
942
943
944
945
946
947
948
949
950
951
...
973
974
975
976
977
978
979
980
981
982
983
984
985
986
987
988
989
990
991
992
993
994
995
996
997
....
1028
1029
1030
1031
1032
1033
1034
1035
1036
1037
1038
1039
1040
1041
1042
1043
1044
1045
1046
1047
1048
1049
1050
1051
1052
....
1084
1085
1086
1087
1088
1089
1090
1091
1092
1093
1094
1095
1096
1097
1098
1099
1100
1101
1102
1103
1104
1105
1106
1107
1108
1109
1110
1111
1112
1113
1114
1115
1116
1117
1118
1119
1120
1121
....
1130
1131
1132
1133
1134
1135
1136
1137
1138
1139
1140
1141
1142
1143
1144
1145
1146
1147
1148
1149
1150
1151
1152
1153
1154
1155
1156
1157
1158

1159
1160
1161
1162
1163
1164
1165
....
1169
1170
1171
1172
1173
1174
1175
1176
1177
1178
1179
1180
1181
1182
1183
1184
1185
1186
1187
1188
1189
1190
1191
1192
1193
1194
1195
1196
1197
1198
1199
1200
1201
**    May you find forgiveness for yourself and forgive others.
**    May you share freely, never taking more than you give.
**
*************************************************************************
** This module contains C code that generates VDBE code used to process
** the WHERE clause of SQL statements.
**
** $Id: where.c,v 1.78 2003/05/02 14:32:14 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.
................................................................................
                          ** p->pLeft is not the column of any table */
  short int idxRight;     /* p->pRight is a column in this table number. -1 if
                          ** p->pRight is not the column of any table */
  unsigned prereqLeft;    /* Bitmask of tables referenced by p->pLeft */
  unsigned prereqRight;   /* Bitmask of tables referenced by p->pRight */
  unsigned prereqAll;     /* Bitmask of tables referenced by p */
};

/*
** An instance of the following structure keeps track of a mapping
** between VDBE cursor numbers and bitmasks.  The VDBE cursor numbers
** are small integers contained in SrcList_item.iCursor and Expr.iTable
** fields.  For any given WHERE clause, we want to track which cursors
** are being used, so we assign a single bit in a 32-bit word to track
** that cursor.  Then a 32-bit integer is able to show the set of all
** cursors being used.
*/
typedef struct ExprMaskSet ExprMaskSet;
struct ExprMaskSet {
  int n;          /* Number of assigned cursor values */
  int ix[32];     /* Cursor assigned to each bit */
};

/*
** Determine the number of elements in an array.
*/
#define ARRAYSIZE(X)  (sizeof(X)/sizeof(X[0]))

/*
................................................................................
  }else{
    cnt = exprSplit(nSlot, aSlot, pExpr->pLeft);
    cnt += exprSplit(nSlot-cnt, &aSlot[cnt], pExpr->pRight);
  }
  return cnt;
}

/*
** Initialize an expression mask set
*/
#define initMaskSet(P)  memset(P, 0, sizeof(*P))

/*
** Return the bitmask for the given cursor.  Assign a new bitmask
** if this is the first time the cursor has been seen.
*/
static int getMask(ExprMaskSet *pMaskSet, int iCursor){
  int i;
  for(i=0; i<pMaskSet->n; i++){
    if( pMaskSet->ix[i]==iCursor ) return 1<<i;
  }
  if( i==pMaskSet->n && i<ARRAYSIZE(pMaskSet->ix) ){
    pMaskSet->n++;
    pMaskSet->ix[i] = iCursor;
    return 1<<i;
  }
  return 0;
}

/*
** Destroy an expression mask set
*/
#define freeMaskSet(P)   /* NO-OP */

/*
** This routine walks (recursively) an expression tree and generates
** a bitmask indicating which tables are used in that expression


** tree.
**
** In order for this routine to work, the calling function must have
** previously invoked sqliteExprResolveIds() on the expression.  See
** the header comment on that routine for additional information.
** The sqliteExprResolveIds() routines looks for column names and
** sets their opcodes to TK_COLUMN and their Expr.iTable fields to
** the VDBE cursor number of the table.


*/
static int exprTableUsage(ExprMaskSet *pMaskSet, Expr *p){
  unsigned int mask = 0;
  if( p==0 ) return 0;
  if( p->op==TK_COLUMN ){
    return getMask(pMaskSet, p->iTable);
  }
  if( p->pRight ){
    mask = exprTableUsage(pMaskSet, p->pRight);
  }
  if( p->pLeft ){
    mask |= exprTableUsage(pMaskSet, p->pLeft);
  }
  if( p->pList ){
    int i;
    for(i=0; i<p->pList->nExpr; i++){
      mask |= exprTableUsage(pMaskSet, p->pList->a[i].pExpr);
    }
  }
  return mask;
}

/*
** Return TRUE if the given operator is one of the operators that is
................................................................................
}

/*
** 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(ExprMaskSet *pMaskSet, ExprInfo *pInfo){
  Expr *pExpr = pInfo->p;
  pInfo->prereqLeft = exprTableUsage(pMaskSet, pExpr->pLeft);
  pInfo->prereqRight = exprTableUsage(pMaskSet, pExpr->pRight);
  pInfo->prereqAll = exprTableUsage(pMaskSet, pExpr);
  pInfo->indexable = 0;
  pInfo->idxLeft = -1;
  pInfo->idxRight = -1;
  if( allowedOp(pExpr->op) && (pInfo->prereqRight & pInfo->prereqLeft)==0 ){
    if( pExpr->pRight && pExpr->pRight->op==TK_COLUMN ){
      pInfo->idxRight = pExpr->pRight->iTable;
      pInfo->indexable = 1;
    }
    if( pExpr->pLeft->op==TK_COLUMN ){
      pInfo->idxLeft = pExpr->pLeft->iTable;
      pInfo->indexable = 1;
    }
  }
}

/*
** pOrderBy is an ORDER BY clause from a SELECT statement.  pTab is the
................................................................................
**          foreach row3 in t3 do   /
**            ...
**          end                     \    Code generated
**        end                        |-- by sqliteWhereEnd()
**      end                         /
**
** There are Btree cursors associated with each table.  t1 uses cursor
** number pTabList->a[0].iCursor.  t2 uses the cursor pTabList->a[1].iCursor.
** And so forth.  This routine generates code to open those VDBE cursors
** and sqliteWhereEnd() generates the code to close them.
**
** If the WHERE clause is empty, the foreach loops must each scan their
** entire tables.  Thus a three-way join is an O(N^3) operation.  But if
** the tables have indices and there are terms in the WHERE clause that
** refer to those indices, a complete table scan can be avoided and the
** code will run much faster.  Most of the work of this routine is checking
** to see if there are indices that can be used to speed up the loop.
................................................................................
** ORDER BY clause already exists.
**
** If the where clause loops cannot be arranged to provide the correct
** output order, then the *ppOrderBy is unchanged.
*/
WhereInfo *sqliteWhereBegin(
  Parse *pParse,       /* The parser context */

  SrcList *pTabList,   /* A list of all tables to be scanned */
  Expr *pWhere,        /* The WHERE clause */
  int pushKey,         /* If TRUE, leave the table key on the stack */
  ExprList **ppOrderBy /* An ORDER BY clause, or NULL */
){
  int i;                     /* Loop counter */
  WhereInfo *pWInfo;         /* Will become the return value of this function */
  Vdbe *v = pParse->pVdbe;   /* The virtual database engine */
  int brk, cont;             /* Addresses used during code generation */
  int nExpr;           /* Number of subexpressions in the WHERE clause */
  int loopMask;        /* One bit set for each outer loop */
  int haveKey;         /* True if KEY is on the stack */
  ExprMaskSet maskSet; /* The expression mask set */
  int iDirectEq[32];   /* Term of the form ROWID==X for the N-th table */
  int iDirectLt[32];   /* Term of the form ROWID<X or ROWID<=X */
  int iDirectGt[32];   /* Term of the form ROWID>X or ROWID>=X */
  ExprInfo aExpr[101]; /* The WHERE clause is divided into these expressions */

  /* pushKey is only allowed if there is a single table (as in an INSERT or
  ** UPDATE statement)
................................................................................
  assert( pushKey==0 || pTabList->nSrc==1 );

  /* Split the WHERE clause into separate subexpressions where each
  ** subexpression is separated by an AND operator.  If the aExpr[]
  ** array fills up, the last entry might point to an expression which
  ** contains additional unfactored AND operators.
  */
  initMaskSet(&maskSet);
  memset(aExpr, 0, sizeof(aExpr));
  nExpr = exprSplit(ARRAYSIZE(aExpr), aExpr, pWhere);
  if( nExpr==ARRAYSIZE(aExpr) ){
    char zBuf[50];
    sprintf(zBuf, "%d", (int)ARRAYSIZE(aExpr)-1);
    sqliteSetString(&pParse->zErrMsg, "WHERE clause too complex - no more "
       "than ", zBuf, " terms allowed", 0);
................................................................................
  pWInfo = sqliteMalloc( sizeof(WhereInfo) + pTabList->nSrc*sizeof(WhereLevel));
  if( sqlite_malloc_failed ){
    sqliteFree(pWInfo);
    return 0;
  }
  pWInfo->pParse = pParse;
  pWInfo->pTabList = pTabList;

  pWInfo->peakNTab = pWInfo->savedNTab = pParse->nTab;
  pWInfo->iBreak = sqliteVdbeMakeLabel(v);

  /* Special case: a WHERE clause that is constant.  Evaluate the
  ** expression and either jump over all of the code or fall thru.
  */
  if( pWhere && (pTabList->nSrc==0 || sqliteExprIsConstant(pWhere)) ){
................................................................................
    sqliteExprIfFalse(pParse, pWhere, pWInfo->iBreak, 1);
    pWhere = 0;
  }

  /* Analyze all of the subexpressions.
  */
  for(i=0; i<nExpr; i++){
    exprAnalyze(&maskSet, &aExpr[i]);

    /* If we are executing a trigger body, remove all references to
    ** new.* and old.* tables from the prerequisite masks.
    */
    if( pParse->trigStack ){
      int x;
      if( (x = pParse->trigStack->newIdx) >= 0 ){
        int mask = ~getMask(&maskSet, x);
        aExpr[i].prereqRight &= mask;
        aExpr[i].prereqLeft &= mask;
        aExpr[i].prereqAll &= mask;
      }
      if( (x = pParse->trigStack->oldIdx) >= 0 ){
        int mask = ~getMask(&maskSet, x);
        aExpr[i].prereqRight &= mask;
        aExpr[i].prereqLeft &= mask;
        aExpr[i].prereqAll &= mask;
      }
    }
  }

................................................................................
  ** Actually, if there are more than 32 tables in the join, only the
  ** first 32 tables are candidates for indices.  This is (again) due
  ** to the limit of 32 bits in an integer bitmask.
  */
  loopMask = 0;
  for(i=0; i<pTabList->nSrc && i<ARRAYSIZE(iDirectEq); i++){
    int j;
    int iCur = pTabList->a[i].iCursor;    /* The cursor for this table */
    int mask = getMask(&maskSet, iCur);   /* Cursor mask for this table */
    Table *pTab = pTabList->a[i].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.  For terms of the form ROWID==expr
    ** set iDirectEq[i] to the index of the term.  For terms of the
................................................................................
    ** (Added:) Treat ROWID IN expr like ROWID=expr.
    */
    pWInfo->a[i].iCur = -1;
    iDirectEq[i] = -1;
    iDirectLt[i] = -1;
    iDirectGt[i] = -1;
    for(j=0; j<nExpr; j++){
      if( aExpr[j].idxLeft==iCur && aExpr[j].p->pLeft->iColumn<0
            && (aExpr[j].prereqRight & loopMask)==aExpr[j].prereqRight ){
        switch( aExpr[j].p->op ){
          case TK_IN:
          case TK_EQ: iDirectEq[i] = j; break;
          case TK_LE:
          case TK_LT: iDirectLt[i] = j; break;
          case TK_GE:
          case TK_GT: iDirectGt[i] = j;  break;
        }
      }
      if( aExpr[j].idxRight==iCur && aExpr[j].p->pRight->iColumn<0
            && (aExpr[j].prereqLeft & loopMask)==aExpr[j].prereqLeft ){
        switch( aExpr[j].p->op ){
          case TK_EQ: iDirectEq[i] = j;  break;
          case TK_LE:
          case TK_LT: iDirectGt[i] = j;  break;
          case TK_GE:
          case TK_GT: iDirectLt[i] = j;  break;
        }
      }
    }
    if( iDirectEq[i]>=0 ){
      loopMask |= mask;
      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.
................................................................................
      int ltMask = 0;  /* Index columns covered by an x<... term */
      int gtMask = 0;  /* Index columns covered by an x>... term */
      int inMask = 0;  /* Index columns covered by an x IN .. term */
      int nEq, m, score;

      if( pIdx->nColumn>32 ) continue;  /* Ignore indices too many columns */
      for(j=0; j<nExpr; j++){
        if( aExpr[j].idxLeft==iCur 
             && (aExpr[j].prereqRight & loopMask)==aExpr[j].prereqRight ){
          int iColumn = aExpr[j].p->pLeft->iColumn;
          int k;
          for(k=0; k<pIdx->nColumn; k++){
            if( pIdx->aiColumn[k]==iColumn ){
              switch( aExpr[j].p->op ){
                case TK_IN: {
................................................................................
                  break;
                }
              }
              break;
            }
          }
        }
        if( aExpr[j].idxRight==iCur 
             && (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: {
................................................................................
        pBestIdx = pIdx;
        bestScore = score;
      }
    }
    pWInfo->a[i].pIdx = pBestIdx;
    pWInfo->a[i].score = bestScore;
    pWInfo->a[i].bRev = 0;
    loopMask |= mask;
    if( pBestIdx ){
      pWInfo->a[i].iCur = pParse->nTab++;
      pWInfo->peakNTab = pParse->nTab;
    }
  }

  /* Check to see if the ORDER BY clause is or can be satisfied by the
................................................................................
     }else if( iDirectEq[0]>=0 || iDirectLt[0]>=0 || iDirectGt[0]>=0 ){
       /* If the left-most column is accessed using its ROWID, then do
       ** not try to sort by index.
       */
       pSortIdx = 0;
     }else{
       int nEqCol = (pWInfo->a[0].score+4)/8;
       pSortIdx = findSortingIndex(pTab, pTabList->a[0].iCursor, 
                                   *ppOrderBy, pIdx, nEqCol, &bRev);
     }
     if( pSortIdx && (pIdx==0 || pIdx==pSortIdx) ){
       if( pIdx==0 ){
         pWInfo->a[0].pIdx = pSortIdx;
         pWInfo->a[0].iCur = pParse->nTab++;
         pWInfo->peakNTab = pParse->nTab;
       }
................................................................................
  */
  for(i=0; i<pTabList->nSrc; i++){
    Table *pTab;

    pTab = pTabList->a[i].pTab;
    if( pTab->isTransient || pTab->pSelect ) continue;
    sqliteVdbeAddOp(v, OP_Integer, pTab->iDb, 0);
    sqliteVdbeAddOp(v, OP_OpenRead, pTabList->a[i].iCursor, pTab->tnum);
    sqliteVdbeChangeP3(v, -1, pTab->zName, P3_STATIC);
    if( i==0 && !pParse->schemaVerified &&
          (pParse->db->flags & SQLITE_InTrans)==0 ){
      sqliteCodeVerifySchema(pParse);
    }
    if( pWInfo->a[i].pIdx!=0 ){
      sqliteVdbeAddOp(v, OP_Integer, pWInfo->a[i].pIdx->iDb, 0);
................................................................................
  }

  /* Generate the code to do the search
  */
  loopMask = 0;
  for(i=0; i<pTabList->nSrc; i++){
    int j, k;
    int iCur = pTabList->a[i].iCursor;
    Index *pIdx;
    WhereLevel *pLevel = &pWInfo->a[i];

    /* If this is the right table of a LEFT OUTER JOIN, allocate and
    ** initialize a memory cell that records if this table matches any
    ** row of the left table of the join.
    */
................................................................................
      **          equality comparison against the ROWID field.  Or
      **          we reference multiple rows using a "rowid IN (...)"
      **          construct.
      */
      k = iDirectEq[i];
      assert( k<nExpr );
      assert( aExpr[k].p!=0 );
      assert( aExpr[k].idxLeft==iCur || aExpr[k].idxRight==iCur );
      brk = pLevel->brk = sqliteVdbeMakeLabel(v);
      if( aExpr[k].idxLeft==iCur ){
        Expr *pX = aExpr[k].p;
        if( pX->op!=TK_IN ){
          sqliteExprCode(pParse, aExpr[k].p->pRight);
        }else if( pX->pList ){
          sqliteVdbeAddOp(v, OP_SetFirst, pX->iTable, brk);
          pLevel->inOp = OP_SetNext;
          pLevel->inP1 = pX->iTable;
................................................................................
      }else{
        sqliteExprCode(pParse, aExpr[k].p->pLeft);
      }
      aExpr[k].p = 0;
      cont = pLevel->cont = sqliteVdbeMakeLabel(v);
      sqliteVdbeAddOp(v, OP_MustBeInt, 1, brk);
      haveKey = 0;
      sqliteVdbeAddOp(v, OP_NotExists, iCur, brk);
      pLevel->op = OP_Noop;
    }else if( pIdx!=0 && pLevel->score>0 && pLevel->score%4==0 ){
      /* Case 2:  There is an index and all terms of the WHERE clause that
      **          refer to the index use the "==" or "IN" operators.
      */
      int start;
      int testOp;
      int nColumn = (pLevel->score+4)/8;
      brk = pLevel->brk = sqliteVdbeMakeLabel(v);
      for(j=0; j<nColumn; j++){
        for(k=0; k<nExpr; k++){
          Expr *pX = aExpr[k].p;
          if( pX==0 ) continue;
          if( aExpr[k].idxLeft==iCur
             && (aExpr[k].prereqRight & loopMask)==aExpr[k].prereqRight 
             && pX->pLeft->iColumn==pIdx->aiColumn[j]
          ){
            if( pX->op==TK_EQ ){
              sqliteExprCode(pParse, pX->pRight);
              aExpr[k].p = 0;
              break;
................................................................................
                pLevel->inOp = OP_Next;
                pLevel->inP1 = pX->iTable;
              }
              aExpr[k].p = 0;
              break;
            }
          }
          if( aExpr[k].idxRight==iCur
             && 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;
................................................................................
        sqliteVdbeAddOp(v, testOp, pLevel->iCur, brk);
        sqliteVdbeAddOp(v, OP_IdxRecno, pLevel->iCur, 0);
        pLevel->op = OP_Next;
      }
      if( i==pTabList->nSrc-1 && pushKey ){
        haveKey = 1;
      }else{
        sqliteVdbeAddOp(v, OP_MoveTo, iCur, 0);
        haveKey = 0;
      }
      pLevel->p1 = pLevel->iCur;
      pLevel->p2 = start;
    }else if( i<ARRAYSIZE(iDirectLt) && (iDirectLt[i]>=0 || iDirectGt[i]>=0) ){
      /* Case 3:  We have an inequality comparison against the ROWID field.
      */
................................................................................

      brk = pLevel->brk = sqliteVdbeMakeLabel(v);
      cont = pLevel->cont = sqliteVdbeMakeLabel(v);
      if( iDirectGt[i]>=0 ){
        k = iDirectGt[i];
        assert( k<nExpr );
        assert( aExpr[k].p!=0 );
        assert( aExpr[k].idxLeft==iCur || aExpr[k].idxRight==iCur );
        if( aExpr[k].idxLeft==iCur ){
          sqliteExprCode(pParse, aExpr[k].p->pRight);
        }else{
          sqliteExprCode(pParse, aExpr[k].p->pLeft);
        }
        sqliteVdbeAddOp(v, OP_MustBeInt, 1, brk);
        if( aExpr[k].p->op==TK_LT || aExpr[k].p->op==TK_GT ){
          sqliteVdbeAddOp(v, OP_AddImm, 1, 0);
        }
        sqliteVdbeAddOp(v, OP_MoveTo, iCur, brk);
        aExpr[k].p = 0;
      }else{
        sqliteVdbeAddOp(v, OP_Rewind, iCur, brk);
      }
      if( iDirectLt[i]>=0 ){
        k = iDirectLt[i];
        assert( k<nExpr );
        assert( aExpr[k].p!=0 );
        assert( aExpr[k].idxLeft==iCur || aExpr[k].idxRight==iCur );
        if( aExpr[k].idxLeft==iCur ){
          sqliteExprCode(pParse, aExpr[k].p->pRight);
        }else{
          sqliteExprCode(pParse, aExpr[k].p->pLeft);
        }
        sqliteVdbeAddOp(v, OP_MustBeInt, 1, sqliteVdbeCurrentAddr(v)+1);
        pLevel->iMem = pParse->nMem++;
        sqliteVdbeAddOp(v, OP_MemStore, pLevel->iMem, 0);
................................................................................
        }else{
          testOp = OP_Gt;
        }
        aExpr[k].p = 0;
      }
      start = sqliteVdbeCurrentAddr(v);
      pLevel->op = OP_Next;
      pLevel->p1 = iCur;
      pLevel->p2 = start;
      if( testOp!=OP_Noop ){
        sqliteVdbeAddOp(v, OP_Recno, iCur, 0);
        sqliteVdbeAddOp(v, OP_MemLoad, pLevel->iMem, 0);
        sqliteVdbeAddOp(v, testOp, 0, brk);
      }
      haveKey = 0;
    }else if( pIdx==0 ){
      /* Case 4:  There is 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, iCur, brk);
      start = sqliteVdbeCurrentAddr(v);
      pLevel->op = OP_Next;
      pLevel->p1 = iCur;
      pLevel->p2 = start;
      haveKey = 0;
    }else{
      /* Case 5: The WHERE clause term that refers to the right-most
      **         column of the index is an inequality.  For example, if
      **         the index is on (x,y,z) and the WHERE clause is of the
      **         form "x=5 AND y<10" then this case is used.  Only the
................................................................................
      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==iCur
             && 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==iCur
             && 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;
................................................................................
      ** 2002-Dec-04: On a reverse-order scan, the so-called "termination"
      ** key computed here really ends up being the start key.
      */
      if( (score & 1)!=0 ){
        for(k=0; k<nExpr; k++){
          Expr *pExpr = aExpr[k].p;
          if( pExpr==0 ) continue;
          if( aExpr[k].idxLeft==iCur
             && (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==iCur
             && (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;
................................................................................
      ** 2002-Dec-04: In the case of a reverse-order search, the so-called
      ** "start" key really ends up being used as the termination key.
      */
      if( (score & 2)!=0 ){
        for(k=0; k<nExpr; k++){
          Expr *pExpr = aExpr[k].p;
          if( pExpr==0 ) continue;
          if( aExpr[k].idxLeft==iCur
             && (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==iCur
             && (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;
................................................................................
        sqliteVdbeAddOp(v, OP_MemLoad, pLevel->iMem, 0);
        sqliteVdbeAddOp(v, testOp, pLevel->iCur, brk);
      }
      sqliteVdbeAddOp(v, OP_IdxRecno, pLevel->iCur, 0);
      if( i==pTabList->nSrc-1 && pushKey ){
        haveKey = 1;
      }else{
        sqliteVdbeAddOp(v, OP_MoveTo, iCur, 0);
        haveKey = 0;
      }

      /* Record the instruction used to terminate the loop.
      */
      pLevel->op = pLevel->bRev ? OP_Prev : OP_Next;
      pLevel->p1 = pLevel->iCur;
      pLevel->p2 = start;
    }
    loopMask |= getMask(&maskSet, iCur);

    /* Insert code to test every subexpression that can be completely
    ** computed using the current set of tables.
    */
    for(j=0; j<nExpr; j++){
      if( aExpr[j].p==0 ) continue;
      if( (aExpr[j].prereqAll & loopMask)!=aExpr[j].prereqAll ) continue;
      if( pLevel->iLeftJoin && !ExprHasProperty(aExpr[j].p,EP_FromJoin) ){
        continue;
      }
      if( haveKey ){
        haveKey = 0;
        sqliteVdbeAddOp(v, OP_MoveTo, iCur, 0);
      }
      sqliteExprIfFalse(pParse, aExpr[j].p, cont, 1);
      aExpr[j].p = 0;
    }
    brk = cont;

    /* For a LEFT OUTER JOIN, generate code that will record the fact that
................................................................................
        if( (aExpr[j].prereqAll & loopMask)!=aExpr[j].prereqAll ) continue;
        if( haveKey ){
          /* Cannot happen.  "haveKey" can only be true if pushKey is true
          ** an pushKey can only be true for DELETE and UPDATE and there are
          ** no outer joins with DELETE and UPDATE.
          */
          haveKey = 0;
          sqliteVdbeAddOp(v, OP_MoveTo, iCur, 0);
        }
        sqliteExprIfFalse(pParse, aExpr[j].p, cont, 1);
        aExpr[j].p = 0;
      }
    }
  }
  pWInfo->iContinue = cont;
  if( pushKey && !haveKey ){
    sqliteVdbeAddOp(v, OP_Recno, pTabList->a[0].iCursor, 0);
  }
  freeMaskSet(&maskSet);
  return pWInfo;
}

/*
** Generate the end of the WHERE loop.  See comments on 
** sqliteWhereBegin() for additional information.
*/
void sqliteWhereEnd(WhereInfo *pWInfo){
  Vdbe *v = pWInfo->pParse->pVdbe;
  int i;

  WhereLevel *pLevel;
  SrcList *pTabList = pWInfo->pTabList;

  for(i=pTabList->nSrc-1; i>=0; i--){
    pLevel = &pWInfo->a[i];
    sqliteVdbeResolveLabel(v, pLevel->cont);
    if( pLevel->op!=OP_Noop ){
................................................................................
    if( pLevel->inOp!=OP_Noop ){
      sqliteVdbeAddOp(v, pLevel->inOp, pLevel->inP1, pLevel->inP2);
    }
    if( pLevel->iLeftJoin ){
      int addr;
      addr = sqliteVdbeAddOp(v, OP_MemLoad, pLevel->iLeftJoin, 0);
      sqliteVdbeAddOp(v, OP_NotNull, 1, addr+4 + (pLevel->iCur>=0));
      sqliteVdbeAddOp(v, OP_NullRow, pTabList->a[i].iCursor, 0);
      if( pLevel->iCur>=0 ){
        sqliteVdbeAddOp(v, OP_NullRow, pLevel->iCur, 0);
      }
      sqliteVdbeAddOp(v, OP_Goto, 0, pLevel->top);
    }
  }
  sqliteVdbeResolveLabel(v, pWInfo->iBreak);
  for(i=0; i<pTabList->nSrc; i++){
    Table *pTab = pTabList->a[i].pTab;
    assert( pTab!=0 );
    if( pTab->isTransient || pTab->pSelect ) continue;
    pLevel = &pWInfo->a[i];
    sqliteVdbeAddOp(v, OP_Close, pTabList->a[i].iCursor, 0);
    if( pLevel->pIdx!=0 ){
      sqliteVdbeAddOp(v, OP_Close, pLevel->iCur, 0);
    }
  }
#if 0  /* Never reuse a cursor */
  if( pWInfo->pParse->nTab==pWInfo->peakNTab ){
    pWInfo->pParse->nTab = pWInfo->savedNTab;
  }
#endif
  sqliteFree(pWInfo);
  return;
}

Changes to test/memdb.test.

7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
..
49
50
51
52
53
54
55
56







57


58
59
60
61
62
63
64

65
66
67
68
69
70
71
..
73
74
75
76
77
78
79
80
81


82
83
84
85
86
87
88
#    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 script is in-memory database backend.
#
# $Id: memdb.test,v 1.2 2003/04/20 23:45:23 drh Exp $


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

# In the following sequence of tests, compute the MD5 sum of the content
# of a table, make lots of modifications to that table, then do a rollback.
................................................................................
# The following procedure computes a "signature" for table "t3".  If
# T3 changes in any way, the signature should change.  
#
# This is used to test ROLLBACK.  We gather a signature for t3, then
# make lots of changes to t3, then rollback and take another signature.
# The two signatures should be the same.
#
proc signature {} {







  return [db eval {SELECT count(*), md5sum(x) FROM t3}]


}

# Do rollbacks.  Make sure the signature does not change.
#
set limit 10
for {set i 2} {$i<=$limit} {incr i} {
  set ::sig [signature]

  set cnt [lindex $::sig 0]
  set ::journal_format [expr {($i%3)+1}]
  if {$i%2==0} {
    execsql {PRAGMA synchronous=FULL}
  } else {
    execsql {PRAGMA synchronous=NORMAL}
  }
................................................................................
     execsql {
       BEGIN;
       DELETE FROM t3 WHERE random()%10!=0;
       INSERT INTO t3 SELECT randstr(10,10)||x FROM t3;
       INSERT INTO t3 SELECT randstr(10,10)||x FROM t3;
       ROLLBACK;
     }
     signature
  } $sig


  do_test memdb-1.$i.2-$cnt {
     execsql {
       BEGIN;
       DELETE FROM t3 WHERE random()%10!=0;
       INSERT INTO t3 SELECT randstr(10,10)||x FROM t3;
       DELETE FROM t3 WHERE random()%10!=0;
       INSERT INTO t3 SELECT randstr(10,10)||x FROM t3;







|







 







|
>
>
>
>
>
>
>
|
>
>






|
>







 







|

>
>







7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
..
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
..
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
#    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 script is in-memory database backend.
#
# $Id: memdb.test,v 1.3 2003/05/02 14:32:15 drh Exp $


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

# In the following sequence of tests, compute the MD5 sum of the content
# of a table, make lots of modifications to that table, then do a rollback.
................................................................................
# The following procedure computes a "signature" for table "t3".  If
# T3 changes in any way, the signature should change.  
#
# This is used to test ROLLBACK.  We gather a signature for t3, then
# make lots of changes to t3, then rollback and take another signature.
# The two signatures should be the same.
#
proc signature {{fn {}}} {
  set rx [db eval {SELECT x FROM t3}]
  # set r1 [md5 $rx\n]
  if {$fn!=""} {
    # set fd [open $fn w]
    # puts $fd $rx
    # close $fd
  }
  # set r [db eval {SELECT count(*), md5sum(x) FROM t3}]
  # puts "SIG($fn)=$r1"
  return [list [string length $rx] $rx]
}

# Do rollbacks.  Make sure the signature does not change.
#
set limit 10
for {set i 2} {$i<=$limit} {incr i} {
  set ::sig [signature one]
  # puts "sig=$sig"
  set cnt [lindex $::sig 0]
  set ::journal_format [expr {($i%3)+1}]
  if {$i%2==0} {
    execsql {PRAGMA synchronous=FULL}
  } else {
    execsql {PRAGMA synchronous=NORMAL}
  }
................................................................................
     execsql {
       BEGIN;
       DELETE FROM t3 WHERE random()%10!=0;
       INSERT INTO t3 SELECT randstr(10,10)||x FROM t3;
       INSERT INTO t3 SELECT randstr(10,10)||x FROM t3;
       ROLLBACK;
     }
     set sig2 [signature two]
  } $sig
  # puts "sig2=$sig2"
  # if {$sig2!=$sig} exit
  do_test memdb-1.$i.2-$cnt {
     execsql {
       BEGIN;
       DELETE FROM t3 WHERE random()%10!=0;
       INSERT INTO t3 SELECT randstr(10,10)||x FROM t3;
       DELETE FROM t3 WHERE random()%10!=0;
       INSERT INTO t3 SELECT randstr(10,10)||x FROM t3;

Changes to test/view.test.

7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
...
292
293
294
295
296
297
298










299
300
301
302
303
304
305
#    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 VIEW statements.
#
# $Id: view.test,v 1.13 2003/04/24 01:45:05 drh Exp $
set testdir [file dirname $argv0]
source $testdir/tester.tcl

do_test view-1.0 {
  execsql {
    CREATE TABLE t1(a,b,c);
    INSERT INTO t1 VALUES(1,2,3);
................................................................................
  }
} 3
do_test view-8.5 {
  execsql {
    SELECT mx+10, mx*2 FROM v8;
  }
} {13 6}











# Tests for a bug found by Michiel de Wit involving ORDER BY in a VIEW.
#
do_test view-9.1 {
  execsql {
    INSERT INTO t2 SELECT * FROM t2 WHERE a<5;
    INSERT INTO t2 SELECT * FROM t2 WHERE a<4;







|







 







>
>
>
>
>
>
>
>
>
>







7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
...
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
#    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 VIEW statements.
#
# $Id: view.test,v 1.14 2003/05/02 14:32:15 drh Exp $
set testdir [file dirname $argv0]
source $testdir/tester.tcl

do_test view-1.0 {
  execsql {
    CREATE TABLE t1(a,b,c);
    INSERT INTO t1 VALUES(1,2,3);
................................................................................
  }
} 3
do_test view-8.5 {
  execsql {
    SELECT mx+10, mx*2 FROM v8;
  }
} {13 6}
do_test view-8.6 {
  execsql {
    SELECT mx+10, pqr FROM v6, v8 WHERE xyz=2;
  }
} {13 7}
do_test view-8.7 {
  execsql {
    SELECT mx+10, pqr FROM v6, v8 WHERE xyz>2;
  }
} {13 13 13 19 13 27}

# Tests for a bug found by Michiel de Wit involving ORDER BY in a VIEW.
#
do_test view-9.1 {
  execsql {
    INSERT INTO t2 SELECT * FROM t2 WHERE a<5;
    INSERT INTO t2 SELECT * FROM t2 WHERE a<4;