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
• Status: Closed
• Category: MySQL Server: DML
• Severity: S3 (Non-critical)
• Version: 5.7.8, 5.7.9
• OS: Any
• 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] MySQL Verification Team

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.