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 15set testdir [file dirname $argv0] 16source $testdir/tester.tcl 17set testprefix returning1 18 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=44 71 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=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# 89reset_db 90do_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 132994c8b1063bfb 119reset_db 120do_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/85aef8bc01 133# Do not silently ignore nonsense table names in the RETURNING clause. 134# Raise an error. 135# 136reset_db 137do_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 alias 160# 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/34c81d83c9177f46 170reset_db 171do_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 187# dbsqlfuzz finds/crash-486f791cbe2dc45839310073e71367a1d8ad22dd 188do_catchsql_test 9.1 { 189 UPDATE pragma_encoding SET encoding='UTF-8' RETURNING a, b, *; 190} {1 {table pragma_encoding may not be modified}} 191 192# dbsqlfuzz crash-0081f863d7b2002045ac2361879fc80dfebb98f1 193reset_db 194do_execsql_test 10.1 { 195 CREATE TABLE t1_a(a, b); 196 CREATE VIEW t1 AS SELECT a, b FROM t1_a; 197 198 INSERT INTO t1_a VALUES('x', 'y'); 199 INSERT INTO t1_a VALUES('x', 'y'); 200 INSERT INTO t1_a VALUES('x', 'y'); 201 202 CREATE TABLE log(op, r, a, b); 203} 204do_execsql_test 10.2 { 205 CREATE TRIGGER tr1 INSTEAD OF INSERT ON t1 BEGIN 206 INSERT INTO log VALUES('insert', new.rowid, new.a, new.b); 207 END; 208 CREATE TRIGGER tr2 INSTEAD OF UPDATE ON t1 BEGIN 209 INSERT INTO log VALUES('update', new.rowid, new.a, new.b); 210 END; 211} 212 213do_execsql_test 10.3 { 214 INSERT INTO t1(a, b) VALUES(1234, 5678) RETURNING rowid; 215} {-1} 216 217do_execsql_test 10.3 { 218 UPDATE t1 SET a='z' WHERE b='y' RETURNING rowid; 219} {1 2 3} 220 221do_execsql_test 10.4 { 222 SELECT * FROM log; 223} { 224 insert -1 1234 5678 update 1 z y update 2 z y update 3 z y 225} 226 227 228finish_test 229 230