Description:
UNION queries try to return widest data type for a column that would cover all datatypes for this column from the corresponding selects.
If datetime related function is used, the resultant data type is an internal datetime type, having a microsecond part.
As of version 5.1.28, UNION queries represent this columns as 29 bytes where first 19 bytes are 'YYYY-MM-DD HH:MM:SS' formated datetime and the last 10 bytes are "binary" 0.
This representation is not comprehensible by other SQL statements. INSERT ... SELECT is known to be affected, possibly cursors and others that can operate on resultsets.
How to repeat:
First run this query in MySQL Query Browser and see the 10 '|' chars appended field contents :
(SELECT (DATE_ADD('2008-10-10 00:04:15', INTERVAL 5 SECOND)) AS DT)
UNION ALL
(SELECT (DATE_ADD('2008-10-10 00:04:15', INTERVAL 10 SECOND)) AS DT);
--> 2008-10-10 00:04:15||||||||||
--> 2008-10-10 00:04:25||||||||||
To see what is the binary content, you can right click any of those piped fields and select "View Filed in Popup Editor" and select the "Binary" tab.
Now, to observe the effect on INSERT ... SELECT by :
mysql> CREATE TEMPORARY TABLE `test`.`ttmp` (`dt` datetime NOT NULL);
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT `test`.`ttmp`
-> (SELECT (DATE_ADD('2008-10-10 00:04:15', INTERVAL 5 SECOND)) AS DT)
-> UNION ALL
-> (SELECT (DATE_ADD('2008-10-10 00:04:15', INTERVAL 10 SECOND)) AS DT);
ERROR 1292 (22007): Incorrect datetime value: '2008-10-10 00:04:20' for column '
dt' at row 1
Workaround
----------
CAST to the appropriate date/time datatype. The following works :
mysql> INSERT `test`.`ttmp`
-> (SELECT CAST(DATE_ADD('2008-10-10 00:04:15', INTERVAL 5 SECOND) AS DATETI
ME) AS DT)
-> UNION ALL
-> (SELECT CAST(DATE_ADD('2008-10-10 00:04:15', INTERVAL 10 SECOND) AS DATET
IME) AS DT);
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM `test`.`ttmp`;
+---------------------+
| dt |
+---------------------+
| 2008-10-10 00:04:20 |
| 2008-10-10 00:04:25 |
+---------------------+
2 rows in set (0.00 sec)
Suggested fix:
There are various issues related to this one. In no particular order :
Date Type column problem when using UNION-Table.
http://bugs.mysql.com/bug.php?id=6931
INSERT + SELECT + UNION ALL + DATE to VARCHAR(8) conversion problem
http://bugs.mysql.com/bug.php?id=4937
UNION ALL with + INTERVAL N DAY crashes server
http://bugs.mysql.com/bug.php?id=9577
UNION query returns different datatype than SELECT in Access
http://bugs.mysql.com/bug.php?id=4782
UNION query can cause server to crash
http://bugs.mysql.com/bug.php?id=8660
Union returns wrong column type size
http://bugs.mysql.com/bug.php?id=30886
If a fix would broke various fixes for other bugs, simply document the behaviour.
Otherwise, UNION [ALL] queries should return (internal datetime) data that is comprehensible by other MySQL Server modules.
Description: UNION queries try to return widest data type for a column that would cover all datatypes for this column from the corresponding selects. If datetime related function is used, the resultant data type is an internal datetime type, having a microsecond part. As of version 5.1.28, UNION queries represent this columns as 29 bytes where first 19 bytes are 'YYYY-MM-DD HH:MM:SS' formated datetime and the last 10 bytes are "binary" 0. This representation is not comprehensible by other SQL statements. INSERT ... SELECT is known to be affected, possibly cursors and others that can operate on resultsets. How to repeat: First run this query in MySQL Query Browser and see the 10 '|' chars appended field contents : (SELECT (DATE_ADD('2008-10-10 00:04:15', INTERVAL 5 SECOND)) AS DT) UNION ALL (SELECT (DATE_ADD('2008-10-10 00:04:15', INTERVAL 10 SECOND)) AS DT); --> 2008-10-10 00:04:15|||||||||| --> 2008-10-10 00:04:25|||||||||| To see what is the binary content, you can right click any of those piped fields and select "View Filed in Popup Editor" and select the "Binary" tab. Now, to observe the effect on INSERT ... SELECT by : mysql> CREATE TEMPORARY TABLE `test`.`ttmp` (`dt` datetime NOT NULL); Query OK, 0 rows affected (0.00 sec) mysql> INSERT `test`.`ttmp` -> (SELECT (DATE_ADD('2008-10-10 00:04:15', INTERVAL 5 SECOND)) AS DT) -> UNION ALL -> (SELECT (DATE_ADD('2008-10-10 00:04:15', INTERVAL 10 SECOND)) AS DT); ERROR 1292 (22007): Incorrect datetime value: '2008-10-10 00:04:20' for column ' dt' at row 1 Workaround ---------- CAST to the appropriate date/time datatype. The following works : mysql> INSERT `test`.`ttmp` -> (SELECT CAST(DATE_ADD('2008-10-10 00:04:15', INTERVAL 5 SECOND) AS DATETI ME) AS DT) -> UNION ALL -> (SELECT CAST(DATE_ADD('2008-10-10 00:04:15', INTERVAL 10 SECOND) AS DATET IME) AS DT); Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> SELECT * FROM `test`.`ttmp`; +---------------------+ | dt | +---------------------+ | 2008-10-10 00:04:20 | | 2008-10-10 00:04:25 | +---------------------+ 2 rows in set (0.00 sec) Suggested fix: There are various issues related to this one. In no particular order : Date Type column problem when using UNION-Table. http://bugs.mysql.com/bug.php?id=6931 INSERT + SELECT + UNION ALL + DATE to VARCHAR(8) conversion problem http://bugs.mysql.com/bug.php?id=4937 UNION ALL with + INTERVAL N DAY crashes server http://bugs.mysql.com/bug.php?id=9577 UNION query returns different datatype than SELECT in Access http://bugs.mysql.com/bug.php?id=4782 UNION query can cause server to crash http://bugs.mysql.com/bug.php?id=8660 Union returns wrong column type size http://bugs.mysql.com/bug.php?id=30886 If a fix would broke various fixes for other bugs, simply document the behaviour. Otherwise, UNION [ALL] queries should return (internal datetime) data that is comprehensible by other MySQL Server modules.