SQLite

**    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.193 2007/11/23 15:02:19 drh Exp $
*/
#include "sqliteInt.h"

/*
** Set P3 of the most recently inserted opcode to a column affinity
** string for index pIdx. A column affinity string has one character
** for each column in the table, according to the affinity of the column:

    pTab->zColAff = zColAff;
  }

  sqlite3VdbeChangeP3(v, -1, pTab->zColAff, 0);
}

/*
** Return non-zero if the table pTab in database iDb or any of its indices
** have been opened at any point in the VDBE program beginning at location
** iStartAddr throught the end of the program.  This is used to see if 
** a statement of the form  "INSERT INTO <iDb, pTab> SELECT ..." can 
** run without using temporary table for the results of the SELECT. 


*/
static int readsTable(Vdbe *v, int iStartAddr, int iDb, Table *pTab){
  int i;
  int iEnd = sqlite3VdbeCurrentAddr(v);
  for(i=iStartAddr; i<iEnd; i++){
    VdbeOp *pOp = sqlite3VdbeGetOp(v, i);
    if( pOp->opcode==OP_OpenRead ){
      VdbeOp *pPrior = &pOp[-1];
      int tnum = pOp->p2;
      assert( i>iStartAddr );
      assert( pPrior->opcode==OP_Integer );
      if( pPrior->p1==iDb ){
        Index *pIndex;
        if( tnum==pTab->tnum ){
          return 1;

        }
        for(pIndex=pTab->pIndex; pIndex; pIndex=pIndex->pNext){
          if( tnum==pIndex->tnum ){
            return 1;
          }
        }
      }
    }
    if( pOp->opcode==OP_VOpen && pOp->p3==(const char*)pTab->pVtab ){
      assert( pOp->p3!=0 );
      assert( pOp->p3type==P3_VTAB );
      return 1;
    }
  }
  return 0;
}

#ifndef SQLITE_OMIT_AUTOINCREMENT
/*

    ** should be written into a temporary table.  Set to FALSE if each
    ** row of the SELECT can be written directly into the result table.
    **
    ** A temp table must be used if the table being updated is also one
    ** of the tables being read by the SELECT statement.  Also use a 
    ** temp table in the case of row triggers.
    */
    if( triggers_exist || readsTable(v, iSelectLoop, iDb, pTab) ){
      useTempTable = 1;
    }

    if( useTempTable ){
      /* Generate the subroutine that SELECT calls to process each row of
      ** the result.  Store the result in a temporary table
      */

    /* This is the case if the data for the INSERT is coming from a VALUES
    ** clause
    */
    NameContext sNC;
    memset(&sNC, 0, sizeof(sNC));
    sNC.pParse = pParse;
    srcTab = -1;
    assert( useTempTable==0 );
    nColumn = pList ? pList->nExpr : 0;
    for(i=0; i<nColumn; i++){
      if( sqlite3ExprResolveNames(&sNC, pList->a[i].pExpr) ){
        goto insert_cleanup;
      }
    }
  }

# 2007 November 23
#
# The author disclaims copyright to this source code.  In place of
# a legal notice, here is a blessing:
#
#    May you do good and not evil.
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#***********************************************************************
#
# The tests in this file ensure that a temporary table is used
# when required by an "INSERT INTO ... SELECT ..." statement.
#
# $Id: insert5.test,v 1.1 2007/11/23 15:02:19 drh Exp $

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

# Return true if the compilation of the sql passed as an argument 
# includes the opcode OpenEphemeral. An "INSERT INTO ... SELECT"
# statement includes such an opcode if a temp-table is used
# to store intermediate results.
# 
proc uses_temp_table {sql} {
  return [expr {[lsearch [execsql "EXPLAIN $sql"] OpenEphemeral]>=0}]
}

# Construct the sample database.
#
do_test insert5-1.0 {
  file delete -force test2.db test2.db-journal
  execsql {
    CREATE TABLE MAIN(Id INTEGER, Id1 INTEGER); 
    CREATE TABLE B(Id INTEGER, Id1 INTEGER); 
    CREATE VIEW v1 AS SELECT * FROM B;
    CREATE VIEW v2 AS SELECT * FROM MAIN;
    INSERT INTO MAIN(Id,Id1) VALUES(2,3); 
    INSERT INTO B(Id,Id1) VALUES(2,3); 
  }
} {}

# Run the query.
#
do_test insert5-1.1 {
  execsql {
    INSERT INTO B 
      SELECT * FROM B UNION ALL 
      SELECT * FROM MAIN WHERE exists (select * FROM B WHERE B.Id = MAIN.Id);
    SELECT * FROM B;
  }
} {2 3 2 3 2 3}

do_test insert5-2.1 {
  uses_temp_table { INSERT INTO b SELECT * FROM main }
} {0}
do_test insert5-2.2 {
  uses_temp_table { INSERT INTO b SELECT * FROM b }
} {1}
do_test insert5-2.3 {
  uses_temp_table { INSERT INTO b SELECT (SELECT id FROM b), id1 FROM main }
} {1}
do_test insert5-2.4 {
  uses_temp_table { INSERT INTO b SELECT id1, (SELECT id FROM b) FROM main }
} {1}
do_test insert5-2.5 {
  uses_temp_table { 
    INSERT INTO b 
      SELECT * FROM main WHERE id = (SELECT id1 FROM b WHERE main.id = b.id) }
} {1}
do_test insert5-2.6 {
  uses_temp_table { INSERT INTO b SELECT * FROM v1 }
} {1}
do_test insert5-2.7 {
  uses_temp_table { INSERT INTO b SELECT * FROM v2 }
} {0}
do_test insert5-2.8 {
  uses_temp_table { 
    INSERT INTO b 
    SELECT * FROM main WHERE id > 10 AND max(id1, (SELECT id FROM b)) > 10;
  }
} {1}
do_test insert5-2.9 {
  uses_temp_table { 
    INSERT INTO b 
    SELECT * FROM main 
    WHERE id > 10 AND (SELECT count(*) FROM v2 GROUP BY main.id)
  }
} {1}

finish_test