MySQL Bugs: #94668: Expression Default is made NULL during CREATE TABLE query, if field is made PK

Bug #94668	Expression Default is made NULL during CREATE TABLE query, if field is made PK
Submitted:	15 Mar 2019 8:38	Modified:	30 Jul 2019 14:17
Reporter:	James Quille	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Data Types	Severity:	S3 (Non-critical)
Version:	8.0.15	OS:	MacOS (Mojave 10.14.3)
Assigned to:		CPU Architecture:	Any
Tags:	CREATE TABLE, DEFAULT, Expression Default, null, pk, primary key

Description:
# Create table, 1 field with Expression Default (but no PK)
# After CREATE TABLE, then ALTER the table to add the PK
# This works, the Default for 1 field is still populated after the ALTER and is PK

# Create table, 1 field with Expression Default and is also PK
# This does not work, the Default for 1 field is NULL (but is set as PK)

How to repeat:
# https://dev.mysql.com/doc/refman/8.0/en/data-type-defaults.html
# Create table with Expression Default (but no PK)
# After CREATE, then ALTER the table to add the PK
# This works, note the Default is still populated after the ALTER
CREATE TABLE `1_keeps_default_after_alter_to_add_pk` (`id` binary(16) NOT NULL DEFAULT (uuid_to_bin(uuid(),true))) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
ALTER TABLE `1_keeps_default_after_alter_to_add_pk` ADD PRIMARY KEY(`id`);
DESCRIBE `1_keeps_default_after_alter_to_add_pk`;

# Create table by using the syntax from SHOW CREATE TABLE
# This will create table with Expression Default AND also include PK
# However this does not work, note the NULL Default
SHOW CREATE TABLE `1_keeps_default_after_alter_to_add_pk`;
CREATE TABLE `2_null_default_when_created_with_pk` (`id` binary(16) NOT NULL DEFAULT (uuid_to_bin(uuid(),true)),PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
DESCRIBE `2_null_default_when_created_with_pk`;

# Create table by using CREATE TABLE LIKE 
# This works, note the Default is populated
CREATE TABLE `3_keeps_default_for_create_like` LIKE `1_keeps_default_after_alter_to_add_pk`;
DESCRIBE `3_keeps_default_for_create_like`;

Suggested fix:
Field should be allowed to be PK and to have Expression Default at same time, when made using CREATE TABLE query.

Hi Mr. Quille,

Thank you for your report.

However, I was not able to repeat it with 8.0.15. This is what I get :

1_keeps_default_after_alter_to_add_pk	CREATE TABLE `1_keeps_default_after_alter_to_add_pk` (\n  `id` binary(16) NOT NULL DEFAULT (uuid_to_bin(uuid(),true)),\n  PRIMARY KEY (`id`)\n) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
Table	Create Table
2_null_default_when_created_with_pk	CREATE TABLE `2_null_default_when_created_with_pk` (\n  `id` binary(16) NOT NULL,\n  PRIMARY KEY (`id`)\n) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
Table	Create Table
3_keeps_default_for_create_like	CREATE TABLE `3_keeps_default_for_create_like` (\n  `id` binary(16) NOT NULL DEFAULT (uuid_to_bin(uuid(),true)),\n  PRIMARY KEY (`id`)\n) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

Hence, can you try 8.0.15 and see if your problem still persists ????

Hi  Sinisa,

I am still seeing the issue. To update on what I've done ;

A.)
I've upgraded to 8.0.15 (confirmed using SHOW VARIABLES LIKE "%version%";)

B.)
I run the below Query to create a table with a populated default...

CREATE TABLE `default_not_saved` (
  `id` binary(16) NOT NULL DEFAULT (uuid_to_bin(uuid(),true)),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

C.)
I see that the default is still not stored, despite being explicitly stated in the create query.
This is confirmed via query where the default is shown to be NULL (DESCRIBE `default_not_saved`;)

I've gone so far as to test this on Docker, FROM mysql:8.0.15
https://hub.docker.com/_/mysql

What are your thoughts?

Hi,

Can you try using uuid_to_bin() with a single parameter ???

Can you also try not using 'true' , but 1 ?????

