what does !! as an operator in mysql?

Question

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?

Answer 1

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))