MySQL Bugs: #61737: show create table with quotes on default numeric values

Bug #61737	show create table with quotes on default numeric values
Submitted:	4 Jul 2011 13:36	Modified:	4 Jul 2011 13:40
Reporter:	Arnaud Adant	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: DDL	Severity:	S4 (Feature request)
Version:	all	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	mysqldump

Description:
The output of show create table <table_name> and mysqldump add default numeric values with quotes. These quotes are not needed for numeric types describe here :

http://dev.mysql.com/doc/refman/5.5/en/numeric-types.html

Please remove these unnecessary quotes.

How to repeat:
For int :

mysql> create table t2 ( t int default 1 ) engine = innodb;
Query OK, 0 rows affected (0.44 sec)

mysql> show create table t2\G
*************************** 1. row ***************************
Table: t2
Create Table: CREATE TABLE `t2` (
`t` int(11) DEFAULT '1'
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

For decimal :

mysql> create table test_decimal(a decimal(10,2) default 1.21);
Query OK, 0 rows affected (0.05 sec)

mysql> show create table test_decimal;
+--------------+----------------------------------------------------------------
-----------------------------------------+
| Table        | Create Table
                                        |
+--------------+----------------------------------------------------------------
-----------------------------------------+
| test_decimal | CREATE TABLE `test_decimal` (
 `a` decimal(10,2) DEFAULT '1.21'
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+--------------+----------------------------------------------------------------
-----------------------------------------+
1 row in set (0.00 sec)

Suggested fix:
Remove the quotes with numeric types.

Thank you for the bug report.

C:\DBS>c:\dbs\5.5\bin\mysql -uroot --port=3540 --prompt="mysql 5.5 >"
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.5.15-log Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql 5.5 >use test
Database changed
mysql 5.5 >create table t2 ( t int default 1 ) engine = innodb;
Query OK, 0 rows affected (0.16 sec)

mysql 5.5 >show create table t2\G
*************************** 1. row ***************************
       Table: t2
Create Table: CREATE TABLE `t2` (
  `t` int(11) DEFAULT '1'
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.01 sec)

mysql 5.5 >

Removing quotes could lead to :

1. less space in dumps : 2 bytes saved per default numeric value in create table

2. less type conversions