Bug #119826 Two equivalent statements returned different results

• Submitted: 1 Feb 13:19
• Modified: 3 Feb 12:08
• Reporter: fan liu
• Status: Not a Bug
• Category: MySQL Server: Optimizer
• Severity: S3 (Non-critical)
• Version: 8.0.44
• OS: Any
• CPU Architecture: Any

[1 Feb 13:19] fan liu

Description:
Two equivalent statements returned different results.
select ALL t0.c1, t1.c1 from t0, t1 where t0.c1=t0.c1;--{1.97588E9|1975880000|}
SELECT ALL t0.c1, t1.c1 FROM t0, t1 WHERE (IF(1, t0.c1, "")) IN (t1.c1); --{}
The second SQL statement did not return the expected result.

How to repeat:
DROP DATABASE IF EXISTS test;
CREATE DATABASE test;
USE test;
create table t0(c1 float );
create table t1(c0 float ,c1 float UNIQUE KEY);
insert into t0 values (1975875403);
insert into t1(c0,c1 ) values (NULL, 1975875403);

select ALL t0.c1, t1.c1 from t0, t1 where t0.c1=t0.c1;
SELECT ALL t0.c1, t1.c1 FROM t0, t1 WHERE (IF(1, t0.c1, "")) IN (t1.c1);

[3 Feb 12:08] Roy Lyseng

Thank you for the bug report.
However, these statements are not equivalent.
IF(1, t0.c1, "") has a string result type, whereas t0.c1 is FLOAT.
Note also that floating point values may be converted slightly different
in different operations, so equality is often not guaranteed.
Not a bug.