| Bug #41842 | Semi-join materialization strategy crashes when the upper query has HAVING | ||
|---|---|---|---|
| Submitted: | 3 Jan 2009 18:29 | Modified: | 22 Nov 2010 1:41 |
| Reporter: | Sergey Petrunya | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S3 (Non-critical) |
| Version: | 6.0-bzr | OS: | Any |
| Assigned to: | Sergey Petrunya | CPU Architecture: | Any |
| Tags: | randgen | ||
[3 Jan 2009 18:30]
Sergey Petrunya
Dataset
Attachment: bug41842.sql (, text), 4.02 KiB.
[3 Jan 2009 19:00]
Sergey Petrunya
Analysis -------- The problem is caused by the intersection of two properties 1. setup_sj_materialization() expects that equality propagation has created an Item_equal for the IN-equality and has put it into join->cond_equal. 2. optimize_cond() replaces join->cond_equal whenever it is invoked. JOIN::optimize() calls it for the WHERE clause, and then for HAVING. That is, if the query has HAVING, then setup_sj_materialization() will not be able to find the IN-equality in join->cond_equal (as it will have HAVING's equalities) and crash. This means three things: 1. Need to check what exactly is the purpose of putting HAVING's equalities into join->cond_equal. 2. Need to check if we want to have the dependency that every IN-equality must be usable for equality propagation. Probably, we don't 3. if the IN-equality *was* usable by equality propagation, then setup_sj_materialization does need to find the equality set. This is because it will need to unpack materialized columns into their original tblname.column_name, or, more precisely, into the first equality propagation-sibling of tblname.column_name, because this is where all conditions will refer to (see substitute_for_best_equal).
[25 Jan 2009 15:07]
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/63999 2808 Sergey Petrunia 2009-01-25 BUG#41842: Semi-join materialization strategy crashes when the upper query has HAVING - Make optimize_cond() not to do build_equal_items() for HAVING clause - Make setup_sj_materialization(), SJM-Scan setup code correctly pick an Item_equal element that sjm-scan should unpack values to.
[28 Jan 2009 17: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/64357 2817 Sergey Petrunia 2009-01-28 BUG#41842: Semi-join materialization strategy crashes when the upper query has HAVING - Make optimize_cond() not to do build_equal_items() for HAVING clause - Make setup_sj_materialization(), SJM-Scan setup code correctly pick an Item_equal element that sjm-scan should unpack values to.
[28 Jan 2009 19:25]
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/64378 2817 Sergey Petrunia 2009-01-28 BUG#41842: Semi-join materialization strategy crashes when the upper query has HAVING - Make optimize_cond() not to do build_equal_items() for HAVING clause - Make setup_sj_materialization(), SJM-Scan setup code correctly pick an Item_equal element that sjm-scan should unpack values to.
[2 Feb 2009 16:06]
Bugs System
Pushed into 6.0.10-alpha (revid:sergefp@mysql.com-20090202090240-dlkxhmc1asrar5rl) (version source revid:sergefp@mysql.com-20090128192454-ci4i10wrw69kwlv1) (merge vers: 6.0.10-alpha) (pib:6)
[11 Feb 2009 3:00]
Paul DuBois
Noted in 6.0.10 changelog. Queries executed using semi-join materialization could cause a crash if the outer query has a HAVING clause.
[24 Mar 2010 12:07]
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/104193 2854 Sergey Glukhov 2010-03-24 Bug#48971 Segfault in add_found_match_trig_cond () at sql_select.cc:5990 In case of simultaneous presence of WHERE and HAVING conds optimize_cond(and in their turn build_equal_items) is called twice. Second call may perform erroneous propagation of some expressions and further it may lead to crash. The fix is to make optimize_cond() not to do build_equal_items() for HAVING clause. Note: This fix is a partial backport of BUG#41842 patch. @ mysql-test/r/join.result test case @ mysql-test/t/join.test test case @ sql/sql_select.cc The fix is to make optimize_cond() not to do build_equal_items() for HAVING clause.
[16 Aug 2010 6:33]
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:19]
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)
[22 Nov 2010 1:41]
Paul DuBois
Bug is not in any released 5.6.x version. No changelog entry needed.
Description: Randgen has found the following crash: the query SELECT OUTR . `datetime_nokey` AS X FROM C AS OUTR WHERE OUTR . `varchar_nokey` IN (SELECT INNR . `varchar_nokey` AS Y FROM BB AS INNR WHERE INNR . `datetime_key` >= INNR . `time_key` OR INNR . `pk` = INNR . `int_nokey` ) AND OUTR . `varchar_nokey` <= 'w' HAVING X > 'r' crashes like this: Program received signal SIGSEGV, Segmentation fault. 0x083e0395 in setup_sj_materialization (tab=0x9f24760) at sql_select.cc:9709 (gdb) print item_eq $8 = (class Item_equal *) 0x0 (gdb) wher #0 0x083e0395 in setup_sj_materialization (tab=0x9f24760) at sql_select.cc:9709 #1 0x083effc3 in make_join_readinfo (join=0x9f41fd0, options=0, no_jbuf_after=2) at sql_select.cc:9996 #2 0x083f23f5 in JOIN::optimize (this=0x9f41fd0) at sql_select.cc:1918 #3 0x083f520d in mysql_select (thd=0x9e47e20, rref_pointer_array=0x9e494c8, tables=0x9eedcf0, wild_num=0, fields=@0x9e49458, conds=0x9eef4c8, og_num=2, order=0x9eef840, group=0x0, having=0x9eef678, proc_param=0x0, select_options=2147764737, result=0x9f23110, unit=0x9e4912c, select_lex=0x9e493c4) at sql_select.cc:3033 #4 0x083faa10 in handle_select (thd=0x9e47e20, lex=0x9e490d0, result=0x9f23110, setup_tables_done_option=0) at sql_select.cc:314 #5 0x0835f0fd in execute_sqlcom_select (thd=0x9e47e20, all_tables=0x9eedcf0) at sql_parse.cc:4747 #6 0x08360171 in mysql_execute_command (thd=0x9e47e20) at sql_parse.cc:2062 #7 0x08368873 in mysql_parse (thd=0x9e47e20, inBuf=0x9eed8d0 "SELECT DISTINCT OUTR . `datetime_nokey` AS X FROM C AS OUTR WHERE OUTR . `varchar_nokey` IN ( SELECT DISTINCT INNR . `varchar_nokey` AS Y FROM BB AS INNR WHERE INNR . `datetime_key` >= INNR . `time_ke"..., length=332, found_semicolon=0xaa4ece70) at sql_parse.cc:5735 The crash goes away if one removes the HAVING clause. How to repeat: See the query above, will attach the dataset.