UUID() for BINARY column results in syntax error

[dveeden]

Bug Report

#33870 allows the use of the UUID() function as default value for a column. However it doesn't seem to work for binary columns.

1. Minimal reproduce step (Required)

CREATE TABLE t1 (id INT PRIMARY KEY, u BINARY(50));
CREATE TABLE t2 (id INT PRIMARY KEY, u CHAR(50));
INSERT INTO t1 VALUES (1, UUID());
INSERT INTO t2 VALUES (1, UUID());
SELECT CONVERT(u USING utf8mb4) FROM t1;
SELECT u FROM t2;
CREATE TABLE t3 (id INT PRIMARY KEY, u CHAR(50) DEFAULT (UUID()));
CREATE TABLE t4 (id INT PRIMARY KEY, u BINARY(50) DEFAULT (UUID()));
INSERT INTO t3(id) VALUES(1);
TABLE t3;

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

sql> CREATE TABLE t1 (id INT PRIMARY KEY, u BINARY(50));
Query OK, 0 rows affected (0.0659 sec)

sql> CREATE TABLE t2 (id INT PRIMARY KEY, u CHAR(50));
Query OK, 0 rows affected (0.0529 sec)

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

sql> INSERT INTO t2 VALUES (1, UUID());
Query OK, 1 row affected (0.0103 sec)

sql> SELECT CONVERT(u USING utf8mb4) FROM t1;
+----------------------------------------------------+
| CONVERT(u USING utf8mb4)                           |
+----------------------------------------------------+
| 70309773-9b35-11ee-9334-5405db7aad56               |
+----------------------------------------------------+
1 row in set (0.0005 sec)

sql> SELECT u FROM t2;
+--------------------------------------+
| u                                    |
+--------------------------------------+
| 7031f11f-9b35-11ee-9334-5405db7aad56 |
+--------------------------------------+
1 row in set (0.0003 sec)

sql> CREATE TABLE t3 (id INT PRIMARY KEY, u CHAR(50) DEFAULT (UUID()));
Query OK, 0 rows affected (0.0550 sec)

sql> CREATE TABLE t4 (id INT PRIMARY KEY, u BINARY(50) DEFAULT (UUID()));
Query OK, 0 rows affected (0.0474 sec)

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

sql> TABLE t3;
+----+--------------------------------------+
| id | u                                    |
+----+--------------------------------------+
|  1 | 70435e80-9b35-11ee-9334-5405db7aad56 |
+----+--------------------------------------+
1 row in set (0.0006 sec)

sql> SELECT VERSION();
+-----------+
| VERSION() |
+-----------+
| 8.2.0     |
+-----------+
1 row in set (0.0007 sec)

3. What did you see instead (Required)

sql> CREATE TABLE t1 (id INT PRIMARY KEY, u BINARY(50));
Query OK, 0 rows affected (0.1806 sec)

sql> CREATE TABLE t2 (id INT PRIMARY KEY, u CHAR(50));
Query OK, 0 rows affected (0.1680 sec)

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

sql> INSERT INTO t2 VALUES (1, UUID());
Query OK, 1 row affected (0.0161 sec)

sql> SELECT CONVERT(u USING utf8mb4) FROM t1;
+----------------------------------------------------+
| CONVERT(u USING utf8mb4)                           |
+----------------------------------------------------+
| 6b3b43de-9b34-11ee-b2ac-5405db7aad56               |
+----------------------------------------------------+
1 row in set (0.0030 sec)

sql> SELECT u FROM t2;
+--------------------------------------+
| u                                    |
+--------------------------------------+
| 6f32262e-9b34-11ee-b2ac-5405db7aad56 |
+--------------------------------------+
1 row in set (0.0018 sec)

sql> CREATE TABLE t3 (id INT PRIMARY KEY, u CHAR(50) DEFAULT (UUID()));
Query OK, 0 rows affected (0.1789 sec)

sql> CREATE TABLE t4 (id INT PRIMARY KEY, u BINARY(50) DEFAULT (UUID()));
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 13 near "

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

sql> TABLE t3;
+----+--------------------------------------+
| id | u                                    |
+----+--------------------------------------+
|  1 | b07516fb-9b34-11ee-b2ac-5405db7aad56 |
+----+--------------------------------------+
1 row in set (0.0035 sec)

4. What is your TiDB version? (Required)

Release Version: v7.5.0
Edition: Community
Git Commit Hash: 069631e2ecfedc000ffb92c67207bea81380f020
Git Branch: heads/refs/tags/v7.5.0
UTC Build Time: 2023-11-24 08:50:14
GoVersion: go1.21.3
Race Enabled: false
Check Table Before Drop: false
Store: tikv

[dveeden]

Not that this happens for BINARY(50) and CHAR(50) CHARACTER SET ascii as well.

sql> CREATE TABLE t4 (id INT PRIMARY KEY, u CHAR(50) CHARACTER SET binary DEFAULT (UUID()));
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 13 near "

sql> CREATE TABLE t4 (id INT PRIMARY KEY, u CHAR(50) CHARACTER SET ascii DEFAULT (UUID()));
Query OK, 0 rows affected (0.1609 sec)

[windtalker]

@jebter I think the sig should be sig/infra?