3 files changed, 60 insertions, 0 deletions

diff --git a/mysql-test/main/opt_trace.result b/mysql-test/main/opt_trace.result
index ad7bce44445..6af1c2afe9e 100644
--- a/mysql-test/main/opt_trace.result
+++ b/mysql-test/main/opt_trace.result
@@ -8530,5 +8530,36 @@ select count(*) from seq_1_to_10000000	{
     }
   ]
 }	0	0
+#
+# MDEV-22891: Optimizer trace: const tables are not clearly visible
+#
+create table t0(a int primary key);
+insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+create table t1 (pk int primary key, a int);
+insert into t1 select a,a from t0;
+create table t2 (pk int primary key, a int);
+insert into t2 select a,a from t0;
+create table t3 (pk int primary key, a int);
+insert into t3 select a,a from t0;
+explain
+select * from t1 left join (t2 join t3 on t3.pk=1000) on t2.a=t1.a and t2.pk is null;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t3	const	PRIMARY	NULL	NULL	NULL	1	Impossible ON condition
+1	SIMPLE	t2	const	PRIMARY	NULL	NULL	NULL	1	Impossible ON condition
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	10	
+select JSON_DETAILED(JSON_EXTRACT(trace, '$**.mark_join_nest_as_const'))
+from information_schema.optimizer_trace;
+JSON_DETAILED(JSON_EXTRACT(trace, '$**.mark_join_nest_as_const'))
+[
+    
+    {
+        "members": 
+        [
+            "t3",
+            "t2"
+        ]
+    }
+]
+drop table t0, t1, t2, t3;
 # End of 10.5 tests
 set optimizer_trace='enabled=off';
diff --git a/mysql-test/main/opt_trace.test b/mysql-test/main/opt_trace.test
index f2d7b67983d..f7f19e38eac 100644
--- a/mysql-test/main/opt_trace.test
+++ b/mysql-test/main/opt_trace.test
@@ -604,5 +604,28 @@ DROP TABLE t1,t2;
 select count(*) from seq_1_to_10000000;
 select * from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
 
+--echo #
+--echo # MDEV-22891: Optimizer trace: const tables are not clearly visible
+--echo #
+create table t0(a int primary key);
+insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+
+create table t1 (pk int primary key, a int);
+insert into t1 select a,a from t0;
+
+create table t2 (pk int primary key, a int);
+insert into t2 select a,a from t0;
+
+create table t3 (pk int primary key, a int);
+insert into t3 select a,a from t0;
+
+explain
+select * from t1 left join (t2 join t3 on t3.pk=1000) on t2.a=t1.a and t2.pk is null;
+
+select JSON_DETAILED(JSON_EXTRACT(trace, '$**.mark_join_nest_as_const'))
+from information_schema.optimizer_trace;
+
+drop table t0, t1, t2, t3;
+
 --echo # End of 10.5 tests
 set optimizer_trace='enabled=off';
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index 7ac42706d1d..895fb0ba899 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -4737,6 +4737,10 @@ void mark_join_nest_as_const(JOIN *join,
 {
   List_iterator<TABLE_LIST> it(join_nest->nested_join->join_list);
   TABLE_LIST *tbl;
+  Json_writer_object emb_obj(join->thd);
+  Json_writer_object trace_obj(join->thd, "mark_join_nest_as_const");
+  Json_writer_array trace_array(join->thd, "members");
+
   while ((tbl= it++))
   {
     if (tbl->nested_join)
@@ -4756,6 +4760,8 @@ void mark_join_nest_as_const(JOIN *join,
       *found_const_table_map|= tab->table->map;
       set_position(join,(*const_count)++,tab,(KEYUSE*) 0);
       mark_as_null_row(tab->table);		// All fields are NULL
+
+      trace_array.add_table_name(tab->table);
     }
   }
 }