1: # 2001 September 15
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: # This file implements regression tests for SQLite library. The
12: # focus of this file is testing the INSERT statement that takes is
13: # result from a SELECT.
14: #
15: # $Id: insert2.test,v 1.1.1.1 2012/02/21 17:04:16 misho Exp $
16:
17: set testdir [file dirname $argv0]
18: source $testdir/tester.tcl
19:
20: # Create some tables with data that we can select against
21: #
22: do_test insert2-1.0 {
23: execsql {CREATE TABLE d1(n int, log int);}
24: for {set i 1} {$i<=20} {incr i} {
25: for {set j 0} {(1<<$j)<$i} {incr j} {}
26: execsql "INSERT INTO d1 VALUES($i,$j)"
27: }
28: execsql {SELECT * FROM d1 ORDER BY n}
29: } {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}
30:
31: # Insert into a new table from the old one.
32: #
33: do_test insert2-1.1.1 {
34: execsql {
35: CREATE TABLE t1(log int, cnt int);
36: PRAGMA count_changes=on;
37: }
38: ifcapable explain {
39: execsql {
40: EXPLAIN INSERT INTO t1 SELECT log, count(*) FROM d1 GROUP BY log;
41: }
42: }
43: execsql {
44: INSERT INTO t1 SELECT log, count(*) FROM d1 GROUP BY log;
45: }
46: } {6}
47: do_test insert2-1.1.2 {
48: db changes
49: } {6}
50: do_test insert2-1.1.3 {
51: execsql {SELECT * FROM t1 ORDER BY log}
52: } {0 1 1 1 2 2 3 4 4 8 5 4}
53:
54: ifcapable compound {
55: do_test insert2-1.2.1 {
56: catch {execsql {DROP TABLE t1}}
57: execsql {
58: CREATE TABLE t1(log int, cnt int);
59: INSERT INTO t1
60: SELECT log, count(*) FROM d1 GROUP BY log
61: EXCEPT SELECT n-1,log FROM d1;
62: }
63: } {4}
64: do_test insert2-1.2.2 {
65: execsql {
66: SELECT * FROM t1 ORDER BY log;
67: }
68: } {0 1 3 4 4 8 5 4}
69: do_test insert2-1.3.1 {
70: catch {execsql {DROP TABLE t1}}
71: execsql {
72: CREATE TABLE t1(log int, cnt int);
73: PRAGMA count_changes=off;
74: INSERT INTO t1
75: SELECT log, count(*) FROM d1 GROUP BY log
76: INTERSECT SELECT n-1,log FROM d1;
77: }
78: } {}
79: do_test insert2-1.3.2 {
80: execsql {
81: SELECT * FROM t1 ORDER BY log;
82: }
83: } {1 1 2 2}
84: } ;# ifcapable compound
85: execsql {PRAGMA count_changes=off;}
86:
87: do_test insert2-1.4 {
88: catch {execsql {DROP TABLE t1}}
89: set r [execsql {
90: CREATE TABLE t1(log int, cnt int);
91: CREATE INDEX i1 ON t1(log);
92: CREATE INDEX i2 ON t1(cnt);
93: INSERT INTO t1 SELECT log, count() FROM d1 GROUP BY log;
94: SELECT * FROM t1 ORDER BY log;
95: }]
96: lappend r [execsql {SELECT cnt FROM t1 WHERE log=3}]
97: lappend r [execsql {SELECT log FROM t1 WHERE cnt=4 ORDER BY log}]
98: } {0 1 1 1 2 2 3 4 4 8 5 4 4 {3 5}}
99:
100: do_test insert2-2.0 {
101: execsql {
102: CREATE TABLE t3(a,b,c);
103: CREATE TABLE t4(x,y);
104: INSERT INTO t4 VALUES(1,2);
105: SELECT * FROM t4;
106: }
107: } {1 2}
108: do_test insert2-2.1 {
109: execsql {
110: INSERT INTO t3(a,c) SELECT * FROM t4;
111: SELECT * FROM t3;
112: }
113: } {1 {} 2}
114: do_test insert2-2.2 {
115: execsql {
116: DELETE FROM t3;
117: INSERT INTO t3(c,b) SELECT * FROM t4;
118: SELECT * FROM t3;
119: }
120: } {{} 2 1}
121: do_test insert2-2.3 {
122: execsql {
123: DELETE FROM t3;
124: INSERT INTO t3(c,a,b) SELECT x, 'hi', y FROM t4;
125: SELECT * FROM t3;
126: }
127: } {hi 2 1}
128:
129: integrity_check insert2-3.0
130:
131: # File table t4 with lots of data
132: #
133: do_test insert2-3.1 {
134: execsql {
135: SELECT * from t4;
136: }
137: } {1 2}
138: do_test insert2-3.2 {
139: set x [db total_changes]
140: execsql {
141: BEGIN;
142: INSERT INTO t4 VALUES(2,4);
143: INSERT INTO t4 VALUES(3,6);
144: INSERT INTO t4 VALUES(4,8);
145: INSERT INTO t4 VALUES(5,10);
146: INSERT INTO t4 VALUES(6,12);
147: INSERT INTO t4 VALUES(7,14);
148: INSERT INTO t4 VALUES(8,16);
149: INSERT INTO t4 VALUES(9,18);
150: INSERT INTO t4 VALUES(10,20);
151: COMMIT;
152: }
153: expr [db total_changes] - $x
154: } {9}
155: do_test insert2-3.2.1 {
156: execsql {
157: SELECT count(*) FROM t4;
158: }
159: } {10}
160: do_test insert2-3.3 {
161: ifcapable subquery {
162: execsql {
163: BEGIN;
164: INSERT INTO t4 SELECT x+(SELECT max(x) FROM t4),y FROM t4;
165: INSERT INTO t4 SELECT x+(SELECT max(x) FROM t4),y FROM t4;
166: INSERT INTO t4 SELECT x+(SELECT max(x) FROM t4),y FROM t4;
167: INSERT INTO t4 SELECT x+(SELECT max(x) FROM t4),y FROM t4;
168: COMMIT;
169: SELECT count(*) FROM t4;
170: }
171: } else {
172: db function max_x_t4 {execsql {SELECT max(x) FROM t4}}
173: execsql {
174: BEGIN;
175: INSERT INTO t4 SELECT x+max_x_t4() ,y FROM t4;
176: INSERT INTO t4 SELECT x+max_x_t4() ,y FROM t4;
177: INSERT INTO t4 SELECT x+max_x_t4() ,y FROM t4;
178: INSERT INTO t4 SELECT x+max_x_t4() ,y FROM t4;
179: COMMIT;
180: SELECT count(*) FROM t4;
181: }
182: }
183: } {160}
184: do_test insert2-3.4 {
185: execsql {
186: BEGIN;
187: UPDATE t4 SET y='lots of data for the row where x=' || x
188: || ' and y=' || y || ' - even more data to fill space';
189: COMMIT;
190: SELECT count(*) FROM t4;
191: }
192: } {160}
193: do_test insert2-3.5 {
194: ifcapable subquery {
195: execsql {
196: BEGIN;
197: INSERT INTO t4 SELECT x+(SELECT max(x)+1 FROM t4),y FROM t4;
198: SELECT count(*) from t4;
199: ROLLBACK;
200: }
201: } else {
202: execsql {
203: BEGIN;
204: INSERT INTO t4 SELECT x+max_x_t4()+1,y FROM t4;
205: SELECT count(*) from t4;
206: ROLLBACK;
207: }
208: }
209: } {320}
210: do_test insert2-3.6 {
211: execsql {
212: SELECT count(*) FROM t4;
213: }
214: } {160}
215: do_test insert2-3.7 {
216: execsql {
217: BEGIN;
218: DELETE FROM t4 WHERE x!=123;
219: SELECT count(*) FROM t4;
220: ROLLBACK;
221: }
222: } {1}
223: do_test insert2-3.8 {
224: db changes
225: } {159}
226: integrity_check insert2-3.9
227:
228: # Ticket #901
229: #
230: ifcapable tempdb {
231: do_test insert2-4.1 {
232: execsql {
233: CREATE TABLE Dependencies(depId integer primary key,
234: class integer, name str, flag str);
235: CREATE TEMPORARY TABLE DepCheck(troveId INT, depNum INT,
236: flagCount INT, isProvides BOOL, class INTEGER, name STRING,
237: flag STRING);
238: INSERT INTO DepCheck
239: VALUES(-1, 0, 1, 0, 2, 'libc.so.6', 'GLIBC_2.0');
240: INSERT INTO Dependencies
241: SELECT DISTINCT
242: NULL,
243: DepCheck.class,
244: DepCheck.name,
245: DepCheck.flag
246: FROM DepCheck LEFT OUTER JOIN Dependencies ON
247: DepCheck.class == Dependencies.class AND
248: DepCheck.name == Dependencies.name AND
249: DepCheck.flag == Dependencies.flag
250: WHERE
251: Dependencies.depId is NULL;
252: };
253: } {}
254: }
255:
256: #--------------------------------------------------------------------
257: # Test that the INSERT works when the SELECT statement (a) references
258: # the table being inserted into and (b) is optimized to use an index
259: # only.
260: do_test insert2-5.1 {
261: execsql {
262: CREATE TABLE t2(a, b);
263: INSERT INTO t2 VALUES(1, 2);
264: CREATE INDEX t2i1 ON t2(a);
265: INSERT INTO t2 SELECT a, 3 FROM t2 WHERE a = 1;
266: SELECT * FROM t2;
267: }
268: } {1 2 1 3}
269: ifcapable subquery {
270: do_test insert2-5.2 {
271: execsql {
272: INSERT INTO t2 SELECT (SELECT a FROM t2), 4;
273: SELECT * FROM t2;
274: }
275: } {1 2 1 3 1 4}
276: }
277:
278: finish_test
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>
![[BACK]](/icons/cvsweb/back.gif){kind=icon}
Return to insert2.test CVS log ![[TXT]](/icons/cvsweb/text.gif){kind=icon}
![[DIR]](/icons/cvsweb/dir.gif){kind=icon}
Up to [ELWIX - Embedded LightWeight unIX -] / embedaddon / sqlite3 / test