Bug #19687 SELECT query with certain values crashes mysqld.exe
Submitted: 10 May 2006 14:42 Modified: 30 May 2006 16:37
Reporter: Michael Phillips Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.0.21-community/5.0BK/5.1BK OS:Windows (WinXP Professional SP2/Linux)
Assigned to: CPU Architecture:Any

[10 May 2006 14:42] Michael Phillips
Description:
I have the following table team_members:

+-------------+------------------+------+-----+---------+----------------+
| Field       | Type                 | Null | Key | Default | Extra          |
+-------------+------------------+------+-----+---------+----------------+
| id             | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| profile_id   | int(10) unsigned | NO   | MUL | 0       |                |
| name        | varchar(255)     | NO     |       | NULL    |                |
| description | longtext          | NO     |       |NULL    |                |
+-------------+------------------+------+-----+---------+----------------+

Executing the this SQL command from the command line:

SELECT * FROM team_members WHERE (id NOT IN (-1,0)) ORDER BY name

causes MySQLd to crash. I receive a Windows dialog box informing me that an error has occurred and that the application has to shut down.

How to repeat:
Log onto MySQL command line. Create a database.

Create the following table:

mysql> create table team_members (
    -> id int(10) unsigned not null auto_increment,
    -> profile_id int(10) unsigned not null default '0',
    -> name varchar(255) not null default '',
    -> description longtext not null,
    -> primary key (id),
    -> index(profile_id));

Then issue the following SQL command:

SELECT * FROM team_members WHERE (id NOT IN (-1,0)) ORDER BY name

MySQLd crashes, producing Windows error message.

Suggested fix:
Unknown.
[10 May 2006 14:55] MySQL Verification Team
Thank you for the bug report. I was unable to repeat:

c:\mysql\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.21

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

mysql> create table team_members (
    -> id int(10) unsigned not null auto_increment,
    -> profile_id int(10) unsigned not null default '0',
    -> name varchar(255) not null default '',
    -> description longtext not null,
    -> primary key (id),
    -> index(profile_id));
Query OK, 0 rows affected (0.13 sec)

mysql>
mysql> SELECT * FROM team_members WHERE (id NOT IN (-1,0)) ORDER BY name;
Empty set (0.03 sec)

mysql>

If is necessary to have data could you please provide a dump and also
if that table was created with previous server version doing a dump and
re-load it again are you still able for to get the crash?

Thanks in advance.
[11 May 2006 11:18] Michael Phillips
Hi, thanks for the reply.

No data in table required to reproduce bug - for me, it occurs with an empty table.

Query works fine, and as expected (produces "empty set"), with my previous version (5.0.15).

Tried creating table in 5.0.15, dumping, and importing to 5.0.21, then executing query. Crash still occurs.

Strange that you can't reproduce the bug; it may be a problem with my system then. Are you testing using Windows XP?

Setup was installed using auto-installer from file mysql-5.0.21-win32.zip. Have tried with both MySQL installed as Windows service, and by running from command line. Crash occurs in both cases.

Thanks for the help,

Michael.
[11 May 2006 13:04] MySQL Verification Team
Thank you for the feedback. Yes I tested on XP SP2 starting with the
default values, could you please provide your my.ini for to test on my
side with similar conditions?

Thanks in advance.
[12 May 2006 9:22] Michael Phillips
MySQL 5.0.21 .ini file

Attachment: my.ini (application/octet-stream, text), 9.02 KiB.

[12 May 2006 9:26] Michael Phillips
Hi Miguel,

Thanks for your work on this. I've uploaded my my.ini file to the "files" section, but it's pretty much just default settings, as I was using a fresh install to test the bug.

I was also able to try the same installation on a different PC yesterday, and was able to reproduce the bug exactly, so it's not just an issue with this system. The other PC was running Windows XP Home with Service Pack 1.

I wonder is it some kind of corruption with the auto-installer? Did you install from the mysql-5.0.21-win32.zip file? I will try re-installing manually later and see if the same issue occurs.

Let me know if you need any more information,

Michael.
[12 May 2006 16:22] MySQL Verification Team
Repeated on linux/windows 5.0-bk using default settings:
testcase:

###
DROP TABLE IF EXISTS `t`;
CREATE TABLE `t`(`id` INT UNSIGNED PRIMARY KEY) ENGINE=InnoDB;
SELECT * FROM `t` WHERE (`id` NOT IN (-1,0));
###
[12 May 2006 16:50] MySQL Verification Team
Thank you for the bug report. Repeatable on Linux 5.0/5.1:

[New Thread 1101630384 (LWP 27628)]
060512 13:34:28 [Note] /home/miguel/dbs/5.1/libexec/mysqld: ready for connections.
Version: '5.1.11-beta-debug'  socket: '/tmp/mysql.sock'  port: 3306  Source distribution
[New Thread 1118682032 (LWP 27630)]

Program received signal SIGSEGV, Segmentation fault.
[Switching to Thread 1118682032 (LWP 27630)]
0x0830b4d5 in SEL_ARG::last (this=0x0) at opt_range.cc:1401
1401      if (!next_arg->right)
(gdb) bt full
#0  0x0830b4d5 in SEL_ARG::last (this=0x0) at opt_range.cc:1401
        next_arg = (SEL_ARG *) 0x0
#1  0x0831629f in get_func_mm_tree (param=0x42ad8090, cond_func=0x92e1848, field=0x931ea58, value=0x0, cmp_type=INT_RESULT, inv=true)
    at opt_range.cc:4751
        last_val = (SEL_ARG *) 0x42ad7f08
        new_interval = (SEL_ARG *) 0x92dd970
        idx = 0
        i = 1
        tmp_root = (MEM_ROOT *) 0x42ada100
        value_item = (class Item *) 0x92e2c20
        tree2 = (SEL_TREE *) 0x92dd838
        func = (class Item_func_in *) 0x92e1848
        tree = (SEL_TREE *) 0x92dd6c8
        _db_func_ = 0x87c572f "get_mm_tree"
        _db_file_ = 0x87c4c25 "opt_range.cc"
        _db_level_ = 11
        _db_framep_ = (char **) 0x42ad7ed8
#2  0x08317084 in get_mm_tree (param=0x42ad8090, cond=0x92e1848) at opt_range.cc:4987
        field_item = (class Item_field *) 0x92e1620

Not affects 4.1 server and MyISAM tables:

mysql> alter table t engine=myisam;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM `t` WHERE (`id` NOT IN (-1,0));
Empty set (0.00 sec)
[15 May 2006 0:24] MySQL Verification Team
Changing to MySQL server, see bug:

http://bugs.mysql.com/bug.php?id=19809

which was marked as duplicate of this one. It crashes with
MyISAM engine.
[15 May 2006 12:11] MySQL Verification Team
Changing to Analyze for to be tested when bug http://bugs.mysql.com/bug.php?id=19618 has its patch pushed.
[30 May 2006 12:12] MySQL Verification Team
Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html