/ Check-in [492b39b6]
Login

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

Overview
Comment:Make sure that INSERT INTO ... SELECT ... always uses an ephemeral intermediate table if the SELECT clause refers to the destination table, even if the SELECT clause is compound or uses the destination table in a subquery. This fixes a long-standing bug that can cause an infinite loop for some SQL statements. (CVS 4552)
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 492b39b6a8bf4ad8792d7a7949f77827a5047fd8
User & Date: drh 2007-11-23 15:02:19
Context
2007-11-23
15:12
Remove most documentation from the source code repository. Documentation configuration management is now handled by a separate repository at http://www.sqlite.org/docsrc. (CVS 4553) check-in: b20f3a6f user: drh tags: trunk
15:02
Make sure that INSERT INTO ... SELECT ... always uses an ephemeral intermediate table if the SELECT clause refers to the destination table, even if the SELECT clause is compound or uses the destination table in a subquery. This fixes a long-standing bug that can cause an infinite loop for some SQL statements. (CVS 4552) check-in: 492b39b6 user: drh tags: trunk
13:42
Avoid a double-free in an out-of-memory situation with a USING clause or NATURAL JOIN. Ticket #2789. (CVS 4551) check-in: 59669475 user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/insert.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 INSERT statements in SQLite.
    14     14   **
    15         -** $Id: insert.c,v 1.192 2007/09/03 17:30:07 danielk1977 Exp $
           15  +** $Id: insert.c,v 1.193 2007/11/23 15:02:19 drh Exp $
    16     16   */
    17     17   #include "sqliteInt.h"
    18     18   
    19     19   /*
    20     20   ** Set P3 of the most recently inserted opcode to a column affinity
    21     21   ** string for index pIdx. A column affinity string has one character
    22     22   ** for each column in the table, according to the affinity of the column:
................................................................................
    95     95       pTab->zColAff = zColAff;
    96     96     }
    97     97   
    98     98     sqlite3VdbeChangeP3(v, -1, pTab->zColAff, 0);
    99     99   }
   100    100   
   101    101   /*
   102         -** Return non-zero if SELECT statement p opens the table with rootpage
   103         -** iTab in database iDb.  This is used to see if a statement of the form 
   104         -** "INSERT INTO <iDb, iTab> SELECT ..." can run without using temporary
   105         -** table for the results of the SELECT. 
   106         -**
   107         -** No checking is done for sub-selects that are part of expressions.
          102  +** Return non-zero if the table pTab in database iDb or any of its indices
          103  +** have been opened at any point in the VDBE program beginning at location
          104  +** iStartAddr throught the end of the program.  This is used to see if 
          105  +** a statement of the form  "INSERT INTO <iDb, pTab> SELECT ..." can 
          106  +** run without using temporary table for the results of the SELECT. 
   108    107   */
   109         -static int selectReadsTable(Select *p, Schema *pSchema, int iTab){
          108  +static int readsTable(Vdbe *v, int iStartAddr, int iDb, Table *pTab){
   110    109     int i;
   111         -  struct SrcList_item *pItem;
   112         -  if( p->pSrc==0 ) return 0;
   113         -  for(i=0, pItem=p->pSrc->a; i<p->pSrc->nSrc; i++, pItem++){
   114         -    if( pItem->pSelect ){
   115         -      if( selectReadsTable(pItem->pSelect, pSchema, iTab) ) return 1;
   116         -    }else{
   117         -      if( pItem->pTab->pSchema==pSchema && pItem->pTab->tnum==iTab ) return 1;
          110  +  int iEnd = sqlite3VdbeCurrentAddr(v);
          111  +  for(i=iStartAddr; i<iEnd; i++){
          112  +    VdbeOp *pOp = sqlite3VdbeGetOp(v, i);
          113  +    if( pOp->opcode==OP_OpenRead ){
          114  +      VdbeOp *pPrior = &pOp[-1];
          115  +      int tnum = pOp->p2;
          116  +      assert( i>iStartAddr );
          117  +      assert( pPrior->opcode==OP_Integer );
          118  +      if( pPrior->p1==iDb ){
          119  +        Index *pIndex;
          120  +        if( tnum==pTab->tnum ){
          121  +          return 1;
          122  +        }
          123  +        for(pIndex=pTab->pIndex; pIndex; pIndex=pIndex->pNext){
          124  +          if( tnum==pIndex->tnum ){
          125  +            return 1;
          126  +          }
          127  +        }
          128  +      }
          129  +    }
          130  +    if( pOp->opcode==OP_VOpen && pOp->p3==(const char*)pTab->pVtab ){
          131  +      assert( pOp->p3!=0 );
          132  +      assert( pOp->p3type==P3_VTAB );
          133  +      return 1;
   118    134       }
   119    135     }
   120    136     return 0;
   121    137   }
   122    138   
   123    139   #ifndef SQLITE_OMIT_AUTOINCREMENT
   124    140   /*
................................................................................
   477    493       ** should be written into a temporary table.  Set to FALSE if each
   478    494       ** row of the SELECT can be written directly into the result table.
   479    495       **
   480    496       ** A temp table must be used if the table being updated is also one
   481    497       ** of the tables being read by the SELECT statement.  Also use a 
   482    498       ** temp table in the case of row triggers.
   483    499       */
   484         -    if( triggers_exist || selectReadsTable(pSelect,pTab->pSchema,pTab->tnum) ){
          500  +    if( triggers_exist || readsTable(v, iSelectLoop, iDb, pTab) ){
   485    501         useTempTable = 1;
   486    502       }
   487    503   
   488    504       if( useTempTable ){
   489    505         /* Generate the subroutine that SELECT calls to process each row of
   490    506         ** the result.  Store the result in a temporary table
   491    507         */
................................................................................
   513    529       /* This is the case if the data for the INSERT is coming from a VALUES
   514    530       ** clause
   515    531       */
   516    532       NameContext sNC;
   517    533       memset(&sNC, 0, sizeof(sNC));
   518    534       sNC.pParse = pParse;
   519    535       srcTab = -1;
   520         -    useTempTable = 0;
          536  +    assert( useTempTable==0 );
   521    537       nColumn = pList ? pList->nExpr : 0;
   522    538       for(i=0; i<nColumn; i++){
   523    539         if( sqlite3ExprResolveNames(&sNC, pList->a[i].pExpr) ){
   524    540           goto insert_cleanup;
   525    541         }
   526    542       }
   527    543     }

Added test/insert5.test.

            1  +# 2007 November 23
            2  +#
            3  +# The author disclaims copyright to this source code.  In place of
            4  +# a legal notice, here is a blessing:
            5  +#
            6  +#    May you do good and not evil.
            7  +#    May you find forgiveness for yourself and forgive others.
            8  +#    May you share freely, never taking more than you give.
            9  +#
           10  +#***********************************************************************
           11  +#
           12  +# The tests in this file ensure that a temporary table is used
           13  +# when required by an "INSERT INTO ... SELECT ..." statement.
           14  +#
           15  +# $Id: insert5.test,v 1.1 2007/11/23 15:02:19 drh Exp $
           16  +
           17  +set testdir [file dirname $argv0]
           18  +source $testdir/tester.tcl
           19  +
           20  +# Return true if the compilation of the sql passed as an argument 
           21  +# includes the opcode OpenEphemeral. An "INSERT INTO ... SELECT"
           22  +# statement includes such an opcode if a temp-table is used
           23  +# to store intermediate results.
           24  +# 
           25  +proc uses_temp_table {sql} {
           26  +  return [expr {[lsearch [execsql "EXPLAIN $sql"] OpenEphemeral]>=0}]
           27  +}
           28  +
           29  +# Construct the sample database.
           30  +#
           31  +do_test insert5-1.0 {
           32  +  file delete -force test2.db test2.db-journal
           33  +  execsql {
           34  +    CREATE TABLE MAIN(Id INTEGER, Id1 INTEGER); 
           35  +    CREATE TABLE B(Id INTEGER, Id1 INTEGER); 
           36  +    CREATE VIEW v1 AS SELECT * FROM B;
           37  +    CREATE VIEW v2 AS SELECT * FROM MAIN;
           38  +    INSERT INTO MAIN(Id,Id1) VALUES(2,3); 
           39  +    INSERT INTO B(Id,Id1) VALUES(2,3); 
           40  +  }
           41  +} {}
           42  +
           43  +# Run the query.
           44  +#
           45  +do_test insert5-1.1 {
           46  +  execsql {
           47  +    INSERT INTO B 
           48  +      SELECT * FROM B UNION ALL 
           49  +      SELECT * FROM MAIN WHERE exists (select * FROM B WHERE B.Id = MAIN.Id);
           50  +    SELECT * FROM B;
           51  +  }
           52  +} {2 3 2 3 2 3}
           53  +
           54  +do_test insert5-2.1 {
           55  +  uses_temp_table { INSERT INTO b SELECT * FROM main }
           56  +} {0}
           57  +do_test insert5-2.2 {
           58  +  uses_temp_table { INSERT INTO b SELECT * FROM b }
           59  +} {1}
           60  +do_test insert5-2.3 {
           61  +  uses_temp_table { INSERT INTO b SELECT (SELECT id FROM b), id1 FROM main }
           62  +} {1}
           63  +do_test insert5-2.4 {
           64  +  uses_temp_table { INSERT INTO b SELECT id1, (SELECT id FROM b) FROM main }
           65  +} {1}
           66  +do_test insert5-2.5 {
           67  +  uses_temp_table { 
           68  +    INSERT INTO b 
           69  +      SELECT * FROM main WHERE id = (SELECT id1 FROM b WHERE main.id = b.id) }
           70  +} {1}
           71  +do_test insert5-2.6 {
           72  +  uses_temp_table { INSERT INTO b SELECT * FROM v1 }
           73  +} {1}
           74  +do_test insert5-2.7 {
           75  +  uses_temp_table { INSERT INTO b SELECT * FROM v2 }
           76  +} {0}
           77  +do_test insert5-2.8 {
           78  +  uses_temp_table { 
           79  +    INSERT INTO b 
           80  +    SELECT * FROM main WHERE id > 10 AND max(id1, (SELECT id FROM b)) > 10;
           81  +  }
           82  +} {1}
           83  +do_test insert5-2.9 {
           84  +  uses_temp_table { 
           85  +    INSERT INTO b 
           86  +    SELECT * FROM main 
           87  +    WHERE id > 10 AND (SELECT count(*) FROM v2 GROUP BY main.id)
           88  +  }
           89  +} {1}
           90  +
           91  +finish_test