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