1 files changed, 37 insertions, 40 deletions

diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index 7850c7a0dcf..d459d0b88bf 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -8510,8 +8510,6 @@ get_best_combination(JOIN *join)
     form=join->table[tablenr]=j->table;
     used_tables|= form->map;
     form->reginfo.join_tab=j;
-    if (!*j->on_expr_ref)
-      form->reginfo.not_exists_optimize=0;	// Only with LEFT JOIN
     DBUG_PRINT("info",("type: %d", j->type));
     if (j->type == JT_CONST)
       goto loop_end;					// Handled in make_join_stat..
@@ -9355,7 +9353,10 @@ make_outerjoin_info(JOIN *join)
       tab->cond_equal= tbl->cond_equal;
       if (embedding && !embedding->is_active_sjm())
         tab->first_upper= embedding->nested_join->first_nested;
-    }    
+    }
+    else if (!embedding)
+      tab->table->reginfo.not_exists_optimize= 0;
+          
     for ( ; embedding ; embedding= embedding->embedding)
     {
       if (embedding->is_active_sjm())
@@ -9365,7 +9366,10 @@ make_outerjoin_info(JOIN *join)
       }
       /* Ignore sj-nests: */
       if (!(embedding->on_expr && embedding->outer_join))
+      {
+        tab->table->reginfo.not_exists_optimize= 0;
         continue;
+      }
       NESTED_JOIN *nested_join= embedding->nested_join;
       if (!nested_join->counter)
       {
@@ -9381,17 +9385,10 @@ make_outerjoin_info(JOIN *join)
       }
       if (!tab->first_inner)  
         tab->first_inner= nested_join->first_nested;
-      if (tab->table->reginfo.not_exists_optimize)
-        tab->first_inner->table->reginfo.not_exists_optimize= 1;         
       if (++nested_join->counter < nested_join->n_tables)
         break;
       /* Table tab is the last inner table for nested join. */
       nested_join->first_nested->last_inner= tab;
-      if (tab->first_inner->table->reginfo.not_exists_optimize)
-      {
-        for (JOIN_TAB *join_tab= tab->first_inner; join_tab <= tab; join_tab++)
-          join_tab->table->reginfo.not_exists_optimize= 1;
-      } 
     }
   }
   DBUG_RETURN(FALSE);
@@ -15510,7 +15507,9 @@ static Field *create_tmp_field_from_item(THD *thd, Item *item, TABLE *table,
   if (new_field)
     new_field->init(table);
     
-  if (copy_func && item->real_item()->is_result_field())
+  if (copy_func &&
+      (item->is_result_field() || 
+       (item->real_item()->is_result_field())))
     *((*copy_func)++) = item;			// Save for copy_funcs
   if (modify_item)
     item->set_result_field(new_field);
@@ -17982,32 +17981,41 @@ evaluate_join_record(JOIN *join, JOIN_TAB *join_tab,
       first_unmatched->found= 1;
       for (JOIN_TAB *tab= first_unmatched; tab <= join_tab; tab++)
       {
+        /*
+          Check whether 'not exists' optimization can be used here.
+          If  tab->table->reginfo.not_exists_optimize is set to true
+          then WHERE contains a conjunctive predicate IS NULL over
+          a non-nullable field of tab. When activated this predicate
+          will filter out all records with matches for the left part
+          of the outer join whose inner tables start from the
+          first_unmatched table and include table tab. To safely use
+          'not exists' optimization we have to check that the
+          IS NULL predicate is really activated, i.e. all guards
+          that wrap it are in the 'open' state. 
+	*/  
+	bool not_exists_opt_is_applicable=
+               tab->table->reginfo.not_exists_optimize;
+	for (JOIN_TAB *first_upper= first_unmatched->first_upper;
+             not_exists_opt_is_applicable && first_upper;
+             first_upper= first_upper->first_upper)
+        {
+          if (!first_upper->found)
+            not_exists_opt_is_applicable= false;
+        }
         /* Check all predicates that has just been activated. */
         /*
           Actually all predicates non-guarded by first_unmatched->found
           will be re-evaluated again. It could be fixed, but, probably,
           it's not worth doing now.
         */
-        /*
-          not_exists_optimize has been created from a
-          select_cond containing 'is_null'. This 'is_null'
-          predicate is still present on any 'tab' with
-          'not_exists_optimize'. Furthermore, the usual rules
-          for condition guards also applies for
-          'not_exists_optimize' -> When 'is_null==false' we
-          know all cond. guards are open and we can apply
-          the 'not_exists_optimize'.
-        */
-        DBUG_ASSERT(!(tab->table->reginfo.not_exists_optimize &&
-                     !tab->select_cond));
-
         if (tab->select_cond && !tab->select_cond->val_int())
         {
           /* The condition attached to table tab is false */
-
           if (tab == join_tab)
           {
             found= 0;
+            if (not_exists_opt_is_applicable)
+              DBUG_RETURN(NESTED_LOOP_NO_MORE_ROWS);
           }            
           else
           {
@@ -18016,21 +18024,10 @@ evaluate_join_record(JOIN *join, JOIN_TAB *join_tab,
               not to the last table of the current nest level.
             */
             join->return_tab= tab;
-          }
-
-          if (tab->table->reginfo.not_exists_optimize)
-          {
-            /*
-              When not_exists_optimize is set: No need to further
-              explore more rows of 'tab' for this partial result.
-              Any found 'tab' matches are known to evaluate to 'false'.
-              Returning .._NO_MORE_ROWS will skip rem. 'tab' rows.
-            */
-            DBUG_RETURN(NESTED_LOOP_NO_MORE_ROWS);
-          }
-          else if (tab != join_tab)
-          {
-            DBUG_RETURN(NESTED_LOOP_OK);
+            if (not_exists_opt_is_applicable)
+              DBUG_RETURN(NESTED_LOOP_NO_MORE_ROWS);
+            else
+              DBUG_RETURN(NESTED_LOOP_OK);
           }
         }
       }