Regression: Mixing AND with OR results in no rows returned
(1.1) By Ondrej Dubaj (odubaj) on 2021-04-12 06:51:51 edited from 1.0 [source]
This seems to be a regression since some version of sqlite. I run this statement: $ sqlite3 contacts.db "select * from folder_id_email_list AS el WHERE (el.value IS NOT NULL AND el.value LIKE '%jackson.com')" I get three rows, as expected, regardless of the version being used. When I just repeat the same expression concatenated with an OR, the result differs per version of the sqlite. As it's an OR, I should get the same result as with the above expression. The executed command: $ sqlite3 contacts.db "select * from folder_id_email_list AS el WHERE (el.value IS NOT NULL AND el.value LIKE '%jackson.com') OR (el.value IS NOT NULL AND el.value LIKE '%jackson.com')" a) sqlite-3.33.0-1.fc32.x86_64 - works correctly b) sqlite-3.34.1-1.fc33.x86_64 - works correctly c) sqlite-3.35.4-1.fc35.x86_64 - returns no rows Reproducer available here: https://odubaj.fedorapeople.org/contacts.db Reference: https://bugzilla.redhat.com/show_bug.cgi?id=1947883
(2.1) Originally by Dan Kennedy (dan) with edits by Richard Hipp (drh) on 2021-04-13 18:25:29 from 2.0 in reply to 1.1 [link] [source]
Thanks for reporting this. I think this problem was fixed just a couple of days ago here:
Can you try with the latest SQLite trunk to see if the problem is fixed?
(3) By Dan Kennedy (dan) on 2021-04-12 10:57:50 in reply to 2.0 [link] [source]
Sorry - I visited the bugzilla page but somehow managed to miss the linked reproducer db. This problem is indeed fixed on trunk.
(4) By Ondrej Dubaj (odubaj) on 2021-04-13 08:04:05 in reply to 3 [link] [source]
Great, thanks for clarifying! Is there a planned release with appropiate fix in the near future ?
(5) By Richard Hipp (drh) on 2021-04-13 18:43:34 in reply to 4 [link] [source]
The fix has been cherry-picked on to the "branch-3.35" branch. So if you want the latest SQLite 3.35.x with this fix included, you can pull down the sources to the latest check-in on that branch: