MySql vs MSSQL subquery problem

Hi

We are currently evaluating a migration of data from MsSql to MySql.

I've copied the tables and indexes across and the data is all fine. However, I have a subquery which returns data on MSSQL, but no data on MySQL

the subquery below refers to ordersh.orddate, which is in the main query. When I alter that to a physical date (stored as integer 20080931 etc), the query runs (which isn't helpful to what i'm trying to achieve!).

SELECT DISTINCT ORDERSH.ORDHID
FROM ORDERSD
INNER JOIN ORDERSH ON ORDERSD.ORDHID = ORDERSH.ORDHID
INNER JOIN CUSTOMERMAILINGLIST on ORDERSH.ORDCUSTOMER = CUSTOMERMAILINGLIST.CUSTID
WHERE ORDERSD.ITEMSTATUS = 5 AND (ORDERSH.ORDSOURCE = 'website') and CUSTOMERMAILINGLIST.LISTID IN
(select CATALOGUESENT.MAILINGLISTID
from CATALOGUESENT
where (CATALOGUESENT.CATALOGUEID = 29)
And (CATALOGUESENT.ISADDITIONALLIST = 0)
and ORDERSH.ORDDATE > CATALOGUESENT.DATESENT)

"explain" gives the following output

1 | PRIMARY | ORDERSD | ref | IX_WAREHOSUESALECOUNT,Index_5,pickditem,Index_8,HID,Index_10 | Index_10 | 5 | const | 160082 | Using where | Using temporary
1 | PRIMARY | ORDERSH | eq_ref | PRIMARY,Index_4,Index_5,Index_6 | PRIMARY | 4 | stilettoxt.ORDERSD.ORDHID | 1 | Using where
1 | PRIMARY | CUSTOMERMAILINGLIST | ref | CUSTID_LISTID_IX,CUSTID_IX,Index_4 | CUSTID_LISTID_IX | 5 | stilettoxt.ORDERSH.ORDCUSTOMER | 1 | Using where | Using index | Distinct
2 | DEPENDENT SUBQUERY | CATALOGUESENT | index_subquery | Index_1,Index_2,Index_3 | Index_2 | 15 | func,const,const | 1 | Using where


Any ideas?
Start Free Trial