Bug #42742 crash in setup_sj_materialization, Copy_field::set
Submitted: 10 Feb 2009 22:00 Modified: 16 Nov 2010 3:57
Reporter: Shane Bester (Platinum Quality Contributor) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S1 (Critical)
Version:6.0.10-debug OS:Any
Assigned to: Evgeny Potemkin CPU Architecture:Any
Tags: Contribution, materialization, optimizer_switch, semijoin

[10 Feb 2009 22:00] Shane Bester
Description:
00452A02    mysqld.exe!Copy_field::set()[field_conv.cc:566]
006F1EFC    mysqld.exe!setup_sj_materialization()[sql_select.cc:9776]
006F21BA    mysqld.exe!make_join_readinfo()[sql_select.cc:10061]
006DEC89    mysqld.exe!JOIN::optimize()[sql_select.cc:1919]
006E2653    mysqld.exe!mysql_select()[sql_select.cc:3044]
0070E914    mysqld.exe!mysql_explain_union()[sql_select.cc:21792]
0069CB9C    mysqld.exe!execute_sqlcom_select()[sql_parse.cc:4734]
00695AC6    mysqld.exe!mysql_execute_command()[sql_parse.cc:2063]
0069ED33    mysqld.exe!mysql_parse()[sql_parse.cc:5751]
00693E48    mysqld.exe!dispatch_command()[sql_parse.cc:1009]
006936C9    mysqld.exe!do_command()[sql_parse.cc:691]
007BC4E9    mysqld.exe!handle_one_connection()[sql_connect.cc:1146]
008A9C48    mysqld.exe!pthread_start()[my_winthread.c:61]
00C10891    mysqld.exe!_callthreadstartex()[threadex.c:348]
00C10832    mysqld.exe!_threadstartex()[threadex.c:331]
7D4DFFF1    kernel32.dll!FlsSetValue()
Trying to get some variables.

How to repeat:
drop table if exists `t2`,`t3`;
create table `t3`( `c1` year)engine=innodb;
insert into t3 values (2135),(2142);
create table `t2` (`c1` tinytext,`c2` text,`c6` timestamp) engine=innodb;
explain select 1 from `t2`where 
`c2` in (select 1 from `t3`, `t2`) and
`c1` in (select convert(`c6`,char(1)) from `t2`);
[10 Feb 2009 23:16] Sveta Smirnova
Could not repeat with lates BZR build on Linux. Probably duplicate of bug #41842
[10 Feb 2009 23:36] MySQL Verification Team
bug #41842 testcase doesn't crash my instance (built from lp bzr today). so i think i have the fix, or there's some memory corruption happening and valgrind might show it for this bug?
[11 Feb 2009 6:25] Sveta Smirnova
Yes, valgrind shows warnings:

