Re: Is this a Bug??? Opinions wanted

Barry, In the first query your filter is on the outer table. The 2 querys will under certain conditions return different results and sometimes they will may give the same. They are *not* the same query. Remember an outer joins always returns something from the outer table. If there is no matching row, null values are returned from the outer table. You really should never put a filter on an outer table. I just had this problem yesterday. Regards, Steve Berg - spb@masada.nb.com On Wed, 19 Jun 1996, Barry Leb wrote: } barryleb@atl.mindspring.com (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. } } snip ... } } >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 } } } } } } }