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:Microsoft Windows (XP)
Assigned to: Marc Alff CPU Architecture:Any
Tags: regression
Triage: Needs Triage: D2 (Serious)

[22 Jul 2010 11:21] Ralf Neubauer
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.
[22 Jul 2010 11:46] Valeriy Kravchuk
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)
[25 Aug 2010 5:52] Marc Alff
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.
[18 Jan 2012 5:16] Valeriy Kravchuk
Bug #64054 was marked as a duplicate of this one.
[27 Nov 2012 2:22] Pang Pang
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.
[23 Jul 2013 15:47] Hartmut Holzgraefe
"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 ...