Bug #104957 table creation failed because of order of columns

• Submitted: 16 Sep 2021 10:04
• Modified: 16 Sep 2021 12:51
• Reporter: Ashish Katiyar
• Status: Not a Bug
• Category: MySQL Server: Data Types
• Severity: S3 (Non-critical)
• OS: Any
• CPU Architecture: Any

[16 Sep 2021 10:04] Ashish Katiyar

Description:
CREATE TABLE test.test2 (
  PersonID int,
  id varchar(20),
  date_end timestamp null,
  date_st timestamp,
  primary key (id, date_st)
)
this query fails
CREATE TABLE test.test2 (
  PersonID int,
  id varchar(20),
  date_st timestamp,
  date_end timestamp null,
  primary key (id, date_st)
)
this works

How to repeat:
CREATE TABLE test.test2 (
  PersonID int,
  id varchar(20),
  date_end timestamp null,
  date_st timestamp,
  primary key (id, date_st)
)
this query fails
CREATE TABLE test.test2 (
  PersonID int,
  id varchar(20),
  date_st timestamp,
  date_end timestamp null,
  primary key (id, date_st)
)
this works

Suggested fix:
default value should be considered in 1st case also.

[16 Sep 2021 12:51] MySQL Verification Team

Hi Mr. Ktiyar,

Thank you for your bug report.

However, it is not a bug.

We presume that you are using 8.0.36 release, since that is the latest one which has so many of the known bugs fixed.

The behaviour that you describe is the expected one. Please, read the sub-chapter 11.2.5 of our 8.0 Reference Manual. Simply, TIMESTAMP columns have to be initialised, unless one configuration variable is used.

Not a bug.