MDEV-28869 Eliminated tables are not shown in EXPLAINbb-10.10-MDEV-28869

Show tables that have been eliminated during optimization stage
in the output of EXPLAIN/ANALYZE [FORMAT=JSON] commands. Show both
regular and derived tables.

14 files changed, 1349 insertions, 92 deletions

diff --git a/mysql-test/main/join_outer_innodb.result b/mysql-test/main/join_outer_innodb.result
index 809a980576d..daccc1eb069 100644
--- a/mysql-test/main/join_outer_innodb.result
+++ b/mysql-test/main/join_outer_innodb.result
@@ -454,6 +454,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t15	eq_ref	PRIMARY	PRIMARY	2	test.t1.a9	1	Using where; Using index
 1	SIMPLE	t16	ref	PRIMARY	PRIMARY	2	test.t15.o1	1	Using where
 1	SIMPLE	t10	ALL	PRIMARY	NULL	NULL	NULL	3	Using where; Using join buffer (flat, BNL join)
+1	SIMPLE	e2	Eliminated	NULL	NULL	NULL	NULL	NULL	
 explain select * from v1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ALL	a4,a6,a5,a7	NULL	NULL	NULL	3	Using where
@@ -476,6 +477,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t15	eq_ref	PRIMARY	PRIMARY	2	test.t1.a9	1	Using where; Using index
 1	SIMPLE	t16	ref	PRIMARY	PRIMARY	2	test.t15.o1	1	Using where
 1	SIMPLE	t10	ALL	PRIMARY	NULL	NULL	NULL	3	Using where; Using join buffer (flat, BNL join)
+1	SIMPLE	e2	Eliminated	NULL	NULL	NULL	NULL	NULL	
 drop view v1;
 drop table t1,t2,t3,t4,t5,t6,t7,t8,t9,t10,t11,t12,t13,t14,t15,t16;
 #
diff --git a/mysql-test/main/opt_trace.result b/mysql-test/main/opt_trace.result
index 3542d7fe051..96040a8d9a3 100644
--- a/mysql-test/main/opt_trace.result
+++ b/mysql-test/main/opt_trace.result
@@ -2418,6 +2418,7 @@ explain
 select t1.a from t1 left join t2 on t1.a=t2.a;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	4	
+1	SIMPLE	t2	Eliminated	NULL	NULL	NULL	NULL	NULL	
 select * from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
 QUERY	TRACE	MISSING_BYTES_BEYOND_MAX_MEM_SIZE	INSUFFICIENT_PRIVILEGES
 explain
