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 Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: DML Severity:S3 (Non-critical)
Version:5.7.29, 5.6.47 OS:Any
Assigned to: 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.