Fixed bug#19077: A nested materialized derived table is used before being populated.

The convert_constant_item() function converts constant items to ints on
prepare phase to optimize execution speed. In this case it tries to evaluate
subselect which contains a derived table and is contained in a derived table. 
All derived tables are filled only after all derived tables are prepared.
So evaluation of subselect with derived table at the prepare phase will
return a wrong result.

A new flag with_subselect is added to the Item class. It indicates that
expression which this item represents is a subselect or contains a subselect.
It is set to 0 by default. It is set to 1 in the Item_subselect constructor
for subselects.
For Item_func and Item_cond derived classes it is set after fixing any argument
in Item_func::fix_fields() and Item_cond::fix_fields accordingly.
The convert_constant_item() function now doesn't convert a constant item
if the with_subselect flag set in it. 

1 files changed, 24 insertions, 5 deletions

diff --git a/sql/item_cmpfunc.cc b/sql/item_cmpfunc.cc
index 54e281a1f14..ca5d79973e5 100644
--- a/sql/item_cmpfunc.cc
+++ b/sql/item_cmpfunc.cc
@@ -204,10 +204,28 @@ longlong Item_func_nop_all::val_int()
 
 
 /*
-  Convert a constant expression or string to an integer.
-  This is done when comparing DATE's of different formats and
-  also when comparing bigint to strings (in which case the string
-  is converted once to a bigint).
+  Convert a constant item to an int and replace the original item
+
+  SYNOPSIS
+    convert_constant_item()
+    thd             thread handle
+    field           item will be converted using the type of this field
+    item  [in/out]  reference to the item to convert
+
+  DESCRIPTION
+    The function converts a constant expression or string to an integer.
+    On successful conversion the original item is substituted for the
+    result of the item evaluation.
+    This is done when comparing DATE/TIME of different formats and
+    also when comparing bigint to strings (in which case strings
+    are converted to bigints).
+
+  NOTES
+    This function is called only at prepare stage.
+    As all derived tables are filled only after all derived tables
+    are prepared we do not evaluate items with subselects here because
+    they can contain derived tables and thus we may attempt to use a
+    table that has not been populated yet.
 
   RESULT VALUES
   0	Can't convert item
@@ -216,7 +234,7 @@ longlong Item_func_nop_all::val_int()
 
 static bool convert_constant_item(THD *thd, Field *field, Item **item)
 {
-  if ((*item)->const_item())
+  if (!(*item)->with_subselect && (*item)->const_item())
   {
     /* For comparison purposes allow invalid dates like 2000-01-32 */
     ulong orig_sql_mode= field->table->in_use->variables.sql_mode;
@@ -2578,6 +2596,7 @@ Item_cond::fix_fields(THD *thd, Item **ref)
       const_item_cache= FALSE;
     }  
     with_sum_func=	    with_sum_func || item->with_sum_func;
+    with_subselect|=        item->with_subselect;
     if (item->maybe_null)
       maybe_null=1;
   }