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 17set testdir [file dirname $argv0] 18source $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# 25proc uses_temp_table {sql} { 26 return [expr {[lsearch [execsql "EXPLAIN $sql"] OpenEphemeral]>=0}] 27} 28 29# Construct the sample database. 30# 31do_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# 45do_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 54do_test insert5-2.1 { 55 uses_temp_table { INSERT INTO b SELECT * FROM main } 56} {0} 57do_test insert5-2.2 { 58 uses_temp_table { INSERT INTO b SELECT * FROM b } 59} {1} 60do_test insert5-2.3 { 61 uses_temp_table { INSERT INTO b SELECT (SELECT id FROM b), id1 FROM main } 62} {1} 63do_test insert5-2.4 { 64 uses_temp_table { INSERT INTO b SELECT id1, (SELECT id FROM b) FROM main } 65} {1} 66do_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} 71do_test insert5-2.6 { 72 uses_temp_table { INSERT INTO b SELECT * FROM v1 } 73} {1} 74do_test insert5-2.7 { 75 uses_temp_table { INSERT INTO b SELECT * FROM v2 } 76} {0} 77do_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} 83do_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 91finish_test 92