MySQL Bugs: #89580: Wrong results of XOR expression

Bug #89580	Wrong results of XOR expression
Submitted:	8 Feb 2018 2:57	Modified:	8 Feb 2018 14:20
Reporter:	Eric Fu	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Data Types	Severity:	S3 (Non-critical)
Version:	5.7.19	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	arithmetic, expression, Logical, operators, xor

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)

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

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

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