Bug #4067 UNION query truncates fields

• Submitted: 8 Jun 2004 23:33
• Modified: 17 Jun 2004 12:54
• Reporter: Ronald Jeremy
• Status: Closed
• Category: MySQL Server
• Severity: S2 (Serious)
• Version: 4.1.3
• OS: Linux (linux redhat fedora core2)
• Assigned to: Oleksandr Byelkin
• CPU Architecture: Any

[8 Jun 2004 23:33] Ronald Jeremy

Description:
UNION queries having one part of the UNION a string, and another part of the UNION an integer, will truncate the integer to the number of characters in the string.  This happens for both MyISAM and InnoDB.

How to repeat:
CREATE TABLE test (uid int(1))Type=MyISAM;
INSERT INTO test SELECT 150;
SELECT 'a' UNION SELECT uid FROM test;

[8 Jun 2004 23:45] Dean Ellis

I cannot repeat this with 4.1.3.  This does occur in 4.0 (it could only be fixed in 4.1), so could you verify your version?

[9 Jun 2004 13:57] Ronald Jeremy

To be sure, I un-installed 4.1.2-alpha and compiled 4.1.3-beta from the Development Source Tree.  Still the same result with 4.1.3-beta:

CREATE TABLE test (uid int(1))Type=MyISAM;INSERT INTO test SELECT 150;SELECT 'a' UNION SELECT uid FROM test;

returns:
+------+
| a    |
+------+
| a    |
| 1    |
+------+

1 should be 150

[9 Jun 2004 17:02] Dean Ellis

On 4.1.3, I get:

+------+
| a    |
+------+
| a    |
| 150  |
+------+

I cannot reproduce this unless I'm connected to 4.0...  Make sure you have the 4.1 server binary running: SELECT VERSION();

[9 Jun 2004 18:17] Ronald Jeremy

Is there a difference between 4.1.3-beta and plain 4.1.3 you mention?  Here are the results of your suggestion:

mysql> SELECT 'a' UNION SELECT uid FROM test;SELECT VERSION();
+------+
| a    |
+------+
| a    |
| 1    |
+------+
2 rows in set (0.00 sec)

+------------+
| VERSION()  |
+------------+
| 4.1.3-beta |
+------------+
1 row in set (0.00 sec)

[9 Jun 2004 19:12] Dean Ellis

Verified against 4.1.3/Linux using latin1 client character set.  Does not occur with utf8 client character set.  Thank you for the report and your assistance.

[16 Jun 2004 15:07] Oleksandr Byelkin

ChangeSet 
  1.1921 04/06/16 16:06:30 [e-mail] +5 -0 
  new length detection for non-string in UNION (BUG#4067)

[17 Jun 2004 12:54] Oleksandr Byelkin

Thank you for bug report. patch is pushed in our internal repository and will 
be present in next server release.