Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Add test cases for UPSERT. And a fix for a "REPLACE INTO ... ON CONFLICT" statement where the new row conflicts with both the IPK and the ON CONFLICT indexes. |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA3-256: |
d8eb9f8d9b61400c7e12f01ef5c23325 |
User & Date: | dan 2018-04-20 15:34:08.067 |
Context
2018-04-20
| ||
17:02 | Avoid unnecessary cursor seeking when performing an UPSERT. (check-in: 693a3dcbdd user: drh tags: trunk) | |
15:56 | Avoid unnecessary cursor seeks during upsert processing. (check-in: 7c4b6d5475 user: drh tags: upsert-opt2) | |
15:34 | Add test cases for UPSERT. And a fix for a "REPLACE INTO ... ON CONFLICT" statement where the new row conflicts with both the IPK and the ON CONFLICT indexes. (check-in: d8eb9f8d9b user: dan tags: trunk) | |
13:18 | Enhance UPSERT so that the UPDATE uses the same set of cursors as the INSERT. (check-in: c37f39d18d user: drh tags: trunk) | |
Changes
Changes to src/insert.c.
︙ | ︙ | |||
1492 1493 1494 1495 1496 1497 1498 | */ assert( OE_Update>OE_Replace ); assert( OE_Ignore<OE_Replace ); assert( OE_Fail<OE_Replace ); assert( OE_Abort<OE_Replace ); assert( OE_Rollback<OE_Replace ); if( onError>=OE_Replace | | | 1492 1493 1494 1495 1496 1497 1498 1499 1500 1501 1502 1503 1504 1505 1506 | */ assert( OE_Update>OE_Replace ); assert( OE_Ignore<OE_Replace ); assert( OE_Fail<OE_Replace ); assert( OE_Abort<OE_Replace ); assert( OE_Rollback<OE_Replace ); if( onError>=OE_Replace && (pUpsert || onError!=overrideError) && pTab->pIndex ){ sAddr.ipkTop = sqlite3VdbeAddOp0(v, OP_Goto)+1; } if( isUpdate ){ /* pkChng!=0 does not mean that the rowid has changed, only that |
︙ | ︙ |
Changes to test/upsert4.test.
︙ | ︙ | |||
112 113 114 115 116 117 118 119 120 121 122 123 124 125 | 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 8 "ON CONFLICT (b COLLATE nocase, c COLLATE nocase, d) DO NOTHING" 2 } { do_catchsql_test 2.$tn.2.$tn2 " INSERT INTO xyz VALUES(11, 1, 1, 'one') $oc " $rtbl($res) } | > | 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 | 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 8 "ON CONFLICT (b COLLATE nocase, c COLLATE nocase, d) DO NOTHING" 2 9 "ON CONFLICT (b, c, d) WHERE b==45 DO NOTHING" 0 } { do_catchsql_test 2.$tn.2.$tn2 " INSERT INTO xyz VALUES(11, 1, 1, 'one') $oc " $rtbl($res) } |
︙ | ︙ | |||
140 141 142 143 144 145 146 | 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 | | | | > > > > > > > > > > > > > > > > > > > > > > > | > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 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 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 | 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 3.$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 3.$tn.2.$tn2 " INSERT INTO abc VALUES(2, 'one', NULL) $oc; " $rtbl($res) } do_execsql_test 3.$tn.3 { SELECT * FROM abc } {1 one two} } foreach {tn sql} { 1 { CREATE TABLE abc(a INTEGER PRIMARY KEY, x, y); CREATE UNIQUE INDEX abc1 ON abc(x) WHERE y>0; CREATE UNIQUE INDEX abc2 ON abc(y) WHERE x='xyz' COLLATE nocase; } } { reset_db execsql $sql do_execsql_test 4.$tn.1 { INSERT INTO abc VALUES(1, 'one', 1); INSERT INTO abc VALUES(2, 'two', 2); INSERT INTO abc VALUES(3, 'xyz', 3); INSERT INTO abc VALUES(4, 'XYZ', 4); } foreach {tn2 oc res} { 1 "ON CONFLICT DO NOTHING" 0 2 "ON CONFLICT(x) WHERE y>0 DO NOTHING" 0 3 "ON CONFLICT(x) DO NOTHING" 2 4 "ON CONFLICT(x) WHERE y>=0 DO NOTHING" 2 5 "ON CONFLICT(y) WHERE x='xyz' COLLATE nocase DO NOTHING" 1 } { do_catchsql_test 4.$tn.2.$tn2 " INSERT INTO abc VALUES(5, 'one', 10) $oc " $rtbl($res) } do_execsql_test 4.$tn.3 { SELECT * FROM abc } {1 one 1 2 two 2 3 xyz 3 4 XYZ 4} foreach {tn2 oc res} { 1 "ON CONFLICT DO NOTHING" 0 2 "ON CONFLICT(y) WHERE x='xyz' COLLATE nocase DO NOTHING" 0 3 "ON CONFLICT(y) WHERE x='xyz' COLLATE binary DO NOTHING" 2 4 "ON CONFLICT(x) WHERE y>0 DO NOTHING" 1 } { do_catchsql_test 4.$tn.2.$tn2 " INSERT INTO abc VALUES(5, 'xYz', 3) $oc " $rtbl($res) } } do_catchsql_test 5.0 { CREATE TABLE w1(a INT PRIMARY KEY, x, y); CREATE UNIQUE INDEX w1expr ON w1(('x' || x)); INSERT INTO w1 VALUES(2, 'one', NULL) ON CONFLICT (('x' || x) COLLATE nocase) DO NOTHING; } {1 {ON CONFLICT clause does not match any PRIMARY KEY or UNIQUE constraint}} #------------------------------------------------------------------------- # do_execsql_test 6.0 { CREATE TABLE t1(a INTEGER PRIMARY KEY, b UNIQUE, c); INSERT INTO t1 VALUES(1, 1, 'one'); INSERT INTO t1 VALUES(2, 2, 'two'); INSERT OR REPLACE INTO t1 VALUES(1, 2, 'two') ON CONFLICT(b) DO NOTHING; PRAGMA integrity_check; } {ok} finish_test |