Bug #46692 | Crash occurring on query with subquery using STRAIGHT_JOIN | ||
---|---|---|---|
Submitted: | 13 Aug 2009 11:45 | Modified: | 23 Nov 2010 2:56 |
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: | Øystein Grøvlen | CPU Architecture: | Any |
Tags: | crashing bug, from, materialization, optimizer_switch, subquery |
[13 Aug 2009 11:45]
Patrick Crews
[13 Aug 2009 11:47]
Patrick Crews
Full rqg-generated crash output - backtrace, etc
Attachment: bug46692_crash_output.txt (text/plain), 17.70 KiB.
[12 Oct 2009 10:52]
Øystein Grøvlen
Example query still core dumps on latest version of mysql-6.0-bugfixing
[14 Oct 2009 14:58]
Øystein Grøvlen
The subquery does not need to be in the FROM clause. The following query crash in the same way: SELECT * FROM CC WHERE (140, 4) IN (SELECT CHILD_SUBQUERY1_t1.int_key, CHILD_SUBQUERY1_t1.pk FROM B CHILD_SUBQUERY1_t1 STRAIGHT_JOIN CC ON CHILD_SUBQUERY1_t1.`int_key`);
[26 Oct 2009 11:08]
Øystein Grøvlen
The test case can be simplified to: CREATE TABLE t1 (i integer); SELECT * FROM t1 WHERE (1) IN (SELECT a.i FROM t1 a STRAIGHT_JOIN t1 b); The difference between success and failure is whether JOIN or STRAIGHT_JOIN is specified. (No practical difference since one is joining a table with itself.) From inspecting the core dump, it seems join->best_positions have not been set in this case. Hence the following code causes a segmentation fault: JOIN_TAB *tab= join->best_positions[i].table; join->map2table[tab->table->tablenr]= tab;
[6 Nov 2009 15:46]
Øystein Grøvlen
The problem appears to be related to const tables. The following query: select * from t1 where (11) in (select t2.i from t2 straight_join t3) fails with segmentation fault unless there is at least two rows in t2 and at least one row in t3. From tracing the code in the debugger it seems the explanation is as follows: * If there is 0 or 1 row in t2, t2 is a const table, and it will be pulled out of the semi-join. The way it currently works, t3 will never be pulled out due to the straight_join. * IF t2 is pulled out, t3 will be the only table in the semijoin-nest. It seems cost estimation fails to come up with a result if there is zero rows in that table. As far as I can tell, the problem is related to a fanout of 0 which is used as a divisor when computing current_record_count (see advance_sj_state(), the FirstMatch stratefy part.)
[13 Nov 2009 10:46]
Øystein Grøvlen
I started a discussion on internals mailing list about table pull-out and straight join; see http://lists.mysql.com/internals/37479. Note that with the query given in the previous comment, pullling out t3 when possible would fix the problems in some scenarios, but not all. If t2 is not a const table, t3 should not be pulled out anyway because of the straight_join requirement. Then, if t3 is empty, you will still run into this bug.
[13 Nov 2009 11:02]
Øystein Grøvlen
It seems the combination of the following two changes, fix the issue: 1. Do not include const tables in the set of tables to be considered by optimize_semijon_nests(). This will make sure cost estimation is not skipped for tables that depend on const tables. (This is consistent with what is done for top level joins where const tables are filtered out from the set out tables considered by choose_plan.) 2. Currently, when the table that is added to the join prefix, is empty, fanout will become zero since it is the product of the number of records of all tables. A zero fanout will cause the computed cost to become infinite. Hence, in order to get a valid cost, we need to skip empty tables when computing the fanout.
[17 Nov 2009 10:19]
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/90637 3712 oystein.grovlen@sun.com 2009-11-17 Bug#46692 Crash occurring on queries with nested FROM subqueries using materialization Subqueries with STRAIGHT_JOIN prevents pull-out of tables that would normally be pulled out of the subquery. This created scenarioes which the plan search was not prepared for, and one ended up without any valid plan. This caused segmentation fault in optimize_semijoin_nests when trying to access the best plan. The problem may occur with either const tables or eq_ref relations that does not have any matches. Bug#46692 reports the problem for FROM subqueries, but ordinary IN subqueries will have the same issue. The following changes are needed to handle this problem: 1. Do not include const tables in the set of tables to be considered by optimize_semijon_nests(). This will make sure cost estimation is not skipped for tables that depend on const tables. (This is consistent with what is done for top level joins where const tables are filtered out from the set out tables considered by choose_plan.) 2. Currently, when the table that is added to the join prefix, is empty, fanout will become zero since it is the product of the number of records of all tables. A zero fanout will cause the computed cost to become infinite. Hence, in order to get a valid cost, we need to skip empty tables when computing the fanout. The fanout issue (issue 2 above) is also present with the duplicate weedout strategy, and this patch contains the necessary changes to handle fanout in this scenario, too. @ mysql-test/r/subselect3.result Updated results for test for subqueries with STRAIGHT_JOIN and different permutations of const and non-const tables. @ mysql-test/r/subselect3_jcl6.result Updated results for test for subqueries with STRAIGHT_JOIN and different permutations of const and non-const tables. @ mysql-test/r/subselect4.result Updated with results for Bug#46692 test case. @ mysql-test/t/subselect3.test Add test cases to check that subqueries with STRAIGHT_JOIN work for different permutations of const and non-const tables. Similar checking is also done for the duplicate weedout strategy (by turning off FirstMatch and Materialization). @ mysql-test/t/subselect4.test Added a test case that is similar to the query reported in Bug#46692. @ sql/sql_select.cc 1. Do not include const tables in the set of tables to be considered by optimize_semijon_nests(). This will make sure cost estimation is not skipped for tables that depend on const tables. (This is consistent with what is done for top level joins where const tables are filtered out from the set out tables considered by choose_plan.) 2. Make sure fanout is always >= 1 when computing plan cost. A zero fanout will cause the computed cost to become infinite. Hence, in order to get a valid cost, we need to skip empty tables when computing the fanout.
[18 Nov 2009 4:54]
Øystein Grøvlen
The following bug reports has been marked as duplicates of this bug: Bug#42353, Bug#43977, Bug#45219, Bug#45933. It has been verified that the committed fix, fixes the issues reported in these reports. Looking at the bug reports, it is evident that the issue fixed here is not only related to STRAIGHT_JOIN in the subquery. Also outer joins, both left and right, is affected. This can be explained with that outer joins will, like STRAIGHT_JOIN, introduce dependencies between tables and prevent pull-outs. I will update the patch with test cases for subqueries with outer join.
[18 Nov 2009 17:36]
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/90895 3717 oystein.grovlen@sun.com 2009-11-18 Bug#46692 Crash occurring on queries with nested FROM subqueries using materialization Subqueries with STRAIGHT_JOIN prevents pull-out of tables that would normally be pulled out of the subquery. This created scenarioes which the plan search was not prepared for, and one ended up without any valid plan. This caused segmentation fault in optimize_semijoin_nests when trying to access the best plan. The problem may occur with either const tables or eq_ref relations that does not have any matches. Bug#46692 reports the problem for FROM subqueries, but ordinary IN subqueries will have the same issue. The following changes are needed to handle this problem: 1. Do not include const tables in the set of tables to be considered by optimize_semijon_nests(). This will make sure cost estimation is not skipped for tables that depend on const tables. (This is consistent with what is done for top level joins where const tables are filtered out from the set out tables considered by choose_plan.) 2. Currently, when the table that is added to the join prefix, is empty, fanout will become zero since it is the product of the number of records of all tables. A zero fanout will cause the computed cost to become infinite. Hence, in order to get a valid cost, we need to skip empty tables when computing the fanout. The fanout issue (issue 2 above) is also present with the duplicate weedout strategy, and this patch contains the necessary changes to handle fanout in this scenario, too. A number of bug reports have been marked as duplicate of this bug report based on that this fix also fixes these issues. Those bug reports show that this is not just an issue for STRAIGHT_JOIN, but also for both right and left outer join. This patch also adds some test cases for these duplicate reports as well as general testing of outer joins in subqueries when constant tables are involved. @ mysql-test/r/subselect3.result Updated results for test for subqueries with STRAIGHT_JOIN and outer join for different permutations of const and non-const tables. @ mysql-test/r/subselect3_jcl6.result Updated results for test for subqueries with STRAIGHT_JOIN and outer join for different permutations of const and non-const tables. @ mysql-test/r/subselect4.result Updated with results for Bug#46692 test case and test cases for duplicate bug reports. @ mysql-test/t/subselect3.test Add test cases to check that subqueries with STRAIGHT_JOIN or outer join work for different permutations of const and non-const tables. Similar checking is also done for the duplicate weedout strategy (by turning off FirstMatch and Materialization). Note that due to a still existing bug, left outer join is not tested. @ mysql-test/t/subselect4.test Added a test case that is similar to the query reported in Bug#46692. Also adds a few test cases for duplicate bug reports. @ sql/sql_select.cc 1. Do not include const tables in the set of tables to be considered by optimize_semijon_nests(). This will make sure cost estimation is not skipped for tables that depend on const tables. (This is consistent with what is done for top level joins where const tables are filtered out from the set out tables considered by choose_plan.) 2. Make sure fanout is always >= 1 when computing plan cost. A zero fanout will cause the computed cost to become infinite. Hence, in order to get a valid cost, we need to skip empty tables when computing the fanout.
[30 Nov 2009 15:27]
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/92110 3738 oystein.grovlen@sun.com 2009-11-30 Bug#46692 Crash occurring on queries with nested FROM subqueries using materialization Subqueries with STRAIGHT_JOIN prevents pull-out of tables that would normally be pulled out of the subquery. This created scenarioes which the plan search was not prepared for, and one ended up without any valid plan. This caused segmentation fault in optimize_semijoin_nests when trying to access the best plan. The problem may occur with either const tables or eq_ref relations that does not have any matches. Bug#46692 reports the problem for FROM subqueries, but ordinary IN subqueries will have the same issue. The following changes are needed to handle this problem: 1. Do not include const tables in the set of tables to be considered by optimize_semijon_nests(). This will make sure cost estimation is not skipped for tables that depend on const tables. (This is consistent with what is done for top level joins where const tables are filtered out from the set out tables considered by choose_plan.) 2. Change computation of record count so that division by inner fanout is not needed. When the table that is added to the join prefix, is empty, fanout will become zero since it is the product of the number of records of all tables. When using division, a zero inner fanout caused the computed cost to become infinite. The fanout issue (issue 2 above) is also present with the duplicate weedout strategy, and this patch contains the necessary changes to avoid division in this scenario, too. A number of bug reports have been marked as duplicate of this bug report based on that this fix also fixes these issues. Those bug reports show that this is not just an issue for STRAIGHT_JOIN, but also for both right and left outer join. This patch also adds some test cases for these duplicate reports as well as general testing of outer joins in subqueries when constant tables are involved. @ mysql-test/r/subselect3.result Updated results for test for subqueries with STRAIGHT_JOIN and outer join for different permutations of const and non-const tables. @ mysql-test/r/subselect3_jcl6.result Updated results for test for subqueries with STRAIGHT_JOIN and outer join for different permutations of const and non-const tables. @ mysql-test/r/subselect4.result Updated with results for Bug#46692 test case and test cases for duplicate bug reports. @ mysql-test/t/subselect3.test Add test cases to check that subqueries with STRAIGHT_JOIN or outer join work for different permutations of const and non-const tables. Similar checking is also done for the duplicate weedout strategy (by turning off FirstMatch and Materialization). Note that due to a still existing bug, left outer join is not tested. @ mysql-test/t/subselect4.test Added a test case that is similar to the query reported in Bug#46692. Also adds a few test cases for duplicate bug reports. @ sql/sql_select.cc 1. Do not include const tables in the set of tables to be considered by choose_plan when called from in optimize_semijon_nests(). This will make sure cost estimation is not skipped for tables that depend on const tables. (This is consistent with what is done for top level joins where const tables are filtered out from the set out tables considered by choose_plan.) 2. Let the record count returned by optimize_wo_join_buffering() be the fanout for outer tables. This way, it is not necessary to divide by inner fanout which created problems when record count from inner tables was 0. 3. Change the current_record_count computation for duplicate weed-out so that division and potential problems associated with that are avoided.
[9 Dec 2009 7:44]
Tor Didriksen
See also Bug #46305 Wrong and variable result on simple JOIN + XOR + subquery optimizations
[16 Dec 2009 11: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/94456 3776 oystein.grovlen@sun.com 2009-12-16 Bug#46692 Crash occurring on queries with nested FROM subqueries using materialization Subqueries with STRAIGHT_JOIN prevents pull-out of tables that would normally be pulled out of the subquery. This created scenarioes which the plan search was not prepared for, and one ended up without any valid plan. This caused segmentation fault in optimize_semijoin_nests when trying to access the best plan. The problem may occur with either const tables or eq_ref relations that does not have any matches. Bug#46692 reports the problem for FROM subqueries, but ordinary IN subqueries will have the same issue. The following changes are needed to handle this problem: 1. Do not include const tables in the set of tables to be considered by optimize_semijon_nests(). This will make sure cost estimation is not skipped for tables that depend on const tables. (This is consistent with what is done for top level joins where const tables are filtered out from the set out tables considered by choose_plan.) 2. Change computation of record count so that division by inner fanout is not needed. When the table that is added to the join prefix, is empty, fanout will become zero since it is the product of the number of records of all tables. When using division, a zero inner fanout caused the computed cost to become infinite. The fanout issue (issue 2 above) is also present with the duplicate weedout strategy, and this patch contains the necessary changes to avoid division in this scenario, too. A number of bug reports have been marked as duplicate of this bug report based on that this fix also fixes these issues. Those bug reports show that this is not just an issue for STRAIGHT_JOIN, but also for both right and left outer join. Note that while this fix, fixes the issue for outer joins, a more proper fix would be to make sure that the dependent constant tables could be pulled out. This will be fixed by bug 49494. This patch also adds some test cases for these duplicate reports as well as general testing of outer joins in subqueries when constant tables are involved. @ mysql-test/r/subselect_sj.result Add results for new test cases. @ mysql-test/suite/optimizer_unfixed_bugs/r/bug45219.result Remove the test for bug 45219 from optimizer_unfixed_bugs test suite since this issue is fixed by this patch and a similar test case has been added to subselect_sj.test. @ mysql-test/suite/optimizer_unfixed_bugs/t/bug45219.test Remove the test for bug 45219 from optimizer_unfixed_bugs test suite since this issue is fixed by this patch and a similar test case has been added to subselect_sj.test. @ mysql-test/t/subselect_sj.test Add test cases to check that subqueries with STRAIGHT_JOIN or outer join work for different permutations of const and non-const tables. Similar checking is also done for the duplicate weedout strategy (by turning off FirstMatch and Materialization). Note that due to a still existing bug, left outer join is not tested. Added a test case that is similar to the query reported in Bug#46692. Also adds a few test cases for duplicate bug reports. @ sql/sql_select.cc 1. Do not include const tables in the set of tables to be considered by choose_plan when called from in optimize_semijon_nests(). This will make sure cost estimation is not skipped for tables that depend on const tables. (This is consistent with what is done for top level joins where const tables are filtered out from the set out tables considered by choose_plan.) 2. Let the record count returned by optimize_wo_join_buffering() be the fanout for outer tables. This way, it is not necessary to divide by inner fanout which created problems when record count from inner tables was 0. 3. Change the current_record_count computation for duplicate weed-out so that division and potential problems associated with that are avoided.
[29 Dec 2009 10:13]
Øystein Grøvlen
Bug#37893 is also a duplicate of this bug. I am not adding another test case for that bug since it is already covered other test cases where the right table of an left outer join is empty.
[29 Dec 2009 10:31]
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/95783 3802 oystein.grovlen@sun.com 2009-12-29 Bug#46692 Crash occurring on queries with nested FROM subqueries using materialization Subqueries with STRAIGHT_JOIN prevents pull-out of tables that would normally be pulled out of the subquery. This created scenarioes which the plan search was not prepared for, and one ended up without any valid plan. This caused segmentation fault in optimize_semijoin_nests when trying to access the best plan. The problem may occur with either const tables or eq_ref relations that does not have any matches. Bug#46692 reports the problem for FROM subqueries, but ordinary IN subqueries will have the same issue. The following changes are needed to handle this problem: 1. Do not include const tables in the set of tables to be considered by optimize_semijon_nests(). This will make sure cost estimation is not skipped for tables that depend on const tables. (This is consistent with what is done for top level joins where const tables are filtered out from the tables considered by choose_plan.) 2. Change computation of record count so that division by inner fanout is not needed. When the table that is added to the join prefix, is empty, fanout will become zero since it is the product of the number of records of all tables. When using division, a zero inner fanout caused the computed cost to become infinite. The fanout issue (issue 2 above) is also present with the duplicate weedout strategy, and this patch contains the necessary changes to avoid division in this scenario, too. A number of bug reports have been marked as duplicate of this bug report based on that this fix also fixes these issues. Those bug reports show that this is not just an issue for STRAIGHT_JOIN, but also for both right and left outer join. Note that while this fix, fixes the issue for outer joins, a more proper fix would be to make sure that the dependent constant tables could be pulled out. This will be fixed by bug 49952. This patch also adds some test cases for these duplicate reports as well as general testing of outer joins in subqueries when constant tables are involved. @ mysql-test/r/subselect_sj.result Add results for new test cases. @ mysql-test/suite/optimizer_unfixed_bugs/r/bug45219.result Remove the test for bug 45219 from optimizer_unfixed_bugs test suite since this issue is fixed by this patch and a similar test case has been added to subselect_sj.test. @ mysql-test/suite/optimizer_unfixed_bugs/t/bug45219.test Remove the test for bug 45219 from optimizer_unfixed_bugs test suite since this issue is fixed by this patch and a similar test case has been added to subselect_sj.test. @ mysql-test/t/subselect_sj.test Add test cases to check that subqueries with STRAIGHT_JOIN or outer join work for different permutations of const and non-const tables. Note that due to a still existing bug, left outer join is not tested. Added a test case that is similar to the query reported in Bug#46692. Also adds a few test cases for duplicate bug reports. @ sql/sql_select.cc 1. Do not include const tables in the set of tables to be considered by choose_plan when called from in optimize_semijon_nests(). This will make sure cost estimation is not skipped for tables that depend on const tables. (This is consistent with what is done for top level joins where const tables are filtered out from the set out tables considered by choose_plan.) 2. Let the record count returned by optimize_wo_join_buffering() be the fanout for outer tables. This way, it is not necessary to divide by inner fanout which created problems when record count from inner tables was 0. 3. Change the current_record_count computation for duplicate weed-out so that division and potential problems associated with that are avoided.
[29 Dec 2009 13:26]
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/95790 3804 oystein.grovlen@sun.com 2009-12-29 Bug#46692 Crash occurring on queries with nested FROM subqueries using materialization Subqueries with STRAIGHT_JOIN prevents pull-out of tables that would normally be pulled out of the subquery. This created scenarioes which the plan search was not prepared for, and one ended up without any valid plan. This caused segmentation fault in optimize_semijoin_nests when trying to access the best plan. The problem may occur with either const tables or eq_ref relations that does not have any matches. Bug#46692 reports the problem for FROM subqueries, but ordinary IN subqueries will have the same issue. The following changes are needed to handle this problem: 1. Do not include const tables in the set of tables to be considered by optimize_semijon_nests(). This will make sure cost estimation is not skipped for tables that depend on const tables. (This is consistent with what is done for top level joins where const tables are filtered out from the tables considered by choose_plan.) 2. Change computation of record count so that division by inner fanout is not needed. When the table that is added to the join prefix, is empty, fanout will become zero since it is the product of the number of records of all tables. When using division, a zero inner fanout caused the computed cost to become infinite. The fanout issue (issue 2 above) is also present with the duplicate weedout strategy, and this patch contains the necessary changes to avoid division in this scenario, too. A number of bug reports have been marked as duplicate of this bug report based on that this fix also fixes these issues. Those bug reports show that this is not just an issue for STRAIGHT_JOIN, but also for both right and left outer join. Note that while this fix, fixes the issue for outer joins, a more proper fix would be to make sure that the dependent constant tables could be pulled out. This will be fixed by bug 49952. This patch also adds some test cases for these duplicate reports as well as general testing of outer joins in subqueries when constant tables are involved. @ mysql-test/r/subselect_sj.result Add results for new test cases. @ mysql-test/suite/optimizer_unfixed_bugs/r/bug45219.result Remove the test for bug 45219 from optimizer_unfixed_bugs test suite since this issue is fixed by this patch and a similar test case has been added to subselect_sj.test. @ mysql-test/suite/optimizer_unfixed_bugs/t/bug45219.test Remove the test for bug 45219 from optimizer_unfixed_bugs test suite since this issue is fixed by this patch and a similar test case has been added to subselect_sj.test. @ mysql-test/t/subselect_sj.test Add test cases to check that subqueries with STRAIGHT_JOIN or outer join work for different permutations of const and non-const tables. Note that due to a still existing bug, left outer join is not tested. Added a test case that is similar to the query reported in Bug#46692. Also adds a few test cases for duplicate bug reports. @ sql/sql_select.cc 1. Do not include const tables in the set of tables to be considered by choose_plan when called from in optimize_semijon_nests(). This will make sure cost estimation is not skipped for tables that depend on const tables. (This is consistent with what is done for top level joins where const tables are filtered out from the set out tables considered by choose_plan.) 2. Let the record count returned by optimize_wo_join_buffering() be the fanout for outer tables. This way, it is not necessary to divide by inner fanout which created problems when record count from inner tables was 0. 3. Change the current_record_count computation for duplicate weed-out so that division and potential problems associated with that are avoided.
[29 Dec 2009 13:36]
Øystein Grøvlen
Patch pushed to mysql-6.0-codebase-bugfixing (revid:oystein.grovlen@sun.com-20091229132523-qv9gud3wyrb1ps4a).
[12 Jan 2010 16:25]
Bugs System
Pushed into 6.0.14-alpha (revid:alik@sun.com-20100112162328-2sblcul1kl08bbib) (version source revid:guilhem@mysql.com-20100108092756-k0zzf4kvx9b7bh38) (merge vers: 6.0.14-alpha) (pib:15)
[21 Jan 2010 8:38]
Bugs System
Pushed into 6.0.14-alpha (revid:alik@sun.com-20100121083501-but9pj2g3zmu10md) (version source revid:alik@sun.com-20100119194323-gcog2uiox2b7wsln) (merge vers: 6.0.14-alpha) (pib:16)
[23 Jan 2010 0:33]
Paul DuBois
Noted in 6.0.14 changelog. Queries with nested subqueries in the FROM clause using materialization could cause a server crash.
[7 Apr 2010 10:43]
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/105143 3067 oystein.grovlen@sun.com 2010-04-07 Bug#46692 Crash occurring on queries with nested FROM subqueries using materialization (Backporting of revid:oystein.grovlen@sun.com-20091229132523-qv9gud3wyrb1ps4a) Subqueries with STRAIGHT_JOIN prevents pull-out of tables that would normally be pulled out of the subquery. This created scenarioes which the plan search was not prepared for, and one ended up without any valid plan. This caused segmentation fault in optimize_semijoin_nests when trying to access the best plan. The problem may occur with either const tables or eq_ref relations that does not have any matches. Bug#46692 reports the problem for FROM subqueries, but ordinary IN subqueries will have the same issue. The following changes are needed to handle this problem: 1. Do not include const tables in the set of tables to be considered by optimize_semijon_nests(). This will make sure cost estimation is not skipped for tables that depend on const tables. (This is consistent with what is done for top level joins where const tables are filtered out from the tables considered by choose_plan.) 2. Change computation of record count so that division by inner fanout is not needed. When the table that is added to the join prefix, is empty, fanout will become zero since it is the product of the number of records of all tables. When using division, a zero inner fanout caused the computed cost to become infinite. The fanout issue (issue 2 above) is also present with the duplicate weedout strategy, and this patch contains the necessary changes to avoid division in this scenario, too. A number of bug reports have been marked as duplicate of this bug report based on that this fix also fixes these issues. Those bug reports show that this is not just an issue for STRAIGHT_JOIN, but also for both right and left outer join. Note that while this fix, fixes the issue for outer joins, a more proper fix would be to make sure that the dependent constant tables could be pulled out. This will be fixed by bug 49952. This patch also adds some test cases for these duplicate reports as well as general testing of outer joins in subqueries when constant tables are involved. @ mysql-test/r/subselect_sj.result Add results for new test cases. @ mysql-test/t/subselect_sj.test Add test cases to check that subqueries with STRAIGHT_JOIN or outer join work for different permutations of const and non-const tables. Note that due to a still existing bug, left outer join is not tested. Added a test case that is similar to the query reported in Bug#46692. Also adds a few test cases for duplicate bug reports. @ sql/sql_select.cc 1. Do not include const tables in the set of tables to be considered by choose_plan when called from in optimize_semijon_nests(). This will make sure cost estimation is not skipped for tables that depend on const tables. (This is consistent with what is done for top level joins where const tables are filtered out from the set out tables considered by choose_plan.) 2. Let the record count returned by optimize_wo_join_buffering() be the fanout for outer tables. This way, it is not necessary to divide by inner fanout which created problems when record count from inner tables was 0. 3. Change the current_record_count computation for duplicate weed-out so that division and potential problems associated with that are avoided.
[16 Aug 2010 6:41]
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:27]
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 2:56]
Paul DuBois
Bug does not appear in any released 5.6.x version. No 5.6.1 changelog entry needed.