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)

2 files changed, 101 insertions, 0 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 #