Bug #107349 text type has default value when sql_mode is NOT STRICT_TRANS_TABLES
Submitted: 20 May 2022 9:52 Modified: 20 May 2022 13:30
Reporter: Dongchao Yang Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: DDL Severity:S3 (Non-critical)
Version:5.7,8.0 OS:Any
Assigned to: CPU Architecture:Any
Tags: default value, SQL_MODE, strict_trans_tables, text

[20 May 2022 9:52] Dongchao Yang
Description:
In MySQL, BLOB, TEXT, GEOMETRY or JSON columns can't have a default value. However, it is possible to create a table with text fields with default value after setting sql_mode to ''(doesn't include STRICT_TRANS_TABLE). But show create table doesn't show the default value. This easily makes users feel puzzled.

How to repeat:
MySQL [(none)]> show variables like 'sql_mode';
+---------------+-----------------------------------------------------------------------------------------------------------------------+
| Variable_name | Value                                                                                                                 |
+---------------+-----------------------------------------------------------------------------------------------------------------------+
| sql_mode      | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION |
+---------------+-----------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

MySQL [mytest]> create table t1(id int auto_increment primary key, name varchar(20), tt text not null default '');
ERROR 1101 (42000): BLOB, TEXT, GEOMETRY or JSON column 'tt' can't have a default value

MySQL [mytest]> set sql_mode = '';
Query OK, 0 rows affected (0.00 sec)

MySQL [mytest]> create table t1(id int auto_increment primary key, name varchar(20), tt text not null default '');
Query OK, 0 rows affected, 1 warning (0.06 sec)

MySQL [mytest]> show warnings;
+---------+------+---------------------------------------------------------------------+
| Level   | Code | Message                                                             |
+---------+------+---------------------------------------------------------------------+
| Warning | 1101 | BLOB, TEXT, GEOMETRY or JSON column 'tt' can't have a default value |
+---------+------+---------------------------------------------------------------------+
1 row in set (0.00 sec)

======================================
Field tt doesn't have a default value.
======================================

MySQL [mytest]> show create table t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(20) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `tt` text COLLATE utf8mb4_general_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
1 row in set (0.01 sec)

MySQL [mytest]> set sql_mode = 'STRICT_TRANS_TABLES';
Query OK, 0 rows affected, 1 warning (0.00 sec)

MySQL [(none)]> show variables like 'sql_mode';
+---------------+---------------------+
| Variable_name | Value               |
+---------------+---------------------+
| sql_mode      | STRICT_TRANS_TABLES |
+---------------+---------------------+
1 row in set (0.01 sec)

MySQL [mytest]> show warnings;
+---------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                                                                                                 |
+---------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Warning | 3135 | 'NO_ZERO_DATE', 'NO_ZERO_IN_DATE' and 'ERROR_FOR_DIVISION_BY_ZERO' sql modes should be used with strict mode. They will be merged with strict mode in a future release. |
+---------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

================================================================
But we can insert a row without providing the value of field tt.
================================================================

MySQL [mytest]> insert into t1(id, name) values(1, 'xxxx');
Query OK, 1 row affected (0.01 sec)

MySQL [mytest]> select * from t1;
+----+------+----+
| id | name | tt |
+----+------+----+
|  1 | xxxx |    |
+----+------+----+
1 row in set (0.00 sec)

Suggested fix:
I think the show create table statement should show the default value of text field here because we can insert a row without providing the value of field tt.
[20 May 2022 13:30] MySQL Verification Team
Hi Mr. Yang,

Thank you for your bug report.

What you describe is expected and documented behaviour. In STRICT mode you get the error and without that mode, you get a warning. That is intended and designed behaviour of our server.

This is explained in detail in several chapters in our Reference Manual. If you, however, find that those descriptions are insufficient, we could verify this bug as a documentation bug. But, please, be precise and thorough, since our Manual explains this in many chapters and in a verbose manner.

Thank you for your interest in MySQL.