MySQL Bugs: #52334: UNION via ODBC returns zero rows

Bug #52334	UNION via ODBC returns zero rows
Submitted:	24 Mar 2010 15:19	Modified:	29 May 2013 6:04
Reporter:	Daniel Kram	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / ODBC	Severity:	S2 (Serious)
Version:	5.1.5	OS:	Any
Assigned to:	Lawrenty Novitsky	CPU Architecture:	Any
Tags:	ODBC union SQL

Description:
I have an archive table and a live table. Mostly we refer to the live table (current year), however, occasional access to the archive table (prior years) is necessary.

A SELECT from either of the tables on their own returns data okay through the ODBC connection (ODBC Version 5.1.6 8/18/2009.

64-bit Community server running on Vista 64-bit Pro (SP2) box

When the two SELECT statements are UNION'ed, the result set is empty. Even though if I paste the SQL into a Query Browser, data is returned.

How to repeat:
SELECT * FROM tblstoresales SS LEFT OUTER JOIN tblstoresalesconfig SSC ON SS.idtblstoresalesconfig = SSC.idtblstoresalesconfig LEFT OUTER JOIN tblcategories CT ON SSC.idtblcategories = CT.idtblcategories WHERE SS.TerminalID IN('000','002','016','019','020','023','026','027','031','032','033','035','038','039','044','045','051','055','056','058','061','065','070','077','090','092','099','111','130','149','800','826','841','999') AND SS.DateLastUpdated  BETWEEN '2009-12-28 0:0:0' AND '2010-1-3 23:59:59' ORDER BY SS.DateLastUpdated

Both Above and below work fine on their own, however, union together and the dataset shows no rows found.

SELECT * FROM tblstoresales_archive SS LEFT OUTER JOIN tblstoresalesconfig SSC ON SS.idtblstoresalesconfig = SSC.idtblstoresalesconfig LEFT OUTER JOIN tblcategories CT ON SSC.idtblcategories = CT.idtblcategories WHERE SS.TerminalID IN('000','002','016','019','020','023','026','027','031','032','033','035','038','039','044','045','051','055','056','058','061','065','070','077','090','092','099','111','130','149','800','826','841','999') AND SS.DateLastUpdated  BETWEEN '2009-12-28 0:0:0' AND '2010-1-3 23:59:59' ORDER BY SS.DateLastUpdated)

UNION'ed:

(SELECT * FROM tblstoresales SS LEFT OUTER JOIN tblstoresalesconfig SSC ON SS.idtblstoresalesconfig = SSC.idtblstoresalesconfig LEFT OUTER JOIN tblcategories CT ON SSC.idtblcategories = CT.idtblcategories WHERE SS.TerminalID IN('000','002','016','019','020','023','026','027','031','032','033','035','038','039','044','045','051','055','056','058','061','065','070','077','090','092','099','111','130','149','800','826','841','999') AND SS.DateLastUpdated  BETWEEN '2009-12-28 0:0:0' AND '2010-1-3 23:59:59' ORDER BY SS.DateLastUpdated) UNION (SELECT * FROM tblstoresales_archive SS LEFT OUTER JOIN tblstoresalesconfig SSC ON SS.idtblstoresalesconfig = SSC.idtblstoresalesconfig LEFT OUTER JOIN tblcategories CT ON SSC.idtblcategories = CT.idtblcategories WHERE SS.TerminalID IN('000','002','016','019','020','023','026','027','031','032','033','035','038','039','044','045','051','055','056','058','061','065','070','077','090','092','099','111','130','149','800','826','841','999') AND SS.DateLastUpdated  BETWEEN '2009-12-28 0:0:0' AND '2010-1-3 23:59:59' ORDER BY SS.DateLastUpdated)

Suggested fix:
Help

Hi Daniel and thanks for your report.

Can you please:
  o Attach full test case along with DDL/DML statements so I can try.
  o Attach DM ODBC trace file (see documentation on how to enable ODBC tracing)
  o Attach general query log from MySQL server so we can see what actually got to server.

If you can, please (download and) run odbct32w.exe (odbcte32.exe for c/ODBC 3.51) program. This is a default Microsoft ODBC client. Try running your query there. ODBCTE is a part of VisualStudio install, usually located in C:\program files (x86)\microsoft visual studio 8\VC\PlatformSDK\Bin.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

I'm closing this bug because I can not continue without feedback from the reporter. If you have new info, please reopen the report.