Oninit cdi / Archive
Talk to an expert →

Strange impact of parantheses and LIKE operator

Posted in 2000

Topics: General Discussion

hash100@my-deja.com — — source: Informix-list mailing list archive (1991-1998)

Hi, We have informix7.3 in use and we found a strange situation. Our application creates sql statement based on the input from the GUI, and as a precaution, it encloses predicates with parantheses as much as possible. Consider these two very similar statements: (1)select vssmdcom.cr, mrflag, trflag,primarycr from vssmdcom, vssmdafr where ( (vssmdafr.cr = vssmdcom.cr) and ((vssmdcom.cr like 'JK%') and (mrflag = 'Y')) or ((vssmdcom.cr like 'FB%') and (trflag = 'N')) ); (2)select vssmdcom.cr, mrflag, trflag,primarycr from vssmdcom, vssmdafr where (vssmdafr.cr = vssmdcom.cr) and ((vssmdcom.cr like 'JK%') and (mrflag = 'Y')) or ((vssmdcom.cr like 'FB%') and (trflag = 'N')) ; Note, the only diff. is the top level () pair is used in the first one. In the first case I get even those rows where trflag is 'Y' which is wrong. Another strange thing is, even with first query, I can get the correct result just by reversing the order of the conditions: ((vssmdcom.cr like 'FB%') and (trflag = 'N')) to ((trflag = 'N') and (vssmdcom.cr like 'FB%')) So, what's going on ? I couldn't find info. on '()' impact or the precedence of LIKE operator. Any help is appreciated. Thanks -AH Sent via Deja.com http://www.deja.com/ Before you buy.

↪ replying to hash100@my-deja.com
David Williams — — source: Informix-list mailing list archive (1991-1998)

In article <8ig1v5$era$1@nnrp1.deja.com>, hash100@my-deja.com writes >Hi, > >We have informix7.3 in use and we found a strange situation. >Our application creates sql statement based on the input from >the GUI, and as a precaution, it encloses predicates with parantheses >as much as possible. Consider these two very similar statements: > >(1)select vssmdcom.cr, mrflag, trflag,primarycr from vssmdcom, vssmdafr >where ( > (vssmdafr.cr = vssmdcom.cr) and > ((vssmdcom.cr like 'JK%') and (mrflag = 'Y')) > or > ((vssmdcom.cr like 'FB%') and (trflag = 'N')) > ); > >(2)select vssmdcom.cr, mrflag, trflag,primarycr from vssmdcom, vssmdafr >where > (vssmdafr.cr = vssmdcom.cr) and > ((vssmdcom.cr like 'JK%') and (mrflag = 'Y')) > or > ((vssmdcom.cr like 'FB%') and (trflag = 'N')) > ; > >Note, the only diff. is the top level () pair is used in the first one. >In the first case I get even those rows where trflag is 'Y' which is >wrong. >Another strange thing is, even with first query, I can get the >correct result just by reversing the order of the conditions: > ((vssmdcom.cr like 'FB%') and (trflag = 'N')) >to > ((trflag = 'N') and (vssmdcom.cr like 'FB%')) > >So, what's going on ? I couldn't find info. on '()' impact or >the precedence of LIKE operator. >Any help is appreciated. > >Thanks >-AH > > Run the queries in dbaccess with set explain on; <myquery> Check the sqexplain.out file produced. Possibly you have a corrupt index. Use oncheck to check indexes on the tables oncheck -cID mydatabase:mytable > > >Sent via Deja.com http://www.deja.com/ >Before you buy. -- David Williams

↪ replying to hash100@my-deja.com
Jay Buckler — — source: Informix-list mailing list archive (1991-1998)

It looks to me like you should get bad results in both cases since the "or" is at the same level as the join clause. Try the following: select vssmdcom.cr, mrflag, trflag,primarycr from vssmdcom, vssmdafr where vssmdafr.cr = vssmdcom.cr and ((vssmdcom.cr like 'JK%' and mrflag = 'Y') or (vssmdcom.cr like 'FB%' and trflag = 'N')); Jay Buckler <hash100@my-deja.com> wrote in message news:8ig1v5$era$1@nnrp1.deja.com... > Hi, > > We have informix7.3 in use and we found a strange situation. > Our application creates sql statement based on the input from > the GUI, and as a precaution, it encloses predicates with parantheses > as much as possible. Consider these two very similar statements: > > (1)select vssmdcom.cr, mrflag, trflag,primarycr from vssmdcom, vssmdafr > where ( > (vssmdafr.cr = vssmdcom.cr) and > ((vssmdcom.cr like 'JK%') and (mrflag = 'Y')) > or > ((vssmdcom.cr like 'FB%') and (trflag = 'N')) > ); > > (2)select vssmdcom.cr, mrflag, trflag,primarycr from vssmdcom, vssmdafr > where > (vssmdafr.cr = vssmdcom.cr) and > ((vssmdcom.cr like 'JK%') and (mrflag = 'Y')) > or > ((vssmdcom.cr like 'FB%') and (trflag = 'N')) > ; > > Note, the only diff. is the top level () pair is used in the first one. > In the first case I get even those rows where trflag is 'Y' which is > wrong. > Another strange thing is, even with first query, I can get the > correct result just by reversing the order of the conditions: > ((vssmdcom.cr like 'FB%') and (trflag = 'N')) > to > ((trflag = 'N') and (vssmdcom.cr like 'FB%')) > > So, what's going on ? I couldn't find info. on '()' impact or > the precedence of LIKE operator. > Any help is appreciated. > > Thanks > -AH > > > > > Sent via Deja.com http://www.deja.com/ > Before you buy.