Bug #114282 Inconsistent result may caused by group by NULL
Submitted: 8 Mar 13:10 Modified: 8 Mar 13:12
Reporter: Ye Shiyang Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: DML Severity:S2 (Serious)
Version:8.35, 8.0.36 OS:Any
Assigned to: CPU Architecture:Any

[8 Mar 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 13:12] MySQL Verification Team
Hello Ye Shiyang,

Thank you for the report and feedback

regards,
Umesh