Bug #49453 re-execution of prepared statement with view and semijoin crashes
Submitted: 4 Dec 2009 10:31 Modified: 23 Nov 2010 3:06
Reporter: Tor Didriksen 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, semijoin, subquery

[4 Dec 2009 10:31] Tor Didriksen
Description:
This crashes, even with fixes for Bug#48508 and Bug#48834

--disable_warnings
DROP TABLE IF EXISTS t1, t2;
DROP VIEW IF EXISTS v1;
--enable_warnings

CREATE TABLE t1 (
  city VARCHAR(50) NOT NULL,
  country_id SMALLINT UNSIGNED NOT NULL
);

INSERT INTO t1 VALUES 
('Batna',2),
('Bchar',2),
('Skikda',2),
('Tafuna',3),
('Algeria',2) ;

CREATE TABLE t2 (
  country_id SMALLINT UNSIGNED NOT NULL,
  country VARCHAR(50) NOT NULL
);

INSERT INTO t2 VALUES
(2,'Algeria'),
(3,'XAmerican Samoa') ;

CREATE VIEW v1 AS 
SELECT country_id, country 
FROM t2
WHERE LEFT(country,1) = "A" 
; 

PREPARE stmt FROM
"
SELECT city, country_id
FROM t1
WHERE country_id IN (
  SELECT country_id 
  FROM v1
);
";

execute stmt;
execute stmt;

deallocate prepare stmt;
drop table t1, t2;
drop view v1;

#0  0x00753424 in __kernel_vsyscall ()
#1  0x00b0a1e8 in pthread_kill () from /lib/libpthread.so.0
#2  0x086ff9ae in my_write_core (sig=11) at stacktrace.c:309
#3  0x082c9d2e in handle_segfault (sig=11) at mysqld.cc:2765
#4  <signal handler called>
#5  0x0822f929 in Item_func::fix_fields (this=0x9641f20, thd=0x95c28a8, ref=0x956d14c) at item_func.cc:179
#6  0x08243c55 in Item_cond::fix_fields (this=0x956d090, thd=0x95c28a8, ref=0x9646ee4) at item_cmpfunc.cc:4068
#7  0x083282a0 in setup_conds (thd=0x95c28a8, tables=0x956fd88, leaves=0x956fd88, conds=0x9646ee4) at sql_base.cc:7806
#8  0x0837b3d5 in setup_without_group (thd=0x95c28a8, ref_pointer_array=0x96418c8, tables=0x956fd88, leaves=0x956fd88, fields=@0x956f568, 
    all_fields=@0x9646e70, conds=0x9646ee4, order=0x0, group=0x0, hidden_group_fields=0x9646e53) at sql_select.cc:454
#9  0x083720b8 in JOIN::prepare (this=0x9642128, rref_pointer_array=0x956f5d8, tables_init=0x956fd88, wild_num=0, conds_init=0x956d090, og_num=0, 
    order_init=0x0, group_init=0x0, having_init=0x0, proc_param_init=0x0, select_lex_arg=0x956f4d4, unit_arg=0x956f01c) at sql_select.cc:536
#10 0x0837346c in mysql_select (thd=0x95c28a8, rref_pointer_array=0x956f5d8, tables=0x956fd88, wild_num=0, fields=@0x956f568, conds=0x956d090, og_num=0, 
    order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2416183808, result=0x9570ea8, unit=0x956f01c, select_lex=0x956f4d4)
    at sql_select.cc:3124
#11 0x08378d1f in handle_select (thd=0x95c28a8, lex=0x956efc0, result=0x9570ea8, setup_tables_done_option=0) at sql_select.cc:308
#12 0x082da31b in execute_sqlcom_select (thd=0x95c28a8, all_tables=0x956fd88) at sql_parse.cc:4961
#13 0x082dbbd2 in mysql_execute_command (thd=0x95c28a8) at sql_parse.cc:2156
#14 0x0838b424 in Prepared_statement::execute (this=0x95a0cf0, expanded_query=0xb73d8ac8, open_cursor=false) at sql_prepare.cc:3768
#15 0x0838ef21 in Prepared_statement::execute_loop (this=0x95a0cf0, expanded_query=0xb73d8ac8, open_cursor=false, packet=0x0, packet_end=0x0)
    at sql_prepare.cc:3401
