Bug #48834 | Procedure with view + subquery + semijoin=on crashes on second call. | ||
---|---|---|---|
Submitted: | 17 Nov 2009 11:37 | Modified: | 22 Nov 2010 0:52 |
Reporter: | Sergei Glukhov | 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: | optimizer_switch, regression, semijoin, subquery |
[17 Nov 2009 11:37]
Sergei Glukhov
[17 Nov 2009 12:05]
Valeriy Kravchuk
Verified just as described with recent 6.0.14 from bzr on Mac OS X: 77-52-1-11:6.0-codebase openxs$ bin/mysql -uroot test Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 Server version: 6.0.14-alpha-debug Source distribution Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> /*!50400 SET SESSION optimizer_switch ='semijoin=on'*/;Query OK, 0 rows affected (0.00 sec) mysql> CREATE TABLE t1 ( f1 integer, primary key (f1));Query OK, 0 rows affected (0.07 sec) mysql> CREATE TABLE t2 LIKE t1;Query OK, 0 rows affected (0.07 sec) mysql> CREATE VIEW t3 AS SELECT f1 FROM t2 A WHERE A.f1 IN ( SELECT f1 FROM t2 ); Query OK, 0 rows affected (0.05 sec) mysql> delimiter // mysql> CREATE PROCEDURE p1 () BEGIN SELECT f1 FROM t3 AS A WHERE A.f1 IN ( SELECT f1 FROM t3 ) ; END// Query OK, 0 rows affected (0.02 sec) mysql> delimiter ; mysql> call p1(); Empty set (0.04 sec) Query OK, 0 rows affected (0.04 sec) mysql> call p1(); ERROR 2013 (HY000): Lost connection to MySQL server during query We have assertion failure: Assertion failed: (*ref), function fix_fields, file item.cc, line 6200. 5.1.x does not crash, so this is a regression bug.
[18 Nov 2009 8:51]
Roy Lyseng
Followup to bug#47649 that was fixed on 5.1 branch
[26 Nov 2009 13: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/91810 3738 Jorgen Loland 2009-11-26 Bug#48834: Procedure with view + subquery + semijoin=on crashes on second call. Item_in_subselect::single_value_transformer() wraps subselects of the form "<outer_expression> IN/ALL/ANY (<inner_expression>)" in Item_in_optimizer objects. The Item_in_optimizer stores the outer expression in a Item_cache. After executing a stored procedure, items are cleaned up in sp_head::execute. As a result of this, the Item_in_optimizer used to set it's cache to 0. The second time the procedure was called, the Item_ref that referred to the Item_in_optimizer triggered an ASSERT in fix_fields() because I_i_o had deleted it's cache after the first execution. The fix for this bug is to instruct the Item_in_optimizer to not reset it's cache during cleanup() by calling keep_top_level_cache() when the I_i_o is created by Item_in_subselect::single_value_transformer(). @ mysql-test/r/subselect_sj.result Added test for BUG#48834 @ mysql-test/r/subselect_sj_jcl6.result Added test for BUG#48834 @ mysql-test/t/subselect_sj.test Added test for BUG#48834 @ sql/item_subselect.cc Instruct the Item_in_optimizer to not reset it's cache during cleanup() by calling keep_top_level_cache() when the I_i_o is created by Item_in_subselect::single_value_transformer(). The cache is later used if this is a SP that is reexecuted.
[2 Dec 2009 7:06]
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/92381 3744 Jorgen Loland 2009-12-02 Bug#48834: Procedure with view + subquery + semijoin=on crashes on second call. Item_in_subselect::single_value_transformer() wraps subselects of the form "<outer_expression> IN/ALL/ANY (<inner_expression>)" in Item_in_optimizer objects. The Item_in_optimizer stores the outer expression in a Item_cache. After executing a stored procedure, items are cleaned up in sp_head::execute(). As a result of this, Item_in_optimizers will reset their cache to 0 unless save_cache=1. save_cache was not properly set to 1 for all queries. Hence the second time the procedure was called, the Item_ref that referred to the Item_in_optimizer triggered an ASSERT in fix_fields() because Item_in_optimizer had deleted it's cache after the first execution. The fix for this bug is to instruct the Item_in_optimizer to not reset it's cache during cleanup() by calling keep_top_level_cache() when the Item_in_optimizer is created by Item_in_subselect::single_value_transformer(). A similar patch was made for bug 2462, but only for Item_in_optimizer wrappings made by Item_in_subselect::row_value_transformer(). @ mysql-test/r/subselect_sj.result Added test for BUG#48834 @ mysql-test/r/subselect_sj_jcl6.result Added test for BUG#48834 @ mysql-test/t/subselect_sj.test Added test for BUG#48834 @ sql/item_subselect.cc Instruct the Item_in_optimizer to not reset it's cache during cleanup() by calling keep_top_level_cache() when the Item_in_optimizer is created by Item_in_subselect::single_value_transformer(). The cache is later used if this is a SP/PS that is reexecuted.
[2 Dec 2009 7:35]
Jørgen Løland
Patch pushed to 6.0-codebase-bugfixing
[11 Dec 2009 6:01]
Bugs System
Pushed into 6.0.14-alpha (revid:alik@sun.com-20091211055901-yp18b3c7xuhl87rf) (version source revid:alik@sun.com-20091211055401-43rjwq7gjed6ds83) (merge vers: 6.0.14-alpha) (pib:13)
[16 Dec 2009 3:01]
Paul DuBois
Noted in 6.0.14 changelog. With semijoin optimization enabled, a stored procedure that selected from a view in a subquery crashed when invoked the second time.
[7 Apr 2010 7: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/105124 3065 oystein.grovlen@sun.com 2010-04-07 Bug#48834: Procedure with view + subquery + semijoin=on crashes on second call. (Backporting of revid:jorgen.loland@sun.com-20091202070826-a6q6tm9y17fjo7rz, actual fix was backported as part of merge revid:epotemkin@mysql.com-20100325110824-7v3wlwvr79zq77up. Only test case is back-ported here.) Item_in_subselect::single_value_transformer() wraps subselects of the form "<outer_expression> IN/ALL/ANY (<inner_expression>)" in Item_in_optimizer objects. The Item_in_optimizer stores the outer expression in a Item_cache. After executing a stored procedure, items are cleaned up in sp_head::execute(). As a result of this, Item_in_optimizers will reset their cache to 0 unless save_cache=1. save_cache was not properly set to 1 for all queries. Hence the second time the procedure was called, the Item_ref that referred to the Item_in_optimizer triggered an ASSERT in fix_fields() because Item_in_optimizer had deleted it's cache after the first execution. The fix for this bug is to instruct the Item_in_optimizer to not reset it's cache during cleanup() by calling keep_top_level_cache() when the Item_in_optimizer is created by Item_in_subselect::single_value_transformer(). A similar patch was made for bug 2462, but only for Item_in_optimizer wrappings made by Item_in_subselect::row_value_transformer(). @ mysql-test/r/subselect_sj.result Added test for BUG#48834 @ mysql-test/t/subselect_sj.test Added test for BUG#48834
[16 Aug 2010 6:34]
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:23]
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 0:52]
Paul DuBois
Noted in 5.6.1 changelog.
[23 Nov 2010 2:23]
Paul DuBois
Correction: No 5.6.1 changelog entry. Bug does not appear in any released 5.6.x version.