gitlab.com/CoiaPrant/sqlite3@v1.19.1/testdata/tcl/insert2.test (about) 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