Bug #79114 IF(bool, date str, date str) throws ERROR in UPDATE
Submitted: 4 Nov 2015 7:53 Modified: 18 Nov 2015 18:24
Reporter: Su Dylan Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: DML Severity:S3 (Non-critical)
Version:5.7.8, 5.7.9 OS:Any
Assigned to: CPU Architecture:Any

[4 Nov 2015 7:53] Su Dylan
Description:
Output:
=======
mysql> drop table if exists t1;
create table t1(c1 int);
Query OK, 0 rows affected (0.00 sec)

mysql> create table t1(c1 int);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t1 values(1);
Query OK, 1 row affected (0.00 sec)

mysql> update t1 set c1 = 2 where if(true, '2015-01-01', '2015-01-01') is not null;
ERROR 1292 (22007): Truncated incorrect INTEGER value: '2015-01-01'
mysql> select @@sql_mode, version();
+-------------------+-----------+
| @@sql_mode        | version() |
+-------------------+-----------+
| STRICT_ALL_TABLES | 5.7.8-rc  |
+-------------------+-----------+
1 row in set (0.00 sec)

Problem:
========
When a string which is a date in expr2 and expr3 of IF, the result is expected to be a string.
However, here IF is trying to convert the date string to integer and throwing a ERROR message.

How to repeat:

drop table if exists t1;
create table t1(c1 int);
insert into t1 values(1);
update t1 set c1 = 2 where if(true, '2015-01-01', '2015-01-01') is not null;

Suggested fix:
The UPDATE is expected to be successful and no warning.
[4 Nov 2015 8:23] Umesh Shastry
Hello Su Dylan,

Thank you for the report and test case.
Verified as described with 5.7.9.

Thanks,
Umesh
[18 Nov 2015 18:24] Paul Dubois
Noted in 5.8.0 changelog.

The optimizer failed when trying to optimize away expressions of the
form IF(true, '2015-01-01', '2015-01-01') IS NOT NULL.