Backport into build-200909301147-5.0.84sp1

> ------------------------------------------------------------
> revno: 2791.2.3
> revision-id: joro@sun.com-20090827114042-h55n7qp9990bl6ge
> parent: anurag.shekhar@sun.com-20090831073231-e55y1hsck6n08ux8
> committer: Georgi Kodinov <joro@sun.com>
> branch nick: B46749-5.0-bugteam
> timestamp: Thu 2009-08-27 14:40:42 +0300
> message:
>   Bug #46749: Segfault in add_key_fields() with outer subquery level 
>     field references
>   
>   This error requires a combination of factors : 
>   1. An "impossible where" in the outermost SELECT
>   2. An aggregate in the outermost SELECT
>   3. A correlated subquery with a WHERE clause that includes an outer 
>   field reference as a top level WHERE sargable predicate
>   
>   When JOIN::optimize detects an "impossible WHERE" it will bail out
>   without doing the rest of the work and initializations. It will not
>   call make_join_statistics() as well.  And make_join_statistics fills 
>   in various structures for each table referenced.
>   When processing the result of the "impossible WHERE" the query must
>   send a single row of data if there are aggregate functions in it.
>   In this case the server marks all the aggregates as having received 
>   no rows and calls the relevant Item::val_xxx() method on the SELECT
>   list. However if this SELECT list happens to contain a correlated 
>   subquery this subquery is evaluated in a normal evaluation mode.
>   And if this correlated subquery has a reference to a field from the 
>   outermost "impossible where" SELECT the add_key_fields will mistakenly
>   consider the outer field reference as a "local" field reference when 
>   looking for sargable predicates.
>   But since the SELECT where the outer field reference refers to is not
>   completely initialized due to the "impossible WHERE" in this level
>   we'll get a NULL pointer reference.
>   Fixed by making a better condition for discovering if a field is "local"
>   to the SELECT level being processed. 
>   It's not enough to look for OUTER_REF_TABLE_BIT in this case since 
>   for outer references to constant tables the Item_field::used_tables() 
>   will return 0 regardless of whether the field reference is from the 
>   local SELECT or not.

3 files changed, 88 insertions, 12 deletions

diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result
index 671e5d8f532..213695fa004 100644
--- a/mysql-test/r/subselect.result
+++ b/mysql-test/r/subselect.result
@@ -4452,4 +4452,32 @@ WHERE 1 IN (SELECT id FROM t1) WITH CHECK OPTION;
 DELETE FROM v3;
 DROP VIEW v1,v2,v3;
 DROP TABLE t1,t2;
+#
+# Bug #46749: Segfault in add_key_fields() with outer subquery level 
+#   field references
+#
+CREATE TABLE t1 (
+a int,
+b int,
+UNIQUE (a), KEY (b)
+);
+INSERT INTO t1 VALUES (1,1), (2,1);
+CREATE TABLE st1 like t1;
+INSERT INTO st1 VALUES (1,1), (2,1);
+CREATE TABLE st2 like t1;
+INSERT INTO st2 VALUES (1,1), (2,1);
+EXPLAIN
+SELECT MAX(b), (SELECT COUNT(*) FROM st1,st2 WHERE st2.b <= t1.b)
+FROM t1 
+WHERE a = 230;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
+2	DEPENDENT SUBQUERY	st1	index	NULL	a	5	NULL	2	Using index
+2	DEPENDENT SUBQUERY	st2	index	b	b	5	NULL	2	Using where; Using index
+SELECT MAX(b), (SELECT COUNT(*) FROM st1,st2 WHERE st2.b <= t1.b)
+FROM t1 
+WHERE a = 230;
+MAX(b)	(SELECT COUNT(*) FROM st1,st2 WHERE st2.b <= t1.b)
+NULL	0
+DROP TABLE t1, st1, st2;
 End of 5.0 tests.
diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test
index 96e5738526b..2b19841d170 100644
--- a/mysql-test/t/subselect.test
+++ b/mysql-test/t/subselect.test
@@ -3428,4 +3428,35 @@ DELETE FROM v3;
 DROP VIEW v1,v2,v3;
 DROP TABLE t1,t2;
 
+--echo #
+--echo # Bug #46749: Segfault in add_key_fields() with outer subquery level 
+--echo #   field references
+--echo #
+
+CREATE TABLE t1 (
+  a int,
+  b int,
+  UNIQUE (a), KEY (b)
+);
+INSERT INTO t1 VALUES (1,1), (2,1);
+
+CREATE TABLE st1 like t1;
+INSERT INTO st1 VALUES (1,1), (2,1);
+
+CREATE TABLE st2 like t1;
+INSERT INTO st2 VALUES (1,1), (2,1);
+
+# should have "impossible where"
+EXPLAIN
+SELECT MAX(b), (SELECT COUNT(*) FROM st1,st2 WHERE st2.b <= t1.b)
+FROM t1 
+WHERE a = 230;
+
+# should not crash
+SELECT MAX(b), (SELECT COUNT(*) FROM st1,st2 WHERE st2.b <= t1.b)
+FROM t1 
+WHERE a = 230;
+
+DROP TABLE t1, st1, st2;
+
 --echo End of 5.0 tests.
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index 672ebaf9259..20bff90612d 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -3197,6 +3197,28 @@ add_key_equal_fields(KEY_FIELD **key_fields, uint and_level,
   }
 }
 
