MySQL Bugs: #33245: Crash on VIEW referencing FROM table in an IN clause

Bug #33245	Crash on VIEW referencing FROM table in an IN clause
Submitted:	14 Dec 2007 14:31	Modified:	16 Nov 2010 2:04
Reporter:	Philip Stoev	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Views	Severity:	S3 (Non-critical)
Version:	6.0.4-BK, 6.0.5-BK	OS:	Any
Assigned to:	Sergey Petrunya	CPU Architecture:	Any

Description:
If you create a VIEW that references one and the same table in the FROM clause and in an IN clause, selecting from that view causes server to crash.

How to repeat:
CREATE TABLE t1 (f1 INT NOT NULL);
CREATE VIEW v1 (a) AS SELECT f1 IN (SELECT f1 FROM t1) FROM t1;
SELECT * FROM v1;

Suggested fix:
It appears to me this is a structure initialization issue, because the following sequence does not lead to a crash:

CREATE TABLE t1 (f1 INT NOT NULL);
SELECT f1 IN (SELECT f1 FROM t1) FROM t1;
CREATE VIEW v1 (a) AS SELECT f1 IN (SELECT f1 FROM t1) FROM t1;
SELECT * FROM v1;

That is, if the query from the view is executed as standalone before selecting from the view.

From backtrace:

0x82d7a7e handle_segfault + 680
0x83571a9 _Z18convert_subq_to_sjP4JOINP17Item_in_subselect + 137
0x8357dca _ZN4JOIN18flatten_subqueriesEv + 610
0x836d994 _Z12mysql_selectP3THDPPP4ItemP10TABLE_LISTjR4ListIS1_ES2_jP8st_orderSB_S2_SB_yP13select_resultP18st_select_lex_unitP13st_select + 688
0x8473d20 _Z21mysql_derived_fillingP3THDP6st_lexP10TABLE_LIST + 464
0x8473acb _Z20mysql_handle_derivedP6st_lexPFbP3THDS0_P10TABLE_LISTE + 115
0x8335137 _Z28open_and_lock_tables_derivedP3THDP10TABLE_LISTb + 459
0x82f4392 _Z20open_and_lock_tablesP3THDP10TABLE_LIST + 32
0x82e7af1 _Z21execute_sqlcom_selectP3THDP10TABLE_LIST + 165
0x82e922c _Z21mysql_execute_commandP3THD + 1726
0x82f1a06 _Z11mysql_parseP3THDPKcjPS2_ + 518
0x82f2419 _Z16dispatch_command19enum_server_commandP3THDPcj + 2177
0x82f359f _Z10do_commandP3THD + 643
0x82e068d handle_one_connection + 279

From mysql-test-run --debug:

Warning: Not freed memory segments: 1
Warning: Memory that was not free'ed (256 bytes):
           256 bytes at 0x0099bf518, allocated at line   39 in 'string.c'

From server log file:

071214 17:39:57 - mysqld got signal 11;

Maybe this is an optimizer bug?

Thank you for a problem report. Had you used a -debug build? I can not repeat the crash with older non-debug 6.0.3 on Windows for example:

C:\Program Files\MySQL\MySQL Server 5.1\bin>mysql -uroot test -P3311
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 6.0.3-alpha-community MySQL Community Server (GPL)

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

mysql> CREATE TABLE t1 (f1 INT NOT NULL);
Query OK, 0 rows affected (0.78 sec)

mysql> CREATE VIEW v1 (a) AS SELECT f1 IN (SELECT f1 FROM t1) FROM t1;
Query OK, 0 rows affected (0.14 sec)

mysql> SELECT * FROM v1;
Empty set (0.11 sec)

I used a -debug build using the latest BK mysql-6.0 tree.

I am also unable to reproduce this bug if I start the server on my own, however if you run the attached testcase using mysql-test-run --record, the crash will occur.

Test case for bug #33245

Attachment: bug33245.test (text/plain), 120 bytes.

I was able to repeat it with a freshly cloned and debug-compiled mysql-6.0 tree and mysql-test-run on the attached testcase.

uname is:

Linux philips 2.6.21-1.3194.fc7 #1 SMP Wed May 23 22:35:01 EDT 2007 i686 i686 i386 GNU/Linux

That is, a 32-bit machine.

Thank you for the report.

Verified as described.

Repeatable on latest mysql-6.0.6 opt-bk. 

Stack trace:
Program received signal SIGSEGV, Segmentation fault.
0x08389be7 in replace_where_subcondition (join=0x8de6ad8, old_cond=0x8ddffb0, 
    new_cond=0x8de0598, do_fix_fields=false) at sql_select.cc:15000