#16 0x0838f1be in mysql_sql_stmt_execute (thd=0x95c28a8) at sql_prepare.cc:2574
#17 0x082dbbfb in mysql_execute_command (thd=0x95c28a8) at sql_parse.cc:2166
#18 0x082e47c1 in mysql_parse (thd=0x95c28a8, inBuf=0x956cf88 "execute stmt", length=12, found_semicolon=0xb73d9e68) at sql_parse.cc:5975
#19 0x082e5273 in dispatch_command (command=COM_QUERY, thd=0x95c28a8, packet=0x9573179 "execute stmt", packet_length=12) at sql_parse.cc:1076
#20 0x082e65f7 in do_command (thd=0x95c28a8) at sql_parse.cc:758
#21 0x082d39e5 in handle_one_connection (arg=0x95c28a8) at sql_connect.cc:1164
#22 0x00b0551f in start_thread () from /lib/libpthread.so.0
#23 0x00a3b04e in clone () from /lib/libc.so.6

How to repeat:
Test case provided above.
We do not crash if semijoin is disabled.
[10 Dec 2009 13:16] Jørgen Løland
Why the crash happens:

In the first execution, the parser sets up the item_list for the
subquery (item_list in st_select_lex). The item_list contains one
Item_field which is the view field. During Item_field::fix_fields
(part of setup_fields), this Item_field is substituted with an
Item_direct_view_ref to the t2 field, and the substitution is stored
in ref_pointer_array in st_select_lex. In convert_subq_to_sj(), this
Item_direct_view_ref is pulled out to the main query (using an
Item_func_eq).

In the second execution, the item_list of the subquery is first
emptied before the parser once more sets up the item_list. Like
before, the list for the subquery lex contains an Item_field for the
view field and nothing more. However, the subselect is no longer part
of the query (the substituted subquery was pulled out), so the
Item_field is not fixed (and hence not substituted) this time. Even
so, optimize_semijoin_nests() tries to find the best access plan by
reading information about the fields in the subquery:

sql_select.cc#optimize_semijoin_nests():

  List<Item> &right_expr_list=
     sj_nest->sj_subq_pred->unit->first_select()->item_list;

Note: similar code ^ in setup_sj_materialization() that must be
fixed as well.

So, in the first execution of the PS, right_expr_list contains the
substituted Item (the Item_direct_view_ref). In the second execution,
the right_expr_list contains the Item_field. The Item_field has not
been fixed, so when we do this:

  map |= item->used_tables();

Item_field::used_tables() crashes because it's field is a
null-pointer.
[10 Dec 2009 13:17] Jørgen Løland
Some suggestions for how to fix the bug:

  1. During the first execution, the st_select_lex of the subselect
     stored the substituted item in it's ref_pointer_array. Theory:
     The ref_pointer_array will remain constant after the first
     execution because setup_fields() is not performed on the items in
     this select lex. Therefore, the references to 

        sj_nest->sj_subq_pred->unit->first_select()->item_list
     can be replaced with
        sj_nest->sj_subq_pred->unit->first_select()->ref_pointer_array;

  2. The Item_field stored in the subselect item_list is the same
     object in all executions of the statement. By adding "Item
     *replaced_with_item" to Item_field, we can get the
     Item_direct_view_ref from the Item_field by doing
          
     while (item->type() == Item::FIELD_ITEM && 
            ((Item_field*)item)->replaced_with) 
       item= ((Item_field*)item)->replaced_with;

     on the items in the item_list

  3. A variable "List<Item> sjnest_resolved_item_list" can be added to
     TABLE_LIST. The items in the item_list of the subselect can be
     stored here for all semijoin nests during convert_subq_to_sj().

  4. "Manually" call setup_fields on the subselect of a sj_nest so
     that the Item_field is replaced with a new Item_direct_view_ref.
     Not sure if this will work yet.

