4 files changed, 93 insertions, 7 deletions

diff --git a/sql/opt_trace.cc b/sql/opt_trace.cc
index 2d14972aba5..a49273c0a9f 100644
--- a/sql/opt_trace.cc
+++ b/sql/opt_trace.cc
@@ -689,6 +689,28 @@ void print_on_expr(JOIN *join, Json_writer_array *trace_on_expr)
 }
 
 
+void print_on_expr(THD *thd, List<TABLE_LIST> *join_list,
+                   Json_writer_array *trace_array)
+{
+  if (join_list == NULL)
+    return;
+  TABLE_LIST *table;
+  List_iterator<TABLE_LIST> li(*join_list);
+
+  while ((table= li++))
+  {
+    if (table->on_expr)
+    {
+      List<TABLE_LIST> *nested_join_list= table->nested_join ?
+        &table->nested_join->join_list : NULL;
+
+      trace_array->add(table->on_expr);
+      print_on_expr(thd, nested_join_list, trace_array);
+    }
+  }
+}
+
+
 /*
   Introduce enum_query_type flags parameter, maybe also allow
   EXPLAIN also use this function.
diff --git a/sql/opt_trace.h b/sql/opt_trace.h
index 37053971ee3..c421d6a5036 100644
--- a/sql/opt_trace.h
+++ b/sql/opt_trace.h
@@ -108,6 +108,8 @@ void print_final_join_order(JOIN *join);
 void print_best_access_for_table(THD *thd, POSITION *pos,
                                  enum join_type type);
 void print_on_expr(JOIN *join, Json_writer_array *trace_on_expr);
+void print_on_expr(THD *thd, List<TABLE_LIST> *join_list,
+                   Json_writer_array *trace_array);
 /*
   Security related (need to add a proper comment here)
 */
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index 3830cc4ea25..13dce02f160 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -170,6 +170,12 @@ static COND *optimize_cond(JOIN *join, COND *conds,
                            Item::cond_result *cond_value, 
                            COND_EQUAL **cond_equal,
                            int flags= 0);
+
+static void optimize_on_expr(JOIN *join,
+                             List<TABLE_LIST> *join_list,
+                             bool ignore_on_conds,
+                             int flags= 0);
+
 bool const_expression_in_where(COND *conds,Item *item, Item **comp_item);
 static int do_select(JOIN *join, Procedure *procedure);
 
@@ -2014,9 +2020,11 @@ JOIN::optimize_inner()
         ignore_on_expr= true;
         break;
       }
-  conds= optimize_cond(this, conds, join_list, ignore_on_expr,
+  conds= optimize_cond(this, conds, join_list, TRUE,
                        &cond_value, &cond_equal, OPT_LINK_EQUAL_FIELDS);
 
+  optimize_on_expr(this, join_list, ignore_on_expr);
+
   if (thd->is_error())
   {
     error= 1;
@@ -2472,11 +2480,19 @@ int JOIN::optimize_stage2()
 
   if (unlikely(thd->trace_started()))
   {
-    Json_writer_object trace_wrapper(thd);
-    trace_wrapper.add("where_clause_after_substitution", conds);
-    trace_wrapper.add("having_clause_after_substitution", having);
-    Json_writer_array trace_on_expr(thd, "on_clause_after_substitution");
-    print_on_expr(this, &trace_on_expr);
+    if (having || conds || outer_join)
+    {
+      Json_writer_object trace_wrapper(thd);
+      if (conds)
+        trace_wrapper.add("where_clause_after_substitution", conds);
+      if (having)
+        trace_wrapper.add("having_clause_after_substitution", having);
+      if (outer_join)
+      {
+        Json_writer_array trace_on_expr(thd, "on_clause_after_substitution");
+        print_on_expr(this, &trace_on_expr);
+      }
+    }
   }
 
   /*
@@ -17007,6 +17023,52 @@ optimize_cond(JOIN *join, COND *conds,
 }
 
 
+static void optimize_on_expr(JOIN *join,
+                             List<TABLE_LIST> *join_list,
+                             bool ignore_on_conds,
+                             int flags)
+{
+  if (ignore_on_conds || !join_list)
+    return;
+
+  THD *thd= join->thd;
+  Json_writer_object trace_wrapper(thd);
+  Json_writer_object trace_cond(thd, "condition_processing");
+  trace_cond.add("condition", "ON CLAUSE");
+  {
+    Json_writer_array trace_array(thd, "original_expr");
+    print_on_expr(thd, join_list, &trace_array);
+  }
+
+  if (join_list && !ignore_on_conds)
+  {
+    TABLE_LIST *table;
+    List_iterator<TABLE_LIST> li(*join_list);
+
+    while ((table= li++))
+    {
+      if (table->on_expr)
+      {
+        List<TABLE_LIST> *nested_join_list= table->nested_join ?
+          &table->nested_join->join_list : NULL;
+        /*
+          We can modify table->on_expr because its old value will
+          be restored before re-execution of PS/SP.
+        */
+        table->on_expr= build_equal_items(join, table->on_expr, join->cond_equal,
+                                          nested_join_list, ignore_on_conds,
+                                          &table->cond_equal);
+      }
+    }
+  }
+
+  {
+    Json_writer_array trace_array(thd, "transformed_expr");
+    print_on_expr(thd, join_list, &trace_array);
+  }
+}
+
+
 /**
   @brief
   Propagate multiple equalities to the sub-expressions of a condition
diff --git a/sql/sys_vars.cc b/sql/sys_vars.cc
index 5f5f9b5daf8..fea924f28fe 100644
--- a/sql/sys_vars.cc
+++ b/sql/sys_vars.cc
@@ -2638,7 +2638,7 @@ static Sys_var_flagset Sys_optimizer_trace(
     " {enabled}"
     " and val is one of {on, off, default}",
     SESSION_VAR(optimizer_trace), CMD_LINE(REQUIRED_ARG),
-    Opt_trace_context::flag_names, DEFAULT(Opt_trace_context::FLAG_DEFAULT));
+    Opt_trace_context::flag_names, DEFAULT(Opt_trace_context::FLAG_ENABLED));
     // @see set_var::is_var_optimizer_trace()
 export sys_var *Sys_optimizer_trace_ptr = &Sys_optimizer_trace;