Please, let me know the results.

Hi Sinisa,

Still seeing the issue unfortunately.

A)
As per your suggestion, I created a table using 1 instead of TRUE.

CREATE TABLE `4_using1_notusingTRUE` (`id` binary(16) NOT NULL DEFAULT (uuid_to_bin(uuid(),1)),PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
DESCRIBE `4_using1_notusingTRUE`;

Please note that the DEFAULT value of the created table is NULL (undesired).

B)
As per your suggestion, I created a table using 1 parameter.

CREATE TABLE `5_using_single_param` (`id` binary(16) NOT NULL DEFAULT (uuid_to_bin(uuid())),PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
DESCRIBE `5_using_single_param`;

Please note that the DEFAULT value of the created table is NULL (undesired).

C-1) Just to circle back on highlighting the main issue.
It is possible to create the desired DEFAULT and also still maintain the primary key. However it requires a CREATE followed by an ALTER

CREATE TABLE `x` (`id` binary(16) NOT NULL DEFAULT (uuid_to_bin(uuid()))) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
ALTER TABLE `x` ADD PRIMARY KEY(`id`);
DESCRIBE `x`;

Please note that the DEFAULT value of the created table is populated and the primary key is intact (desired).

C-2) In fact, you can even successfully copy this table using CREATE LIKE

CREATE TABLE `y` LIKE `x`;
DESCRIBE `y`;

Please note that the DEFAULT value of the created table is populated and the primary key is intact (desired).

C-3) The main issue I see is that the CREATE TABLE syntax fails to properly create this table, even when taken from an existing table.

SHOW CREATE TABLE `x`;
CREATE TABLE `z` (
  `id` binary(16) NOT NULL DEFAULT (uuid_to_bin(uuid())),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
DESCRIBE `z`;

Please note that the DEFAULT value of the created table is NULL (undesired).

Hi,

I have run your test case 10 (ten) times and each time it succeeded with all variants that you have tested:

Table	Create Table
1_keeps_default_after_alter_to_add_pk	CREATE TABLE `1_keeps_default_after_alter_to_add_pk` (\n  `id` binary(16) NOT NULL DEFAULT (uuid_to_bin(uuid(),true)),\n  PRIMARY KEY (`id`)\n) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
Table	Create Table
2_null_default_when_created_with_pk	CREATE TABLE `2_null_default_when_created_with_pk` (\n  `id` binary(16) NOT NULL,\n  PRIMARY KEY (`id`)\n) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
Table	Create Table
3_keeps_default_for_create_like	CREATE TABLE `3_keeps_default_for_create_like` (\n  `id` binary(16) NOT NULL DEFAULT (uuid_to_bin(uuid(),true)),\n  PRIMARY KEY (`id`)\n) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

Hence, this bug can not be verified as it is not repeatable.

Hi Sinisa,

I've just created a brand new MYSQL Docker container FROM mysql:8.0.15
Using this completely fresh instance, I have replicated the issue.

mysql> CREATE TABLE `bug_is_blank_default` ( `id` binary(16) NOT NULL DEFAULT (uuid_to_bin(uuid())), PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
Query OK, 0 rows affected (0.07 sec)

mysql> DESCRIBE `bug_is_blank_default`;
+-------+------------+------+-----+---------+-------------------+
| Field | Type       | Null | Key | Default | Extra             |
+-------+------------+------+-----+---------+-------------------+
| id    | binary(16) | NO   | PRI | NULL    | DEFAULT_GENERATED |
+-------+------------+------+-----+---------+-------------------+
1 row in set (0.00 sec)

Please note that both Querys execute without issue.
The bug is that the DEFAULT value is NULL.

When you run DESCRIBE, what do you see?

Hi,

You saw what I have got in my two outputs.

I ran the following queries:

CREATE TABLE `1_keeps_default_after_alter_to_add_pk` (`id` binary(16) NOT NULL DEFAULT (uuid_to_bin(uuid(),true))) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
ALTER TABLE `1_keeps_default_after_alter_to_add_pk` ADD PRIMARY KEY(`id`);
SHOW CREATE TABLE `1_keeps_default_after_alter_to_add_pk`;

CREATE TABLE `2_null_default_when_created_with_pk` (`id` binary(16) NOT NULL DEFAULT (uuid_to_bin(uuid(),true)),PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
SHOW CREATE TABLE  `2_null_default_when_created_with_pk`;

CREATE TABLE `3_keeps_default_for_create_like` LIKE `1_keeps_default_after_alter_to_add_pk`;
SHOW CREATE TABLE  `3_keeps_default_for_create_like`;

I do not use DESCRIBE as it is a bit outdated, and you can see that default is nicely shown for me ......

Hi Sinisa,

Can I please request your support for one last review of my approach?

1.) Create a brand new Docker Container by downloading the latest MYSQL 8.0.15

docker run --name freshtestmysql -e MYSQL_ROOT_PASSWORD=bugtest -d mysql:8.0.15

2.) Log in via bash

docker exec -it freshtestmysql bash

3.) Access root

