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 17set testdir [file dirname $argv0] 18source $testdir/tester.tcl 19 20ifcapable !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# 30proc uses_temp_table {sql} { 31 return [expr {[lsearch [execsql "EXPLAIN $sql"] OpenEphemeral]>=0}] 32} 33 34# Construct the sample database. 35# 36do_test insert5-1.0 { 37 file delete -force 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# 50ifcapable 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} 69do_test insert5-2.1 { 70 uses_temp_table { INSERT INTO b SELECT * FROM main } 71} {0} 72do_test insert5-2.2 { 73 uses_temp_table { INSERT INTO b SELECT * FROM b } 74} {1} 75do_test insert5-2.3 { 76 uses_temp_table { INSERT INTO b SELECT (SELECT id FROM b), id1 FROM main } 77} {1} 78do_test insert5-2.4 { 79 uses_temp_table { INSERT INTO b SELECT id1, (SELECT id FROM b) FROM main } 80} {1} 81do_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} 86do_test insert5-2.6 { 87 uses_temp_table { INSERT INTO b SELECT * FROM v1 } 88} {1} 89do_test insert5-2.7 { 90 uses_temp_table { INSERT INTO b SELECT * FROM v2 } 91} {0} 92do_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# } {} 109do_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 117finish_test 118