Bug#17029399 - CRASH IN ITEM_REF::FIX_FIELDS WITH TRIGGER ERRORS

Problem:-
In a Procedure, when we are comparing value of select query 
with IN clause and they both have different collation, cause 
error on first time execution and assert second time.
procedure will have query like
set @x = ((select a from t1) in (select d from t2));<---proc1
              sel1                   sel2

Analysis:-
When we execute this proc1(first time)
While resolving the fields of user variable, we will call 
Item_in_subselect::fix_fields while will resolve sel2. There 
in Item_in_subselect::select_transformer, we evaluate the 
left expression(sel1) and store it in Item_cache_* object 
(to avoid re-evaluating it many times during subquery execution) 
by making Item_in_optimizer class.
While evaluating left expression we will prepare sel1.
After that, we will put a new condition in sel2  
in Item_in_subselect::select_transformer() which will compare 
t2.d and sel1(which is cached in Item_in_optimizer).

Later while checking the collation in agg_item_collations() 
we get error and we cleanup the item. While cleaning up we cleaned 
the cached value in Item_in_optimizer object.

When we execute the procedure second time, we have condition for 
sel2 and while setup_cond(), we can't able to find reference item 
as it is cleanup while item cleanup.So it assert.


Solution:-
We should not cleanup the cached value for Item_in_optimizer object, 
if we have put the condition to subselect.

1 files changed, 4 insertions, 1 deletions

diff --git a/sql/item_subselect.cc b/sql/item_subselect.cc
index d2c3f0db230..1cd4175f9df 100644
--- a/sql/item_subselect.cc
+++ b/sql/item_subselect.cc
@@ -1,4 +1,4 @@
-/* Copyright (c) 2002, 2012, Oracle and/or its affiliates. All rights reserved.
+/* Copyright (c) 2002, 2013, Oracle and/or its affiliates. All rights reserved.
 
    This program is free software; you can redistribute it and/or modify
    it under the terms of the GNU General Public License as published by
@@ -1083,6 +1083,9 @@ Item_in_subselect::single_value_transformer(JOIN *join,
     }
     thd->lex->current_select= current;
 
+    /* We will refer to upper level cache array => we have to save it for SP */
+    optimizer->keep_top_level_cache();
+
     /*
       As far as  Item_ref_in_optimizer do not substitute itself on fix_fields
       we can use same item for all selects.