Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | Add tests for name resolution in ON CONFLICT clauses. |
|---|---|
| Downloads: | Tarball | ZIP archive | SQL archive |
| Timelines: | family | ancestors | descendants | both | trunk |
| Files: | files | file ages | folders |
| SHA3-256: |
cf253584ecf7aed04406b4bae78b5368 |
| User & Date: | dan 2018-04-20 17:50:49 |
Context
|
2018-04-20
| ||
| 18:01 | Fix a VDBE comment on upsert. Provide an error message when upsert detects index corruption. (check-in: 279c48f6 user: drh tags: trunk) | |
| 17:50 | Add tests for name resolution in ON CONFLICT clauses. (check-in: cf253584 user: dan tags: trunk) | |
| 17:02 | Avoid unnecessary cursor seeking when performing an UPSERT. (check-in: 693a3dcb user: drh tags: trunk) | |
Changes
Changes to test/upsert4.test.
215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 |
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
|
> > < > > | > > > > > > > > > > > | | | | | | < > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > |
215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332 333 334 335 336 337 338 339 340 341 342 343 344 345 346 347 348 349 350 351 352 353 354 355 356 357 358 359 360 361 362 363 364 365 |
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}}
#-------------------------------------------------------------------------
# Test that ON CONFLICT constraint processing occurs before any REPLACE
# constraint processing.
#
foreach {tn sql} {
1 {
CREATE TABLE t1(a INTEGER PRIMARY KEY, b UNIQUE, c);
}
2 {
CREATE TABLE t1(a INT PRIMARY KEY, b UNIQUE, c);
}
3 {
CREATE TABLE t1(a INT PRIMARY KEY, b UNIQUE, c) WITHOUT ROWID;
}
} {
reset_db
execsql $sql
do_execsql_test 6.1.$tn {
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}
}
foreach {tn sql} {
1 {
CREATE TABLE t1(a INTEGER PRIMARY KEY, b UNIQUE, c UNIQUE);
}
} {
reset_db
execsql $sql
do_execsql_test 6.2.$tn.1 {
INSERT INTO t1 VALUES(1, 1, 1);
INSERT INTO t1 VALUES(2, 2, 2);
}
do_execsql_test 6.2.$tn.2 {
INSERT OR REPLACE INTO t1 VALUES(3, 1, 1) ON CONFLICT(b) DO NOTHING;
SELECT * FROM t1;
PRAGMA integrity_check;
} {1 1 1 2 2 2 ok}
do_execsql_test 6.2.$tn.3 {
INSERT OR REPLACE INTO t1 VALUES(3, 2, 2) ON CONFLICT(c) DO NOTHING;
SELECT * FROM t1;
PRAGMA integrity_check;
} {1 1 1 2 2 2 ok}
do_execsql_test 6.2.$tn.2 {
INSERT OR REPLACE INTO t1 VALUES(3, 1, 1) ON CONFLICT(b)
DO UPDATE SET b=b||'x';
SELECT * FROM t1;
PRAGMA integrity_check;
} {1 1x 1 2 2 2 ok}
do_execsql_test 6.2.$tn.2 {
INSERT OR REPLACE INTO t1 VALUES(3, 2, 2) ON CONFLICT(c)
DO UPDATE SET c=c||'x';
SELECT * FROM t1;
PRAGMA integrity_check;
} {1 1x 1 2 2 2x ok}
}
#-------------------------------------------------------------------------
# Test references to "excluded". And using an alias in an INSERT
# statement.
#
foreach {tn sql} {
1 {
CREATE TABLE t1(w, x, y, z, PRIMARY KEY(x, y));
CREATE UNIQUE INDEX zz ON t1(z);
}
2 {
CREATE TABLE t1(w, x, y, z, PRIMARY KEY(x, y)) WITHOUT ROWID;
CREATE UNIQUE INDEX zz ON t1(z);
}
} {
reset_db
execsql $sql
do_execsql_test 7.$tn.0 {
INSERT INTO t1 VALUES('a', 1, 1, 1);
INSERT INTO t1 VALUES('b', 2, 2, 2);
}
do_execsql_test 7.$tn.1 {
INSERT INTO t1 VALUES('c', 3, 3, 1) ON CONFLICT(z)
DO UPDATE SET w = excluded.w;
SELECT * FROM t1;
} {c 1 1 1 b 2 2 2}
do_execsql_test 7.$tn.2 {
INSERT INTO t1 VALUES('c', 2, 2, 3) ON CONFLICT(y, x)
DO UPDATE SET w = w||w;
SELECT * FROM t1;
} {c 1 1 1 bb 2 2 2}
do_execsql_test 7.$tn.3 {
INSERT INTO t1 VALUES('c', 2, 2, 3) ON CONFLICT(y, x)
DO UPDATE SET w = w||t1.w;
SELECT * FROM t1;
} {c 1 1 1 bbbb 2 2 2}
do_execsql_test 7.$tn.4 {
INSERT INTO t1 AS tbl VALUES('c', 2, 2, 3) ON CONFLICT(y, x)
DO UPDATE SET w = w||tbl.w;
SELECT * FROM t1;
} {c 1 1 1 bbbbbbbb 2 2 2}
}
foreach {tn sql} {
1 {
CREATE TABLE excluded(w, x, 'a b', z, PRIMARY KEY(x, 'a b'));
CREATE UNIQUE INDEX zz ON excluded(z);
}
2 {
CREATE TABLE excluded(w, x, 'a b', z, PRIMARY KEY(x, 'a b')) WITHOUT ROWID;
CREATE UNIQUE INDEX zz ON excluded(z);
}
} {
reset_db
execsql $sql
do_execsql_test 8.$tn.0 {
INSERT INTO excluded VALUES('a', 1, 1, 1);
INSERT INTO excluded VALUES('b', 2, 2, 2);
}
# Note: An error in Postgres: "table reference "excluded" is ambiguous".
#
do_execsql_test 8.$tn.1 {
INSERT INTO excluded VALUES('hello', 1, 1, NULL) ON CONFLICT(x, "a b")
DO UPDATE SET w=excluded.w;
SELECT * FROM excluded;
} {a 1 1 1 b 2 2 2}
do_execsql_test 8.$tn.2 {
INSERT INTO excluded AS x1 VALUES('hello', 1, 1, NULL) ON CONFLICT(x, [a b])
DO UPDATE SET w=excluded.w;
SELECT * FROM excluded;
} {hello 1 1 1 b 2 2 2}
}
finish_test
|