MySQL Bugs: #40454: Text types allow setting of default value

Bug #40454	Text types allow setting of default value
Submitted:	31 Oct 2008 11:03	Modified:	31 Oct 2008 13:00
Reporter:	Jakub Vrána (Candidate Quality Contributor)	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Data Types	Severity:	S3 (Non-critical)
Version:	5.1.26/5.0/6.0	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	ALTER TABLE, qc

Description:
Documentation says that text types doesn't allow storing default values. However it is possible in ALTER TABLE with tinytext, mediumtext and longtext types.

The same would maybe hold also for blob variants.

How to repeat:
CREATE TABLE t (t longtext NOT NULL);
ALTER TABLE t ALTER t SET DEFAULT 'a'; -- no error
EXPLAIN t; -- default value is not visible
INSERT INTO t () VALUES ();
SELECT * FROM t; -- default value applied

Thank you for the bug report. Verified as described:

Server version: 5.1.30-nt-debug-log Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql 5.1 >create database g1;
Query OK, 1 row affected (0.00 sec)

mysql 5.1 >use g1
Database changed
mysql 5.1 >CREATE TABLE t (t longtext NOT NULL);
Query OK, 0 rows affected (0.09 sec)

mysql 5.1 >ALTER TABLE t ALTER t SET DEFAULT 'a'; -- no error
Query OK, 0 rows affected (0.09 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql 5.1 >EXPLAIN t; -- default value is not visible
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| t     | longtext | NO   |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
1 row in set (0.06 sec)

mysql 5.1 >INSERT INTO t () VALUES ();
Query OK, 1 row affected (0.02 sec)

mysql 5.1 >SELECT * FROM t; -- default value applied
+---+
| t |
+---+
| a |
+---+
1 row in set (0.00 sec)