[#MDEV-8373] Zero date can be inserted in strict no-zero mode through CREATE TABLE AS SELECT timestamp

[MDEV-8373] Zero date can be inserted in strict no-zero mode through CREATE TABLE AS SELECT timestamp_field Created: 2015-06-25 Updated: 2015-06-26 Resolved: 2015-06-26
Status:	Closed
Project:	MariaDB Server
Component/s:	Temporal Types
Affects Version/s:	5.5, 10.0, 10.1
Fix Version/s:	10.1.6

Type:

Bug

Priority:

Major

Reporter:

Alexander Barkov

Assignee:

Alexander Barkov

Resolution:

Fixed

Votes:

Labels:

None

Description

set sql_mode=default;

drop table if exists t1;

create table t1 (a timestamp);

insert into t1 values (0);

set sql_mode='TRADITIONAL';

drop table if exists t2;

create table t2 as select * from t1;

The last statement should fail on error, because zero date is not allowed by sql_mode.

Note, if I change data time from TIMESTAMP to DATE or DATETIME, it correctly returns the error:

ERROR 1292 (22007): Incorrect datetime value: '0000-00-00 00:00:00' for column 'a' at row 1

Generated at Thu Feb 08 07:26:40 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.