Version: '6.0.10-alpha-debug-log'  socket: '/users/ssmirnova/build/mysql-6.0/mysql-test/var/tmp/mysqld.1.sock'  port: 12500  Source distribution
==8524== 
==8524== Thread 22:
==8524== Conditional jump or move depends on uninitialised value(s)
==8524==    at 0x834F80C: setup_sj_materialization(st_join_table*) (sql_select.cc:9629)
==8524==    by 0x835009A: make_join_readinfo(JOIN*, unsigned long long, unsigned) (sql_select.cc:10061)
==8524==    by 0x836306C: JOIN::optimize() (sql_select.cc:1919)
==8524==    by 0x8369A0B: mysql_select(THD*, Item***, TABLE_LIST*, unsigned, List<Item>&, Item*, unsigned, st_order*, st_order*, Item*, st_order*, unsigned long long, select_result*, st_select_lex_unit*, st_select_lex*) (sql_select.cc:3044)
==8524==    by 0x836458D: mysql_explain_union(THD*, st_select_lex_unit*, select_result*) (sql_select.cc:21792)
==8524==    by 0x82C9604: execute_sqlcom_select(THD*, TABLE_LIST*) (sql_parse.cc:4734)
==8524==    by 0x82CF2B9: mysql_execute_command(THD*) (sql_parse.cc:2063)
==8524==    by 0x82D80B4: mysql_parse(THD*, char const*, unsigned, char const**) (sql_parse.cc:5751)
==8524==    by 0x82D8AF6: dispatch_command(enum_server_command, THD*, char*, unsigned) (sql_parse.cc:1009)
==8524==    by 0x82D9E5A: do_command(THD*) (sql_parse.cc:691)
==8524==    by 0x82C7D6A: handle_one_connection (sql_connect.cc:1146)
==8524==    by 0x45FBD3: start_thread (in /lib/libpthread-2.3.6.so)
==8524==    by 0x3B74FD: clone (in /lib/libc-2.3.6.so)
==8524== 
==8524== Conditional jump or move depends on uninitialised value(s)
==8524==    at 0x8366F5A: select_describe(JOIN*, bool, bool, bool, char const*) (sql_select.cc:21686)
==8524==    by 0x8367DF3: JOIN::exec() (sql_select.cc:2428)
==8524==    by 0x8369A9B: mysql_select(THD*, Item***, TABLE_LIST*, unsigned, List<Item>&, Item*, unsigned, st_order*, st_order*, Item*, st_order*, unsigned long long, select_result*, st_select_lex_unit*, st_select_lex*) (sql_select.cc:3058)
==8524==    by 0x836458D: mysql_explain_union(THD*, st_select_lex_unit*, select_result*) (sql_select.cc:21792)
==8524==    by 0x82C9604: execute_sqlcom_select(THD*, TABLE_LIST*) (sql_parse.cc:4734)
==8524==    by 0x82CF2B9: mysql_execute_command(THD*) (sql_parse.cc:2063)
==8524==    by 0x82D80B4: mysql_parse(THD*, char const*, unsigned, char const**) (sql_parse.cc:5751)
==8524==    by 0x82D8AF6: dispatch_command(enum_server_command, THD*, char*, unsigned) (sql_parse.cc:1009)
==8524==    by 0x82D9E5A: do_command(THD*) (sql_parse.cc:691)
==8524==    by 0x82C7D6A: handle_one_connection (sql_connect.cc:1146)
==8524==    by 0x45FBD3: start_thread (in /lib/libpthread-2.3.6.so)
==8524==    by 0x3B74FD: clone (in /lib/libc-2.3.6.so)
[21 Mar 2009 15:47] Sergey Petrunya
Doesn't crash anymore but EXPLAIN doesn't look right:

mysql> explain select 1 from `t2`where 
    -> `c2` in (select 1 from `t3`, `t2`) and
    -> `c1` in (select convert(`c6`,char(1)) from `t2`);
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                             |
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------+
|  1 | PRIMARY     | t2    | ALL  | NULL          | NULL | NULL    | NULL |    1 | Using where                       | 
|  1 | PRIMARY     | t2    | ALL  | NULL          | NULL | NULL    | NULL |    1 |                                   | 
|  1 | PRIMARY     | t2    | ALL  | NULL          | NULL | NULL    | NULL |    1 | Start materialize                 | 
|  1 | PRIMARY     | t3    | ALL  | NULL          | NULL | NULL    | NULL |    2 | FirstMatch(t2); Using join buffer | 
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------+
4 rows in set (5.69 sec)
[21 Mar 2009 16:03] MySQL Verification Team
[17:53] <sbester|weekend> spetrunia: just to clarify: bug 42742 might not always crash. did you get any valgrind errors ?
[6 Jul 2009 14:38] Sergey Petrunya
The fix is available here: 
https://code.launchpad.net/~maria-captains/maria/mysql-6.0-testing2

