Merge 10.6 into 10.8

1 files changed, 482 insertions, 0 deletions

diff --git a/mysql-test/main/derived_view.result b/mysql-test/main/derived_view.result
index b86cd1c42cc..e040aa7c217 100644
--- a/mysql-test/main/derived_view.result
+++ b/mysql-test/main/derived_view.result
@@ -3765,3 +3765,485 @@ drop procedure sp2;
 drop view v, v2;
 drop table t1,t2;
 # End of 10.2 tests
+#
+# MDEV-30706: view defined as select with implicit grouping and
+#             a set function used in a subquery
+#
+CREATE TABLE t1 (a INT PRIMARY KEY, b INT);
+INSERT INTO t1 VALUES (1,1), (2,2);
+CREATE TABLE t2 (a INT PRIMARY KEY, b INT);
+INSERT INTO t2 VALUES (1,1), (3,3);
+CREATE TABLE t3 (a INT PRIMARY KEY, b INT);
+INSERT INTO t3 VALUES (2,2), (4,4), (7,7);
+CREATE TABLE t4 (a INT PRIMARY KEY, b INT);
+INSERT INTO t4 VALUES (2,2), (5,5), (7,7);
+CREATE VIEW v AS SELECT
+(SELECT SUM(t4.b) FROM t1, t2 WHERE t1.a = t2.b GROUP BY t1.a) AS m
+FROM t3, t4
+WHERE t3.a = t4.b;
+SELECT
+(SELECT SUM(t4.b) FROM t1, t2 WHERE t1.a = t2.b GROUP BY t1.a) AS m
+FROM t3, t4
+WHERE t3.a = t4.b;
+m
+9
+SELECT * FROM v;
+m
+9
+WITH cte AS ( SELECT
+(SELECT SUM(t4.b) FROM t1, t2 WHERE t1.a = t2.b GROUP BY t1.a) AS m
+FROM t3, t4
+WHERE t3.a = t4.b ) SELECT * FROM cte;
+m
+9
+EXPLAIN SELECT
+(SELECT SUM(t4.b) FROM t1, t2 WHERE t1.a = t2.b GROUP BY t1.a) AS m
+FROM t3, t4
+WHERE t3.a = t4.b;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t4	ALL	NULL	NULL	NULL	NULL	3	Using where
+1	PRIMARY	t3	eq_ref	PRIMARY	PRIMARY	4	test.t4.b	1	Using index
+2	DEPENDENT SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where; Using temporary; Using filesort
+2	DEPENDENT SUBQUERY	t1	eq_ref	PRIMARY	PRIMARY	4	test.t2.b	1	Using index
+EXPLAIN SELECT * FROM v;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	3	
+2	SUBQUERY	t4	ALL	NULL	NULL	NULL	NULL	3	Using where
+2	SUBQUERY	t3	eq_ref	PRIMARY	PRIMARY	4	test.t4.b	1	Using index
+3	DEPENDENT SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where; Using temporary; Using filesort
+3	DEPENDENT SUBQUERY	t1	eq_ref	PRIMARY	PRIMARY	4	test.t2.b	1	Using index
+EXPLAIN WITH cte AS ( SELECT
+(SELECT SUM(t4.b) FROM t1, t2 WHERE t1.a = t2.b GROUP BY t1.a) AS m
+FROM t3, t4
+WHERE t3.a = t4.b ) SELECT * FROM cte;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	3	
+2	DERIVED	t4	ALL	NULL	NULL	NULL	NULL	3	Using where
+2	DERIVED	t3	eq_ref	PRIMARY	PRIMARY	4	test.t4.b	1	Using index
+3	DEPENDENT SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where; Using temporary; Using filesort
+3	DEPENDENT SUBQUERY	t1	eq_ref	PRIMARY	PRIMARY	4	test.t2.b	1	Using index
+PREPARE stmt FROM "SELECT
+(SELECT SUM(t4.b) FROM t1, t2 WHERE t1.a = t2.b GROUP BY t1.a) AS m
+FROM t3, t4
+WHERE t3.a = t4.b";
+execute stmt;
+m
+9
+execute stmt;
+m
+9
+DEALLOCATE PREPARE stmt;
+PREPARE stmt FROM "SELECT * FROM v";
+execute stmt;
+m
+9
+execute stmt;
+m
+9
+DEALLOCATE PREPARE stmt;
+PREPARE stmt FROM "WITH cte AS ( SELECT
+(SELECT SUM(t4.b) FROM t1, t2 WHERE t1.a = t2.b GROUP BY t1.a) AS m
+FROM t3, t4
+WHERE t3.a = t4.b ) SELECT * FROM cte";
+execute stmt;
+m
+9
+execute stmt;
+m
+9
+DEALLOCATE PREPARE stmt;
+DROP VIEW v;
+DROP TABLE t1,t2,t3,t4;
+#
+# MDEV-29224: view defined as select with implicit grouping and
+#             a set function used in a subquery
+#
+CREATE TABLE t1 (f1 INT);
+INSERT INTO t1 VALUES (1),(2);
+CREATE TABLE t2 (f2 int);
+INSERT INTO t2 VALUES (3);
+CREATE VIEW v AS SELECT ( SELECT MAX(f1) FROM t2 ) FROM t1;
+SELECT ( SELECT MAX(f1) FROM t2 ) FROM t1;
+( SELECT MAX(f1) FROM t2 )
+2
+SELECT * FROM v;
+( SELECT MAX(f1) FROM t2 )
+2
+WITH cte AS ( SELECT ( SELECT MAX(f1) FROM t2 ) FROM t1 ) SELECT * FROM cte;
+( SELECT MAX(f1) FROM t2 )
+2
+EXPLAIN SELECT ( SELECT MAX(f1) FROM t2 ) FROM t1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	
+2	DEPENDENT SUBQUERY	t2	system	NULL	NULL	NULL	NULL	1	
+EXPLAIN SELECT * FROM v;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	2	
+2	SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	2	
+3	DEPENDENT SUBQUERY	t2	system	NULL	NULL	NULL	NULL	1	
+EXPLAIN WITH cte AS ( SELECT ( SELECT MAX(f1) FROM t2 ) FROM t1 ) SELECT * FROM cte;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	2	
+2	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	2	
+3	DEPENDENT SUBQUERY	t2	system	NULL	NULL	NULL	NULL	1	
+PREPARE stmt FROM "SELECT ( SELECT MAX(f1) FROM t2 ) FROM t1";
+execute stmt;
+( SELECT MAX(f1) FROM t2 )
+2
+execute stmt;
+( SELECT MAX(f1) FROM t2 )
+2
+DEALLOCATE PREPARE stmt;
+PREPARE stmt FROM "SELECT * FROM v";
+execute stmt;
+( SELECT MAX(f1) FROM t2 )
+2
+execute stmt;
+( SELECT MAX(f1) FROM t2 )
+2
+DEALLOCATE PREPARE stmt;
+PREPARE stmt FROM "WITH cte AS ( SELECT ( SELECT MAX(f1) FROM t2 ) FROM t1 ) SELECT * FROM cte";
+execute stmt;
+( SELECT MAX(f1) FROM t2 )
+2
+execute stmt;
+( SELECT MAX(f1) FROM t2 )
+2
+DEALLOCATE PREPARE stmt;
+DROP VIEW v;
+DROP TABLE t1,t2;
+#
+# MDEV-28573: view defined as select with implicit grouping and
+#             a set function used in a subquery
+#
+CREATE TABLE t1 (a INTEGER, b INTEGER);
+CREATE TABLE t2 (c INTEGER);
+INSERT INTO t1 VALUES (1,11), (2,22), (2,22);
+INSERT INTO t2 VALUES (1), (2);
+CREATE VIEW v1 AS SELECT (SELECT COUNT(b) FROM t2) FROM t1;
+CREATE VIEW v2 AS SELECT (SELECT COUNT(b) FROM t2 WHERE c > 1) FROM t1;
+SELECT (SELECT COUNT(b) FROM t2) FROM t1;
+ERROR 21000: Subquery returns more than 1 row
+SELECT * FROM v1;
+ERROR 21000: Subquery returns more than 1 row
+WITH cte AS ( SELECT (SELECT COUNT(b) FROM t2) FROM t1 ) SELECT * FROM cte;
+ERROR 21000: Subquery returns more than 1 row
+SELECT (SELECT COUNT(b) FROM t2 WHERE c > 1) FROM t1;
+(SELECT COUNT(b) FROM t2 WHERE c > 1)
+3
+SELECT * FROM v2;
+(SELECT COUNT(b) FROM t2 WHERE c > 1)
+3
+WITH cte AS ( SELECT (SELECT COUNT(b) FROM t2 WHERE c > 1) FROM t1 ) SELECT * FROM cte;
+(SELECT COUNT(b) FROM t2 WHERE c > 1)
+3
+EXPLAIN SELECT (SELECT COUNT(b) FROM t2) FROM t1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	
+2	DEPENDENT SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	2	
+EXPLAIN SELECT * FROM v1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	3	
+2	SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	3	
+3	DEPENDENT SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	2	
+EXPLAIN WITH cte AS ( SELECT (SELECT COUNT(b) FROM t2) FROM t1 ) SELECT * FROM cte;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	3	
+2	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	3	
+3	DEPENDENT SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	2	
+PREPARE stmt FROM "SELECT (SELECT COUNT(b) FROM t2) FROM t1";
+execute stmt;
+ERROR 21000: Subquery returns more than 1 row
+execute stmt;
+ERROR 21000: Subquery returns more than 1 row
+DEALLOCATE PREPARE stmt;
+PREPARE stmt FROM "SELECT * FROM v1";
+execute stmt;
+ERROR 21000: Subquery returns more than 1 row
+execute stmt;
+ERROR 21000: Subquery returns more than 1 row
+DEALLOCATE PREPARE stmt;
+PREPARE stmt FROM "WITH cte AS ( SELECT (SELECT COUNT(b) FROM t2) FROM t1 ) SELECT * FROM cte";
+execute stmt;
+ERROR 21000: Subquery returns more than 1 row
+execute stmt;
+ERROR 21000: Subquery returns more than 1 row
+DEALLOCATE PREPARE stmt;
+PREPARE stmt FROM "SELECT (SELECT COUNT(b) FROM t2 WHERE c > 1) FROM t1";
+execute stmt;
+(SELECT COUNT(b) FROM t2 WHERE c > 1)
+3
+execute stmt;
+(SELECT COUNT(b) FROM t2 WHERE c > 1)
+3
+DEALLOCATE PREPARE stmt;
+PREPARE stmt FROM "SELECT * FROM v2";
+execute stmt;
+(SELECT COUNT(b) FROM t2 WHERE c > 1)
+3
+execute stmt;
+(SELECT COUNT(b) FROM t2 WHERE c > 1)
+3
+DEALLOCATE PREPARE stmt;
+PREPARE stmt FROM "WITH cte AS ( SELECT (SELECT COUNT(b) FROM t2 WHERE c > 1) FROM t1 ) SELECT * FROM cte";
+execute stmt;
+(SELECT COUNT(b) FROM t2 WHERE c > 1)
+3
+execute stmt;
+(SELECT COUNT(b) FROM t2 WHERE c > 1)
+3
+DEALLOCATE PREPARE stmt;
+DROP VIEW v1,v2;
+DROP TABLE t1,t2;
+#
+# MDEV-28570: VIEW with WHERE containing subquery
+#             with set function aggregated in query
+#
+CREATE TABLE t1 (a int, b int);
+CREATE TABLE t2 (c int, d int);
+INSERT INTO t1 VALUES
+(1,10), (2,10), (1,20), (2,20), (3,20), (2,30), (4,40);
+INSERT INTO t2 VALUES
+(2,10), (2,20), (4,10), (5,10), (3,20), (2,40);
+CREATE VIEW v AS SELECT a FROM t1 GROUP BY a
+HAVING a IN (SELECT c FROM t2 WHERE MAX(b)>20);
+SELECT a FROM t1 GROUP BY a
+HAVING a IN (SELECT c FROM t2 WHERE MAX(b)>20);
+a
+2
+4
+SELECT * FROM v;
+a
+2
+4
+WITH cte AS ( SELECT a FROM t1 GROUP BY a
+HAVING a IN (SELECT c FROM t2 WHERE MAX(b)>20) ) SELECT * FROM cte;
+a
+2
+4
+EXPLAIN SELECT a FROM t1 GROUP BY a
+HAVING a IN (SELECT c FROM t2 WHERE MAX(b)>20);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	7	Using temporary; Using filesort
+2	DEPENDENT SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	6	
+EXPLAIN SELECT * FROM v;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	7	
+2	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	7	Using temporary; Using filesort
+3	DEPENDENT SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	6	
+EXPLAIN WITH cte AS ( SELECT a FROM t1 GROUP BY a
+HAVING a IN (SELECT c FROM t2 WHERE MAX(b)>20) ) SELECT * FROM cte;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	7	
+2	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	7	Using temporary; Using filesort
+3	DEPENDENT SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	6	
+PREPARE stmt FROM "SELECT a FROM t1 GROUP BY a
+HAVING a IN (SELECT c FROM t2 WHERE MAX(b)>20)";
+execute stmt;
+a
+2
+4
+execute stmt;
+a
+2
+4
+DEALLOCATE PREPARE stmt;
+PREPARE stmt FROM "SELECT * FROM v";
+execute stmt;
+a
+2
+4
+execute stmt;
+a
+2
+4
+DEALLOCATE PREPARE stmt;
+PREPARE stmt FROM "WITH cte AS ( SELECT a FROM t1 GROUP BY a
+HAVING a IN (SELECT c FROM t2 WHERE MAX(b)>20) ) SELECT * FROM cte";
+execute stmt;
+a
+2
+4
+execute stmt;
+a
+2
+4
+DEALLOCATE PREPARE stmt;
+DROP VIEW v;
+DROP TABLE t1,t2;
+#
+# MDEV-28571: VIEW with select list containing subquery
+#             with set function aggregated in query
+#
+CREATE TABLE t1 (a int, b int);
+CREATE TABLE t2 (m int, n int);
+INSERT INTO t1 VALUES (2,2), (2,2), (3,3), (3,3), (3,3), (4,4);
+INSERT INTO t2 VALUES (1,11), (2,22), (3,32), (4,44), (4,44);
+CREATE VIEW v AS SELECT (SELECT GROUP_CONCAT(COUNT(a)) FROM t2 WHERE m = a) AS c
+FROM t1
+GROUP BY a;
+SELECT (SELECT GROUP_CONCAT(COUNT(a)) FROM t2 WHERE m = a) AS c
+FROM t1
+GROUP BY a;
+c
+2
+3
+1,1
+SELECT * FROM v;
+c
+2
+3
+1,1
+WITH cte AS ( SELECT (SELECT GROUP_CONCAT(COUNT(a)) FROM t2 WHERE m = a) AS c
+FROM t1
+GROUP BY a ) SELECT * FROM cte;
+c
+2
+3
+1,1
+EXPLAIN SELECT (SELECT GROUP_CONCAT(COUNT(a)) FROM t2 WHERE m = a) AS c
+FROM t1
+GROUP BY a;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	6	Using temporary; Using filesort
+2	DEPENDENT SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	5	Using where
+EXPLAIN SELECT * FROM v;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	6	
+2	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	6	Using temporary; Using filesort
+3	DEPENDENT SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	5	Using where
+EXPLAIN WITH cte AS ( SELECT (SELECT GROUP_CONCAT(COUNT(a)) FROM t2 WHERE m = a) AS c
+FROM t1
+GROUP BY a ) SELECT * FROM cte;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	6	
+2	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	6	Using temporary; Using filesort
+3	DEPENDENT SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	5	Using where
+PREPARE stmt FROM "SELECT (SELECT GROUP_CONCAT(COUNT(a)) FROM t2 WHERE m = a) AS c
+FROM t1
+GROUP BY a";
+execute stmt;
+c
+2
+3
+1,1
+execute stmt;
+c
+2
+3
+1,1
+DEALLOCATE PREPARE stmt;
+PREPARE stmt FROM "SELECT * FROM v";
+execute stmt;
+c
+2
+3
+1,1
+execute stmt;
+c
+2
+3
+1,1
+DEALLOCATE PREPARE stmt;
+PREPARE stmt FROM "WITH cte AS ( SELECT (SELECT GROUP_CONCAT(COUNT(a)) FROM t2 WHERE m = a) AS c
+FROM t1
+GROUP BY a ) SELECT * FROM cte";
+execute stmt;
+c
+2
+3
+1,1
+execute stmt;
+c
+2
+3
+1,1
+DEALLOCATE PREPARE stmt;
+DROP VIEW v;
+DROP TABLE t1,t2;
+#
+# MDEV-30668: VIEW with WHERE containing nested subquery
+#             with set function aggregated in outer subquery
+#
+create table t1 (a int);
+insert into t1 values (3), (7), (1);
+create table t2 (b int);
+insert into t2 values (2), (1), (4), (7);
+create table t3 (a int, b int);
+insert into t3 values (2,10), (7,30), (2,30), (1,10), (7,40);
+create view v as select * from t1
+where t1.a in (select t3.a from t3 group by t3.a
+having t3.a > any (select t2.b from t2
+where t2.b*10 < sum(t3.b)));
+select * from t1
+where t1.a in (select t3.a from t3 group by t3.a
+having t3.a > any (select t2.b from t2
+where t2.b*10 < sum(t3.b)));
+a
+7
+select * from v;
+a
+7
+with cte as ( select * from t1
+where t1.a in (select t3.a from t3 group by t3.a
+having t3.a > any (select t2.b from t2
+where t2.b*10 < sum(t3.b))) ) select * from cte;
+a
+7
+explain select * from t1
+where t1.a in (select t3.a from t3 group by t3.a
+having t3.a > any (select t2.b from t2
+where t2.b*10 < sum(t3.b)));
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	Using where
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	test.t1.a	1	
+2	MATERIALIZED	t3	ALL	NULL	NULL	NULL	NULL	5	Using temporary
+3	DEPENDENT SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	4	Using where
+explain select * from v;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	Using where
+1	PRIMARY	<subquery3>	eq_ref	distinct_key	distinct_key	4	test.t1.a	1	
+3	MATERIALIZED	t3	ALL	NULL	NULL	NULL	NULL	5	Using temporary
+4	DEPENDENT SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	4	Using where
+explain with cte as ( select * from t1
+where t1.a in (select t3.a from t3 group by t3.a
+having t3.a > any (select t2.b from t2
+where t2.b*10 < sum(t3.b))) ) select * from cte;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	Using where
+1	PRIMARY	<subquery3>	eq_ref	distinct_key	distinct_key	4	test.t1.a	1	
+3	MATERIALIZED	t3	ALL	NULL	NULL	NULL	NULL	5	Using temporary
+4	DEPENDENT SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	4	Using where
+prepare stmt from "select * from t1
+where t1.a in (select t3.a from t3 group by t3.a
+having t3.a > any (select t2.b from t2
+where t2.b*10 < sum(t3.b)))";
+execute stmt;
+a
+7
+execute stmt;
+a
+7
+deallocate prepare stmt;
+prepare stmt from "select * from v";
+execute stmt;
+a
+7
+execute stmt;
+a
+7
+deallocate prepare stmt;
+prepare stmt from "with cte as ( select * from t1
+where t1.a in (select t3.a from t3 group by t3.a
+having t3.a > any (select t2.b from t2
+where t2.b*10 < sum(t3.b))) ) select * from cte";
+execute stmt;
+a
+7
+execute stmt;
+a
+7
+deallocate prepare stmt;
+drop view v;
+drop table t1,t2,t3;
+# End of 10.4 tests