Bug #19618 "select x from x where x not in(1,-1)" may crash the server
Submitted: 8 May 2006 16:24 Modified: 17 May 2006 18:06
Reporter: Nik Soggia Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S1 (Critical)
Version:5.0.21/5.0BK/5.1BK OS:Linux (linux)
Assigned to: Sergey Petrunya CPU Architecture:Any

[8 May 2006 16:24] Nik Soggia
Description:
the in() operator crashes the server if
- it is preceded by the "not" operator in the where clause,
- its arguments contain at least one positive and at least one negative value,
- the left-hand expression is a unsigned-int key column,
- the table engine is myisam or innodb (maybe others), the memory engine is not affected.

At a first glance, it looks like that some boundary check is missing.

Mysql 4.1 is not affected.

How to repeat:
-- tested with mysql-standard-5.0.21-linux-i686.tar.gz
drop table if exists crash_me;
create table crash_me (
  some_id smallint(5) unsigned,
  key (some_id)
) engine=myisam;
insert into crash_me values (2),(4),(6);
--
select some_id from crash_me where some_id not in(2,4); -- no crash
select some_id from crash_me where some_id not in(2,-1); -- crash
[8 May 2006 16:33] Nik Soggia
same bug as http://bugs.mysql.com/bug.php?id=19520
[8 May 2006 17:06] MySQL Verification Team
Thank you for the bug report. 4.1 isn't affected.

miguel@hegel:~/dbs/5.0> bin/mysql -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.22-debug

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> drop table if exists crash_me;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> create table crash_me (
    ->   some_id smallint(5) unsigned,
    ->   key (some_id)
    -> ) engine=myisam;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into crash_me values (2),(4),(6);
Query OK, 3 rows affected (0.02 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select some_id from crash_me where some_id not in(2,4); -- no crash
+---------+
| some_id |
+---------+
|       6 | 
+---------+
1 row in set (0.05 sec)

mysql> select some_id from crash_me where some_id not in(2,-1); -- crash
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql> 

[New Thread 1129204656 (LWP 6248)]
[New Thread 1129405360 (LWP 6249)]
060509 13:32:00 [Note] /home/miguel/dbs/5.0/libexec/mysqld: ready for connections.
Version: '5.0.22-debug'  socket: '/tmp/mysql.sock'  port: 3306  Source distribution
[New Thread 1129606064 (LWP 6251)]

Program received signal SIGSEGV, Segmentation fault.
[Switching to Thread 1129606064 (LWP 6251)]
0x082a4a59 in SEL_ARG::last (this=0x0) at opt_range.cc:1295
1295      if (!next_arg->right)
(gdb) quit
------------------------------------------------------------------------

miguel@hegel:~/dbs/5.1> bin/mysql -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.1.10-beta-debug

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> drop table if exists crash_me;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> create table crash_me (
    ->   some_id smallint(5) unsigned,
    ->   key (some_id)
    -> ) engine=myisam;
Query OK, 0 rows affected (0.01 sec)

mysql> insert into crash_me values (2),(4),(6);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> --
mysql> select some_id from crash_me where some_id not in(2,4); -- no crash
+---------+
| some_id |
+---------+
|       6 | 
+---------+
1 row in set (0.04 sec)

mysql> select some_id from crash_me where some_id not in(2,-1); -- crash
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql> 
------------------------------------------------------------------------
miguel@hegel:~/dbs/4.1> bin/mysql -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 4.1.19-debug

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> drop table if exists crash_me;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> create table crash_me (
    ->   some_id smallint(5) unsigned,
    ->   key (some_id)
    -> ) engine=myisam;
Query OK, 0 rows affected (0.02 sec)

mysql> insert into crash_me values (2),(4),(6);
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> --
mysql> select some_id from crash_me where some_id not in(2,4); -- no crash
+---------+
| some_id |
+---------+
|       6 |
+---------+
1 row in set (0.01 sec)

mysql> select some_id from crash_me where some_id not in(2,-1); -- crash
+---------+
| some_id |
+---------+
|       4 |
|       6 |
+---------+
2 rows in set (0.00 sec)
[8 May 2006 17:15] MySQL Verification Team
Bug: http://bugs.mysql.com/bug.php?id=19520 was marked as duplicate
of this one. A test case more simples here.
[15 May 2006 11:36] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/6375
[15 May 2006 12:17] MySQL Verification Team
See bug: http://bugs.mysql.com/bug.php?id=19687 I will test
when this patch will be pushed.
[16 May 2006 9:30] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/6455
[16 May 2006 11:51] Sergey Petrunya
The fix has been pushed into 5.0.22 tree.
Notes for the changelog:
 The bug was introduced in the fix for BUG#15872.
 The bug was as follows: mysqld range optimizer caused crash when processing a clause in form "t.unsigned_keypart NOT IN (negative_const, ...)". 
 The fix removes the crash.
[17 May 2006 18:06] Paul DuBois
Noted in 5.0.22 changelog.
[1 Jun 2006 11:33] Marco Herwaarden
I was running into this problem with 5.0.21.

Upgrading to 5.0.22 (it is marked as fixed in 5.0.22) however don't fix this issue.

I tried this with both Linux x86 rpm, and windows essentials. Both downloaded today and still crashing when using a "WHERE NOT LIKE (-1, 1)" on an unsigned numeric column.

Can anyone confirm that it is either fixed or still present in 5.0.22.
[1 Jun 2006 12:56] Marco Herwaarden
Would i be correc in asuming that this change nevre made it to the real 5.0.22, since that was an unexpected security release?
[8 Jun 2006 23:12] Christian Hammers
Marco, the confirmation that the bugfix was committed for 5.0.22 but then postponed to 5.0.23 due to the fact that 5.0.22 was a security release can be read in the already made comments to the upcoming 5.0.23 release notes: http://dev.mysql.com/doc/refman/5.0/en/news-5-0-23.html

Hope that helps,

-christian-
[4 Aug 2006 7:30] Rouven Homann
Hi, since 5.0.23 was never released is there a chance that this bugfix is included in 5.0.24 that was released a few days before? the list of changes doesn't have that fix listed... 

especially for all vbulletin users/admins this fix is very important to be released.
[7 Aug 2006 22:34] Christian Hammers
Hello Rouven

I cannot confirm this bug in 5.0.24 any more so it seems to really have been fixed.

bye,

-christian-