Bug #12432 Crash on SELECT with unusual condition on foreign key

• Submitted: 8 Aug 2005 12:17
• Modified: 9 Aug 2005 6:53
• Reporter: Tobias Hofer
• Status: Can't repeat
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 4.1.11
• OS: Linux (Debian GNU/Linux (Sarge))
• Assigned to: Bugs System
• CPU Architecture: Any

[8 Aug 2005 12:17] Tobias Hofer

Description:
MySQL Server crashes having a foreign key condition with a negative test value.

The following select statement leads to a server crash
mysql> select * from Child as c left join Parent as p on c.parent=p.id where p.id=-1;

But the following does not (test value greater than or equal zero)
mysql> select * from Child as c left join Parent as p on c.parent=p.id where p.id=10;

Note: It's quite plain to me that the statement should be used like this which in turn goes well.
mysql> select * from Child as c left join Parent as p on c.parent=p.id where c.parent=-1;

Resolved stacktrace

0x81893bf handle_segfault + 703
0x4005a825 _end + 933034357
0x84d8838 my_strtoll10 + 40
0x8112c90 _ZN8Item_intC1EPKcj + 64
0x811b0f2 _ZN17Item_int_with_ref8new_itemEv + 66
0x81d7ceb _Z24change_cond_ref_to_constP3THDP6I_ListI8COND_CMPEP4ItemS6_S6_S6_ + 571
0x81d7e3d _Z24change_cond_ref_to_constP3THDP6I_ListI8COND_CMPEP4ItemS6_S6_S6_ + 909
0x81cba85 _Z24propagate_cond_constantsP3THDP6I_ListI8COND_CMPEP4ItemS6_ + 261
0x81cbb2f _Z24propagate_cond_constantsP3THDP6I_ListI8COND_CMPEP4ItemS6_ + 431
0x81c3c46 _ZN4JOIN8optimizeEv + 134
0x81c6a16 _Z12mysql_selectP3THDPPP4ItemP13st_table_listjR4ListIS1_ES2_jP8st_orderSB_S2_SB_mP13select_resultP18st_select_lex_unitP13st_sel + 102
0x81c33f2 _Z13handle_selectP3THDP6st_lexP13select_result + 242
0x819d716 _Z21mysql_execute_commandP3THD + 934
0x81a2dac _Z11mysql_parseP3THDPcj + 204
0x819c165 _Z16dispatch_command19enum_server_commandP3THDPcj + 981
0x819bd46 _Z10do_commandP3THD + 134
0x819b4a8 handle_one_connection + 856
0x40054e51 _end + 933011361
0x4029f92a _end + 935414394

How to repeat:

CREATE TABLE `Parent` (
  `id` bigint(20) NOT NULL auto_increment,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB

CREATE TABLE `Child` (
  `id` bigint(20) NOT NULL auto_increment,
  `parent` bigint(20) NOT NULL default '0',
  PRIMARY KEY  (`id`),
  KEY `parent` (`parent`),
  CONSTRAINT `Child_ibfk_1` FOREIGN KEY (`parent`) REFERENCES `Parent` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB

mysql> SELECT * FROM Child AS c LEFT JOIN Parent AS p ON c.parent=p.id WHERE p.id=-1;

[8 Aug 2005 15:00] Tobias Hofer

Corrected synopsis from '..unusable..' to '..unusual..'.

[9 Aug 2005 5:58] Jan Lindström

Thank you for your bug report. However I can't repeat this bug using 4.1.14bk.

mysql> CREATE TABLE `Parent` (
    ->   `id` bigint(20) NOT NULL auto_increment,
    ->   PRIMARY KEY  (`id`)
    -> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE `Child` (
    ->   `id` bigint(20) NOT NULL auto_increment,
    ->   `parent` bigint(20) NOT NULL default '0',
    ->   PRIMARY KEY  (`id`),
    ->   KEY `parent` (`parent`),
    ->   CONSTRAINT `Child_ibfk_1` FOREIGN KEY (`parent`) REFERENCES `Parent` (`id`) ON
    -> DELETE CASCADE
    -> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT * FROM Child AS c LEFT JOIN Parent AS p ON c.parent=p.id WHERE
    -> p.id=-1;
Empty set (0.00 sec)

Could you provide contents of tables Child and Parent as small as possible using mysqldump.

Regards
    Jan

[9 Aug 2005 6:30] Tobias Hofer

There's no need for any content.

It looks like the crash is caused by the where clause (with a negative test value)

[9 Aug 2005 6:53] Jan Lindström

I can't repeat this bug using current release. Please consider upgrading to 4.1.13.

mysql> CREATE TABLE `Parent` (`id` bigint(20) NOT NULL auto_increment,
    ->   PRIMARY KEY  (`id`)) ENGINE=InnoDB;
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE `Child` (`id` bigint(20) NOT NULL auto_increment,
    ->   `parent` bigint(20) NOT NULL default '0', PRIMARY KEY  (`id`),
    ->   KEY `parent` (`parent`),
    ->   CONSTRAINT `Child_ibfk_1` FOREIGN KEY (`parent`) REFERENCES `Parent` (`id`) ON DELETE CASCADE) ENGINE=InnoDB;
Query OK, 0 rows affected (0.01 sec)

mysql> insert into Parent values(-1);
Query OK, 1 row affected (0.01 sec)

mysql> insert into Child values(null,-1);
Query OK, 1 row affected (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into Parent values(2);
Query OK, 1 row affected (0.01 sec)

mysql> insert into Child values(null,2);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM Child AS c LEFT JOIN Parent AS p ON c.parent=p.id WHERE
    -> p.id=-1;
+----+--------+----+
| id | parent | id |
+----+--------+----+
|  1 |     -1 | -1 |
+----+--------+----+
1 row in set (0.00 sec)

Regards,
    Jan