Re: Query with strange behaviour

Ria Vandenberghe (rvdbergh@egidius.rug.ac.be) wrote: : We are using Informix-OnLine Dynamic Server 7.10.UD1 and are faced with a : query which shows a very strange behaviour. <snip> : Below 2 variants of the query are shown (only difference: order of the tables : in the FROM-clause!) : Well, I was able to (sort of) reproduce your problem. The order of the tables made no difference, but updating statistics did result the wrong rows returned. I tested this on 7.10.UD1 and on 7.11.UC1, and it reproduced on 7.10.UD1, but NOT on 7.11.UC1. It looks like it might be bug number 40674: SELECT WITH HASHJOIN CONTAINING COMPLEX SUBQUERY RETURNS WRONG RESULTS. (even though your subquery was not complex) This bug is fixed in 7.11.UC1, and 7.11 does NOT reproduce your problem. So you can upgrade to 7.11, or you can set OPTCOMPIND to 0 in your ONCONFIG file -- this causes the optimizer not to choose dynamic hash joins. I tested this as well, and got the right results back. June ---- June Tong Informix Software ---- ---- Senior Consultant (415) 926-6140 ---- ---- International Support junet@informix.com ---- ---- Location-du-jour: Menlo Park ----