MySQL Bugs: #55477: Bad parsing of SQL operators (!! is different from ! !)

Bug #55477	Bad parsing of SQL operators (!! is different from ! !)
Submitted:	22 Jul 2010 11:21	Modified:	7 Sep 2010 17:35
Reporter:	Ralf Neubauer	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Parser	Severity:	S1 (Critical)
Version:	5.1.44, 5.1.50-bzr, 5.5.20	OS:	Windows (XP)
Assigned to:	Marc ALFF	CPU Architecture:	Any
Tags:	regression

Description:
I found not way to search for that operator on this site, but

select version(), !w, !!w, !(!w), ! !w, not w, not not w, w is true, w is not false, (not w) is false, not(w is false), if(w,'true','false')
from (select 0 w union select 1) w

results in:
version(), !w, !!w, !(!w), ! !w, not w, not not w, w is true, w is not false, (not w) is false, not(w is false), if(w,'true','false')
'5.1.44-community', 1, 1, 0, 0, 1, 0, 0, 0, 0, 0, 'false'
'5.1.44-community', 0, 0, 1, 1, 0, 1, 1, 1, 1, 1, 'true'

As you can see, !! behaves like !, but not like ! !. In other words, not not is not, not not not. The space actually makes a difference. Depending on the parser, !! could be parsed as one token, but I can't find an actual operator !!. In C you can use !! as double negation to convert values to their truth value as an integer.

How to repeat:
See above.

Thank you for the problem report. It is easy to verify:

macbook-pro:5.1 openxs$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.50-debug Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select version(), !w, !!w, !(!w), ! !w, not w, not not w, w is true, w is not false, (not
    -> w) is false, not(w is false), if(w,'true','false')
    -> from (select 0 w union select 1) w
    -> ;
+--------------+----+-----+-------+------+-------+-----------+-----------+----------------+------------------+-----------------+----------------------+
| version()    | !w | !!w | !(!w) | ! !w | not w | not not w | w is true | w is not false | (not
w) is false | not(w is false) | if(w,'true','false') |
+--------------+----+-----+-------+------+-------+-----------+-----------+----------------+------------------+-----------------+----------------------+
| 5.1.50-debug |  1 |   1 |     0 |    0 |     1 |         0 |         0 |              0 |                0 |               0 | false                |
| 5.1.50-debug |  0 |   0 |     1 |    1 |     0 |         1 |         1 |              1 |                1 |               1 | true                 |
+--------------+----+-----+-------+------+-------+-----------+-----------+----------------+------------------+-----------------+----------------------+
2 rows in set (0.00 sec)

Actually, there is even simpler test case:

mysql> select !!0, not not 0, !(!0);
+-----+-----------+-------+
| !!0 | not not 0 | !(!0) |
+-----+-----------+-------+
|   1 |         0 |     0 |
+-----+-----------+-------+
1 row in set (0.00 sec)

And we can see how server (wrongly!) rewrites the query:
 
mysql> explain extended select !!0, !(!0), ! !0, not not 0;
+----+-------------+-------+------+---------------+------+---------+------+------+----------+----------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra          |
+----+-------------+-------+------+---------------+------+---------+------+------+----------+----------------+
|  1 | SIMPLE      | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL |     NULL | No tables used |
+----+-------------+-------+------+---------------+------+---------+------+------+----------+----------------+
1 row in set, 1 warning (0.00 sec)

mysql> show warnings\G
*************************** 1. row ***************************
  Level: Note
   Code: 1003
Message: select (not(0)) AS `!!0`,(0 <> 0) AS `!(!0)`,(0 <> 0) AS `! !0`,(0 <> 0) AS `not not 0`
1 row in set (0.00 sec)

Analysis
========

This seems to be a lexer bug.

For characters that can be part of operators, such as
'>', '<', '!', '='
the lexer tries to find 2 characters (of that class) in a row.
Then, it checks if these two characters match an operator such as:
'>=', '<=', '!=', ...

When if works, fine.
When it does not work, the lexer returns the first character,
for example '!', as a character ... but silently drops the second one.

As a result, '!' '!' is seen as only one '!' token NOT_SYM,
where '!' <space> '!' is seen as NOT_SYM followed by NOT_SYM,
aka two tokens, as expected.

This can be verified with other sequences:

"select !<0"
actually works (it should be a syntax error),
and is parsed as "select !0" : the '<' character is dropped.

The root cause is in sql_lex.cc:

------
    case MY_LEX_CMP_OP:                 // Incomplete comparison operator
      if (state_map[lip->yyPeek()] == MY_LEX_CMP_OP ||
          state_map[lip->yyPeek()] == MY_LEX_LONG_CMP_OP)
(1)      lip->yySkip();
      if ((tokval = find_keyword(lip, lip->yyLength() + 1, 0)))
      {
        lip->next_state= MY_LEX_START;  // Allow signed numbers
        return(tokval);
      }
(2)   state = MY_LEX_CHAR;              // Something fishy found
      break;
------

The second character, accepted in (1), is dropped in (2), and lost.

This is a very serious bug, as parsing of operators in general needs
to be revisited.

Bug #64054 was marked as a duplicate of this one.

I am able to repeat this bug on:
* MySQL 5.5.28 MySQL Community Server (GPL) (just downloaded from http://dev.mysql.com/downloads/mysql/).
* MySQL 5.5.16 MySQL Community Server (GPL) (bundled in XAMPP 1.7.5).

Using Windows 7 Home Premium SP1.

"This is a very serious bug, as parsing of operators in general needs
to be revisited."

Fast forward about three years ... and it is still reproducible in 5.6.12 ...

I just encountered this bug when testing various 'truthiness' of string conversions in MySQL (commonly done as !!value) and was surprised to notice that !! is the same as ! and !!!! is the same as !!.

Wow.  This is a really serious logical error.  Its set as a severity 1, and its a decade old?  

Ouch...

Hi Dillon,
notice that in MySQL 8.0, using the operator ! for negation is deprecated.
It is recommended to use standard SQL syntax (NOT) instead.
The same applies to the logical operators && and ||, which should rather be written as AND and OR.

Note also that a predicate "value" (without any comparison operator) is now rewritten internally as "value <> 0".