@@ -2762,6 +2763,8 @@ explain select * from t1 left join t2 on t2.a=t1.a	{
 explain select t1.a from t1 left join (t2 join t3 on t2.b=t3.b) on t2.a=t1.a and t3.a=t1.a;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	4	
+1	SIMPLE	t3	Eliminated	NULL	NULL	NULL	NULL	NULL	
+1	SIMPLE	t2	Eliminated	NULL	NULL	NULL	NULL	NULL	
 select * from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
 QUERY	TRACE	MISSING_BYTES_BEYOND_MAX_MEM_SIZE	INSUFFICIENT_PRIVILEGES
 explain select t1.a from t1 left join (t2 join t3 on t2.b=t3.b) on t2.a=t1.a and t3.a=t1.a	{
diff --git a/mysql-test/main/select.result b/mysql-test/main/select.result
index da013eb2619..61bf8aa1705 100644
--- a/mysql-test/main/select.result
+++ b/mysql-test/main/select.result
@@ -3592,6 +3592,7 @@ INSERT INTO t2 VALUES (1,'a'),(2,'b'),(3,'c');
 EXPLAIN SELECT t1.a FROM t1 LEFT JOIN t2 ON t2.b=t1.b WHERE t1.a=3;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	const	PRIMARY	PRIMARY	4	const	1	
+1	SIMPLE	t2	Eliminated	NULL	NULL	NULL	NULL	NULL	
 DROP TABLE t1,t2;
 CREATE TABLE t1(id int PRIMARY KEY, b int, e int);
 CREATE TABLE t2(i int, a int, INDEX si(i), INDEX ai(a));
diff --git a/mysql-test/main/select_jcl6.result b/mysql-test/main/select_jcl6.result
index 0d09df225c9..3269a94cf3e 100644
--- a/mysql-test/main/select_jcl6.result
+++ b/mysql-test/main/select_jcl6.result
@@ -3603,6 +3603,7 @@ INSERT INTO t2 VALUES (1,'a'),(2,'b'),(3,'c');
 EXPLAIN SELECT t1.a FROM t1 LEFT JOIN t2 ON t2.b=t1.b WHERE t1.a=3;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	const	PRIMARY	PRIMARY	4	const	1	
+1	SIMPLE	t2	Eliminated	NULL	NULL	NULL	NULL	NULL	
 DROP TABLE t1,t2;
 CREATE TABLE t1(id int PRIMARY KEY, b int, e int);
 CREATE TABLE t2(i int, a int, INDEX si(i), INDEX ai(a));
diff --git a/mysql-test/main/select_pkeycache.result b/mysql-test/main/select_pkeycache.result
index da013eb2619..61bf8aa1705 100644
--- a/mysql-test/main/select_pkeycache.result
+++ b/mysql-test/main/select_pkeycache.result
@@ -3592,6 +3592,7 @@ INSERT INTO t2 VALUES (1,'a'),(2,'b'),(3,'c');
 EXPLAIN SELECT t1.a FROM t1 LEFT JOIN t2 ON t2.b=t1.b WHERE t1.a=3;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	const	PRIMARY	PRIMARY	4	const	1	
+1	SIMPLE	t2	Eliminated	NULL	NULL	NULL	NULL	NULL	
 DROP TABLE t1,t2;
 CREATE TABLE t1(id int PRIMARY KEY, b int, e int);
 CREATE TABLE t2(i int, a int, INDEX si(i), INDEX ai(a));
diff --git a/mysql-test/main/table_elim.result b/mysql-test/main/table_elim.result
index 4f648f45895..4cc3ac8da01 100644
--- a/mysql-test/main/table_elim.result
+++ b/mysql-test/main/table_elim.result
@@ -13,11 +13,37 @@ as select a, a as b from t1 where a in (1,3);
 explain select t1.a from t1 left join t2 on t2.a=t1.a;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	4	
+1	SIMPLE	t2	Eliminated	NULL	NULL	NULL	NULL	NULL	
 explain extended select t1.a from t1 left join t2 on t2.a=t1.a;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	4	100.00	
+1	SIMPLE	t2	Eliminated	NULL	NULL	NULL	NULL	NULL	NULL	
 Warnings:
 Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` where 1
+explain format=json select t1.a from t1 left join t2 on t2.a=t1.a;
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "const_condition": "1",
+    "nested_loop": [
+      {
+        "table": {
+          "table_name": "t1",
+          "access_type": "ALL",
+          "rows": 4,
+          "filtered": 100
+        }
+      },
+      {
+        "table": {
+          "table_name": "t2",
+          "access_type": "Eliminated"
+        }
+      }
+    ]
+  }
+}
 select t1.a from t1 left join t2 on t2.a=t1.a;
 a
 0
@@ -29,6 +55,38 @@ explain select * from t1 left join t2 on t2.a=t1.a;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	4	
 1	SIMPLE	t2	eq_ref	PRIMARY	PRIMARY	4	test.t1.a	1	Using where
+explain format=json select * from t1 left join t2 on t2.a=t1.a;
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "const_condition": "1",
+    "nested_loop": [
+      {
+        "table": {
+          "table_name": "t1",
+          "access_type": "ALL",
+          "rows": 4,
+          "filtered": 100
+        }
+      },
+      {
+        "table": {
+          "table_name": "t2",
+          "access_type": "eq_ref",
+          "possible_keys": ["PRIMARY"],
+          "key": "PRIMARY",
+          "key_length": "4",
+          "used_key_parts": ["a"],
+          "ref": ["test.t1.a"],
+          "rows": 1,
+          "filtered": 100,
+          "attached_condition": "trigcond(trigcond(t1.a is not null))"
+        }
+      }
+    ]
+  }
+}
 # This will not be eliminated as t2.b is in in order list:
 explain select t1.a from t1 left join t2 on t2.a=t1.a order by t2.b;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
@@ -48,10 +106,74 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 explain select t1.a from t1 left join (t2 join t3) on t2.a=t1.a and t3.a=t1.a;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	4	
+1	SIMPLE	t3	Eliminated	NULL	NULL	NULL	NULL	NULL	
+1	SIMPLE	t2	Eliminated	NULL	NULL	NULL	NULL	NULL	
+explain format=json select t1.a from t1 left join (t2 join t3) on t2.a=t1.a and t3.a=t1.a;
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "const_condition": "1",
+    "nested_loop": [
+      {
+        "table": {
+          "table_name": "t1",
+          "access_type": "ALL",
+          "rows": 4,
+          "filtered": 100
+        }
+      },
+      {
+        "table": {
+          "table_name": "t3",
+          "access_type": "Eliminated"
+        }
+      },
+      {
+        "table": {
+          "table_name": "t2",
+          "access_type": "Eliminated"
+        }
+      }
+    ]
+  }
+}
 # Elimination of multiple tables (2):
 explain select t1.a from t1 left join (t2 join t3 on t2.b=t3.b) on t2.a=t1.a and t3.a=t1.a;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	4	
+1	SIMPLE	t3	Eliminated	NULL	NULL	NULL	NULL	NULL	
+1	SIMPLE	t2	Eliminated	NULL	NULL	NULL	NULL	NULL	
+explain format=json select t1.a from t1 left join (t2 join t3 on t2.b=t3.b) on t2.a=t1.a and t3.a=t1.a;
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "const_condition": "1",
+    "nested_loop": [
+      {
+        "table": {
+          "table_name": "t1",
+          "access_type": "ALL",
+          "rows": 4,
+          "filtered": 100
+        }
+      },
+      {
+        "table": {
+          "table_name": "t3",
+          "access_type": "Eliminated"
+        }
+      },
+      {
+        "table": {
+          "table_name": "t2",
+          "access_type": "Eliminated"
+        }
+      }
+    ]
+  }
+}
 # Elimination when done within an outer join nest:
 explain extended
 select t0.*
@@ -61,23 +183,133 @@ t3.a=t1.a) on t0.a=t1.a;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	t0	ALL	NULL	NULL	NULL	NULL	4	100.00	
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	4	100.00	Using where
+1	SIMPLE	t3	Eliminated	NULL	NULL	NULL	NULL	NULL	NULL	
+1	SIMPLE	t2	Eliminated	NULL	NULL	NULL	NULL	NULL	NULL	
 Warnings:
 Note	1003	select `test`.`t0`.`a` AS `a` from `test`.`t0` left join (`test`.`t1`) on(`test`.`t1`.`a` = `test`.`t0`.`a`) where 1
+explain format=json
+select t0.*
+from
+t0 left join (t1 left join (t2 join t3 on t2.b=t3.b) on t2.a=t1.a and
+t3.a=t1.a) on t0.a=t1.a;
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "const_condition": "1",
+    "nested_loop": [
+      {
+        "table": {
+          "table_name": "t0",
+          "access_type": "ALL",
+          "rows": 4,
+          "filtered": 100
+        }
+      },
+      {
+        "table": {
+          "table_name": "t1",
+          "access_type": "ALL",
+          "rows": 4,
+          "filtered": 100,
+          "attached_condition": "trigcond(t1.a = t0.a)"
+        }
+      },
+      {
+        "table": {
+          "table_name": "t3",
+          "access_type": "Eliminated"
+        }
+      },
+      {
+        "table": {
+          "table_name": "t2",
+          "access_type": "Eliminated"
+        }
+      }
+    ]
+  }
+}
 # Elimination with aggregate functions
 explain select count(*) from t1 left join t2 on t2.a=t1.a;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	4	
+1	SIMPLE	t2	Eliminated	NULL	NULL	NULL	NULL	NULL	
+explain format=json select count(*) from t1 left join t2 on t2.a=t1.a;
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "const_condition": "1",
+    "nested_loop": [
+      {
+        "table": {
+          "table_name": "t1",
+          "access_type": "ALL",
+          "rows": 4,
+          "filtered": 100
+        }
+      },
+      {
+        "table": {
+          "table_name": "t2",
+          "access_type": "Eliminated"
+        }
+      }
+    ]
+  }
+}
 explain select count(1) from t1 left join t2 on t2.a=t1.a;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	4	
+1	SIMPLE	t2	Eliminated	NULL	NULL	NULL	NULL	NULL	
 explain select count(1) from t1 left join t2 on t2.a=t1.a group by t1.a;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	4	Using temporary; Using filesort
+1	SIMPLE	t2	Eliminated	NULL	NULL	NULL	NULL	NULL	
 This must not use elimination:
 explain select count(1) from t1 left join t2 on t2.a=t1.a group by t2.a;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	4	Using temporary; Using filesort
 1	SIMPLE	t2	eq_ref	PRIMARY	PRIMARY	4	test.t1.a	1	Using where; Using index
+explain format=json select count(1) from t1 left join t2 on t2.a=t1.a group by t2.a;
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "const_condition": "1",
+    "filesort": {
+      "sort_key": "t2.a",
+      "temporary_table": {
+        "nested_loop": [
+          {
+            "table": {
+              "table_name": "t1",
+              "access_type": "ALL",
+              "rows": 4,
+              "filtered": 100
+            }
+          },
+          {
+            "table": {
+              "table_name": "t2",
+              "access_type": "eq_ref",
+              "possible_keys": ["PRIMARY"],
+              "key": "PRIMARY",
+              "key_length": "4",
+              "used_key_parts": ["a"],
+              "ref": ["test.t1.a"],
+              "rows": 1,
+              "filtered": 100,
+              "attached_condition": "trigcond(trigcond(t1.a is not null))",
+              "using_index": true
+            }
+          }
+        ]
+      }
+    }
+  }
+}
 drop table t0, t1, t2, t3;
 create table t0 ( id integer, primary key (id));
 create table t1 (
@@ -120,58 +352,434 @@ This should use one table:
 explain select id from v1 where id=2;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	f	const	PRIMARY	PRIMARY	4	const	1	Using index
+1	PRIMARY	a2	Eliminated	NULL	NULL	NULL	NULL	NULL	
+1	PRIMARY	a1	Eliminated	NULL	NULL	NULL	NULL	NULL	
+explain format=json select id from v1 where id=2;
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "const_condition": "1",
+    "nested_loop": [
+      {
+        "table": {
+          "table_name": "f",
+          "access_type": "const",
+          "possible_keys": ["PRIMARY"],
+          "key": "PRIMARY",
+          "key_length": "4",
+          "used_key_parts": ["id"],
+          "ref": ["const"],
+          "rows": 1,
+          "filtered": 100,
+          "using_index": true
+        }
+      },
+      {
+        "table": {
+          "table_name": "a2",
+          "access_type": "Eliminated"
+        }
+      },
+      {
+        "table": {
+          "table_name": "a1",
+          "access_type": "Eliminated"
+        }
+      }
+    ]
+  }
+}
 This should use one table:
 explain extended select id from v1 where id in (1,2,3,4);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	f	range	PRIMARY	PRIMARY	4	NULL	4	100.00	Using where; Using index
+1	PRIMARY	a2	Eliminated	NULL	NULL	NULL	NULL	NULL	NULL	
+1	PRIMARY	a1	Eliminated	NULL	NULL	NULL	NULL	NULL	NULL	
 Warnings:
 Note	1276	Field or reference 'test.a2.id' of SELECT #3 was resolved in SELECT #2
 Note	1003	/* select#1 */ select `f`.`id` AS `id` from `test`.`t0` `f` where `f`.`id` in (1,2,3,4)
+explain format=json select id from v1 where id in (1,2,3,4);
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "nested_loop": [
+      {
+        "table": {
+          "table_name": "f",
+          "access_type": "range",
+          "possible_keys": ["PRIMARY"],
+          "key": "PRIMARY",
+          "key_length": "4",
+          "used_key_parts": ["id"],
+          "rows": 4,
+          "filtered": 100,
+          "attached_condition": "f.`id` in (1,2,3,4)",
+          "using_index": true
+        }
+      },
+      {
+        "table": {
+          "table_name": "a2",
+          "access_type": "Eliminated"
+        }
+      },
+      {
+        "table": {
+          "table_name": "a1",
+          "access_type": "Eliminated"
+        }
+      }
+    ]
+  }
+}
 This should use facts and a1 tables:
 explain extended select id from v1 where attr1 between 12 and 14;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	a1	range	PRIMARY,attr1	attr1	5	NULL	2	100.00	Using index condition
 1	PRIMARY	f	eq_ref	PRIMARY	PRIMARY	4	test.a1.id	1	100.00	Using index
+1	PRIMARY	a2	Eliminated	NULL	NULL	NULL	NULL	NULL	NULL	
 Warnings:
 Note	1276	Field or reference 'test.a2.id' of SELECT #3 was resolved in SELECT #2
 Note	1003	/* select#1 */ select `f`.`id` AS `id` from `test`.`t0` `f` join `test`.`t1` `a1` where `f`.`id` = `a1`.`id` and `a1`.`attr1` between 12 and 14
+explain format=json select id from v1 where attr1 between 12 and 14;
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "nested_loop": [
+      {
+        "table": {
+          "table_name": "a1",
+          "access_type": "range",
+          "possible_keys": ["PRIMARY", "attr1"],
+          "key": "attr1",
+          "key_length": "5",
+          "used_key_parts": ["attr1"],
+          "rows": 2,
+          "filtered": 100,
+          "index_condition": "a1.attr1 between 12 and 14"
+        }
+      },
+      {
+        "table": {
+          "table_name": "f",
+          "access_type": "eq_ref",
+          "possible_keys": ["PRIMARY"],
+          "key": "PRIMARY",
+          "key_length": "4",
+          "used_key_parts": ["id"],
+          "ref": ["test.a1.id"],
+          "rows": 1,
+          "filtered": 100,
+          "using_index": true
+        }
+      },
+      {
+        "table": {
+          "table_name": "a2",
+          "access_type": "Eliminated"
+        }
+      }
+    ]
+  }
+}
 This should use facts, a2 and its subquery:
 explain extended select id from v1 where attr2 between 12 and 14;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	a2	range	PRIMARY,attr2	attr2	5	NULL	4	100.00	Using index condition; Using where
 1	PRIMARY	f	eq_ref	PRIMARY	PRIMARY	4	test.a2.id	1	100.00	Using index
+1	PRIMARY	a1	Eliminated	NULL	NULL	NULL	NULL	NULL	NULL	
 3	DEPENDENT SUBQUERY	t2	ref	PRIMARY	PRIMARY	4	test.a2.id	2	100.00	Using index
 Warnings:
 Note	1276	Field or reference 'test.a2.id' of SELECT #3 was resolved in SELECT #2
 Note	1003	/* select#1 */ select `f`.`id` AS `id` from `test`.`t0` `f` join `test`.`t2` `a2` where `f`.`id` = `a2`.`id` and `a2`.`attr2` between 12 and 14 and `a2`.`fromdate` = <expr_cache><`a2`.`id`>((/* select#3 */ select max(`test`.`t2`.`fromdate`) from `test`.`t2` where `test`.`t2`.`id` = `a2`.`id`))
+explain format=json select id from v1 where attr2 between 12 and 14;
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "nested_loop": [
+      {
+        "table": {
+          "table_name": "a2",
+          "access_type": "range",
+          "possible_keys": ["PRIMARY", "attr2"],
+          "key": "attr2",
+          "key_length": "5",
+          "used_key_parts": ["attr2"],
+          "rows": 4,
+          "filtered": 100,
+          "index_condition": "a2.attr2 between 12 and 14",
+          "attached_condition": "a2.fromdate = (subquery#3)"
+        }
+      },
+      {
+        "table": {
+          "table_name": "f",
+          "access_type": "eq_ref",
+          "possible_keys": ["PRIMARY"],
+          "key": "PRIMARY",
+          "key_length": "4",
+          "used_key_parts": ["id"],
+          "ref": ["test.a2.id"],
+          "rows": 1,
+          "filtered": 100,
+          "using_index": true
+        }
+      },
+      {
+        "table": {
+          "table_name": "a1",
+          "access_type": "Eliminated"
+        }
+      }
+    ],
+    "subqueries": [
+      {
+        "expression_cache": {
+          "state": "uninitialized",
+          "query_block": {
+            "select_id": 3,
+            "nested_loop": [
+              {
+                "table": {
+                  "table_name": "t2",
+                  "access_type": "ref",
+                  "possible_keys": ["PRIMARY"],
+                  "key": "PRIMARY",
+                  "key_length": "4",
+                  "used_key_parts": ["id"],
+                  "ref": ["test.a2.id"],
+                  "rows": 2,
+                  "filtered": 100,
+                  "using_index": true
+                }
+              }
+            ]
+          }
+        }
+      }
+    ]
+  }
+}
 This should use one table:
 explain select id from v2 where id=2;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	f	const	PRIMARY	PRIMARY	4	const	1	Using index
+1	PRIMARY	a2	Eliminated	NULL	NULL	NULL	NULL	NULL	
+1	PRIMARY	a1	Eliminated	NULL	NULL	NULL	NULL	NULL	
+explain format=json select id from v2 where id=2;
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "const_condition": "1",
+    "nested_loop": [
+      {
+        "table": {
+          "table_name": "f",
+          "access_type": "const",
+          "possible_keys": ["PRIMARY"],
+          "key": "PRIMARY",
+          "key_length": "4",
+          "used_key_parts": ["id"],
+          "ref": ["const"],
+          "rows": 1,
+          "filtered": 100,
+          "using_index": true
+        }
+      },
+      {
+        "table": {
+          "table_name": "a2",
+          "access_type": "Eliminated"
+        }
+      },
+      {
+        "table": {
+          "table_name": "a1",
+          "access_type": "Eliminated"
+        }
+      }
+    ]
+  }
+}
 This should use one table:
 explain extended select id from v2 where id in (1,2,3,4);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	f	range	PRIMARY	PRIMARY	4	NULL	4	100.00	Using where; Using index
+1	PRIMARY	a2	Eliminated	NULL	NULL	NULL	NULL	NULL	NULL	
+1	PRIMARY	a1	Eliminated	NULL	NULL	NULL	NULL	NULL	NULL	
 Warnings:
 Note	1276	Field or reference 'test.f.id' of SELECT #3 was resolved in SELECT #2
 Note	1003	/* select#1 */ select `f`.`id` AS `id` from `test`.`t0` `f` where `f`.`id` in (1,2,3,4)
+explain format=json select id from v2 where id in (1,2,3,4);
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "nested_loop": [
+      {
+        "table": {
+          "table_name": "f",
+          "access_type": "range",
+          "possible_keys": ["PRIMARY"],
+          "key": "PRIMARY",
+          "key_length": "4",
+          "used_key_parts": ["id"],
+          "rows": 4,
+          "filtered": 100,
+          "attached_condition": "f.`id` in (1,2,3,4)",
+          "using_index": true
+        }
+      },
+      {
+        "table": {
+          "table_name": "a2",
+          "access_type": "Eliminated"
+        }
+      },
+      {
+        "table": {
+          "table_name": "a1",
+          "access_type": "Eliminated"
+        }
+      }
+    ]
+  }
+}
 This should use facts and a1 tables:
 explain extended select id from v2 where attr1 between 12 and 14;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	a1	range	PRIMARY,attr1	attr1	5	NULL	2	100.00	Using index condition
 1	PRIMARY	f	eq_ref	PRIMARY	PRIMARY	4	test.a1.id	1	100.00	Using index
+1	PRIMARY	a2	Eliminated	NULL	NULL	NULL	NULL	NULL	NULL	
 Warnings:
 Note	1276	Field or reference 'test.f.id' of SELECT #3 was resolved in SELECT #2
 Note	1003	/* select#1 */ select `f`.`id` AS `id` from `test`.`t0` `f` join `test`.`t1` `a1` where `f`.`id` = `a1`.`id` and `a1`.`attr1` between 12 and 14
+explain format=json select id from v2 where attr1 between 12 and 14;
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "nested_loop": [
+      {
+        "table": {
+          "table_name": "a1",
+          "access_type": "range",
+          "possible_keys": ["PRIMARY", "attr1"],
+          "key": "attr1",
+          "key_length": "5",
+          "used_key_parts": ["attr1"],
+          "rows": 2,
+          "filtered": 100,
+          "index_condition": "a1.attr1 between 12 and 14"
+        }
+      },
+      {
+        "table": {
+          "table_name": "f",
+          "access_type": "eq_ref",
+          "possible_keys": ["PRIMARY"],
+          "key": "PRIMARY",
+          "key_length": "4",
+          "used_key_parts": ["id"],
+          "ref": ["test.a1.id"],
+          "rows": 1,
+          "filtered": 100,
+          "using_index": true
+        }
+      },
+      {
+        "table": {
+          "table_name": "a2",
+          "access_type": "Eliminated"
+        }
+      }
+    ]
+  }
+}
 This should use facts, a2 and its subquery:
 explain extended select id from v2 where attr2 between 12 and 14;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	a2	range	PRIMARY,attr2	attr2	5	NULL	4	100.00	Using index condition
 1	PRIMARY	f	eq_ref	PRIMARY	PRIMARY	4	test.a2.id	1	100.00	Using where; Using index
+1	PRIMARY	a1	Eliminated	NULL	NULL	NULL	NULL	NULL	NULL	
 3	DEPENDENT SUBQUERY	t2	ref	PRIMARY	PRIMARY	4	test.f.id	2	100.00	Using index
 Warnings:
 Note	1276	Field or reference 'test.f.id' of SELECT #3 was resolved in SELECT #2
 Note	1003	/* select#1 */ select `f`.`id` AS `id` from `test`.`t0` `f` join `test`.`t2` `a2` where `f`.`id` = `a2`.`id` and `a2`.`attr2` between 12 and 14 and `a2`.`fromdate` = <expr_cache><`f`.`id`>((/* select#3 */ select max(`test`.`t2`.`fromdate`) from `test`.`t2` where `test`.`t2`.`id` = `f`.`id`))
+explain format=json select id from v2 where attr2 between 12 and 14;
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "nested_loop": [
+      {
+        "table": {
+          "table_name": "a2",
+          "access_type": "range",
+          "possible_keys": ["PRIMARY", "attr2"],
+          "key": "attr2",
+          "key_length": "5",
+          "used_key_parts": ["attr2"],
+          "rows": 4,
+          "filtered": 100,
+          "index_condition": "a2.attr2 between 12 and 14"
+        }
+      },
+      {
+        "table": {
+          "table_name": "f",
+          "access_type": "eq_ref",
+          "possible_keys": ["PRIMARY"],
+          "key": "PRIMARY",
+          "key_length": "4",
+          "used_key_parts": ["id"],
+          "ref": ["test.a2.id"],
+          "rows": 1,
+          "filtered": 100,
+          "attached_condition": "a2.fromdate = (subquery#3)",
+          "using_index": true
+        }
+      },
+      {
+        "table": {
+          "table_name": "a1",
+          "access_type": "Eliminated"
+        }
+      }
+    ],
+    "subqueries": [
+      {
+        "expression_cache": {
+          "state": "uninitialized",
+          "query_block": {
+            "select_id": 3,
+            "nested_loop": [
+              {
+                "table": {
+                  "table_name": "t2",
+                  "access_type": "ref",
+                  "possible_keys": ["PRIMARY"],
+                  "key": "PRIMARY",
+                  "key_length": "4",
+                  "used_key_parts": ["id"],
+                  "ref": ["test.f.id"],
+                  "rows": 2,
+                  "filtered": 100,
+                  "using_index": true
+                }
+              }
+            ]
+          }
+        }
+      }
+    ]
+  }
+}
 drop view v1, v2;
 drop table t0, t1, t2;
 create table t1 (a int);
@@ -184,12 +792,40 @@ t2.pk2=t2.pk1+1 and
 t2.pk3=t2.pk2+1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	4	
+1	SIMPLE	t2	Eliminated	NULL	NULL	NULL	NULL	NULL	
+explain format=json select t1.* from t1 left join t2 on t2.pk1=t1.a and 
+t2.pk2=t2.pk1+1 and
+t2.pk3=t2.pk2+1;
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "const_condition": "1",
+    "nested_loop": [
+      {
+        "table": {
+          "table_name": "t1",
+          "access_type": "ALL",
+          "rows": 4,
+          "filtered": 100
+        }
+      },
+      {
+        "table": {
+          "table_name": "t2",
+          "access_type": "Eliminated"
+        }
+      }
+    ]
+  }
+}
 This must use only t1:
 explain select t1.* from t1 left join t2 on t2.pk1=t1.a and 
 t2.pk3=t2.pk1+1 and
 t2.pk2=t2.pk3+1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	4	
+1	SIMPLE	t2	Eliminated	NULL	NULL	NULL	NULL	NULL	
 This must use both:
 explain select t1.* from t1 left join t2 on t2.pk1=t1.a and 
 t2.pk3=t2.pk1+1 and
@@ -203,6 +839,7 @@ t2.pk1=t2.pk2+1 and
 t2.pk3=t2.pk1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	4	
+1	SIMPLE	t2	Eliminated	NULL	NULL	NULL	NULL	NULL	
 drop table t1, t2;
 create table t1 (pk int primary key, col int);
 insert into t1 values (1,1),(2,2);
@@ -215,11 +852,86 @@ select t1.* from t1 left join ( t2 left join t3 on t3.pk=t2.col) on t2.col=t1.co
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	
 1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	2	Using where
+1	SIMPLE	t3	Eliminated	NULL	NULL	NULL	NULL	NULL	
+explain format=json
+select t1.* from t1 left join ( t2 left join t3 on t3.pk=t2.col) on t2.col=t1.col;
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "const_condition": "1",
+    "nested_loop": [
+      {
+        "table": {
+          "table_name": "t1",
+          "access_type": "ALL",
+          "rows": 2,
+          "filtered": 100
+        }
+      },
+      {
+        "table": {
+          "table_name": "t2",
+          "access_type": "ALL",
+          "rows": 2,
+          "filtered": 100,
+          "attached_condition": "trigcond(t2.col = t1.col)"
+        }
+      },
+      {
+        "table": {
+          "table_name": "t3",
+          "access_type": "Eliminated"
+        }
+      }
+    ]
+  }
+}
 explain 
 select t1.*, t2.* from t1 left join (t2 left join t3 on t3.pk=t2.col) on t2.pk=t1.col;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	
 1	SIMPLE	t2	eq_ref	PRIMARY	PRIMARY	4	test.t1.col	1	Using where
+1	SIMPLE	t3	Eliminated	NULL	NULL	NULL	NULL	NULL	
+explain format=json
+select t1.*, t2.* from t1 left join (t2 left join t3 on t3.pk=t2.col) on t2.pk=t1.col;
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "const_condition": "1",
+    "nested_loop": [
+      {
+        "table": {
+          "table_name": "t1",
+          "access_type": "ALL",
+          "rows": 2,
+          "filtered": 100
+        }
+      },
+      {
+        "table": {
+          "table_name": "t2",
+          "access_type": "eq_ref",
+          "possible_keys": ["PRIMARY"],
+          "key": "PRIMARY",
+          "key_length": "4",
+          "used_key_parts": ["pk"],
+          "ref": ["test.t1.col"],
+          "rows": 1,
+          "filtered": 100,
+          "attached_condition": "trigcond(trigcond(t1.col is not null))"
+        }
+      },
+      {
+        "table": {
+          "table_name": "t3",
+          "access_type": "Eliminated"
+        }
+      }
+    ]
+  }
+}
 explain select t1.* 
 from 
 t1 left join ( t2 left join t3 on t3.pk=t2.col or t3.pk=t2.col) 
@@ -227,6 +939,43 @@ on t2.col=t1.col or t2.col=t1.col;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	
 1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	2	Using where
+1	SIMPLE	t3	Eliminated	NULL	NULL	NULL	NULL	NULL	
+explain format=json select t1.* 
+from 
+t1 left join ( t2 left join t3 on t3.pk=t2.col or t3.pk=t2.col) 
+on t2.col=t1.col or t2.col=t1.col;
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "const_condition": "1",
+    "nested_loop": [
+      {
+        "table": {
+          "table_name": "t1",
+          "access_type": "ALL",
+          "rows": 2,
+          "filtered": 100
+        }
+      },
+      {
+        "table": {
+          "table_name": "t2",
+          "access_type": "ALL",
+          "rows": 2,
+          "filtered": 100,
+          "attached_condition": "trigcond(t2.col = t1.col or t2.col = t1.col)"
+        }
+      },
+      {
+        "table": {
+          "table_name": "t3",
+          "access_type": "Eliminated"
+        }
+      }
+    ]
+  }
+}
 explain select t1.*, t2.* 
 from 
 t1 left join 
@@ -235,6 +984,49 @@ on t2.pk=t1.col or t2.pk=t1.col;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	
 1	SIMPLE	t2	eq_ref	PRIMARY	PRIMARY	4	test.t1.col	1	Using where
+1	SIMPLE	t3	Eliminated	NULL	NULL	NULL	NULL	NULL	
+explain format=json select t1.*, t2.* 
+from 
+t1 left join 
+(t2 left join t3 on t3.pk=t2.col or t3.pk=t2.col) 
+on t2.pk=t1.col or t2.pk=t1.col;
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "const_condition": "1",
+    "nested_loop": [
+      {
+        "table": {
+          "table_name": "t1",
+          "access_type": "ALL",
+          "rows": 2,
+          "filtered": 100
+        }
+      },
+      {
+        "table": {
+          "table_name": "t2",
+          "access_type": "eq_ref",
+          "possible_keys": ["PRIMARY"],
+          "key": "PRIMARY",
+          "key_length": "4",
+          "used_key_parts": ["pk"],
+          "ref": ["test.t1.col"],
+          "rows": 1,
+          "filtered": 100,
+          "attached_condition": "trigcond(trigcond(t1.col is not null))"
+        }
+      },
+      {
+        "table": {
+          "table_name": "t3",
+          "access_type": "Eliminated"
+        }
+      }
+    ]
+  }
+}
 drop table t1, t2, t3;
 # 
 # Check things that look like functional dependencies but really are not
@@ -322,6 +1114,7 @@ insert into t3 select a as pk1, a as pk2, a as b from t1 where a in (1,3);
 explain select t1.a from t1 left join t2 on t2.pk=t1.a and t2.b<t1.b;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	4	
+1	SIMPLE	t2	Eliminated	NULL	NULL	NULL	NULL	NULL	
 explain select t1.a from t1 left join t2 on t2.pk=t1.a or t2.b<t1.b;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	4	
@@ -341,12 +1134,15 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 explain select t1.a from t1 left join t2 on t2.pk between 10 and 10;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	4	
+1	SIMPLE	t2	Eliminated	NULL	NULL	NULL	NULL	NULL	
 explain select t1.a from t1 left join t2 on t2.pk in (10);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	4	
+1	SIMPLE	t2	Eliminated	NULL	NULL	NULL	NULL	NULL	
 explain select t1.a from t1 left join t2 on t2.pk in (t1.a);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	4	
+1	SIMPLE	t2	Eliminated	NULL	NULL	NULL	NULL	NULL	
 explain select t1.a from t1 left join t2 on TRUE;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	4	
@@ -354,6 +1150,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 explain select t1.a from t1 left join t3 on t3.pk1=t1.a and t3.pk2 IS NULL;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	4	
+1	SIMPLE	t3	Eliminated	NULL	NULL	NULL	NULL	NULL	
 drop table t1,t2,t3;
 #
 # Multi-equality tests 
@@ -368,6 +1165,7 @@ from t1 left join t2 on t2.pk=t2.c and t2.b=t1.a and t1.a=t1.b and t2.c=t2.b
 where t1.d=1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	4	Using where
+1	SIMPLE	t2	Eliminated	NULL	NULL	NULL	NULL	NULL	
 explain 
 select t1.* 
 from 
@@ -379,6 +1177,7 @@ on (t2.pk=t2.c and t2.b=t1.a and t1.a=t1.b and t2.c=t2.b) or
 where t1.d=1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	4	Using where
+1	SIMPLE	t2	Eliminated	NULL	NULL	NULL	NULL	NULL	
 #This can't be eliminated:
 explain 
 select t1.* 
@@ -403,6 +1202,7 @@ on (t2.pk=t2.c and t2.b=t1.a and t2.c=t1.b) or
 ;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	4	
+1	SIMPLE	t2	Eliminated	NULL	NULL	NULL	NULL	NULL	
 explain 
 select t1.* 
 from t1 left join t2 on t2.pk=3 or t2.pk= 4;
@@ -414,6 +1214,7 @@ select t1.*
 from t1 left join t2 on t2.pk=3 or t2.pk= 3;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	4	
+1	SIMPLE	t2	Eliminated	NULL	NULL	NULL	NULL	NULL	
 explain 
 select t1.* 
 from t1 left join t2 on (t2.pk=3 and t2.b=3) or (t2.pk= 4 and t2.b=3);
@@ -566,6 +1367,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t5	ref	f5	f5	5	test.t3.f1	2	Using where; Using index
 1	SIMPLE	t4	ALL	NULL	NULL	NULL	NULL	3	Using where
 1	SIMPLE	t2	ALL	f4	NULL	NULL	NULL	11	Using where; Using join buffer (flat, BNL join)
+1	SIMPLE	t1	Eliminated	NULL	NULL	NULL	NULL	NULL	
 # ^^ The above must not produce a QEP of t3,t5,t2,t4
 #    as that violates the "no interleaving of outer join nests" rule.
 DROP TABLE t1,t2,t3,t4,t5;
@@ -654,6 +1456,7 @@ SELECT SUM(a) FROM t1 LEFT JOIN t2 ON b=a
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t2	index	NULL	b	5	NULL	2	100.00	Using where; Using index
 2	DEPENDENT SUBQUERY	t1	system	NULL	NULL	NULL	NULL	1	100.00	
+2	DEPENDENT SUBQUERY	t2	Eliminated	NULL	NULL	NULL	NULL	NULL	NULL	
 Warnings:
 Note	1003	/* select#1 */ select `test`.`t2`.`b` AS `b` from `test`.`t2` where <expr_cache><`test`.`t2`.`b`>(<in_optimizer>(`test`.`t2`.`b`,<exists>(/* select#2 */ select sum(1) from dual where 1 having <cache>(`test`.`t2`.`b`) = <ref_null_helper>(sum(1)))))
 DROP TABLE t1,t2;
@@ -731,6 +1534,9 @@ group by t11.a;
 explain select t1.* from t1 left join v2b on v2b.a=t1.a;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	10	
+1	PRIMARY	<derived2>	Eliminated	NULL	NULL	NULL	NULL	NULL	
+2	DERIVED	t12	Eliminated	NULL	NULL	NULL	NULL	NULL	
+2	DERIVED	t11	Eliminated	NULL	NULL	NULL	NULL	NULL	
 # Check format JSON as well
 explain format=JSON select t1.* from t1 left join v2b on t1.a=v2b.a;
 EXPLAIN
@@ -746,6 +1552,32 @@ EXPLAIN
           "rows": 10,
           "filtered": 100
         }
+      },
+      {
+        "table": {
+          "table_name": "<derived2>",
+          "access_type": "Eliminated",
+          "materialized": {
+            "query_block": {
+              "select_id": 2,
+              "eliminated": true,
+              "nested_loop": [
+                {
+                  "table": {
+                    "table_name": "t12",
+                    "access_type": "Eliminated"
+                  }
+                },
+                {
+                  "table": {
+                    "table_name": "t11",
+                    "access_type": "Eliminated"
+                  }
+                }
+              ]
+            }
+          }
+        }
       }
     ]
   }
@@ -757,13 +1589,63 @@ from t11 left join t12 on t12.pk=t11.b
 group by t11.a) v2b on v2b.a=t1.a;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	10	
+1	PRIMARY	<derived2>	Eliminated	NULL	NULL	NULL	NULL	NULL	
+2	DERIVED	t12	Eliminated	NULL	NULL	NULL	NULL	NULL	
+2	DERIVED	t11	Eliminated	NULL	NULL	NULL	NULL	NULL	
+explain format=JSON select t1.* from t1 left join 
+(select t11.a as a, count(*) as b
+from t11 left join t12 on t12.pk=t11.b
+group by t11.a) v2b on v2b.a=t1.a;
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "const_condition": "1",
+    "nested_loop": [
+      {
+        "table": {
+          "table_name": "t1",
+          "access_type": "ALL",
+          "rows": 10,
+          "filtered": 100
+        }
+      },
+      {
+        "table": {
+          "table_name": "<derived2>",
+          "access_type": "Eliminated",
+          "materialized": {
+            "query_block": {
+              "select_id": 2,
+              "eliminated": true,
+              "nested_loop": [
+                {
+                  "table": {
+                    "table_name": "t12",
+                    "access_type": "Eliminated"
+                  }
+                },
+                {
+                  "table": {
+                    "table_name": "t11",
+                    "access_type": "Eliminated"
+                  }
+                }
+              ]
+            }
+          }
+        }
+      }
+    ]
+  }
+}
 # In this case v2b cannot be eliminated (since v2b.b is not unique)!
 explain select t1.* from t1 left join v2b on t1.a=v2b.b;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	10	
 1	PRIMARY	<derived2>	ref	key0	key0	8	test.t1.a	10	Using where
 2	DERIVED	t11	ALL	NULL	NULL	NULL	NULL	1000	Using temporary; Using filesort
-# Check format JSON as well
+2	DERIVED	t12	Eliminated	NULL	NULL	NULL	NULL	NULL	
 explain format=JSON select t1.* from t1 left join v2b on t1.a=v2b.b;
 EXPLAIN
 {
@@ -806,6 +1688,12 @@ EXPLAIN
                         "rows": 1000,
                         "filtered": 100
                       }
+                    },
+                    {
+                      "table": {
+                        "table_name": "t12",
+                        "access_type": "Eliminated"
+                      }
                     }
                   ]
                 }
@@ -825,7 +1713,9 @@ group by t11.a;
 explain select t1.* from t1 left join v2c on v2c.a=t1.a;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	10	
-# Check format JSON as well
+1	PRIMARY	<derived2>	Eliminated	NULL	NULL	NULL	NULL	NULL	
+2	DERIVED	t11	Eliminated	NULL	NULL	NULL	NULL	NULL	
+2	DERIVED	t12	Eliminated	NULL	NULL	NULL	NULL	NULL	
 explain format=JSON select t1.* from t1 left join v2c on v2c.a=t1.a;
 EXPLAIN
 {
@@ -840,6 +1730,32 @@ EXPLAIN
           "rows": 10,
           "filtered": 100
         }
+      },
+      {
+        "table": {
+          "table_name": "<derived2>",
+          "access_type": "Eliminated",
+          "materialized": {
+            "query_block": {
+              "select_id": 2,
+              "eliminated": true,
+              "nested_loop": [
+                {
+                  "table": {
+                    "table_name": "t11",
+                    "access_type": "Eliminated"
+                  }
+                },
+                {
+                  "table": {
+                    "table_name": "t12",
+                    "access_type": "Eliminated"
+                  }
+                }
+              ]
+            }
+          }
+        }
       }
     ]
   }
@@ -851,7 +1767,6 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	<derived2>	ref	key0	key0	5	test.t1.a	10	Using where
 2	DERIVED	t11	ALL	NULL	NULL	NULL	NULL	1000	Using temporary; Using filesort
 2	DERIVED	t12	eq_ref	PRIMARY	PRIMARY	4	test.t11.b	1	Using where
-# Check format JSON as well
 explain format=JSON select t1.* from t1 left join v2c on t1.a=v2c.b;
 EXPLAIN
 {
@@ -935,6 +1850,53 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 explain select t1.* from t1 left join v2d on v2d.a=t1.a and v2d.b=t1.b;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	10	
+1	PRIMARY	<derived2>	Eliminated	NULL	NULL	NULL	NULL	NULL	
+2	DERIVED	t11	Eliminated	NULL	NULL	NULL	NULL	NULL	
+2	DERIVED	t12	Eliminated	NULL	NULL	NULL	NULL	NULL	
+explain format=JSON select t1.* from t1 left join v2d on v2d.a=t1.a and v2d.b=t1.b;
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "const_condition": "1",
+    "nested_loop": [
+      {
+        "table": {
+          "table_name": "t1",
+          "access_type": "ALL",
+          "rows": 10,
+          "filtered": 100
+        }
+      },
+      {
+        "table": {
+          "table_name": "<derived2>",
+          "access_type": "Eliminated",
+          "materialized": {
+            "query_block": {
+              "select_id": 2,
+              "eliminated": true,
+              "nested_loop": [
+                {
+                  "table": {
+                    "table_name": "t11",
+                    "access_type": "Eliminated"
+                  }
+                },
+                {
+                  "table": {
+                    "table_name": "t12",
+                    "access_type": "Eliminated"
+                  }
+                }
+              ]
+            }
+          }
+        }
+      }
+    ]
+  }
+}
 create table t13 (dt date, b int);
 # Function year() in the GROUP BY list prevents treating this field 
 # as a unique key
@@ -974,12 +1936,56 @@ explain select t1.* from t1 left join
 on D.a1=t1.a and D.b1=t1.b;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	10	
+1	PRIMARY	<derived2>	Eliminated	NULL	NULL	NULL	NULL	NULL	
+2	DERIVED	t2	Eliminated	NULL	NULL	NULL	NULL	NULL	
+explain format=JSON select t1.* from t1 left join 
+(select a as a1, b as b1, count(*) as cnt from t2 group by a, b) D 
+on D.a1=t1.a and D.b1=t1.b;
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "const_condition": "1",
+    "nested_loop": [
+      {
+        "table": {
+          "table_name": "t1",
+          "access_type": "ALL",
+          "rows": 10,
+          "filtered": 100
+        }
+      },
+      {
+        "table": {
+          "table_name": "<derived2>",
+          "access_type": "Eliminated",
+          "materialized": {
+            "query_block": {
+              "select_id": 2,
+              "eliminated": true,
+              "nested_loop": [
+                {
+                  "table": {
+                    "table_name": "t2",
+                    "access_type": "Eliminated"
+                  }
+                }
+              ]
+            }
+          }
+        }
+      }
+    ]
+  }
+}
 # Different order of fields in GROUP BY and SELECT lists
 # must not hamper the elimination
 explain select t1.* from t1 left join 
 (select count(*) as cnt, b, a from t2 group by a, b) D on D.a=t1.a and D.b=t1.b;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	10	
+1	PRIMARY	<derived2>	Eliminated	NULL	NULL	NULL	NULL	NULL	
+2	DERIVED	t2	Eliminated	NULL	NULL	NULL	NULL	NULL	
 drop view v2b, v2c, v2d, v2e;
 drop table t1, t11, t12, t13, t2;
 #
diff --git a/mysql-test/main/table_elim.test b/mysql-test/main/table_elim.test
index a1f7ef91522..866d1866851 100644
--- a/mysql-test/main/table_elim.test
+++ b/mysql-test/main/table_elim.test
@@ -23,11 +23,13 @@ create table t3 (a int primary key, b int)
 --echo # This will be  eliminated:
 explain select t1.a from t1 left join t2 on t2.a=t1.a;
 explain extended select t1.a from t1 left join t2 on t2.a=t1.a;
+explain format=json select t1.a from t1 left join t2 on t2.a=t1.a;
 
 select t1.a from t1 left join t2 on t2.a=t1.a;
 
 --echo # This will not be eliminated as t2.b is in in select list:
 explain select * from t1 left join t2 on t2.a=t1.a;
+explain format=json select * from t1 left join t2 on t2.a=t1.a;
 
 --echo # This will not be eliminated as t2.b is in in order list:
 explain select t1.a from t1 left join t2 on t2.a=t1.a order by t2.b;
@@ -40,9 +42,11 @@ explain select t1.a from t1 left join t2 on t2.a=t1.a where t2.b < 3 or t2.b is
 
 --echo # Elimination of multiple tables:
 explain select t1.a from t1 left join (t2 join t3) on t2.a=t1.a and t3.a=t1.a;
+explain format=json select t1.a from t1 left join (t2 join t3) on t2.a=t1.a and t3.a=t1.a;
 
 --echo # Elimination of multiple tables (2):
 explain select t1.a from t1 left join (t2 join t3 on t2.b=t3.b) on t2.a=t1.a and t3.a=t1.a;
+explain format=json select t1.a from t1 left join (t2 join t3 on t2.b=t3.b) on t2.a=t1.a and t3.a=t1.a;
 
 --echo # Elimination when done within an outer join nest:
 explain extended
@@ -50,14 +54,22 @@ select t0.*
 from
   t0 left join (t1 left join (t2 join t3 on t2.b=t3.b) on t2.a=t1.a and
   t3.a=t1.a) on t0.a=t1.a;
+  
+explain format=json
+select t0.*
+from
+  t0 left join (t1 left join (t2 join t3 on t2.b=t3.b) on t2.a=t1.a and
+  t3.a=t1.a) on t0.a=t1.a;
 
 --echo # Elimination with aggregate functions
 explain select count(*) from t1 left join t2 on t2.a=t1.a;
+explain format=json select count(*) from t1 left join t2 on t2.a=t1.a;
 explain select count(1) from t1 left join t2 on t2.a=t1.a;
 explain select count(1) from t1 left join t2 on t2.a=t1.a group by t1.a;
 
 --echo This must not use elimination:
 explain select count(1) from t1 left join t2 on t2.a=t1.a group by t2.a;
+explain format=json select count(1) from t1 left join t2 on t2.a=t1.a group by t2.a;
 
 drop table t0, t1, t2, t3;
 
@@ -109,23 +121,31 @@ from
 
 --echo This should use one table:
 explain select id from v1 where id=2;
+explain format=json select id from v1 where id=2;
 --echo This should use one table:
 explain extended select id from v1 where id in (1,2,3,4);
+explain format=json select id from v1 where id in (1,2,3,4);
 --echo This should use facts and a1 tables:
 explain extended select id from v1 where attr1 between 12 and 14;
+explain format=json select id from v1 where attr1 between 12 and 14;
 --echo This should use facts, a2 and its subquery:
 explain extended select id from v1 where attr2 between 12 and 14;
+explain format=json select id from v1 where attr2 between 12 and 14;
 
 # Repeat for v2: 
 
 --echo This should use one table:
 explain select id from v2 where id=2;
+explain format=json select id from v2 where id=2;
 --echo This should use one table:
 explain extended select id from v2 where id in (1,2,3,4);
+explain format=json select id from v2 where id in (1,2,3,4);
 --echo This should use facts and a1 tables:
 explain extended select id from v2 where attr1 between 12 and 14;
+explain format=json select id from v2 where attr1 between 12 and 14;
 --echo This should use facts, a2 and its subquery:
 explain extended select id from v2 where attr2 between 12 and 14;
+explain format=json select id from v2 where attr2 between 12 and 14;
 
 drop view v1, v2;
 drop table t0, t1, t2;
@@ -144,6 +164,9 @@ insert into t2 select a,a,a,a from t1;
 explain select t1.* from t1 left join t2 on t2.pk1=t1.a and 
                                             t2.pk2=t2.pk1+1 and
                                             t2.pk3=t2.pk2+1;
+explain format=json select t1.* from t1 left join t2 on t2.pk1=t1.a and 
+                                                        t2.pk2=t2.pk1+1 and
+                                                        t2.pk3=t2.pk2+1;
 
 --echo This must use only t1:
 explain select t1.* from t1 left join t2 on t2.pk1=t1.a and 
@@ -175,20 +198,33 @@ insert into t3 select * from t1;
 
 explain 
 select t1.* from t1 left join ( t2 left join t3 on t3.pk=t2.col) on t2.col=t1.col;
+explain format=json
+select t1.* from t1 left join ( t2 left join t3 on t3.pk=t2.col) on t2.col=t1.col;
 
 explain 
 select t1.*, t2.* from t1 left join (t2 left join t3 on t3.pk=t2.col) on t2.pk=t1.col;
+explain format=json
+select t1.*, t2.* from t1 left join (t2 left join t3 on t3.pk=t2.col) on t2.pk=t1.col;
 
 explain select t1.* 
 from 
   t1 left join ( t2 left join t3 on t3.pk=t2.col or t3.pk=t2.col) 
   on t2.col=t1.col or t2.col=t1.col;
+explain format=json select t1.* 
+from 
+  t1 left join ( t2 left join t3 on t3.pk=t2.col or t3.pk=t2.col) 
+  on t2.col=t1.col or t2.col=t1.col;
 
 explain select t1.*, t2.* 
 from 
   t1 left join 
   (t2 left join t3 on t3.pk=t2.col or t3.pk=t2.col) 
   on t2.pk=t1.col or t2.pk=t1.col;
+explain format=json select t1.*, t2.* 
+from 
+  t1 left join 
+  (t2 left join t3 on t3.pk=t2.col or t3.pk=t2.col) 
+  on t2.pk=t1.col or t2.pk=t1.col;
 
 drop table t1, t2, t3;
 
@@ -681,11 +717,13 @@ explain select t1.* from t1 left join
   (select t11.a as a, count(*) as b
    from t11 left join t12 on t12.pk=t11.b
    group by t11.a) v2b on v2b.a=t1.a;
+explain format=JSON select t1.* from t1 left join 
+  (select t11.a as a, count(*) as b
+   from t11 left join t12 on t12.pk=t11.b
+   group by t11.a) v2b on v2b.a=t1.a;
 
 --echo # In this case v2b cannot be eliminated (since v2b.b is not unique)!
 explain select t1.* from t1 left join v2b on t1.a=v2b.b;
-
---echo # Check format JSON as well
 explain format=JSON select t1.* from t1 left join v2b on t1.a=v2b.b;
 
 create view v2c as 
@@ -695,14 +733,10 @@ group by t11.a;
 
 --echo # The whole v2c is eliminated
 explain select t1.* from t1 left join v2c on v2c.a=t1.a;
-
---echo # Check format JSON as well
 explain format=JSON select t1.* from t1 left join v2c on v2c.a=t1.a;
 
 --echo # In this case v2c cannot be eliminated (since v2c.b is not unique)!
 explain select t1.* from t1 left join v2c on t1.a=v2c.b;
-
---echo # Check format JSON as well
 explain format=JSON select t1.* from t1 left join v2c on t1.a=v2c.b;
 
 --echo # Create a view with multiple fields in the GROUP BY clause:
@@ -716,6 +750,7 @@ explain select t1.* from t1 left join v2d on v2d.a=t1.a;
 
 --echo # This must be eliminated since both fields are bound:
 explain select t1.* from t1 left join v2d on v2d.a=t1.a and v2d.b=t1.b;
+explain format=JSON select t1.* from t1 left join v2d on v2d.a=t1.a and v2d.b=t1.b;
 
 create table t13 (dt date, b int);
 
@@ -746,6 +781,9 @@ explain select t1.* from t1 left join
 explain select t1.* from t1 left join 
   (select a as a1, b as b1, count(*) as cnt from t2 group by a, b) D 
   on D.a1=t1.a and D.b1=t1.b;
+explain format=JSON select t1.* from t1 left join 
+  (select a as a1, b as b1, count(*) as cnt from t2 group by a, b) D 
+  on D.a1=t1.a and D.b1=t1.b;
   
 --echo # Different order of fields in GROUP BY and SELECT lists
 --echo # must not hamper the elimination
diff --git a/mysql-test/main/table_elim_debug.result b/mysql-test/main/table_elim_debug.result
index ae49b1433fd..50449354b4d 100644
--- a/mysql-test/main/table_elim_debug.result
+++ b/mysql-test/main/table_elim_debug.result
@@ -6,6 +6,7 @@ as select a, a as b from t1 where a in (1,2);
 explain select t1.a from t1 left join t2 on t2.a=t1.a;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	4	
+1	SIMPLE	t2	Eliminated	NULL	NULL	NULL	NULL	NULL	
 set optimizer_switch='table_elimination=off';
 explain select t1.a from t1 left join t2 on t2.a=t1.a;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
@@ -15,8 +16,10 @@ set optimizer_switch='table_elimination=on';
 explain select t1.a from t1 left join t2 on t2.a=t1.a;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	4	
+1	SIMPLE	t2	Eliminated	NULL	NULL	NULL	NULL	NULL	
 set optimizer_switch='table_elimination=default';
 explain select t1.a from t1 left join t2 on t2.a=t1.a;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	4	
+1	SIMPLE	t2	Eliminated	NULL	NULL	NULL	NULL	NULL	
 drop table t1, t2;
diff --git a/mysql-test/main/type_varchar.result b/mysql-test/main/type_varchar.result
index cfc8218b8ab..2a7c0416dbe 100644
--- a/mysql-test/main/type_varchar.result
+++ b/mysql-test/main/type_varchar.result
@@ -673,6 +673,7 @@ c
 EXPLAIN SELECT t1.* FROM t1 LEFT JOIN t2 USING (c1);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	
+1	SIMPLE	t2	Eliminated	NULL	NULL	NULL	NULL	NULL	
 ALTER TABLE t1 MODIFY c1 VARBINARY(10);
 SELECT t1.* FROM t1 LEFT JOIN t2 USING (c1);
 c1
@@ -682,6 +683,7 @@ c
 EXPLAIN SELECT t1.* FROM t1 LEFT JOIN t2 USING (c1);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	
+1	SIMPLE	t2	Eliminated	NULL	NULL	NULL	NULL	NULL	
 DROP TABLE t1,t2;
 #
 # End of 10.0 tests
diff --git a/sql/sql_explain.cc b/sql/sql_explain.cc
index 7c3918bfd20..d767592bd99 100644
--- a/sql/sql_explain.cc
+++ b/sql/sql_explain.cc
@@ -541,7 +541,8 @@ uint Explain_union::make_union_table_name(char *buf)
 int Explain_union::print_explain(Explain_query *query, 
                                  select_result_sink *output,
                                  uint8 explain_flags, 
-                                 bool is_analyze)
+                                 bool is_analyze,
+                                 bool is_eliminated)
 {
   THD *thd= output->thd;
   MEM_ROOT *mem_root= thd->mem_root;
@@ -642,7 +643,8 @@ int Explain_union::print_explain(Explain_query *query,
 
 void Explain_union::print_explain_json(Explain_query *query, 
                                        Json_writer *writer, bool is_analyze,
-                                       bool no_tmp_tbl)
+                                       bool no_tmp_tbl,
+                                       bool is_eliminated)
 {
   Json_writer_nesting_guard guard(writer);
   char table_name_buffer[SAFE_NAME_LEN];
@@ -713,8 +715,9 @@ int Explain_node::print_explain_for_children(Explain_query *query,
 {
   for (int i= 0; i < (int) children.elements(); i++)
   {
-    Explain_node *node= query->get_node(children.at(i));
-    if (node->print_explain(query, output, explain_flags, is_analyze))
+    Explain_node *node= query->get_node(children.at(i).select_no);
+    if (node->print_explain(query, output, explain_flags, is_analyze,
+                            children.at(i).is_eliminated))
       return 1;
   }
   return 0;
@@ -758,7 +761,7 @@ void Explain_node::print_explain_json_for_children(Explain_query *query,
   bool started= false;
   for (int i= 0; i < (int) children.elements(); i++)
   {
-    Explain_node *node= query->get_node(children.at(i));
+    Explain_node *node= query->get_node(children.at(i).select_no);
     /* Derived tables are printed inside Explain_table_access objects */
     
     if (!is_connection_printable_in_json(node->connection_type))
@@ -771,7 +774,8 @@ void Explain_node::print_explain_json_for_children(Explain_query *query,
     }
 
     writer->start_object();
-    node->print_explain_json(query, writer, is_analyze, no_tmp_tbl);
+    node->print_explain_json(query, writer, is_analyze, no_tmp_tbl,
+                             children.at(i).is_eliminated);
     writer->end_object();
   }
 
@@ -821,7 +825,8 @@ Explain_basic_join::~Explain_basic_join()
 
 int Explain_select::print_explain(Explain_query *query, 
                                   select_result_sink *output,
-                                  uint8 explain_flags, bool is_analyze)
+                                  uint8 explain_flags, bool is_analyze,
+                                  bool is_eliminated)
 {
   THD *thd= output->thd;
   MEM_ROOT *mem_root= thd->mem_root;
@@ -885,13 +890,20 @@ int Explain_select::print_explain(Explain_query *query,
       }
     }
 
-    for (uint i=0; i< n_join_tabs; i++)
+    /*
+      If there is no elimination of the whole node then do the first pass:
+      print the tables which are not eliminated individually
+    */
+    if (!is_eliminated)
     {
-      join_tabs[i]->print_explain(output, explain_flags, is_analyze, select_id,
-                                  select_type, using_tmp, using_fs);
-      if (i == 0)
+      for (uint i= 0; i < n_join_tabs; i++)
       {
-        /* 
+        if (unlikely(join_tabs[i]->is_eliminated))
+          continue;
+        join_tabs[i]->print_explain(output, explain_flags, is_analyze,
+                                    select_id, select_type, using_tmp,
+                                    using_fs);
+        /*
           "Using temporary; Using filesort" should only be shown near the 1st
           table
         */
@@ -899,11 +911,25 @@ int Explain_select::print_explain(Explain_query *query,
         using_fs= false;
       }
     }
+
+    /*
+      Second pass: print the tables which have been eliminated
+      either due to the whole node elimination or individually
+    */
+    for (uint i= 0; i < n_join_tabs; i++)
+    {
+      if (unlikely(join_tabs[i]->is_eliminated || is_eliminated))
+        join_tabs[i]->print_explain(output, explain_flags, is_analyze,
+                                    select_id, select_type, false,
+                                    false, is_eliminated);
+    }
+
     for (uint i=0; i< n_join_tabs; i++)
     {
       Explain_basic_join* nest;
       if ((nest= join_tabs[i]->sjm_nest))
-        nest->print_explain(query, output, explain_flags, is_analyze);
+        nest->print_explain(query, output, explain_flags, is_analyze,
+                            is_eliminated);
     }
   }
 
@@ -913,7 +939,8 @@ int Explain_select::print_explain(Explain_query *query,
 
 int Explain_basic_join::print_explain(Explain_query *query, 
                                       select_result_sink *output,
-                                      uint8 explain_flags, bool is_analyze)
+                                      uint8 explain_flags, bool is_analyze,
+                                      bool is_eliminated)
 {
   for (uint i=0; i< n_join_tabs; i++)
   {
@@ -952,9 +979,20 @@ void Explain_select::add_linkage(Json_writer *writer)
 
 void Explain_select::print_explain_json(Explain_query *query, 
                                         Json_writer *writer, bool is_analyze,
-                                        bool no_tmp_tbl)
+                                        bool no_tmp_tbl,
+                                        bool is_eliminated)
 {
   Json_writer_nesting_guard guard(writer);
+  if (unlikely(is_eliminated))
+  {
+    writer->add_member("query_block").start_object();
+    writer->add_member("select_id").add_ll(select_id);
+    writer->add_member("eliminated").add_bool(true);
+    Explain_basic_join::print_explain_json_interns(query, writer, is_analyze,
+                                                   no_tmp_tbl, is_eliminated);
+    writer->end_object();
+    return;
+  }
   
   bool started_cache= print_explain_json_cache(writer, is_analyze);
 
@@ -1059,7 +1097,7 @@ void Explain_select::print_explain_json(Explain_query *query,
     }
     
     Explain_basic_join::print_explain_json_interns(query, writer, is_analyze,
-                                                   no_tmp_tbl);
+                                                   no_tmp_tbl, is_eliminated);
 
     for (;started_objects; started_objects--)
       writer->end_object();
@@ -1139,12 +1177,14 @@ void Explain_aggr_window_funcs::print_json_members(Json_writer *writer,
 
 void Explain_basic_join::print_explain_json(Explain_query *query, 
                                             Json_writer *writer, 
-                                            bool is_analyze, bool no_tmp_tbl)
+                                            bool is_analyze, bool no_tmp_tbl,
+                                            bool is_eliminated)
 {
   writer->add_member("query_block").start_object();
   writer->add_member("select_id").add_ll(select_id);
   
-  print_explain_json_interns(query, writer, is_analyze, no_tmp_tbl);
+  print_explain_json_interns(query, writer, is_analyze, no_tmp_tbl,
+    is_eliminated);
 
   writer->end_object();
 }
@@ -1153,27 +1193,50 @@ void Explain_basic_join::print_explain_json(Explain_query *query,
 void Explain_basic_join::
 print_explain_json_interns(Explain_query *query, 
                            Json_writer *writer, 
-                           bool is_analyze, bool no_tmp_tbl)
+                           bool is_analyze, bool no_tmp_tbl,
+                           bool is_eliminated)
 {
   {
     Json_writer_array loop(writer, "nested_loop");
-    for (uint i=0; i< n_join_tabs; i++)
+
+    /*
+      If there is no elimination of the whole node then do the first pass:
+      print the tables which are not eliminated individually
+    */
+    if (!is_eliminated)
     {
-      if (join_tabs[i]->start_dups_weedout)
+      for (uint i= 0; i < n_join_tabs; i++)
       {
-        writer->start_object();
-        writer->add_member("duplicates_removal");
-        writer->start_array();
-      }
+        if (unlikely(join_tabs[i]->is_eliminated))
+          continue;
+        if (join_tabs[i]->start_dups_weedout)
+        {
+          writer->start_object();
+          writer->add_member("duplicates_removal");
+          writer->start_array();
+        }
 
-      join_tabs[i]->print_explain_json(query, writer, is_analyze, no_tmp_tbl);
+        join_tabs[i]->print_explain_json(query, writer, is_analyze,
+                                         no_tmp_tbl);
 
-      if (join_tabs[i]->end_dups_weedout)
-      {
-        writer->end_array();
-        writer->end_object();
+        if (join_tabs[i]->end_dups_weedout)
+        {
+          writer->end_array();
+          writer->end_object();
+        }
       }
     }
+
+    /*
+      Second pass: print the tables which have been eliminated
+      either due to the whole node elimination or individually
+    */
+    for (uint i= 0; i < n_join_tabs; i++)
+    {
+      if (unlikely(join_tabs[i]->is_eliminated || is_eliminated))
+        join_tabs[i]->print_explain_json(query, writer, is_analyze,
+                                          no_tmp_tbl, true);
+    }
   } // "nested_loop"
   print_explain_json_for_children(query, writer, is_analyze, no_tmp_tbl);
 }
@@ -1332,8 +1395,14 @@ double Explain_table_access::get_r_filtered()
 int Explain_table_access::print_explain(select_result_sink *output, uint8 explain_flags, 
                                         bool is_analyze,
                                         uint select_id, const char *select_type,
-                                        bool using_temporary, bool using_filesort)
+                                        bool using_temporary, bool using_filesort,
+                                        bool is_parent_node_eliminated)
 {
+  // Check whether the table is eliminated itself or its parent node
+  if (unlikely(is_eliminated || is_parent_node_eliminated))
+    return print_explain_eliminated(output, explain_flags, is_analyze,
+                                    select_id, select_type);
+
   THD *thd= output->thd; // note: for SHOW EXPLAIN, this is target thd.
   MEM_ROOT *mem_root= thd->mem_root;
 
@@ -1552,6 +1621,72 @@ int Explain_table_access::print_explain(select_result_sink *output, uint8 explai
 }
 
 
+int Explain_table_access::print_explain_eliminated(select_result_sink *output,
+                                                   uint8 explain_flags,
+                                                   bool is_analyze,
+                                                   uint select_id,
+                                                   const char *select_type)
+{
+  THD *thd= output->thd; // note: for SHOW EXPLAIN, this is target thd.
+  MEM_ROOT *mem_root= thd->mem_root;
+
+  List<Item> item_list;
+  Item *item_null= new (mem_root) Item_null(thd);
+
+  /* `id` column */
+  item_list.push_back(new (mem_root) Item_int(thd, (int32) select_id),
+                      mem_root);
+
+  /* `select_type` column */
+  push_str(thd, &item_list, select_type);
+
+  /* `table` column */
+  push_string(thd, &item_list, &table_name);
+
+  /* `partitions` column */
+  if (explain_flags & DESCRIBE_PARTITIONS)
+    item_list.push_back(item_null, mem_root);
+
+  /* `type` column */
+  push_str(thd, &item_list, "Eliminated");
+
+  /* `possible_keys` column */
+  item_list.push_back(item_null, mem_root);
+
+  /* `key` */
+  item_list.push_back(item_null, mem_root);
+
+  /* `key_len` */
+  item_list.push_back(item_null, mem_root);
+
+  /* `ref` */
+  item_list.push_back(item_null, mem_root);
+
+  /* `rows` */
+  item_list.push_back(item_null, mem_root);
+
+  /* `r_rows` */
+  if (is_analyze)
+    item_list.push_back(item_null, mem_root);
+
+  /* `filtered` */
+  if (explain_flags &DESCRIBE_EXTENDED || is_analyze)
+    item_list.push_back(item_null, mem_root);
+
+  /* `r_filtered` */
+  if (is_analyze)
+    item_list.push_back(item_null, mem_root);
+
+  /* `Extra` */
+  push_str(thd, &item_list, "");
+
+  if (output->send_data(item_list))
+    return 1;
+
+  return 0;
+}
+
+
 /**
   Adds copy of the string to the list
 
@@ -1760,8 +1895,15 @@ void Explain_rowid_filter::print_explain_json(Explain_query *query,
 
 void Explain_table_access::print_explain_json(Explain_query *query,
                                               Json_writer *writer,
-                                              bool is_analyze, bool no_tmp_tbl)
+                                              bool is_analyze, bool no_tmp_tbl,
+                                              bool is_parent_node_eliminated)
 {
+  if (unlikely(is_eliminated || is_parent_node_eliminated))
+  {
+    print_explain_json_eliminated(query, writer);
+    return;
+  }
+  
   Json_writer_object jsobj(writer);
   
   if (pre_join_sort)
@@ -2005,6 +2147,30 @@ void Explain_table_access::print_explain_json(Explain_query *query,
 }
 
 
+void Explain_table_access::print_explain_json_eliminated(Explain_query *query,
+                                                         Json_writer *writer)
+{
+  Json_writer_object jsobj(writer);
+  writer->add_member("table").start_object();
+  writer->add_member("table_name").add_str(table_name);
+  writer->add_member("access_type").add_str("Eliminated");
+  if (derived_select_number)
+  {
+    /* This is a derived table. Print its contents here */
+    writer->add_member("materialized").start_object();
+    Explain_node *node= query->get_node(derived_select_number);
+    if (node->get_type() == Explain_node::EXPLAIN_SELECT &&
+        ((Explain_select *) node)->is_lateral)
+    {
+      writer->add_member("lateral").add_ll(1);
+    }
+    node->print_explain_json(query, writer, false, false, true);
+    writer->end_object();
+  }
+  writer->end_object();
+}
+
+
 /*
   Elements in this array match members of enum explain_extra_tag, defined in
   sql_explain.h
@@ -2275,8 +2441,8 @@ void Explain_quick_select::print_key_len(String *str)
 
 int Explain_delete::print_explain(Explain_query *query, 
                                   select_result_sink *output,
-                                  uint8 explain_flags,
-                                  bool is_analyze)
+                                  uint8 explain_flags, bool is_analyze,
+                                  bool is_eliminated)
 {
   if (deleting_all_rows)
   {
@@ -2290,7 +2456,7 @@ int Explain_delete::print_explain(Explain_query *query,
   else
   {
     return Explain_update::print_explain(query, output, explain_flags,
-                                         is_analyze);
+                                         is_analyze, is_eliminated);
   }
 }
 
@@ -2298,7 +2464,8 @@ int Explain_delete::print_explain(Explain_query *query,
 void Explain_delete::print_explain_json(Explain_query *query, 
                                         Json_writer *writer,
                                         bool is_analyze,
-                                        bool no_tmp_tbl)
+                                        bool no_tmp_tbl,
+                                        bool is_eliminated)
 {
   Json_writer_nesting_guard guard(writer);
 
@@ -2320,7 +2487,8 @@ void Explain_delete::print_explain_json(Explain_query *query,
 int Explain_update::print_explain(Explain_query *query, 
                                   select_result_sink *output,
                                   uint8 explain_flags,
-                                  bool is_analyze)
+                                  bool is_analyze,
+                                  bool is_eliminated)
 {
   StringBuffer<64> key_buf;
   StringBuffer<64> key_len_buf;
@@ -2417,7 +2585,8 @@ int Explain_update::print_explain(Explain_query *query,
 void Explain_update::print_explain_json(Explain_query *query,
                                         Json_writer *writer,
                                         bool is_analyze,
-                                        bool no_tmp_tbl)
+                                        bool no_tmp_tbl, 
+                                        bool is_eliminated)
 {
   Json_writer_nesting_guard guard(writer);
 
@@ -2602,7 +2771,8 @@ void Explain_update::print_explain_json(Explain_query *query,
 int Explain_insert::print_explain(Explain_query *query, 
                                   select_result_sink *output, 
                                   uint8 explain_flags,
-                                  bool is_analyze)
+                                  bool is_analyze,
+                                  bool is_eliminated)
 {
   const char *select_type="INSERT";
   print_explain_row(output, explain_flags, is_analyze,
@@ -2625,7 +2795,7 @@ int Explain_insert::print_explain(Explain_query *query,
 
 void Explain_insert::print_explain_json(Explain_query *query, 
                                         Json_writer *writer, bool is_analyze,
-                                        bool no_tmp_tbl)
+                                        bool no_tmp_tbl, bool is_eliminated)
 {
   Json_writer_nesting_guard guard(writer);
 
diff --git a/sql/sql_explain.h b/sql/sql_explain.h
index 919d51aef8b..1fe7d08c6f8 100644
--- a/sql/sql_explain.h
+++ b/sql/sql_explain.h
@@ -120,21 +120,29 @@ public:
   enum explain_connection_type connection_type;
 
 protected:
+  struct Child_info
+  {
+    int select_no;
+    bool is_eliminated;
+  };
   /* 
     A node may have children nodes. When a node's explain structure is 
-    created, children nodes may not yet have QPFs. This is why we store ids.
+    created, children nodes may not yet have QPFs. This is why we store
+    pairs {select_no, is_eliminated}.
   */
-  Dynamic_array<int> children;
+  Dynamic_array<Child_info> children;
 public:
-  void add_child(int select_no)
+  void add_child(int select_no, bool is_eliminated= false)
   {
-    children.append(select_no);
+    children.append({select_no, is_eliminated});
   }
 
   virtual int print_explain(Explain_query *query, select_result_sink *output, 
-                            uint8 explain_flags, bool is_analyze)=0;
+                            uint8 explain_flags, bool is_analyze,
+                            bool is_eliminated= false)= 0;
   virtual void print_explain_json(Explain_query *query, Json_writer *writer, 
-                                  bool is_analyze, bool no_tmp_tbl)= 0;
+                                  bool is_analyze, bool no_tmp_tbl,
+                                  bool is_eliminated= false)= 0;
 
   int print_explain_for_children(Explain_query *query, select_result_sink *output, 
                                  uint8 explain_flags, bool is_analyze);
@@ -172,12 +180,15 @@ public:
   uint select_id;
 
   int print_explain(Explain_query *query, select_result_sink *output,
-                    uint8 explain_flags, bool is_analyze);
-  void print_explain_json(Explain_query *query, Json_writer *writer, 
-                          bool is_analyze, bool no_tmp_tbl);
-
+                    uint8 explain_flags, bool is_analyze,
+                    bool is_eliminated= false) override;
+  void print_explain_json(Explain_query *query, Json_writer *writer,
+                          bool is_analyze, bool no_tmp_tbl,
+                          bool is_eliminated= false) override;
+  // OLEGS: check this func and is the flag is_eliminated required
   void print_explain_json_interns(Explain_query *query, Json_writer *writer,
-                                  bool is_analyze, bool no_tmp_tbl);
+                                  bool is_analyze, bool no_tmp_tbl,
+                                  bool is_eliminated);
 
   /* A flat array of Explain structs for tables. */
   Explain_table_access** join_tabs;
@@ -258,10 +269,12 @@ public:
   */
   Explain_aggr_node* aggr_tree;
 
-  int print_explain(Explain_query *query, select_result_sink *output, 
-                    uint8 explain_flags, bool is_analyze);
-  void print_explain_json(Explain_query *query, Json_writer *writer, 
-                          bool is_analyze, bool no_tmp_tbl);
+  int print_explain(Explain_query *query, select_result_sink *output,
+                    uint8 explain_flags, bool is_analyze,
+                    bool is_eliminated= false) override;
+  void print_explain_json(Explain_query *query, Json_writer *writer,
+                          bool is_analyze, bool no_tmp_tbl,
+                          bool is_eliminated= false) override;
   
   Table_access_tracker *get_using_temporary_read_tracker()
   {
@@ -346,7 +359,7 @@ extern const char *pushed_select_text;
 class Explain_union : public Explain_node
 {
 public:
-  Explain_union(MEM_ROOT *root, bool is_analyze) : 
+  Explain_union(MEM_ROOT *root, bool is_analyze) :
     Explain_node(root), union_members(PSI_INSTRUMENT_MEM),
     is_recursive_cte(false),
     fake_select_lex_explain(root, is_analyze)
@@ -377,10 +390,12 @@ public:
   {
     union_members.append(select_no);
   }
-  int print_explain(Explain_query *query, select_result_sink *output, 
-                    uint8 explain_flags, bool is_analyze);
-  void print_explain_json(Explain_query *query, Json_writer *writer, 
-                          bool is_analyze, bool no_tmp_tbl);
+  int print_explain(Explain_query *query, select_result_sink *output,
+                    uint8 explain_flags, bool is_analyze,
+                    bool is_eliminated) override;
+  void print_explain_json(Explain_query *query, Json_writer *writer,
+                          bool is_analyze, bool no_tmp_tbl,
+                          bool is_eliminated) override;
 
   const char *fake_select_type;
   bool using_filesort;
@@ -755,6 +770,7 @@ public:
     full_scan_on_null_key(false),
     start_dups_weedout(false),
     end_dups_weedout(false),
+    is_eliminated(false),
     where_cond(NULL),
     cache_cond(NULL),
     pushed_index_cond(NULL),
@@ -836,6 +852,9 @@ public:
 
   bool start_dups_weedout;
   bool end_dups_weedout;
+
+  /* OLEGS: comment*/
+  bool is_eliminated;
   
   /*
     Note: lifespan of WHERE condition is less than lifespan of this object.
@@ -874,9 +893,11 @@ public:
   int print_explain(select_result_sink *output, uint8 explain_flags, 
                     bool is_analyze,
                     uint select_id, const char *select_type,
-                    bool using_temporary, bool using_filesort);
+                    bool using_temporary, bool using_filesort, 
+                    bool is_parent_node_eliminated= false);
   void print_explain_json(Explain_query *query, Json_writer *writer,
-                          bool is_analyze, bool no_tmp_tbl);
+                          bool is_analyze, bool no_tmp_tbl,
+                          bool is_parent_node_eliminated= false);
 
 private:
   void append_tag_name(String *str, enum explain_extra_tag tag);
@@ -885,6 +906,11 @@ private:
   double get_r_filtered();
   void tag_to_json(Json_writer *writer, enum explain_extra_tag tag,
                    bool no_tmp_tbl);
+  int print_explain_eliminated(select_result_sink *output, uint8 explain_flags,
+                               bool is_analyze, uint select_id,
+                               const char *select_type);
+  void print_explain_json_eliminated(Explain_query *query,
+                                     Json_writer *writer);
 };
 
 
@@ -965,9 +991,11 @@ public:
   Exec_time_tracker table_tracker;
 
   virtual int print_explain(Explain_query *query, select_result_sink *output, 
-                            uint8 explain_flags, bool is_analyze);
+                            uint8 explain_flags, bool is_analyze,
+                            bool is_eliminated= false) override;
   virtual void print_explain_json(Explain_query *query, Json_writer *writer,
-                                  bool is_analyze, bool no_tmp_tbl);
+                                  bool is_analyze, bool no_tmp_tbl,
+                                  bool is_eliminated= false) override;
 };
 
 
@@ -991,9 +1019,11 @@ public:
   uint get_select_id() { return 1; /* always root */ }
 
   int print_explain(Explain_query *query, select_result_sink *output, 
-                    uint8 explain_flags, bool is_analyze);
+                    uint8 explain_flags, bool is_analyze,
+                    bool is_eliminated= false) override;
   void print_explain_json(Explain_query *query, Json_writer *writer, 
-                          bool is_analyze, bool no_tmp_tbl);
+                          bool is_analyze, bool no_tmp_tbl,
+                          bool is_eliminated= false) override;
 };
 
 
@@ -1018,9 +1048,11 @@ public:
   virtual uint get_select_id() { return 1; /* always root */ }
 
   virtual int print_explain(Explain_query *query, select_result_sink *output, 
-                            uint8 explain_flags, bool is_analyze);
+                            uint8 explain_flags, bool is_analyze,
+                            bool is_eliminated= false) override;
   virtual void print_explain_json(Explain_query *query, Json_writer *writer,
-                                  bool is_analyze, bool no_tmp_tbl);
+                                  bool is_analyze, bool no_tmp_tbl,
+                                  bool is_eliminated= false) override;
 };
 
 
diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc
index 3db784ce61d..4590804437d 100644
--- a/sql/sql_lex.cc
+++ b/sql/sql_lex.cc
@@ -11917,7 +11917,7 @@ bool SELECT_LEX_UNIT::explainable() const
     EXPLAIN/ANALYZE unit, when:
     (1) if it's a subquery - it's not part of eliminated WHERE/ON clause.
     (2) if it's a CTE - it's not hanging (needed for execution)
-    (3) if it's a derived - it's not merged or eliminated
+    (3) if it's a derived - it's not merged
     if it's not 1/2/3 - it's some weird internal thing, ignore it
   */
 
@@ -11927,12 +11927,12 @@ bool SELECT_LEX_UNIT::explainable() const
              derived && derived->derived_result &&
                !with_element->is_hanging_recursive(): // (2)
              derived ?
-               derived->is_materialized_derived() && // (3)
-                 !is_derived_eliminated() :
+               derived->is_materialized_derived() :   // (3)
                false;
 }
 
-bool SELECT_LEX_UNIT::is_derived_eliminated() const
+
+bool SELECT_LEX_UNIT::is_eliminated() const
 {
   if (!derived)
     return false;
diff --git a/sql/sql_lex.h b/sql/sql_lex.h
index 5647484e286..6f30a1a5397 100644
--- a/sql/sql_lex.h
+++ b/sql/sql_lex.h
@@ -1027,6 +1027,7 @@ public:
   unit_common_op common_op();
 
   bool explainable() const;
+  bool is_eliminated() const;
 
   void reset_distinct();
   void fix_distinct();
@@ -1040,8 +1041,6 @@ public:
 
   friend class st_select_lex;
 
-private:
-  bool is_derived_eliminated() const;
 };
 
 typedef class st_select_lex_unit SELECT_LEX_UNIT;
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index 3b07122e7c1..33f02d64744 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -28228,19 +28228,17 @@ int JOIN::save_explain_data_intern(Explain_query *output,
       JOIN_TAB *saved_join_tab= NULL;
       TABLE *cur_table= tab->table;
 
-      /* Don't show eliminated tables */
-      if (cur_table->map & join->eliminated_tables)
-      {
-        used_tables|= cur_table->map;
-        continue;
-      }
-
 
       Explain_table_access *eta= (new (output->mem_root)
                                   Explain_table_access(output->mem_root));
 
       if (!eta)
         DBUG_RETURN(1);
+      if (cur_table->map & join->eliminated_tables)
+      {
+        eta->is_eliminated= true;
+      }
+
       if (tab->bush_root_tab != prev_bush_root_tab)
       {
         if (tab->bush_root_tab)
@@ -28293,7 +28291,8 @@ int JOIN::save_explain_data_intern(Explain_query *output,
          tmp_unit;
          tmp_unit= tmp_unit->next_unit())
       if (tmp_unit->explainable())
-        explain->add_child(tmp_unit->first_select()->select_number);
+        explain->add_child(tmp_unit->first_select()->select_number,
+                           tmp_unit->is_eliminated());
 
   if (select_lex->is_top_level_node())
     output->query_plan_ready();
@@ -28355,7 +28354,7 @@ static void select_describe(JOIN *join, bool need_tmp_table, bool need_order,
       DBUG_ASSERT(ref == unit->item);
     }
 
-    if (unit->explainable())
+    if (unit->explainable() && !unit->is_eliminated())
     {
       if (mysql_explain_union(thd, unit, result))
         DBUG_VOID_RETURN;