The query should indeed return one row. Sorry I forget to say that. The NOT NULL for id in table t does not make any difference. It was only added to rule out that it might make any difference..
op 28-08-2013 16:29 schreef Marc L. Allen op mlal...@outsitenetworks.com: > 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 _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
