MDEV-23646: Optimizer trace: optimize_cond() should show ON expression processing

Print the build_equal_items() step for ON expression processing

3 files changed, 80 insertions, 0 deletions

diff --git a/mysql-test/main/opt_trace.result b/mysql-test/main/opt_trace.result
index 192f9040c35..4d00e32c4a7 100644
--- a/mysql-test/main/opt_trace.result
+++ b/mysql-test/main/opt_trace.result
@@ -2320,6 +2320,13 @@ select t1.a from t1 left join t2 on t1.a=t2.a	{
         "select_id": 1,
         "steps": [
           {
+            "build_equal_items": {
+              "condition": "ON expr",
+              "attached_to": "t2",
+              "resulting_condition": "multiple equal(t1.a, t2.a)"
+            }
+          },
+          {
             "table_dependencies": [
               {
                 "table": "t1",
@@ -2450,6 +2457,13 @@ explain select * from t1 left join t2 on t2.a=t1.a	{
         "select_id": 1,
         "steps": [
           {
+            "build_equal_items": {
+              "condition": "ON expr",
+              "attached_to": "t2",
+              "resulting_condition": "multiple equal(t2.a, t1.a)"
+            }
+          },
+          {
             "table_dependencies": [
               {
                 "table": "t1",
@@ -2622,6 +2636,13 @@ 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": [
           {
+            "build_equal_items": {
+              "condition": "ON expr",
+              "attached_to": "t3",
+              "resulting_condition": "multiple equal(t2.a, t1.a, t3.a) and multiple equal(t2.b, t3.b)"
+            }
+          },
+          {
             "table_dependencies": [
               {
                 "table": "t1",
@@ -9007,5 +9028,46 @@ json_detailed(json_extract(trace, '$**.substitute_best_equal'))
         "resulting_condition": "t2.a = t1.a and t3.a = t1.a and t1.a + t1.a < 1000"
     }
 ]
+# The next query is test for:
+# 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.b > 5555",
+        "steps": 
+        [
+            
+            {
+                "build_equal_items": 
+                {
+                    "condition": "ON expr",
+                    "attached_to": "t3",
+                    "resulting_condition": "t3.a + t2.a < 1000 and multiple equal(t2.a, t1.a, t3.a)"
+                }
+            },
+            
+            {
+                "transformation": "equality_propagation",
+                "resulting_condition": "t1.b > 5555"
+            },
+            
+            {
+                "transformation": "constant_propagation",
+                "resulting_condition": "t1.b > 5555"
+            },
+            
+            {
+                "transformation": "trivial_condition_removal",
+                "resulting_condition": "t1.b > 5555"
+            }
+        ]
+    }
+]
 drop table t1,t2,t3;
 set optimizer_trace='enabled=off';
diff --git a/mysql-test/main/opt_trace.test b/mysql-test/main/opt_trace.test
index c4166774ab1..b351699c7a6 100644
--- a/mysql-test/main/opt_trace.test
+++ b/mysql-test/main/opt_trace.test
@@ -773,11 +773,19 @@ select
 from t1 left join (t2,t3) on t2.a=t1.a and t3.a=t2.a and t3.a + t2.a <1000
 where 
   t1.b > 5555;
+
 select
   json_detailed(json_extract(trace, '$**.substitute_best_equal'))
 from
   information_schema.optimizer_trace;
 
+--echo # The next query is test for:
+--echo # MDEV-23646: Optimizer trace: optimize_cond() should show ON expression processing
+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/sql/sql_select.cc b/sql/sql_select.cc
index 05dc145f5cb..93f5d3591ed 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -15488,6 +15488,16 @@ static COND *build_equal_items(JOIN *join, COND *cond,
         table->on_expr= build_equal_items(join, table->on_expr, inherited,
                                           nested_join_list, ignore_on_conds,
                                           &table->cond_equal);
+        if (unlikely(join->thd->trace_started()))
+        {
+          const char *table_name;
+          if (table->nested_join)
+            table_name= table->nested_join->join_list.head()->alias.str;
+          else
+            table_name= table->alias.str;
+          trace_condition(join->thd, "ON expr", "build_equal_items",
+                          table->on_expr, table_name);
+        }
       }
     }
   }