(launchpad email lists are down now so can't post link to the patch).

The tree is in our buildbot: http://askmonty.org/buildbot/grid?branch=mysql-6.0-testing2
[10 Sep 2009 7:56] Sergei Golubchik
http://lists.mysql.com/internals/37295
[10 Sep 2009 7:58] Sergei Golubchik
sorry, wrong url. use this one:

http://lists.mysql.com/internals/37294
[20 Oct 2009 8:25] Lenz Grimmer
Patch contributed under SCA governance is here:

http://lists.mysql.com/internals/37399
[20 Oct 2009 16:22] Masood Mortazavi
Looking at the internals thread, we still need some clarification.

Is this Anna Widenius' contribution or is it Sergey Petrunya's?

Sergey Petrunya has not signed the SCA (Sun Contributor Agreement) but Anna has.

As you know, we can only accept contributions from those
persons who have signed the SCA (Sun Contributor's Agreement) and who
can grant rights to the code they are submitting.

[ Also, Anna needs to update her (forge.mysql.com / bugs.mysql.com) profile to ensure that the her e-mail is updated to the one she is now using. Otherwise, it is difficult to know what the contributor has posted where. ]
[20 Oct 2009 17:14] Sergei Golubchik
There is no uncertainty here.

This is Anna Widenius' contribution, and it was sent from the email address she specified when signing the SCA.
[20 Oct 2009 18:37] Masood Mortazavi
Hi Sergei -

It has to be Anna Widenius who needs to confirm that this is indeed *her* contribution because the code she had attached seems to be pointing to a committed patch by Sergey Petrunya. (See Anna's original e-mail on internals and my response to it earlier today.)

As a contributor under SCA, Anna needs to update her profile on mysql.com so that it is clear that her profile is associated with the e-mail alias she is using. (A contributor's participation on the MySQL forums has to be transparent.)
[22 Oct 2009 10:46] Bugs System
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/87762

3659 Evgeny Potemkin	2009-10-22
      BUG#42742: crash in setup_sj_materialization, Copy_field::set
      - If a semi-join strategy covers certain [first_table; last_table] 
        range in join order, do reset the sj_strategy member for all tables
        within the range, except the first one.
        Failure to do so caused EXPLAIN/execution code to try applying two 
        strategies at once which would cause all kinds of undesired effects. 
     @ mysql-test/r/subselect_sj2.result
        Added a test case for the bug#42742.
     @ mysql-test/r/subselect_sj2_jcl6.result
        Added a test case for the bug#42742.
     @ mysql-test/t/subselect_sj2.test
        Added a test case for the bug#42742.
     @ sql/sql_select.cc
        BUG#42742: crash in setup_sj_materialization, Copy_field::set.
[3 Nov 2009 13:04] Bugs System
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/89131

3695 Evgeny Potemkin	2009-11-03 [merge]
      Auto-merged fix for the bug#42742.
[20 Nov 2009 12:57] Bugs System
Pushed into 6.0.14-alpha (revid:kostja@sun.com-20091120124947-yi6h2jbgw0kbciwm) (version source revid:epotemkin@mysql.com-20091103130325-gl3md51912ifo95e) (merge vers: 6.0.14-alpha) (pib:13)
[10 Dec 2009 15:39] Paul DuBois
Noted in 6.0.14 changelog.

Use of semijoin optimization could cause a server crash. Thanks to
Sergey Petrunya for the patch to fix this bug.
[8 May 2010 14:48] Guilhem Bichot
backported to next-mr-opt-backporting guilhem@mysql.com-20100507185857-66cll4pe3eqol2at
[16 Aug 2010 6:35] Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100816062819-bluwgdq8q4xysmlg) (version source revid:alik@sun.com-20100816062612-enatdwnv809iw3s9) (pib:20)
[13 Nov 2010 16:24] Bugs System
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)
[16 Nov 2010 3:57] Paul DuBois
Noted in 5.6.1 changelog.
[8 Apr 2011 14:57] Paul DuBois
Correction. Bug does not affect 5.6.x users. No changelog entry needed.