Bug #518 Two level subquery crash MySQL server
Submitted: 27 May 2003 10:47 Modified: 30 May 2003 8:18
Reporter: Oleg Ivanov Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S1 (Critical)
Version:4.1.0alpha OS:Any (All)
Assigned to: Oleksandr Byelkin CPU Architecture:Any

[27 May 2003 10:47] Oleg Ivanov
Description:
Two level subquery crash MySQL server. Testcase attached.

How to repeat:
DROP TABLE IF EXISTS tab1;
CREATE TABLE tab1 (
  USER_ID int(5) unsigned NOT NULL default '0',
  TAB1_ID int(10) unsigned NOT NULL default '0',
  SUB_ID int(3) unsigned default '0',
  PRIMARY KEY  (USER_ID,TAB1_ID),
  UNIQUE KEY CRIGHTS_PK (USER_ID,TAB1_ID)
) TYPE=MyISAM CHARSET=cp1251;
INSERT INTO tab1 VALUES (2,15,0),(1,15,0);
DROP TABLE IF EXISTS tab2;
CREATE TABLE tab2 (
  ID int(10) unsigned NOT NULL auto_increment,
  SUB_ID int(3) unsigned NOT NULL default '0',
  REF_ID int(10) unsigned default NULL,
  REF_SUB int(3) unsigned default '0',
  PRIMARY KEY (ID,SUB_ID),
  UNIQUE KEY tab2_PK (ID,SUB_ID),
  KEY tab2_FK (REF_ID,REF_SUB),
  KEY tab2_REFID (REF_ID)
) TYPE=MyISAM CHARSET=cp1251;
INSERT INTO tab2 VALUES (1,0,NULL,NULL),(2,0,NULL,NULL);

select distinct TAB1_ID, REF_ID from tab1 
JOIN tab2 ON (TAB1_ID=ID) 
where USER_ID=1 AND REF_ID=
(SELECT DISTINCT REF_ID FROM tab2 WHERE ID=
(SELECT DISTINCT REF_ID FROM tab2 WHERE ID=2))

Suggested fix:
[27 May 2003 10:59] Oleg Ivanov
If change one "=" sign to "IN" query works correctly.
Example:
=====WORKS WITHOUT CRASH:====
select TAB1_ID from tab1 JOIN tab2 ON (TAB1_ID=ID) where REF_ID IN
(SELECT REF_ID FROM tab2 WHERE ID =
(SELECT REF_ID FROM tab2 WHERE ID=2))
=======================

=====SERVER CRASH:========
select TAB1_ID from tab1 JOIN tab2 ON (TAB1_ID=ID) where REF_ID =
(SELECT REF_ID FROM tab2 WHERE ID =
(SELECT REF_ID FROM tab2 WHERE ID=2))
===================
[27 May 2003 16:23] Miguel Solorzano
Thanks you for the bug report. Below the stack trace of the core
dump:

/usr/local/mysql/libexec/mysqld: ready for connections.
Version: '4.1.1-alpha-debug-log'  socket: '/tmp/mysql.sock'  port: 3306
[New Thread 9226 (LWP 12867)]

Program received signal SIGSEGV, Segmentation fault.
[Switching to Thread 9226 (LWP 12867)]
0x0815e696 in free_tmp_table(THD*, st_table*) (thd=0x88598b8, entry=0x88640d8)
    at sql_select.cc:4778
4778        (void) entry->file->close();
(gdb) backtrace full
#0  0x0815e696 in free_tmp_table(THD*, st_table*) (thd=0x88598b8, entry=0x88640d8)
    at sql_select.cc:4778
        save_proc_info = 0x8340744 "end"
        _db_func_ = 0x40040cce "\201Ãò\235"
        _db_file_ = 0x88624fc ""
        _db_level_ = 143018144
        _db_framep_ = (char **) 0xbe7ff454
