test/upsert1.test

   1 # 2018-04-12
   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 # Test cases for UPSERT
  13
  14 set testdir [file dirname $argv0]
  15 source $testdir/tester.tcl
  16 set testprefix zipfile
  17
  18 do_execsql_test upsert1-100 {
  19   CREATE TABLE t1(a INTEGER PRIMARY KEY, b TEXT, c DEFAULT 0);
  20   CREATE UNIQUE INDEX t1x1 ON t1(b);
  21   INSERT INTO t1(a,b) VALUES(1,2) ON CONFLICT DO NOTHING;
  22   INSERT INTO t1(a,b) VALUES(1,99),(99,2) ON CONFLICT DO NOTHING;
  23   SELECT * FROM t1;
  24 } {1 2 0}
  25 do_execsql_test upsert1-101 {
  26   DELETE FROM t1;
  27   INSERT INTO t1(a,b) VALUES(2,3) ON CONFLICT(a) DO NOTHING;
  28   INSERT INTO t1(a,b) VALUES(2,99) ON CONFLICT(a) DO NOTHING;
  29   SELECT * FROM t1;
  30 } {2 3 0}
  31 do_execsql_test upsert1-102 {
  32   DELETE FROM t1;
  33   INSERT INTO t1(a,b) VALUES(3,4) ON CONFLICT(b) DO NOTHING;
  34   INSERT INTO t1(a,b) VALUES(99,4) ON CONFLICT(b) DO NOTHING;
  35   SELECT * FROM t1;
  36 } {3 4 0}
  37 do_catchsql_test upsert1-110 {
  38   INSERT INTO t1(a,b) VALUES(5,6) ON CONFLICT(x) DO NOTHING;
  39   SELECT * FROM t1;
  40 } {1 {no such column: x}}
  41 do_catchsql_test upsert1-120 {
  42   INSERT INTO t1(a,b) VALUES(5,6) ON CONFLICT(c) DO NOTHING;
  43   SELECT * FROM t1;
  44 } {1 {ON CONFLICT clause does not match any PRIMARY KEY or UNIQUE constraint}}
  45 breakpoint
  46 do_catchsql_test upsert1-130 {
  47   INSERT INTO t1(a,b) VALUES(5,6) ON CONFLICT(b COLLATE nocase) DO NOTHING;
  48   SELECT * FROM t1;
  49 } {1 {ON CONFLICT clause does not match any PRIMARY KEY or UNIQUE constraint}}
  50 do_execsql_test upsert1-140 {
  51   DELETE FROM t1;
  52   INSERT INTO t1(a,b) VALUES(5,6) ON CONFLICT(b COLLATE binary) DO NOTHING;
  53   SELECT * FROM t1;
  54 } {5 6 0}
  55
  56 do_catchsql_test upsert1-200 {
  57   DROP TABLE t1;
  58   CREATE TABLE t1(a INTEGER PRIMARY KEY, b INT, c DEFAULT 0);
  59   CREATE UNIQUE INDEX t1x1 ON t1(a+b);
  60   INSERT INTO t1(a,b) VALUES(7,8) ON CONFLICT(a+b) DO NOTHING;
  61   INSERT INTO t1(a,b) VALUES(8,7),(9,6) ON CONFLICT(a+b) DO NOTHING;
  62   SELECT * FROM t1;
  63 } {0 {7 8 0}}
  64 do_catchsql_test upsert1-201 {
  65   INSERT INTO t1(a,b) VALUES(8,7),(9,6) ON CONFLICT(a) DO NOTHING;
  66 } {1 {UNIQUE constraint failed: index 't1x1'}}
  67 do_catchsql_test upsert1-210 {
  68   DELETE FROM t1;
  69   INSERT INTO t1(a,b) VALUES(9,10) ON CONFLICT(a+(+b)) DO NOTHING;
  70   SELECT * FROM t1;
  71 } {1 {ON CONFLICT clause does not match any PRIMARY KEY or UNIQUE constraint}}
  72
  73 do_catchsql_test upsert1-300 {
  74   DROP INDEX t1x1;
  75   DELETE FROM t1;
  76   CREATE UNIQUE INDEX t1x1 ON t1(b) WHERE b>10;
  77   INSERT INTO t1(a,b) VALUES(1,2),(3,2) ON CONFLICT(b) DO NOTHING;
  78   SELECT * FROM t1;
  79 } {1 {ON CONFLICT clause does not match any PRIMARY KEY or UNIQUE constraint}}
  80 do_catchsql_test upsert1-310 {
  81   DELETE FROM t1;
  82   INSERT INTO t1(a,b) VALUES(1,2),(3,2) ON CONFLICT(b) WHERE b!=10 DO NOTHING;
  83   SELECT * FROM t1;
  84 } {1 {ON CONFLICT clause does not match any PRIMARY KEY or UNIQUE constraint}}
  85 do_execsql_test upsert1-320 {
  86   DELETE FROM t1;
  87   INSERT INTO t1(a,b) VALUES(1,2),(3,2),(4,20),(5,20)
  88          ON CONFLICT(b) WHERE b>10 DO NOTHING;
  89   SELECT *, 'x' FROM t1 ORDER BY b, a;
  90 } {1 2 0 x 3 2 0 x 4 20 0 x}
  91
  92 # Upsert works with count_changes=on;
  93 do_execsql_test upsert1-400 {
  94   DROP TABLE IF EXISTS t2;
  95   CREATE TABLE t2(a TEXT UNIQUE, b INT DEFAULT 1);
  96   INSERT INTO t2(a) VALUES('one'),('two'),('three');
  97   PRAGMA count_changes=ON;
  98   INSERT INTO t2(a) VALUES('one'),('one'),('three'),('four')
  99       ON CONFLICT(a) DO UPDATE SET b=b+1;
 100 } {1}
 101 do_execsql_test upsert1-410 {
 102   PRAGMA count_changes=OFF;
 103   SELECT a, b FROM t2 ORDER BY a;
 104 } {four 1 one 3 three 2 two 1}
 105
 106 # Problem found by AFL prior to any release
 107 do_execsql_test upsert1-500 {
 108   DROP TABLE t1;
 109   CREATE TABLE t1(x INTEGER PRIMARY KEY, y INT UNIQUE);
 110   INSERT INTO t1(x,y) SELECT 1,2 WHERE true
 111     ON CONFLICT(x) DO UPDATE SET y=max(t1.y,excluded.y) AND true;
 112   SELECT * FROM t1;
 113 } {1 2}
 114
 115 # 2018-07-11
 116 # Ticket https://sqlite.org/src/tktview/79cad5e4b2e219dd197242e9e5f4
 117 # UPSERT leads to a corrupt index.
 118 #
 119 do_execsql_test upsert1-600 {
 120   DROP TABLE t1;
 121   CREATE TABLE t1(b UNIQUE, a INT PRIMARY KEY) WITHOUT ROWID;
 122   INSERT OR IGNORE INTO t1(a) VALUES('1') ON CONFLICT(a) DO NOTHING;
 123   PRAGMA integrity_check;
 124 } {ok}
 125 do_execsql_test upsert1-610 {
 126   DELETE FROM t1;
 127   INSERT OR IGNORE INTO t1(a) VALUES('1'),(1) ON CONFLICT(a) DO NOTHING;
 128   PRAGMA integrity_check;
 129 } {ok}
 130
 131 # 2018-08-14
 132 # Ticket https://www.sqlite.org/src/info/908f001483982c43
 133 # If there are multiple uniqueness contraints, the UPSERT should fire
 134 # if the one constraint it targets fails, regardless of whether or not
 135 # the other constraints pass or fail.  In other words, the UPSERT constraint
 136 # should be tested first.
 137 #
 138 do_execsql_test upsert1-700 {
 139   DROP TABLE t1;
 140   CREATE TABLE t1(a INTEGER PRIMARY KEY, b INT, c INT, d INT, e INT);
 141   CREATE UNIQUE INDEX t1b ON t1(b);
 142   CREATE UNIQUE INDEX t1e ON t1(e);
 143   INSERT INTO t1(a,b,c,d,e) VALUES(1,2,3,4,5);
 144   INSERT INTO t1(a,b,c,d,e) VALUES(1,2,33,44,5)
 145     ON CONFLICT(e) DO UPDATE SET c=excluded.c;
 146   SELECT * FROM t1;
 147 } {1 2 33 4 5}
 148 do_execsql_test upsert1-710 {
 149   DELETE FROM t1;
 150   INSERT INTO t1(a,b,c,d,e) VALUES(1,2,3,4,5);
 151   INSERT INTO t1(a,b,c,d,e) VALUES(1,2,33,44,5)
 152     ON CONFLICT(a) DO UPDATE SET c=excluded.c;
 153   SELECT * FROM t1;
 154 } {1 2 33 4 5}
 155 do_execsql_test upsert1-720 {
 156   DELETE FROM t1;
 157   INSERT INTO t1(a,b,c,d,e) VALUES(1,2,3,4,5);
 158   INSERT INTO t1(a,b,c,d,e) VALUES(1,2,33,44,5)
 159     ON CONFLICT(b) DO UPDATE SET c=excluded.c;
 160   SELECT * FROM t1;
 161 } {1 2 33 4 5}
 162 do_execsql_test upsert1-730 {
 163   DROP TABLE t1;
 164   CREATE TABLE t1(a INT, b INT, c INT, d INT, e INT);
 165   CREATE UNIQUE INDEX t1a ON t1(a);
 166   CREATE UNIQUE INDEX t1b ON t1(b);
 167   CREATE UNIQUE INDEX t1e ON t1(e);
 168   INSERT INTO t1(a,b,c,d,e) VALUES(1,2,3,4,5);
 169   INSERT INTO t1(a,b,c,d,e) VALUES(1,2,33,44,5)
 170     ON CONFLICT(e) DO UPDATE SET c=excluded.c;
 171   SELECT * FROM t1;
 172 } {1 2 33 4 5}
 173 do_execsql_test upsert1-740 {
 174   DELETE FROM t1;
 175   INSERT INTO t1(a,b,c,d,e) VALUES(1,2,3,4,5);
 176   INSERT INTO t1(a,b,c,d,e) VALUES(1,2,33,44,5)
 177     ON CONFLICT(a) DO UPDATE SET c=excluded.c;
 178   SELECT * FROM t1;
 179 } {1 2 33 4 5}
 180 do_execsql_test upsert1-750 {
 181   DELETE FROM t1;
 182   INSERT INTO t1(a,b,c,d,e) VALUES(1,2,3,4,5);
 183   INSERT INTO t1(a,b,c,d,e) VALUES(1,2,33,44,5)
 184     ON CONFLICT(b) DO UPDATE SET c=excluded.c;
 185   SELECT * FROM t1;
 186 } {1 2 33 4 5}
 187 do_execsql_test upsert1-760 {
 188   DROP TABLE t1;
 189   CREATE TABLE t1(a INT PRIMARY KEY, b INT, c INT, d INT, e INT) WITHOUT ROWID;
 190   CREATE UNIQUE INDEX t1a ON t1(a);
 191   CREATE UNIQUE INDEX t1b ON t1(b);
 192   CREATE UNIQUE INDEX t1e ON t1(e);
 193   INSERT INTO t1(a,b,c,d,e) VALUES(1,2,3,4,5);
 194   INSERT INTO t1(a,b,c,d,e) VALUES(1,2,33,44,5)
 195     ON CONFLICT(e) DO UPDATE SET c=excluded.c;
 196   SELECT * FROM t1;
 197 } {1 2 33 4 5}
 198 do_execsql_test upsert1-770 {
 199   DELETE FROM t1;
 200   INSERT INTO t1(a,b,c,d,e) VALUES(1,2,3,4,5);
 201   INSERT INTO t1(a,b,c,d,e) VALUES(1,2,33,44,5)
 202     ON CONFLICT(a) DO UPDATE SET c=excluded.c;
 203   SELECT * FROM t1;
 204 } {1 2 33 4 5}
 205 do_execsql_test upsert1-780 {
 206   DELETE FROM t1;
 207   INSERT INTO t1(a,b,c,d,e) VALUES(1,2,3,4,5);
 208   INSERT INTO t1(a,b,c,d,e) VALUES(1,2,33,44,5)
 209     ON CONFLICT(b) DO UPDATE SET c=excluded.c;
 210   SELECT * FROM t1;
 211 } {1 2 33 4 5}
 212
 213 # 2019-08-30 ticket https://sqlite.org/src/info/5a3dba8104421320
 214 do_execsql_test upsert1-800 {
 215   DROP TABLE IF EXISTS t0;
 216   CREATE TABLE t0(c0 REAL UNIQUE, c1);
 217   CREATE UNIQUE INDEX test800i0 ON t0(0 || c1);
 218   INSERT INTO t0(c0, c1) VALUES (1, 2),  (2, 1);
 219   INSERT INTO t0(c0) VALUES (1) ON CONFLICT(c0) DO UPDATE SET c1=excluded.c0;
 220   PRAGMA integrity_check;
 221   REINDEX;
 222 } {ok}
 223
 224 # 2019-12-06 gramfuzz find
 225 sqlite3 db :memory:
 226 do_execsql_test upsert1-900 {
 227   CREATE VIEW t1(a) AS SELECT 1;
 228   CREATE TRIGGER t1r1 INSTEAD OF INSERT ON t1 BEGIN
 229      SELECT 2;
 230   END;
 231 }
 232 do_catchsql_test upsert1-910 {
 233   INSERT INTO t1 VALUES(3) ON CONFLICT(x) DO NOTHING;
 234 } {1 {cannot UPSERT a view}}
 235
 236 # 2019-12-26 ticket 7c13db5c3bf74001
 237 reset_db
 238 do_catchsql_test upsert1-1000 {
 239   CREATE TABLE t0(c0 PRIMARY KEY, c1, c2 UNIQUE) WITHOUT ROWID;
 240   INSERT OR FAIL INTO t0(c2) VALUES (0), (NULL)
 241     ON CONFLICT(c2) DO UPDATE SET c1 = c0;
 242 } {1 {NOT NULL constraint failed: t0.c0}}
 243
 244 # 2021-12-29 forum post https://sqlite.org/forum/forumpost/06b16b8b29f8c8c3
 245 # By Jingzhou Fu. When there is both an INTEGER PRIMARY KEY ON CONFLICT REPLACE
 246 # and an upsert on a constraint other than the INTEGER PRIMARY KEY, the
 247 # constraint checking logic generates invalid bytecode which might result
 248 # in a NULL pointer dereference.
 249 #
 250 reset_db
 251 do_execsql_test upsert1-1100 {
 252   CREATE TABLE t1(a INTEGER PRIMARY KEY ON CONFLICT REPLACE, b UNIQUE);
 253   INSERT INTO t1(b) VALUES(22);
 254   INSERT INTO t1 VALUES(2,22) ON CONFLICT (b) DO NOTHING;
 255   SELECT * FROM t1;
 256 } {1 22}
 257
 258 # 2023-08-17 dbsqlfuzz 9983e2c77634a8ccf33b5c91fa9982599de5f9e9
 259 # Bound parameters in the ON CONFLICT clause of an UPSERT.
 260 #
 261 reset_db
 262 do_execsql_test upsert1-1200 {
 263   CREATE TABLE t1(a INT, b INT);
 264   CREATE UNIQUE INDEX t1x ON t1(b+3);
 265 }
 266 sqlite3_db_config db ENABLE_QPSG 1
 267 do_catchsql_test upsert1-1210 {
 268   INSERT INTO t1(a,b) VALUES(1,2) ON CONFLICT(b+?1) DO NOTHING;
 269 } {1 {ON CONFLICT clause does not match any PRIMARY KEY or UNIQUE constraint}}
 270
 271 # 2024-04-11 https://sqlite.org/forum/forumpost/284955a3cd454a15
 272 # Incorrect value passed into a trigger that fires as the result of
 273 # an upsert.
 274 #
 275 reset_db
 276 do_execsql_test upsert1-1300 {
 277   CREATE TABLE t1(x INT, y TEXT);
 278   INSERT INTO t1 VALUES
 279     (11, printf('%.9000c','a')),
 280     (11, printf('%.9000c','a')),
 281     (33, printf('%.9000c','b')),
 282     (33, printf('%.9000c','b'));
 283   CREATE TABLE t2(x INT UNIQUE, y TEXT);
 284   CREATE TRIGGER r1 BEFORE UPDATE ON t2 BEGIN
 285     SELECT raise(ABORT,'Incorrect old.y value passed to trigger!')
 286      WHERE old.y != new.y;
 287     /* ^^^ This trigger will fire and cause the ABORT if the problem has
 288     ** not been fixed, or if there is a regression. */
 289   END;
 290   INSERT INTO t2(x, y) SELECT x, y FROM t1
 291    WHERE true
 292    ON CONFLICT (x) DO UPDATE SET y = excluded.y;
 293 } {}
 294
 295 finish_test