Bug #113586 IFNULL Incorrect query result

• Submitted: 9 Jan 2024 3:13
• Modified: 9 Jan 2024 10:49
• Reporter: Ye Shiyang
• Status: Verified
• Category: MySQL Server: Optimizer
• Severity: S3 (Non-critical)
• Version: 8.0
• OS: Any
• CPU Architecture: Any

[9 Jan 2024 3:13] Ye Shiyang

Description:
In theory, the result of sql should have the same result of sql2.

However,sql return the value 2, sql2 return the value 512.

-- sql1

SELECT f1 FROM (
SELECT t0.c0 >> IFNULL("\r8*&t", NULL) AS f1 FROM t0
) AS t WHERE f1!=123;

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

-- sql2

SELECT f1 FROM (
SELECT t0.c0 >> (IFNULL("\r8*&t", NULL)) AS f1,
((t0.c0 >> IFNULL("\r8*&t", NULL)) != 123) IS TRUE AS flag FROM t0
) AS t WHERE flag=1;

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

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

-- sql1
SELECT f1 FROM (
SELECT t0.c0 >> IFNULL("\r8*&t", NULL) AS f1 FROM t0
) AS t WHERE f1!=123;

-- sql2
SELECT f1 FROM (
SELECT t0.c0 >> (IFNULL("\r8*&t", NULL)) AS f1,
((t0.c0 >> IFNULL("\r8*&t", NULL)) != 123) IS TRUE AS flag FROM t0
) AS t WHERE flag=1;

Suggested fix:
I think sql1 and sql2 should return the same result.

[9 Jan 2024 10:49] MySQL Verification Team

Hi Mr. eric,

Thank you for your bug report.

Yes, this is a very small bug in our Optimiser, but still it is a bug.

We have found EXACTLY where a bug occurs .......

SELECT f1 FROM (SELECT t0.c0 >> IFNULL("\r8*&t", NULL) AS f1 FROM t0) as t

+------+
| f1   |
+------+
|  512 |
+------+

SELECT f1 FROM (SELECT t0.c0 >> IFNULL("\r8*&t", NULL) AS f1 FROM t0) as t  WHERE f1 != 123

+------+
| f1   |
+------+
|    2 |
+------+

it is the WHERE filtering that confused the optimiser.

Since 5.7 is no longer maintained, this is now a verified bug for the version 8.0 and all higher versions.