AryaWu/sqlite
0
1# 2021-01-282#3# The author disclaims copyright to this source code. In place of4# 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. The12# focus of this file is the new RETURNING clause13#14 15set testdir [file dirname $argv0]16source $testdir/tester.tcl17set testprefix returning118 19do_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}23do_execsql_test 1.1 {24 SELECT * FROM t1;25} {1 10 pax 2 happy pax 3 {} pax}26do_execsql_test 1.2 {27 INSERT INTO t1(b,c) VALUES(5,99) RETURNING b,c,a,rowid;28} {5 99 4 4}29do_execsql_test 1.3 {30 SELECT * FROM t1;31} {1 10 pax 2 happy pax 3 {} pax 4 5 99}32do_execsql_test 1.4 {33 INSERT INTO t1 DEFAULT VALUES RETURNING *;34} {5 {} pax}35do_execsql_test 1.5 {36 SELECT * FROM t1;37} {1 10 pax 2 happy pax 3 {} pax 4 5 99 5 {} pax}38do_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}42do_execsql_test 1.7 {43 INSERT INTO t1 SELECT * FROM t2 RETURNING *;44} {11 12 13 21 b c 31 b-value 4.75}45do_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 49do_execsql_test 2.1 {50 UPDATE t1 SET c='bellum' WHERE c='pax' RETURNING rowid, b, '|';51} {1 10 | 2 happy | 3 {} | 5 {} |}52do_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 56do_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 |}59do_execsql_test 3.2 {60 SELECT *, '|' FROM t1;61} {4 5 99 | 11 12 13 | 21 b c | 31 b-value 4.75 |}62 63do_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} {}68do_execsql_test 4.2 {69 INSERT INTO t4(a,b,c) VALUES(1,22,33)70 ON CONFLICT(a) DO UPDATE SET b=4471 RETURNING *;72} {1 44 3}73do_execsql_test 4.3 {74 SELECT * FROM t4;75} {1 44 3}76do_execsql_test 4.4 {77 DELETE FROM t4;78 INSERT INTO t4 VALUES(1,2,3),(4,5,6),(7,8,9);79} {}80do_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=10083 RETURNING *, '|';84} {2 3 4 | 4 100 6 | 5 6 7 |}85 86#-------------------------------------------------------------------------87# Test RETURNING on a table with virtual columns.88#89reset_db90do_execsql_test 5.0 {91 CREATE TABLE t1(xyz);92 CREATE TABLE t2(a as (1+1), b);93}94 95do_execsql_test 5.1 {96 UPDATE t2 SET b='123' WHERE b='abc' RETURNING (SELECT b FROM t1);97} {}98 99do_execsql_test 5.2 {100 INSERT INTO t2(b) VALUES('abc');101}102 103do_execsql_test 5.3 {104 UPDATE t2 SET b='123' WHERE b='abc' RETURNING (SELECT b FROM t1);105} {{}}106 107do_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 113do_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 132994c8b1063bfb119reset_db120do_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}}127do_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/85aef8bc01133# Do not silently ignore nonsense table names in the RETURNING clause.134# Raise an error.135#136reset_db137do_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} {}143do_catchsql_test 7.2 {144 UPDATE t1 SET b=b+1 RETURNING new.b;145} {1 {no such column: new.b}}146do_catchsql_test 7.3 {147 UPDATE t1 SET b=b+1 RETURNING old.b;148} {1 {no such column: old.b}}149do_catchsql_test 7.4 {150 UPDATE t1 SET b=b+1 RETURNING another.b;151} {1 {no such column: another.b}}152do_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}}155do_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 alias160# for the table being modified. This might change in the future.161#162do_catchsql_test 7.7 {163 UPDATE t1 AS alias SET b=123 RETURNING alias.b;164} {1 {no such column: alias.b}}165do_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/34c81d83c9177f46170reset_db171do_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} {}177do_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}}180do_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}}183do_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 187ifcapable vtab {188# dbsqlfuzz finds/crash-486f791cbe2dc45839310073e71367a1d8ad22dd189do_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 vtab193 194# dbsqlfuzz crash-0081f863d7b2002045ac2361879fc80dfebb98f1195reset_db196do_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}206do_execsql_test 10.2 {207 CREATE TRIGGER tr1 INSTEAD OF INSERT ON t1 BEGIN208 INSERT INTO log VALUES('insert', new.rowid, new.a, new.b);209 END;210 CREATE TRIGGER tr2 INSTEAD OF UPDATE ON t1 BEGIN211 INSERT INTO log VALUES('update', new.rowid, new.a, new.b);212 END;213}214 215ifcapable !allow_rowid_in_view {216 do_catchsql_test 10.3a {217 INSERT INTO t1(a, b) VALUES(1234, 5678) RETURNING rowid;218 } {1 {no such column: new.rowid}}219 220 do_catchsql_test 10.3b {221 UPDATE t1 SET a='z' WHERE b='y' RETURNING rowid;222 } {1 {no such column: new.rowid}}223 224 do_execsql_test 10.4 {225 SELECT * FROM log;226 } {}227} else {228 # Note: The values returned by the RETURNING clauses of the following229 # two statements are the rowid columns of views. These values are not230 # well defined, so the INSERT returns -1, and the UPDATE returns NULL.231 # These match the values used for new.rowid expressions, but not much 232 # else.233 do_catchsql_test 10.3a {234 INSERT INTO t1(a, b) VALUES(1234, 5678) RETURNING rowid;235 } {0 -1}236 237 do_catchsql_test 10.3b {238 UPDATE t1 SET a='z' WHERE b='y' RETURNING rowid;239 } {0 {{} {} {}}}240 241 do_execsql_test 10.4 {242 SELECT * FROM log;243 } {244 insert -1 1234 5678 update {} z y update {} z y update {} z y245 }246}247 248# 2021-04-27 dbsqlfuzz 78b9400770ef8cc7d9427dfba26f4fcf46ea7dc2249# Returning clauses on TEMP tables with triggers.250#251reset_db252do_execsql_test 11.1 {253 CREATE TEMP TABLE t1(a,b);254 CREATE TEMP TABLE t2(c,d);255 CREATE TEMP TABLE t3(e,f);256 CREATE TEMP TABLE log(op,x,y);257 CREATE TEMP TRIGGER t1r1 AFTER INSERT ON t1 BEGIN258 INSERT INTO log(op,x,y) VALUES('I1',new.a,new.b);259 END;260 CREATE TEMP TRIGGER t1r2 BEFORE DELETE ON t1 BEGIN261 INSERT INTO log(op,x,y) VALUES('D1',old.a,old.b);262 END;263 CREATE TEMP TRIGGER t2r3 AFTER UPDATE ON t1 BEGIN264 INSERT INTO log(op,x,y) VALUES('U1',new.a,new.b);265 END;266 CREATE TEMP TRIGGER t2r1 BEFORE INSERT ON t2 BEGIN267 INSERT INTO log(op,x,y) VALUES('I2',new.c,new.d);268 END;269 CREATE TEMP TRIGGER t3r1 AFTER DELETE ON t3 BEGIN270 INSERT INTO log(op,x,y) VALUES('D3',old.e,old.f);271 END;272 CREATE TEMP TRIGGER t3r2 BEFORE UPDATE ON t3 BEGIN273 INSERT INTO log(op,x,y) VALUES('U3',new.e,new.f);274 END;275 INSERT INTO t1(a,b) VALUES(1,2),('happy','glad') RETURNING a, b, '|';276} {1 2 | happy glad |}277do_execsql_test 11.2 {278 UPDATE t1 SET b=9 WHERE a=1 RETURNING a, b, 'x';279} {1 9 x}280do_execsql_test 11.3 {281 DELETE FROM t1 WHERE a<>'xray' RETURNING a, b, '@';282} {1 9 @ happy glad @}283do_execsql_test 11.4 {284 SELECT * FROM log;285 DELETE FROM log;286} {I1 1 2 I1 happy glad U1 1 9 D1 1 9 D1 happy glad}287do_execsql_test 11.5 {288 INSERT INTO t2 VALUES('bravo','charlie') RETURNING d, c, 'z';289} {charlie bravo z}290do_execsql_test 11.6 {291 SELECT * FROM log;292 DELETE FROM log;293} {I2 bravo charlie}294do_execsql_test 11.7 {295 INSERT INTO t3(e) VALUES(1),(2),(3) RETURNING 'I', e;296 UPDATE t3 SET f=e+100 RETURNING 'U', e, f;297 DELETE FROM t3 WHERE f>100 RETURNING 'D', e, f;298} {I 1 I 2 I 3 U 1 101 U 2 102 U 3 103 D 1 101 D 2 102 D 3 103}299do_execsql_test 11.6 {300 SELECT * FROM log;301 DELETE FROM log;302} {U3 1 101 U3 2 102 U3 3 103 D3 1 101 D3 2 102 D3 3 103}303 304reset_db305do_execsql_test 11.11 {306 CREATE TEMP TABLE t1(a,b);307 CREATE TRIGGER r1 BEFORE INSERT ON t1 BEGIN SELECT 1; END;308 DELETE FROM t1 RETURNING *;309 DROP TRIGGER r1;310 INSERT INTO t1 VALUES(5,30);311} {}312do_execsql_test 11.12 {313 SELECT * FROM t1;314} {5 30}315 316# RETURNING column names are dequoted.317# https://sqlite.org/forum/forumpost/033daf0b32318#319reset_db320do_test 12.1 {321 db eval {CREATE TABLE t1(x INT, y INT)}322 unset -nocomplain cname323 db eval {INSERT INTO t1(x) VALUES(1) RETURNING "x";} cname {}324 lsort [array names cname]325} {* x}326do_test 12.2 {327 unset -nocomplain cname328 db eval {INSERT INTO t1(x) VALUES(2) RETURNING [x];} cname {}329 lsort [array names cname]330} {* x} 331do_test 12.3 {332 unset -nocomplain cname333 db eval {INSERT INTO t1(x) VALUES(3) RETURNING x AS [xyz];} cname {}334 lsort [array names cname]335} {* xyz}336do_test 12.4 {337 unset -nocomplain cname338 db eval {INSERT INTO t1(x,y) VALUES(4,5) RETURNING "x"+"y";} cname {}339 lsort [array names cname]340} {{"x"+"y"} *}341 342ifcapable rtree {343#-------------------------------------------------------------------------344# Based on dbsqlfuzz find crash-ffbba524cac354b2a61bfd677cec9d2a4333f49a345reset_db346do_execsql_test 13.0 {347 CREATE VIRTUAL TABLE t1 USING rtree(a, b, c);348 CREATE TABLE t2(x);349}350 351do_execsql_test 13.1 {352 INSERT INTO t1(a,b,c) VALUES(1,2,3) 353 RETURNING (SELECT b FROM t2);354} {{}}355} ;# end ifcapable rtree356 357# 2021-12-01 Forum post https://sqlite.org/forum/forumpost/793beaf322358# Need to report foreign key constraint errors prior to RETURNING359#360reset_db361do_execsql_test 14.0 {362 PRAGMA foreign_keys(1);363 CREATE TABLE Parent(id INTEGER PRIMARY KEY);364 CREATE TABLE Child(id INTEGER PRIMARY KEY, parent_id INTEGER REFERENCES Parent(id));365} {}366do_catchsql_test 14.1 {367 INSERT INTO child(parent_id) VALUES(123) RETURNING id;368} {1 {FOREIGN KEY constraint failed}}369 370# 2021-12-28 Forum post https://sqlite.org/forum/forumpost/e0c7574ab2371# Incorrect affinity for REAL values that can be represented as integers.372#373reset_db374sqlite3_test_control SQLITE_TESTCTRL_INTERNAL_FUNCTIONS db375do_execsql_test 15.0 {376 CREATE TABLE t1(x REAL);377 INSERT INTO t1(x) VALUES(5.0) RETURNING x, affinity(x);378} {5.0 real}379do_execsql_test 15.1 {380 UPDATE t1 SET x=x+1 RETURNING x, affinity(x);381} {6.0 real}382do_execsql_test 15.2 {383 DELETE FROM t1 RETURNING x, affinity(x);384} {6.0 real}385 386# 2022-02-28 Forum post https://sqlite.org/forum/forumpost/595e132f71387# RETURNING with the xfer optimization388#389reset_db390do_execsql_test 16.0 {391 CREATE TABLE t1(a,b,c);392 INSERT INTO t1 VALUES(1,2,3),('a','b','c');393 CREATE TEMP TABLE t2(x,y,z);394 INSERT INTO t2 SELECT * FROM t1 RETURNING *;395} {1 2 3 a b c}396do_execsql_test 16.1 {397 SELECT * FROM t2;398} {1 2 3 a b c}399 400 401foreach {tn temp} {402 1 ""403 2 TEMP404} {405 reset_db406 do_execsql_test 17.$tn.0 "407 CREATE $temp TABLE foo (408 fooid INTEGER PRIMARY KEY,409 fooval INTEGER NOT NULL UNIQUE,410 refcnt INTEGER NOT NULL DEFAULT 1411 );412 "413 do_execsql_test 17.$tn.1 {414 INSERT INTO foo (fooval) VALUES (17), (4711), (17)415 ON CONFLICT DO416 UPDATE SET refcnt = refcnt+1417 RETURNING fooid;418 } {419 1 2 1420 }421}422 423# 2022-01-13 https://sqlite.org/forum/forumpost/d010a26798424#425reset_db426do_execsql_test 17.0 {427 CREATE TABLE bug(id INTEGER PRIMARY KEY NOT NULL, x);428 INSERT INTO bug(id,x) VALUES(20, NULL);429 UPDATE bug SET x=NULL WHERE id = 20 RETURNING quote(x), x IS NULL;430} {NULL 1}431 432# 2023-03-08 https://sqlite.org/forum/forumpost/f5a2b1db87433# NULL pointer dereference following an error.434#435do_execsql_test 18.0 {436 CREATE TABLE v0(c1 INT);437 CREATE VIEW view_2(c1) AS SELECT CASE WHEN c1 COLLATE TRUE THEN TRUE ELSE TRUE END FROM v0;438 CREATE TRIGGER x1 INSTEAD OF INSERT ON view_2 BEGIN SELECT true; END;439}440do_catchsql_test 18.1 {441 INSERT INTO view_2 DEFAULT VALUES RETURNING *;442} {1 {no such collation sequence: TRUE}}443 444# 2023-03-16 445# https://sqlite.org/forum/forumpost/c99d6e0329446# ticket d15b3a4ea901ef0d447# ticket 89d259d45b855a0d448#449# A RETURNING clause on an IF NOT EXISTS trigger does not generate450# an error if the trigger already exists.451#452do_execsql_test 19.0 {453 DROP TABLE IF EXISTS t1;CREATE TABLE t1(a);454 CREATE TRIGGER r1 AFTER UPDATE ON t1 BEGIN VALUES(0); END;455} {}456do_catchsql_test 19.1 {457 CREATE TRIGGER IF NOT EXISTS r1 AFTER DELETE ON t1 BEGIN458 INSERT INTO t1(a) VALUES (1) RETURNING FALSE;459 INSERT INTO t1(a) VALUES (2) RETURNING TRUE;460 END;461} {0 {}}462 463# 2024-04-24464# https://sqlite.org/forum/forumpost/2c83569ce8945d39465#466# If the RETURNING clause includes subqueries that reference the467# table being modified, make sure that the subqueries are identified468# as correlated so that the results are recomputed after each step469# instead of being computed once and reused.470#471reset_db472db null N473do_execsql_test 20.1 {474 CREATE TABLE t1(a INTEGER PRIMARY KEY, b INT);475 INSERT INTO t1 VALUES(1,10),(2,20),(3,30),(4,40),(6,60),(8,80);476 BEGIN;477 DELETE FROM t1 WHERE a<>3478 RETURNING a,479 (SELECT min(a) FROM t1),480 (SELECT max(a) FROM t1),481 (SELECT round(avg(a),2) FROM t1);482 ROLLBACK;483} {484 1 2 8 4.6485 2 3 8 5.25486 4 3 8 5.67487 6 3 8 5.5488 8 3 3 3.0489}490do_execsql_test 20.2 {491 BEGIN;492 DELETE FROM t1493 RETURNING a,494 (SELECT min(a) FROM t1),495 (SELECT max(a) FROM t1),496 (SELECT round(avg(a),2) FROM t1);497 ROLLBACK;498} {499 1 2 8 4.6500 2 3 8 5.25501 3 4 8 6.0502 4 6 8 7.0503 6 8 8 8.0504 8 N N N505}506do_execsql_test 20.3 {507 BEGIN;508 DELETE FROM t1509 RETURNING a,510 (SELECT min(t2.a)+t1.a*100 FROM t1 AS t2),511 (SELECT max(t2.a)+t1.a*100 FROM t1 AS t2),512 (SELECT round(avg(t2.a),2)+t1.a*100 FROM t1 AS t2);513 ROLLBACK;514} {515 1 102 108 104.6516 2 203 208 205.25517 3 304 308 306.0518 4 406 408 407.0519 6 608 608 608.0520 8 N N N521}522 523#-------------------------------------------------------------------------524reset_db525do_execsql_test 21.0 {526 PRAGMA writable_schema=ON;527 INSERT INTO sqlite_schema DEFAULT VALUES RETURNING sqlite_schema.name;528} {{}}529 530do_execsql_test 21.1 {531 INSERT INTO sqlite_temp_schema DEFAULT VALUES RETURNING sqlite_temp_schema.name;532} {{}}533 534#-------------------------------------------------------------------------535reset_db 536do_execsql_test 22.0 {537 PRAGMA writable_schema=ON;538 CREATE TABLE xyz (a);539}540do_catchsql_test 22.1 {541 INSERT INTO sqlite_temp_schema DEFAULT VALUES 542 RETURNING543 (SELECT * FROM xyz AS sqlite_master WHERE a=sqlite_master.name);544} {1 {no such column: sqlite_master.name}}545 546 547#-------------------------------------------------------------------------548reset_db 549do_execsql_test 23.0 {550 PRAGMA recursive_triggers = 1;551 CREATE TABLE t1(x, y);552 CREATE TRIGGER t1insert AFTER INSERT ON t1 WHEN new.x<5 BEGIN553 INSERT INTO t1 VALUES(new.x+1, new.y);554 END;555}556 557do_execsql_test 23.1 {558 INSERT INTO t1 VALUES(1, 'one') RETURNING *;559} {1 one}560 561do_execsql_test 23.2 {562 SELECT * FROM t1563} {1 one 2 one 3 one 4 one 5 one}564 565#-------------------------------------------------------------------------566reset_db 567ifcapable fts5 {568 569 do_execsql_test 24.0 {570 CREATE VIRTUAL TABLE ft USING fts5(c);571 CREATE TABLE t1(x);572 INSERT INTO t1 VALUES('x');573 }574 575 db close576 577 sqlite3 db test.db578 sqlite3 db2 test.db579 580 do_execsql_test 24.1 {581 SELECT * FROM t1582 } {x}583 584 do_execsql_test -db db2 24.2 {585 CREATE TABLE t2(y);586 INSERT INTO t2 VALUES('y');587 } {}588 589 db2 close590 591 do_execsql_test 24.3 {592 INSERT INTO ft VALUES('hello world') RETURNING *593 } {{hello world}}594}595 596 597finish_test598