MySQL Bugs: #62277: data types defaults

Bug #62277	data types defaults
Submitted:	28 Aug 2011 13:26	Modified:	31 Aug 2011 4:42
Reporter:	Susanne Ebrecht	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	5.5	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Hello Doc-Team,

This report is about 5.5 and:

data-type-defaults.html

I found two sentences which seems to be outdated:

1.
quote:
BLOB and TEXT columns cannot be assigned a default value.

This is wrong.

CREATE TABLE t(i integer, t text);
INSERT INTO t(i) VALUES (1);

Works!! Should throw an error when text couldn't have a default.
The default is NULL by the way.

SELECT * FROM t where t is NULL;
+------+------+
| i    | t    |
+------+------+
|    1 | NULL |
+------+------+

2.
quote:
For numeric types, the default is 0,

CREATE TABLE t(n numeric(5,2), d decimal(5,2), f float, do double);
SHOW CREATE TABLE t\G

Create Table: CREATE TABLE `t` (
  `n` decimal(5,2) DEFAULT NULL,
  `d` decimal(5,2) DEFAULT NULL,
  `f` float DEFAULT NULL,
  `do` double DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1

Susanne

How to repeat:
see above

Case 1 is explained at the beginning of the same manual page:

"If a column definition includes no explicit DEFAULT value, MySQL determines the default value as follows:

If the column can take NULL as a value, the column is defined with an explicit DEFAULT NULL clause."

Case 2 should be tested by inserting row without explicit values for columns, in non-strict SQL mode. This is when implicit default 0 should appear. "Implicit" means that it is NOT presented in CREATE TABLE.

If you ask me, there is no bug here. But I let you double check.

create table t(i integer, n numeric(5,2), d decimal(5,2));
insert into t(i) values(2);
select * from t;
+------+------+------+
| i    | n    | d    |
+------+------+------+
|    2 | NULL | NULL |
+------+------+------+
1 row in set (0.00 sec)

Looks like NULL or?

In your table all columns accept NULLs, so NULLs are used when explicit value is not provided. What else one should expect?

Implicit defaults are used when column is declared as NOT NULL, has no explicit DEFAULT defined and you insert row without any defined value for the column:

mysql> create table ti(i int, d decimal(5.2) NOT NULL);
Query OK, 0 rows affected (0.23 sec)

mysql> insert into ti(i) values(1);
Query OK, 1 row affected, 1 warning (0.03 sec)

mysql> show warnings\G
*************************** 1. row ***************************
  Level: Warning
   Code: 1364
Message: Field 'd' doesn't have a default value
1 row in set (0.00 sec)

mysql> select * from ti;
+------+---+
| i    | d |
+------+---+
|    1 | 0 |
+------+---+
1 row in set (0.02 sec)

You can see implicit default used above.

IMHO documentation is clear enough on this.