1
2#pragma ident	"%Z%%M%	%I%	%E% SMI"
3
4# 2001 September 15
5#
6# The author disclaims copyright to this source code.  In place of
7# a legal notice, here is a blessing:
8#
9#    May you do good and not evil.
10#    May you find forgiveness for yourself and forgive others.
11#    May you share freely, never taking more than you give.
12#
13#***********************************************************************
14# This file implements regression tests for SQLite library.  The
15# focus of this file is testing the INSERT statement that takes is
16# result from a SELECT.
17#
18# $Id: insert2.test,v 1.10 2002/06/25 13:16:04 drh Exp $
19
20set testdir [file dirname $argv0]
21source $testdir/tester.tcl
22
23# Create some tables with data that we can select against
24#
25do_test insert2-1.0 {
26  execsql {CREATE TABLE d1(n int, log int);}
27  for {set i 1} {$i<=20} {incr i} {
28    for {set j 0} {pow(2,$j)<$i} {incr j} {}
29    execsql "INSERT INTO d1 VALUES($i,$j)"
30  }
31  execsql {SELECT * FROM d1 ORDER BY n}
32} {1 0 2 1 3 2 4 2 5 3 6 3 7 3 8 3 9 4 10 4 11 4 12 4 13 4 14 4 15 4 16 4 17 5 18 5 19 5 20 5}
33
34# Insert into a new table from the old one.
35#
36do_test insert2-1.1.1 {
37  execsql {
38    CREATE TABLE t1(log int, cnt int);
39    PRAGMA count_changes=on;
40    INSERT INTO t1 SELECT log, count(*) FROM d1 GROUP BY log;
41  }
42} {6}
43do_test insert2-1.1.2 {
44  db changes
45} {6}
46do_test insert2-1.1.3 {
47  execsql {SELECT * FROM t1 ORDER BY log}
48} {0 1 1 1 2 2 3 4 4 8 5 4}
49
50do_test insert2-1.2.1 {
51  catch {execsql {DROP TABLE t1}}
52  execsql {
53    CREATE TABLE t1(log int, cnt int);
54    INSERT INTO t1
55       SELECT log, count(*) FROM d1 GROUP BY log
56       EXCEPT SELECT n-1,log FROM d1;
57  }
58} {4}
59do_test insert2-1.2.2 {
60  execsql {
61    SELECT * FROM t1 ORDER BY log;
62  }
63} {0 1 3 4 4 8 5 4}
64do_test insert2-1.3.1 {
65  catch {execsql {DROP TABLE t1}}
66  execsql {
67    CREATE TABLE t1(log int, cnt int);
68    PRAGMA count_changes=off;
69    INSERT INTO t1
70       SELECT log, count(*) FROM d1 GROUP BY log
71       INTERSECT SELECT n-1,log FROM d1;
72  }
73} {}
74do_test insert2-1.3.2 {
75  execsql {
76    SELECT * FROM t1 ORDER BY log;
77  }
78} {1 1 2 2}
79do_test insert2-1.4 {
80  catch {execsql {DROP TABLE t1}}
81  set r [execsql {
82    CREATE TABLE t1(log int, cnt int);
83    CREATE INDEX i1 ON t1(log);
84    CREATE INDEX i2 ON t1(cnt);
85    INSERT INTO t1 SELECT log, count() FROM d1 GROUP BY log;
86    SELECT * FROM t1 ORDER BY log;
87  }]
88  lappend r [execsql {SELECT cnt FROM t1 WHERE log=3}]
89  lappend r [execsql {SELECT log FROM t1 WHERE cnt=4 ORDER BY log}]
90} {0 1 1 1 2 2 3 4 4 8 5 4 4 {3 5}}
91
92do_test insert2-2.0 {
93  execsql {
94    CREATE TABLE t3(a,b,c);
95    CREATE TABLE t4(x,y);
96    INSERT INTO t4 VALUES(1,2);
97    SELECT * FROM t4;
98  }
99} {1 2}
100do_test insert2-2.1 {
101  execsql {
102    INSERT INTO t3(a,c) SELECT * FROM t4;
103    SELECT * FROM t3;
104  }
105} {1 {} 2}
106do_test insert2-2.2 {
107  execsql {
108    DELETE FROM t3;
109    INSERT INTO t3(c,b) SELECT * FROM t4;
110    SELECT * FROM t3;
111  }
112} {{} 2 1}
113do_test insert2-2.3 {
114  execsql {
115    DELETE FROM t3;
116    INSERT INTO t3(c,a,b) SELECT x, 'hi', y FROM t4;
117    SELECT * FROM t3;
118  }
119} {hi 2 1}
120
121integrity_check insert2-3.0
122
123# File table t4 with lots of data
124#
125do_test insert2-3.1 {
126  execsql {
127    SELECT * from t4;
128  }
129} {1 2}
130do_test insert2-3.2 {
131  execsql {
132    BEGIN;
133    INSERT INTO t4 VALUES(2,4);
134    INSERT INTO t4 VALUES(3,6);
135    INSERT INTO t4 VALUES(4,8);
136    INSERT INTO t4 VALUES(5,10);
137    INSERT INTO t4 VALUES(6,12);
138    INSERT INTO t4 VALUES(7,14);
139    INSERT INTO t4 VALUES(8,16);
140    INSERT INTO t4 VALUES(9,18);
141    INSERT INTO t4 VALUES(10,20);
142    COMMIT;
143  }
144  db changes
145} {9}
146do_test insert2-3.2.1 {
147  execsql {
148    SELECT count(*) FROM t4;
149  }
150} {10}
151do_test insert2-3.3 {
152  execsql {
153    BEGIN;
154    INSERT INTO t4 SELECT x+(SELECT max(x) FROM t4),y FROM t4;
155    INSERT INTO t4 SELECT x+(SELECT max(x) FROM t4),y FROM t4;
156    INSERT INTO t4 SELECT x+(SELECT max(x) FROM t4),y FROM t4;
157    INSERT INTO t4 SELECT x+(SELECT max(x) FROM t4),y FROM t4;
158    COMMIT;
159    SELECT count(*) FROM t4;
160  }
161} {160}
162do_test insert2-3.4 {
163  execsql {
164    BEGIN;
165    UPDATE t4 SET y='lots of data for the row where x=' || x
166                     || ' and y=' || y || ' - even more data to fill space';
167    COMMIT;
168    SELECT count(*) FROM t4;
169  }
170} {160}
171do_test insert2-3.5 {
172  execsql {
173    BEGIN;
174    INSERT INTO t4 SELECT x+(SELECT max(x)+1 FROM t4),y FROM t4;
175    SELECT count(*) from t4;
176    ROLLBACK;
177  }
178} {320}
179do_test insert2-3.6 {
180  execsql {
181    SELECT count(*) FROM t4;
182  }
183} {160}
184do_test insert2-3.7 {
185  execsql {
186    BEGIN;
187    DELETE FROM t4 WHERE x!=123;
188    SELECT count(*) FROM t4;
189    ROLLBACK;
190  }
191} {1}
192do_test insert2-3.8 {
193  db changes
194} {159}
195integrity_check insert2-3.9
196
197finish_test
198