Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Add extra test cases for UPSERT. |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA3-256: |
7ea08d0750dd9e07d2ffea3361270348 |
User & Date: | dan 2018-04-18 17:56:37.901 |
Context
2018-04-18
| ||
18:18 | Improved matching of COLLATE clauses within the ON CONFLICT conflict-target. (check-in: 8f4376e52b user: drh tags: trunk) | |
17:56 | Add extra test cases for UPSERT. (check-in: 7ea08d0750 user: dan tags: trunk) | |
17:52 | Fix a problem in the sqlite3ExprCompare() function that caused two dissimilar expressions to match if they have the same "COLLATE name" at the outer layer. (check-in: fb16348a5b user: drh tags: trunk) | |
Changes
Changes to test/upsert4.test.
︙ | ︙ | |||
9 10 11 12 13 14 15 | # #*********************************************************************** # # Test cases for UPSERT set testdir [file dirname $argv0] source $testdir/tester.tcl | | | 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 | # #*********************************************************************** # # Test cases for UPSERT set testdir [file dirname $argv0] source $testdir/tester.tcl set testprefix upsert4 foreach {tn sql} { 1 { CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c UNIQUE) } 2 { CREATE TABLE t1(a INT PRIMARY KEY, b, c UNIQUE) } 3 { CREATE TABLE t1(a INT PRIMARY KEY, b, c UNIQUE) WITHOUT ROWID} } { reset_db |
︙ | ︙ | |||
59 60 61 62 63 64 65 | INSERT INTO t1 VALUES(2, NULL, 'zero') ON CONFLICT (a) DO UPDATE SET c = 'one'; } {1 {UNIQUE constraint failed: t1.c}} do_execsql_test 1.$tn.6 { SELECT * FROM t1; } {1 {} one 2 2 two 3 {} three} | | > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 | INSERT INTO t1 VALUES(2, NULL, 'zero') ON CONFLICT (a) DO UPDATE SET c = 'one'; } {1 {UNIQUE constraint failed: t1.c}} do_execsql_test 1.$tn.6 { SELECT * FROM t1; } {1 {} one 2 2 two 3 {} three} do_execsql_test 1.$tn.7 { INSERT INTO t1 VALUES(2, NULL, 'zero') ON CONFLICT (a) DO UPDATE SET (b, c) = (SELECT 'x', 'y'); SELECT * FROM t1; } {1 {} one 2 x y 3 {} three} do_execsql_test 1.$tn.8 { INSERT INTO t1 VALUES(1, NULL, NULL) ON CONFLICT (a) DO UPDATE SET (c, a) = ('four', 4); SELECT * FROM t1 ORDER BY 1; } {2 x y 3 {} three 4 {} four} } #------------------------------------------------------------------------- # Test target analysis. # set rtbl(0) {0 {}} set rtbl(1) {/1 .*failed.*/} set rtbl(2) {1 {ON CONFLICT clause does not match any PRIMARY KEY or UNIQUE constraint}} foreach {tn sql} { 1 { CREATE TABLE xyz(a INTEGER PRIMARY KEY, b, c, d); CREATE UNIQUE INDEX xyz1 ON xyz(d, c, b COLLATE nocase); } 2 { CREATE TABLE xyz(a INT PRIMARY KEY, b, c, d); CREATE UNIQUE INDEX xyz1 ON xyz(d, c, b COLLATE nocase); } 3 { CREATE TABLE xyz(a INT PRIMARY KEY, b, c, d) WITHOUT ROWID; CREATE UNIQUE INDEX xyz1 ON xyz(d, c, b COLLATE nocase); } } { reset_db execsql $sql do_execsql_test 2.$tn.1 { INSERT INTO xyz VALUES(10, 1, 1, 'one'); } foreach {tn2 oc res} { 1 "ON CONFLICT (b COLLATE nocase, c, d) DO NOTHING" 0 2 "ON CONFLICT (b, c, d) DO NOTHING" 0 3 "ON CONFLICT (b, c COLLATE nocase, d) DO NOTHING" 2 4 "ON CONFLICT (a) DO NOTHING" 1 5 "ON CONFLICT DO NOTHING" 0 6 "ON CONFLICT (b, c, d) WHERE a!=0 DO NOTHING" 0 7 "ON CONFLICT (d, c, c) WHERE a!=0 DO NOTHING" 2 } { do_catchsql_test 2.$tn.2.$tn2 " INSERT INTO xyz VALUES(11, 1, 1, 'one') $oc " $rtbl($res) } do_execsql_test 2.$tn.3 { SELECT * FROM xyz; } {10 1 1 one} } foreach {tn sql} { 1 { CREATE TABLE abc(a INTEGER PRIMARY KEY, x, y); CREATE UNIQUE INDEX abc1 ON abc(('x' || x) COLLATE nocase); } 2 { CREATE TABLE abc(a INT PRIMARY KEY, x, y); CREATE UNIQUE INDEX abc1 ON abc(('x' || x) COLLATE nocase); } 3 { CREATE TABLE abc(a INT PRIMARY KEY, x, y) WITHOUT ROWID; CREATE UNIQUE INDEX abc1 ON abc(('x' || x) COLLATE nocase); } } { reset_db execsql $sql do_execsql_test 2.$tn.1 { INSERT INTO abc VALUES(1, 'one', 'two'); } foreach {tn2 oc res} { 1 "ON CONFLICT DO NOTHING" 0 2 "ON CONFLICT ('x' || x) DO NOTHING" 0 3 "ON CONFLICT (('x' || x) COLLATE nocase) DO NOTHING" 0 4 "ON CONFLICT (('x' || x) COLLATE binary) DO NOTHING" 2 5 "ON CONFLICT (x || 'x') DO NOTHING" 2 6 "ON CONFLICT ((('x' || x))) DO NOTHING" 0 } { do_catchsql_test 2.$tn.2.$tn2 " INSERT INTO abc VALUES(2, 'one', NULL) $oc; " $rtbl($res) } do_execsql_test 2.$tn.3 { SELECT * FROM abc } {1 one two} } finish_test |