Regression: Mixing AND with OR results in no rows returned
(1) By Ondrej Dubaj (odubaj) on 2021-04-12 06:30:51 updated by 1.1 [link] [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
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 [link] [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) By Dan Kennedy (dan) on 2021-04-12 10:52:10 in reply to 1.1 updated by 2.1 [source]
Thanks for reporting this. I think this problem was fixed just a couple of days ago here: ()[https://sqlite.org/src/info/40852ca8e215e51f] Can you try with the latest SQLite trunk to see if the problem is fixed? Cheers, Dan.
(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:
https://sqlite.org/src/info/40852ca8e215e51f
Can you try with the latest SQLite trunk to see if the problem is fixed?
Cheers,
Dan.
(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.
Dan.
(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:
(6) By anonymous on 2021-04-13 23:20:16 in reply to 5 updated by 6.1 [link] [source]
Looking at the check-ins since version 3.35.4 I notice a few that look like cherry-picking candidates for version 3.35.5 - for example check-in 778a9a6e6f or 35cf295e02. Any chance of these making it to 3.35.5 or will they be queued for 3.36.0?
(6.1) Originally by anonymous with edits by Richard Hipp (drh) on 2021-04-13 23:44:45 from 6.0 in reply to 5 [link] [source]
Looking at the check-ins since version 3.35.4 I notice a few that look like cherry-picking candidates for version 3.35.5 - for example check-in 778a9a6e6f or 35cf295e02.
Any chance of these making it to 3.35.5 or will they be queued for 3.36.0?