CODEWITHSUNDEEP
mysqldatemysql-5.7
user3590489
user3590489

Reputation: 913

MYSQL date validation head-scratcher

I'm running into an issue trying to insert dates into a table that I can't figure out for the life of me. Let's say I do this:

select "2018-11-40" from dual where dayname("2018-02-31") is not null;

I correctly get 0 rows back, since Nov 40th isn't a valid day.

But if I have a table MY_TABLE with a date field EVENT_DATE, and do this:

insert into MY_TABLE (EVENT_DATE)

select "2018-11-40" from dual where dayname("2018-11-40") is not null;

It errors out with: Incorrect datetime value: '2018-11-40', rather than running with no errors and 0 rows affected.

(dayname("2018-11-40") is not null) definitely should resolve as false before the insert... and if I do this:

insert into MY_TABLE (EVENT_DATE)
select "2018-11-40" from dual where 1=2;

everything works fine (no errors. 0 rows affected).

So what gives? This is on MySQL 5.7.21

Upvotes: 1

Views: 60

Answers (2)

gview
gview

Reputation: 15361

With mysql 5.7, your initial query of select "2018-11-40" from dual where dayname("2018-02-31") is not null; actually is generating a warning.

You can see this warning by following that query directly with show warnings;

You should see something similar to this:

+---------+------+----------------------------------------+
| Level   | Code | Message                                |
+---------+------+----------------------------------------+
| Warning | 1292 | Incorrect datetime value: '2018-02-31' |
+---------+------+----------------------------------------+
1 row in set (0.00 sec)

If I understand you correctly, what you found problematic was what you perceived as the variation in behavior from a test select to the case where you tried to use the select as input to an insert.

The answer to this question is related to the various server sql mode settings you can configure.

What you are seeing is a combination of a "strict mode" in conjunction with the default setting of NO_ZERO_DATE. The manual says this about that combination of settings:

If this mode and strict mode are enabled, '0000-00-00' is not permitted and inserts produce an error, unless IGNORE is given as well. For INSERT IGNORE and UPDATE IGNORE, '0000-00-00' is permitted and inserts produce a warning.

Your invalid dates are being converted to '00' and this triggers a warning when you are noodling around with SELECT, but a full blown error if you then try to combine that with an INSERT.

Upvotes: 2

spencer7593
spencer7593

Reputation: 108370

In a DML statement (INSERT/UPDATE/DELETE), the 1292 is an error.

But in a SELECT statement, the 1292 is not an error. It's a warning, so the statement can continue processing.

Query: select '2018-11-40' + INTERVAL 0 DAY

1 row(s) affected, 1 warning(s)

Warning Code : 1292
Incorrect datetime value: '2018-11-40'

The SELECT statement is throwing a warning. MySQL reports it, but your client app is ignoring it.

The difference is that with a warning condition, the statement can continue processing. In the context of an INSERT statement, the 1292 is an error, and the statement processing halts.

(There might be settings of sql_mode that influence this behavior in MySQL 5.7, for example, to get the SELECT statement to throw an error instead of a warning.)

We can see the same behavior with error 1411. It's an error in DML, a warning in a SELECT.

Query: select STR_TO_DATE('2018-11-40','%Y-%m-%d')

1 row(s) affected, 1 warning(s)

Warning Code : 1411
Incorrect datetime value: '2018-11-40' for function str_to_date

Upvotes: 1

Related Questions