| Bug #45429 | Strange characters on output | ||
|---|---|---|---|
| Submitted: | 10 Jun 2009 12:01 | Modified: | 10 Jun 2009 17:00 |
| Reporter: | Michal Albrecht | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: General | Severity: | S3 (Non-critical) |
| Version: | 5.0.67, 5.1.36 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | DATE_ADD, UNION | ||
[10 Jun 2009 13:23]
MySQL Verification Team
Thank you for the bug report. Could you please print here your output:
c:\dbs>c:\dbs\5.0\bin\mysql -uroot --port=3500 --prompt="mysql 5.0 > "
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.83-Win X64-log Source distribution
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql 5.0 > (SELECT DATE_ADD('2005-01-01', INTERVAL 1 DAY), NULL, 1) UNION (SELECT
-> DATE_ADD('2009-06-10', INTERVAL 1 DAY), NULL, 1);
+----------------------------------------+------+---+
| DATE_ADD('2005-01-01', INTERVAL 1 DAY) | NULL | 1 |
+----------------------------------------+------+---+
| 2005-01-02 | NULL | 1 |
| 2009-06-11 | NULL | 1 |
+----------------------------------------+------+---+
2 rows in set (0.00 sec)
mysql 5.0 >
[10 Jun 2009 13:49]
Michal Albrecht
I've checked the output once again and the first column of result set is 29 bytes long. First 10 characters form a date string and the rest are 0x00. That's why you will not see it on the console. However you can use a MySQL Query Browser. I found it using PHP function (mysql_fetch_assoc).
[10 Jun 2009 17:00]
Valeriy Kravchuk
Actually, the problem is in different column metadata when UNION is used. Compare these:
valeriy-kravchuks-macbook-pro:5.0 openxs$ bin/mysql -uroot -T test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.1.36-debug Source distribution
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> (SELECT DATE_ADD('2005-01-01', INTERVAL 1 DAY), NULL, 1) UNION (SELECT
-> DATE_ADD('2009-06-10', INTERVAL 1 DAY), NULL, 1);
Field 1: `DATE_ADD('2005-01-01', INTERVAL 1 DAY)`
Catalog: `def`
Database: ``
Table: ``
Org_table: ``
Type: STRING
Collation: binary (63)
Length: 29
Max_length: 29
Decimals: 0
Flags: BINARY
...
to these:
mysql> SELECT DATE_ADD('2005-01-01', INTERVAL 1 DAY), NULL, 1;Field 1: `DATE_ADD('2005-01-01', INTERVAL 1 DAY)`
Catalog: `def`
Database: ``
Table: ``
Org_table: ``
Type: STRING
Collation: binary (63)
Length: 29
Max_length: 10
Decimals: 31
Flags: BINARY
...
The question is: why do we have Max_length: 29 with UNION vs 10 without? This is inconsistent and, thus, a bug.
[10 Jun 2009 17:48]
MySQL Verification Team
union
Attachment: strange_characters.png (image/png, text), 21.11 KiB.
[23 Feb 2011 14:39]
Valeriy Kravchuk
According to some comments in Bug #60217, this may be already fixed in 5.5.9 (but NOT in 5.1.x).
[24 Feb 2011 12:04]
Valeriy Kravchuk
Bug #60217 was marked as a duplicate of this one.
Description: Strange characters after date in first column of result set. Both queries work fine separate without UNION in between. How to repeat: (SELECT DATE_ADD('2005-01-01', INTERVAL 1 DAY), NULL, 1) UNION (SELECT DATE_ADD('2009-06-10', INTERVAL 1 DAY), NULL, 1)