MDEV-27957 Select from view with subselect fails with lost connectionbb-10.3-MDEV-27957

The problem is in getting select number of excluded EXPLAIN-node,
because on excluding links in exclued unit removed.

IMHO it is simplier to get the number in other way and let the EXPLAIN
proceed then fix EXPLAIN. (the other solution is to do not create nodes
for excluded units)

4 files changed, 110 insertions, 1 deletions

diff --git a/mysql-test/main/explain.result b/mysql-test/main/explain.result
index f593e0dfaba..fb3584d853b 100644
--- a/mysql-test/main/explain.result
+++ b/mysql-test/main/explain.result
@@ -407,3 +407,65 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 2	SUBQUERY	t1	system	NULL	NULL	NULL	NULL	1	
 drop table t1, t2;
 # End of 10.1 tests
+#
+# MDEV-27957: Select from view with subselect fails with lost connection
+#
+CREATE TABLE t1 (id INT PRIMARY KEY);
+INSERT INTO t1 VALUES (1),(2);
+CREATE VIEW v1 AS SELECT
+1 IN (
+SELECT
+(SELECT COUNT(id)
+FROM t1
+WHERE t1_outer.id <> id
+) AS f
+FROM
+t1 AS t1_outer
+GROUP BY f
+);
+explain extended SELECT
+1 IN (
+SELECT
+(SELECT COUNT(id)
+FROM t1
+WHERE t1_outer.id <> id
+) AS f
+FROM
+t1 AS t1_outer
+GROUP BY f
+);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+2	SUBQUERY	t1_outer	index	NULL	PRIMARY	4	NULL	2	100.00	Using where; Using index
+3	DEPENDENT SUBQUERY	t1	index	NULL	PRIMARY	4	NULL	2	100.00	Using where; Using index
+Warnings:
+Note	1276	Field or reference 'test.t1_outer.id' of SELECT #3 was resolved in SELECT #2
+Note	1003	/* select#1 */ select <in_optimizer>(1,<exists>(/* select#2 */ select <expr_cache><`t1_outer`.`id`>((/* select#3 */ select count(`test`.`t1`.`id`) from `test`.`t1` where `t1_outer`.`id` <> `test`.`t1`.`id`)) from `test`.`t1` `t1_outer` where 1 = (/* select#3 */ select count(`test`.`t1`.`id`) from `test`.`t1` where `t1_outer`.`id` <> `test`.`t1`.`id`) or (/* select#3 */ select count(`test`.`t1`.`id`) from `test`.`t1` where `t1_outer`.`id` <> `test`.`t1`.`id`) is null having (/* select#3 */ select count(`test`.`t1`.`id`) from `test`.`t1` where `t1_outer`.`id` <> `test`.`t1`.`id`) is null)) AS `1 IN (
+SELECT
+(SELECT COUNT(id)
+FROM t1
+WHERE t1_outer.id <> id
+) AS f
+FROM
+t1 AS t1_outer
+GROUP BY f
+)`
+SELECT * FROM v1;
+Name_exp_1
+1
+explain extended
+SELECT * FROM v1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	<derived2>	system	NULL	NULL	NULL	NULL	1	100.00	
+2	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+3	SUBQUERY	t1_outer	index	NULL	PRIMARY	4	NULL	2	100.00	Using where; Using index
+4	DEPENDENT SUBQUERY	t1	index	NULL	PRIMARY	4	NULL	2	100.00	Using where; Using index
+Warnings:
+Note	1276	Field or reference 'test.t1_outer.id' of SELECT #4 was resolved in SELECT #3
+Note	1276	Field or reference 'test.t1_outer.id' of SELECT #5 was resolved in SELECT #3
+Note	1003	/* select#1 */ select 1 AS `Name_exp_1` from dual
+DROP VIEW v1;
+DROP TABLE t1;
+#
+# End of 10.3 tests
+#
diff --git a/mysql-test/main/explain.test b/mysql-test/main/explain.test
index d5be354c852..416abf9888d 100644
--- a/mysql-test/main/explain.test
+++ b/mysql-test/main/explain.test
@@ -333,3 +333,42 @@ explain replace into t2 select 100, (select a from t1);
 drop table t1, t2;
 
 --echo # End of 10.1 tests
+
+--echo #
+--echo # MDEV-27957: Select from view with subselect fails with lost connection
+--echo #
+
+CREATE TABLE t1 (id INT PRIMARY KEY);
+INSERT INTO t1 VALUES (1),(2);
+
+CREATE VIEW v1 AS SELECT
+  1 IN (
+    SELECT
+      (SELECT COUNT(id)
+       FROM t1
+       WHERE t1_outer.id <> id
+       ) AS f
+    FROM
+      t1 AS t1_outer
+    GROUP BY f
+  );
+explain extended SELECT
+  1 IN (
+    SELECT
+      (SELECT COUNT(id)
+       FROM t1
+       WHERE t1_outer.id <> id
+       ) AS f
+    FROM
+      t1 AS t1_outer
+    GROUP BY f
+  );
+SELECT * FROM v1;
+explain extended
+SELECT * FROM v1;
+DROP VIEW v1;
+DROP TABLE t1;
+
+--echo #
+--echo # End of 10.3 tests
+--echo #
diff --git a/sql/item_subselect.cc b/sql/item_subselect.cc
index a5ad00c9bb9..19a13b24cd1 100644
--- a/sql/item_subselect.cc
+++ b/sql/item_subselect.cc
@@ -3679,6 +3679,13 @@ bool subselect_union_engine::is_executed() const
   return unit->executed;
 }
 
+
+int subselect_union_engine::get_identifier()
+{
+  return unit->first_select()->select_number;
+}
+
+
 void subselect_union_engine::force_reexecution()
 { 
   unit->executed= false;
@@ -6872,7 +6879,7 @@ void Item_subselect::init_expr_cache_tracker(THD *thd)
 
   Explain_query *qw= thd->lex->explain;
   DBUG_ASSERT(qw);
-  Explain_node *node= qw->get_node(unit->first_select()->select_number);
+  Explain_node *node= qw->get_node(engine->get_identifier());
   if (!node)
     return;
   DBUG_ASSERT(expr_cache->type() == Item::EXPR_CACHE_ITEM);
diff --git a/sql/item_subselect.h b/sql/item_subselect.h
index 16c683b27e0..35ad8e039c6 100644
--- a/sql/item_subselect.h
+++ b/sql/item_subselect.h
@@ -929,6 +929,7 @@ public:
                      bool temp= FALSE);
   bool no_tables();
   bool is_executed() const;
+  int get_identifier();
   void force_reexecution();
   bool no_rows();
   virtual enum_engine_type engine_type() { return UNION_ENGINE; }