wasCSharpSQLite – Blame information for rev 7
?pathlinks?
Rev | Author | Line No. | Line |
---|---|---|---|
1 | office | 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 |