Return to insert5.test CVS log

Up to [ELWIX - Embedded LightWeight unIX -] / embedaddon / sqlite3 / test

sqlite3

    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.1.1 2012/02/21 17:04:16 misho Exp $
   16: 
   17: set testdir [file dirname $argv0]
   18: source $testdir/tester.tcl
   19: 
   20: ifcapable !subquery {
   21:   finish_test
   22:   return
   23: }
   24: 
   25: # Return true if the compilation of the sql passed as an argument 
   26: # includes the opcode OpenEphemeral. An "INSERT INTO ... SELECT"
   27: # statement includes such an opcode if a temp-table is used
   28: # to store intermediate results.
   29: # 
   30: proc uses_temp_table {sql} {
   31:   return [expr {[lsearch [execsql "EXPLAIN $sql"] OpenEphemeral]>=0}]
   32: }
   33: 
   34: # Construct the sample database.
   35: #
   36: do_test insert5-1.0 {
   37:   forcedelete test2.db test2.db-journal
   38:   execsql {
   39:     CREATE TABLE MAIN(Id INTEGER, Id1 INTEGER); 
   40:     CREATE TABLE B(Id INTEGER, Id1 INTEGER); 
   41:     CREATE VIEW v1 AS SELECT * FROM B;
   42:     CREATE VIEW v2 AS SELECT * FROM MAIN;
   43:     INSERT INTO MAIN(Id,Id1) VALUES(2,3); 
   44:     INSERT INTO B(Id,Id1) VALUES(2,3); 
   45:   }
   46: } {}
   47: 
   48: # Run the query.
   49: #
   50: ifcapable compound {
   51:   do_test insert5-1.1 {
   52:     execsql {
   53:       INSERT INTO B 
   54:         SELECT * FROM B UNION ALL 
   55:         SELECT * FROM MAIN WHERE exists (select * FROM B WHERE B.Id = MAIN.Id);
   56:       SELECT * FROM B;
   57:     }
   58:   } {2 3 2 3 2 3}
   59: } else {
   60:   do_test insert5-1.1 {
   61:     execsql {
   62:       INSERT INTO B SELECT * FROM B;
   63:       INSERT INTO B
   64:         SELECT * FROM MAIN WHERE exists (select * FROM B WHERE B.Id = MAIN.Id);
   65:       SELECT * FROM B;
   66:     }
   67:   } {2 3 2 3 2 3}
   68: }
   69: do_test insert5-2.1 {
   70:   uses_temp_table { INSERT INTO b SELECT * FROM main }
   71: } {0}
   72: do_test insert5-2.2 {
   73:   uses_temp_table { INSERT INTO b SELECT * FROM b }
   74: } {1}
   75: do_test insert5-2.3 {
   76:   uses_temp_table { INSERT INTO b SELECT (SELECT id FROM b), id1 FROM main }
   77: } {1}
   78: do_test insert5-2.4 {
   79:   uses_temp_table { INSERT INTO b SELECT id1, (SELECT id FROM b) FROM main }
   80: } {1}
   81: do_test insert5-2.5 {
   82:   uses_temp_table { 
   83:     INSERT INTO b 
   84:       SELECT * FROM main WHERE id = (SELECT id1 FROM b WHERE main.id = b.id) }
   85: } {1}
   86: do_test insert5-2.6 {
   87:   uses_temp_table { INSERT INTO b SELECT * FROM v1 }
   88: } {1}
   89: do_test insert5-2.7 {
   90:   uses_temp_table { INSERT INTO b SELECT * FROM v2 }
   91: } {0}
   92: do_test insert5-2.8 {
   93:   uses_temp_table { 
   94:     INSERT INTO b 
   95:     SELECT * FROM main WHERE id > 10 AND max(id1, (SELECT id FROM b)) > 10;
   96:   }
   97: } {1}
   98: 
   99: # UPDATE: Using a column from the outer query (main.id) in the GROUP BY
  100: # or ORDER BY of a sub-query is no longer supported.
  101: #
  102: # do_test insert5-2.9 {
  103: #   uses_temp_table { 
  104: #     INSERT INTO b 
  105: #     SELECT * FROM main 
  106: #     WHERE id > 10 AND (SELECT count(*) FROM v2 GROUP BY main.id)
  107: #   }
  108: # } {}
  109: do_test insert5-2.9 {
  110:   catchsql { 
  111:     INSERT INTO b 
  112:     SELECT * FROM main 
  113:     WHERE id > 10 AND (SELECT count(*) FROM v2 GROUP BY main.id)
  114:   }
  115: } {1 {no such column: main.id}}
  116: 
  117: finish_test