MySQL Bugs: #22413: EXPLAIN SELECT FROM view with ORDER BY yield server crash

Bug #22413	EXPLAIN SELECT FROM view with ORDER BY yield server crash
Submitted:	16 Sep 2006 13:59	Modified:	27 Nov 2006 18:19
Reporter:	Masaaki HIROSE	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S1 (Critical)
Version:	5.0.24a,5.0BK,5.1BK	OS:
Assigned to:	Sergei Glukhov	CPU Architecture:	Any
Tags:	crash, explain, information_schema, mysqld, temp table

Description:
EXPLAIN SELECT FROM view with ORDER BY yield server crash.

There is no problem without ORDER BY.

How to repeat:
REATE OR REPLACE VIEW test_view AS
  SELECT
    table_schema AS object_schema
   ,table_name   AS object_name
   ,table_type   AS object_type
    FROM information_schema.tables
ORDER BY object_schema;

EXPLAIN SELECT * FROM test_view;

ERROR 2013 (HY000): Lost connection to MySQL server during query

verified on 5.0 and 5.1.

0x80db22b handle_segfault + 417
0x83c921d __pthread_sighandler + 173
0x81fda14 _Z20mysql_handle_derivedP6st_lexPFbP3THDS0_P13st_table_listE + 116
0x811f1ab _Z30open_normal_and_derived_tablesP3THDP13st_table_listj + 203
0x81d32a4 _Z14get_all_tablesP3THDP13st_table_listP4Item + 2022
0x81d97af _Z24get_schema_tables_resultP4JOIN + 435
0x812e22f _ZN4JOIN4execEv + 1487
0x812fb6b _Z12mysql_selectP3THDPPP4ItemP13st_table_listjR4ListIS1_ES2_jP8st_orderSB_S2_SB_mP13select_resultP18st_select_lex_unitP13st_sel + 617
0x81fdedf _Z21mysql_derived_fillingP3THDP6st_lexP13st_table_list + 285
0x81fd9f2 _Z20mysql_handle_derivedP6st_lexPFbP3THDS0_P13st_table_listE + 82
0x811f04b _Z20open_and_lock_tablesP3THDP13st_table_list + 257
0x80f230e _Z21mysql_execute_commandP3THD + 1096
0x80fb2d4 _Z11mysql_parseP3THDPcj + 370
0x80f07d8 _Z16dispatch_command19enum_server_commandP3THDPcj + 1946
0x80f0033 _Z10do_commandP3THD + 523
0x80ef1cf handle_one_connection + 799
0x83c40f1 pthread_start_thread + 225
0x841e88a __clone + 106

stack traces

Attachment: stacks.txt (plain/text, text), 2.44 KiB.

Accoriding to Igor, the problem has nothing to do with VIEWs. It is a problem of INFORMATION_SCHEMA and derived tables.
Here is a simpler testcase: 

mysql > explain select * from (select table_name from information_schema.tables ) AS a;

  Version: '5.0.26-debug'  socket: '/tmp/mysql.sock'  port: 3306  Source distribution
  [New Thread 163851 (LWP 28014)]
  
  Program received signal SIGSEGV, Segmentation fault.
  [Switching to Thread 163851 (LWP 28014)]
  0x0820f79f in simplify_joins (join=0x8d49d88, join_list=0x8d49548, conds=0x0, top=true) at sql_select.cc:7664
(gdb) wher
  #0  0x0820f79f in simplify_joins (join=0x8d49d88, join_list=0x8d49548, conds=0x0, top=true) at sql_select.cc:7664
  #1  0x081fe856 in JOIN::optimize (this=0x8d49d88) at sql_select.cc:618
  #2  0x08203052 in mysql_select (thd=0x8cc59c0, rref_pointer_array=0x8d49594, tables=0x8d49ae0, wild_num=0, fields=@0x8d494f8, conds=0x0, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2424588804, result=0x8d6e148, unit=0x8d4923c, select_lex=0x8d4946c) at sql_select.cc:1974
  #3  0x0821ffd2 in mysql_explain_union (thd=0x8cc59c0, unit=0x8d4923c, result=0x8d6e148) at sql_select.cc:14626
  #4  0x0821fc3f in select_describe (join=0x8cef158, need_tmp_table=false, need_order=false, distinct=false, message=0x0) at sql_select.cc:14578
  #5  0x082015b4 in JOIN::exec (this=0x8cef158) at sql_select.cc:1409
  #6  0x082030db in mysql_select (thd=0x8cc59c0, rref_pointer_array=0x8ce5a10, tables=0x8ce5da0, wild_num=0, fields=@0x8ce5974, conds=0x0, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2961459716, result=0x8d6e148, unit=0x8ce5a58, select_lex=0x8ce58e8) at sql_select.cc:1988
  #7  0x0821ffd2 in mysql_explain_union (thd=0x8cc59c0, unit=0x8ce5a58, result=0x8d6e148) at sql_select.cc:14626
  #8  0x0821fc3f in select_describe (join=0x8d6e4c0, need_tmp_table=false, need_order=false, distinct=false, message=0x0) at sql_select.cc:14578
  #9  0x082015b4 in JOIN::exec (this=0x8d6e4c0) at sql_select.cc:1409
  #10 0x082030db in mysql_select (thd=0x8cc59c0, rref_pointer_array=0x8cc5dbc, tables=0x8ce6148, wild_num=1, fields=@0x8cc5d20, conds=0x0, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2156153348, result=0x8d6e148, unit=0x8cc5a64, select_lex=0x8cc5c94) at sql_select.cc:1988
  #11 0x0821ffd2 in mysql_explain_union (thd=0x8cc59c0, unit=0x8cc5a64, result=0x8d6e148) at sql_select.cc:14626
  #12 0x081bfc2b in mysql_execute_command (thd=0x8cc59c0) at sql_parse.cc:2554
  #13 0x081c906e in mysql_parse (thd=0x8cc59c0, inBuf=0x8ce55e8 "explain  select * from ( select table_name from information_schema.tables ) AS a", length=80) at sql_parse.cc:5842
  #14 0x081bdda7 in dispatch_command (command=COM_QUERY, thd=0x8cc59c0, packet=0x8cdd589 "", packet_length=81) at sql_parse.cc:1766
  #15 0x081bd4f7 in do_command (thd=0x8cc59c0) at sql_parse.cc:1550
  #16 0x081bc5e0 in handle_one_connection (arg=0x8cc59c0) at sql_parse.cc:1181
  #17 0xb7eff0a5 in pthread_start_thread () from /lib/libpthread.so.0
  #18 0xb7eff254 in pthread_start_thread_event () from /lib/libpthread.so.0
  #19 0xb7e320aa in clone () from /lib/libc.so.6

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/14934

ChangeSet@1.2284, 2006-11-07 13:15:28+04:00, gluh@mysql.com +3 -0
  Bug#22413 EXPLAIN SELECT FROM view with ORDER BY yield server crash
  disable infill of I_S tables for EXPLAIN

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/15612

ChangeSet@1.2315, 2006-11-21 13:45:01+04:00, gluh@mysql.com +3 -0
  Bug#22413 EXPLAIN SELECT FROM view with ORDER BY yield server crash
  disable filling of I_S tables for EXPLAIN

Pushed in 5.0.32/5.1.14-beta

Noted in 5.0.32, 5.1.14 changelogs.

Using EXPLAIN caused a server crash for queries that selected from
INFORMATION_SCHEMA in a subquery in the FROM clause.