MySQL Bugs: #78912: Union Queries via ODBC Fail If Multiple UNION Statements Used

Bug #78912	Union Queries via ODBC Fail If Multiple UNION Statements Used
Submitted:	22 Oct 2015 0:50	Modified:	4 Dec 2015 7:58
Reporter:	Max Hugen	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	Connector / ODBC	Severity:	S2 (Serious)
Version:	5.03.04	OS:	Windows
Assigned to:		CPU Architecture:	Any
Tags:	Union Query

Description:
From MS Access 10, with linked MySQL tables, queries using more than one UNION fail with "error in SQL syntax". eg

SELECT * FROM form_changereject_popup_email_pa
UNION
SELECT * FROM form_changereject_popup_email_cm
UNION 
SELECT * FROM form_changereject_popup_email_pm;

The above example uses Access subqueries, but also fails without subqueries, eg:

SELECT changedocs.DocID FROM changedocs WHERE changedocs.DocNo="123"
UNION
SELECT changedocs.DocID FROM changedocs WHERE changedocs.DocNo="456"
UNION
SELECT changedocs.DocID FROM changedocs WHERE changedocs.DocNo="789";

I've searched but found only bug reports from 2015, eg http://bugs.mysql.com/bug.php?id=7684.

This bug appears to be ongoing - is there any way around it?

How to repeat:
Create any UNION query, using more than one UNION.

Hello Max Hugen,

Thank you for the bug report.
I tried to reproduce the issue at my end using MS Access 2013 with  MySQL Connector / ODBC 5.3.5, but not seeing any issue in using more than one UNION.
Could you please list out exact steps you tried out at your end, this would help us to reproduce the issue at our end.

Thanks,
Chiranjeevi.

Hi Chiranjeevi, sorry, missed your previous reply. I'm using:

MS Access 10
MySQL ODBC 5.2 Unicode Driver 32-bit (5.02.06)
MySQL 5.6.17

I have also tested on a server machine using ODBC 5.03.04, same result.

I've tried joining 3 simple select statements via 2 Unions, and it always returns the same error msg.

Hello Max Hugen,

Thank you for your feedback.
I tried to reproduce the issue at my end using MS Access 2013 with  MySQL Connector / ODBC 5.3.5, but not seeing any issue in using multiple UNION's.
If you can provide more information with exact test case, feel free to add it to this bug and change the status back to 'Open'.

Thank you for your interest in MySQL.

Thanks,
Chiranjeevi.

This bug is not fixed. I am running Access 2013 with Connector/ODBC 5.3.6. Queries with 2 or more UNIONs fail consistently.

Mysql version : 5.5.57
Mysql ODBC : 5.3.10 windows 32bit
Character set : utf8_general_ci

I got a solution. 
------------------------------------
Select ID, No from customs
UNION ALL Select ID, No from payment
UNION ALL Select ID, No from tbl_ballance
------------------------------------

There occurred error as you know, odbc failure and so on with above sqls.
I changed something as follwings. There occured no error.

First field must be text NOT number.
---------------------------------------------
Select ''&ID as ID, No from customs
UNION ALL Select ID, No from payment
UNION ALL Select ID, No from tbl_ballance;
---------------------------------------------
I have no idear about the reason.
I wasted so long time.
I hope to help peoples who is in trouble in multiple UNION

Sorry. more information are belows.
Mysql is backend
msaccess 2007 is front end.

This is still an issue in the very latest community server and ODBC driver

Did anyone find a work around? I ensure that the first column was not numeric and it made no difference