Default value for TEXT, BLOB or JSON columns

[dveeden]

Bug Report

TiDB doesn't allow a default value for TEXT, BLOB or JSON columns.

1. Minimal reproduce step (Required)

CREATE TABLE t1 (id INT PRIMARY KEY, c1 TEXT DEFAULT 'foo');
CREATE TABLE t1 (id INT PRIMARY KEY, c1 TEXT DEFAULT ('foo'));
SHOW CREATE TABLE t1\G
INSERT INTO t1(id) VALUES(1);
TABLE t1;

2. What did you expect to see? (Required)

With MySQL 8.1.0:

sql> CREATE TABLE t1 (id INT PRIMARY KEY, c1 TEXT DEFAULT 'foo');
ERROR: 1101 (42000): BLOB, TEXT, GEOMETRY or JSON column 'c1' can't have a default value

sql> CREATE TABLE t1 (id INT PRIMARY KEY, c1 TEXT DEFAULT ('foo'));
Query OK, 0 rows affected (0.0586 sec)

sql> SHOW CREATE TABLE t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `id` int NOT NULL,
  `c1` text COLLATE utf8mb4_general_ci DEFAULT (_utf8mb4'foo'),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
1 row in set (0.0184 sec)

sql> INSERT INTO t1(id) VALUES(1);
Query OK, 1 row affected (0.0076 sec)

sql> TABLE t1;
+----+-----+
| id | c1  |
+----+-----+
|  1 | foo |
+----+-----+
1 row in set (0.0009 sec)

3. What did you see instead (Required)

sql> CREATE TABLE t1 (id INT PRIMARY KEY, c1 TEXT DEFAULT 'foo');
ERROR: 1101 (42000): BLOB/TEXT/JSON column 'c1' can't have a default value

sql> CREATE TABLE t1 (id INT PRIMARY KEY, c1 TEXT DEFAULT ('foo'));
ERROR: 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your TiDB version for the right syntax to use line 1 column 59 near "'foo'))" 

...

4. What is your TiDB version? (Required)

Release Version: v7.2.0
Edition: Community
Git Commit Hash: 9fd5f4a8e4f273a60fbe7d3848f85a1be8f0600b
Git Branch: heads/refs/tags/v7.2.0
UTC Build Time: 2023-06-27 15:04:42
GoVersion: go1.20.5
Race Enabled: false
Check Table Before Drop: false
Store: tikv

[dveeden]

Looks like #10377 and #32608 should have covered this.
cc @CbcWestwolf

[dveeden]

Note that MySQL 8.0.34 and MySQL 8.1.0 now support CURRENT_USER() and some similar functions as default values.

• https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-34.html#mysqld-8-0-34-sql-syntax
• https://dev.mysql.com/doc/relnotes/mysql/8.1/en/news-8-1-0.html#mysqld-8-1-0-sql-syntax

[CbcWestwolf]

TiDB only supports several functions (nextVal(), now(), current_timestamp(), curdate() and rand()) as default values on the limited types of columns.
For example, TiDB does not support default md5() like create table test.tt (c1 int, c2 char(64) default (md5(c1)));.
We would implement these for better compatibility with MySQL :-)

[dveeden]

@CbcWestwolf Yes, this includes allowing users to use a UUID PK.

CREATE TABLE t(
  uuid BINARY(16) PRIMARY KEY DEFAULT (UUID_TO_BIN(UUID())),
  c1 VARCHAR(255) NOT NULL
);

sql> CREATE TABLE t(uuid BINARY(16) PRIMARY KEY DEFAULT (UUID_TO_BIN(UUID())), c1 VARCHAR(255) NOT NULL);
Query OK, 0 rows affected (0.0607 sec)

sql> INSERT INTO t(c1) VALUES ('test');
Query OK, 1 row affected (0.0174 sec)

sql> INSERT INTO t(c1) VALUES ('barfoo');
Query OK, 1 row affected (0.0182 sec)

sql> INSERT INTO t(c1) VALUES ('foobar');
Query OK, 1 row affected (0.0185 sec)

sql> SELECT BIN_TO_UUID(uuid),c1 FROM t;
+--------------------------------------+--------+
| BIN_TO_UUID(uuid)                    | c1     |
+--------------------------------------+--------+
| e3ed2dcc-29f5-11ee-a50a-5405db7aad56 | test   |
| e5aa4d84-29f5-11ee-a50a-5405db7aad56 | barfoo |
| e798b182-29f5-11ee-a50a-5405db7aad56 | foobar |
+--------------------------------------+--------+
3 rows in set (0.0009 sec)

sql> SELECT VERSION();
+-----------+
| VERSION() |
+-----------+
| 8.1.0     |
+-----------+
1 row in set (0.0010 sec)

TiDB:

sql> CREATE TABLE t(uuid BINARY(16) PRIMARY KEY DEFAULT (UUID_TO_BIN(UUID())), c1 VARCHAR(255) NOT NULL);
ERROR: 3770 (HY000): Default value expression of column 'uuid' contains a disallowed function: `UUID_TO_BIN`.

[dveeden]

This now works with TiDB:

sql> CREATE TABLE t(
   ->   uuid BINARY(16) PRIMARY KEY DEFAULT (UUID_TO_BIN(UUID())),
   ->   c1 VARCHAR(255) NOT NULL
   -> );
Query OK, 0 rows affected (0.0200 sec)

sql> INSERT INTO t(c1) VALUES ('test');
Query OK, 1 row affected (0.0011 sec)

sql> INSERT INTO t(c1) VALUES ('barfoo');
Query OK, 1 row affected (0.0008 sec)

sql> INSERT INTO t(c1) VALUES ('foobar');
Query OK, 1 row affected (0.0008 sec)

sql> SELECT BIN_TO_UUID(uuid),c1 FROM t;
+--------------------------------------+--------+
| BIN_TO_UUID(uuid)                    | c1     |
+--------------------------------------+--------+
| b72c6aa5-db93-11ee-afda-5405db7aad56 | test   |
| b90c318e-db93-11ee-afda-5405db7aad56 | barfoo |
| bb3b3236-db93-11ee-afda-5405db7aad56 | foobar |
+--------------------------------------+--------+
3 rows in set (0.0014 sec)

sql> SELECT TIDB_VERSION()\G
*************************** 1. row ***************************
TIDB_VERSION(): Release Version: v8.0.0-alpha-407-ga1a64d6bfe
Edition: Community
Git Commit Hash: a1a64d6bfea1b44d12bb762b92b50a1ee8b0d48c
Git Branch: admin_check_info
UTC Build Time: 2024-03-06 08:27:11
GoVersion: go1.21.0
Race Enabled: false
Check Table Before Drop: false
Store: unistore
1 row in set (0.0004 sec)

@CbcWestwolf I think we can close this isue