Bug #114450 Cannot use standard SQL DATE or TIMESTAMP literal in DEFAULT expression
Submitted: 22 Mar 11:13 Modified: 3 Apr 12:53
Reporter: Lukas Eder Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: DDL Severity:S3 (Non-critical)
Version:8.30.0 OS:Any
Assigned to: CPU Architecture:Any

[22 Mar 11:13] Lukas Eder
Description:
I cannot use the standard SQL DATE or TIMESTAMP literals as a DEFAULT expression for DATETIME columns. I don't see why these shouldn't work. There's no mention of it here in the documentation:
https://dev.mysql.com/doc/refman/8.0/en/date-and-time-literals.html

How to repeat:
-- This doesn't work:
create table t (b datetime default timestamp '1970-01-01 00:00:00.0');

-- SQL Error [1067] [42000]: Invalid default value for 'b'

-- Both of these do
create table t (b datetime default '1970-01-01 00:00:00.0');
create table t (b datetime default {ts '1970-01-01 00:00:00.0'});
[22 Mar 11:34] MySQL Verification Team
Hi Mr. Eder,

Thank you for your bug report.

However, this is not a bug.

MySQL strictly follows SQL Standard and the current one is SQL Standard 2016. It allows usage of the expressions in the query, but not in the CREATE TABLE command. Hence, in the queries you can use TIMESTAMP as an introducers. According to the said SQL Standard, expressions are not allowed for the attribute definitions in DDLs.

Hence, we did not document it, since SQL Standard has documented it in Chapter 2, clause 11.3.

Not a bug.
[22 Mar 11:44] Lukas Eder
ISO/IEC 9075-2:2016(E) 11.5 <default clause> specifies:

<default option> ::=
    <literal> 
  | ...

And <literal> is, among other things, a <datetime literal>
[27 Mar 14:00] Lukas Eder
May I ask you to review your assessment? Standard SQL allows for timestamp *literals* to be placed in DEFAULT expressions (not just string literals), so I'd say this is a bug (or a limitation worth documenting).
[27 Mar 14:50] MySQL Verification Team
Hi,

Literals are not a problem here .

The problem is in your usage of the introducers ......
[3 Apr 12:53] Lukas Eder
I don't know what you mean by "introducer." The TIMESTAMP keyword? But the SQL standard ISO/IEC 9075-2:2016(E) <timestamp literal> is precisely this:

<timestamp literal> ::=
  TIMESTAMP <timestamp string>

I don't understand why it isn't supported here.
[3 Apr 13:12] MySQL Verification Team
Hi,

Actually, this syntax is specified already in SQL 2011, but it was decided, internally, that it will not be supported, since there is a very little demand for that syntax.

Actually, this is very first report where someone expressed interest in that syntax.

Hence, it is not supported and we do not know whether it will ever be supported.