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 14set testdir [file dirname $argv0] 15source $testdir/tester.tcl 16set testprefix zipfile 17 18do_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} 25do_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} 31do_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} 37do_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}} 41do_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}} 45breakpoint 46do_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}} 50do_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 56do_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}} 64do_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'}} 67do_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 73do_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}} 80do_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}} 85do_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; 93do_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} 101do_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 107do_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# 119do_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} 125do_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 131finish_test 132