MDEV-22891: Optimizer trace: const tables are not clearly visible

Make mark_join_nest_as_const() print its action into the trace.

2 files changed, 54 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';