MySQL Bugs: #7684: more than two UNION SELECT statement problem (with MS Access)

Bug #7684	more than two UNION SELECT statement problem (with MS Access)
Submitted:	5 Jan 2005 11:15	Modified:	20 Feb 2016 18:42
Reporter:	Mam narakka	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: Parser	Severity:	S3 (Non-critical)
Version:	5.0, probably all, 5.1, 4.1	OS:	Any (Windows XP)
Assigned to:		CPU Architecture:	Any
Tags:	affects_connectors, unscheduled

Description:
Running MSAccess97 and MySQL 4.1.7nt on same machine. Testing MSAccess as
a feasable front-end with MySQL as backend. Tables are linked using ODBC driver
3.51.

Attempt is made to run a union query with more then two "UNION SELECT" statements (see query bellow). causes a "syntax error message"  #1064. 
However:
1)The same syntax works fine in MSAccess alone (non-linked tables).
2)The same syntax works as a pass-through query to MySQL.

How to repeat:
Query:
(SELECT Cheque.DateOperation, Cheque.codeSecour, Cheque.Montant, Cheque.RefDemande
FROM Cheque WHERE Cheque.estEdite=1)
union ALL
(SELECT Autres.DateOperation, Autres.codeSecour, Autres.Montant, Autres.RefDemande
FROM Autres)
UNION ALL (SELECT Virement.DateOperation, Virement.codeSecour, Virement.Montant, Virement.RefDemande
FROM Virement
WHERE Virement.estEffectue=1);

I tried to run the query with only 2 UNION Select:
1 st possibility
(SELECT Cheque.DateOperation, Cheque.codeSecour, Cheque.Montant, Cheque.RefDemande
FROM Cheque WHERE Cheque.estEdite=1)
union ALL
(SELECT Autres.DateOperation, Autres.codeSecour, Autres.Montant, Autres.RefDemande
FROM Autres)
2 st possibility
(SELECT Cheque.DateOperation, Cheque.codeSecour, Cheque.Montant, Cheque.RefDemande
FROM Cheque WHERE Cheque.estEdite=1)
UNION ALL (SELECT Virement.DateOperation, Virement.codeSecour, Virement.Montant, Virement.RefDemande
FROM Virement
WHERE Virement.estEffectue=1);

for both possibilties,it works.

Hi, is it possible for you to enable the General Log (by adding 'log' to the [mysqld] section in the my.ini file or by starting the server with '--log' option)?

The General Log logs all queries sent to the server, including failed ones, so this would allow to exactly analyze what causes the syntax error in the code generated by Access.

Hi!

That kind of query works just fine in MySQL server.

Please try execute it from mysql command-line program  (mysql.exe) and see if it works. 

Let us know. 

Besides, let us know a full error message that you get . There should be some text after error 1064.

We need this info in order to find out where is a cause of the problem.

Thanks in advance.

Thank you in advance.
As you said, I tried to execute my request from mysql command-line program. It work’s.

mysql> (SELECT Cheque.DateOperation, Cheque.codeSecour, Cheque.Montant, Cheque.R
efDemande
    -> FROM Cheque WHERE Cheque.estEdite=1)
    -> union ALL
    -> (SELECT Autres.DateOperation, Autres.codeSecour, Autres.Montant, Autres.R
efDemande
    -> FROM Autres)
    -> UNION ALL (SELECT Virement.DateOperation, Virement.codeSecour, Virement.M
ontant, Virement.RefDemande
    -> FROM Virement
    -> WHERE Virement.estEffectue=1);
+---------------------+------------+---------+------------+
| DateOperation       | codeSecour | Montant | RefDemande |
+---------------------+------------+---------+------------+
| 2004-12-17 00:00:00 | EHPAD      |     364 | 2004/00004 |
| 2004-12-30 00:00:00 | SECOURS    |      78 | 2004/00020 |
| 2005-01-03 00:00:00 | EHPAD      |     158 | 2004/00004 |
| 2005-01-03 00:00:00 | EHPAD      |     158 | 2004/00004 |
| 2005-01-03 00:00:00 | EHPAD      |     158 | 2004/00004 |
| 2005-01-03 00:00:00 | EHPAD      |     188 | 2004/00003 |
| 2004-12-16 00:00:00 | AMDS       |      70 | 2004/00002 |
| 2004-12-16 00:00:00 | AMDS       |      14 | 2004/00002 |
| 2004-12-16 00:00:00 | EHPAD      |     182 | 2004/00003 |
| 2004-12-17 00:00:00 | EHPAD      |   912.5 | 2004/00004 |
| 2004-12-17 00:00:00 | AMDS       |     150 | 2004/00002 |
| 2004-12-22 00:00:00 | TRAFAM     |   50.62 | 2004/00009 |
| 2004-12-23 00:00:00 | GARDOMV    |      30 | 2004/00011 |
| 2004-12-23 00:00:00 | GARDOMM    |     158 | 2004/00012 |
+---------------------+------------+---------+------------+
14 rows in set (0.00 sec)

