[MDEV-7845] update date column with erroneous date does not return an error but sets to null - JIRA

Details

Type: Bug
Status: Closed
Priority: Major
Resolution: Not a Bug
Affects Version/s: 10.0.16
Fix Version/s: N/A
Component/s: Temporal Types
Labels:
None
Environment:
Redhat Linux 2.6.32-431.29.2.el6.x86_64
10.0.16-MariaDB-log

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

Gliffy Diagrams

Attachments

Issue Links

relates to

MDEV-7635 update defaults and simplify mysqld config parameters

Open

Activity

Ascending order - Click to sort in descending order

Hide

Permalink

Elena Stepanova added a comment - 2015-03-26 13:01

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.

Show

Elena Stepanova added a comment - 2015-03-26 13:01 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.

Hide

Permalink

Elena Stepanova added a comment - 2015-04-30 15:09

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

Show

Elena Stepanova added a comment - 2015-04-30 15:09 Closing as not-a-bug for now. Please feel free to comment to re-open the issue if you disagree and have additional information.

update date column with erroneous date does not return an error but sets to null

Details

Description

Gliffy Diagrams

Attachments

Issue Links

Activity

People

Dates

Agile