MDEV-23646: Optimizer trace: optimize_cond() should show ON expression processing10.4-opt_trace

Print in the optimizer trace equality and constant propagation of the ON expression

10 files changed, 359 insertions, 13 deletions

diff --git a/mysql-test/main/opt_trace.result b/mysql-test/main/opt_trace.result
index fdf2d2a338a..94bf20aaa4f 100644
--- a/mysql-test/main/opt_trace.result
+++ b/mysql-test/main/opt_trace.result
@@ -6,7 +6,7 @@ OPTIMIZER_TRACE	MISSING_BYTES_BEYOND_MAX_MEM_SIZE
 OPTIMIZER_TRACE	INSUFFICIENT_PRIVILEGES
 show variables like 'optimizer_trace';
 Variable_name	Value
-optimizer_trace	enabled=off
+optimizer_trace	enabled=on
 set optimizer_trace="enabled=on";
 show variables like 'optimizer_trace';
 Variable_name	Value
@@ -89,6 +89,13 @@ select * from v1	{
             }
           },
           {
+            "condition_processing": {
+              "condition": "ON CLAUSE",
+              "original_expr": [],
+              "transformed_expr": []
+            }
+          },
+          {
             "table_dependencies": [
               {
                 "table": "t1",
@@ -240,6 +247,13 @@ select * from (select * from t1 where t1.a=1)q	{
             }
           },
           {
+            "condition_processing": {
+              "condition": "ON CLAUSE",
+              "original_expr": [],
+              "transformed_expr": []
+            }
+          },
+          {
             "table_dependencies": [
               {
                 "table": "t1",
@@ -372,6 +386,13 @@ select * from v2	{
         "select_id": 1,
         "steps": [
           {
+            "condition_processing": {
+              "condition": "ON CLAUSE",
+              "original_expr": [],
+              "transformed_expr": []
+            }
+          },
+          {
             "join_optimization": {
               "select_id": 2,
               "steps": [
@@ -396,6 +417,13 @@ select * from v2	{
                   }
                 },
                 {
+                  "condition_processing": {
+                    "condition": "ON CLAUSE",
+                    "original_expr": [],
+                    "transformed_expr": []
+                  }
+                },
+                {
                   "table_dependencies": [
                     {
                       "table": "t1",
@@ -627,6 +655,13 @@ explain select * from v2	{
         "select_id": 1,
         "steps": [
           {
+            "condition_processing": {
+              "condition": "ON CLAUSE",
+              "original_expr": [],
+              "transformed_expr": []
+            }
+          },
+          {
             "table_dependencies": [
               {
                 "table": "t2",
@@ -746,10 +781,24 @@ explain select * from v1	{
         "select_id": 1,
         "steps": [
           {
+            "condition_processing": {
+              "condition": "ON CLAUSE",
+              "original_expr": [],
+              "transformed_expr": []
+            }
+          },
+          {
             "join_optimization": {
               "select_id": 2,
               "steps": [
                 {
+                  "condition_processing": {
+                    "condition": "ON CLAUSE",
+                    "original_expr": [],
+                    "transformed_expr": []
+                  }
+                },
+                {
                   "table_dependencies": [
                     {
                       "table": "t1",
@@ -970,6 +1019,13 @@ explain select * from t1,t2 where t1.a=t2.b+2 and t2.a= t1.b	{
             }
           },
           {
+            "condition_processing": {
+              "condition": "ON CLAUSE",
+              "original_expr": [],
+              "transformed_expr": []
+            }
+          },
+          {
             "table_dependencies": [
               {
                 "table": "t1",
@@ -1201,6 +1257,13 @@ EXPLAIN SELECT DISTINCT a FROM t1	{
         "select_id": 1,
         "steps": [
           {
+            "condition_processing": {
+              "condition": "ON CLAUSE",
+              "original_expr": [],
+              "transformed_expr": []
+            }
+          },
+          {
             "table_dependencies": [
               {
                 "table": "t1",
@@ -1391,6 +1454,13 @@ EXPLAIN SELECT MIN(d) FROM t1 where b=2 and c=3  group by a	{
             }
           },
           {
+            "condition_processing": {
+              "condition": "ON CLAUSE",
+              "original_expr": [],
+              "transformed_expr": []
+            }
+          },
+          {
             "table_dependencies": [
               {
                 "table": "t1",
@@ -1609,6 +1679,13 @@ EXPLAIN SELECT id,MIN(a),MAX(a) FROM t1 WHERE a>=20010104e0 GROUP BY id	{
             }
           },
           {
+            "condition_processing": {
+              "condition": "ON CLAUSE",
+              "original_expr": [],
+              "transformed_expr": []
+            }
+          },
+          {
             "table_dependencies": [
               {
                 "table": "t1",
@@ -1805,6 +1882,13 @@ EXPLAIN SELECT * FROM t1 WHERE a = 20010104e0 GROUP BY id	{
             }
           },
           {
+            "condition_processing": {
+              "condition": "ON CLAUSE",
+              "original_expr": [],
+              "transformed_expr": []
+            }
+          },
+          {
             "table_dependencies": [
               {
                 "table": "t1",
@@ -2028,6 +2112,13 @@ explain  select * from t1 where a=1 and b=2 order by c limit 1	{
             }
           },
           {
+            "condition_processing": {
+              "condition": "ON CLAUSE",
+              "original_expr": [],
+              "transformed_expr": []
+            }
+          },
+          {
             "table_dependencies": [
               {
                 "table": "t1",
@@ -2384,6 +2475,13 @@ select t1.a from t1 left join t2 on t1.a=t2.a	{
         "select_id": 1,
         "steps": [
           {
+            "condition_processing": {
+              "condition": "ON CLAUSE",
+              "original_expr": ["t1.a = t2.a"],
+              "transformed_expr": ["multiple equal(t1.a, t2.a)"]
+            }
+          },
+          {
             "table_dependencies": [
               {
                 "table": "t1",
@@ -2514,6 +2612,13 @@ explain select * from t1 left join t2 on t2.a=t1.a	{
         "select_id": 1,
         "steps": [
           {
+            "condition_processing": {
+              "condition": "ON CLAUSE",
+              "original_expr": ["t2.a = t1.a"],
+              "transformed_expr": ["multiple equal(t2.a, t1.a)"]
+            }
+          },
+          {
             "table_dependencies": [
               {
                 "table": "t1",
@@ -2679,6 +2784,15 @@ explain select t1.a from t1 left join (t2 join t3 on t2.b=t3.b) on t2.a=t1.a and
         "select_id": 1,
         "steps": [
           {
+            "condition_processing": {
+              "condition": "ON CLAUSE",
+              "original_expr": ["t2.a = t1.a and t3.a = t1.a and t2.b = t3.b"],
+              "transformed_expr": [
+                "multiple equal(t2.a, t1.a, t3.a) and multiple equal(t2.b, t3.b)"
+              ]
+            }
+          },
+          {
             "table_dependencies": [
               {
                 "table": "t1",
@@ -2917,6 +3031,13 @@ explain extended select * from t1 where a in (select pk from t10)	{
             }
           },
           {
+            "condition_processing": {
+              "condition": "ON CLAUSE",
+              "original_expr": [],
+              "transformed_expr": []
+            }
+          },
+          {
             "table_dependencies": [
               {
                 "table": "t1",
@@ -3206,6 +3327,13 @@ explain select * from t1 where pk = 2 and a=5 and b=1	{
             }
           },
           {
+            "condition_processing": {
+              "condition": "ON CLAUSE",
+              "original_expr": [],
+              "transformed_expr": []
+            }
+          },
+          {
             "table_dependencies": [
               {
                 "table": "t1",
@@ -3548,6 +3676,13 @@ select f1(a) from t1	{
         "select_id": 1,
         "steps": [
           {
+            "condition_processing": {
+              "condition": "ON CLAUSE",
+              "original_expr": [],
+              "transformed_expr": []
+            }
+          },
+          {
             "table_dependencies": [
               {
                 "table": "t1",
@@ -3651,6 +3786,13 @@ select f2(a) from t1	{
         "select_id": 1,
         "steps": [
           {
+            "condition_processing": {
+              "condition": "ON CLAUSE",
+              "original_expr": [],
+              "transformed_expr": []
+            }
+          },
+          {
             "table_dependencies": [
               {
                 "table": "t1",
@@ -3746,7 +3888,7 @@ a
 2
 select length(trace) from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
 length(trace)
-2342
+2531
 set optimizer_trace_max_mem_size=100;
 select * from t1;
 a
@@ -3760,7 +3902,7 @@ select * from t1	{
       "join_preparation": {
         "select_id": 1,
         "steps": [
-        	2242	0
+        	2431	0
 set optimizer_trace_max_mem_size=0;
 select * from t1;
 a
@@ -3768,7 +3910,7 @@ a
 2
 select * from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
 QUERY	TRACE	MISSING_BYTES_BEYOND_MAX_MEM_SIZE	INSUFFICIENT_PRIVILEGES
-select * from t1		2342	0
+select * from t1		2531	0
 drop table t1;
 set optimizer_trace='enabled=off';
 set @@optimizer_trace_max_mem_size= @save_optimizer_trace_max_mem_size;
@@ -3893,6 +4035,13 @@ explain delete t0,t1 from t0, t1 where t0.a=t1.a and t1.a<3	{
             }
           },
           {
+            "condition_processing": {
+              "condition": "ON CLAUSE",
+              "original_expr": [],
+              "transformed_expr": []
+            }
+          },
+          {
             "table_dependencies": [
               {
                 "table": "t0",
@@ -4263,10 +4412,24 @@ explain select * from (select rand() from t1)q	{
             }
           },
           {
+            "condition_processing": {
+              "condition": "ON CLAUSE",
+              "original_expr": [],
+              "transformed_expr": []
+            }
+          },
+          {
             "join_optimization": {
               "select_id": 2,
               "steps": [
                 {
+                  "condition_processing": {
+                    "condition": "ON CLAUSE",
+                    "original_expr": [],
+                    "transformed_expr": []
+                  }
+                },
+                {
                   "table_dependencies": [
                     {
                       "table": "t1",
@@ -4514,6 +4677,13 @@ explain select * from t1 where a in (select t_inner_1.a from t1 t_inner_1, t1 t_
             }
           },
           {
+            "condition_processing": {
+              "condition": "ON CLAUSE",
+              "original_expr": [],
+              "transformed_expr": []
+            }
+          },
+          {
             "table_dependencies": [
               {
                 "table": "t1",
@@ -4999,6 +5169,13 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4)	{
             }
           },
           {
+            "condition_processing": {
+              "condition": "ON CLAUSE",
+              "original_expr": [],
+              "transformed_expr": []
+            }
+          },
+          {
             "table_dependencies": [
               {
                 "table": "t_outer_1",
@@ -6579,6 +6756,13 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4)	{
             }
           },
           {
+            "condition_processing": {
+              "condition": "ON CLAUSE",
+              "original_expr": [],
+              "transformed_expr": []
+            }
+          },
+          {
             "table_dependencies": [
               {
                 "table": "t_outer_1",
@@ -8755,6 +8939,48 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.on_clause_after_substitution'))
         "t3.a = t1.a"
     ]
 ]
+#
+# MDEV-23646: Optimizer trace: optimize_cond() should show ON expression processing
+#
+SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.condition_processing')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+JSON_DETAILED(JSON_EXTRACT(trace, '$**.condition_processing'))
+[
+    
+    {
+        "condition": "WHERE",
+        "original_condition": "t1.a = t2.a",
+        "steps": 
+        [
+            
+            {
+                "transformation": "equality_propagation",
+                "resulting_condition": "multiple equal(t1.a, t2.a)"
+            },
+            
+            {
+                "transformation": "constant_propagation",
+                "resulting_condition": "multiple equal(t1.a, t2.a)"
+            },
+            
+            {
+                "transformation": "trivial_condition_removal",
+                "resulting_condition": "multiple equal(t1.a, t2.a)"
+            }
+        ]
+    },
+    
+    {
+        "condition": "ON CLAUSE",
+        "original_expr": 
+        [
+            "t2.a = t3.a"
+        ],
+        "transformed_expr": 
+        [
+            "multiple equal(t1.a, t2.a, t3.a)"
+        ]
+    }
+]
 DROP TABLE t1,t2,t3;
 set optimizer_trace='enabled=off';
 # End of 10.4 tests
diff --git a/mysql-test/main/opt_trace.test b/mysql-test/main/opt_trace.test
index 99ddbae8079..9f83a596e38 100644
--- a/mysql-test/main/opt_trace.test
+++ b/mysql-test/main/opt_trace.test
@@ -642,6 +642,12 @@ where t1.a = t2.a;
 SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.where_clause_after_substitution')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
 SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.having_clause_after_substitution')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
 SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.on_clause_after_substitution')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+
+--echo #
+--echo # MDEV-23646: Optimizer trace: optimize_cond() should show ON expression processing
+--echo #
+
+SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.condition_processing')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
 DROP TABLE t1,t2,t3;
 
 set optimizer_trace='enabled=off';
diff --git a/mysql-test/main/opt_trace_index_merge.result b/mysql-test/main/opt_trace_index_merge.result
index c64758bcaa4..a938dc106fd 100644
--- a/mysql-test/main/opt_trace_index_merge.result
+++ b/mysql-test/main/opt_trace_index_merge.result
@@ -54,6 +54,13 @@ explain select * from t1 where a=1 or b=1	{
             }
           },
           {
+            "condition_processing": {
+              "condition": "ON CLAUSE",
+              "original_expr": [],
+              "transformed_expr": []
+            }
+          },
+          {
             "table_dependencies": [
               {
                 "table": "t1",
diff --git a/mysql-test/main/opt_trace_index_merge_innodb.result b/mysql-test/main/opt_trace_index_merge_innodb.result
index 614595c380a..427384c5ec4 100644
--- a/mysql-test/main/opt_trace_index_merge_innodb.result
+++ b/mysql-test/main/opt_trace_index_merge_innodb.result
@@ -62,6 +62,13 @@ explain select * from t1 where pk1 != 0  and key1 = 1	{
             }
           },
           {
+            "condition_processing": {
+              "condition": "ON CLAUSE",
+              "original_expr": [],
+              "transformed_expr": []
+            }
+          },
+          {
             "table_dependencies": [
               {
                 "table": "t1",
diff --git a/mysql-test/main/opt_trace_security.result b/mysql-test/main/opt_trace_security.result
index c94d7231ce0..c5386c39670 100644
--- a/mysql-test/main/opt_trace_security.result
+++ b/mysql-test/main/opt_trace_security.result
@@ -65,6 +65,13 @@ select * from db1.t1	{
         "select_id": 1,
         "steps": [
           {
+            "condition_processing": {
+              "condition": "ON CLAUSE",
+              "original_expr": [],
+              "transformed_expr": []
+            }
+          },
+          {
             "table_dependencies": [
               {
                 "table": "t1",
@@ -193,6 +200,13 @@ select * from db1.v1	{
         "select_id": 1,
         "steps": [
           {
+            "condition_processing": {
+              "condition": "ON CLAUSE",
+              "original_expr": [],
+              "transformed_expr": []
+            }
+          },
+          {
             "table_dependencies": [
               {
                 "table": "t1",
diff --git a/mysql-test/main/subselect2.result b/mysql-test/main/subselect2.result
index a3d7fda7abc..6084d7e6209 100644
--- a/mysql-test/main/subselect2.result
+++ b/mysql-test/main/subselect2.result
@@ -339,10 +339,10 @@ where t1.a = t2.a and ( t1.a = ( select min(a) from t1 ) or 0 );
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
 1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where; Using join buffer (flat, BNL join)
-1	PRIMARY	t3	ref	idx	idx	6	func	2	100.00	Using where; Using index
+1	PRIMARY	t3	ref	idx	idx	6	const	1	100.00	Using where; Using index
 2	SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	
 Warnings:
-Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t2`.`a` AS `a`,`test`.`t3`.`a` AS `a` from `test`.`t1` join `test`.`t2` left join `test`.`t3` on(`test`.`t3`.`a` = `test`.`t1`.`a`) where `test`.`t1`.`a` = (/* select#2 */ select min(`test`.`t1`.`a`) from `test`.`t1`) and `test`.`t2`.`a` = (/* select#2 */ select min(`test`.`t1`.`a`) from `test`.`t1`)
+Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t2`.`a` AS `a`,`test`.`t3`.`a` AS `a` from `test`.`t1` join `test`.`t2` left join `test`.`t3` on(`test`.`t3`.`a` = (/* select#2 */ select min(`test`.`t1`.`a`) from `test`.`t1`)) where `test`.`t1`.`a` = (/* select#2 */ select min(`test`.`t1`.`a`) from `test`.`t1`) and `test`.`t2`.`a` = (/* select#2 */ select min(`test`.`t1`.`a`) from `test`.`t1`)
 select * from t1, t2 left join t3 on ( t2.a = t3.a )
 where t1.a = t2.a and ( t1.a = ( select min(a) from t1 ) or 0 );
 a	a	a
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;