Bug #27301 space padding to display width does not occur
Submitted: 20 Mar 2007 19:33 Modified: 29 Oct 2008 20:23
Reporter: Scott Noyes (Basic Quality Contributor) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Documentation Severity:S3 (Non-critical)
Version:all OS:Windows (Windows, Linux)
Assigned to: Paul DuBois CPU Architecture:Any
Tags: qc

[20 Mar 2007 19:33] Scott Noyes
Description:
http://dev.mysql.com/doc/refman/5.0/en/numeric-types.html says:

"Another extension is supported by MySQL for optionally specifying the display width of integer data types in parentheses following the base keyword for the type (for example, INT(4)). This optional display width is used to display integer values having a width less than the width specified for the column by left-padding them with spaces."

No space padding occurs. If you specify zerofill, then zero padding occurs as documented.

How to repeat:
mysql> CREATE TABLE test (id bigint(10));
Query OK, 0 rows affected (0.05 sec)

mysql> INSERT INTO test VALUES (1);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM test; -- Should show 9 spaces before the 1
+------+
| id   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

mysql> ALTER TABLE test MODIFY COLUMN id bigint(10) zerofill;
Query OK, 1 row affected (0.03 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM test; -- Works as expected
+------------+
| id         |
+------------+
| 0000000001 |
+------------+
1 row in set (0.00 sec)

Suggested fix:
Left pad with spaces as documentation indicates, or alter manual to reflect current behavior, which is probably what everyone expects anyway.
[20 Mar 2007 22:16] Sveta Smirnova
Thank you for the report.

Verified as described.
[26 Sep 2008 18:38] Omer Barnir
triage: correcting tag from SR51MRU to CHECKED as D=4
[29 Oct 2008 20:23] Paul DuBois
Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant products.

The width information *is* present in result set metadata, but need not be used by the application. I've changed the paragraph in question as follows:

Another extension is supported by MySQL for optionally specifying the
display width of integer data types in parentheses following the base
keyword for the type (for example, INT(4)). This optional display 
width may be used by applications to display integer values having a
width less than the width specified for the column by left-padding 
them with spaces. (That is, this width is present in the metadata
returned with result sets. Whether it is used or not is up to the
application.)