MySQL Bugs: #116479: json value unexpected with default ''

Bug #116479	json value unexpected with default ''
Submitted:	25 Oct 2024 8:42	Modified:	6 Feb 16:20
Reporter:	chen shawn	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: JSON	Severity:	S3 (Non-critical)
Version:	8.0.36	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	json

Description:
we can create a table with json field which has attribute: default '' under default sql_mode. But when we use 'show create table' to get the table define and run this sql on another mysqld, error will be reported:
error:(1101, "BLOB, TEXT, GEOMETRY or JSON column 'extend' can't have a default value")

even when the sql_mode of the mysqld is non-strict.

For blob/text column, we do not have this issue.

How to repeat:
// create table with json default ''
create table t4(a int, b json NOT NULL DEFAULT '');
// show create table tell us the json column has a default value 'null'
mysql> show create table t4;
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                      |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------+
| t4    | CREATE TABLE `t4` (
  `a` int DEFAULT NULL,
  `b` json NOT NULL DEFAULT 'null'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

//insert two record without json col:
insert into t4 (a) values(1),(2);

//select with json value, cannot get any result
mysql> select * from t4 where b is NULL;
Empty set (0.01 sec)

mysql> select * from t4 where b = 'null';
Empty set (0.00 sec)

mysql> select * from t4 where b = '';
Empty set (0.00 sec)

//if I want to create a same table on another mysqld:
CREATE TABLE `t4` (
  `a` int DEFAULT NULL,
  `b` json NOT NULL DEFAULT 'null'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
//Error is reported:
error:(1101, "BLOB, TEXT, GEOMETRY or JSON column 'extend' can't have a default value")

For blob column, the behavior is expected:
// create table with blob default ''
mysql> create table t3(a int, b blob NOT NULL DEFAULT '');

// show create table say column b has no default value
mysql> show create table t3;
+-------+------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                       |
+-------+------------------------------------------------------------------------------------------------------------------------------------+
| t3    | CREATE TABLE `t3` (
  `a` int DEFAULT NULL,
  `b` blob NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci |
+-------+------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

//we can also insert records without column b
mysql> insert into t3 (a) values(1),(2);

//and we can get the expected result with condition: b = ''
mysql> select * from t3 where b = '';
+------+------+
| a    | b    |
+------+------+
|    1 | 0x   |
|    2 | 0x   |
+------+------+
2 rows in set (0.00 sec)

Suggested fix:
I think the behavior of blob/text column is expected. We can create a new table with the sql given by 'show create table'.But for json column, the sql given by 'show create table' does not work.

This will cause some third party binary (like mydumper) fail to copy table schema.

Hi Mr. shawn,

Thank you for your bug report.

However, this is not a bug. You are using an unsupported release of the version 8.0.

With latest MySQL release, 8.0.40, it is all functioning correctly, like this:

create table t4(a int, b json NOT NULL DEFAULT '');
ERROR 1101 (42000): BLOB, TEXT, GEOMETRY or JSON column 'b' can't have a default value

Unsupported.

how about 8.0.36 ? It has same issue, have u set sql_mode to:'NO_ENGINE_SUBSTITUTION'

mysql>set session sql_mode='NO_ENGINE_SUBSTITUTION';create database test;use test;
Query OK, 1 row affected (0.01 sec)

Database changed
mysql> create table t1(a int, b json not null default '');
Query OK, 0 rows affected, 1 warning (0.27 sec)

mysql> show create table t1;
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                      |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------+
| t1    | CREATE TABLE `t1` (
  `a` int DEFAULT NULL,
  `b` json NOT NULL DEFAULT 'null'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

mysql> select @@version;
+---------------------+
| @@version           |
+---------------------+
| 8.0.36-28           |
+---------------------+
1 row in set (0.00 sec)

Hi Mr. Shawn,

We only test for bugs with latest public release available.

fwiw I can reproduce the issue in 8.0.36, 8.0.40 and 9.0.1

The important bit is 
set session sql_mode='NO_ENGINE_SUBSTITUTION';

which turns the error into a warning:

mysql> set session sql_mode='NO_ENGINE_SUBSTITUTION';
Query OK, 0 rows affected (0.00 sec)

mysql> create table t4(a int, b json NOT NULL DEFAULT '');
Query OK, 0 rows affected, 1 warning (0.01 sec)

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

mysql> select @@version;
+-----------+
| @@version |
+-----------+
| 9.0.1     |
+-----------+
1 row in set (0.00 sec)

mysql> show create table t4;
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                      |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------+
| t4    | CREATE TABLE `t4` (
  `a` int DEFAULT NULL,
  `b` json NOT NULL DEFAULT 'null'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

Yes, once we set sql_mode to 'NO_ENGINE_SUBSTITUTION', we can create such kind of table. But when we want to use mydumper or some other third party tool to copy the table schema. We will get the json column with default 'null' value, which cannot be executed on other db.

I think both of blob/text and GEOMETRY are expected:
// For blob/text, the output of 'show create table' does not have default value.
mysql> create table t3(a int, b blob NOT NULL DEFAULT '');
mysql> show create table t3;
+-------+------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                       |
+-------+------------------------------------------------------------------------------------------------------------------------------------+
| t3    | CREATE TABLE `t3` (
  `a` int DEFAULT NULL,
  `b` blob NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci |
+-------+------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

//For GEOMETRY we cannot create it even with sql_mode='NO_ENGINE_SUBSTITUTION':
mysql> create table t6(a int, b GEOMETRY NOT NULL DEFAULT '');
ERROR 1101 (42000): BLOB, TEXT, GEOMETRY or JSON column 'b' can't have a default value

Thank you, Alfredo.

Any updates of this issue. Is this result is by design or exception?

Hi Mr. Shawn,

This is a bug in coding.

It is unknown in which release it will be fixed.

However, when it is fixed, you will get the update on this page. That means that you will get an e-mail that a bug is fixed.

Schedules for bug fixing are changing every week, so we can not provide any predictions.

Documented fix as follows in the MySQL 9.3.0 changelog:

    It was possible to create a table including a JSON column with
    DEFAULT '' under the default sql_mode, but the output from SHOW
    CREATE TABLE for this table run on another mysqld resulted in
    the error -BLOB, TEXT, GEOMETRY or JSON column ... can't have a
    default value- even when the sql_mode of the second mysqld was
    also non-strict. This issue did not occur with BLOB or TEXT
    columns.

Closed.

Thank you, Jon.