MySQL Bugs: #50089: Second call of procedure with view in subselect crashes server

Bug #50089	Second call of procedure with view in subselect crashes server
Submitted:	5 Jan 2010 14:57	Modified:	22 Nov 2010 0:35
Reporter:	Jørgen Løland	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S2 (Serious)
Version:	6.0	OS:	Any
Assigned to:	Roy Lyseng	CPU Architecture:	Any
Tags:	optimizer_switch, semijoin, SP, VIEW

Description:
With semijoin=on, the test script in BUG#49198 crashes with this stack trace:

#0  0x00385422 in __kernel_vsyscall ()
#1  0x00c51e93 in __pthread_kill (threadid=3068204912, signo=11)
    at ../nptl/sysdeps/unix/sysv/linux/pthread_kill.c:64
#2  0x0863df90 in my_write_core (sig=11) at stacktrace.c:328
#3  0x082987e7 in handle_segfault (sig=11) at mysqld.cc:2606
#4  <signal handler called>
#5  0x081d62ee in mark_select_range_as_dependent (thd=0x9a1dd58, 
    last_select=0x9ab0a78, current_sel=0x9a18228, found_field=0x99f2bf0, 
    found_item=0x9ab1600, resolved_item=0x9ab1600) at item.cc:3879
#6  0x0830880b in find_field_in_tables (thd=0x9a1dd58, item=0x9ab1600, 
    first_table=0x9ab11b0, last_table=0x0, ref=0x9a192d4, 
    report_error=IGNORE_EXCEPT_NON_UNIQUE, check_privileges=true, 
    register_tree_change=true) at sql_base.cc:6090
#7  0x081d7768 in Item_field::fix_fields (this=0x9ab1600, thd=0x9a1dd58, 
    reference=0x9a192d4) at item.cc:4478
#8  0x0831be72 in JOIN::prepare (this=0x9abee68, rref_pointer_array=0x9a1905c, 
    tables_init=0x9ab84e0, wild_num=0, conds_init=0x9ab8b38, og_num=0, 
    order_init=0x0, group_init=0x0, having_init=0x0, proc_param_init=0x0, 
    select_lex_arg=0x9a18f58, unit_arg=0x9ab8020) at sql_select.cc:617
#9  0x0824ec1a in subselect_single_select_engine::prepare (this=0x9a19318)
    at item_subselect.cc:2116
#10 0x082498cb in Item_subselect::fix_fields (this=0x9a19250, 
    thd_param=0x9a1dd58, ref=0x9ab89a4) at item_subselect.cc:174
#11 0x0824e154 in Item_in_subselect::fix_fields (this=0x9a19250, 
    thd_arg=0x9a1dd58, ref=0x9ab89a4) at item_subselect.cc:1795
#12 0x08210d5a in Item_in_optimizer::fix_fields (this=0x9ab8948, 
    thd=0x9a1dd58, ref=0x9ab616c) at item_cmpfunc.cc:1726
#13 0x08218773 in Item_cond::fix_fields (this=0x9ab60b8, thd=0x9a1dd58, 
    ref=0x9abedc4) at item_cmpfunc.cc:4195
#14 0x0830bdd7 in setup_conds (thd=0x9a1dd58, tables=0x9ab11b0, 
    leaves=0x9ab11b0, conds=0x9abedc4) at sql_base.cc:7831
#15 0x083516fb in setup_without_group (thd=0x9a1dd58, 
    ref_pointer_array=0x9a19408, tables=0x9ab11b0, leaves=0x9ab11b0, 
    fields=..., all_fields=..., conds=0x9abedc4, order=0x0, group=0x0, 
    hidden_group_fields=0x9abed33) at sql_select.cc:450
#16 0x0831baf9 in JOIN::prepare (this=0x9aba008, rref_pointer_array=0x9ab0b7c, 
    tables_init=0x9ab11b0, wild_num=0, conds_init=0x9ab60b8, og_num=0, 
    order_init=0x0, group_init=0x0, having_init=0x0, proc_param_init=0x0, 
    select_lex_arg=0x9ab0a78, unit_arg=0x9ab05bc) at sql_select.cc:532