From access , I got two message :
the first one :
ODBC – l’appel à échoué
It’s in French (my OS is a french version), it means ODBC -  Call failure

The second message :
[MySQL][ODBC 3.51 Driver][mysqld-4.1.7-nt-long]You have an error in your SQL syntax ; check the ma nanual that corresponds to your MySQL server version for the right syntax to use near ‘(SELECT `DateOperation`,`codeSecour`,`Montant`,`RefDemande` FROM `cheque`WH’ at line 1 (#1064)
 
Also, I enable the General log and I tried request from Access and mySQL command line.
Herse the log :

C:\Program Files\MySQL\MySQL Server 4.1\bin\mysqld-nt, Version: 4.1.7-nt-log. started with:
TCP Port: 3306, Named Pipe: MySQL
Time                 Id Command    Argument
050106 13:53:45	      1 Connect     user@localhost on atals
		      1 Query       ((SELECT `DateOperation` ,`codeSecour` ,`Montant` ,`RefDemande`  FROM `cheque` WHERE (`estEdite` = 1 ) ) UNION ALL (SELECT `DateOperation` ,`codeSecour` ,`Montant` ,`RefDemande`  FROM `autres` )) UNION ALL (SELECT `DateOperation` ,`codeSecour` ,`Montant` ,`RefDemande`  FROM `virement` WHERE (`estEffectue` = 1 ) )
050106 13:54:19	      2 Connect     user@localhost on 
050106 13:54:29	      3 Connect     root@localhost on 
		      3 Query       SET SQL_SELECT_LIMIT=1000
		      3 Query       SHOW DATABASES
		      3 Init DB     atals
		      3 Query       SHOW TABLE STATUS FROM `atals`
050106 13:54:35	      2 Query       SELECT DATABASE()
		      2 Init DB     atals
050106 13:54:40	      2 Query       (SELECT Cheque.DateOperation, Cheque.codeSecour, Cheque.Montant, Cheque.RefDemande
FROM Cheque WHERE Cheque.estEdite=1)
union ALL
(SELECT Autres.DateOperation, Autres.codeSecour, Autres.Montant, Autres.RefDemande
FROM Autres)
UNION ALL (SELECT Virement.DateOperation, Virement.codeSecour, Virement.Montant, Virement.RefDemande
FROM Virement
WHERE Virement.estEffectue=1)
050106 13:54:50	      2 Quit   

 Thank’s

Need Feedback

Hi!

I was able to reproduce this behaviour using Access 2003 / MySQL 4.1.9nt.

I'll look further into this bug report as I'm not sure if this is a MyODBC bug, or if its a bug in MS Access.

Hi! I seem to got stuck with the some problem as the original poster.

My configuration: 
MySQL server v4.1.8nt via TCP/IP
MyODBC-3.51.11-1-win
MsAccess2002 (XP)

I was upgrading some MsAccess tables and queries and got into the some problem.

My original MDB needed to make a query with 4 UNION SELECT:

SELECT C_Especialidade1 AS Compras FROM Fornecedores
UNION SELECT C_Especialidade2
FROM Fornecedores
UNION SELECT C_Especialidade3
FROM Fornecedores
UNION SELECT C_Especialidade4
FROM Fornecedores
UNION SELECT C_Especialidade5
FROM Fornecedores;

Tests and conclusions:

 1 - This original query made on a Access (MDB) table works fine.
 2 - This original query made directly on MySQL Query Browser works fine.
 3 - This original query made on a MySQL table via MyODBC with MSaccess returns the following error message on a dialog box:
          ODBC -- Call failure
          You have an error in your SQL syntax; check the manual that corresponds  to your MySQL server version for the right syntax to use near '(((SELECT `C_Especialidade1` FROM `fornecedores`) UNION (SELECT `C_Especialida' at line 1 (#1064)

 4 - Just like suggested by 'Mam Narakka', I reduced the query to only 2 UNION SELECT and it worked fine! So it seems to exist some MyODBC bug in there!

      This is the "reduced" query that worked fine:

   SELECT C_Especialidade1 AS Compras
   FROM Fornecedores
   UNION SELECT C_Especialidade2
   FROM Fornecedores;

 Thanks!

Thanks for the bug report.  This is a MyODBC bug.

mysql> SELECT REPEAT('a',1) UNION SELECT REPEAT('b',10) UNION SELECT REPEAT('c',
4) UNION SELECT REPEAT('d',2);
+---------------+
| REPEAT('a',1) |
+---------------+
| a             |
| bbbbbbbbbb    |
| cccc          |
| dd            |
+---------------+
4 rows in set (0.00 sec)

(using imyodbc)
SQL> SELECT REPEAT('a',1) UNION SELECT REPEAT('b',10) UNION SELECT REPEAT('c',4)
, UNION SELECT REPEAT('d',3);
[imyodbc]ERROR: Could not SQLExecute

Just tryed the same query, same MsAccess, on another ODBC connection (via Oracle) and it worked correctly... But with MyODBC the syntax error always comes up!

Looks like really a MyODBC Bug!

The thing seems to work if you call the query as a "pass-through" query

(on MsAccess, when building the query, go to Query - SQL Specific - Pass Through)

I have the same problem.. Im glad that someone has my same problem.. It was driving me crazy thinking that was some problem of access.. but I tested to "union" just two Selects, and running the entire query on mysql, etc.. and those worked.. so definitely the problem is when running a query with more than one "union" statement (two Select and one Union) in msaccess using myodbc to connect to the mysql server..

I tested the "pass-through" query and it didnt work either.. when I try to run it Im prompted for the ODBC connection and then it ends with "ODBC call failed"

does anybody found a solution?

thanks

mike

Per tracing the ODBC log, I found the reason of the error occured was due to myodbc driver. For example, the original sql in access was '(SELECT * from a) union (select * from b) union (select * from c)', but after mysqlODBC parsed, the sql changed '((SELECT * from a) union (select * from b)) union (select * from c)', and couldn't work on in mysql. So how to get rid of the '(' and ')' is the key. Who knows.

张琰 yykk601

Per tracing the ODBC log, I found the reason of the error occured was due to myodbc driver. For example, the original sql in access was '(SELECT * from a) union (select * from b) union (select * from c)', but after mysqlODBC parsed, the sql changed '((SELECT * from a) union (select * from b)) union (select * from c)', and couldn't work on in mysql. So how to get rid of the '(' and ')' is the key. Who knows.

china yykk601

Hi,
i have this problem too

 1 - This original query made on a Access (MDB) table works fine.
 2 - This original query made directly on MySQL Query Browser works fine.
 3 - This original query made on a MySQL table via MyODBC with MSaccess returns
the following error message on a dialog box:
          ODBC -- Call failure

i have try with OpenOffice connected with same DNS and i don't haved problem.
thanks
axis

This is actually a server bug, and not an ODBC bug. Access adds additional parenthesis around the statement:

mysql> ((SELECT "foo" FROM DUAL) UNION (SELECT "bar" FROM DUAL));
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UNION (SELECT "bar" FROM DUAL))' at line 1

Same query works fine in SQL Server, PostgreSQL and Oracle.

Might be related to #4805, #14654, #25374 and #21904

Thank you for the feedback.

Verified as described in last comment.

All versions affected.

As (SELECT "foo" FROM DUAL UNION (SELECT "bar" FROM DUAL)); doesn't work too, marked as "Verified" and not duplicate of reports listed above.

To be fixed with WL#4145

I have encouneterd same issue while trying to refactor MS-ACCESS 2002 code and having multiple UNIONs would greatly help when I discovered this bug.

I see that it says it will be handled sometime, but I see no commited release/date.

In my mind this is higher priority then S3 since in my case I don't have a workaround.

Please advise,

Eugene.

Setting to "To be fixed later", since depends on a worklog task and is not planned currently.

Konstantin, 
is there any schedule, when this will be fixed?
If not, can you provide here some hints on the dependencies (WL etc.).
We need to get this handled (e.g. as a bloody workaround in myodbc)
as it breaks Access-compatibilty for us.
Thanks,
Armin.

Just trying to follow up.... Is the fix in near term plans?

Not in any immediate plans afaik. Please check the quoted WL.

I got it the solution!!!. Access union query via Mysql odbc only work right, if join not only tables to access query ,but join an access query too and it will works...

for example:

select * from tblname
UNION ALL
select * from tblname2 // it works all the time in Access
UNION ALL
select * from tblname3 // reqiured to join the same local access query for all tables and you didnt get the odbc error

This is one of the anoying bugs at the time. please fix it

Hi All,
For some reason this works for me running access 2003 and driver 5.1.8:

(
SELECT Year(Date) AS Year, Sum(tblAcctAdvertising.Amount) AS Amount, "Advertising" AS Category
FROM tblAcctAdvertising
GROUP BY Year(Date), "Advertising"
) 
UNION 
(
SELECT * FROM qryScheduleCCTE
) 
UNION 
(
SELECT Year(Date) AS Year, Sum(tblAcctContractLabor.Amount) AS Amount, "ContractLabor" AS Category
FROM tblAcctContractLabor
GROUP BY Year(Date)
) 
UNION 
(
SELECT Year(Date) AS Year, Sum(tblAcctInsurance.Amount) AS Amount, "Insurance" AS Category
FROM tblAcctInsurance
GROUP BY Year(Date), "Insurance"
) 
UNION 
(
SELECT Year(Date) AS Year, Sum(tblAcctInterestMortgage.Amount) AS Amount, "InterestaMortgage" AS Category
FROM tblAcctInterestMortgage
GROUP BY Year(Date), "InterestMortgage"
) 
UNION 
(
SELECT Year(Date) AS Year, Sum(tblAcctInterestOther.Amount) AS Amount, "InterestbOther" AS Category
FROM tblAcctInterestOther
GROUP BY Year(Date), "InterestOther"
) 
UNION 
(
SELECT Year(Date) AS Year, Sum(tblAcctLegalProfServices.Amount) AS Amount, "LegalProffessionalServices" AS Category
FROM tblAcctLegalProfServices
GROUP BY Year(Date), "LegalProffessionalServices"
) 
UNION 
(
SELECT Year(Date) AS Year, Sum(tblAcctOffice.Amount) AS Amount, "Office" AS Category
FROM tblAcctOffice
GROUP BY Year(Date), "Office"
) 
UNION 
(
SELECT Year(Date) AS Year, Sum(tblAcctOther.Amount) AS Amount, "Other" AS Category
FROM tblAcctOther
GROUP BY Year(Date), "Other"
) 
UNION 
(
SELECT Year(Date) AS Year, Sum(tblAcctRenta.Amount) AS Amount, "Renta" AS Category
FROM tblAcctRenta
GROUP BY Year(Date), "Renta"
) 
UNION 
(
SELECT Year(Date) AS Year, Sum(tblAcctRentb.Amount) AS Amount, "Rentb" AS Category
FROM tblAcctRentb
GROUP BY Year(Date), "Rentb"
) 
UNION 
(
SELECT Year(Date) AS Year, Sum(tblAcctRepairs.Amount) AS Amount, "Repairs" AS Category
FROM tblAcctRepairs
GROUP BY Year(Date), "Repairs"
) 
UNION 
(
SELECT Year(Date) AS Year, Sum(tblAcctSupplies.Amount) AS SumOfAmount, "Supplies" AS Category
FROM tblAcctSupplies
GROUP BY Year(Date), "Supplies"
) 
UNION 
(
SELECT Year(Date) AS Year, Sum(tblAcctTLLicensing.Amount) AS Amount, "TLLicensing" AS Category
FROM tblAcctTLLicensing
GROUP BY Year(Date), "TLLicensing"
) 
UNION 
(
SELECT Year(Date) AS Year, Sum(tblAcctTrMEaTravel.Amount) AS Amount, "TrMEaTravel" AS Category
FROM tblAcctTrMEaTravel
GROUP BY Year(Date), "TrMEaTravel"
) 
UNION 
(
SELECT Year, Round((SumOfAmount/2),2) AS Amount, Category FROM (SELECT Year(Date) AS Year, Sum(tblAcctTrMEbMeals.Amount) AS SumOfAmount, "TrMEbMeals" AS Category
FROM tblAcctTrMEbMeals
GROUP BY Year(Date), "TrMEbMeals") AS Q1
) 
UNION 
( 
SELECT Year(Date) AS Year, Sum(tblAcctUtilities.Amount) AS Amount, "Utilities" AS Category
FROM tblAcctUtilities
GROUP BY Year(Date), "Utilities"
) 
UNION 
( 
SELECT Year(BotOfPeriod) AS Year, Sum(tblAcctWages.CurGross) AS Amount, "Wages" AS Category
FROM tblAcctWages
GROUP BY Year(BotOfPeriod), "Wages"
);

I turn it every subquery of the union query, to query pass through and it work.

It is my query:

SELECT * FROM qryVentasPeriodoAnterior_pass;

UNION ALL SELECT * FROM qryVentasPeriodoAnterior2_pass;

UNION ALL SELECT * FROM qryVentasPeriodoAnterior3_pass;

Found a simple work-around, documented here:
http://stackoverflow.com/questions/12804495/multiple-union-query-doesnt-work/15004004

Another workaround: embeed your SQL with some Access native function Example:

Instead if

select a FROM A
UNION ALL 
select b FROM B
UNION ALL
select c FROM C

USE

select a FROM A
UNION ALL 
select iif(1=1, b, b) FROM B
UNION ALL
select c FROM C

Closing the bug.

This case is fixed in trunk:

(SELECT "foo" FROM DUAL UNION (SELECT "bar" FROM DUAL));

Other issues have been fixed in 5.6 or earlier.