MySQL Bugs: #34198: DESCRIBE reports default value to be NULL when it isn't

Bug #34198	DESCRIBE reports default value to be NULL when it isn't
Submitted:	31 Jan 2008 14:32	Modified:	7 Apr 2010 11:56
Reporter:	Daniel DeLorme	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Information schema	Severity:	S3 (Non-critical)
Version:	5.0.51, 5.1+	OS:	Linux
Assigned to:	Ramil Kalimullin	CPU Architecture:	Any
Tags:	DEFAULT, DESCRIBE, null

Description:
"describe table" claims a NULL default value for columns with a '' (empty string, not-NULL) default value.

How to repeat:
create table txt ( 
  a text not null default '', 
  b varchar(100) not null default '',
  c text null default '', 
  d varchar(100) null default ''
);

describe txt;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| a     | text         | NO   |     | NULL    |       | (bad)
| b     | varchar(100) | NO   |     | NULL    |       | (bad)
| c     | text         | YES  |     | NULL    |       | (ok)
| d     | varchar(100) | YES  |     | NULL    |       | (bad)
+-------+--------------+------+-----+---------+-------+

Suggested fix:
not-null VARCHAR fields with '' default value should be reported as ''

not-null TEXT fields have no default value but the default value reported by DESCRIBE should reflect the value inserted in the table if the INSERT or REPLACE statement includes no value for the column, i.e.
- NULL in strict SQL mode (and therefore error)
- "" in non-strict SQL mode

Many thanks for writing a bug report.

Consider:
http://dev.mysql.com/doc/refman/5.0/en/blob.html
"BLOB and TEXT columns cannot have DEFAULT values."

I tried to reproduce this with MySQL 5.0.51a and FreeBSD.

Look here:
mysql> create table t(a text not null default '',b varchar(5) not null default '',c char(5) not null default '', d text default '', e varchar(5) default '', f char(5) default '');
Query OK, 0 rows affected, 2 warnings (0.41 sec)

mysql> show warnings;
+---------+------+-------------------------------------------------+
| Level   | Code | Message                                         |
+---------+------+-------------------------------------------------+
| Warning | 1101 | BLOB/TEXT column 'a' can't have a default value | 
| Warning | 1101 | BLOB/TEXT column 'd' can't have a default value | 
+---------+------+-------------------------------------------------+
2 rows in set (0.00 sec)

mysql> describe t\G
*************************** 1. row ***************************
  Field: a
   Type: text
   Null: NO
    Key: 
Default: NULL
  Extra: 
*************************** 2. row ***************************
  Field: b
   Type: varchar(5)
   Null: NO
    Key: 
Default: 
  Extra: 
*************************** 3. row ***************************
  Field: c
   Type: char(5)
   Null: NO
    Key: 
Default: 
  Extra: 
*************************** 4. row ***************************
  Field: d
   Type: text
   Null: YES
    Key: 
Default: NULL
  Extra: 
*************************** 5. row ***************************
  Field: e
   Type: varchar(5)
   Null: YES
    Key: 
Default: 
  Extra: 
*************************** 6. row ***************************
  Field: f
   Type: char(5)
   Null: YES
    Key: 
Default: 
  Extra: 
6 rows in set (0.01 sec)

DESCRIBE shows me the right values for the DEFAULT for CHAR and VARCHAR.

Maybe this is a OS related issue.

Please let me know, which OS you use.
Also I need the exact version and how you installed MySQL.
Please make:
mysql> select version()

I came across this bug while testing on my development machine (ubuntu 6.06). I downloaded and installed the mysql-5.0.51a-linux-i686-glibc23.tar.gz package from http://dev.mysql.com/downloads/mysql/5.0.html because I wanted to have the latest version rather than ubuntu's 5.0.22

mysql> select version();
+-------------+
| version()   |
+-------------+
| 5.0.51a-log |
+-------------+

Also, IMHO in your example the default value reported for column "a" should be '', since that is the value inserted in the table if you don't specify a value for that column, e.g. insert into t (b) values ('');

In my opinion (c) is not OK either!

default is '' (empty string).  NULL is allowed for that column, but it only IS NULL if explicitly set to be!

Daniel.

"BLOB and TEXT columns cannot have DEFAULT values."
That means, the default value that is listed at describe is NULL.

Take my example:
create table t(a text not null default '',b varchar(5) not null default '',char(5) not null default '', d text default '', e varchar(5) default '', f char(5) default'');

insert into t(b,c,d,e,f) values ('a','a','a','a','a');

This should occur an error message, because column a has the "NOT NULL" check constraint.

create table t(a text default 'something',b varchar(5) not null default '',char(5) not null default '', d text default '', e varchar(5) default '', f char(5) default'');

insert into t(b,c,d,e,f) values ('a','a','a','a','a');

select a from t;

result: NULL

Because you can't change this default value.

Peter,

c is ok. Default is '', that on means that:
insert into t(b,c,d,e,f) values ('a','a','a','a','a');

select a from t;

result: ''

Of course I also can do:

insert into t(a,b,c,d,e,f) values (NULL,'a','a','a','a','a');

Therefor you will get an error message because of the "NOT NULL" check constraints.

There're two bugs here.

mysql> create table t(a text not null default '',b varchar(5) not null default '',c char(5) not null default '', d text default '', e varchar(5) default '', f char(5) default '');

1:

mysql> desc t;
+-------+------------+------+-----+---------+-------+
| Field | Type       | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| a     | text       | NO   |     | NULL    |       | 
| b     | varchar(5) | NO   |     | NULL    |       | 
| c     | char(5)    | NO   |     | NULL    |       | 
| d     | text       | YES  |     | NULL    |       | 
| e     | varchar(5) | YES  |     | NULL    |       | 
| f     | char(5)    | YES  |     | NULL    |       | 
+-------+------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

