Description:
On 8.4.11:
CREATE TABLE t1 (j JSON NOT NULL);
CREATE TABLE t2 AS SELECT COALESCE(j, j) as c FROM t1;
SHOW CREATE TABLE t2;
Table Create Table
t2 CREATE TABLE `t2` (
`c` json NOT NULL DEFAULT 'null'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
# t2 DEFAULT is active:
INSERT INTO t2 VALUES ();
# SHOW CREATE TABLE cannot be used as CREATE TABLE:
CREATE TABLE t3 (c JSON NOT NULL DEFAULT 'null');
ERROR 42000: BLOB, TEXT, GEOMETRY or JSON column 'c' can't have a default value
DROP TABLE t2, t1;
On 9.7.2 we get warnings and "correct" SHOW CREATE TABLE but the default is still active:
CREATE TABLE t1 (j JSON NOT NULL);
CREATE TABLE t2 AS SELECT COALESCE(j, j) as c FROM t1;
Warnings:
Warning 1101 BLOB, TEXT, GEOMETRY or JSON column 'c' can't have a default value
SHOW CREATE TABLE t2;
Table Create Table
t2 CREATE TABLE `t2` (
`c` json NOT NULL /* DEFAULT 'null' */
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
Warnings:
Warning 1101 BLOB, TEXT, GEOMETRY or JSON column 'c' can't have a default value
# DEFAULT is active:
INSERT INTO t2 VALUES ();
DROP TABLE t2, t1;
How to repeat:
MTR:
CREATE TABLE t1 (j JSON NOT NULL);
CREATE TABLE t2 AS SELECT COALESCE(j, j) as c FROM t1;
SHOW CREATE TABLE t2;
INSERT INTO t2 VALUES ();
--error 1101
CREATE TABLE t3 (c JSON NOT NULL DEFAULT 'null');
DROP TABLE t2, t1;
