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 12:15	Modified:	2 Oct 2008 0:24
Reporter:	Mam narakka
Status:	Verified
Category:	Server: Parser	Severity:	S3 (Non-critical)
Version:	5.0, probably all, 5.1, 4.1	OS:	Any (Windows XP)
Assigned to:		Target Version:	TBD
Tags:	unscheduled, affects_connectors
Triage:	Triaged: D2 (Serious) / R4 (High) / E4 (High)

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.