15000     if (join->conds->type() == Item::COND_ITEM) {
(gdb) wher
#0  0x08389be7 in replace_where_subcondition (join=0x8de6ad8, 
    old_cond=0x8ddffb0, new_cond=0x8de0598, do_fix_fields=false)
    at sql_select.cc:15000
#1  0x0839c937 in JOIN::flatten_subqueries (this=0x8de6ad8)
    at sql_select.cc:3402
#2  0x083b2414 in mysql_select (thd=0x8d7b4c0, rref_pointer_array=0x8ddf1c8, 
    tables=0x8de0110, wild_num=0, fields=@0x8ddf158, conds=0x0, og_num=0, 
    order=0x0, group=0x0, having=0x0, proc_param=0x0, 
    select_options=2416200192, result=0x8de0390, unit=0x8ddee3c, 
    select_lex=0x8ddf0c4) at sql_select.cc:2950
#3  0x084c0020 in mysql_derived_filling (thd=0x8d7b4c0, lex=0x8d7c678, 
    orig_table_list=0x8ddea18) at sql_derived.cc:264
#4  0x084bfdcb in mysql_handle_derived (lex=0x8d7c678, 
    processor=0x84bfe50 <mysql_derived_filling(THD*, st_lex*, TABLE_LIST*)>)
    at sql_derived.cc:56
#5  0x08379eed in open_and_lock_tables_derived (thd=0x8d7b4c0, 
    tables=0x8ddea18, derived=true) at sql_base.cc:4810
#6  0x083375de in open_and_lock_tables (thd=0x8d7b4c0, tables=0x8ddea18)
    at mysql_priv.h:1569
#7  0x0832a3e1 in execute_sqlcom_select (thd=0x8d7b4c0, all_tables=0x8ddea18)
    at sql_parse.cc:4771
#8  0x0832bb3a in mysql_execute_command (thd=0x8d7b4c0) at sql_parse.cc:1994
#9  0x083349b0 in mysql_parse (thd=0x8d7b4c0, 
---Type <return> to continue, or q <return> to quit---
    inBuf=0x8dde8e0 "SELECT * FROM v1", length=16, found_semicolon=0xb0388260)
    at sql_parse.cc:5767
#10 0x08335408 in dispatch_command (command=COM_QUERY, thd=0x8d7b4c0, 
    packet=0x8dcfc51 "SELECT * FROM v1", packet_length=16) at sql_parse.cc:1044
#11 0x08336668 in do_command (thd=0x8d7b4c0) at sql_parse.cc:721
#12 0x0832292b in handle_one_connection (arg=0x8d7b4c0) at sql_connect.cc:1134
#13 0xb7f6618b in start_thread () from /lib/libpthread.so.0
#14 0xb7d7209e in clone () from /lib/libc.so.6

The reason for crash:

(gdb) p join->conds
$1 = (COND *) 0x0

The actual cause of the problem was that thd->thd_marker was initialized after derived table/VIEW processing. The value of thd->thd_marker is used by semi-join applicability check to determine if the subquery is an AND-part of WHERE clause or or not. Uninitialized value could cause mysqld to conclude that the subquery that is located in the select_list is located in the WHERE, which would later cause crash because of logical inconsistencies.

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

ChangeSet@1.2628, 2008-04-29 03:00:37+04:00, sergefp@mysql.com +9 -0
  BUG#33245: Crash on VIEW referencing FROM table in an IN clause:
  - Initialize thd->thd_marker.emb_on_expr_nest before we process derived tables/VIEWs. The processing
    may invoke in_subquery_predicate->fix_fields() which required thd->thd_marker to have correct value
  - Code cleanup: make thd->thd_marker a union with a member having meaningful type/name

Pushed into 6.0.6-alpha

Noted in 6.0.6 changelog.

Selecting from a view that referenced the same table in the FROM
clause and an IN clause caused a server crash.

Pushed into mysql-next-mr (revid:alik@sun.com-20100816062819-bluwgdq8q4xysmlg) (version source revid:alik@sun.com-20100816062612-enatdwnv809iw3s9) (pib:20)

Pushed into mysql-trunk 5.6.99-m5 (revid:alexander.nozdrin@oracle.com-20101113155825-czmva9kg4n31anmu) (version source revid:vasil.dimov@oracle.com-20100629074804-359l9m9gniauxr94) (merge vers: 5.6.99-m4) (pib:21)

Noted in 5.6.1 changelog.