I tried to use the exclamation mark as a not operator in a query
SELECT !0, !!0, !!!0, !1, !!1, !!!1, !2, !!2, !!!2;
| !0 | !!0 | !!!0 | !1 | !!1 | !!!1 | !2 | !!2 | !!!2 |
|----|-----|------|----|-----|------|----|-----|------|
| 1 | 1 | 0 | 0 | 0 | 1 | 0 | 0 | 1 |
the results are surprising, but it's working as I thought it would if I add parentheses or spaces:
SELECT !0, !(!0), !(!(!0)), !1, !(!1), !(!(!1)), !2, !(!2), !(!(!2));
SELECT !0, ! !0, ! ! !0, !1, ! !1, ! ! !1, !2, ! !2, ! ! !2;
| !0 | !(!0) | !(!(!0)) | !1 | !(!1) | !(!(!1)) | !2 | !(!2) | !(!(!2)) |
|----|-------|----------|----|-------|----------|----|-------|----------|
| 1 | 1 | 0 | 0 | 0 | 1 | 0 | 0 | 1 |
EXPLAIN EXTENDED SELECT !0, !!0, !!!0, !1, !!1, !!!1, !2, !!2, !!!2;
SHOW WARNINGS;
when executing those 2 queries, I have the following message from SHOW WARNINGS
:
select (not(0)) AS `!0`,(not(0)) AS `!!0`,(0 <> 0) AS `!!!0`,(not(1)) AS `!1`,(not(1)) AS `!!1`,(1 <> 0) AS `!!!1`,(not(2)) AS `!2`,(not(2)) AS `!!2`,(2 <> 0) AS `!!!2`
Can anyone explain why mysql (and mariadb) comes to such a query?
Best Answer
That's a fairly serious defect.
http://bugs.mysql.com/bug.php?id=55477
The lexer, encountering
!
considers that the next character might be a continuation of the!=
operator,=
, and if that happens, everything is fine. Otherwise, if the next character is of the same class (of characters which might be part of a logical operator, such as<
) but doesn't match what's expected... then the next character just... disappears, apparently since!
is a complete logical operator by itself.The result is that
!!
resolves to!
when not separated by whitespace...!!expr
resolves to!(expr)
!!!expr
resolves to!(!(expr))
!!!!expr
also resolves to!(!(expr))