Re: Is this a Bug? - Take 2

Barry Leb (barryleb@atl.mindspring.com) 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. Yes, these queries should return different results. Here's what happens (and you can kind of see this happening in the set explain output): (note that I have reversed the order of the selects because that's just the way I think) : select * : from item_master im, outer item_xref ix : where im.item_number = "02124" : and im.item_number = ix.item_number Searches im for rows where item_number = "02124". It's unique, so this returns one row (at most). Now this row is outer joined with ix. If item_number "02124" exists in ix, you get a row that consists of: im.* ix.* If item_number "02124" does not exist in ix, you get a row that consists of: im.* NULL NULL ... (If item_number "02124" does not exist in im, you get no rows at all.) This is one row outer joined with a table. : select * : from item_master im, outer item_xref ix : where ix.item_number = "02124" : and im.item_number = ix.item_number Searches ix for rows where item_number = "02124". It's unique, so this returns one row (at most). Now im is outer joined with this row. This returns exactly as many rows as are in im, all with NULLs in the ix.* columns, except the one row (if any) where im.item_number = "02124". This is a table outer joined with one row. It might help if you think of this as the following TWO queries: select * from item_xref ix where ix.item_number = "02124" into temp t1; select * from item_master im, outer t1 where im.item_number = t1.item_number; The rule is, filters are applied, then the results are outer joined. (IF it was the other way around, outer join THEN filter, you would get the same result from both queries only IF item_number "02124" exists in ix. Otherwise, you STILL wouldn't get the same results.) June ---- June Tong Informix Software ---- ---- Senior Consultant (415) 926-6140 ---- ---- International Support junet@informix.com ---- ---- Location-du-jour: Miami ---- * * Standard disclaimers apply * - Please do not send me requests/questions by mail. When I have the knowledge - and time permits, I try to answer questions on comp.databases.informix, but - travel schedule, time, and volume make responding to personal requests - difficult and often slow. Please call your local Informix Technical Support - organization for assistance with technical issues.