[MDEV-27928] ERROR 1901 (HY000): Function or expression '`UUID_TO_BIN`()' cannot be used in the DEFAULT clause Created: 2022-02-23  Updated: 2022-02-24  Resolved: 2022-02-23

Status: Closed
Project: MariaDB Server
Component/s: Data Definition - Create Table
Affects Version/s: 10.6.7
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Roger S Assignee: Unassigned
Resolution: Not a Bug Votes: 0
Labels: None
Environment:

Redhat 8

Issue Links:
Relates
relates to MDEV-15854 Implement uuid_to_bin, bin_to_uuid an... Open

 Description   

Hi,

Supposedly it is possible to run the following:

CREATE TABLE `session` (
 
  `id` binary(16) NOT NULL DEFAULT (UUID_TO_BIN(UUID(), TRUE)),
 
  `start_timestamp` timestamp NOT NULL, 
  `end_timestamp` timestamp NULL DEFAULT NULL,
 
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
 
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 
  `status` varchar(20) DEFAULT NULL
 
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

But there's an error saying:

ERROR 1901 (HY000): Function or expression '`UUID_TO_BIN`()' cannot be used in the DEFAULT clause of `id`

According to https://stackoverflow.com/questions/60462208/mysql-8-0-13-default-value-as-uuid-not-working it should work on MySQL anyway.

And for MariaDB, supposedly all deterministic functions should be supported in the DEFAULT clause, no?

Please help, thanks!

 Comments   
Comment by Elena Stepanova [ 2022-02-23 ]

MariaDB doesn't have the function. Admittedly the error message isn't very helpful, it assumes that you are trying to use a stored function or something.
This would be clearer:

MariaDB [test]> select UUID_TO_BIN(UUID());
 
ERROR 1305 (42000): FUNCTION test.UUID_TO_BIN does not exist

The absence of the function is even explicitly documented here:
https://mariadb.com/kb/en/function-differences-between-mariadb-106-and-mysql-80/

Comment by Daniel Black [ 2022-02-24 ]

Note that 10.7 has uuids as a native type: https://mariadb.org/10-7-preview-feature-uuid-data-type/

Comment by Roger S [ 2022-02-24 ]

@elena: Thanks for the prompt reply! As a workaround I have just manually created the corresponding functions by using https://stackoverflow.com/a/58015720/10769628 as example.

@daniel: Thanks for the hint! Too bad I'm on AWS RDS which currently only supports MariaDB up to 10.6.

Generated at Thu Feb 08 09:56:42 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.