I have verified that #1 and #2 work, but the theory of #1 needs to be
confirmed. However, after removing the crash using #1 or #2, I get a
wrong result set for the second execution. This is very similar to
BUG#49198 and might be a duplicate.
[16 Dec 2009 13:44] Jørgen Løland
Reason for wrong result on second execution:
 * In optimize_semijoin_nests(), the pulled out view item, an
   Item_ref, returns OUTER_REF_TABLE_BIT (via the Item_field it refers
   to) instead of it's table map here:

   map |= item->used_tables();

 * It returns OUTER_REF_TABLE_BIT instead of the table map because
   Item_field depended_from points to the select lex of the inner
   select.
 * depended_from points to the inner select because when the pulled
   out Item_field is fixed (Item_field::fix_item), it's select lex is
   not the same select lex as that of the context. Specifically, the
   context is the outer query, while the item's select lex is that of
   the inner query.
 * The select lex of the pulled out item wrongly points to the select
   lex of the inner query because fix_after_pullout() was never called
   for the Item_field. The reason is this code in
   Item_ref::fix_after_pullout():

   if (depended_from == new_parent)
   {
     (*ref)->fix_after_pullout(new_parent, ref);
     depended_from= NULL;
   }

   Since the view is referred only in the sub select, depended_from
   is NULL in our case.
[16 Dec 2009 13:46] Jørgen Løland
Combining strategy #1 above with this change gives correct result of the query:

 void Item_ref::fix_after_pullout(st_select_lex *new_parent, Item **refptr)
 {
+  (*ref)->fix_after_pullout(new_parent, ref);
   if (depended_from == new_parent)
   {
-    (*ref)->fix_after_pullout(new_parent, ref);
     depended_from= NULL;
   }
[17 Dec 2009 13:12] Jørgen Løland
The patch implements a variant of strategy #4 above. Views pulled out to semijoin nests are reresolved on next PS execution. This ensures that the Item_field is properly replaced with the Item_direct_view_ref just as in the first execution of the PS.
[17 Dec 2009 13:37] 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/94742

3778 Jorgen Loland	2009-12-17
      Bug#49453: "re-execution of prepared statement with view and 
                  semijoin crashes"
      
      There were two bugs:
      1) When semijoin was applied on a view, the replaced items were
         pulled out instead of Item_field since the items referring
         to view columns had already been fixed. However, the semijoin
         nest still stored the item list in sj_subq_pred, and on next
         execution of the PS, this item list was reset during parsing.
         Since this list is not attached to the query, Item_fields 
         referring to view columns were not replaced with 
         Item_direct_view_ref as would happen in normal execution. 
         The fix for this is to reresolve views pulled out to semijoin
         nests on next execute.
      2) After pulling views out to semijoin nests, fix_after_pullout
         is called on all the items. However, this call did not 
         propagate to items referred to from views because 
         Item_ref::fix_after_pullout() did only recursively call this
         method on it's children if the item had depended_from 
         set to the select lex of the semijoin nest. The fix is to 
         call fix_after_pullout() on the children even if the view 
         was not dependent on that select lex. The Item_ref's 
         depended_from will, e.g., be null if the view is only 
         used inside the subselect.
     @ mysql-test/r/subselect_sj.result
        Added test for BUG#49453
     @ mysql-test/r/subselect_sj_jcl6.result
        Added test for BUG#49453
     @ mysql-test/t/subselect_sj.test
        Added test for BUG#49453
     @ sql/item.cc
        Make Item_ref::fix_after_pullout() call fix_after_pullout() on children even if this Item_ref wasn't dependent on the select lex it has been pulled out to.
     @ sql/item_cmpfunc.cc
        REVIEWERS DISREGARD. This is patch for bug 48508, which has not yet been merged into 6.0-codebase-bugfixing
     @ sql/sql_base.cc
        REVIEWERS DISREGARD. This is patch for bug 48508, which has not yet been merged into 6.0-codebase-bugfixing
     @ sql/sql_class.h
        REVIEWERS DISREGARD. This is patch for bug 48508, which has not yet been merged into 6.0-codebase-bugfixing
     @ sql/sql_select.cc
        Make views pulled out to a semijoin-nest be reresolved on next PS/SP execution.
     @ sql/sql_view.cc
        Resolve views over again if execution of PS/SP requires it. This happens if the view has been pulled out to a semijoin nest.
     @ sql/table.h
        Added variable bool view_needs_new_resolve, used to indicate that a view needs to be resolved on next PS/SP execution even though it was already resolved on a previous execution. This happens if the view has been pulled out to a semijoin nest.
