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:
None 
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
Description:
Procedure with view + subquery + semijoin=on crashes on second call.
If semijoin is disabled then everything works fine.

How to repeat:
/*!50400 SET SESSION optimizer_switch ='semijoin=on'*/;

CREATE TABLE t1 ( f1 integer, primary key (f1));
CREATE TABLE t2 LIKE t1;
CREATE VIEW t3 AS SELECT f1 FROM t2 A WHERE A.f1 IN ( SELECT f1 FROM t2 );
delimiter |;
CREATE PROCEDURE p1 () BEGIN SELECT f1 FROM t3 AS A WHERE A.f1 IN ( SELECT f1 FROM t3 ) ;
END|
delimiter ;|
CALL p1;
CALL p1;
[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.