From: Dan Nelson
Date: April 24 2003 5:20pm
Subject: Re: crash in mysql 4.1.0 EXPLAIN w/ subquery
List-Archive: http://lists.mysql.com/mysql/138288
Message-Id: <20030424172011.GD45035@dan.emsphone.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 8bit

In the last episode (Apr 23), dnelson@stripped said:
> >Description:
> 
> An EXPLAIN SELECT .. WHERE field = ( SELECT ... ) query crashes, where
> the query itself runs fine.

And here's the associated stack trace.  

(gdb) bt
#0  0x0810b510 in ~SQL_SELECT (this=0xd7c38a8) at opt_range.cc:377
#1  0x080d8295 in JOIN::cleanup(THD*) (this=0xd7d3690, thd=0xd7ae000)
    at sql_select.cc:1309
#2  0x0814eaef in st_select_lex_unit::cleanup() (this=0xd7d32f8)
    at sql_union.cc:379
#3  0x080d8268 in JOIN::cleanup(THD*) (this=0xd7d4068, thd=0xd7ae000)
    at sql_select.cc:1306
#4  0x080d838b 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=0xd7ae000, 
    rref_pointer_array=0xd7c38a8, tables=0xd7c38a8, wild_num=226244776, 
    fields=@0xd7c38a8, conds=0xd7c38a8, og_num=226244776, order=0xd7c38a8, 
    group=0xd7c38a8, having=0xd7c38a8, proc_param=0xd7c38a8, 
    select_options=84415492, result=0x0, unit=0xd7ae144, select_lex=0xd7ae22c, 
    tables_and_fields_initied=false) at sql_select.cc:1374
#5  0x080e6c63 in mysql_explain_select(THD*, st_select_lex*, char const*, select_result*) (thd=0xd7ae000, select_lex=0xd7ae27c, 
    type=0xd7c38a8 '╤' <repeats 200 times>..., result=0xd7c38a8)
    at sql_select.cc:8128
#6  0x080e6b1f in mysql_explain_union(THD*, st_select_lex_unit*, select_result*) (thd=0xd7ae000, unit=0xd7c38a8, result=0xd7d3d98) at sql_select.cc:8096
#7  0x080bf86c in mysql_execute_command(THD*) (thd=0xd7ae000)
    at sql_parse.cc:1692
#8  0x080c3d8e in mysql_parse(THD*, char*, unsigned) (thd=0xd7ae22c, 
    inBuf=0xd7d3010 "EXPLAIN SELECT COUNT(*) FROM table1 WHERE name1 = ( SELECT name2 FROM table2 WHERE id=1)", length=226156544) at sql_parse.cc:3375
#9  0x080be4e3 in dispatch_command(enum_server_command, THD*, char*, unsigned)
    (command=226157100, thd=0xd7d3010, packet=0xd7af001 "", 
    packet_length=226156544) at sql_parse.cc:1249
#10 0x080be121 in do_command(THD*) (thd=0xd7ae000) at sql_parse.cc:1049
#11 0x080bda29 in handle_one_connection (arg=0xd7ae000) at sql_parse.cc:838
#12 0x2846b3ee in _thread_start ()
    at /usr/src/lib/libc_r/uthread/uthread_create.c:275
(gdb) 

 
> >How-To-Repeat:
> 
> Run the following commands.  The EXPLAIN will fail.  Removing the INDEX
> on table1 makes the crash disappear also.  I can generate a stack trace
> if necessary.
> 
> DROP TABLE IF EXISTS table1;
> CREATE TABLE table1 (
>   name1 char(20),
>   INDEX(name1)
> ) TYPE=MyISAM;
> 
> DROP TABLE IF EXISTS table2;
> CREATE TABLE table2 (
>   name2 char(20),
>   id int
> ) TYPE=MyISAM;
> 
> INSERT INTO table1 VALUES ('record 1'), ('record 2');
> INSERT INTO table2 VALUES ('record 1', 1);
> 
> EXPLAIN SELECT COUNT(*) FROM table1 WHERE name1 = ( SELECT name2 FROM table2 WHERE id=1 );

-- 
	Dan Nelson
	dnelson@stripped