MySQL Bugs: #19618: "select x from x where x not in(1,-1)" may crash the server

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

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

same bug as http://bugs.mysql.com/bug.php?id=19520

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)

Bug: http://bugs.mysql.com/bug.php?id=19520 was marked as duplicate
of this one. A test case more simples here.

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

See bug: http://bugs.mysql.com/bug.php?id=19687 I will test
when this patch will be pushed.

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

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.

Noted in 5.0.22 changelog.

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.

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?

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-

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.

Hello Rouven

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

bye,

-christian-