mysql> desc t\G
*************************** 1. row ***************************
  Field: a
   Type: text
   Null: NO
    Key: 
Default: NULL
  Extra: 
*************************** 2. row ***************************
  Field: b
   Type: varchar(5)
   Null: NO
    Key: 
Default: 
  Extra: 
*************************** 3. row ***************************
  Field: c
   Type: char(5)
   Null: NO
    Key: 
Default: 
  Extra: 
*************************** 4. row ***************************
  Field: d
   Type: text
   Null: YES
    Key: 
Default: NULL
  Extra: 
*************************** 5. row ***************************
  Field: e
   Type: varchar(5)
   Null: YES
    Key: 
Default: 
  Extra: 
*************************** 6. row ***************************
  Field: f
   Type: char(5)
   Null: YES
    Key: 
Default: 
  Extra: 
6 rows in set (0.01 sec)

note different values in the 'Default' column. It's a bug in the mysql command line client in 5.0, it's already fixed in 5.1.

2.

mysql> insert t () values ();
Query OK, 1 row affected (0.02 sec)

mysql> select * from t;
+---+---+---+------+------+------+
| a | b | c | d    | e    | f    |
+---+---+---+------+------+------+
|   |   |   | NULL |      |      | 
+---+---+---+------+------+------+
1 rows in set (0.00 sec)

column 'a' has a default of '' (empty string), not NULL.

workaround: use show create table

Actually, 5.0.51b has that SHOW COLUMNS inconsistancy from previous releases as well: It's a bug in MySQL server, not in command-line interface, I verified it through CocoaMySQL tool too.

Problem is that SHOW COLUMNS returns the set DEFAULT value, and not a coherant computed real default value, if no default value has been specified.

e.g.:

CREATE TABLE `test` (
  `username` varchar(50) NOT NULL
);

SHOW COLUMNS FROM `test`;

shows the Default being NULL (inconsistant with real value and previous MySQL versions)

CREATE TABLE `test` (
  `username` varchar(50) NOT NULL default ''
);

SHOW COLUMNS FROM `test`;

shows the DEFAULT being '' (same as in previous versions)

same holds true with: int, tinyint (show NULL instead of 0 for NOT NULL columns)

and also for the 8 types TEXT and BLOB and their MEDIUMTEXT etc variants. Even if those last 8 types don't have default values, SHOW COLUMNS should imho show real default values and not NULL if type is NOT NULL.

Similar problems for int and datetime values as well (DESCRIBE and SHOW COLUMNS FROM).

mysql> CREATE TABLE  `global`.`gplcache` (`id` bigint(19) NOT NULL, `status` int(11) NOT NULL, `create` datetime NOT NULL) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.06 sec)

mysql> describe gplcache;
+--------+------------+------+-----+---------+-------+
| Field  | Type       | Null | Key | Default | Extra |
+--------+------------+------+-----+---------+-------+
| id     | bigint(19) | NO   |     | NULL    |       |
| status | int(11)    | NO   |     | NULL    |       |
| create | datetime   | NO   |     | NULL    |       |
+--------+------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

The resolution of this bug might have to depend on the value of SQL_MODE.

With STRICT_TRANS_TABLES, those not-null columns with no default values act indeed as if the default value was NULL; they produce an error if the column is omitted from the INSERT. Only if STRICT_TRANS_TABLES is unset does the effective default value become ''.

mysql> set sql_mode='';
mysql> create table t2(mytext text not null);
mysql> describe t2;
+--------+------+------+-----+---------+-------+
| Field  | Type | Null | Key | Default | Extra |
+--------+------+------+-----+---------+-------+
| mytext | text | NO   |     | NULL    |       | 
+--------+------+------+-----+---------+-------+
mysql> insert into t2 () values ();
Query OK, 1 row affected, 1 warning (0.00 sec)

The default is listed as NULL but effectively it is '' (as the warning indicates).

mysql> set sql_mode='STRICT_TRANS_TABLES';
mysql> create table t6(mytext text not null);
mysql> describe t6;
+--------+------+------+-----+---------+-------+
| Field  | Type | Null | Key | Default | Extra |
+--------+------+------+-----+---------+-------+
| mytext | text | NO   |     | NULL    |       | 
+--------+------+------+-----+---------+-------+
mysql> insert into t6 () values ();
ERROR 1364 (HY000): Field 'mytext' doesn't have a default value

The default is indeed NULL, and MySQL refuses the operation.

Manual says:
"
If a column definition includes no explicit DEFAULT value,
MySQL determines the default value as follows: ...
 If the column cannot take NULL as the value, MySQL defines
 the column with no explicit DEFAULT clause. For data entry,
 if an INSERT or REPLACE statement includes no value for the
 column, or an UPDATE statement sets the column to NULL, MySQL
 handles the column according to the SQL mode in effect at the time:
* If strict SQL mode is not enabled, MySQL sets the column to
  the IMPLICIT DEFAULT VALUE for the column data type. ...
Suppose that a table t is defined as follows:
CREATE TABLE t (i INT NOT NULL);
In this case, i has no explicit default ...
For a given table, you can use the SHOW CREATE TABLE statement
to see which columns have an explicit DEFAULT clause. ...
IMPLICIT DEFAULTS are defined as follows:
For string types other than ENUM, the default value
is the empty string.
"

And it doesn't state that 
SHOW COLUMNS should show the implicit default value.

As BLOBs can't have default values, DEFAULT column "could be NULL in the sense of "we don't know the value or the value is inapplicable".

I'm closing this one as not a bug.