MySQL Bugs: #114450: Cannot use standard SQL DATE or TIMESTAMP literal in DEFAULT expression

Bug #114450	Cannot use standard SQL DATE or TIMESTAMP literal in DEFAULT expression
Submitted:	22 Mar 2024 11:13	Modified:	3 Apr 2024 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

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'});

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.

ISO/IEC 9075-2:2016(E) 11.5 <default clause> specifies:

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

And <literal> is, among other things, a <datetime literal>

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

Hi,

Literals are not a problem here .

The problem is in your usage of the introducers ......

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.

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.