+
+/**
+  Check if an expression is a non-outer field.
+
+  Checks if an expression is a field and belongs to the current select.
+
+  @param   field  Item expression to check
+
+  @return boolean
+     @retval TRUE   the expression is a local field
+     @retval FALSE  it's something else
+*/
+
+inline static bool
+is_local_field (Item *field)
+{
+  field= field->real_item();
+  return field->type() == Item::FIELD_ITEM && 
+    !((Item_field *)field)->depended_from;
+}
+
+
 static void
 add_key_fields(JOIN *join, KEY_FIELD **key_fields, uint *and_level,
                COND *cond, table_map usable_tables,
@@ -3272,13 +3294,12 @@ add_key_fields(JOIN *join, KEY_FIELD **key_fields, uint *and_level,
   {
     Item **values;
     // BETWEEN, IN, NE
-    if (cond_func->key_item()->real_item()->type() == Item::FIELD_ITEM &&
+    if (is_local_field (cond_func->key_item()) &&
 	!(cond_func->used_tables() & OUTER_REF_TABLE_BIT))
     {
       values= cond_func->arguments()+1;
       if (cond_func->functype() == Item_func::NE_FUNC &&
-        cond_func->arguments()[1]->real_item()->type() == Item::FIELD_ITEM &&
-	     !(cond_func->arguments()[0]->used_tables() & OUTER_REF_TABLE_BIT))
+        is_local_field (cond_func->arguments()[1]))
         values--;
       DBUG_ASSERT(cond_func->functype() != Item_func::IN_FUNC ||
                   cond_func->argument_count() != 2);
@@ -3294,9 +3315,7 @@ add_key_fields(JOIN *join, KEY_FIELD **key_fields, uint *and_level,
       for (uint i= 1 ; i < cond_func->argument_count() ; i++)
       {
         Item_field *field_item;
-        if (cond_func->arguments()[i]->real_item()->type() == Item::FIELD_ITEM
-            &&
-            !(cond_func->arguments()[i]->used_tables() & OUTER_REF_TABLE_BIT))
+        if (is_local_field (cond_func->arguments()[i]))
         {
           field_item= (Item_field *) (cond_func->arguments()[i]->real_item());
           add_key_equal_fields(key_fields, *and_level, cond_func,
@@ -3312,8 +3331,7 @@ add_key_fields(JOIN *join, KEY_FIELD **key_fields, uint *and_level,
     bool equal_func=(cond_func->functype() == Item_func::EQ_FUNC ||
 		     cond_func->functype() == Item_func::EQUAL_FUNC);
 
-    if (cond_func->arguments()[0]->real_item()->type() == Item::FIELD_ITEM &&
-	!(cond_func->arguments()[0]->used_tables() & OUTER_REF_TABLE_BIT))
+    if (is_local_field (cond_func->arguments()[0]))
     {
       add_key_equal_fields(key_fields, *and_level, cond_func,
 	                (Item_field*) (cond_func->arguments()[0])->real_item(),
@@ -3321,9 +3339,8 @@ add_key_fields(JOIN *join, KEY_FIELD **key_fields, uint *and_level,
                            cond_func->arguments()+1, 1, usable_tables,
                            sargables);
     }
-    if (cond_func->arguments()[1]->real_item()->type() == Item::FIELD_ITEM &&
-	cond_func->functype() != Item_func::LIKE_FUNC &&
-	!(cond_func->arguments()[1]->used_tables() & OUTER_REF_TABLE_BIT))
+    if (is_local_field (cond_func->arguments()[1]) &&
+	cond_func->functype() != Item_func::LIKE_FUNC)
     {
       add_key_equal_fields(key_fields, *and_level, cond_func, 
                        (Item_field*) (cond_func->arguments()[1])->real_item(),
@@ -3335,7 +3352,7 @@ add_key_fields(JOIN *join, KEY_FIELD **key_fields, uint *and_level,
   }
   case Item_func::OPTIMIZE_NULL:
     /* column_name IS [NOT] NULL */
-    if (cond_func->arguments()[0]->real_item()->type() == Item::FIELD_ITEM &&
+    if (is_local_field (cond_func->arguments()[0]) &&
 	!(cond_func->used_tables() & OUTER_REF_TABLE_BIT))
     {
       Item *tmp=new Item_null;