Bug #80703 DATE('space') can not be inserted to DATE type with IFNULL in INSERT-SELECT

• Submitted: 11 Mar 2016 9:21
• Modified: 11 Dec 2019 22:32
• Reporter: Sangjin Han
• Status: Not a Bug
• Category: MySQL Server: DML
• Severity: S3 (Non-critical)
• Version: 5.7.11, 5.6.29
• OS: Windows (10)
• CPU Architecture: Any

[11 Mar 2016 9:21] Sangjin Han

Description:
When I copy data of old table to new table, I cannot convert the VARCHAR typed date information to DATE type using INSERT-SELECT-IFNULL if it has a blank field.

How to repeat:
Input
-----
drop table if exists test;
create table test (ddd date);
insert into test (ddd) select ifnull(date('blank'), date('1970-01-01'))

Output
------
Incorrect datetime value: 'badform'

Expected
--------
1 record inserted // '1970-01-01'

[11 Mar 2016 9:28] Sangjin Han

Changed version number.

[11 Mar 2016 9:30] Sangjin Han

Mistyped, the output was 

Incorrect datetime value: 'blank'

[11 Mar 2016 12:58] MySQL Verification Team

Hello Sangjin Han,

Thank you for the report and test case.
This issue is observed even with 5.6.29 with strict mode enabled.

Thanks,
Umesh

[11 Mar 2016 13:48] Peter Laursen

For me it is reproducible in '' (empty) sqlmode on both 5.6 and 5.7 - ie 

SET sqlmode = '';
DROP TABLE IF EXISTS test;
CREATE TABLE test (ddd DATE);
INSERT INTO test (ddd) SELECT IFNULL(DATE('blank'), DATE('1970-01-01'))
-- Error Code: 1292 - Incorrect datetime value: 'blank'

[11 Dec 2019 22:32] Roy Lyseng

Posted by developer:
 
This is not a bug, as far as I can tell.
DATE('blank') returns NULL when not in strict mode, but correctly gives an error in strict mode.