Select with OR show different results
AleV found that two logically equivalent queries on a CUSTOMER table returned different numbers of rows depending only on the order of the predicates in the WHERE clause (originally written with AND, but actually using OR), on Informix 9.21 under Windows 2000. A respondent suggested it was likely a bug in the now-obsolete 9.21 and asked for clarification (row order vs. row count, expected counts, query plans) and whether it reproduced on a newer version. AleV had a colleague run the same queries on Informix 9.30, where the results came out correct, pointing to a 9.21 bug fixed in the later release.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hi, I have realized the SELECT statement shows different results
depending on the order the WHERE is build. For instance, for search
repeated name and lastname:
SELECT NAME, LASTNAME FROM CUSTOMER WHERE (NAME LIKE='JHON' AND
LASTNAME LIKE 'DOE')
shows different results than
SELECT NAME, LASTNAME FROM CUSTOMER WHERE (LASTNAME LIKE 'DOE' AND NAME
LIKE='JHON')
Could you help me with this issue?.
By now I'm developing in Windows 2000 with Informix 9.21.
Thanks a lot.
AleV
could well be a bug in 9.21 that product has been obsolete for more than 12 months do you see the same problem in 9.40 on Windows?
and, when you say "different results" what do you mean? Is it that yoy get the same rows but in a different order? or is it that you get a different number of rows returned? how many rows do youe xepct it to return and which one is right? what is the query paln both of the queries is using? you've said in your title Select with OR show howvere you don't have an OR in either of the 2 queries you posted
First) different results means different numbers of rows Second) for both queries I expect the same number. I don't expect many results, the table is in the import process, I mean, merging tables from different sources. I don't have any clue about the query plan. Third) Good Point!! please replace AND with OR. Thanks
I don't have option to test this issue in other environment. Sorry.
GOOD NEWS! Thanks for your quick help, I ask a colleague to run the real queries, similar to this I wrote here, in an informix 9.30 and guess what??? It works!!. thanks again for the clue!! AleV