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 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 # 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 118
