surprising behavior of MySql not operator

Question

I expected this query to return all columns as 0, how is the right expression 1?

select (not(55))=1, !(55)=1, not(55)=1 ;

+-------------+---------+-----------+
| (not(55))=1 | !(55)=1 | not(55)=1 |
+-------------+---------+-----------+
|           0 |       0 |         1 |
+-------------+---------+-----------+

I find this surprising that NOT and ! aren't equivalent, and that the parens are significant.

[edit: simplified the query from original post]

Answer 1

in your first column the code is like (mysql perform an implicit evaluation of the code (55)=0 )

select  (55)=1 from dual;  /* result 0  false*/ 

then

select  not (55)=1 from dual; /* resul  1 true alias not false */

Answer 2

Greg:

According to MYSQL documentation of IFNULL(expr1,expr2):

If expr1 is not NULL, IFNULL() returns expr1; otherwise it returns expr2. IFNULL() returns a numeric or string value, depending on the context in which it is used.

Reference: http://dev.mysql.com/doc/refman/5.7/en/control-flow-functions.html#function_ifnull

Answer 3

It appears to me what you are seeing is that the comparison = operator has higher precedence than the NOT operator:

NOT( IFNULL(55,0)) = 1 is equivalent to NOT (IFNULL(55,0)=1)

(NOT( IFNULL(55,0))) = 1 is equivalent to (NOT IFNULL(55,0)) = 1

!(IFNULL(55,0)) = 1 is equivalent to exactly what is looks like; and indicates ! has higher or equal precedence to =.

Which is corroborated by the official docs here.

I would guess the different precedence of ! and NOT has something to do with the slightly different semantics and expected uses of the two operators. I've never tried, but I am pretty sure x IS !NULL and x ! IN ([set]) are not permitted.