Bug #114282 Inconsistent result may caused by group by NULL

• Submitted: 8 Mar 2024 13:10
• Modified: 8 Mar 2024 13:12
• Reporter: Ye Shiyang
• Status: Verified
• Category: MySQL Server: DML
• Severity: S2 (Serious)
• Version: 8.35, 8.0.36
• OS: Any
• CPU Architecture: Any

[8 Mar 2024 13:10] Ye Shiyang

Description:
In theory, sql1 and sql2 should return the same result. However, sql1 return nothing while sql2 return 400145000.

--sql1
SELECT COALESCE(t0.c0, 400145287) AS f1 FROM t0 GROUP BY c0 HAVING (f1) & (0.6979778231950815); 
Empty set (0.01 sec)

--sql2
SELECT f1 FROM (SELECT DISTINCT (COALESCE(t0.c0, 400145287)) AS f1, ((COALESCE(t0.c0, 400145287)) & (0.6979778231950815)) IS TRUE AS flag FROM t0 HAVING flag=1) as tmp_t;
+-----------+
| f1        |
+-----------+
| 400145000 |
+-----------+

How to repeat:
it's noted that the bug can't be reproduced if change NULL to other value of colunmn c0.

CREATE TABLE t0(c0 FLOAT) ;
INSERT INTO t0(c0) VALUES(NULL);

SELECT COALESCE(t0.c0, 400145287) AS f1 FROM t0 GROUP BY c0 HAVING (f1) & (0.6979778231950815);

SELECT f1 FROM (SELECT COALESCE(t0.c0, 400145287) AS f1, (COALESCE(t0.c0, 400145287) & (0.6979778231950815)) IS TRUE AS flag FROM t0 GROUP BY c0 HAVING flag=1) as t;

[8 Mar 2024 13:12] MySQL Verification Team

Hello Ye Shiyang,

Thank you for the report and feedback

regards,
Umesh