gitlab.com/CoiaPrant/sqlite3@v1.19.1/testdata/tcl/insert5.test (about)

     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.5 2008/08/04 03:51:24 danielk1977 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