test/upfrom2.test

   1 # 2020 April 29
   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 #
  12
  13 set testdir [file dirname $argv0]
  14 source $testdir/tester.tcl
  15 set testprefix upfrom2
  16
  17 # Test cases:
  18 #
  19 #   1.*: Test that triggers are fired correctly for UPDATE FROM statements,
  20 #        and only once for each row. Except for INSTEAD OF triggers on
  21 #        views - these are fired once for each row returned by the join,
  22 #        including duplicates.
  23 #
  24 #   2.*: Test adding ORDER BY and LIMIT clauses with UPDATE FROM statements.
  25 #
  26 #   5.*: Test that specifying the target table name or alias in the FROM
  27 #        clause of an UPDATE statement is an error.
  28 #
  29
  30 foreach {tn wo} {
  31   1 ""
  32   2 "WITHOUT ROWID"
  33 } {
  34   reset_db
  35
  36   eval [string map [list %WO% $wo %TN% $tn] {
  37   do_execsql_test 1.%TN%.0 {
  38     CREATE TABLE log(t TEXT);
  39     CREATE TABLE t1(x PRIMARY KEY, y, z UNIQUE) %WO%;
  40     CREATE INDEX t1y ON t1(y);
  41
  42     INSERT INTO t1 VALUES(1, 'i',   'one');
  43     INSERT INTO t1 VALUES(2, 'ii',  'two');
  44     INSERT INTO t1 VALUES(3, 'iii', 'three');
  45     INSERT INTO t1 VALUES(4, 'iv',  'four');
  46
  47     CREATE TRIGGER tr1 BEFORE UPDATE ON t1 BEGIN
  48       INSERT INTO log VALUES(old.z || '->' || new.z);
  49     END;
  50     CREATE TRIGGER tr2 AFTER UPDATE ON t1 BEGIN
  51       INSERT INTO log VALUES(old.y || '->' || new.y);
  52     END;
  53   }
  54
  55   do_execsql_test 1.%TN%.1 {
  56     WITH data(k, v) AS (
  57       VALUES(3, 'thirty'), (1, 'ten')
  58     )
  59     UPDATE t1 SET z=v FROM data WHERE x=k;
  60
  61     SELECT * FROM t1;
  62     SELECT * FROM log;
  63   } {
  64     1 i ten   2 ii two   3 iii thirty   4 iv four
  65     one->ten        i->i
  66     three->thirty   iii->iii
  67   }
  68
  69   do_execsql_test 1.%TN%.2 {
  70     CREATE TABLE t2(a, b);
  71     CREATE TABLE t3(k, v);
  72
  73     INSERT INTO t3 VALUES(5,   'v');
  74     INSERT INTO t3 VALUES(12, 'xii');
  75
  76     INSERT INTO t2 VALUES(2, 12);
  77     INSERT INTO t2 VALUES(3, 5);
  78
  79     DELETE FROM log;
  80     UPDATE t1 SET y=v FROM t2, t3 WHERE t1.x=t2.a AND t3.k=t2.b;
  81
  82     SELECT * FROM t1;
  83     SELECT * FROM log;
  84   } {
  85     1 i ten   2 xii two   3 v thirty   4 iv four
  86     two->two         ii->xii
  87     thirty->thirty   iii->v
  88   }
  89
  90   do_execsql_test 1.%TN%.3 {
  91     DELETE FROM log;
  92     WITH data(k, v) AS (
  93       VALUES(1, 'seven'), (1, 'eight'), (2, 'eleven'), (2, 'twelve')
  94     )
  95     UPDATE t1 SET z=v FROM data WHERE x=k;
  96
  97     SELECT * FROM t1;
  98     SELECT * FROM log;
  99   } {
 100     1 i eight   2 xii twelve   3 v thirty   4 iv four
 101     ten->eight        i->i
 102     two->twelve       xii->xii
 103   }
 104
 105   do_test 1.%TN%.4 { db changes } {2}
 106
 107   do_execsql_test 1.%TN%.5 {
 108     CREATE VIEW v1 AS SELECT * FROM t1;
 109     CREATE TRIGGER v1tr INSTEAD OF UPDATE ON v1 BEGIN
 110       UPDATE t1 SET y=new.y, z=new.z WHERE x=new.x;
 111     END;
 112
 113     DELETE FROM log;
 114     WITH data(k, v) AS (
 115       VALUES(3, 'thirteen'), (3, 'fourteen'), (4, 'fifteen'), (4, 'sixteen')
 116     )
 117     UPDATE v1 SET z=v FROM data WHERE x=k;
 118   }
 119
 120   do_execsql_test 1.%TN%.6 {
 121     SELECT * FROM v1;
 122     SELECT * FROM log;
 123   } {
 124     1 i eight   2 xii twelve   3 v fourteen   4 iv sixteen
 125     thirty->thirteen  v->v
 126     thirteen->fourteen  v->v
 127     four->fifteen  iv->iv
 128     fifteen->sixteen  iv->iv
 129   }
 130
 131   #--------------------------------------------------------------
 132
 133   do_execsql_test 1.%TN%.7 {
 134     CREATE TABLE o1(w, x, y, z UNIQUE, PRIMARY KEY(w, x)) %WO%;
 135     CREATE INDEX o1y ON t1(y);
 136
 137     INSERT INTO o1 VALUES(0, 0, 'i', 'one');
 138     INSERT INTO o1 VALUES(0, 1, 'ii', 'two');
 139     INSERT INTO o1 VALUES(1, 0, 'iii', 'three');
 140     INSERT INTO o1 VALUES(1, 1, 'iv', 'four');
 141
 142     CREATE TRIGGER tro1 BEFORE UPDATE ON o1 BEGIN
 143       INSERT INTO log VALUES(old.z || '->' || new.z);
 144     END;
 145     CREATE TRIGGER tro2 AFTER UPDATE ON o1 BEGIN
 146       INSERT INTO log VALUES(old.y || '->' || new.y);
 147     END;
 148   }
 149
 150   do_execsql_test 1.%TN%.8 {
 151     DELETE FROM log;
 152     WITH data(k, v) AS (
 153       VALUES(3, 'thirty'), (1, 'ten')
 154     )
 155     UPDATE o1 SET z=v FROM data WHERE (1+x+w*2)=k;
 156
 157     SELECT * FROM o1;
 158     SELECT * FROM log;
 159   } {
 160     0 0 i ten   0 1 ii two   1 0 iii thirty   1 1 iv four
 161     one->ten        i->i
 162     three->thirty   iii->iii
 163   }
 164
 165   do_execsql_test 1.%TN%.9 {
 166     DELETE FROM log;
 167     UPDATE o1 SET y=v FROM t2, t3 WHERE (1+o1.w*2+o1.x)=t2.a AND t3.k=t2.b;
 168
 169     SELECT * FROM o1;
 170     SELECT * FROM log;
 171   } {
 172     0 0 i ten   0 1 xii two   1 0 v thirty   1 1 iv four
 173     two->two         ii->xii
 174     thirty->thirty   iii->v
 175   }
 176
 177   do_execsql_test 1.%TN%.10 {
 178     DELETE FROM log;
 179     WITH data(k, v) AS (
 180       VALUES(1, 'seven'), (1, 'eight'), (2, 'eleven'), (2, 'twelve')
 181     )
 182     UPDATE o1 SET z=v FROM data WHERE (1+w*2+x)=k;
 183
 184     SELECT * FROM o1;
 185     SELECT * FROM log;
 186   } {
 187     0 0 i eight   0 1 xii twelve   1 0 v thirty   1 1 iv four
 188     ten->eight        i->i
 189     two->twelve       xii->xii
 190   }
 191
 192   do_test 1.%TN%.11 { db changes } {2}
 193
 194   do_execsql_test 1.%TN%.12 {
 195     CREATE VIEW w1 AS SELECT * FROM o1;
 196     CREATE TRIGGER w1tr INSTEAD OF UPDATE ON w1 BEGIN
 197       UPDATE o1 SET y=new.y, z=new.z WHERE w=new.w AND x=new.x;
 198     END;
 199
 200     DELETE FROM log;
 201     WITH data(k, v) AS (
 202       VALUES(3, 'thirteen'), (3, 'fourteen'), (4, 'fifteen'), (4, 'sixteen')
 203     )
 204     UPDATE w1 SET z=v FROM data WHERE (1+w*2+x)=k;
 205   }
 206
 207   do_execsql_test 1.%TN%.13 {
 208     SELECT * FROM w1;
 209     SELECT * FROM log;
 210   } {
 211     0 0 i eight   0 1 xii twelve   1 0 v fourteen   1 1 iv sixteen
 212     thirty->thirteen  v->v
 213     thirteen->fourteen  v->v
 214     four->fifteen  iv->iv
 215     fifteen->sixteen  iv->iv
 216   }
 217
 218 }]
 219 }
 220
 221 ifcapable update_delete_limit {
 222 foreach {tn wo} {
 223   1 ""
 224   2 "WITHOUT ROWID"
 225 } {
 226   reset_db
 227
 228 eval [string map [list %WO% $wo %TN% $tn] {
 229   do_execsql_test 2.%TN%.1 {
 230     CREATE TABLE x1(a INTEGER PRIMARY KEY, b) %WO%;
 231     INSERT INTO x1 VALUES
 232         (1, 'one'), (2, 'two'), (3, 'three'), (4, 'four'),
 233         (5, 'five'), (6, 'six'), (7, 'seven'), (8, 'eight');
 234   }
 235
 236   do_execsql_test 2.%TN%.2 {
 237     CREATE TABLE data1(x, y);
 238     INSERT INTO data1 VALUES
 239     (1, 'eleven'), (1, 'twenty-one'), (2, 'twelve'), (2, 'twenty-two'),
 240     (3, 'thirteen'), (3, 'twenty-three'), (4, 'fourteen'), (4, 'twenty-four');
 241   }
 242
 243   do_execsql_test 2.%TN%.3 {
 244     UPDATE x1 SET b=y FROM data1 WHERE a=x ORDER BY a LIMIT 3;
 245     SELECT * FROM x1;
 246   } {
 247     1 eleven 2 twelve 3 thirteen 4 four 5 five 6 six 7 seven 8 eight
 248   }
 249
 250   do_execsql_test 2.%TN%.4 {
 251     UPDATE x1 SET b=b||y FROM data1 WHERE a=x ORDER BY b LIMIT 3;
 252     SELECT * FROM x1;
 253   } {
 254     1 eleveneleven 2 twelve 3 thirteenthirteen 4 fourfourteen
 255     5 five 6 six 7 seven 8 eight
 256   }
 257
 258   do_catchsql_test 2.%TN%.5 {
 259     UPDATE x1 SET b=b||b ORDER BY b;
 260   } {1 {ORDER BY without LIMIT on UPDATE}}
 261   do_catchsql_test 2.%TN%.6 {
 262     UPDATE x1 SET b=b||y FROM data1 WHERE a=x ORDER BY b;
 263   } {1 {ORDER BY without LIMIT on UPDATE}}
 264
 265   #-----------------------------------------------------------------------
 266
 267   do_execsql_test 2.%TN%.6 {
 268     DROP TABLE x1;
 269     CREATE TABLE x1(u, v, b, PRIMARY KEY(u, v)) %WO%;
 270     INSERT INTO x1 VALUES
 271         (0, 1, 'one'), (1, 0, 'two'), (1, 1, 'three'), (2, 0, 'four'),
 272         (2, 1, 'five'), (3, 0, 'six'), (3, 1, 'seven'), (4, 0, 'eight');
 273   }
 274
 275   do_execsql_test 2.%TN%.7 {
 276     UPDATE x1 SET b=y FROM data1 WHERE (u*2+v)=x ORDER BY u, v LIMIT 3;
 277     SELECT * FROM x1;
 278   } {
 279     0 1 eleven 1 0 twelve 1 1 thirteen 2 0 four
 280     2 1 five 3 0 six 3 1 seven 4 0 eight
 281   }
 282
 283   do_execsql_test 2.%TN%.8 {
 284     UPDATE x1 SET b=b||y FROM data1 WHERE (u*2+v)=x ORDER BY b LIMIT 3;
 285     SELECT * FROM x1;
 286   } {
 287     0 1 eleveneleven 1 0 twelve 1 1 thirteenthirteen 2 0 fourfourteen
 288     2 1 five  3 0 six 3 1 seven 4 0 eight
 289   }
 290
 291
 292 }]
 293 }}
 294
 295 reset_db
 296 do_execsql_test 3.0 {
 297   CREATE TABLE data(x, y, z);
 298   CREATE VIEW t1 AS SELECT * FROM data;
 299   CREATE TRIGGER t1_insert INSTEAD OF INSERT ON t1 BEGIN
 300     INSERT INTO data VALUES(new.x, new.y, new.z);
 301   END;
 302   CREATE TRIGGER t1_update INSTEAD OF UPDATE ON t1 BEGIN
 303     INSERT INTO log VALUES(old.z || '->' || new.z);
 304   END;
 305
 306   CREATE TABLE log(t TEXT);
 307
 308   INSERT INTO t1 VALUES(1, 'i',   'one');
 309   INSERT INTO t1 VALUES(2, 'ii',  'two');
 310   INSERT INTO t1 VALUES(3, 'iii', 'three');
 311   INSERT INTO t1 VALUES(4, 'iv',  'four');
 312 }
 313
 314 do_execsql_test 3.1 {
 315   WITH input(k, v) AS (
 316       VALUES(3, 'thirty'), (1, 'ten')
 317   )
 318   UPDATE t1 SET z=v FROM input WHERE x=k;
 319 }
 320
 321 foreach {tn sql} {
 322   2 {
 323     CREATE TABLE x1(a INT PRIMARY KEY, b, c) WITHOUT ROWID;
 324   }
 325   1 {
 326     CREATE TABLE x1(a INTEGER PRIMARY KEY, b, c);
 327   }
 328   3 {
 329     CREATE TABLE x1(a INT PRIMARY KEY, b, c);
 330   }
 331 } {
 332
 333   reset_db
 334   execsql $sql
 335
 336   do_execsql_test 4.$tn.0 {
 337     INSERT INTO x1 VALUES(1, 1, 1);
 338     INSERT INTO x1 VALUES(2, 2, 2);
 339     INSERT INTO x1 VALUES(3, 3, 3);
 340     INSERT INTO x1 VALUES(4, 4, 4);
 341     INSERT INTO x1 VALUES(5, 5, 5);
 342     CREATE TABLE map(o, t);
 343     INSERT INTO map VALUES(3, 30), (4, 40), (1, 10);
 344   }
 345
 346   do_execsql_test 4.$tn.1 {
 347     UPDATE x1 SET a=t FROM map WHERE a=o;
 348     SELECT * FROM x1 ORDER BY a;
 349   } {2 2 2   5 5 5   10 1 1  30 3 3  40 4 4}
 350 }
 351
 352 reset_db
 353 do_execsql_test 5.0 {
 354   CREATE TABLE x1(a, b, c);
 355   CREATE TABLE x2(a, b, c);
 356 }
 357
 358 foreach {tn update nm} {
 359   1 "UPDATE x1 SET a=5 FROM x1" x1
 360   2 "UPDATE x1 AS grapes SET a=5 FROM x1 AS grapes" grapes
 361   3 "UPDATE x1 SET a=5 FROM x2, x1" x1
 362   4 "UPDATE x1 AS grapes SET a=5 FROM x2, x1 AS grapes" grapes
 363 } {
 364   do_catchsql_test 5.$tn $update \
 365     "1 {target object/alias may not appear in FROM clause: $nm}"
 366 }
 367
 368 #--------------------------------------------------------------------------
 369 reset_db
 370 do_execsql_test 6.0 {
 371   CREATE TABLE t1(a);
 372 }
 373
 374 do_execsql_test 6.1 {
 375   UPDATE t1 SET a = 1 FROM (
 376       SELECT * FROM t1
 377   )
 378 } {}
 379 do_execsql_test 6.2 {
 380   UPDATE t1 SET a = 1 FROM (
 381       SELECT * FROM t1 UNION ALL SELECT * FROM t1
 382   )
 383 } {}
 384
 385 # 2022-03-21
 386 # https://sqlite.org/forum/forumpost/929168fdd6
 387 #
 388 reset_db
 389 do_execsql_test 7.0 {
 390   CREATE TABLE t1(a);
 391   INSERT INTO t1(a) VALUES(11),(22),(33),(44),(55);
 392   CREATE VIEW t2(b,c) AS SELECT a, COUNT(*) OVER () FROM t1;
 393   CREATE TABLE t3(x,y);
 394   CREATE TRIGGER t2r1 INSTEAD OF UPDATE ON t2 BEGIN
 395     INSERT INTO t3(x,y) VALUES(new.b,new.c);
 396   END;
 397   SELECT * FROM t2;
 398 } {11 5 22 5 33 5 44 5 55 5}
 399 do_execsql_test 7.1 {
 400   UPDATE t2 SET c=t1.a FROM t1 WHERE t2.b=t1.a;
 401   SELECT * FROM t3;
 402 } {11 11 22 22 33 33 44 44 55 55}
 403
 404
 405 finish_test