Flaky MySQL?
This takes the cake. Same functional parts to the JOINs in the query but in a different order. One returns 0 rows the other 78.
78 rows returned
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
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)
0 rows returned
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
where in the User Manual does it outline this?
David