Bug #109975 unique key cause wrong result
Submitted: 8 Feb 2023 1:19 Modified: 8 Feb 2023 8:32
Reporter: haizhen xue Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: DML Severity:S3 (Non-critical)
Version:5.7, 8.0 OS:Any
Assigned to: CPU Architecture:Any

[8 Feb 2023 1:19] haizhen xue 
Description:
1.add index or unique key       
  CREATE TABLE IF NOT EXISTS t0(c0 FLOAT UNIQUE KEY);
  CREATE TABLE t1 LIKE t0;
  INSERT INTO t0(c0) VALUES(-136077321),(-1426139031),(NULL);
  INSERT INTO t1(c0) VALUES(-1426139031);
2. wrong result
mysql> SELECT t0.c0 AS ref0 FROM t0, t1 WHERE (IF(false, "1", t1.c0)) = (t0.c0);
Empty set (0.00 sec)

Not Use IF，result is correct：
mysql> SELECT t0.c0 AS ref0 FROM t0, t1 WHERE (t1.c0) = (t0.c0);
+-------------+
| ref0        |
+-------------+
| -1426140000 |
+-------------+
1 row in set (0.00 sec)

How to repeat:
1.add index or unique key       
  CREATE TABLE IF NOT EXISTS t0(c0 FLOAT UNIQUE KEY);
  CREATE TABLE t1 LIKE t0;
  INSERT INTO t0(c0) VALUES(-136077321),(-1426139031),(NULL);
  INSERT INTO t1(c0) VALUES(-1426139031);
2. wrong result
mysql> SELECT t0.c0 AS ref0 FROM t0, t1 WHERE (IF(false, "1", t1.c0)) = (t0.c0);
Empty set (0.00 sec)
[8 Feb 2023 8:32] MySQL Verification Team 
Hello haizhen xue,

Thank you for the report and test case.

regards,
Umesh