[MDEV-7845] update date column with erroneous date does not return an error but sets to null Created: 2015-03-26  Updated: 2015-04-30  Due: 2015-04-26  Resolved: 2015-04-30

Status: Closed
Project: MariaDB Server
Component/s: Temporal Types
Affects Version/s: 10.0.16
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Peter McLarty Assignee: Unassigned
Resolution: Not a Bug Votes: 0
Labels: None
Environment:

Redhat Linux 2.6.32-431.29.2.el6.x86_64
10.0.16-MariaDB-log

Issue Links:
Relates
relates to MDEV-7635 update defaults and simplify mysqld c... Closed

 Description   

We came across this issue when a developer tried to set a date value to 2015-04-31 only 30 days in April.
If using the above value it will set that date column to null not throw an error.

Test case

CREATE TABLE test.baddate (
 
  id int(11) NOT NULL AUTO_INCREMENT,
 
  testtext varchar(255) DEFAULT NULL,
 
  startdate date DEFAULT '1970-01-01',
 
  enddate date DEFAULT '1970-01-01',
 
  PRIMARY KEY (id),
 
  INDEX UK_baddate_testtext (testtext)
 
)
 
ENGINE = INNODB
 
AUTO_INCREMENT = 8
 
AVG_ROW_LENGTH = 5461
 
CHARACTER SET latin1
 
COLLATE latin1_swedish_ci
 
COMMENT = 'Testing of date bug in update with erroneous date';

Insert some values 

  INSERT INTO test.baddate(testtext) SELECT tp.Table_name FROM mysql.tables_priv tp;

Check the contents All dates set to 01/01/1970

  SELECT * FROM test.baddate;

Update to erroneous date value

  UPDATE test.baddate SET enddate = '2013-10-32';

Evaluate the result

  SELECT * FROM test.baddate;

Date is now null in the column set to erroneous date

 Comments   
Comment by Elena Stepanova [ 2015-03-26 ]

Hi Peter,

Date is now null in the column set to erroneous date

Do you actually mean NULL, or do you mean '0000-00-00'?
If you get NULL, please provide the output of SHOW VARIABLES and the full output of the commands that you run (you don't have to insert from tables_priv if it's confidential, just put some 'foo' values there).

If you mean '0000-00-00', then please note that when you run the UPDATE, you get a warning (or a bunch of warnings) like this:

MySQL [test]>  UPDATE test.baddate SET enddate = '2013-10-32';
 
Query OK, 1 row affected, 1 warning (0.55 sec)
 
Rows matched: 1  Changed: 1  Warnings: 1
 
 
 
MySQL [test]> show warnings;
 
+---------+------+----------------------------------------------+
 
| Level   | Code | Message                                      |
 
+---------+------+----------------------------------------------+
 
| Warning | 1265 | Data truncated for column 'enddate' at row 1 |
 
+---------+------+----------------------------------------------+
 
1 row in set (0.00 sec)

It works the same way in MySQL, it is an expected and documented behavior. See
http://dev.mysql.com/doc/refman/5.6/en/datetime.html

The server requires that month and day values be valid, and not merely in the range 1 to 12 and 1 to 31, respectively. With strict mode disabled, invalid dates such as '2004-04-31' are converted to '0000-00-00' and a warning is generated. With strict mode enabled, invalid dates generate an error. To permit such dates, enable ALLOW_INVALID_DATES. See Section 5.1.7, “Server SQL Modes”, for more information.

So, for the first example, '2015-04-31':

  • if sql_mode sets ALLOW_INVALID_DATES, it just runs quietly;
  • if sql_mode doesn't set either strict mode or ALLOW_INVALID_DATES, you get a warning;
  • if sql_mode sets strict mode but not ALLOW_INVALID_DATES, you get an error;

For the second example, '2013-10-32':

  • if sql_mode doesn't set strict mode, you get a warning;
  • if sql_mode sets strict mode, you get an error.

Hopes it clarifies the matter.

Comment by Elena Stepanova [ 2015-04-30 ]

Closing as not-a-bug for now. Please feel free to comment to re-open the issue if you disagree and have additional information.

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