Sanitize expression evaluation
In MySQL, an expression like 'a'='b'='c' evaluates to 1.
I find this very counterintuitive (and I am not the only one, http://
'a'='b'='c' is parsed as ('a'='b') = 'c' which is fine. 'a' = 'b' is clearly FALSE, but problem 1 is that MySQL does internally not have a proper boolean type for computing expressions, so FALSE is simply equal to the integer 0. Now we have 0 = 'c'. problem 2 is that here, MySQL now converts 'c' to 0 in order to compare it to the numeric left hand operand. Clearly, 0=0 which is TRUE which is again projected as 1 (problem 1 again)
Now, there are a number of things that could be done:
- make a comparison like <int> = <string> illegal, and either throw a runtime error
- same as above but evaluate to NULL instead of throwing an error
- allow comparisons like <int> = <string>, but convert the number to string (instead of the other way around). Something like 'a'='b'='c' would become '0'='c' which would be false. This would still allow 'a'='b'='0' to be true though which is still non-sense.
- introduce a proper boolean type for the purpose of computation. 'a'='b' would be FALSE, and FALSE = 'c' would become FALSE = TRUE (assuming non-empty strings are TRUE). Problem here would be that 'a'='b'='' would be true
So of all these options it seems only the first two deliver a clear result.