#1  0x081562e4 in JOIN::cleanup(THD*) (this=0x88648a0, thd=0x88598b8)
    at sql_select.cc:1321
        _db_func_ = 0xbe7ff448 "ü$\206\bà&\205\bü$\206\b\224ô\177¾­b\025\b H\206\b¸\230\205\b|ô\177¾\200ô\177¾\220V)@ \f\206\b´ô\177¾ðö\204\b8#\205\b \f\206\bÔô\177¾à&\205\b \f\206\b(4\205\bÔô\177¾<ô\034\bà&\205\b¸\230\205\bÄô\177¾Àô\177¾ V)@@\"\205\b"
        _db_file_ = 0x88526e0 "Ð#\206\büÝ\177¾8\"\206\bø8\205\bø8\205\b"
        _db_level_ = 142943968
        _db_framep_ = (char **) 0xbe7ff494
#2  0x081562ad in JOIN::cleanup(THD*) (this=0x88526e0, thd=0x88598b8)
    at sql_select.cc:1315
        _db_func_ = 0x884f6f0 "hT)@hT)@"
        _db_file_ = 0x8852338 "hÐ7\b"
        _db_level_ = 143002784
        _db_framep_ = (char **) 0xbe7ff4d4
#3  0x081cf43c in st_select_lex_unit::cleanup() (this=0x8852338) at sql_union.cc:392
        join = (class JOIN *) 0x88526e0
        sl = (class st_select_lex *) 0x8852240
        error = 0
        _db_func_ = 0x82f50d1 "Ç\003"
        _db_file_ = 0x884f6f0 "hT)@hT)@"
---Type <return> to continue, or q <return> to quit---
        _db_level_ = 143002784
        _db_framep_ = (char **) 0xbe7ff514
#4  0x0815635d in JOIN::cleanup(THD*) (this=0x8860ca0, thd=0x88598b8)
    at sql_select.cc:1331
        unit = (class st_select_lex_unit *) 0x8852338
        _db_func_ = 0x8863708 "8U)@8U)@"
        _db_file_ = 0x8852008 "hÐ7\b"
        _db_level_ = 143005272
        _db_framep_ = (char **) 0xbe7ff554
#5  0x081cf43c in st_select_lex_unit::cleanup() (this=0x8852008) at sql_union.cc:392
        join = (JOIN *) 0x8860ca0
        sl = (st_select_lex *) 0x8851f10
        error = 0
        _db_func_ = 0x82f50d1 "Ç\003"
        _db_file_ = 0x8863708 "8U)@8U)@"
        _db_level_ = 143005272
        _db_framep_ = (char **) 0xbe7ff594
#6  0x0815635d in JOIN::cleanup(THD*) (this=0x8861658, thd=0x88598b8)
    at sql_select.cc:1331
        unit = (st_select_lex_unit *) 0x8852008
        _db_func_ = 0x0
        _db_file_ = 0x8853260 "èY8\b"
        _db_level_ = 0
        _db_framep_ = (char **) 0x0
#7  0x08156599 in mysql_select(THD*, Item***, st_table_list*, unsigned, List<Item>&, Item*, unsigned, st_order*, st_order*, Item*, st_order*, unsigned long, select_result*, st_select_lex_unit*, st_select_lex*, bool) (thd=0x88598b8,
---Type <return> to continue, or q <return> to quit---
    rref_pointer_array=0x8859c78, tables=0x8851b38, wild_num=0, fields=@0x8859cbc,
    conds=0x8853260, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0,
    select_options=84448257, result=0x8853428, unit=0x8859b3c,
    select_lex=0x8859c28, tables_and_fields_initied=false) at sql_select.cc:1399
        curr_join = (JOIN *) 0x8863bf8
        err = 0
        free_join = true
        _db_func_ = 0xbe7ff5e0 "(4\205\bDö\177¾v4\025\b¸\230\205\bx\234\205\b8\e\205\b"
        _db_file_ = 0x88598b8 "Xö8\b %:\b¤%:\b\220§\205\b ]\205\b }\205\b ]\205\b ]\205\b\r"
        _db_level_ = 142947368
        _db_framep_ = (char **) 0xbe7ff644
        join = (JOIN *) 0x8861658
