xref: /sqlite-3.40.0/test/insert5.test (revision 85b623f2) 
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.3 2007/12/13 07:58:51 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#
50do_test insert5-1.1 {
51  execsql {
52    INSERT INTO B
53      SELECT * FROM B UNION ALL
54      SELECT * FROM MAIN WHERE exists (select * FROM B WHERE B.Id = MAIN.Id);
55    SELECT * FROM B;
56  }
57} {2 3 2 3 2 3}
58
59do_test insert5-2.1 {
60  uses_temp_table { INSERT INTO b SELECT * FROM main }
61} {0}
62do_test insert5-2.2 {
63  uses_temp_table { INSERT INTO b SELECT * FROM b }
64} {1}
65do_test insert5-2.3 {
66  uses_temp_table { INSERT INTO b SELECT (SELECT id FROM b), id1 FROM main }
67} {1}
68do_test insert5-2.4 {
69  uses_temp_table { INSERT INTO b SELECT id1, (SELECT id FROM b) FROM main }
70} {1}
71do_test insert5-2.5 {
72  uses_temp_table {
73    INSERT INTO b
74      SELECT * FROM main WHERE id = (SELECT id1 FROM b WHERE main.id = b.id) }
75} {1}
76do_test insert5-2.6 {
77  uses_temp_table { INSERT INTO b SELECT * FROM v1 }
78} {1}
79do_test insert5-2.7 {
80  uses_temp_table { INSERT INTO b SELECT * FROM v2 }
81} {0}
82do_test insert5-2.8 {
83  uses_temp_table {
84    INSERT INTO b
85    SELECT * FROM main WHERE id > 10 AND max(id1, (SELECT id FROM b)) > 10;
86  }
87} {1}
88
89# UPDATE: Using a column from the outer query (main.id) in the GROUP BY
90# or ORDER BY of a sub-query is no longer supported.
91#
92# do_test insert5-2.9 {
93#   uses_temp_table {
94#     INSERT INTO b
95#     SELECT * FROM main
96#     WHERE id > 10 AND (SELECT count(*) FROM v2 GROUP BY main.id)
97#   }
98# } {}
99do_test insert5-2.9 {
100  catchsql {
101    INSERT INTO b
102    SELECT * FROM main
103    WHERE id > 10 AND (SELECT count(*) FROM v2 GROUP BY main.id)
104  }
105} {1 {no such column: main.id}}
106
107finish_test
108