MySQL Bugs: #101122: Default value expressions "(NOW())" AND "NOW()" behave differently

Bug #101122	Default value expressions "(NOW())" AND "NOW()" behave differently
Submitted:	11 Oct 2020 21:31	Modified:	16 Nov 2020 21:42
Reporter:	Moriyoshi Koizumi	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Data Types	Severity:	S3 (Non-critical)
Version:	8.0.21	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
If table contains a DATETIME column whose default value is an expression and it is NOW() (or CURRENT_TIMESTAMP) and is parenthesized, subsequent CREATE INDEX operations fail with ERROR 1067.

This doesn't happen if the expressoin is not parenthesized.

How to repeat:
mysql> CREATE TABLE foo (dt DATETIME NOT NULL DEFAULT NOW(), i INTEGER NOT NULL DEFAULT 0);
Query OK, 0 rows affected (0.02 sec)

mysql> SHOW CREATE TABLE foo\G
*************************** 1. row ***************************
       Table: foo
Create Table: CREATE TABLE `foo` (
  `dt` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `i` int NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

mysql> CREATE INDEX ix_foo_i ON foo (i);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> CREATE TABLE bar (dt DATETIME NOT NULL DEFAULT (NOW()), i INTEGER NOT NULL DEFAULT 0);
Query OK, 0 rows affected (0.01 sec)

mysql> SHOW CREATE TABLE bar\G
*************************** 1. row ***************************
       Table: bar
Create Table: CREATE TABLE `bar` (
  `dt` datetime NOT NULL DEFAULT (now()),
  `i` int NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

mysql> CREATE INDEX ix_bar_i ON bar (i);
ERROR 1067 (42000): Invalid default value for 'dt'

The DDL was actually emitted by SQLAlchemy (a Python ORM) and there is no workaround to configure it to not do so except modifying the ORM engine, so I consider it to be rather serious.

Hi Mr. Koisumi,

Thank you for your bug report.

However, this is not a bug.

Our Reference Manual clearly states that this syntax can not be used.

But, it could be possible to make this a feature request. That means, that syntax error is thrown on CREATE TABLE , ALTER TABLE and similar and not after.

What do you say ??????

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

As far as I see in your manual, it clearly say column definitions accept any valid expressions.

column_definition: {
    data_type [NOT NULL | NULL] [DEFAULT {literal | (expr)} ]
...

https://dev.mysql.com/doc/refman/8.0/en/create-table.html

Can you elaborate on what you were referring to ?

This is obviously a bug. Please reopen.

Hi Mr. Koizumi,

'0' is not a valid default for any DATE / TIME / DATETIME data type.

Hence, this is not a bug.