[5 Jan 2010 15:22] Jørgen Løland
An alternative to adding the view_needs_new_resolve variable in the patch above, is to reset the table_list's view pointer like this:

 while ((tl= li++))
   {
     tl->embedding= sj_nest;
     tl->join_list= &nested_join->join_list;
     nested_join->join_list.push_back(tl);
+    if (tl->view)
+      tl->view= NULL;
   }

However, it is expected that WL#4389 will make the handling of the subselect's select list cleaner. Thus, this bug is postponed for now.
[22 Jan 2010 12:18] Roy Lyseng
Ran this test on the wl4389 branch. One additional changed code line makes the bug go away:

Function optimize_semijoin_nests():
replace the assignment to right_expr_list with the expression
sj_nest->nested_join->sj_inner_exprs.
[16 Mar 2010 10:04] Tor Didriksen
See also
Bug #50996 Wrong result for second call of prepared statement with view in subselect
[3 Aug 2010 9:20] 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/114909

3222 Jorgen Loland	2010-08-03
      Bug#49453: "re-execution of prepared statement with 
                  view and semijoin crashes"
      
      When semijoin was applied on a view, the replaced items were
      pulled out instead of Item_field since the items referring
      to view columns had already been fixed. However, the semijoin
      nest still stored the item list in sj_subq_pred, and on next
      execution of the PS, this item list was reset during parsing.
      Since this list is not attached to the query, Item_fields 
      referring to view columns were not replaced with 
      Item_direct_view_ref as would happen in normal execution.
      
      The fix for this is to use the sj_inner_exprs list of the 
      semijoin nest instead of the item list. The sj_inner_exprs list
      contains the resolved items in the subquery.
     @ mysql-test/include/subquery_sj.inc
        Added test for BUG#49453
     @ mysql-test/r/subquery_sj_all.result
        Added test for BUG#49453
     @ mysql-test/r/subquery_sj_all_jcl6.result
        Added test for BUG#49453
     @ mysql-test/r/subquery_sj_all_jcl7.result
        Added test for BUG#49453
     @ mysql-test/r/subquery_sj_dupsweed.result
        Added test for BUG#49453
     @ mysql-test/r/subquery_sj_dupsweed_jcl6.result
        Added test for BUG#49453
     @ mysql-test/r/subquery_sj_dupsweed_jcl7.result
        Added test for BUG#49453
     @ mysql-test/r/subquery_sj_firstmatch.result
        Added test for BUG#49453
     @ mysql-test/r/subquery_sj_firstmatch_jcl6.result
        Added test for BUG#49453
     @ mysql-test/r/subquery_sj_firstmatch_jcl7.result
        Added test for BUG#49453
     @ mysql-test/r/subquery_sj_loosescan.result
        Added test for BUG#49453
     @ mysql-test/r/subquery_sj_loosescan_jcl6.result
        Added test for BUG#49453
     @ mysql-test/r/subquery_sj_loosescan_jcl7.result
        Added test for BUG#49453
     @ mysql-test/r/subquery_sj_mat.result
        Added test for BUG#49453
     @ mysql-test/r/subquery_sj_mat_jcl6.result
        Added test for BUG#49453
     @ mysql-test/r/subquery_sj_mat_jcl7.result
        Added test for BUG#49453
     @ mysql-test/r/subquery_sj_mat_nosj.result
        Added test for BUG#49453
     @ mysql-test/r/subquery_sj_none.result
        Added test for BUG#49453
     @ mysql-test/r/subquery_sj_none_jcl6.result
        Added test for BUG#49453
     @ mysql-test/r/subquery_sj_none_jcl7.result
        Added test for BUG#49453
     @ sql/sql_select.cc
        Use semijoin's sj_inner_exprs list instead of the subquery's
        item_list in optimize_semijoin_nest() since the item_list is
        not re-resolved on second execution of PS.
