Bug #51489 Toggling semijoin optimizer_switch makes server crash
Submitted: 25 Feb 2010 8:34 Modified: 27 Jan 2011 12:38
Reporter: Tor Didriksen Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:6.0 OS:Any
Assigned to: Roy Lyseng CPU Architecture:Any
Tags: optimizer_switch, semijoin

[25 Feb 2010 8:34] Tor Didriksen 
Description:
The semijoin transformation is permanent, which leads to numerous problems for
prepared statements and stored procedures.
Toggling on/off semijoin leads to unpredictable/wrong results and/or server crash.
The fix for bug#46744 was mostly a quick hack to fix one instance of the problem.

How to repeat:
CREATE TABLE t1 ( f1 int );
CREATE TABLE t2 ( f1 int );

insert into t2 values (5), (7);

CREATE OR REPLACE VIEW v1 AS SELECT * FROM t1 LIMIT 2;

create procedure p1() 
select COUNT(*) 
FROM v1 WHERE f1 IN 
(SELECT f1 FROM t2 WHERE f1 = ANY (SELECT f1 FROM v1));

SET SESSION optimizer_switch = 'semijoin=on';
CALL p1();
SET SESSION optimizer_switch = 'semijoin=off';
CALL p1();
SET SESSION optimizer_switch = 'semijoin=on';
CALL p1();

drop table t1, t2;
drop view v1;
drop procedure p1;

Suggested fix:
 - make optimizer settings 'sticky' for prepared statements?
   (i.e. use setting from prepare time, when executing)
or
 - invalidate all prepared statements if optimizer settings change?
[2 May 2010 20:40] Sveta Smirnova 
Thank you for the report.

Crash verified as described:

/users/ssmirnova/blade12/src/mysql-6.0-codebase-bugfixing/sql/mysqld(_ZN4JOIN8optimizeEv+0xae2)[0x618c44]
/users/ssmirnova/blade12/src/mysql-6.0-codebase-bugfixing/sql/mysqld(_Z12mysql_selectP3THDPPP4ItemP10TABLE_LISTjR4ListIS1_ES2_jP8st_orderSB_S2_SB_yP13select_resultP18st_select_lex_unitP13st_select_lex+0x2
63)[0x61e46d]
/users/ssmirnova/blade12/src/mysql-6.0-codebase-bugfixing/sql/mysqld(_Z13handle_selectP3THDP3LEXP13select_resultm+0x1a4)[0x6155d5]
/users/ssmirnova/blade12/src/mysql-6.0-codebase-bugfixing/sql/mysqld[0x5f2577]
/users/ssmirnova/blade12/src/mysql-6.0-codebase-bugfixing/sql/mysqld(_Z21mysql_execute_commandP3THD+0xa0e)[0x5ea446]
/users/ssmirnova/blade12/src/mysql-6.0-codebase-bugfixing/sql/mysqld(_ZN13sp_instr_stmt9exec_coreEP3THDPj+0x20)[0x888036]
/users/ssmirnova/blade12/src/mysql-6.0-codebase-bugfixing/sql/mysqld(_ZN13sp_lex_keeper23reset_lex_and_exec_coreEP3THDPjbP8sp_instr+0x18f)[0x887997]
/users/ssmirnova/blade12/src/mysql-6.0-codebase-bugfixing/sql/mysqld(_ZN13sp_instr_stmt7executeEP3THDPj+0x1c9)[0x887e0b]
/users/ssmirnova/blade12/src/mysql-6.0-codebase-bugfixing/sql/mysqld(_ZN7sp_head7executeEP3THD+0x614)[0x883fd0]
/users/ssmirnova/blade12/src/mysql-6.0-codebase-bugfixing/sql/mysqld(_ZN7sp_head17execute_procedureEP3THDP4ListI4ItemE+0x73a)[0x885c0e]
/users/ssmirnova/blade12/src/mysql-6.0-codebase-bugfixing/sql/mysqld(_Z21mysql_execute_commandP3THD+0x71a5)[0x5f0bdd]
/users/ssmirnova/blade12%2
[2 May 2010 20:41] Sveta Smirnova 
Thank you for the report.

Crash verified as described:

/users/ssmirnova/blade12/src/mysql-6.0-codebase-bugfixing/sql/mysqld(_ZN4JOIN8optimizeEv+0xae2)[0x618c44]
/users/ssmirnova/blade12/src/mysql-6.0-codebase-bugfixing/sql/mysqld(_Z12mysql_selectP3THDPPP4ItemP10TABLE_LISTjR4ListIS1_ES2_jP8st_orderSB_S2_SB_yP13select_resultP18st_select_lex_unitP13st_select_lex+0x2
63)[0x61e46d]
/users/ssmirnova/blade12/src/mysql-6.0-codebase-bugfixing/sql/mysqld(_Z13handle_selectP3THDP3LEXP13select_resultm+0x1a4)[0x6155d5]
/users/ssmirnova/blade12/src/mysql-6.0-codebase-bugfixing/sql/mysqld[0x5f2577]
/users/ssmirnova/blade12/src/mysql-6.0-codebase-bugfixing/sql/mysqld(_Z21mysql_execute_commandP3THD+0xa0e)[0x5ea446]
/users/ssmirnova/blade12/src/mysql-6.0-codebase-bugfixing/sql/mysqld(_ZN13sp_instr_stmt9exec_coreEP3THDPj+0x20)[0x888036]
/users/ssmirnova/blade12/src/mysql-6.0-codebase-bugfixing/sql/mysqld(_ZN13sp_lex_keeper23reset_lex_and_exec_coreEP3THDPjbP8sp_instr+0x18f)[0x887997]
/users/ssmirnova/blade12/src/mysql-6.0-codebase-bugfixing/sql/mysqld(_ZN13sp_instr_stmt7executeEP3THDPj+0x1c9)[0x887e0b]
/users/ssmirnova/blade12/src/mysql-6.0-codebase-bugfixing/sql/mysqld(_ZN7sp_head7executeEP3THD+0x614)[0x883fd0]
/users/ssmirnova/blade12/src/mysql-6.0-codebase-bugfixing/sql/mysqld(_ZN7sp_head17execute_procedureEP3THDP4ListI4ItemE+0x73a)[0x885c0e]
/users/ssmirnova/blade12/src/mysql-6.0-codebase-bugfixing/sql/mysqld(_Z21mysql_execute_commandP3THD+0x71a5)[0x5f0bdd]
/users/ssmirnova/blade12%2
[2 May 2010 20:41] Sveta Smirnova 
Thank you for the report.

Crash verified as described:

/users/ssmirnova/blade12/src/mysql-6.0-codebase-bugfixing/sql/mysqld(_ZN4JOIN8optimizeEv+0xae2)[0x618c44]
/users/ssmirnova/blade12/src/mysql-6.0-codebase-bugfixing/sql/mysqld(_Z12mysql_selectP3THDPPP4ItemP10TABLE_LISTjR4ListIS1_ES2_jP8st_orderSB_S2_SB_yP13select_resultP18st_select_lex_unitP13st_select_lex+0x2
63)[0x61e46d]
/users/ssmirnova/blade12/src/mysql-6.0-codebase-bugfixing/sql/mysqld(_Z13handle_selectP3THDP3LEXP13select_resultm+0x1a4)[0x6155d5]
/users/ssmirnova/blade12/src/mysql-6.0-codebase-bugfixing/sql/mysqld[0x5f2577]
/users/ssmirnova/blade12/src/mysql-6.0-codebase-bugfixing/sql/mysqld(_Z21mysql_execute_commandP3THD+0xa0e)[0x5ea446]
/users/ssmirnova/blade12/src/mysql-6.0-codebase-bugfixing/sql/mysqld(_ZN13sp_instr_stmt9exec_coreEP3THDPj+0x20)[0x888036]
/users/ssmirnova/blade12/src/mysql-6.0-codebase-bugfixing/sql/mysqld(_ZN13sp_lex_keeper23reset_lex_and_exec_coreEP3THDPjbP8sp_instr+0x18f)[0x887997]
/users/ssmirnova/blade12/src/mysql-6.0-codebase-bugfixing/sql/mysqld(_ZN13sp_instr_stmt7executeEP3THDPj+0x1c9)[0x887e0b]
/users/ssmirnova/blade12/src/mysql-6.0-codebase-bugfixing/sql/mysqld(_ZN7sp_head7executeEP3THD+0x614)[0x883fd0]
/users/ssmirnova/blade12/src/mysql-6.0-codebase-bugfixing/sql/mysqld(_ZN7sp_head17execute_procedureEP3THDP4ListI4ItemE+0x73a)[0x885c0e]
/users/ssmirnova/blade12/src/mysql-6.0-codebase-bugfixing/sql/mysqld(_Z21mysql_execute_commandP3THD+0x71a5)[0x5f0bdd]
/users/ssmirnova/blade12%2
[3 May 2010 7:45] Guilhem Bichot 
another possible solution: in a stored function, changing the binary log's format is forbidden (you get an error). So here we could apply the same thing: in the function which sets @@optimizer_switch, do something like:
if (this connection has prepared statements) throw error.
[27 Jan 2011 12:38] Roy Lyseng 
Duplicate of bug#43768. Remember to add reproduction case to bug fix.