#17 0x08322e19 in mysql_select (thd=0x9a1dd58, rref_pointer_array=0x9ab0b7c, 
    tables=0x9ab11b0, wild_num=0, fields=..., conds=0x9ab60b8, og_num=0, 
    order=0x0, group=0x0, having=0x0, proc_param=0x0, 
    select_options=2147749632, result=0x9ab61e8, unit=0x9ab05bc, 
    select_lex=0x9ab0a78) at sql_select.cc:3119

How to repeat:
Run test script from BUG#49198

Suggested fix:
Make it not crash :)

The regression bug number is 45863.

This is a preliminary analysis of the situation:

View definition:
SELECT t1field AS v1_field
FROM t1 AS A
WHERE A.t1field IN (SELECT t1field FROM t1);

Select_lex graph:
 VS1(A)
   \
    VS2(t1)

query:
SELECT t1field FROM t1
WHERE t1field IN (SELECT * FROM v1);

expanded query:
SELECT t1field FROM t1
WHERE t1field IN (SELECT t1field AS v1_field
                  FROM t1 AS A
                  WHERE A.t1field IN (SELECT t1field FROM t1));

Select_lex graph:
 S1(t1)
   \
    S2(A)
      \
       VS2(t1)

after semijoin conversion:
SELECT t1_field FROM t1 semijoin t1 AS A
                     ON t1.t1_field = A.t1_field
WHERE A.t1_field IN (SELECT t1_field FROM t1));

Now, S2 is optimized away and the table A is added to S1.

The master field of VS2 points to S1 after conversion. However, the return_to field of VS2 points to VS1. This means that when re-resolving A.t1_field, function mark_select_range_as_dependent() is called with VS1 as current_sel and S1 as last_select arguments. Now, the master unit of VS1 does not have an "item", and we segfault.

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/99719

3873 Roy Lyseng	2010-02-09
      BUG#50089: Second call of procedure with view in subselect crashes server
      
      See bug report for an analysis of the situation.
      Problem occurs when mark_select_range_as_dependent() is called with two
      select_lex pointers that point into different query trees, one tree being
      the main query tree, the other being the tree representing the view.
      
      This problem is due to the fact that the function return_after_parsing() is
      used to provide pointer to the select_lex object representing the outer
      query specification of a subquery. However, the return_to field was not
      properly updated in all situations. But it was also noticed that outer_select()gives the same information as return_after_parsing(), and the data used by
      this function is always kept up-to-date.
      
      The bug was thus fixed by removing return_after_parsing() and associated data,
      and replacing calls with outer_select().
      
        mysql-test/r/subselect_sj.result
          Test result for BUG#50089
        mysql-test/r/subselect_sj_jcl6.result
          Test result for BUG#50089
        mysql-test/t/subselect_sj.test
          Test case for BUG#50089
        sql/item_subselect.cc
          Replaced references to return_after_parsing() with outer_select().
        sql/sql_lex.cc
          Removed function return_after_parsing() and data field return_to.
        sql/sql_lex.h
          Removed function return_after_parsing() and data field return_to.
        sql/sql_parse.cc
          Replaced reference to return_after_parsing() with outer_select().
        sql/sql_select.cc
          Replaced reference to return_after_parsing() with outer_select().
        sql/sql_yacc.yy
          Replaced reference to return_after_parsing() with outer_select().

Please note that the attached bug fix depends on the fix for bug#49198.
Without that fix, this fix will still cause an invalid result on second and subsequent executions.

approved with minor comments sent by mail

See also:
Bug #50996 Wrong result for second call of prepared statement with view in subselect

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/102862

