MySQL Bugs: #9707: UNIONs trim leading zeros in ZEROFILL fields

Bug #9707	UNIONs trim leading zeros in ZEROFILL fields
Submitted:	7 Apr 2005 8:28	Modified:	17 May 2006 21:11
Reporter:	Adam Wilson	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S2 (Serious)
Version:	4.1.11	OS:	Any (*)
Assigned to:	Mike Hillyer	CPU Architecture:	Any

Description:
When performing a UNION on NUMERIC fields with the ZEROFILL flag, MySQL trims the leading zeros (if any) from the fields when they are output.

How to repeat:
mysql> CREATE TABLE `table` (`field` INT(6) UNSIGNED ZEROFILL PRIMARY KEY);
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO `table` SET `field` = 024528;
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM `table`;
+--------+
| field  |
+--------+
| 024528 |
+--------+
1 row in set (0.00 sec)

mysql> (SELECT `field` FROM `table`) UNION (SELECT `field` FROM `table`);
+-------+
| field |
+-------+
| 24528 |
+-------+
1 row in set (0.00 sec)

Suggested fix:
Sorry... Can't be too much help here... :S

I wasn't sure wether to mark this as 'Serious' or 'Non-Critical'... but I'm sure that I'm not the only one affected by this...

As a workaround... you can use this...

mysql> (SELECT CONVERT(`field`,CHAR) as `field` FROM `table`) UNION (SELECT CONVERT(`field`,CHAR) as `field` FROM `table`);
+--------+
| field  |
+--------+
| 024528 |
+--------+
1 row in set (0.00 sec)

Hi Adam,

Thank you for the report. I was able to repeat this using MySQL 4.1.11. Using MySQL 5.0.3beta however, the result was OK.

I will look for a changeset and eventually document it.

Regards,

Geert

Not sure that I was entirely clear before... Just to clarify, this behavior is new to 4.1.11... 4.1.10 returned the correct values for the fields...

Hi Adam,

Thanks for that extra info there! In 4.1.10a it works indeed fine.

Regards,

Geert

I couldn't find in the manual any statements about properties of columns which are result of UNION statements.  (in my opinion we should have a clean definintion of what properties UNION result columns will have, but currently we don't have it).

Setting to 'to be fixed later' based on the above reasoning. I'll ask docs team to document this.

Note added to documentation:

<para>
      <emphasis role="bold">Note</emphasis>: The
      <literal>ZEROFILL</literal> attribute is stripped when a column is
      involved in expressions or <literal>UNION</literal> queries.
    </para>

For the record:

In 5.0.22

INT(4) ZEROFILL ATUTOINCREMENT

I want
0001
0002
0003
as sequence stored and displayed but output from SELECT `FIELD` FROM MY_TABLE is
1
2
3

Any comments?
Andrew

Ok, just answered from earlier message:

SELECT CONVERT(FIELD,CHAR) as `ID` FROM MY_TABLE

Gives me what I want...

But is the stripping of the leading/padding zeros a bug or intentional?