test/returning1.test

   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.3a {
 216   INSERT INTO t1(a, b) VALUES(1234, 5678) RETURNING rowid;
 217 } {1 {no such column: new.rowid}}
 218
 219 do_catchsql_test 10.3b {
 220   UPDATE t1 SET a='z' WHERE b='y' RETURNING rowid;
 221 } {1 {no such column: new.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
 380 foreach {tn temp} {
 381   1 ""
 382   2 TEMP
 383 } {
 384   reset_db
 385   do_execsql_test 17.$tn.0 "
 386     CREATE $temp TABLE foo (
 387       fooid INTEGER PRIMARY KEY,
 388       fooval INTEGER NOT NULL UNIQUE,
 389       refcnt INTEGER NOT NULL DEFAULT 1
 390     );
 391   "
 392   do_execsql_test 17.$tn.1 {
 393     INSERT INTO foo (fooval) VALUES (17), (4711), (17)
 394       ON CONFLICT DO
 395       UPDATE SET refcnt = refcnt+1
 396     RETURNING fooid;
 397   } {
 398     1 2 1
 399   }
 400 }
 401
 402 # 2022-01-13 https://sqlite.org/forum/forumpost/d010a26798
 403 #
 404 reset_db
 405 do_execsql_test 17.0 {
 406   CREATE TABLE bug(id INTEGER PRIMARY KEY NOT NULL, x);
 407   INSERT INTO bug(id,x) VALUES(20, NULL);
 408   UPDATE bug SET x=NULL WHERE id = 20 RETURNING quote(x), x IS NULL;
 409 } {NULL 1}
 410
 411 # 2023-03-08 https://sqlite.org/forum/forumpost/f5a2b1db87
 412 # NULL pointer dereference following an error.
 413 #
 414 do_execsql_test 18.0 {
 415   CREATE TABLE v0(c1 INT);
 416   CREATE VIEW view_2(c1) AS SELECT CASE WHEN c1 COLLATE TRUE THEN TRUE ELSE TRUE END FROM v0;
 417   CREATE TRIGGER x1 INSTEAD OF INSERT ON view_2 BEGIN SELECT true; END;
 418 }
 419 do_catchsql_test 18.1 {
 420   INSERT INTO view_2 DEFAULT VALUES RETURNING *;
 421 } {1 {no such collation sequence: TRUE}}
 422
 423 # 2023-03-16
 424 # https://sqlite.org/forum/forumpost/c99d6e0329
 425 # ticket d15b3a4ea901ef0d
 426 # ticket 89d259d45b855a0d
 427 #
 428 # A RETURNING clause on an IF NOT EXISTS trigger does not generate
 429 # an error if the trigger already exists.
 430 #
 431 do_execsql_test 19.0 {
 432   DROP TABLE IF EXISTS t1;CREATE TABLE t1(a);
 433   CREATE TRIGGER r1 AFTER UPDATE ON t1 BEGIN VALUES(0); END;
 434 } {}
 435 do_catchsql_test 19.1 {
 436   CREATE TRIGGER IF NOT EXISTS r1 AFTER DELETE ON t1 BEGIN
 437     INSERT  INTO t1(a) VALUES (1) RETURNING FALSE;
 438     INSERT  INTO t1(a) VALUES (2) RETURNING TRUE;
 439   END;
 440 } {0 {}}
 441
 442 finish_test