Bug #89580 Wrong results of XOR expression

• Submitted: 8 Feb 2018 2:57
• Modified: 8 Feb 2018 14:20
• Reporter: Eric Fu
• Status: Verified
• Category: MySQL Server: Data Types
• Severity: S3 (Non-critical)
• Version: 5.7.19
• OS: Any
• CPU Architecture: Any
• Tags: arithmetic, expression, Logical, operators, xor

[8 Feb 2018 2:57] Eric Fu

Description:
```
mysql root@127.0.0.1:(none)> SELECT 0.4 XOR 0
+-----------+
| 0.4 XOR 0 |
+-----------+
| 0         |
+-----------+
1 row in set
Time: 0.014s

mysql root@127.0.0.1:(none)> SELECT 0.6 XOR 0
+-----------+
| 0.6 XOR 0 |
+-----------+
| 1         |
+-----------+
1 row in set
Time: 0.013s
```

According to docs:

a XOR b is mathematically equal to (a AND (NOT b)) OR ((NOT a) and b).

However,

```
mysql root@127.0.0.1:(none)> SELECT (0.4 AND (NOT 0)) OR ((NOT 0.4) and 0)
+----------------------------------------+
| (0.4 AND (NOT 0)) OR ((NOT 0.4) and 0) |
+----------------------------------------+
| 1                                      |
+----------------------------------------+
```

How to repeat:
Run query:

SELECT 0.4 XOR 0

Suggested fix:
I guess that when evaluating a XOR b both operands are converted to int (thus 0.4 rounded to 0)

[8 Feb 2018 14:15] MySQL Verification Team

Hi,

That is expected behavior because of 

 > select cast(0.4 AS unsigned int), cast(0.6 as unsigned int);
+---------------------------+---------------------------+
| cast(0.4 AS unsigned int) | cast(0.6 as unsigned int) |
+---------------------------+---------------------------+
|                         0 |                         1 |
+---------------------------+---------------------------+
1 row in set (0.00 sec)

all best
Bogdan

[8 Feb 2018 14:17] MySQL Verification Team

NOT() is a little inconsistent to operators as it looks at != 0 instead of cast to int so you get

 > select not(0.4), not(0.6);
+----------+----------+
| not(0.4) | not(0.6) |
+----------+----------+
|        0 |        0 |
+----------+----------+
1 row in set (0.00 sec)

and that's why there's a confusion, but is expected behavior

[8 Feb 2018 14:20] MySQL Verification Team

hi,
looking forward, this actually is a bug as it's not only not() .. XOR is def. behaving differently then others

mysql [localhost] {msandbox} ((none)) > select 0 and 1;
+---------+
| 0 and 1 |
+---------+
|       0 |
+---------+
1 row in set (0.00 sec)

mysql [localhost] {msandbox} ((none)) > select 0.3 and 1;
+-----------+
| 0.3 and 1 |
+-----------+
|         1 |
+-----------+
1 row in set (0.00 sec)

 > select 0.3 or 0;
+----------+
| 0.3 or 0 |
+----------+
|        1 |
+----------+
1 row in set (0.00 sec)

verifying this
thanks for report
Bogdan