Why is the result of `select 'a'=0;` 1?

Question

mysql> SELECT 'a'='b'='c';
+-------------+
| 'a'='b'='c' |
+-------------+
|           1 |
+-------------+
mysql> select 'a'=0, 'b'='c';
+-------+---------+
| 'a'=0 | 'b'='c' |
+-------+---------+
|     1 |       0 | 
+-------+---------+

Why does 'a' equals 0 in MySQL?

Answer 1

See this page in the MySQL documentation.

The 'b'='c' case is governed by this rule:

If both arguments in a comparison operation are strings, they are compared as strings.

Since they are different strings, the result is false.

The 'a'=0 case is governed by this rule:

In all other cases, the arguments are compared as floating-point (real) numbers

Both sides are the comparison are converted to floating point numbers, and since 'a' doesn't contain any digits it evaluates to zero, as does the numeric 0 on the right hand side, so they are considered equal.

Answer 2

When you compare a string literal with a numeric value, MySQL must to convert the string literal to a numeric value as well so that the comparison can take place. Since 'a' is not a number, the resulting value is zero—hence the equality. When comparing 'b' and 'c', both operands are strings, so no conversion takes place and the result is false (0).

The first expression in your code can be rewritten as:

('a' = 'b') = 'c'

Since ('a' = 'b') returns 0, after that operation has taken place, your expression will be interpreted as

0 = 'c'

Which is 1 because of the reason I explained above. Incidentally, this expression:

'a' = 0 = 'c'

Returns false, because ('a' = 0) returns 1 and then (1 = 'c') returns 0.

Answer 3

TEXT values are cast to INTEGER values by interpreting all the leading digits as a number. If there are no digits, it casts to 0.

So:

'123a' = 123
'4a' = 4
'a' = 0

Answer 4

I believe MySQL calls atoi() or something similar on the string 'a', which equals 0. What does select 'a'=1 give?