#8  0x08153476 in handle_select(THD*, st_lex*, select_result*) (thd=0x88598b8,
    lex=0x8859b30, result=0x8853428) at sql_select.cc:174
        res = 142973992
        select_lex = (st_select_lex *) 0x8859c28
#9  0x08134e8e in mysql_execute_command(THD*) (thd=0x88598b8) at sql_parse.cc:2014
        want_priv = 142947368
        table = (st_table_list *) 0x8853428
        res = -1
        lex = (st_lex *) 0x8859b30
        tables = (st_table_list *) 0x8853378
        select_lex = (class st_select_lex *) 0x8859c28
        unit = (class st_select_lex_unit *) 0x8859b3c
        _db_func_ = 0x0
---Type <return> to continue, or q <return> to quit---
        _db_file_ = 0x0
        _db_level_ = 0
        _db_framep_ = (char **) 0x0
#10 0x08138750 in mysql_parse(THD*, char*, unsigned) (thd=0x88598b8,
    inBuf=0x8851968 "select distinct TAB1_ID, REF_ID from tab1 \r\nJOIN tab2 ON (TAB1_ID=ID) \r\nwhere USER_ID=1 AND REF_ID=\r\n(SELECT DISTINCT REF_ID FROM tab2 WHERE ID=\r\n(SELECT DISTINCT REF_ID FROM tab2 WHERE ID=2))", length=142973744)
    at sql_parse.cc:3519
        lex = (st_lex *) 0x8859b30
        _db_func_ = 0x88598b8 "Xö8\b %:\b¤%:\b\220§\205\b ]\205\b }\205\b ]\205\b ]\205\b\r"
        _db_file_ = 0x3 <Address 0x3 out of bounds>
        _db_level_ = 142973112
        _db_framep_ = (char **) 0xbe7ff9f4
#11 0x08133457 in dispatch_command(enum_server_command, THD*, char*, unsigned) (
    command=COM_QUERY, thd=0x88598b8, packet=0x8855d21 "", packet_length=193)
    at sql_parse.cc:1272
        net = (st_net *) 0x88598c4
        error = false
        slow_command = false
        _db_func_ = 0x3f <Address 0x3f out of bounds>
        _db_file_ = 0xbe7ff948 "îü\003@p\217)@ÿÿÿÿdù\177¾Á"
        _db_level_ = 3196057932
        _db_framep_ = (char **) 0xbe7ff950
        start_of_query = 142973112
#12 0x08132dfc in do_command(THD*) (thd=0x88598b8) at sql_parse.cc:1072
        packet = 0x8855d20 "\005"
---Type <return> to continue, or q <return> to quit---
        old_timeout = 30
        packet_length = 193
        net = (st_net *) 0x88598c4
        command = COM_QUERY
        _db_func_ = 0x8116773 "\203Ä\020\213]ü\211ì]Ã\220U\211åS\203ì\020\213]\bSè<"
        _db_file_ = 0x885a448 "@~\205\b"
        _db_level_ = 4096
        _db_framep_ = (char **) 0x1000
#13 0x08132497 in handle_one_connection (arg=0x8863bf8) at sql_parse.cc:861
        error = 0
        net = (st_net *) 0x88598c4
        thd = (THD *) 0x88598b8
        launch_time = 143014904
        set = {__val = {0 <repeats 32 times>}}
#14 0x4003c1b0 in pthread_start_thread () from /lib/libpthread.so.0
No symbol table info available.
#15 0x4003c22f in pthread_start_thread_event () from /lib/libpthread.so.0
[28 May 2003 6:57] Oleksandr Byelkin
ChangeSet 
  1.1570 03/05/28 16:52:56 bell@sanja.is.com.ua +4 -0
[30 May 2003 8:18] Oleksandr Byelkin
patch was pushed to bk repository and will be in next 4.1 release. 
 
Thank you for bug report.