MySQL Bugs: #83901: insert into...select... with cast function, working error

Bug #83901	insert into...select... with cast function, working error
Submitted:	21 Nov 2016 8:09	Modified:	24 Nov 2016 2:26
Reporter:	Lyu Joey	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S3 (Non-critical)
Version:	5.6.26	OS:	Windows (WINDOWS 10)
Assigned to:		CPU Architecture:	Any

Description:
there are two tables:

table t_str:
my_str varchar(20);

table t_date:
my_date date;

table t_str has 3 rows:
-------------
INSERT INTO `T_str` (`my_str`) VALUES ('2015-12-20');
INSERT INTO `T_str` (`my_str`) VALUES ('abc');
INSERT INTO `T_str` (`my_str`) VALUES ('');
-------------

then I executed this SQL:
insert into t_date(my_Date) select cast(my_Str as date) from t_str;

I got the error message:

[Err] 1292 - Incorrect datetime value: 'abc'

but when I execute this SQL:
select cast(my_Str as date) from t_str;

I got this result and it seemed correct:
-----------------
2015-12-20
Null
Null
-----------------

So, why there's an error when executing 'insert into...select...'?

How to repeat:
just create the two tables, and add some test data into t_str, and execute the INSERT INTO ... SELECT ...

Hello Lyu Joey,

Thank you for the report.
Please could you share exact create table statements(SHOW CREATE TABLE <table_name>\G) to reproduce this issue at our end? Also, what is the value set for sql_mode? A value can be invalid for several reasons. For example, it might have the wrong data type for the column, or it might be out of range. A value is missing when a new row to be inserted does not contain a value for a non-NULL column that has no explicit DEFAULT clause in its definition etc.

Thanks,
Umesh

CREATE TABLE `t_date` (
  `my_date` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8

CREATE TABLE `t_str` (
  `my_str` varchar(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8

sql_mode: STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION

here is a similar post:
http://stackoverflow.com/questions/28796190/using-cast-with-an-insert-into-select

Thank you for the requested details.
Imho, this is by design and expected behavior.
When strict mode is in effect, any invalid values are rejected, please see related bug See, https://bugs.mysql.com/bug.php?id=8294

thank you~