[9 Aug 2010 7:35] 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/115279

3223 Jorgen Loland	2010-08-09
      Bug#49453: "re-execution of prepared statement with 
                  view and semijoin crashes"
      
      When semijoin was applied on a view, the replaced items were
      pulled out instead of Item_field since the items referring
      to view columns had already been fixed. However, the semijoin
      nest still stored the item list in sj_subq_pred, and on next
      execution of the PS, this item list was reset during parsing.
      Since this list is not attached to the query, Item_fields 
      referring to view columns were not replaced with 
      Item_direct_view_ref as would happen in normal execution.
      
      The fix for this is to use the sj_inner_exprs list of the 
      semijoin nest instead of the item list. The sj_inner_exprs list
      contains the resolved items in the subquery.
     @ mysql-test/include/subquery_sj.inc
        Added test for BUG#49453
     @ mysql-test/r/subquery_sj_all.result
        Added test for BUG#49453
     @ mysql-test/r/subquery_sj_all_jcl6.result
        Added test for BUG#49453
     @ mysql-test/r/subquery_sj_all_jcl7.result
        Added test for BUG#49453
     @ mysql-test/r/subquery_sj_dupsweed.result
        Added test for BUG#49453
     @ mysql-test/r/subquery_sj_dupsweed_jcl6.result
        Added test for BUG#49453
     @ mysql-test/r/subquery_sj_dupsweed_jcl7.result
        Added test for BUG#49453
     @ mysql-test/r/subquery_sj_firstmatch.result
        Added test for BUG#49453
     @ mysql-test/r/subquery_sj_firstmatch_jcl6.result
        Added test for BUG#49453
     @ mysql-test/r/subquery_sj_firstmatch_jcl7.result
        Added test for BUG#49453
     @ mysql-test/r/subquery_sj_loosescan.result
        Added test for BUG#49453
     @ mysql-test/r/subquery_sj_loosescan_jcl6.result
        Added test for BUG#49453
     @ mysql-test/r/subquery_sj_loosescan_jcl7.result
        Added test for BUG#49453
     @ mysql-test/r/subquery_sj_mat.result
        Added test for BUG#49453
     @ mysql-test/r/subquery_sj_mat_jcl6.result
        Added test for BUG#49453
     @ mysql-test/r/subquery_sj_mat_jcl7.result
        Added test for BUG#49453
     @ mysql-test/r/subquery_sj_mat_nosj.result
        Added test for BUG#49453
     @ mysql-test/r/subquery_sj_none.result
        Added test for BUG#49453
     @ mysql-test/r/subquery_sj_none_jcl6.result
        Added test for BUG#49453
     @ mysql-test/r/subquery_sj_none_jcl7.result
        Added test for BUG#49453
     @ sql/sql_select.cc
        Use semijoin's sj_inner_exprs list instead of the subquery's
        item_list in optimize_semijoin_nest() since the item_list is
        not re-resolved on second execution of PS.
[9 Aug 2010 7:41] Jørgen Løland
Pushed to mysql-next-mr-opt-backporting
[2 Oct 2010 18:15] Bugs System
Pushed into mysql-next-mr (revid:alexander.nozdrin@oracle.com-20101002181053-6iotvl26uurcoryp) (version source revid:alexander.nozdrin@oracle.com-20101002180917-h0n62akupm3z20nt) (pib:21)
[13 Nov 2010 16:10] 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 3:06] Paul DuBois
Bug does not appear in any released 5.6.x version. No 5.6.1 changelog entry needed.