| Bug #49630 | Segfault in select_describe() with double nested subquery and materialization | ||
|---|---|---|---|
| Submitted: | 11 Dec 2009 16:30 | Modified: | 23 Nov 2010 3:09 |
| Reporter: | Patrick Crews | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S3 (Non-critical) |
| Version: | 6.0 | OS: | Any |
| Assigned to: | Jørgen Løland | CPU Architecture: | Any |
| Tags: | materialization, optimizer_switch, semijoin | ||
[11 Dec 2009 16:32]
Patrick Crews
Full crash output
Attachment: bug49630_crash_output.txt (text/plain), 12.45 KiB.
[8 Jan 2010 10:28]
Jørgen Løland
Simplified test case:
---------------------
CREATE TABLE t1 (
t1i int
);
INSERT INTO t1 VALUES (1);
CREATE TABLE t2 (
t2i int
);
INSERT INTO t2 VALUES (2);
INSERT INTO t2 VALUES (3);
CREATE TABLE t3 (
t3i int
);
INSERT INTO t3 VALUES (4);
EXPLAIN
SELECT t1i
FROM t1 JOIN t3 ON t1i=t3i
WHERE (1) IN (
SELECT t1i
FROM t1
WHERE (t1i) IN (
SELECT t2i
FROM t2
GROUP BY t2i)
);
[24 Feb 2010 17:57]
MySQL Verification Team
After a full review procedure, I fully endorse Jørgen's patch. It is only possible that some of the old tests has to be changed, which I did not check.
[4 Mar 2010 9:59]
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/102261 2995 Jorgen Loland 2010-03-04 Bug#49630 "Segfault in select_describe() with double nested subquery and materialization" If a JOIN contains const tables, make_join_select() will evaluate the conditions in the WHERE clause. If the WHERE clause contains a subquery, the process of evaluating the condition involves optimize() and exec(). Calling optimize() and exec() on a subselect may require the use of temp tables, in which case the original JOIN query execution plan will be replaced by a simple scan of the temp table. To be able to describe the query plan, the original query layout needs to be saved. This was not done for materialized subqueries. The fix is to make materialized subqueries save the original join query layout if it is needed by EXPLAIN. @ mysql-test/r/subselect4.result Added test for BUG#49630 @ mysql-test/t/subselect4.test Added test for BUG#49630 @ sql/item_subselect.cc Make subselect_hash_sj_engine::exec save JOIN layout if needed by explain. Added function subselect_single_select_engine::save_join_if_explain() with code common to single_select_engine and hash_sj_engine @ sql/item_subselect.h Add function subselect_single_select_engine::save_join_if_explain() that saves the JOIN layout if needed by explain
[15 Mar 2010 7:52]
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/103175 3821 Jorgen Loland 2010-03-15 Bug#49630 "Segfault in select_describe() with double nested subquery and materialization" If a JOIN contains const tables, make_join_select() will evaluate the conditions in the WHERE clause. If the WHERE clause contains a subquery, the process of evaluating the condition involves optimize() and exec(). Calling optimize() and exec() on a subselect may require the use of temp tables, in which case the original JOIN query execution plan will be replaced by a simple scan of the temp table. To be able to describe the query plan, the original query layout needs to be saved. This was not done for materialized subqueries. The fix is to make materialized subqueries save the original join query layout if it is needed by EXPLAIN. @ mysql-test/r/subselect4.result Added test for BUG#49630 @ mysql-test/t/subselect4.test Added test for BUG#49630 @ sql/item_subselect.cc Make subselect_hash_sj_engine::exec save JOIN layout if needed by explain. Added function subselect_single_select_engine::save_join_if_explain() with code common to single_select_engine and hash_sj_engine @ sql/item_subselect.h Add function subselect_single_select_engine::save_join_if_explain() that saves the JOIN layout if needed by explain
[15 Mar 2010 7:53]
Jørgen Løland
Pushed to 6.0-codebase-bugfixing
[24 Mar 2010 8:14]
Bugs System
Pushed into 6.0.14-alpha (revid:alik@sun.com-20100324081249-yfwol7qtcek6dh7w) (version source revid:alik@sun.com-20100324081113-kc7x1iytnplww91u) (merge vers: 6.0.14-alpha) (pib:16)
[7 Apr 2010 12:05]
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/105152 3069 oystein.grovlen@sun.com 2010-04-07 Bug#49630 "Segfault in select_describe() with double nested subquery and materialization" (Backporting of revid:jorgen.loland@sun.com-20100315075225-rcwlxcsuuidwnwzi) If a JOIN contains const tables, make_join_select() will evaluate the conditions in the WHERE clause. If the WHERE clause contains a subquery, the process of evaluating the condition involves optimize() and exec(). Calling optimize() and exec() on a subselect may require the use of temp tables, in which case the original JOIN query execution plan will be replaced by a simple scan of the temp table. To be able to describe the query plan, the original query layout needs to be saved. This was not done for materialized subqueries. The fix is to make materialized subqueries save the original join query layout if it is needed by EXPLAIN. @ mysql-test/r/subselect4.result Added test for BUG#49630 @ mysql-test/t/subselect4.test Added test for BUG#49630 @ sql/item_subselect.cc Make subselect_hash_sj_engine::exec save JOIN layout if needed by explain. Added function subselect_single_select_engine::save_join_if_explain() with code common to single_select_engine and hash_sj_engine @ sql/item_subselect.h Add function subselect_single_select_engine::save_join_if_explain() that saves the JOIN layout if needed by explain
[26 Apr 2010 14:21]
Paul DuBois
Noted in 6.0.14 changelog. EXPLAIN for queries with subqueries evaluated using materialization (with the result stored in a temporary table) caused a server crash.
[16 Aug 2010 6:31]
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:10]
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)
[23 Nov 2010 3:09]
Paul DuBois
Bug does not appear in any released 5.6.x version. No 5.6.1 changelog entry needed.
Description: Crashing bug in 6.0 with materialization *and* semijoin on. Turning off either optimizer_switch value to 'off' will remove the bug. Not present in 5.1 From attached test case: This query: EXPLAIN SELECT table2 .`col_datetime_key` FROM C table1 JOIN CC table2 ON table1 .`col_varchar_key` WHERE ( 'i' , 'b' ) IN ( SELECT `col_varchar_nokey` , `col_varchar_key` FROM CC WHERE ( `col_varchar_key` , `col_varchar_key` ) IN ( SELECT `col_varchar_nokey` CHILD_SUBQUERY1_field1 , `col_varchar_nokey` FROM D GROUP BY child_subquery1_field1 ) ) ; Produces this crash output: (full output attached separately) Thread 1 (Thread 4491): #0 0x00f2d422 in __kernel_vsyscall () #1 0x00516e93 in __pthread_kill (threadid=3069376368, signo=11) at ../nptl/sysdeps/unix/sysv/linux/pthread_kill.c:64 #2 0x08ca1f17 in my_write_core (sig=11) at stacktrace.c:309 #3 0x08403b76 in handle_segfault (sig=11) at mysqld.cc:2765 #4 <signal handler called> #5 0x085714fe in select_describe (join=0xa148600, need_tmp_table=true, need_order=true, distinct=false, message=0x0) at sql_select.cc:21805 #6 0x08518c3e in JOIN::exec (this=0xa148600) at sql_select.cc:2483 #7 0x0851c8a0 in mysql_select (thd=0xa096be0, rref_pointer_array=0xa061e24, tables=0xa062440, wild_num=0, fields=..., conds=0x0, og_num=1, order=0x0, group=0xa062920, having=0x0, proc_param=0x0, select_options=2147748356, result=0xa062ba0, unit=0xa061eb0, select_lex=0xa061d20) at sql_select.cc:3135 #8 0x08575500 in mysql_explain_union (thd=0xa096be0, unit=0xa061eb0, result=0xa062ba0) at sql_select.cc:22230 #9 0x08574db4 in select_describe (join=0xa1437a0, need_tmp_table=false, need_order=false, distinct=false, message=0x0) at sql_select.cc:22171 #10 0x08518c3e in JOIN::exec (this=0xa1437a0) at sql_select.cc:2483 #11 0x0851c8a0 in mysql_select (thd=0xa096be0, rref_pointer_array=0xa060dcc, tables=0xa061900, wild_num=0, fields=..., conds=0xa14db58, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147748356, result=0xa062ba0, unit=0xa060e60, select_lex=0xa060cc8) at sql_select.cc:3135 #12 0x08575500 in mysql_explain_union (thd=0xa096be0, unit=0xa060e60, result=0xa062ba0) at sql_select.cc:22230 #13 0x08574db4 in select_describe (join=0xa13e940, need_tmp_table=false, need_order=false, distinct=false, message=0x8fb3a13 "Impossible WHERE") at sql_select.cc:22171 #14 0x0853eee0 in return_zero_rows (join=0xa13e940, result=0xa062ba0, tables=0xa05fd38, fields=..., send_row=false, select_options=2147748356, info=0x8fb3a13 "Impossible WHERE", having=0x0) at sql_select.cc:11029 #15 0x085186b9 in JOIN::exec (this=0xa13e940) at sql_select.cc:2445 #16 0x0851c8a0 in mysql_select (thd=0xa096be0, rref_pointer_array=0xa098144, tables=0xa05fd38, wild_num=0, fields=..., conds=0xa062a40, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147748356, result=0xa062ba0, unit=0xa097b88, select_lex=0xa098040) at sql_select.cc:3135 #17 0x08575500 in mysql_explain_union (thd=0xa096be0, unit=0xa097b88, result=0xa062ba0) at sql_select.cc:22230 #18 0x0843a11e in execute_sqlcom_select (thd=0xa096be0, all_tables=0xa05fd38) at sql_parse.cc:4939 #19 0x08428960 in mysql_execute_command (thd=0xa096be0) at sql_parse.cc:2156 #20 0x0843e792 in mysql_parse (thd=0xa096be0, inBuf=0xa05f8d0 "EXPLAIN \nSELECT table2 .`col_datetime_key` \nFROM C table1 JOIN CC table2 ON table1 .`col_varchar_key` \nWHERE ( 'i' , 'b' ) IN ( \nSELECT `col_varchar_nokey` , `col_varchar_key` \nFROM CC \nWHER"..., length=376, found_semicolon=0xb6f2e98c) at sql_parse.cc:5975 #21 0x08423aef in dispatch_command (command=COM_QUERY, thd=0xa096be0, packet=0xa065ad1 "", packet_length=379) at sql_parse.cc:1076 #22 0x08422313 in do_command (thd=0xa096be0) at sql_parse.cc:758 #23 0x0841f884 in handle_one_connection (arg=0xa096be0) at sql_connect.cc:1164 #24 0x0051180e in start_thread (arg=0xb6f2f770) at pthread_create.c:300 #25 0x001fe7ee in clone () at ../sysdeps/unix/sysv/linux/i386/clone.S:130 How to repeat: MTR test case. Toggle the materialization and semijoin switches to verify the bug will vanish if either one is set to 'off' - crash only occurs with both on. #/* Server0: MySQL 6.0.14-alpha-gcov-debug-log */ /*!50400 SET SESSION optimizer_switch = 'firstmatch=off,index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,loosescan=off,materialization=on,semijoin=on' */; /*!50400 SET SESSION optimizer_use_mrr = 'force' */; /*!50400 SET SESSION engine_condition_pushdown = 'ON' */; /*!50400 SET SESSION join_cache_level = 1 */; /*!50400 SET SESSION debug = '' */; #/* Begin test case for query 0 */ --disable_warnings DROP TABLE /*! IF EXISTS */ CC; DROP TABLE /*! IF EXISTS */ D; DROP TABLE /*! IF EXISTS */ C; --enable_warnings CREATE TABLE `CC` ( `col_datetime_key` datetime DEFAULT NULL, `col_varchar_key` varchar(1) DEFAULT NULL, `col_varchar_nokey` varchar(1) DEFAULT NULL, KEY `col_datetime_key` (`col_datetime_key`), KEY `col_varchar_key` (`col_varchar_key`) ) DEFAULT CHARSET=latin1; INSERT INTO `CC` VALUES (NULL,'e','e'); INSERT INTO `CC` VALUES ('2005-11-10 12:40:29','h','h'); INSERT INTO `CC` VALUES ('2009-04-25 00:00:00','b','b'); CREATE TABLE `D` ( `col_datetime_key` datetime DEFAULT NULL, `col_varchar_key` varchar(1) DEFAULT NULL, `col_varchar_nokey` varchar(1) DEFAULT NULL, KEY `col_datetime_key` (`col_datetime_key`), KEY `col_varchar_key` (`col_varchar_key`) ) DEFAULT CHARSET=latin1; INSERT INTO `D` VALUES ('2001-09-01 00:00:00','a','a'); INSERT INTO `D` VALUES ('2002-05-27 18:38:45','r','r'); CREATE TABLE `C` ( `col_datetime_key` datetime DEFAULT NULL, `col_varchar_key` varchar(1) DEFAULT NULL, `col_varchar_nokey` varchar(1) DEFAULT NULL, KEY `col_datetime_key` (`col_datetime_key`), KEY `col_varchar_key` (`col_varchar_key`) ) DEFAULT CHARSET=latin1; INSERT INTO `C` VALUES ('2005-08-15 12:39:41','k','k'); EXPLAIN SELECT table2 .`col_datetime_key` FROM C table1 JOIN CC table2 ON table1 .`col_varchar_key` WHERE ( 'i' , 'b' ) IN ( SELECT `col_varchar_nokey` , `col_varchar_key` FROM CC WHERE ( `col_varchar_key` , `col_varchar_key` ) IN ( SELECT `col_varchar_nokey` CHILD_SUBQUERY1_field1 , `col_varchar_nokey` FROM D GROUP BY child_subquery1_field1 ) ) ; DROP TABLE CC; DROP TABLE D; DROP TABLE C; #/* End of test case for query 0 */