Re: Is this a Bug??? Opinions wanted

No, it isn't a bug. It is a consequence of the use of OUTER. It also isn't obvious, but these are two very different queries, with vastly different result sets. The key difference between the queries is whether the filter condition on item_number is on the column in the inner table (item_master) or on the outer table (item_xref). When the filter is applied to the inner table, the query will return the data you expect -- those rows in item_master with an item_number of "02124", with the associated row from the item_xref table if there is one, or nulls if there isn't one. When the filter is applied to the outer table (item_xref), it will return every row in the item_master table (regardless of whether the item_number is "02124"), with the correct data from item_xref for those rows where there is an item_xref with the value "02124" and the item_master item_number is also "02124", but for the other rows it will return nulls for the item_xref columns. This is defined to be the correct behaviour -- it is typically not what you want. So you have to be very careful with OUTER joins. Yours, Jonathan Leffler (johnl@informix.com) #include <disclaimer.h> PS: Who defines it as correct? Informix does. The OUTER notation used is an Informix extension to ANSI SQL. It has always worked this way. It is very unobvious and means that you can get different results depending on whether or not you use intermediate temporary tables, etc. PPS: I reported it as a bug when I first found the behaviour (1987?); the bug report was rejected because the implemented behaviour was deemed correct. }From: barryleb@atl.mindspring.com (Barry Leb) }Date: Wed, 19 Jun 1996 19:35:56 GMT }X-Informix-List-Id: <news.25160> } }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. } }[...] } }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