MySQL Bugs: #1044: Attempting to run a Union query on linked tables in MSAccess

Bug #1044	Attempting to run a Union query on linked tables in MSAccess
Submitted:	13 Aug 2003 16:45	Modified:	26 Nov 2003 14:23
Reporter:	Henry Fischer	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	Connector / ODBC	Severity:	S3 (Non-critical)
Version:	3.51	OS:	Windows (Windows 2000)
Assigned to:		CPU Architecture:	Any
Tags:	ODBC5-RC

Description:
I hope this is a bug and not a waste of time...

Setup:
Running MSAccess2000 and MySQL 4.0. max-nt on same machine. Testing MSAccess as a feasable front-end with MySQL as backend. Tables are linked using ODBC driver 3.51.

Problem:
Attempt is made to run a union query with multiple "UNION SELECT" statements.
Inclusion of more than one UNION SELECT statement causes a "syntax error message" and does not return any values. 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.
3)The same syntax is as listed in reference manual.

 

How to repeat:
Example:

This query works fine on non-linked tables, as a pass-through query, on linked tables:

SELECT 'tbl1' AS DataTable, tbl1.startDate, tbl1.endDate
FROM tbl1

UNION SELECT 'tbl2' , tbl2.startDate, tbl2.endDate
FROM tbl2;

This query works fine on non-linked tables, as a pass-through query, BUT returns the following error when using linked tables:

ODBC--call failed
[MySQL][ODBC 3.51 Driver] [mysqld-4.0.14-max-nt] You have an error in your SQL syntax,...(#1064)

SELECT 'tbl1' AS DataTable, tbl1.startDate, tbl1.endDate
FROM tbl1

UNION SELECT 'tbl2' , tbl2.startDate, tbl2.endDate
FROM tbl2

UNION SELECT 'tbl3' , tbl3.startDate, tbl3.endDate
FROM tbl3;

Suggested fix:
Driver connection problem?

I did a test without to find any problems.
When you linked the table you had used e.g: a column type int
for to be used by Access as primary key ?

Thanks,

I don't understand the last comment but assume it is a syntax error on my part. Please send the syntax used in your verification.

The tables are identical.

Error is returned with or without keys defined. Error is returned even if the query is run on the same table without any keys defined. Error is retuned no matter how many fields are chosen.

Again the syntax works without problem against Access tables alone, or as a pass-through query against the mysql tables directly.

I am not sure why this was classified as "not a bug".
Has anyone been able to resolve this problem?

I was able to work around the issue by creating this as a pass-through query, as Henry suggested, but would rather not have to select the data source each time, since other, nontechnical users will be running reports based on this type of query.

I just ran into this bug after having not run into the bug. Let me explain.

Query 1:
SELECT CWIPH.JOB_NAME, CWIPH.JOB_NO, tblsubsafetyrecord.event_id
FROM CWIPH
INNER JOIN tblsubsafetyrecord ON LTRIM(CWIPH.JOB_NO) = tblsubsafetyrecord.event_project

UNION SELECT CWIPH.JOB_NAME, CWIPH.JOB_NO, tblsafetycitations.Citation_ID as event_id
FROM CWIPH
INNER JOIN tblsafetycitations ON LTRIM(CWIPH.JOB_NO) = tblsafetycitations.job_no

UNION SELECT CWIPH.JOB_NAME, CWIPH.JOB_NO, tblaccidents.accident_id as event_id
FROM CWIPH
INNER JOIN tblaccidents ON LTRIM(CWIPH.JOB_NO) = tblaccidents.JobNo;

With multiple unions, this does work on a linked table in Access 2000 using the 3.51.12 myODBC Connector.

Query 2:
SELECT tblaccidents.Injury AS Description, tblaccidents.Date AS IncidentDate, tblaccidents.SS_No AS Person, tblaccidents.JobNo AS job
FROM tblaccidents

UNION SELECT tblsafetycitations.Violation AS Description, tblsafetycitations.Date AS IncidentDate, tblsafetycitations.SS_No AS Person, tblsafetycitations.job_no AS job
FROM tblsafetycitations

UNION SELECT tblsubsafetyrecord.event_description AS Description, tblsubsafetyrecord.event_date AS IncidentDate, tblsubsafetyrecord.event_subemp_name AS Person, tblsubsafetyrecord.event_project AS job
FROM tblsubsafetyrecord;

This query, very similar, does not function with multiple unions and comes back with the Error 1064 as per other users' comments. This query is in Access 2000 on linked tables using the 3.51.12 myODBC Connector.

I hope there has been some resolution on this. I'm getting worried about the state of myODBC.

I am surprise to see that this problem is still ongoing 2 years later. I am having the same problem with mysql 5