5

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?

3
  • use <> as not operator.. Commented Jan 20, 2015 at 9:55
  • 2
    @NawazSohail: <> is the "not equal" operator. It's a binary operator that requires two values. That is something different than a the unary NOT operator on a "boolean" value.
    – user1822
    Commented Jan 20, 2015 at 10:32
  • 1
    Seems like a bug in the parser. SELECT ! ! ! 0, ...; works as expected. Commented Jan 20, 2015 at 11:57

1 Answer 1

7

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.