gitlab.com/CoiaPrant/sqlite3@v1.19.1/testdata/tcl/returning1.test (about) 1 # 2021-01-28 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 the new RETURNING clause 13 # 14 15 set testdir [file dirname $argv0] 16 source $testdir/tester.tcl 17 set testprefix returning1 18 19 do_execsql_test 1.0 { 20 CREATE TABLE t1(a INTEGER PRIMARY KEY,b,c DEFAULT 'pax'); 21 INSERT INTO t1(b) VALUES(10),('happy'),(NULL) RETURNING a,b,c; 22 } {1 10 pax 2 happy pax 3 {} pax} 23 do_execsql_test 1.1 { 24 SELECT * FROM t1; 25 } {1 10 pax 2 happy pax 3 {} pax} 26 do_execsql_test 1.2 { 27 INSERT INTO t1(b,c) VALUES(5,99) RETURNING b,c,a,rowid; 28 } {5 99 4 4} 29 do_execsql_test 1.3 { 30 SELECT * FROM t1; 31 } {1 10 pax 2 happy pax 3 {} pax 4 5 99} 32 do_execsql_test 1.4 { 33 INSERT INTO t1 DEFAULT VALUES RETURNING *; 34 } {5 {} pax} 35 do_execsql_test 1.5 { 36 SELECT * FROM t1; 37 } {1 10 pax 2 happy pax 3 {} pax 4 5 99 5 {} pax} 38 do_execsql_test 1.6 { 39 CREATE TABLE t2(x,y,z); 40 INSERT INTO t2 VALUES(11,12,13),(21,'b','c'),(31,'b-value',4.75); 41 } 42 do_execsql_test 1.7 { 43 INSERT INTO t1 SELECT * FROM t2 RETURNING *; 44 } {11 12 13 21 b c 31 b-value 4.75} 45 do_execsql_test 1.8 { 46 SELECT *, '|' FROM t1; 47 } {1 10 pax | 2 happy pax | 3 {} pax | 4 5 99 | 5 {} pax | 11 12 13 | 21 b c | 31 b-value 4.75 |} 48 49 do_execsql_test 2.1 { 50 UPDATE t1 SET c='bellum' WHERE c='pax' RETURNING rowid, b, '|'; 51 } {1 10 | 2 happy | 3 {} | 5 {} |} 52 do_execsql_test 2.2 { 53 SELECT *, '|' FROM t1; 54 } {1 10 bellum | 2 happy bellum | 3 {} bellum | 4 5 99 | 5 {} bellum | 11 12 13 | 21 b c | 31 b-value 4.75 |} 55 56 do_execsql_test 3.1 { 57 DELETE FROM t1 WHERE c='bellum' RETURNING rowid, *, '|'; 58 } {1 1 10 bellum | 2 2 happy bellum | 3 3 {} bellum | 5 5 {} bellum |} 59 do_execsql_test 3.2 { 60 SELECT *, '|' FROM t1; 61 } {4 5 99 | 11 12 13 | 21 b c | 31 b-value 4.75 |} 62 63 do_execsql_test 4.1 { 64 CREATE TABLE t4(a INT, b INT DEFAULT 1234, c INT DEFAULT -16); 65 CREATE UNIQUE INDEX t4a ON t4(a); 66 INSERT INTO t4(a,b,c) VALUES(1,2,3); 67 } {} 68 do_execsql_test 4.2 { 69 INSERT INTO t4(a,b,c) VALUES(1,22,33) 70 ON CONFLICT(a) DO UPDATE SET b=44 71 RETURNING *; 72 } {1 44 3} 73 do_execsql_test 4.3 { 74 SELECT * FROM t4; 75 } {1 44 3} 76 do_execsql_test 4.4 { 77 DELETE FROM t4; 78 INSERT INTO t4 VALUES(1,2,3),(4,5,6),(7,8,9); 79 } {} 80 do_execsql_test 4.5 { 81 INSERT INTO t4(a,b,c) VALUES(2,3,4),(4,5,6),(5,6,7) 82 ON CONFLICT(a) DO UPDATE SET b=100 83 RETURNING *, '|'; 84 } {2 3 4 | 4 100 6 | 5 6 7 |} 85 86 #------------------------------------------------------------------------- 87 # Test RETURNING on a table with virtual columns. 88 # 89 reset_db 90 do_execsql_test 5.0 { 91 CREATE TABLE t1(xyz); 92 CREATE TABLE t2(a as (1+1), b); 93 } 94 95 do_execsql_test 5.1 { 96 UPDATE t2 SET b='123' WHERE b='abc' RETURNING (SELECT b FROM t1); 97 } {} 98 99 do_execsql_test 5.2 { 100 INSERT INTO t2(b) VALUES('abc'); 101 } 102 103 do_execsql_test 5.3 { 104 UPDATE t2 SET b='123' WHERE b='abc' RETURNING (SELECT b FROM t1); 105 } {{}} 106 107 do_execsql_test 5.4 { 108 INSERT INTO t2(b) VALUES('abc'); 109 INSERT INTO t1(xyz) VALUES(1); 110 UPDATE t2 SET b='123' WHERE b='abc' RETURNING b; 111 } {123} 112 113 do_execsql_test 5.5 { 114 INSERT INTO t2(b) VALUES('abc'); 115 UPDATE t2 SET b='123' WHERE b='abc' RETURNING (SELECT b FROM t1); 116 } {123} 117 118 # Ticket 132994c8b1063bfb 119 reset_db 120 do_catchsql_test 6.0 { 121 CREATE TABLE t1(id INTEGER PRIMARY KEY); 122 CREATE TABLE t2(x INT, y INT); 123 INSERT INTO t1 VALUES(1),(2),(4),(9); 124 INSERT INTO t2 VALUES(3,7), (4,25), (5,99); 125 UPDATE t1 SET id=id+y FROM t2 WHERE t1.id=t2.x RETURNING t2.*; 126 } {1 {RETURNING may not use "TABLE.*" wildcards}} 127 do_catchsql_test 6.1 { 128 UPDATE t1 SET id=id+y FROM t2 WHERE t1.id=t2.x RETURNING *, '|'; 129 SELECT * FROM t1 ORDER BY id; 130 } {0 {29 | 1 2 9 29}} 131 132 # Forum https://sqlite.org/forum/forumpost/85aef8bc01 133 # Do not silently ignore nonsense table names in the RETURNING clause. 134 # Raise an error. 135 # 136 reset_db 137 do_execsql_test 7.1 { 138 CREATE TABLE t1(a INT, b INT); 139 CREATE TABLE t2(x INT, y INT); 140 INSERT INTO t1(a,b) VALUES(1,2); 141 INSERT INTO t2(x,y) VALUES(1,30); 142 } {} 143 do_catchsql_test 7.2 { 144 UPDATE t1 SET b=b+1 RETURNING new.b; 145 } {1 {no such column: new.b}} 146 do_catchsql_test 7.3 { 147 UPDATE t1 SET b=b+1 RETURNING old.b; 148 } {1 {no such column: old.b}} 149 do_catchsql_test 7.4 { 150 UPDATE t1 SET b=b+1 RETURNING another.b; 151 } {1 {no such column: another.b}} 152 do_catchsql_test 7.5 { 153 UPDATE t1 SET b=b+y FROM t2 WHERE t2.x=t1.a RETURNING t2.x; 154 } {1 {no such column: t2.x}} 155 do_catchsql_test 7.6 { 156 UPDATE t1 SET b=b+y FROM t2 WHERE t2.x=t1.a RETURNING t1.b; 157 } {0 32} 158 159 # This is goofy: The RETURNING clause does not honor the alias 160 # for the table being modified. This might change in the future. 161 # 162 do_catchsql_test 7.7 { 163 UPDATE t1 AS alias SET b=123 RETURNING alias.b; 164 } {1 {no such column: alias.b}} 165 do_catchsql_test 7.8 { 166 UPDATE t1 AS alias SET b=alias.b+1000 RETURNING t1.b; 167 } {0 1032} 168 169 # Forum: https://sqlite.org/forum/info/34c81d83c9177f46 170 reset_db 171 do_execsql_test 8.1 { 172 CREATE TABLE t1(a); 173 CREATE TABLE t2(b,c); 174 INSERT INTO t1 VALUES(1); 175 INSERT INTO t2 VALUES(3,40); 176 } {} 177 do_catchsql_test 8.2 { 178 INSERT INTO t1 VALUES(3) RETURNING a, (SELECT c FROM t2 WHERE new.a=t2.b) AS x; 179 } {1 {no such column: new.a}} 180 do_catchsql_test 8.3 { 181 INSERT INTO t1 VALUES(3) RETURNING a, (SELECT c FROM t2 WHERE old.a=t2.b) AS x; 182 } {1 {no such column: old.a}} 183 do_catchsql_test 8.4 { 184 INSERT INTO t1 VALUES(3) RETURNING a, (SELECT c FROM t2 WHERE t1.a=t2.b) AS x; 185 } {0 {3 40}} 186 187 ifcapable vtab { 188 # dbsqlfuzz finds/crash-486f791cbe2dc45839310073e71367a1d8ad22dd 189 do_catchsql_test 9.1 { 190 UPDATE pragma_encoding SET encoding='UTF-8' RETURNING a, b, *; 191 } {1 {table pragma_encoding may not be modified}} 192 } ;# ifcapable vtab 193 194 # dbsqlfuzz crash-0081f863d7b2002045ac2361879fc80dfebb98f1 195 reset_db 196 do_execsql_test 10.1 { 197 CREATE TABLE t1_a(a, b); 198 CREATE VIEW t1 AS SELECT a, b FROM t1_a; 199 200 INSERT INTO t1_a VALUES('x', 'y'); 201 INSERT INTO t1_a VALUES('x', 'y'); 202 INSERT INTO t1_a VALUES('x', 'y'); 203 204 CREATE TABLE log(op, r, a, b); 205 } 206 do_execsql_test 10.2 { 207 CREATE TRIGGER tr1 INSTEAD OF INSERT ON t1 BEGIN 208 INSERT INTO log VALUES('insert', new.rowid, new.a, new.b); 209 END; 210 CREATE TRIGGER tr2 INSTEAD OF UPDATE ON t1 BEGIN 211 INSERT INTO log VALUES('update', new.rowid, new.a, new.b); 212 END; 213 } 214 215 do_catchsql_test 10.3 { 216 INSERT INTO t1(a, b) VALUES(1234, 5678) RETURNING rowid; 217 } {1 {no such column: rowid}} 218 219 do_catchsql_test 10.3 { 220 UPDATE t1 SET a='z' WHERE b='y' RETURNING rowid; 221 } {1 {no such column: rowid}} 222 223 do_execsql_test 10.4 { 224 SELECT * FROM log; 225 } {} 226 227 # 2021-04-27 dbsqlfuzz 78b9400770ef8cc7d9427dfba26f4fcf46ea7dc2 228 # Returning clauses on TEMP tables with triggers. 229 # 230 reset_db 231 do_execsql_test 11.1 { 232 CREATE TEMP TABLE t1(a,b); 233 CREATE TEMP TABLE t2(c,d); 234 CREATE TEMP TABLE t3(e,f); 235 CREATE TEMP TABLE log(op,x,y); 236 CREATE TEMP TRIGGER t1r1 AFTER INSERT ON t1 BEGIN 237 INSERT INTO log(op,x,y) VALUES('I1',new.a,new.b); 238 END; 239 CREATE TEMP TRIGGER t1r2 BEFORE DELETE ON t1 BEGIN 240 INSERT INTO log(op,x,y) VALUES('D1',old.a,old.b); 241 END; 242 CREATE TEMP TRIGGER t2r3 AFTER UPDATE ON t1 BEGIN 243 INSERT INTO log(op,x,y) VALUES('U1',new.a,new.b); 244 END; 245 CREATE TEMP TRIGGER t2r1 BEFORE INSERT ON t2 BEGIN 246 INSERT INTO log(op,x,y) VALUES('I2',new.c,new.d); 247 END; 248 CREATE TEMP TRIGGER t3r1 AFTER DELETE ON t3 BEGIN 249 INSERT INTO log(op,x,y) VALUES('D3',old.e,old.f); 250 END; 251 CREATE TEMP TRIGGER t3r2 BEFORE UPDATE ON t3 BEGIN 252 INSERT INTO log(op,x,y) VALUES('U3',new.e,new.f); 253 END; 254 INSERT INTO t1(a,b) VALUES(1,2),('happy','glad') RETURNING a, b, '|'; 255 } {1 2 | happy glad |} 256 do_execsql_test 11.2 { 257 UPDATE t1 SET b=9 WHERE a=1 RETURNING a, b, 'x'; 258 } {1 9 x} 259 do_execsql_test 11.3 { 260 DELETE FROM t1 WHERE a<>'xray' RETURNING a, b, '@'; 261 } {1 9 @ happy glad @} 262 do_execsql_test 11.4 { 263 SELECT * FROM log; 264 DELETE FROM log; 265 } {I1 1 2 I1 happy glad U1 1 9 D1 1 9 D1 happy glad} 266 do_execsql_test 11.5 { 267 INSERT INTO t2 VALUES('bravo','charlie') RETURNING d, c, 'z'; 268 } {charlie bravo z} 269 do_execsql_test 11.6 { 270 SELECT * FROM log; 271 DELETE FROM log; 272 } {I2 bravo charlie} 273 do_execsql_test 11.7 { 274 INSERT INTO t3(e) VALUES(1),(2),(3) RETURNING 'I', e; 275 UPDATE t3 SET f=e+100 RETURNING 'U', e, f; 276 DELETE FROM t3 WHERE f>100 RETURNING 'D', e, f; 277 } {I 1 I 2 I 3 U 1 101 U 2 102 U 3 103 D 1 101 D 2 102 D 3 103} 278 do_execsql_test 11.6 { 279 SELECT * FROM log; 280 DELETE FROM log; 281 } {U3 1 101 U3 2 102 U3 3 103 D3 1 101 D3 2 102 D3 3 103} 282 283 reset_db 284 do_execsql_test 11.11 { 285 CREATE TEMP TABLE t1(a,b); 286 CREATE TRIGGER r1 BEFORE INSERT ON t1 BEGIN SELECT 1; END; 287 DELETE FROM t1 RETURNING *; 288 DROP TRIGGER r1; 289 INSERT INTO t1 VALUES(5,30); 290 } {} 291 do_execsql_test 11.12 { 292 SELECT * FROM t1; 293 } {5 30} 294 295 # RETURNING column names are dequoted. 296 # https://sqlite.org/forum/forumpost/033daf0b32 297 # 298 reset_db 299 do_test 12.1 { 300 db eval {CREATE TABLE t1(x INT, y INT)} 301 unset -nocomplain cname 302 db eval {INSERT INTO t1(x) VALUES(1) RETURNING "x";} cname {} 303 lsort [array names cname] 304 } {* x} 305 do_test 12.2 { 306 unset -nocomplain cname 307 db eval {INSERT INTO t1(x) VALUES(2) RETURNING [x];} cname {} 308 lsort [array names cname] 309 } {* x} 310 do_test 12.3 { 311 unset -nocomplain cname 312 db eval {INSERT INTO t1(x) VALUES(3) RETURNING x AS [xyz];} cname {} 313 lsort [array names cname] 314 } {* xyz} 315 do_test 12.4 { 316 unset -nocomplain cname 317 db eval {INSERT INTO t1(x,y) VALUES(4,5) RETURNING "x"+"y";} cname {} 318 lsort [array names cname] 319 } {{"x"+"y"} *} 320 321 ifcapable rtree { 322 #------------------------------------------------------------------------- 323 # Based on dbsqlfuzz find crash-ffbba524cac354b2a61bfd677cec9d2a4333f49a 324 reset_db 325 do_execsql_test 13.0 { 326 CREATE VIRTUAL TABLE t1 USING rtree(a, b, c); 327 CREATE TABLE t2(x); 328 } 329 330 do_execsql_test 13.1 { 331 INSERT INTO t1(a,b,c) VALUES(1,2,3) 332 RETURNING (SELECT b FROM t2); 333 } {{}} 334 } ;# end ifcapable rtree 335 336 # 2021-12-01 Forum post https://sqlite.org/forum/forumpost/793beaf322 337 # Need to report foreign key constraint errors prior to RETURNING 338 # 339 reset_db 340 do_execsql_test 14.0 { 341 PRAGMA foreign_keys(1); 342 CREATE TABLE Parent(id INTEGER PRIMARY KEY); 343 CREATE TABLE Child(id INTEGER PRIMARY KEY, parent_id INTEGER REFERENCES Parent(id)); 344 } {} 345 do_catchsql_test 14.1 { 346 INSERT INTO child(parent_id) VALUES(123) RETURNING id; 347 } {1 {FOREIGN KEY constraint failed}} 348 349 # 2021-12-28 Forum post https://sqlite.org/forum/forumpost/e0c7574ab2 350 # Incorrect affinity for REAL values that can be represented as integers. 351 # 352 reset_db 353 sqlite3_test_control SQLITE_TESTCTRL_INTERNAL_FUNCTIONS db 354 do_execsql_test 15.0 { 355 CREATE TABLE t1(x REAL); 356 INSERT INTO t1(x) VALUES(5.0) RETURNING x, affinity(x); 357 } {5.0 real} 358 do_execsql_test 15.1 { 359 UPDATE t1 SET x=x+1 RETURNING x, affinity(x); 360 } {6.0 real} 361 do_execsql_test 15.2 { 362 DELETE FROM t1 RETURNING x, affinity(x); 363 } {6.0 real} 364 365 # 2022-02-28 Forum post https://sqlite.org/forum/forumpost/595e132f71 366 # RETURNING with the xfer optimization 367 # 368 reset_db 369 do_execsql_test 16.0 { 370 CREATE TABLE t1(a,b,c); 371 INSERT INTO t1 VALUES(1,2,3),('a','b','c'); 372 CREATE TEMP TABLE t2(x,y,z); 373 INSERT INTO t2 SELECT * FROM t1 RETURNING *; 374 } {1 2 3 a b c} 375 do_execsql_test 16.1 { 376 SELECT * FROM t2; 377 } {1 2 3 a b c} 378 379 finish_test