Return to insert5.test CVS log

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

Annotation of embedaddon/sqlite3/test/insert5.test, revision 1.1.1.1

1.1       misho       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