/ 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 Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/update.c.

     8      8   **    May you find forgiveness for yourself and forgive others.
     9      9   **    May you share freely, never taking more than you give.
    10     10   **
    11     11   *************************************************************************
    12     12   ** This file contains C code routines that are called by the parser
    13     13   ** to handle UPDATE statements.
    14     14   **
    15         -** $Id: update.c,v 1.47 2002/07/05 21:42:37 drh Exp $
           15  +** $Id: update.c,v 1.48 2002/07/16 17:22:51 drh Exp $
    16     16   */
    17     17   #include "sqliteInt.h"
    18     18   
    19     19   /*
    20     20   ** Process an UPDATE statement.
    21     21   */
    22     22   void sqliteUpdate(
................................................................................
    93     93     for(i=0; i<pTab->nCol; i++) aXRef[i] = -1;
    94     94   
    95     95     /* If there are FOR EACH ROW triggers, allocate temp tables */
    96     96     if( row_triggers_exist ){
    97     97       newIdx = pParse->nTab++;
    98     98       oldIdx = pParse->nTab++;
    99     99     }
          100  +
          101  +  /* Allocate a cursors for the main database table and for all indices.
          102  +  ** The index cursors might not be used, but if they are used they
          103  +  ** need to occur right after the database cursor.  So go ahead and
          104  +  ** allocate enough space, just in case.
          105  +  */
          106  +  base = pParse->nTab++;
          107  +  for(pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext){
          108  +    pParse->nTab++;
          109  +  }
   100    110   
   101    111     /* Resolve the column names in all the expressions in both the
   102    112     ** WHERE clause and in the new values.  Also find the column index
   103    113     ** for each column to be updated in the pChanges array.
   104    114     */
   105         -  base = pParse->nTab++;
   106    115     if( pWhere ){
   107    116       if( sqliteExprResolveIds(pParse, base, pTabList, 0, pWhere) ){
   108    117         goto update_cleanup;
   109    118       }
   110    119       if( sqliteExprCheck(pParse, pWhere, 0, 0) ){
   111    120         goto update_cleanup;
   112    121       }
................................................................................
   266    275           break;
   267    276         }
   268    277       }
   269    278     }
   270    279     for(i=0, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, i++){
   271    280       if( openAll || aIdxUsed[i] ){
   272    281         sqliteVdbeAddOp(v, openOp, base+i+1, pIdx->tnum);
   273         -      assert( pParse->nTab==base+i+1 );
          282  +      assert( pParse->nTab>base+i+1 );
   274    283       }
   275         -    pParse->nTab++;
   276    284     }
   277    285   
   278    286     /* Loop over every record that needs updating.  We have to load
   279    287     ** the old data for each record to be updated because some columns
   280    288     ** might not change and we will need to copy the old value.
   281    289     ** Also, the old data is needed to delete the old index entires.
   282    290     ** So make the cursor point at the old record.

Changes to test/update.test.

     7      7   #    May you find forgiveness for yourself and forgive others.
     8      8   #    May you share freely, never taking more than you give.
     9      9   #
    10     10   #***********************************************************************
    11     11   # This file implements regression tests for SQLite library.  The
    12     12   # focus of this file is testing the UPDATE statement.
    13     13   #
    14         -# $Id: update.test,v 1.9 2002/05/21 12:56:44 drh Exp $
           14  +# $Id: update.test,v 1.10 2002/07/16 17:22:51 drh Exp $
    15     15   
    16     16   set testdir [file dirname $argv0]
    17     17   source $testdir/tester.tcl
    18     18   
    19     19   # Try to update an non-existent table
    20     20   #
    21     21   do_test update-1.1 {
................................................................................
   475    475     }
   476    476   } {1 {constraint failed}}
   477    477   do_test update-10.10 {
   478    478     catchsql {
   479    479       SELECT * FROM t1;
   480    480     }
   481    481   } {0 {1 2 3 4 13 6 2 3 4 4 6 7}}
          482  +
          483  +# Make sure we can handle a subquery in the where clause.
          484  +#
          485  +do_test update-11.1 {
          486  +  execsql {
          487  +    UPDATE t1 SET e=e+1 WHERE b IN (SELECT b FROM t1);
          488  +    SELECT b,e FROM t1;
          489  +  }
          490  +} {2 14 3 7}
          491  +do_test update-11.2 {
          492  +  execsql {
          493  +    UPDATE t1 SET e=e+1 WHERE a IN (SELECT a FROM t1);
          494  +    SELECT a,e FROM t1;
          495  +  }
          496  +} {1 15 2 8}
   482    497   
   483    498   finish_test