3804 Roy Lyseng	2010-03-10
      Bug#50089: Second call of procedure with view in subselect crashes server
      
      The bug case contains a query that references a view.
      Here is the view definition:
      
      SELECT t1field AS v1_field
      FROM t1 AS A
      WHERE A.t1field IN (SELECT t1field FROM t1);
      
      When expanding the view at prepare time, this select_lex graph is created:
      
       VS1(A)
         \
          VS2(t1)
      
      This is the query that is executed:
      
      SELECT t1field FROM t1
      WHERE t1field IN (SELECT * FROM v1);
      
      At prepare time, the query is conceptually expanded to the following query:
      
      SELECT t1field FROM t1
      WHERE t1field IN (SELECT t1field AS v1_field
                        FROM t1 AS A
                        WHERE A.t1field IN (SELECT t1field FROM t1));
      
      Here is the select_lex graph after view expansion:
      
       S1(t1)
         \
          S2(A)
            \
             VS2(t1)
      
      The contents of select_lex VS1 is merged with the query graph and VS1
      is discarded after merging.
      
      At first execution, semijoin conversion is performed, and the query is
      transformed to:
      
      SELECT t1_field FROM t1 semijoin t1 AS A
                           ON t1.t1_field = A.t1_field
      WHERE A.t1_field IN (SELECT t1_field FROM t1));
      
      Now, the select_lex object S2 is optimized away and the table A is added to S1.
      Here is the select_lex graph after transformation:
      
       S1(t1)
         \
          VS2(t1)
      
      The master field of VS2 points to S1 after conversion.
      However, the return_to field of VS2 still points to VS1.
      
      First execution proceeds normally after this.
      
      At second execution, all items in the transformed query are first re-resolved. 
      When re-resolving A.t1_field, function mark_select_range_as_dependent()
      is called with VS1 as current_sel and S1 as last_select arguments.
      Now, the master unit of VS1 does not have an "item", and we segfault.
      
      Problem occurs when mark_select_range_as_dependent() is called with two
      select_lex pointers that point into different query trees, one tree being
      the main query tree, the other being the tree representing the view.
            
      This problem is due to the fact that the function return_after_parsing() is
      used to provide pointer to the select_lex object representing the outer
      query specification of a subquery. However, the return_to field was not
      properly updated in all situations. But it was also noticed that
      outer_select() gives the same information as return_after_parsing(),
      and the data used by this function is always kept up-to-date.
            
      The bug was thus fixed by removing return_after_parsing() and associated data,
      and replacing calls with outer_select().
      
      Notice also that queries within procedures internally create
      prepared statements, so the solution works equally well for procedures
      as well as for prepared statements.
            
      mysql-test/r/subselect_sj.result
        Test result for Bug#50089
      mysql-test/r/subselect_sj_jcl6.result
        Test result for Bug#50089
      mysql-test/t/subselect_sj.test
        Test case for Bug#50089
      sql/item_subselect.cc
        Replaced references to return_after_parsing() with outer_select().
      sql/sql_lex.cc
        Removed function return_after_parsing() and data field return_to.
      sql/sql_lex.h
        Removed function return_after_parsing() and data field return_to.
      sql/sql_parse.cc
        Replaced reference to return_after_parsing() with outer_select().
      sql/sql_select.cc
        Replaced reference to return_after_parsing() with outer_select().
      sql/sql_yacc.yy
        Replaced reference to return_after_parsing() with outer_select().

Pushed into 6.0.14-alpha (revid:alik@sun.com-20100310133305-0jdlngbtrkoqzckh) (version source revid:alik@sun.com-20100310132404-uqarl0o0vlra2kjx) (merge vers: 6.0.14-alpha) (pib:16)

Noted in 6.0.14 changelog.

With semijoin optimization enabled, references to views within a
subquery could cause a server crash.

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/103247

3824 Roy Lyseng	2010-03-15
      Bug#50089: Second call of procedure with view in subselect crashes server
      
      Followup commit to test case: Use lowercase table alias name to avoid
      platform dependent test results.
      
      mysql-test/r/subselect_sj.result
        Test result for Bug#50089
      mysql-test/r/subselect_sj_jcl6.result
        Test result for Bug#50089
      mysql-test/t/subselect_sj.test
        Rename table alias from A (upper case a) to a (lower case a).

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)

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/107741

