Bug #99119 different xor results with float in MySQL 5.7 and MySQL 8.0

• Submitted: 31 Mar 2020 7:14
• Modified: 1 Apr 2020 16:26
• Reporter: Feng Liyuan
• Status: Closed
• Category: MySQL Server: DML
• Severity: S3 (Non-critical)
• Version: 5.7.29, 5.6.47
• OS: Any
• CPU Architecture: Any

[31 Mar 2020 7:14] Feng Liyuan

Description:
----------------------------------------
mysql> select '0.001' xor 0;
+---------------+
| '0.001' xor 0 |
+---------------+
|             0 |
+---------------+
1 row in set, 1 warning (0.00 sec)

mysql> select 0.001 xor 0;
+-------------+
| 0.001 xor 0 |
+-------------+
|           0 |
+-------------+
1 row in set (0.00 sec)

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.7.29    |
+-----------+
1 row in set (0.00 sec)
---------------------------------------------------------

---------------------------------------------------------
mysql> select '0.001' xor 0;
+---------------+
| '0.001' xor 0 |
+---------------+
|             1 |
+---------------+
1 row in set (0.00 sec)

mysql> select 0.001 xor 0;
+-------------+
| 0.001 xor 0 |
+-------------+
|           1 |
+-------------+
1 row in set (0.00 sec)

mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.19    |
+-----------+
1 row in set (0.00 sec)
---------------------------------------------------------

How to repeat:
select 0.001 xor 0;

[31 Mar 2020 8:06] MySQL Verification Team

Hello Feng Liyuan,

Thank you for the report and feedback.

regards,
Umesh

[1 Apr 2020 7:43] Roy Lyseng

Posted by developer:
 
This problem has been fixed in MySQL 8.0 by rewriting predicates as complete predicates.
A workaround is available for earlier releases by doing the same workaround, e.g.

  select '0.001'<>0 xor 0<>0;

[1 Apr 2020 16:26] Feng Liyuan

Why shouldn't this bug be fixed in 5.7?

[2 Apr 2020 7:15] Roy Lyseng

The fix was part of a rather large refactoring effort and is unlikely to be backported to earlier versions.