diff options
Diffstat (limited to 'test/upsert5.test')
| -rw-r--r-- | test/upsert5.test | 411 |
1 files changed, 411 insertions, 0 deletions
diff --git a/test/upsert5.test b/test/upsert5.test new file mode 100644 index 0000000..3161abf --- /dev/null +++ b/test/upsert5.test @@ -0,0 +1,411 @@ +# 2020-12-11 +# +# 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 generalized UPSERT + +set testdir [file dirname $argv0] +source $testdir/tester.tcl +set testprefix upsert5 + +foreach {tn sql} { + 1 { CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c UNIQUE, d UNIQUE, e UNIQUE) } + 2 { CREATE TABLE t1(a INT PRIMARY KEY, b, c UNIQUE, d UNIQUE, e UNIQUE) } + 3 { CREATE TABLE t1(a INT PRIMARY KEY, b, c UNIQUE, d UNIQUE, e UNIQUE) WITHOUT ROWID} + 4 { CREATE TABLE t1(e UNIQUE, d UNIQUE, c UNIQUE, a INTEGER PRIMARY KEY, b) } + 5 { CREATE TABLE t1(e UNIQUE, d UNIQUE, c UNIQUE, a INT PRIMARY KEY, b) } + 6 { CREATE TABLE t1(e UNIQUE, d UNIQUE, c UNIQUE, a INT PRIMARY KEY, b) WITHOUT ROWID} +} { + reset_db + execsql $sql + + do_execsql_test 1.$tn.100 { + DELETE FROM t1; + INSERT INTO t1(a,b,c,d,e) VALUES(1,2,3,4,5); + INSERT INTO t1(a,b,c,d,e) VALUES(1,NULL,3,4,5) + ON CONFLICT(a) DO UPDATE SET b='a' + ON CONFLICT(c) DO UPDATE SET b='c' + ON CONFLICT(d) DO UPDATE SET b='d' + ON CONFLICT(e) DO UPDATE SET b='e'; + SELECT a,b,c,d,e FROM t1; + } {1 a 3 4 5} + do_execsql_test 1.$tn.101 { + DELETE FROM t1; + INSERT INTO t1(a,b,c,d,e) VALUES(1,2,3,4,5); + INSERT INTO t1(a,b,c,d,e) VALUES(91,NULL,3,4,5) + ON CONFLICT(a) DO UPDATE SET b='a' + ON CONFLICT(c) DO UPDATE SET b='c' + ON CONFLICT(d) DO UPDATE SET b='d' + ON CONFLICT(e) DO UPDATE SET b='e'; + SELECT a,b,c,d,e FROM t1; + } {1 c 3 4 5} + do_execsql_test 1.$tn.102 { + DELETE FROM t1; + INSERT INTO t1(a,b,c,d,e) VALUES(1,2,3,4,5); + INSERT INTO t1(a,b,c,d,e) VALUES(91,NULL,93,4,5) + ON CONFLICT(a) DO UPDATE SET b='a' + ON CONFLICT(c) DO UPDATE SET b='c' + ON CONFLICT(d) DO UPDATE SET b='d' + ON CONFLICT(e) DO UPDATE SET b='e'; + SELECT a,b,c,d,e FROM t1; + } {1 d 3 4 5} + do_execsql_test 1.$tn.103 { + DELETE FROM t1; + INSERT INTO t1(a,b,c,d,e) VALUES(1,2,3,4,5); + INSERT INTO t1(a,b,c,d,e) VALUES(91,NULL,93,94,5) + ON CONFLICT(a) DO UPDATE SET b='a' + ON CONFLICT(c) DO UPDATE SET b='c' + ON CONFLICT(d) DO UPDATE SET b='d' + ON CONFLICT(e) DO UPDATE SET b='e'; + SELECT a,b,c,d,e FROM t1; + } {1 e 3 4 5} + do_execsql_test 1.$tn.200 { + DELETE FROM t1; + INSERT INTO t1(a,b,c,d,e) VALUES(1,2,3,4,5); + INSERT INTO t1(a,b,c,d,e) VALUES(1,NULL,93,94,95) + ON CONFLICT(c) DO UPDATE SET b='c' + ON CONFLICT(a) DO UPDATE SET b='a' + ON CONFLICT(d) DO UPDATE SET b='d' + ON CONFLICT(e) DO UPDATE SET b='e'; + SELECT a,b,c,d,e FROM t1; + } {1 a 3 4 5} + do_execsql_test 1.$tn.201 { + DELETE FROM t1; + INSERT INTO t1(a,b,c,d,e) VALUES(1,2,3,4,5); + INSERT INTO t1(a,b,c,d,e) VALUES(1,NULL,3,94,95) + ON CONFLICT(c) DO UPDATE SET b='c' + ON CONFLICT(a) DO UPDATE SET b='a' + ON CONFLICT(d) DO UPDATE SET b='d' + ON CONFLICT(e) DO UPDATE SET b='e'; + SELECT a,b,c,d,e FROM t1; + } {1 c 3 4 5} + do_execsql_test 1.$tn.202 { + DELETE FROM t1; + INSERT INTO t1(a,b,c,d,e) VALUES(1,2,3,4,5); + INSERT INTO t1(a,b,c,d,e) VALUES(1,NULL,3,4,5) + ON CONFLICT(c) DO UPDATE SET b='c' + ON CONFLICT(a) DO UPDATE SET b='a' + ON CONFLICT(d) DO UPDATE SET b='d' + ON CONFLICT(e) DO UPDATE SET b='e'; + SELECT a,b,c,d,e FROM t1; + } {1 c 3 4 5} + do_execsql_test 1.$tn.203 { + DELETE FROM t1; + INSERT INTO t1(a,b,c,d,e) VALUES(1,2,3,4,5); + INSERT INTO t1(a,b,c,d,e) VALUES(1,NULL,93,94,5) + ON CONFLICT(c) DO UPDATE SET b='c' + ON CONFLICT(a) DO UPDATE SET b='a' + ON CONFLICT(d) DO UPDATE SET b='d' + ON CONFLICT(e) DO UPDATE SET b='e'; + SELECT a,b,c,d,e FROM t1; + } {1 a 3 4 5} + do_execsql_test 1.$tn.204 { + DELETE FROM t1; + INSERT INTO t1(a,b,c,d,e) VALUES(1,2,3,4,5); + INSERT INTO t1(a,b,c,d,e) VALUES(1,NULL,93,4,95) + ON CONFLICT(c) DO UPDATE SET b='c' + ON CONFLICT(a) DO UPDATE SET b='a' + ON CONFLICT(d) DO UPDATE SET b='d' + ON CONFLICT(e) DO UPDATE SET b='e'; + SELECT a,b,c,d,e FROM t1; + } {1 a 3 4 5} + do_execsql_test 1.$tn.210 { + DELETE FROM t1; + INSERT INTO t1(a,b,c,d,e) VALUES(1,2,3,4,5); + INSERT INTO t1(a,b,c,d,e) VALUES(1,NULL,93,94,95) + ON CONFLICT(c) DO UPDATE SET b='c' + ON CONFLICT(d) DO UPDATE SET b='d' + ON CONFLICT(a) DO UPDATE SET b='a' + ON CONFLICT(e) DO UPDATE SET b='e'; + SELECT a,b,c,d,e FROM t1; + } {1 a 3 4 5} + do_execsql_test 1.$tn.211 { + DELETE FROM t1; + INSERT INTO t1(a,b,c,d,e) VALUES(1,2,3,4,5); + INSERT INTO t1(a,b,c,d,e) VALUES(1,NULL,93,4,95) + ON CONFLICT(c) DO UPDATE SET b='c' + ON CONFLICT(d) DO UPDATE SET b='d' + ON CONFLICT(a) DO UPDATE SET b='a' + ON CONFLICT(e) DO UPDATE SET b='e'; + SELECT a,b,c,d,e FROM t1; + } {1 d 3 4 5} + do_execsql_test 1.$tn.212 { + DELETE FROM t1; + INSERT INTO t1(a,b,c,d,e) VALUES(1,2,3,4,5); + INSERT INTO t1(a,b,c,d,e) VALUES(1,NULL,93,94,5) + ON CONFLICT(c) DO UPDATE SET b='c' + ON CONFLICT(d) DO UPDATE SET b='d' + ON CONFLICT(a) DO UPDATE SET b='a' + ON CONFLICT(e) DO UPDATE SET b='e'; + SELECT a,b,c,d,e FROM t1; + } {1 a 3 4 5} + do_execsql_test 1.$tn.213 { + DELETE FROM t1; + INSERT INTO t1(a,b,c,d,e) VALUES(1,2,3,4,5); + INSERT INTO t1(a,b,c,d,e) VALUES(91,NULL,93,94,5) + ON CONFLICT(c) DO UPDATE SET b='c' + ON CONFLICT(d) DO UPDATE SET b='d' + ON CONFLICT(a) DO UPDATE SET b='a' + ON CONFLICT(e) DO UPDATE SET b='e'; + SELECT a,b,c,d,e FROM t1; + } {1 e 3 4 5} + do_execsql_test 1.$tn.214 { + DELETE FROM t1; + INSERT INTO t1(a,b,c,d,e) VALUES(1,2,3,4,5); + INSERT INTO t1(a,b,c,d,e) VALUES(91,NULL,93,94,5) + ON CONFLICT(c) DO UPDATE SET b='c' + ON CONFLICT(d) DO UPDATE SET b='d' + ON CONFLICT(e) DO UPDATE SET b='e' + ON CONFLICT(a) DO UPDATE SET b='a'; + SELECT a,b,c,d,e FROM t1; + } {1 e 3 4 5} + do_execsql_test 1.$tn.215 { + DELETE FROM t1; + INSERT INTO t1(a,b,c,d,e) VALUES(1,2,3,4,5); + INSERT INTO t1(a,b,c,d,e) VALUES(1,NULL,93,94,5) + ON CONFLICT(c) DO UPDATE SET b='c' + ON CONFLICT(d) DO UPDATE SET b='d' + ON CONFLICT(e) DO UPDATE SET b='e' + ON CONFLICT(a) DO UPDATE SET b='a'; + SELECT a,b,c,d,e FROM t1; + } {1 e 3 4 5} + do_execsql_test 1.$tn.216 { + DELETE FROM t1; + INSERT INTO t1(a,b,c,d,e) VALUES(1,2,3,4,5); + INSERT INTO t1(a,b,c,d,e) VALUES(1,NULL,93,94,95) + ON CONFLICT(c) DO UPDATE SET b='c' + ON CONFLICT(d) DO UPDATE SET b='d' + ON CONFLICT(e) DO UPDATE SET b='e' + ON CONFLICT(a) DO UPDATE SET b='a'; + SELECT a,b,c,d,e FROM t1; + } {1 a 3 4 5} + + do_execsql_test 1.$tn.300 { + DELETE FROM t1; + INSERT INTO t1(a,b,c,d,e) VALUES(1,2,3,4,5); + INSERT INTO t1(a,b,c,d,e) VALUES(1,NULL,93,94,95) + ON CONFLICT(c) DO UPDATE SET b='c' + ON CONFLICT(d) DO UPDATE SET b='d' + ON CONFLICT(a) DO UPDATE SET b='a1' + ON CONFLICT(a) DO UPDATE SET b='a2' + ON CONFLICT(a) DO UPDATE SET b='a3' + ON CONFLICT(a) DO UPDATE SET b='a4' + ON CONFLICT(a) DO UPDATE SET b='a5' + ON CONFLICT(e) DO UPDATE SET b='e'; + SELECT a,b,c,d,e FROM t1; + } {1 a1 3 4 5} + do_execsql_test 1.$tn.301 { + DELETE FROM t1; + INSERT INTO t1(a,b,c,d,e) VALUES(1,2,3,4,5); + INSERT INTO t1(a,b,c,d,e) VALUES(91,NULL,93,94,5) + ON CONFLICT(c) DO UPDATE SET b='c' + ON CONFLICT(d) DO UPDATE SET b='d' + ON CONFLICT(a) DO UPDATE SET b='a1' + ON CONFLICT(a) DO UPDATE SET b='a2' + ON CONFLICT(a) DO UPDATE SET b='a3' + ON CONFLICT(a) DO UPDATE SET b='a4' + ON CONFLICT(a) DO UPDATE SET b='a5' + ON CONFLICT(e) DO UPDATE SET b='e'; + SELECT a,b,c,d,e FROM t1; + } {1 e 3 4 5} + + do_execsql_test 1.$tn.400 { + DELETE FROM t1; + INSERT INTO t1(a,b,c,d,e) VALUES(1,2,3,4,5); + INSERT INTO t1(a,b,c,d,e) VALUES(1,NULL,93,94,95) + ON CONFLICT(c) DO UPDATE SET b='c' + ON CONFLICT(d) DO UPDATE SET b='d' + ON CONFLICT DO UPDATE set b='x'; + SELECT a,b,c,d,e FROM t1; + } {1 x 3 4 5} + do_execsql_test 1.$tn.401 { + DELETE FROM t1; + INSERT INTO t1(a,b,c,d,e) VALUES(1,2,3,4,5); + INSERT INTO t1(a,b,c,d,e) VALUES(91,NULL,93,94,5) + ON CONFLICT(c) DO UPDATE SET b='c' + ON CONFLICT(d) DO UPDATE SET b='d' + ON CONFLICT DO UPDATE set b='x'; + SELECT a,b,c,d,e FROM t1; + } {1 x 3 4 5} + do_execsql_test 1.$tn.402 { + DELETE FROM t1; + INSERT INTO t1(a,b,c,d,e) VALUES(1,2,3,4,5); + INSERT INTO t1(a,b,c,d,e) VALUES(1,NULL,93,94,95) + ON CONFLICT(c) DO UPDATE SET b='c' + ON CONFLICT(d) DO UPDATE SET b='d' + ON CONFLICT DO UPDATE set b='x'; + SELECT a,b,c,d,e FROM t1; + } {1 x 3 4 5} + do_execsql_test 1.$tn.403 { + DELETE FROM t1; + INSERT INTO t1(a,b,c,d,e) VALUES(1,2,3,4,5); + INSERT INTO t1(a,b,c,d,e) VALUES(91,NULL,3,94,95) + ON CONFLICT(c) DO UPDATE SET b='c' + ON CONFLICT(d) DO UPDATE SET b='d' + ON CONFLICT DO UPDATE set b='x'; + SELECT a,b,c,d,e FROM t1; + } {1 c 3 4 5} + do_execsql_test 1.$tn.404 { + DELETE FROM t1; + INSERT INTO t1(a,b,c,d,e) VALUES(1,2,3,4,5); + INSERT INTO t1(a,b,c,d,e) VALUES(91,NULL,3,4,95) + ON CONFLICT(c) DO UPDATE SET b='c' + ON CONFLICT(d) DO UPDATE SET b='d' + ON CONFLICT DO UPDATE set b='x'; + SELECT a,b,c,d,e FROM t1; + } {1 c 3 4 5} + do_execsql_test 1.$tn.405 { + DELETE FROM t1; + INSERT INTO t1(a,b,c,d,e) VALUES(1,2,3,4,5); + INSERT INTO t1(a,b,c,d,e) VALUES(1,NULL,93,4,5) + ON CONFLICT(c) DO UPDATE SET b='c' + ON CONFLICT(d) DO UPDATE SET b='d' + ON CONFLICT DO UPDATE set b='x'; + SELECT a,b,c,d,e FROM t1; + } {1 d 3 4 5} + + do_execsql_test 1.$tn.410 { + DELETE FROM t1; + INSERT INTO t1(a,b,c,d,e) VALUES(1,2,3,4,5); + INSERT INTO t1(a,b,c,d,e) VALUES(1,NULL,93,94,95) + ON CONFLICT DO UPDATE set b='x'; + SELECT a,b,c,d,e FROM t1; + } {1 x 3 4 5} + do_execsql_test 1.$tn.411 { + DELETE FROM t1; + INSERT INTO t1(a,b,c,d,e) VALUES(1,2,3,4,5); + INSERT INTO t1(a,b,c,d,e) VALUES(91,NULL,93,94,5) + ON CONFLICT DO UPDATE set b='x'; + SELECT a,b,c,d,e FROM t1; + } {1 x 3 4 5} + do_execsql_test 1.$tn.412 { + DELETE FROM t1; + INSERT INTO t1(a,b,c,d,e) VALUES(1,2,3,4,5); + INSERT INTO t1(a,b,c,d,e) VALUES(91,NULL,93,4,95) + ON CONFLICT DO UPDATE set b='x'; + SELECT a,b,c,d,e FROM t1; + } {1 x 3 4 5} + do_execsql_test 1.$tn.413 { + DELETE FROM t1; + INSERT INTO t1(a,b,c,d,e) VALUES(1,2,3,4,5); + INSERT INTO t1(a,b,c,d,e) VALUES(91,NULL,3,94,95) + ON CONFLICT DO UPDATE set b='x'; + SELECT a,b,c,d,e FROM t1; + } {1 x 3 4 5} + + do_execsql_test 1.$tn.420 { + DELETE FROM t1; + INSERT INTO t1(a,b,c,d,e) VALUES(1,2,3,4,5); + INSERT INTO t1(a,b,c,d,e) VALUES(1,NULL,93,94,95) + ON CONFLICT(c) DO NOTHING + ON CONFLICT(d) DO NOTHING + ON CONFLICT DO UPDATE set b='x'; + SELECT a,b,c,d,e FROM t1; + } {1 x 3 4 5} + do_execsql_test 1.$tn.421 { + DELETE FROM t1; + INSERT INTO t1(a,b,c,d,e) VALUES(1,2,3,4,5); + INSERT INTO t1(a,b,c,d,e) VALUES(91,NULL,93,94,5) + ON CONFLICT(c) DO NOTHING + ON CONFLICT(d) DO NOTHING + ON CONFLICT DO UPDATE set b='x'; + SELECT a,b,c,d,e FROM t1; + } {1 x 3 4 5} + do_execsql_test 1.$tn.422 { + DELETE FROM t1; + INSERT INTO t1(a,b,c,d,e) VALUES(1,2,3,4,5); + INSERT INTO t1(a,b,c,d,e) VALUES(91,NULL,93,4,95) + ON CONFLICT(c) DO NOTHING + ON CONFLICT(d) DO NOTHING + ON CONFLICT DO UPDATE set b='x'; + SELECT a,b,c,d,e FROM t1; + } {1 2 3 4 5} + do_execsql_test 1.$tn.423 { + DELETE FROM t1; + INSERT INTO t1(a,b,c,d,e) VALUES(1,2,3,4,5); + INSERT INTO t1(a,b,c,d,e) VALUES(91,NULL,3,94,95) + ON CONFLICT(c) DO NOTHING + ON CONFLICT(d) DO NOTHING + ON CONFLICT DO UPDATE set b='x'; + SELECT a,b,c,d,e FROM t1; + } {1 2 3 4 5} + + do_execsql_test 1.$tn.500 { + DELETE FROM t1; + INSERT INTO t1(a,b,c,d,e) VALUES(1,2,3,4,5); + INSERT INTO t1(a,b,c,d,e) VALUES(1,NULL,93,94,95) + ON CONFLICT(c) DO UPDATE SET b='c' + ON CONFLICT(d) DO UPDATE SET b='d' + ON CONFLICT DO NOTHING; + SELECT a,b,c,d,e FROM t1; + } {1 2 3 4 5} + do_execsql_test 1.$tn.501 { + DELETE FROM t1; + INSERT INTO t1(a,b,c,d,e) VALUES(1,2,3,4,5); + INSERT INTO t1(a,b,c,d,e) VALUES(91,NULL,93,94,5) + ON CONFLICT(c) DO UPDATE SET b='c' + ON CONFLICT(d) DO UPDATE SET b='d' + ON CONFLICT DO NOTHING; + SELECT a,b,c,d,e FROM t1; + } {1 2 3 4 5} + do_execsql_test 1.$tn.502 { + DELETE FROM t1; + INSERT INTO t1(a,b,c,d,e) VALUES(1,2,3,4,5); + INSERT INTO t1(a,b,c,d,e) VALUES(1,NULL,93,94,95) + ON CONFLICT(c) DO UPDATE SET b='c' + ON CONFLICT(d) DO UPDATE SET b='d' + ON CONFLICT DO NOTHING; + SELECT a,b,c,d,e FROM t1; + } {1 2 3 4 5} + do_execsql_test 1.$tn.503 { + DELETE FROM t1; + INSERT INTO t1(a,b,c,d,e) VALUES(1,2,3,4,5); + INSERT INTO t1(a,b,c,d,e) VALUES(91,NULL,3,94,95) + ON CONFLICT(c) DO UPDATE SET b='c' + ON CONFLICT(d) DO UPDATE SET b='d' + ON CONFLICT DO NOTHING; + SELECT a,b,c,d,e FROM t1; + } {1 c 3 4 5} + do_execsql_test 1.$tn.504 { + DELETE FROM t1; + INSERT INTO t1(a,b,c,d,e) VALUES(1,2,3,4,5); + INSERT INTO t1(a,b,c,d,e) VALUES(91,NULL,3,4,95) + ON CONFLICT(c) DO UPDATE SET b='c' + ON CONFLICT(d) DO UPDATE SET b='d' + ON CONFLICT DO NOTHING; + SELECT a,b,c,d,e FROM t1; + } {1 c 3 4 5} + do_execsql_test 1.$tn.505 { + DELETE FROM t1; + INSERT INTO t1(a,b,c,d,e) VALUES(1,2,3,4,5); + INSERT INTO t1(a,b,c,d,e) VALUES(1,NULL,93,4,5) + ON CONFLICT(c) DO UPDATE SET b='c' + ON CONFLICT(d) DO UPDATE SET b='d' + ON CONFLICT DO NOTHING; + SELECT a,b,c,d,e FROM t1; + } {1 d 3 4 5} + +} + +#-------------------------------------------------------------------------- +reset_db +do_execsql_test 2.0 { + CREATE TABLE t2(a, b, c REAL, d, e, PRIMARY KEY(a,b)) WITHOUT ROWID; + CREATE UNIQUE INDEX t2c ON t2(c); +} + +do_catchsql_test 2.1 { + INSERT INTO t2(a,b,c,e,d) VALUES(1,2,3,4,5) + ON CONFLICT(c) DO UPDATE SET b='' + ON CONFLICT((SELECT t2 FROM nosuchtable)) DO NOTHING; + +} {1 {no such table: nosuchtable}} + +finish_test |