3148 Roy Lyseng	2010-05-07
      Bug#50089: Second call of procedure with view in subselect crashes server
            
      The bug case contains a query that references a view.
      Here is the view definition:
            
      SELECT t1field AS v1_field
      FROM t1 AS A
      WHERE A.t1field IN (SELECT t1field FROM t1);
            
      When expanding the view at prepare time, this select_lex graph is created:
            
       VS1(A)
         \
          VS2(t1)
            
      This is the query that is executed:
            
      SELECT t1field FROM t1
      WHERE t1field IN (SELECT * FROM v1);
            
      At prepare time, the query is conceptually expanded to the following query:
            
      SELECT t1field FROM t1
      WHERE t1field IN (SELECT t1field AS v1_field
                        FROM t1 AS A
                        WHERE A.t1field IN (SELECT t1field FROM t1));
            
      Here is the select_lex graph after view expansion:
            
       S1(t1)
         \
          S2(A)
            \
             VS2(t1)
            
      The contents of select_lex VS1 is merged with the query graph and VS1
      is discarded after merging.
            
      At first execution, semijoin conversion is performed, and the query is
      transformed to:
            
      SELECT t1_field FROM t1 semijoin t1 AS A
                           ON t1.t1_field = A.t1_field
      WHERE A.t1_field IN (SELECT t1_field FROM t1));
            
      Now, the select_lex object S2 is optimized away and the table A is added to S1.
      Here is the select_lex graph after transformation:
            
       S1(t1)
         \
          VS2(t1)
            
      The master field of VS2 points to S1 after conversion.
      However, the return_to field of VS2 still points to VS1.
            
      First execution proceeds normally after this.
            
      At second execution, all items in the transformed query are first re-resolved. 
      When re-resolving A.t1_field, function mark_select_range_as_dependent()
      is called with VS1 as current_sel and S1 as last_select arguments.
      Now, the master unit of VS1 does not have an "item", and we segfault.
            
      Problem occurs when mark_select_range_as_dependent() is called with two
      select_lex pointers that point into different query trees, one tree being
      the main query tree, the other being the tree representing the view.
                  
      This problem is due to the fact that the function return_after_parsing() is
      used to provide pointer to the select_lex object representing the outer
      query specification of a subquery. However, the return_to field was not
      properly updated in all situations. But it was also noticed that
      outer_select() gives the same information as return_after_parsing(),
      and the data used by this function is always kept up-to-date.
                  
      The bug was thus fixed by removing return_after_parsing() and associated data,
      and replacing calls with outer_select().
            
      Notice also that queries within procedures internally create
      prepared statements, so the solution works equally well for procedures
      as well as for prepared statements.
                  
      mysql-test/r/subselect_sj.result
        Test result for Bug#50089
      mysql-test/r/subselect_sj_jcl6.result
        Test result for Bug#50089
      mysql-test/t/subselect_sj.test
        Test case for Bug#50089
      sql/item_subselect.cc
        Replaced references to return_after_parsing() with outer_select().
      sql/sql_lex.cc
        Removed function return_after_parsing() and data field return_to.
      sql/sql_lex.h
        Removed function return_after_parsing() and data field return_to.
      sql/sql_parse.cc
        Replaced reference to return_after_parsing() with outer_select().
      sql/sql_select.cc
        Replaced reference to return_after_parsing() with outer_select().
      sql/sql_yacc.yy
        Replaced reference to return_after_parsing() with outer_select().
      
      revid:marc.alff@sun.com-20100310053357-iufi8n11rjkh8lrb..roy.lyseng@sun.com-20100310111425-7r30qg17f5z13ptd

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/107742

3149 Roy Lyseng	2010-05-07
      Bug#50089: Second call of procedure with view in subselect crashes server
                
      Followup commit to test case: Use lowercase table alias name to avoid
      platform dependent test results.
                
      mysql-test/r/subselect_sj.result
        Test result for Bug#50089
      mysql-test/r/subselect_sj_jcl6.result
        Test result for Bug#50089
      mysql-test/t/subselect_sj.test
        Rename table alias from A (upper case a) to a (lower case a).
      
      revid:alik@sun.com-20100315102711-m5b8u7banzzc30q7..roy.lyseng@sun.com-20100315134319-qe3k8vb0l371340w

Pushed into mysql-next-mr (revid:alik@sun.com-20100816062819-bluwgdq8q4xysmlg) (version source revid:alik@sun.com-20100816062612-enatdwnv809iw3s9) (pib:20)

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)

Noted in 5.6.1 changelog.

Correction: No 5.6.1 changelog entry. Bug does not appear in any released 5.6.x version.