I want to use a "mutex" technique to execute an "insert if not
exists"-like statement:
INSERT INTO t(a, b)
SELECT 1, 2
FROM mutex LEFT JOIN t
ON a = 1 OR b = 2
WHERE i = 1 AND id IS NULL;
However, sqlite3 3.7.16.2 (2013-04-12 11:52:43) is giving me
inconsistent results (creates duplicate rows in the above case). The
problem only happens if I use an "OR" in the join clause. If I
substitute OR with AND in a situation where they are equivalent the
results come out differently.
The following script demonstrates the problem:
(test.sql)
CREATE TABLE t(id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
a INTEGER, b INTEGER);
CREATE TABLE mutex(i INTEGER);
INSERT INTO mutex(i) VALUES (0);
INSERT INTO mutex(i) VALUES (1);
INSERT INTO t(a, b) VALUES (1, 2);
SELECT * FROM t;
SELECT 'not found (1)'
FROM mutex LEFT JOIN t
ON a = 1 AND b = 2
WHERE i = 1 AND id IS NULL;
SELECT 'not found (2)'
FROM mutex LEFT JOIN t
ON a = 1 OR b = 2
WHERE i = 1 AND id IS NULL;
Running "sqlite3 < test.sql" yields the following results:
1|1|2
not found (2)
I would expect the following:
1|1|2
I *can* reproduce that with sqlite3 3.7.9 2011-11-01 00:52:41 c7c6050e...
I *cannot* reproduce that with sqlite3 3.7.8 2011-09-19 14:49:19 3e0da808...
Tests were done on Ubuntu 13.04 3.8.0-19-generic x86_64.
It looks like 3.7.9 introduced a bug?
Regards,
Romulo Ceccon
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
