EdgeCaseBerg EdgeCaseBerg - 6 months ago 9
MySQL Question

MySQL Null Safe Equals incorrect on Numeric types?

This one bit me today and it seems like a bug. It seems like when comparing the result of the

LOCATE
function in MySQL that it behaves totally different when using the not equals
<>
versus the null safe version
<=>
See the session below:

$ mysql --version
mysql Ver 14.14 Distrib 5.6.25, for osx10.8 (x86_64) using EditLine wrapper

mysql> SELECT LOCATE('A', "The Apple");
+--------------------------+
| LOCATE('A', "The Apple") |
+--------------------------+
| 5 |
+--------------------------+
1 row in set (0.00 sec)


mysql> SELECT LOCATE('A', "The Apple") <=> 0; -- WRONG!?
+--------------------------------+
| LOCATE('A', "The Apple") <=> 0 |
+--------------------------------+
| 0 |
+--------------------------------+
1 row in set (0.00 sec)

mysql> SELECT LOCATE('A', "The Apple") <> 0;
+-------------------------------+
| LOCATE('A', "The Apple") <> 0 |
+-------------------------------+
| 1 |
+-------------------------------+
1 row in set (0.00 sec)


The null safe version returns 0 (false) which seems odd to me. The
LOCATE
method's documentation doesn't specify what happens with nulls, but I tested it and it does return null for something like
LOCATE('A', null)
but the real issue seems to be comparing the integer that
LOCATE
returns to
0
.

I checked the documentation on <=> but it just says:


NULL-safe equal. This operator performs an equality comparison like the = operator, but returns 1 rather than NULL if both operands are NULL, and 0 rather than NULL if one operand is NULL.


Which doesn't really tell me why
0 <=> 5
comes back
0
and
0 <> 5
comes back
1
The only thing I could think of was that MySQL says:


These operations work for both numbers and strings. Strings are automatically converted to numbers and numbers to strings as necessary.


And maybe there was an automatic cast, but that doesn't make any sense since doing the check directly (typing out
0 <=> 5
) shows the same issue.

Does anyone know what's causing this? Or where in the documentation this is indicated? It seems like a pretty major issue if the null-safe equality operations return invalid results for as simple a comparison as 5 and 0.

Answer

The Null safe equality comparison <=> (spaceship) operator is an equality operator. It works like the = equality comparison operator, except with the bit about not returning NULL.

To get "inequality" out of that operator, you would need to negate it, for example

 SELECT NOT ( 0 <=> 5 )