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 ifcapable !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 following
 229   # two statements are the rowid columns of views. These values are not
 230   # 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 y
 245   }
 246 }
 247
 248 # 2021-04-27 dbsqlfuzz 78b9400770ef8cc7d9427dfba26f4fcf46ea7dc2
 249 # Returning clauses on TEMP tables with triggers.
 250 #
 251 reset_db
 252 do_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 BEGIN
 258      INSERT INTO log(op,x,y) VALUES('I1',new.a,new.b);
 259   END;
 260   CREATE TEMP TRIGGER t1r2 BEFORE DELETE ON t1 BEGIN
 261      INSERT INTO log(op,x,y) VALUES('D1',old.a,old.b);
 262   END;
 263   CREATE TEMP TRIGGER t2r3 AFTER UPDATE ON t1 BEGIN
 264      INSERT INTO log(op,x,y) VALUES('U1',new.a,new.b);
 265   END;
 266   CREATE TEMP TRIGGER t2r1 BEFORE INSERT ON t2 BEGIN
 267      INSERT INTO log(op,x,y) VALUES('I2',new.c,new.d);
 268   END;
 269   CREATE TEMP TRIGGER t3r1 AFTER DELETE ON t3 BEGIN
 270      INSERT INTO log(op,x,y) VALUES('D3',old.e,old.f);
 271   END;
 272   CREATE TEMP TRIGGER t3r2 BEFORE UPDATE ON t3 BEGIN
 273      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 |}
 277 do_execsql_test 11.2 {
 278   UPDATE t1 SET b=9 WHERE a=1 RETURNING a, b, 'x';
 279 } {1 9 x}
 280 do_execsql_test 11.3 {
 281   DELETE FROM t1 WHERE a<>'xray' RETURNING a, b, '@';
 282 } {1 9 @ happy glad @}
 283 do_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}
 287 do_execsql_test 11.5 {
 288   INSERT INTO t2 VALUES('bravo','charlie') RETURNING d, c, 'z';
 289 } {charlie bravo z}
 290 do_execsql_test 11.6 {
 291   SELECT * FROM log;
 292   DELETE FROM log;
 293 } {I2 bravo charlie}
 294 do_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}
 299 do_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
 304 reset_db
 305 do_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 } {}
 312 do_execsql_test 11.12 {
 313   SELECT * FROM t1;
 314 } {5 30}
 315
 316 # RETURNING column names are dequoted.
 317 # https://sqlite.org/forum/forumpost/033daf0b32
 318 #
 319 reset_db
 320 do_test 12.1 {
 321   db eval {CREATE TABLE t1(x INT, y INT)}
 322   unset -nocomplain cname
 323   db eval {INSERT INTO t1(x) VALUES(1) RETURNING "x";} cname {}
 324   lsort [array names cname]
 325 } {* x}
 326 do_test 12.2 {
 327   unset -nocomplain cname
 328   db eval {INSERT INTO t1(x) VALUES(2) RETURNING [x];} cname {}
 329   lsort [array names cname]
 330 } {* x}
 331 do_test 12.3 {
 332   unset -nocomplain cname
 333   db eval {INSERT INTO t1(x) VALUES(3) RETURNING x AS [xyz];} cname {}
 334   lsort [array names cname]
 335 } {* xyz}
 336 do_test 12.4 {
 337   unset -nocomplain cname
 338   db eval {INSERT INTO t1(x,y) VALUES(4,5) RETURNING "x"+"y";} cname {}
 339   lsort [array names cname]
 340 } {{"x"+"y"} *}
 341
 342 ifcapable rtree {
 343 #-------------------------------------------------------------------------
 344 # Based on dbsqlfuzz find crash-ffbba524cac354b2a61bfd677cec9d2a4333f49a
 345 reset_db
 346 do_execsql_test 13.0 {
 347   CREATE VIRTUAL TABLE t1 USING rtree(a, b, c);
 348   CREATE TABLE t2(x);
 349 }
 350
 351 do_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 rtree
 356
 357 # 2021-12-01 Forum post https://sqlite.org/forum/forumpost/793beaf322
 358 # Need to report foreign key constraint errors prior to RETURNING
 359 #
 360 reset_db
 361 do_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 } {}
 366 do_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/e0c7574ab2
 371 # Incorrect affinity for REAL values that can be represented as integers.
 372 #
 373 reset_db
 374 sqlite3_test_control SQLITE_TESTCTRL_INTERNAL_FUNCTIONS db
 375 do_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}
 379 do_execsql_test 15.1 {
 380   UPDATE t1 SET x=x+1 RETURNING x, affinity(x);
 381 } {6.0 real}
 382 do_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/595e132f71
 387 # RETURNING with the xfer optimization
 388 #
 389 reset_db
 390 do_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}
 396 do_execsql_test 16.1 {
 397   SELECT * FROM t2;
 398 } {1 2 3 a b c}
 399
 400
 401 foreach {tn temp} {
 402   1 ""
 403   2 TEMP
 404 } {
 405   reset_db
 406   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 1
 411     );
 412   "
 413   do_execsql_test 17.$tn.1 {
 414     INSERT INTO foo (fooval) VALUES (17), (4711), (17)
 415       ON CONFLICT DO
 416       UPDATE SET refcnt = refcnt+1
 417     RETURNING fooid;
 418   } {
 419     1 2 1
 420   }
 421 }
 422
 423 # 2022-01-13 https://sqlite.org/forum/forumpost/d010a26798
 424 #
 425 reset_db
 426 do_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/f5a2b1db87
 433 # NULL pointer dereference following an error.
 434 #
 435 do_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 }
 440 do_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/c99d6e0329
 446 # ticket d15b3a4ea901ef0d
 447 # ticket 89d259d45b855a0d
 448 #
 449 # A RETURNING clause on an IF NOT EXISTS trigger does not generate
 450 # an error if the trigger already exists.
 451 #
 452 do_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 } {}
 456 do_catchsql_test 19.1 {
 457   CREATE TRIGGER IF NOT EXISTS r1 AFTER DELETE ON t1 BEGIN
 458     INSERT  INTO t1(a) VALUES (1) RETURNING FALSE;
 459     INSERT  INTO t1(a) VALUES (2) RETURNING TRUE;
 460   END;
 461 } {0 {}}
 462
 463 finish_test