| # 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 |