[MDEV-7824] [Bug #68041] Zero date can be inserted in strict no-zero mode through a default value Created: 2015-03-24  Updated: 2015-09-15  Resolved: 2015-06-26

Status: Closed
Project: MariaDB Server
Component/s: Temporal Types
Affects Version/s: 5.5.42, 10.0.17, 10.1.3
Fix Version/s: 10.1.6

Type: Bug Priority: Major
Reporter: Alexander Barkov Assignee: Alexander Barkov
Resolution: Fixed Votes: 0
Labels: upstream-fixed

Issue Links:
Blocks
blocks MDEV-3929 Add system variable explicit_defaults... Closed
Sprint: 10.1.6-1

 Description   

Upstream bug: http://bugs.mysql.com/bug.php?id=68041

I create a table with zero date:

SET sql_mode=DEFAULT;
 
DROP TABLE IF EXISTS t1;
 
CREATE TABLE t1 (a DATE  DEFAULT '0000-00-00');

Now change sql_mode to traditional:

SET sql_mode=traditional;
 
INSERT INTO t1 VALUES ('0000-00-00');

It correctly rejects the value:

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

However, It's still possible to insert the bad value through the default:

INSERT INTO t1 VALUES ();
 
SELECT * FROM t1;

The INSERT statement works (even without warnings) and the SELECT statement returns:

+------------+
 
| a          |
 
+------------+
 
| 0000-00-00 |
 
+------------+

The expected behaviour would be to reject the INSERT that uses the default value '0000-00-00'.

Using DEFAULT also does not reject:

SET sql_mode=DEFAULT;
 
DROP TABLE IF EXISTS t1;
 
CREATE TABLE t1 (a TIMESTAMP DEFAULT '0000-00-00 00:00:00');
 
SET sql_mode=traditional;
 
INSERT INTO t1 VALUES (DEFAULT);

It also does not reject bad values when using INSERT..SELECT:

SET sql_mode=DEFAULT;
 
DROP TABLE IF EXISTS t1,t2;
 
CREATE TABLE t1 (a DATE DEFAULT '0000-00-00');
 
CREATE TABLE t2 (a DATE DEFAULT '0000-00-00');
 
INSERT INTO t2 VALUES ('0000-00-00');
 
SET sql_mode=traditional;
 
INSERT INTO t1 (a) SELECT a FROM t2;
 
DROP TABLE t1, t2;



 Comments   
Comment by Alexander Barkov [ 2015-03-24 ]

In MySQL-5.7, the patch for explicit_defaults_for_timestamp depends on the code that fixes this bug.

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