MySQL Bugs: #4782: UNION query returns different datatype than SELECT in Access

Bug #4782	UNION query returns different datatype than SELECT in Access
Submitted:	28 Jul 2004 8:19	Modified:	28 Jul 2004 19:52
Reporter:	Matthew Kerr	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	Connector / ODBC	Severity:	S1 (Critical)
Version:	3.51.08	OS:	Windows (Win XP)
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
When I perform a pass-thru query in Access, the data is returned differently to Access if I use UNION syntax than if I run a single query.

(See 'how to repeat' for details)

In particular, a date field is correctly typed as a "date" in Access when I run any single query; but running similar queries with UNION syntax changes the result to a "binary" field type. MySQL CLI works fine in either case.

Thanks,
-Matt

How to repeat:
This works fine:

SELECT empID, clockIn, clockOut, DATE_FORMAT(clockin, '%Y-%m-%d') AS date 
 FROM tc_clockinout WHERE (DATE_FORMAT(clockout, '%Y%m%d') = DATE_FORMAT(clockin, '%Y%m%d'))

However, if I use UNION syntax to combine some datasets:

(SELECT empID, clockIn, clockOut, DATE_FORMAT(clockin, '%Y-%m-%d') AS date 
 FROM tc_clockinout WHERE (DATE_FORMAT(clockout, '%Y%m%d') = DATE_FORMAT(clockin, '%Y%m%d')))
UNION
(SELECT empID, clockIn, clockOut, DATE_FORMAT(clockin, '%Y-%m-%d') AS date 
 FROM tc_clockinout WHERE (DATE_FORMAT(clockout, '%Y%m%d') = DATE_FORMAT(clockin, '%Y%m%d') + 1))

Then the 'date' field is returned to (interpreted by) MS Access 2003 as a binary field type, instead of a date!

Both examples return correct data from the MySQL commandline client.

Suggested fix:
See how the data is returned differently when using a UNION statement. 
Change it to match how data is returned when running a single query.

Microsoft ODBC SQL trace file of two example queries

Attachment: SQL.LOG (application/octet-stream, text), 36.20 KiB.

From the trace file, it seems like MyODBC/MySQL respond differently to the date field:

MSACCESS        a08-c54	EXIT  SQLGetData  with return code 0 (SQL_SUCCESS)
		HSTMT               0A7E1D58
		UWORD                        4 
		SWORD                       -2 <SQL_C_BINARY>
(bad)

vs.

MSACCESS        a08-c54	EXIT  SQLGetData  with return code 0 (SQL_SUCCESS)
		HSTMT               0A7E1D58
		UWORD                        4 
		SWORD                       -8 <SQL_C_WCHAR>
(good!)

This is the same issue as reported in 4578, and was corrected in Connector/ODBC 3.51.9.

Note that UNION can return different data types than a simple SELECT currently due to the method by which the union is resolved; this should be addressed in 4.1.  The problem regarding returning as a binary data type, however, has been fixed.

3.51.09 solved the binary issue, but now it's showing as a text/char field when being returned from a UNION versus correctly returning it as date or datetime from a single SELECT. 

I can't seem to use your CAST workaround in this case. Do you have any updated information or workarounds?

That is due to the UNION (as above), with no real workaround for 4.0.