From b7ab29ecbe848fd11b9349cb4b86a69f9d02b47a Mon Sep 17 00:00:00 2001
From: "D. Richard Hipp" <drh@hwaci.com>
Date: Tue, 17 Apr 2018 16:16:40 +0000
Subject: [PATCH] New test cases for upsert.

---
 test/distinct.test |   2 +-
 test/upsert2.test  | 170 +++++++++++++++++++++++++++++++++++++++++++++++++++++
 test/upsert3.test  |  58 ++++++++++++++++++
 3 files changed, 229 insertions(+), 1 deletion(-)
 create mode 100644 test/upsert2.test
 create mode 100644 test/upsert3.test

diff --git a/test/distinct.test b/test/distinct.test
index dac2269b0b..807fb0dd37 100644
--- a/test/distinct.test
+++ b/test/distinct.test
@@ -168,7 +168,7 @@ foreach {tn sql temptables res} {
   6   "b FROM t1 ORDER BY +b COLLATE binary"          {btree hash} {B b}
   7   "a FROM t1"                                          {}      {A a}
   8   "b COLLATE nocase FROM t1"                           {}      {b}
-  9   "b COLLATE nocase FROM t1 ORDER BY b COLLATE nocase" {}      {b}
+  9   "b COLLATE nocase FROM t1 ORDER BY b COLLATE nocase" {}      {B}
 } {
   do_execsql_test    2.$tn.1 "SELECT DISTINCT $sql" $res
   do_temptables_test 2.$tn.2 "SELECT DISTINCT $sql" $temptables
diff --git a/test/upsert2.test b/test/upsert2.test
new file mode 100644
index 0000000000..74195644fb
--- /dev/null
+++ b/test/upsert2.test
@@ -0,0 +1,170 @@
+# 2018-04-17
+#
+# The author disclaims copyright to this source code.  In place of
+# a legal notice, here is a blessing:
+#
+#    May you do good and not evil.
+#    May you find forgiveness for yourself and forgive others.
+#    May you share freely, never taking more than you give.
+#
+#***********************************************************************
+#
+# Test cases for UPSERT
+
+set testdir [file dirname $argv0]
+source $testdir/tester.tcl
+set testprefix zipfile
+
+do_execsql_test upsert2-100 {
+  CREATE TABLE t1(a INTEGER PRIMARY KEY, b int, c DEFAULT 0);
+  INSERT INTO t1(a,b) VALUES(1,2),(3,4);
+  INSERT INTO t1(a,b) VALUES(1,8),(2,11),(3,1)
+    ON CONFLICT(a) DO UPDATE SET b=excluded.b, c=c+1 WHERE t1.b<excluded.b;
+  SELECT *, 'x' FROM t1 ORDER BY a;
+} {1 8 1 x 2 11 0 x 3 4 0 x}
+do_execsql_test upsert2-110 {
+  DROP TABLE t1;
+  CREATE TABLE t1(a INT PRIMARY KEY, b int, c DEFAULT 0) WITHOUT ROWID;
+  INSERT INTO t1(a,b) VALUES(1,2),(3,4);
+  INSERT INTO t1(a,b) VALUES(1,8),(2,11),(3,1)
+    ON CONFLICT(a) DO UPDATE SET b=excluded.b, c=c+1 WHERE t1.b<excluded.b;
+  SELECT *, 'x' FROM t1 ORDER BY a;
+} {1 8 1 x 2 11 0 x 3 4 0 x}
+
+do_execsql_test upsert2-200 {
+  DROP TABLE t1;
+  CREATE TABLE t1(a INTEGER PRIMARY KEY, b int, c DEFAULT 0);
+  INSERT INTO t1(a,b) VALUES(1,2),(3,4);
+  WITH nx(a,b) AS (VALUES(1,8),(2,11),(3,1),(2,15),(1,4),(1,99))
+  INSERT INTO t1(a,b) SELECT a, b FROM nx WHERE true
+    ON CONFLICT(a) DO UPDATE SET b=excluded.b, c=c+1 WHERE t1.b<excluded.b;
+  SELECT *, 'x' FROM t1 ORDER BY a;
+} {1 99 2 x 2 15 1 x 3 4 0 x}
+do_execsql_test upsert2-201 {
+  DELETE FROM t1;
+  INSERT INTO t1(a,b) VALUES(1,2),(3,4);
+  WITH nx(a,b) AS (VALUES(1,8),(2,11),(3,1),(2,15),(1,4),(1,99))
+  INSERT INTO t1(a,b) AS t2 SELECT a, b FROM nx WHERE true
+    ON CONFLICT(a) DO UPDATE SET b=excluded.b, c=t2.c+1 WHERE t2.b<excluded.b;
+  SELECT *, 'x' FROM t1 ORDER BY a;
+} {1 99 2 x 2 15 1 x 3 4 0 x}
+do_catchsql_test upsert2-202 {
+  WITH nx(a,b) AS (VALUES(1,8),(2,11),(3,1),(2,15),(1,4),(1,99))
+  INSERT INTO t1(a,b) AS t2 SELECT a, b FROM nx WHERE true
+    ON CONFLICT(a) DO UPDATE SET b=excluded.b, c=t1.c+1 WHERE t1.b<excluded.b;
+} {1 {no such column: t1.c}}
+do_execsql_test upsert2-210 {
+  DROP TABLE t1;
+  CREATE TABLE t1(a INT PRIMARY KEY, b int, c DEFAULT 0) WITHOUT ROWID;
+  INSERT INTO t1(a,b) VALUES(1,2),(3,4);
+  WITH nx(a,b) AS (VALUES(1,8),(2,11),(3,1),(2,15),(1,4),(1,99))
+  INSERT INTO t1(a,b) SELECT a, b FROM nx WHERE true
+    ON CONFLICT(a) DO UPDATE SET b=excluded.b, c=c+1 WHERE t1.b<excluded.b;
+  SELECT *, 'x' FROM t1 ORDER BY a;
+} {1 99 2 x 2 15 1 x 3 4 0 x}
+
+# On an ON CONFLICT DO UPDATE, the before-insert, before-update, and
+# after-update triggers fire.
+#
+do_execsql_test upsert2-300 {
+  DROP TABLE t1;
+  CREATE TABLE t1(a INTEGER PRIMARY KEY, b int, c DEFAULT 0);
+  CREATE TABLE record(x TEXT, y TEXT);
+  CREATE TRIGGER r1 BEFORE INSERT ON t1 BEGIN
+    INSERT INTO record(x,y)
+        VALUES('before-insert',printf('%d,%d,%d',new.a,new.b,new.c));
+  END;
+  CREATE TRIGGER r2 AFTER INSERT ON t1 BEGIN
+    INSERT INTO record(x,y)
+        VALUES('after-insert',printf('%d,%d,%d',new.a,new.b,new.c));
+  END;
+  CREATE TRIGGER r3 BEFORE UPDATE ON t1 BEGIN
+    INSERT INTO record(x,y)
+        VALUES('before-update',printf('%d,%d,%d/%d,%d,%d',
+                                      old.a,old.b,old.c,new.a,new.b,new.c));
+  END;
+  CREATE TRIGGER r4 AFTER UPDATE ON t1 BEGIN
+    INSERT INTO record(x,y)
+        VALUES('after-update',printf('%d,%d,%d/%d,%d,%d',
+                                      old.a,old.b,old.c,new.a,new.b,new.c));
+  END;
+  INSERT INTO t1(a,b) VALUES(1,2);
+  DELETE FROM record;
+  INSERT INTO t1(a,b) VALUES(1,2)
+    ON CONFLICT(a) DO UPDATE SET c=t1.c+1;
+  SELECT * FROM record
+} {before-insert 1,2,0 before-update 1,2,0/1,2,1 after-update 1,2,0/1,2,1}
+
+# On an ON CONFLICT DO NOTHING, only the before-insert trigger fires.
+#
+do_execsql_test upsert2-310 {
+  DELETE FROM record;
+  INSERT INTO t1(a,b) VALUES(1,2) ON CONFLICT DO NOTHING;
+  SELECT * FROM record;
+} {before-insert 1,2,0}
+
+# With ON CONFLICT DO UPDATE and a failed WHERE, only the before-insert
+# trigger fires.
+#
+do_execsql_test upsert2-320 {
+  DELETE FROM record;
+  INSERT INTO t1(a,b) VALUES(1,2)
+    ON CONFLICT(a) DO UPDATE SET c=c+1 WHERE c<0;
+  SELECT * FROM record;
+} {before-insert 1,2,0}
+do_execsql_test upsert2-321 {
+  SELECT * FROM t1;
+} {1 2 1}
+
+# Trigger tests repeated for a WITHOUT ROWID table.
+#
+do_execsql_test upsert2-400 {
+  DROP TABLE t1;
+  CREATE TABLE t1(a INT PRIMARY KEY, b int, c DEFAULT 0) WITHOUT ROWID;
+  CREATE TRIGGER r1 BEFORE INSERT ON t1 BEGIN
+    INSERT INTO record(x,y)
+        VALUES('before-insert',printf('%d,%d,%d',new.a,new.b,new.c));
+  END;
+  CREATE TRIGGER r2 AFTER INSERT ON t1 BEGIN
+    INSERT INTO record(x,y)
+        VALUES('after-insert',printf('%d,%d,%d',new.a,new.b,new.c));
+  END;
+  CREATE TRIGGER r3 BEFORE UPDATE ON t1 BEGIN
+    INSERT INTO record(x,y)
+        VALUES('before-update',printf('%d,%d,%d/%d,%d,%d',
+                                      old.a,old.b,old.c,new.a,new.b,new.c));
+  END;
+  CREATE TRIGGER r4 AFTER UPDATE ON t1 BEGIN
+    INSERT INTO record(x,y)
+        VALUES('after-update',printf('%d,%d,%d/%d,%d,%d',
+                                      old.a,old.b,old.c,new.a,new.b,new.c));
+  END;
+  INSERT INTO t1(a,b) VALUES(1,2);
+  DELETE FROM record;
+  INSERT INTO t1(a,b) VALUES(1,2)
+    ON CONFLICT(a) DO UPDATE SET c=t1.c+1;
+  SELECT * FROM record
+} {before-insert 1,2,0 before-update 1,2,0/1,2,1 after-update 1,2,0/1,2,1}
+
+# On an ON CONFLICT DO NOTHING, only the before-insert trigger fires.
+#
+do_execsql_test upsert2-410 {
+  DELETE FROM record;
+  INSERT INTO t1(a,b) VALUES(1,2) ON CONFLICT DO NOTHING;
+  SELECT * FROM record;
+} {before-insert 1,2,0}
+
+# With ON CONFLICT DO UPDATE and a failed WHERE, only the before-insert
+# trigger fires.
+#
+do_execsql_test upsert2-420 {
+  DELETE FROM record;
+  INSERT INTO t1(a,b) VALUES(1,2)
+    ON CONFLICT(a) DO UPDATE SET c=c+1 WHERE c<0;
+  SELECT * FROM record;
+} {before-insert 1,2,0}
+do_execsql_test upsert2-421 {
+  SELECT * FROM t1;
+} {1 2 1}
+
+finish_test
diff --git a/test/upsert3.test b/test/upsert3.test
new file mode 100644
index 0000000000..30a2479326
--- /dev/null
+++ b/test/upsert3.test
@@ -0,0 +1,58 @@
+# 2018-04-17
+#
+# The author disclaims copyright to this source code.  In place of
+# a legal notice, here is a blessing:
+#
+#    May you do good and not evil.
+#    May you find forgiveness for yourself and forgive others.
+#    May you share freely, never taking more than you give.
+#
+#***********************************************************************
+#
+# Test cases for UPSERT
+#
+
+set testdir [file dirname $argv0]
+source $testdir/tester.tcl
+set testprefix zipfile
+
+do_execsql_test upsert3-100 {
+  CREATE TABLE t1(k int, v text);
+  CREATE UNIQUE INDEX x1 ON t1(k, v);
+} {}
+do_catchsql_test upsert3-110 {
+  INSERT INTO t1 VALUES(0,'abcdefghij')
+     ON CONFLICT(k) DO NOTHING;
+} {1 {ON CONFLICT clause does not match any PRIMARY KEY or UNIQUE constraint}}
+do_catchsql_test upsert3-120 {
+  INSERT INTO t1 VALUES(0,'abcdefghij')
+     ON CONFLICT(v) DO NOTHING;
+} {1 {ON CONFLICT clause does not match any PRIMARY KEY or UNIQUE constraint}}
+
+do_execsql_test upsert3-130 {
+  INSERT INTO t1 VALUES(0, 'abcdefghij')
+      ON CONFLICT(k,v) DO NOTHING;
+  SELECT * FROM t1;
+} {0 abcdefghij}
+do_execsql_test upsert3-140 {
+  INSERT INTO t1 VALUES(0, 'abcdefghij')
+      ON CONFLICT(v,k) DO NOTHING;
+  SELECT * FROM t1;
+} {0 abcdefghij}
+
+do_execsql_test upsert3-200 {
+  CREATE TABLE excluded(a INT, b INT, c INT DEFAULT 0);
+  CREATE UNIQUE INDEX excludedab ON excluded(a,b);
+  INSERT INTO excluded(a,b) VALUES(1,2),(1,2),(3,4),(1,2),(5,6),(3,4)
+    ON CONFLICT(b,a) DO UPDATE SET c=excluded.c+1;
+  SELECT *, 'x' FROM excluded ORDER BY a;
+} {1 2 2 x 3 4 1 x 5 6 0 x}
+do_execsql_test upsert3-210 {
+  INSERT INTO excluded(a,b,c) AS base VALUES(1,2,8),(1,2,3)
+    ON CONFLICT(b,a) DO UPDATE SET c=excluded.c+1 WHERE base.c<excluded.c;
+  SELECT *, 'x' FROM excluded ORDER BY a;
+} {1 2 9 x 3 4 1 x 5 6 0 x}
+
+
+
+finish_test
-- 
2.11.4.GIT