Bug #1322 String values truncate with UNION
Submitted: 17 Sep 2003 11:04 Modified: 18 Sep 2003 4:07
Reporter: Erik Goodlad Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:4.0.15 OS:Microsoft Windows (Windows XP Pro / 2000 Pro)
Assigned to: CPU Architecture:Any

[17 Sep 2003 11:04] Erik Goodlad
Description:
When doing a UNION to get the latest records from two similar tables, I added a unique string value in the select statements for each table so I could distinguish the record type.

The resulting dataset truncates the string value to the len of the shortest string.

How to repeat:
mysql> SELECT
    ->  e.date_posted,
    ->  e.entry_id,
    ->  'entry' as type
    -> FROM
    ->  entries e,
    ->  users u
    -> WHERE
    ->  entry_user_id = user_id
    -> UNION
    -> SELECT
    ->  c.date_posted,
    ->  e.entry_id,
    ->  'comment' as type
    -> FROM
    ->  comments c,
    ->  entries e,
    ->  users u
    -> WHERE
    ->  comment_user_id = user_id AND
    ->  comment_entry_id = entry_id
    -> ORDER BY
    ->  1 DESC
    -> LIMIT 10;

+---------------------+-------------------------------------+-------+
| date_posted         | entry_id                            | type  |
+---------------------+-------------------------------------+-------+
| 2003-09-09 14:24:05 | 87F00A79-8048-D85E-7E794D671DC40D3A | comme |
| 2003-09-09 12:59:37 | 87F00A79-8048-D85E-7E794D671DC40D3A | entry |
| 2003-09-03 06:56:49 | 5423D929-8048-D85E-7E58F312341C0C7B | comme |
| 2003-09-02 20:39:23 | 5423D929-8048-D85E-7E58F312341C0C7B | comme |
| 2003-09-02 20:35:52 | 5423D929-8048-D85E-7E58F312341C0C7B | comme |
| 2003-09-02 20:34:55 | 5423D929-8048-D85E-7E58F312341C0C7B | comme |
| 2003-08-30 17:46:43 | 2B8825F7-8048-D85E-7E41478443F690A3 | comme |
| 2003-08-30 11:41:44 | 203674D3-8048-D85E-7EBB0F5ACFA4C8CC | comme |
| 2003-08-30 11:35:57 | 5423D929-8048-D85E-7E58F312341C0C7B | entry |
| 2003-08-30 11:26:58 | 2B8825F7-8048-D85E-7E41478443F690A3 | comme |
+---------------------+-------------------------------------+-------+
10 rows in set (0.00 sec)

Notice the TYPE column, the values being returned are "comme" and "entry, even though I specified to SELECT the values "comment" and "entry".
[18 Sep 2003 4:07] Indrek Siitan
This is expected behaviour. The field types are determined according to the
first query in the UNION. In the first query you have 'entry' as type, according
to which the type field will become VARCHAR(5). The subsequent 'comment'
entry will therefore be truncated to fit into the VARCHAR(5) field.

The workaround is to pad the first entry with enough spaces so the subsequent
possible values will fit, i.e. 'entry          ' as type.