xref: /sqlite-3.40.0/test/upsert1.test (revision c8a0c90b) 
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
92finish_test
93