/ Check-in [bbca16f8]
Login

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

Overview
Comment:Fix for ticket #105: Fix the UPDATE command so that it works properly with indexed tables when there is a subquery in the WHERE clause. Add tests to verify correct operation. (CVS 680)
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: bbca16f88d00cd33ac7229edf3ee4623eff6e62f
User & Date: drh 2002-07-16 17:22:51
Context
2002-07-18
00:34
Fix for ticket #107: Fix a design defect in indices that was causing queries to fail when using an index on a column containing an empty string. This fix is an incompatible file-format change. (CVS 681) check-in: 20d152fc user: drh tags: trunk
2002-07-16
17:22
Fix for ticket #105: Fix the UPDATE command so that it works properly with indexed tables when there is a subquery in the WHERE clause. Add tests to verify correct operation. (CVS 680) check-in: bbca16f8 user: drh tags: trunk
02:05
Fix for ticket #100: Correctly handle ON and USING clauses of JOINs within a VIEW. (CVS 679) check-in: 93710f7e user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/update.c.

8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
..
93
94
95
96
97
98
99










100
101
102
103
104
105
106
107
108
109
110
111
112
...
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
**    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.47 2002/07/05 21:42:37 drh Exp $
*/
#include "sqliteInt.h"

/*
** Process an UPDATE statement.
*/
void sqliteUpdate(
................................................................................
  for(i=0; i<pTab->nCol; i++) aXRef[i] = -1;

  /* If there are FOR EACH ROW triggers, allocate temp tables */
  if( row_triggers_exist ){
    newIdx = pParse->nTab++;
    oldIdx = pParse->nTab++;
  }











  /* Resolve the column names in all the expressions in both the
  ** WHERE clause and in the new values.  Also find the column index
  ** for each column to be updated in the pChanges array.
  */
  base = pParse->nTab++;
  if( pWhere ){
    if( sqliteExprResolveIds(pParse, base, pTabList, 0, pWhere) ){
      goto update_cleanup;
    }
    if( sqliteExprCheck(pParse, pWhere, 0, 0) ){
      goto update_cleanup;
    }
................................................................................
        break;
      }
    }
  }
  for(i=0, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, i++){
    if( openAll || aIdxUsed[i] ){
      sqliteVdbeAddOp(v, openOp, base+i+1, pIdx->tnum);
      assert( pParse->nTab==base+i+1 );
    }
    pParse->nTab++;
  }

  /* 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.







|







 







>
>
>
>
>
>
>
>
>
>





<







 







|

<







8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
..
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
...
275
276
277
278
279
280
281
282
283

284
285
286
287
288
289
290
**    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.48 2002/07/16 17:22:51 drh Exp $
*/
#include "sqliteInt.h"

/*
** Process an UPDATE statement.
*/
void sqliteUpdate(
................................................................................
  for(i=0; i<pTab->nCol; i++) aXRef[i] = -1;

  /* If there are FOR EACH ROW triggers, allocate temp tables */
  if( row_triggers_exist ){
    newIdx = pParse->nTab++;
    oldIdx = pParse->nTab++;
  }

  /* 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 in both the
  ** WHERE clause and in the new values.  Also find the column index
  ** for each column to be updated in the pChanges array.
  */

  if( pWhere ){
    if( sqliteExprResolveIds(pParse, base, pTabList, 0, pWhere) ){
      goto update_cleanup;
    }
    if( sqliteExprCheck(pParse, pWhere, 0, 0) ){
      goto update_cleanup;
    }
................................................................................
        break;
      }
    }
  }
  for(i=0, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, i++){
    if( openAll || aIdxUsed[i] ){
      sqliteVdbeAddOp(v, openOp, 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.

Changes to test/update.test.

7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
...
475
476
477
478
479
480
481
482















483
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is testing the UPDATE statement.
#
# $Id: update.test,v 1.9 2002/05/21 12:56:44 drh Exp $

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

# Try to update an non-existent table
#
do_test update-1.1 {
................................................................................
  }
} {1 {constraint failed}}
do_test update-10.10 {
  catchsql {
    SELECT * FROM t1;
  }
} {0 {1 2 3 4 13 6 2 3 4 4 6 7}}
















finish_test







|







 








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

7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
...
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is testing the UPDATE statement.
#
# $Id: update.test,v 1.10 2002/07/16 17:22:51 drh Exp $

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

# Try to update an non-existent table
#
do_test update-1.1 {
................................................................................
  }
} {1 {constraint failed}}
do_test update-10.10 {
  catchsql {
    SELECT * FROM t1;
  }
} {0 {1 2 3 4 13 6 2 3 4 4 6 7}}

# Make sure we can handle a subquery in the where clause.
#
do_test update-11.1 {
  execsql {
    UPDATE t1 SET e=e+1 WHERE b IN (SELECT b FROM t1);
    SELECT b,e FROM t1;
  }
} {2 14 3 7}
do_test update-11.2 {
  execsql {
    UPDATE t1 SET e=e+1 WHERE a IN (SELECT a FROM t1);
    SELECT a,e FROM t1;
  }
} {1 15 2 8}

finish_test