Re: Is this a Bug??? Opinions wanted

In article <4q9kmo$dhu@mule1.mindspring.com>, Barry Leb <barryleb@atl.mindspring.com> writes >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 > > > > If there are the same number of rows in each table and all the item numbers match across the two tables then yes otherwise no. This is the famous 'so what does outer really mean?' question. OK, you have two tables x and y x has two columns , item_number and cost. y has two columns , item_number and supplier. select x.cost,y.supplier from x,y where x.item_number = y.item_number means Give me all the cost of all items in x and the corresponding supplier from y. If there is no corresponding supplier in y then give me a NULL for the supllier name. Therefore Query one will return the same number of rows as in item_master where item_master.item_number = "02124" . Simple. Query two will return the same number of rows as in item_master where there is an entry in item_xref AND in item_xref the item number is "02124". Now comes the clever bit. If item_master.item_number and item_xref.item_number are of the same type. i.e. One is an integer and one is a char then these results may not be the same. Integer column table1.item_number = "02124" and table1.item_number = table2.item_number (char column). "02124" gets converted to 2124 (integer) table1.item_number = table2.item_number table2.item_number gets converted to integer. So "02124" -> 2124 and matches "002124" -> 2124 and matches "000000000000002124" -> 2124 and matches Char column table2.item_number = "02124" and table2.item_number = table1.item_number (integer column). ONLY table2.item_number = "02124" matches i.e. Only EXACT matches. Not "002124" or "000000000000002124". Solution convert table2.item_number to integer and never store integer values in character columns. PS If this is not the answer than it is a bug. There are known bugs with fragmentation on early 7.1x OnLine. This part of the optimizer which performs fragment elimination was buggy. Sometimes it skipped over fragments which could contain rows matching a search and sometimes it didn't skip over fragments which could not contain matching rows. Get the latest version - 7.13 or 7.2 depending upon your platform. -- David Williams