MySQL Forums
Forum List  »  Newbie

Maybe a bug in the parser?
Posted by: David Wynter
Date: July 27, 2005 04:04AM

I have a query that returns over 900 rows. When I add a OUTER LEFT JOIN to it it returns 0 rows. This surpprises me as I understoood that the OUTER LEFT JOIN should never affect the number of rows returned as it substitutes NULLS for joined columns where the condition is not satisfied.

Before query - 900+ rows

SELECT *
FROM corax_dividends cd INNER JOIN EQUITIES_XREF eq ON cd.RIC = eq.RIC AND
(cd.Div_Feature NOT IN (10, 30, 33) OR (cd.Div_Feature IN (10, 30, 33) AND NOT EXISTS
(SELECT * FROM corax_dividends cd2 WHERE cd.RIC = cd2.RIC AND cd.Div_PILC = cd2.Div_PILC AND cd.Div_Pay_Date = cd2.Div_Pay_Date
AND cd.Ex_Dividend_Date = cd2.Ex_Dividend_Date AND cd2.Div_Feature IN (11, 31, 34))))
INNER JOIN cd_mt564_map mt ON cd.Div_Feature = mt.feature AND cd.Div_Type_Marker = mt.type_marker
INNER JOIN corax_event ev ON ev.RIC = cd.RIC AND ev.Event_Type = 1 AND ev.Div_Market_Lvl_ID = cd.Div_Market_Lvl_ID
LEFT OUTER JOIN corax_capital_change cc ON cc.RIC = cd.RIC AND cd.Div_Type_Marker = 30 AND DATEDIFF(cd.ENT_TIME, cc.ENT_TIME) <2 AND cc.Event_Type = 80 AND cd.Div_Currency <> 'PCT'
LEFT OUTER JOIN corax_capital_change cc2 ON cc2.RIC = cd.RIC AND cd.Div_Type_Marker = 31 AND DATEDIFF(cd.ENT_TIME, cc2.ENT_TIME) <2 AND cc2.Event_Type = 82 AND cd.Div_Currency <> 'PCT'
INNER JOIN customer_watch_list cw ON ev.SECURITY_ID = cw.SecurityId
INNER JOIN customer_portfolio cp ON cw.Portfolio_Id = cp.Portfolio_Id AND ev.Event_Time > cp.Last_Run

After query - 0 rows

SELECT *
FROM corax_dividends cd INNER JOIN EQUITIES_XREF eq ON cd.RIC = eq.RIC AND
(cd.Div_Feature NOT IN (10, 30, 33) OR (cd.Div_Feature IN (10, 30, 33) AND NOT EXISTS
(SELECT * FROM corax_dividends cd2 WHERE cd.RIC = cd2.RIC AND cd.Div_PILC = cd2.Div_PILC AND cd.Div_Pay_Date = cd2.Div_Pay_Date
AND cd.Ex_Dividend_Date = cd2.Ex_Dividend_Date AND cd2.Div_Feature IN (11, 31, 34))))
INNER JOIN cd_mt564_map mt ON cd.Div_Feature = mt.feature AND cd.Div_Type_Marker = mt.type_marker
INNER JOIN corax_event ev ON ev.RIC = cd.RIC AND ev.Event_Type = 1 AND ev.Div_Market_Lvl_ID = cd.Div_Market_Lvl_ID
LEFT OUTER JOIN corax_event ev2 ON ev.RIC = ev2.RIC AND ev.Div_Market_Lvl_ID = ev2.Div_Market_Lvl_Id AND ev2.Event_Id <> ev.Event_Id AND
ev2.Event_Id = (SELECT MAX(ev3.Event_Id) FROM corax_event ev3 WHERE ev2.RIC = ev3.RIC AND ev2.Div_Market_Lvl_Id = ev3.Div_Market_Lvl_Id)
LEFT OUTER JOIN corax_capital_change cc ON cc.RIC = cd.RIC AND cd.Div_Type_Marker = 30 AND DATEDIFF(cd.ENT_TIME, cc.ENT_TIME) <2 AND cc.Event_Type = 80 AND cd.Div_Currency <> 'PCT'
LEFT OUTER JOIN corax_capital_change cc2 ON cc2.RIC = cd.RIC AND cd.Div_Type_Marker = 31 AND DATEDIFF(cd.ENT_TIME, cc2.ENT_TIME) <2 AND cc2.Event_Type = 82 AND cd.Div_Currency <> 'PCT'
INNER JOIN customer_watch_list cw ON ev.SECURITY_ID = cw.SecurityId
INNER JOIN customer_portfolio cp ON cw.Portfolio_Id = cp.Portfolio_Id AND ev.Event_Time > cp.Last_Run

The difference is this

LEFT OUTER JOIN corax_event ev2 ON ev.RIC = ev2.RIC AND ev.Div_Market_Lvl_ID = ev2.Div_Market_Lvl_Id AND ev2.Event_Id <> ev.Event_Id AND
ev2.Event_Id = (SELECT MAX(ev3.Event_Id) FROM corax_event ev3 WHERE ev2.RIC = ev3.RIC AND ev2.Div_Market_Lvl_Id = ev3.Div_Market_Lvl_Id)

Can anyone explain this phenomenon? I also tried it removing the subselect from the ev2 LEFT OUTER JOIN to the where clause, no difference. The interesting thing this exact same LEFT OUTER JOIN for ev2 is used in another slightly different query, which returns rows both with the LEFT OUTER JOIN and without it!

Thx.

David

Options: ReplyQuote


Subject
Written By
Posted
Maybe a bug in the parser?
July 27, 2005 04:04AM
July 28, 2005 01:08AM
July 28, 2005 03:18AM
July 28, 2005 03:27AM
July 28, 2005 09:06AM
July 28, 2005 01:39PM
August 01, 2005 03:25AM
August 01, 2005 03:42AM
August 13, 2005 04:02PM
August 13, 2005 05:08PM
August 13, 2005 05:10PM
August 15, 2005 11:50AM
August 16, 2005 02:07AM
August 16, 2005 02:33AM
August 17, 2005 05:56AM
August 17, 2005 12:16PM
August 19, 2005 01:44AM
August 19, 2005 01:47AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.