Re: Is this a Bug??? Opinions wanted

Hi Barry, You may have already gotten some explanations - my news server can be very late. It took me a while to notice the difference in the two select statements. I think this is an Informix "feature" or what they consider to be appropriate behavior. Query A: -------- select * from item_master im, outer item_xref ix where im.item_number = "02124" and im.item_number = ix.item_number In this statement the search criteria is specified on the master table and the result should be as expected for outer joins. Query B: -------- select * from item_master im, outer item_xref ix where ix.item_number = "02124" and im.item_number = ix.item_number Here the search criteria is specified on the joined table, which can create a problem because Informix performs the join AFTER the search criteria is applied. In other words, ALL the master rows are outer joined to the set of detail rows that meet the specified criteria. If this is your case, then you have met with the infamous "left outer join" argument with Informix. There is some theoretical logic to it but in practice it is mostly a nuisance. I believe most SQL database systems first outer join the tables, and then apply the search criteria. OPTCOMPIND: ----------- This is a new feature of online (7?) and makes things more complicated. With OPTCOMPIND=0, a so called nested-loop join is performed and the above scenario occurs in good Informix tradition. With OPTCOMPIND=2, the server optimizer decides whether to perform a nested-loop join or to perform a sort-merge or hash join. The latter case apparently, although I haven't tested it, performs the join BEFORE applying the search criteria, and the result can be different. This is unfortunate because now the same query on the same data can return different results based on the optimizer's latest whims, like after a fresh "update statistics". Perhaps some Informix folks can give us some good reasons for this. Maybe they're so caught up in breaking speed records lately that they don't care whether the proper rows are returned, only how fast they arrive. :) Meanwhile, I guess the coding in Query B should be strictly avoided. In other words, never use search criteria on outer joined tables. -- ---------------------------------------------------------------------- John H. Frantz Power-4gl: Extending Informix-4gl frantz@centrum.is http://www.strengur.is/~frantz/pow4gl.html Barry Leb wrote: > > Below is the set explain output from two very similar queries. Please > look carefully at the SQL. These queries do return different results. > Should they? We have differing opinions within our database team. > > To complicate matters even more, if we change the setting of > OPTCOMPIND, the query results change in the first query example to > match those in the second query example. This happens when > OPTCOMPIND=0. > > We are currently running Informix OnLine 7.11.uc1 on a SparCenter > 2000E with Solaris 2.4. The database is approximately 70 gig with > informix mirroring. We extensively fragment our tables and indexes, > and many indexes are detached. > > Any and all opinions are welcome. > > QUERY: > ------ > select * > from item_master im, outer item_xref ix > where ix.item_number = "02124" > and im.item_number = ix.item_number> > Estimated Cost: 58582 > Estimated # of Rows Returned: 1 > Maximum Threads: 1 > > 1) informix.im: SEQUENTIAL SCAN > > 2) informix.ix: INDEX PATH > > Filters: informix.ix.item_number = '02124' > > (1) Index Keys: item_number sales_category > Lower Index Filter: informix.ix.item_number = > informix.im.item_number > > QUERY: > ------ > select * > from item_master im, outer item_xref ix > where im.item_number = "02124" > and im.item_number = ix.item_number> > Estimated Cost: 69 > Estimated # of Rows Returned: 1 > Maximum Threads: 1 > > 1) informix.im: INDEX PATH > > (1) Index Keys: item_number > Lower Index Filter: informix.im.item_number = '02124' > > 2) informix.ix: INDEX PATH > > (1) Index Keys: item_number sales_category > Lower Index Filter: informix.ix.item_number = > informix.im.item_number