4

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?

  • use <> as not operator.. – Nawaz Sohail Jan 20 '15 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. – a_horse_with_no_name Jan 20 '15 at 10:32
  • 1
    Seems like a bug in the parser. SELECT ! ! ! 0, ...; works as expected. – ypercubeᵀᴹ Jan 20 '15 at 11:57
6

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, privacy policy and cookie policy

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