Looks like that should return one row, yes? I wonder if operator precedence is broken for that query and the OR is binding higher than the AND. Also possible is that the NOT NULL for id in table t is messing up some query optimization with t2.id NOT NULL.
-----Original Message----- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of E.Pasma Sent: Wednesday, August 28, 2013 10:20 AM To: General Discussion of SQLite Database Subject: [sqlite] Different result from experimental query An experimantal query, involving OUTER JOIN with BETWEEN and JOIN with a combined OR and AND expression, does not return all expected rows. I tried this just after SQLite 3.8.0. was released and found that the issue is particular to this version. At least it is alright in version 3.7.17. Below is a simplified case. This is still complex but if you leave out anything further, the problem no longer occurs. Hope it is useful to report this. .echo on .version SQLite 3.8.0 2013-08-26 04:50:08 f64cd21e2e23ed7cff48f7dafa5e76adde9321c2 CREATE TABLE t (id INTEGER PRIMARY KEY NOT NULL) ; INSERT INTO t VALUES (1) ; SELECT * FROM t t1 LEFT OUTER JOIN t t2 ON t2.id BETWEEN 10 AND 20 JOIN t t3 ON ( t3.id = t1.id OR t2.id IS NOT NULL AND t3.id = t2.id ) ; -- E Pasma _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users This email and any attachments are only for use by the intended recipient(s) and may contain legally privileged, confidential, proprietary or otherwise private information. Any unauthorized use, reproduction, dissemination, distribution or other disclosure of the contents of this e-mail or its attachments is strictly prohibited. If you have received this email in error, please notify the sender immediately and delete the original. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
