MySQL Bugs: #113593: Incorrect query result of keyword IF

Bug #113593	Incorrect query result of keyword IF
Submitted:	9 Jan 2024 11:42	Modified:	9 Jan 2024 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

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.

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.