mysql -u root -p

4.) Enter root password "bugtest"

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 8.0.15 MySQL Community Server - GPL

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

5.) Create testdb

mysql> CREATE database testdb;
Query OK, 1 row affected (0.00 sec)

6.) Switch to testdb

mysql> USE testdb;
Database changed

7.) Create table `checkthedefault` which has an Expression Default of "uuid_to_bin()"

mysql> CREATE TABLE `checkthedefault` ( `id` binary(16) NOT NULL DEFAULT (uuid_to_bin(uuid())), PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
Query OK, 0 rows affected (0.02 sec)

8.) Check if the Expression Default was saved correctly by running the query SHOW CREATE TABLE

mysql> SHOW CREATE TABLE `checkthedefault`;
+-----------------+------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table           | Create Table                                                                                                                                         |
+-----------------+------------------------------------------------------------------------------------------------------------------------------------------------------+
| checkthedefault | CREATE TABLE `checkthedefault` (
  `id` binary(16) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-----------------+------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

The Query was successful but please note the result of the query "`id` binary(16) NOT NULL,"
Because the Expression Default of "uuid_to_bin()" is not saved, this confirms that there is a bug.

Can you please share your thoughts if this is a bug with MYSQL?

Thank you,
James

Hi,

Thank you for your bug report.

However, I do not use Docker at all. Also, this is not a forum for Docker bugs, but only for MySQL bugs. Docker has its own set of restrictions, which are configurable and we do not deal with those either.

You need to be able to repeat the behaviour with MySQL server running without Docker.

Hi Sinisa,

I've downloaded mysql-8.0.15-macos10.14-x86_64.dmg from https://dev.mysql.com/downloads/mysql/ and installed it to a fresh macOS Mojave 10.14.3 install.

Using MySQL Workbench, I've ran the following 4 commands

09:54:24	CREATE database testdb	1 row(s) affected	0.0042 sec

09:55:07	USE testdb	0 row(s) affected	0.00049 sec

09:56:00	CREATE TABLE `checkthedefault` ( `id` binary(16) NOT NULL DEFAULT (uuid_to_bin(uuid())), PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci	0 row(s) affected	0.011 sec

09:56:40	SHOW CREATE TABLE `checkthedefault`	1 row(s) returned	0.0091 sec / 0.0000091 sec

The result was 

'CREATE TABLE `checkthedefault` (
  `id` binary(16) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci' 

As you can see, the Expression Default was again NOT saved. Can you please confirm?
This is a repeatable bug. The issue is not that the queries fail to run, its that data is lost from the table structure (i.e. the Default value).

HI,

I have managed to repeat the behaviour you report:

mysql> CREATE TABLE bug ( `id` binary(16) NOT NULL DEFAULT (uuid_to_bin(uuid())), PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
Query OK, 0 rows affected (0.06 sec)

mysql> show create table bug;
+-------+------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                             |
+-------+------------------------------------------------------------------------------------------------------------------------------------------+
| bug   | CREATE TABLE `bug` (
  `id` binary(16) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------+------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Verified as reported.

Posted by developer:
 
Fixed in 8.0.19.

For a column defined as a PRIMARY KEY in a CREATE TABLE statement, a
default value given as an expression was ignored.

Thank you, Paul.