bk commit into 5.0 tree (evgen:1.2144) BUG#19077

[eugene]

Below is the list of changes that have just been committed into a local
5.0 repository of evgen. When evgen does a push these changes will
be propagated to the main repository and, within 24 hours after the
push, to the public repository.
For information on how to access the public repository
see http://dev.mysql.com/doc/mysql/en/installing-source-tree.html

ChangeSet
  1.2144 06/05/18 00:12:00 evgen@stripped +7 -0
  Fixed bug#19077: A nested materialized derived table is used before being populated.
  
  The convert_constant_item() functions 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 contained in a derived table. 
  All derived tables are filled only after all derived tables are prepared.
  So evaluation of subselect with derived table on the prepare phase will
  return wrong result.
  
  New flag with_subselect is added to the Item class. It shows 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
  with the with_subselect flag set. 

  sql/item.cc
    1.223 06/05/18 00:10:31 evgen@stripped +1 -0
    Fixed bug#19077: A nested materialized derived table is used before being populated.
    Set new with_subselect flag to default value - 0 at the Item constructor.

  sql/item.h
    1.197 06/05/18 00:09:25 evgen@stripped +3 -0
    Fixed bug#19077: A nested materialized derived table is used before being populated.
    New flag with_subselect is added to the Item class. It shows that expression
    which this item represents is a subselect or contains a subselect.
    It is set to 0 by default.

  sql/item_cmpfunc.cc
    1.201 06/05/18 00:08:24 evgen@stripped +24 -5
    Fixed bug#19077: A nested materialized derived table is used before being populated.
    The convert_constant_item() function now doesn't convert a constant item
    with the with_subselect flag set.
    The Item_cond::fix_fields() sets new with_subselect flag from with_subselect flags of
its arguments.

  mysql-test/r/subselect.result
    1.143 06/05/18 00:08:07 evgen@stripped +6 -0
    Added test case for bug#19077: A nested materialized derived table is used before
being populated.

  mysql-test/t/subselect.test
    1.118 06/05/17 23:53:54 evgen@stripped +9 -0
    Added test case for bug#19077: A nested materialized derived table is used before
being populated.

  sql/item_func.cc
    1.287 06/05/17 23:46:16 evgen@stripped +1 -0
    Fixed bug#19077: A nested materialized derived table is used before being populated.
    
    The Item_func::fix_fields() sets new with_subselect flag from with_subselect flags of
its arguments.

  sql/item_subselect.cc
    1.125 06/05/17 23:45:23 evgen@stripped +1 -0
    Fixed bug#19077: A nested materialized derived table is used before being populated.
    The Item_subselect class constructor sets new with_subselect flag to 1.

# This is a BitKeeper patch.  What follows are the unified diffs for the
# set of deltas contained in the patch.  The rest of the patch, the part
# that BitKeeper cares about, is below these diffs.
# User:	evgen
# Host:	moonbone.local
# Root:	/work/19077-bug-5.0-mysql

--- 1.222/sql/item.cc	2006-05-16 03:44:39 +04:00
+++ 1.223/sql/item.cc	2006-05-18 00:10:31 +04:00
@@ -304,6 +304,7 @@
   marker= 0;
   maybe_null=null_value=with_sum_func=unsigned_flag=0;
   decimals= 0; max_length= 0;
+  with_subselect= 0;
 
   /* Put item in free list so that we can free all items at end */
   THD *thd= current_thd;

--- 1.196/sql/item.h	2006-05-16 03:44:39 +04:00
+++ 1.197/sql/item.h	2006-05-18 00:09:25 +04:00
@@ -460,6 +460,9 @@
   my_bool is_autogenerated_name;        /* indicate was name of this Item
                                            autogenerated or set by user */
   DTCollation collation;
+  my_bool with_subselect;               /* If this item is a subselect or some
+                                           of its arguments is or contains a
+                                           subselect */
 
   // alloc & destruct is done as start of select using sql_alloc
   Item();

--- 1.200/sql/item_cmpfunc.cc	2006-04-25 01:50:25 +04:00
+++ 1.201/sql/item_cmpfunc.cc	2006-05-18 00:08:24 +04:00
@@ -204,10 +204,28 @@
 
 
 /*
-  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 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 @@
 
 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 @@
       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;
   }

--- 1.286/sql/item_func.cc	2006-05-15 15:28:10 +04:00
+++ 1.287/sql/item_func.cc	2006-05-17 23:46:16 +04:00
@@ -184,6 +184,7 @@
       used_tables_cache|=     item->used_tables();
       not_null_tables_cache|= item->not_null_tables();
       const_item_cache&=      item->const_item();
+      with_subselect|=        item->with_subselect;
     }
   }
   fix_length_and_dec();

--- 1.142/mysql-test/r/subselect.result	2006-05-12 18:03:49 +04:00
+++ 1.143/mysql-test/r/subselect.result	2006-05-18 00:08:07 +04:00
@@ -3177,3 +3177,9 @@
 SELECT * FROM t1 WHERE no_such_column = ANY (SELECT 1);
 ERROR 42S22: Unknown column 'no_such_column' in 'IN/ALL/ANY subquery'
 DROP TABLE t1;
+create table t1 (i int, j bigint);
+insert into t1 values (1, 2), (2, 2), (3, 2);
+select * from (select min(i) from t1 where j=(select * from (select min(j) from t1) t2))
t3;
+min(i)
+1
+drop table t1;

--- 1.117/mysql-test/t/subselect.test	2006-05-11 16:30:49 +04:00
+++ 1.118/mysql-test/t/subselect.test	2006-05-17 23:53:54 +04:00
@@ -2100,3 +2100,12 @@
 SELECT * FROM t1 WHERE no_such_column = ANY (SELECT 1);
 
 DROP TABLE t1;
+
+#
+# Bug#19077: A nested materialized derived table is used before being populated.
+#
+create table t1 (i int, j bigint);
+insert into t1 values (1, 2), (2, 2), (3, 2);
+select * from (select min(i) from t1 where j=(select * from (select min(j) from t1) t2))
t3;
+drop table t1;
+

--- 1.124/sql/item_subselect.cc	2006-05-11 16:30:49 +04:00
+++ 1.125/sql/item_subselect.cc	2006-05-17 23:45:23 +04:00
@@ -39,6 +39,7 @@
   engine(0), old_engine(0), used_tables_cache(0), have_to_be_excluded(0),
   const_item_cache(1), engine_changed(0), changed(0)
 {
+  with_subselect= 1;
   reset();
   /*
     item value is NULL if select_subselect not changed this value