Bug #113593 Incorrect query result of keyword IF
Submitted: 9 Jan 11:42 Modified: 9 Jan 12:16
Reporter: Ye Shiyang Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:8.0 OS:Any
Assigned to: CPU Architecture:Any

[9 Jan 11:42] Ye Shiyang
Description:
In theory, the result of sql should have the same result of sql2.

However,sql return the value 10, sql2 return the value 11.

DROP DATABASE IF EXISTS database0;
CREATE DATABASE database0;
USE database0;
CREATE TABLE IF NOT EXISTS t0(c0 DECIMAL);
REPLACE INTO t0(c0) VALUES(10);

-- sql1 

SELECT f1 FROM (SELECT ((t0.c0) | (IF(-1, 0.67557, '1'))) AS f1 FROM t0) as t WHERE f1;

+------+
| f1   |
+------+
|   10 |
+------+
1 row in set (0.00 sec)

--sql2

SELECT f1 FROM (SELECT ((t0.c0) | (IF(-1, 0.67557, '1'))) AS f1, ((t0.c0) | (IF(-1, 0.67557, '1'))) IS TRUE AS flag FROM t0) as t WHERE flag=1;

+------+
| f1   |
+------+
|   11 |
+------+
1 row in set (0.00 sec)

How to repeat:
DROP DATABASE IF EXISTS database0;
CREATE DATABASE database0;
USE database0;
CREATE TABLE IF NOT EXISTS t0(c0 DECIMAL);
REPLACE INTO t0(c0) VALUES(10);

SELECT f1 FROM (SELECT ((t0.c0) | (IF(-1, 0.67557, '1'))) AS f1 FROM t0) as t WHERE f1; --sql1

SELECT f1 FROM (SELECT ((t0.c0) | (IF(-1, 0.67557, '1'))) AS f1, ((t0.c0) | (IF(-1, 0.67557, '1'))) IS TRUE AS flag FROM t0) as t WHERE flag=1; --sql2

Suggested fix:
I think sql1 and sql2 should return the same result.
[9 Jan 12:16] MySQL Verification Team
Hi Mr. eric,

Thank you for your bug report.

We have repeated your test case and it is true that query returns incorrect results.

However, we have gone further and debugged our code. We discovered that the cause of your bug is identical to the cause of this bug report:

https://bugs.mysql.com/bug.php?id=113586

The symptoms from the error in the query are different, but this bug report is caused by the identical fault in our Optimiser as the above bug.

This is why this bug report is a duplicate.