Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-27928

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

Details

    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!

      Attachments

        Issue Links

          Activity

            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/

            elenst Elena Stepanova added a comment - 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/
            danblack Daniel Black added a comment -

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

            danblack Daniel Black added a comment - Note that 10.7 has uuids as a native type: https://mariadb.org/10-7-preview-feature-uuid-data-type/
            MariaDBFreak Roger S added a comment -

            @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.

            MariaDBFreak Roger S added a comment - @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.

            People

              Unassigned Unassigned
              MariaDBFreak Roger S
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.