SHOW CREATE TABLE doesn't show default values

[Stewart Smith]

drizzle> CREATE TABLE t1 (col1 INT NOT NULL default 99, col2 CHAR(6) NOT NULL);
Query OK, 0 rows affected (0.02 sec)

drizzle> SHOW CREATE TABLE t1;
+-------+-----------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+-----------------------------------------------------------------------------------------+
| t1 | CREATE TABLE `t1` (
  `col1` int NOT NULL,
  `col2` varchar(6) NOT NULL
) ENGINE=InnoDB |
+-------+-----------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

See also strict test (result will need to be changed when this is fixed)

[Jay Pipes (jaypipes)]

Confirmed, on both MyISAM and InnoDB:

drizzle> CREATE TABLE t10 (col1 INT NOT NULL default 99, col2 CHAR(6) NOT NULL);
Query OK, 0 rows affected (0.02 sec)

drizzle> SHOW CREATE TABLE t10\g
+-------+------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+------------------------------------------------------------------------------------------+
| t10 | CREATE TABLE `t10` (
  `col1` int NOT NULL,
  `col2` varchar(6) NOT NULL
) ENGINE=InnoDB |
+-------+------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

drizzle> CREATE TABLE t11 (col1 INT NOT NULL default 99, col2 CHAR(6) NOT NULL)engine=MyISAM;
Query OK, 0 rows affected (0.05 sec)

drizzle> SHOW CREATE TABLE t11\g
+-------+------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+------------------------------------------------------------------------------------------+
| t11 | CREATE TABLE `t11` (
  `col1` int NOT NULL,
  `col2` varchar(6) NOT NULL
) ENGINE=MyISAM |
+-------+------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

[Toru Maesaka (tmaesaka)]

Started looking into this. So far I've found this crazy evaluation in drizzled/show.cc / get_field_default_value():

has_default= (field->type() != DRIZZLE_TYPE_BLOB &&
!(field->flags & NO_DEFAULT_VALUE_FLAG) &&
field->unireg_check != Field::NEXT_NUMBER
&& has_now_default);

to evaluate to false when it needs to be true for further processing. If I manage to fix this I'll rewrite it to be more readable ;)

[Toru Maesaka (tmaesaka)]

Found the problem, will write the patch and push shortly.

[Toru Maesaka (tmaesaka)]

Fixed in my tree and it is proposed for merge at the moment. Here's my result with MyISAM and InnoDB:

drizzle> CREATE TABLE myisam_tbl (col1 INT NOT NULL default 99, col2 CHAR(6) NOT NULL)engine=MyISAM;
Query OK, 0 rows affected (0.01 sec)

drizzle> show create table myisam_tbl;
+------------+--------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+------------+--------------------------------------------------------------------------------------------------------------+
| myisam_tbl | CREATE TABLE `myisam_tbl` (
  `col1` int NOT NULL DEFAULT '99',
  `col2` varchar(6) NOT NULL
) ENGINE=MyISAM |
+------------+--------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

drizzle> CREATE TABLE innodb_tbl (col1 INT NOT NULL default 99, col2 CHAR(6) NOT NULL)engine=InnoDB;
Query OK, 0 rows affected (0.02 sec)

drizzle> show create table innodb_tbl;
+------------+--------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+------------+--------------------------------------------------------------------------------------------------------------+
| innodb_tbl | CREATE TABLE `innodb_tbl` (
  `col1` int NOT NULL DEFAULT '99',
  `col2` varchar(6) NOT NULL
) ENGINE=InnoDB |
+------------+--------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)