Merge branch '10.0' into 10.1

12 files changed, 467 insertions, 52 deletions

diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result
index 49a033626ee..da418accd6c 100644
--- a/mysql-test/r/subselect.result
+++ b/mysql-test/r/subselect.result
@@ -4701,7 +4701,7 @@ pk
 SET SESSION sql_mode=@old_sql_mode;
 drop table t2, t1;
 drop view v1;
-End of 5.0 tests.
+# End of 5.0 tests.
 create table t_out (subcase char(3),
 a1 char(2), b1 char(2), c1 char(2));
 create table t_in  (a2 char(2), b2 char(2), c2 char(2));
@@ -5576,7 +5576,7 @@ insert into t2 values ('x'), ('y');
 select * from t2 where a=(select a from t1) and a='x';
 ERROR 21000: Subquery returns more than 1 row
 drop table t1,t2;
-End of 5.1 tests
+# End of 5.1 tests
 #
 # Bug #11765713 58705:
 # OPTIMIZER LET ENGINE DEPEND ON UNINITIALIZED VALUES
@@ -6188,7 +6188,7 @@ WHERE (c_sq1_alias1.col_int_nokey != @var2
 OR c_sq1_alias1.pk != @var3)) ) AS alias3;
 pk	col_int_nokey	col_int_key	col_time_key	col_varchar_key	col_varchar_nokey
 DROP TABLE t1,t2;
-End of 5.2 tests
+# End of 5.2 tests
 #
 # BUG#779885: Crash in eliminate_item_equal with materialization=on in
 #
@@ -7145,9 +7145,10 @@ INSERT INTO t1 VALUES (1),(2);
 CREATE TABLE t2 (f2 INT, KEY(f2));
 INSERT INTO t2 VALUES (3);
 CREATE ALGORITHM=MERGE VIEW v2 AS SELECT * FROM t2;
+SELECT ( SELECT MIN(t2.f2) FROM t1 ) AS sq FROM t2 GROUP BY sq;
+ERROR 42000: Can't group on 'sq'
 SELECT ( SELECT MIN(v2.f2) FROM t1 ) AS sq FROM v2 GROUP BY sq;
-sq
-3
+ERROR 42000: Can't group on 'sq'
 SELECT * FROM v2 where ( SELECT MIN(v2.f2) FROM t1 ) > 0;
 f2
 3
@@ -7155,9 +7156,10 @@ SELECT count(*) FROM v2 group by ( SELECT MIN(v2.f2) FROM t1 );
 count(*)
 1
 delete from t1;
+SELECT ( SELECT MIN(t2.f2) FROM t1 ) AS sq FROM t2 GROUP BY sq;
+ERROR 42000: Can't group on 'sq'
 SELECT ( SELECT MIN(v2.f2) FROM t1 ) AS sq FROM v2 GROUP BY sq;
-sq
-NULL
+ERROR 42000: Can't group on 'sq'
 drop view v2;
 drop table t1,t2;
 #
@@ -7172,6 +7174,29 @@ f1	f2
 foo	bar
 DROP TABLE t1;
 #
+# MDEV-10146: Wrong result (or questionable result and behavior)
+# with aggregate function in uncorrelated SELECT subquery
+#
+CREATE TABLE t1 (f1 INT);
+CREATE VIEW v1 AS SELECT * FROM t1;
+INSERT INTO t1 VALUES (1),(2);
+CREATE TABLE t2 (f2 int);
+INSERT INTO t2 VALUES (3);
+SELECT ( SELECT MAX(f1) FROM t2 ) FROM t1;
+( SELECT MAX(f1) FROM t2 )
+2
+SELECT ( SELECT MAX(f1) FROM t2 ) FROM v1;
+( SELECT MAX(f1) FROM t2 )
+2
+INSERT INTO t2 VALUES (4);
+SELECT ( SELECT MAX(f1) FROM t2 ) FROM v1;
+ERROR 21000: Subquery returns more than 1 row
+SELECT ( SELECT MAX(f1) FROM t2 ) FROM t1;
+ERROR 21000: Subquery returns more than 1 row
+drop view v1;
+drop table t1,t2;
+# End of 10.0 tests
+#
 # MDEV-9487: Server crashes in Time_and_counter_tracker::incr_loops
 # with UNION in ALL subquery
 #
@@ -7198,4 +7223,4 @@ SELECT * FROM t1, t2 WHERE f3 = f2 AND f1 > ANY ( SELECT 'foo');
 f1	f2	f3
 DROP TABLE t1, t2;
 SET NAMES default;
-End of 10.1 tests
+# End of 10.1 tests
diff --git a/mysql-test/r/subselect_no_exists_to_in.result b/mysql-test/r/subselect_no_exists_to_in.result
index d968a81c638..5382e291dfc 100644
--- a/mysql-test/r/subselect_no_exists_to_in.result
+++ b/mysql-test/r/subselect_no_exists_to_in.result
@@ -4703,7 +4703,7 @@ pk
 SET SESSION sql_mode=@old_sql_mode;
 drop table t2, t1;
 drop view v1;
-End of 5.0 tests.
+# End of 5.0 tests.
 create table t_out (subcase char(3),
 a1 char(2), b1 char(2), c1 char(2));
 create table t_in  (a2 char(2), b2 char(2), c2 char(2));
@@ -5578,7 +5578,7 @@ insert into t2 values ('x'), ('y');
 select * from t2 where a=(select a from t1) and a='x';
 ERROR 21000: Subquery returns more than 1 row
 drop table t1,t2;
-End of 5.1 tests
+# End of 5.1 tests
 #
 # Bug #11765713 58705:
 # OPTIMIZER LET ENGINE DEPEND ON UNINITIALIZED VALUES
@@ -6188,7 +6188,7 @@ WHERE (c_sq1_alias1.col_int_nokey != @var2
 OR c_sq1_alias1.pk != @var3)) ) AS alias3;
 pk	col_int_nokey	col_int_key	col_time_key	col_varchar_key	col_varchar_nokey
 DROP TABLE t1,t2;
-End of 5.2 tests
+# End of 5.2 tests
 #
 # BUG#779885: Crash in eliminate_item_equal with materialization=on in
 #
@@ -7145,9 +7145,10 @@ INSERT INTO t1 VALUES (1),(2);
 CREATE TABLE t2 (f2 INT, KEY(f2));
 INSERT INTO t2 VALUES (3);
 CREATE ALGORITHM=MERGE VIEW v2 AS SELECT * FROM t2;
+SELECT ( SELECT MIN(t2.f2) FROM t1 ) AS sq FROM t2 GROUP BY sq;
+ERROR 42000: Can't group on 'sq'
 SELECT ( SELECT MIN(v2.f2) FROM t1 ) AS sq FROM v2 GROUP BY sq;
-sq
-3
+ERROR 42000: Can't group on 'sq'
 SELECT * FROM v2 where ( SELECT MIN(v2.f2) FROM t1 ) > 0;
 f2
 3
@@ -7155,9 +7156,10 @@ SELECT count(*) FROM v2 group by ( SELECT MIN(v2.f2) FROM t1 );
 count(*)
 1
 delete from t1;
+SELECT ( SELECT MIN(t2.f2) FROM t1 ) AS sq FROM t2 GROUP BY sq;
+ERROR 42000: Can't group on 'sq'
 SELECT ( SELECT MIN(v2.f2) FROM t1 ) AS sq FROM v2 GROUP BY sq;
-sq
-NULL
+ERROR 42000: Can't group on 'sq'
 drop view v2;
 drop table t1,t2;
 #
@@ -7172,6 +7174,29 @@ f1	f2
 foo	bar
 DROP TABLE t1;
 #
+# MDEV-10146: Wrong result (or questionable result and behavior)
+# with aggregate function in uncorrelated SELECT subquery
+#
+CREATE TABLE t1 (f1 INT);
+CREATE VIEW v1 AS SELECT * FROM t1;
+INSERT INTO t1 VALUES (1),(2);
+CREATE TABLE t2 (f2 int);
+INSERT INTO t2 VALUES (3);
+SELECT ( SELECT MAX(f1) FROM t2 ) FROM t1;
+( SELECT MAX(f1) FROM t2 )
+2
+SELECT ( SELECT MAX(f1) FROM t2 ) FROM v1;
+( SELECT MAX(f1) FROM t2 )
+2
+INSERT INTO t2 VALUES (4);
+SELECT ( SELECT MAX(f1) FROM t2 ) FROM v1;
+ERROR 21000: Subquery returns more than 1 row
+SELECT ( SELECT MAX(f1) FROM t2 ) FROM t1;
+ERROR 21000: Subquery returns more than 1 row
+drop view v1;
+drop table t1,t2;
+# End of 10.0 tests
+#
 # MDEV-9487: Server crashes in Time_and_counter_tracker::incr_loops
 # with UNION in ALL subquery
 #
@@ -7198,7 +7223,7 @@ SELECT * FROM t1, t2 WHERE f3 = f2 AND f1 > ANY ( SELECT 'foo');
 f1	f2	f3
 DROP TABLE t1, t2;
 SET NAMES default;
-End of 10.1 tests
+# End of 10.1 tests
 set optimizer_switch=default;
 select @@optimizer_switch like '%exists_to_in=off%';
 @@optimizer_switch like '%exists_to_in=off%'
diff --git a/mysql-test/r/subselect_no_mat.result b/mysql-test/r/subselect_no_mat.result
index 19a936e86c7..255f8e30ccf 100644
--- a/mysql-test/r/subselect_no_mat.result
+++ b/mysql-test/r/subselect_no_mat.result
@@ -4701,7 +4701,7 @@ pk
 SET SESSION sql_mode=@old_sql_mode;
 drop table t2, t1;
 drop view v1;
-End of 5.0 tests.
+# End of 5.0 tests.
 create table t_out (subcase char(3),
 a1 char(2), b1 char(2), c1 char(2));
 create table t_in  (a2 char(2), b2 char(2), c2 char(2));
@@ -5576,7 +5576,7 @@ insert into t2 values ('x'), ('y');
 select * from t2 where a=(select a from t1) and a='x';
 ERROR 21000: Subquery returns more than 1 row
 drop table t1,t2;
-End of 5.1 tests
+# End of 5.1 tests
 #
 # Bug #11765713 58705:
 # OPTIMIZER LET ENGINE DEPEND ON UNINITIALIZED VALUES
@@ -6183,7 +6183,7 @@ WHERE (c_sq1_alias1.col_int_nokey != @var2
 OR c_sq1_alias1.pk != @var3)) ) AS alias3;
 pk	col_int_nokey	col_int_key	col_time_key	col_varchar_key	col_varchar_nokey
 DROP TABLE t1,t2;
-End of 5.2 tests
+# End of 5.2 tests
 #
 # BUG#779885: Crash in eliminate_item_equal with materialization=on in
 #
@@ -7138,9 +7138,10 @@ INSERT INTO t1 VALUES (1),(2);
 CREATE TABLE t2 (f2 INT, KEY(f2));
 INSERT INTO t2 VALUES (3);
 CREATE ALGORITHM=MERGE VIEW v2 AS SELECT * FROM t2;
+SELECT ( SELECT MIN(t2.f2) FROM t1 ) AS sq FROM t2 GROUP BY sq;
+ERROR 42000: Can't group on 'sq'
 SELECT ( SELECT MIN(v2.f2) FROM t1 ) AS sq FROM v2 GROUP BY sq;
-sq
-3
+ERROR 42000: Can't group on 'sq'
 SELECT * FROM v2 where ( SELECT MIN(v2.f2) FROM t1 ) > 0;
 f2
 3
@@ -7148,9 +7149,10 @@ SELECT count(*) FROM v2 group by ( SELECT MIN(v2.f2) FROM t1 );
 count(*)
 1
 delete from t1;
+SELECT ( SELECT MIN(t2.f2) FROM t1 ) AS sq FROM t2 GROUP BY sq;
+ERROR 42000: Can't group on 'sq'
 SELECT ( SELECT MIN(v2.f2) FROM t1 ) AS sq FROM v2 GROUP BY sq;
-sq
-NULL
+ERROR 42000: Can't group on 'sq'
 drop view v2;
 drop table t1,t2;
 #
@@ -7165,6 +7167,29 @@ f1	f2
 foo	bar
 DROP TABLE t1;
 #
+# MDEV-10146: Wrong result (or questionable result and behavior)
+# with aggregate function in uncorrelated SELECT subquery
+#
+CREATE TABLE t1 (f1 INT);
+CREATE VIEW v1 AS SELECT * FROM t1;
+INSERT INTO t1 VALUES (1),(2);
+CREATE TABLE t2 (f2 int);
+INSERT INTO t2 VALUES (3);
+SELECT ( SELECT MAX(f1) FROM t2 ) FROM t1;
+( SELECT MAX(f1) FROM t2 )
+2
+SELECT ( SELECT MAX(f1) FROM t2 ) FROM v1;
+( SELECT MAX(f1) FROM t2 )
+2
+INSERT INTO t2 VALUES (4);
+SELECT ( SELECT MAX(f1) FROM t2 ) FROM v1;
+ERROR 21000: Subquery returns more than 1 row
+SELECT ( SELECT MAX(f1) FROM t2 ) FROM t1;
+ERROR 21000: Subquery returns more than 1 row
+drop view v1;
+drop table t1,t2;
+# End of 10.0 tests
+#
 # MDEV-9487: Server crashes in Time_and_counter_tracker::incr_loops
 # with UNION in ALL subquery
 #
@@ -7191,7 +7216,7 @@ SELECT * FROM t1, t2 WHERE f3 = f2 AND f1 > ANY ( SELECT 'foo');
 f1	f2	f3
 DROP TABLE t1, t2;
 SET NAMES default;
-End of 10.1 tests
+# End of 10.1 tests
 set optimizer_switch=default;
 select @@optimizer_switch like '%materialization=on%';
 @@optimizer_switch like '%materialization=on%'
diff --git a/mysql-test/r/subselect_no_opts.result b/mysql-test/r/subselect_no_opts.result
index 1293e1de412..938c8edc2e9 100644
--- a/mysql-test/r/subselect_no_opts.result
+++ b/mysql-test/r/subselect_no_opts.result
@@ -4697,7 +4697,7 @@ pk
 SET SESSION sql_mode=@old_sql_mode;
 drop table t2, t1;
 drop view v1;
-End of 5.0 tests.
+# End of 5.0 tests.
 create table t_out (subcase char(3),
 a1 char(2), b1 char(2), c1 char(2));
 create table t_in  (a2 char(2), b2 char(2), c2 char(2));
@@ -5572,7 +5572,7 @@ insert into t2 values ('x'), ('y');
 select * from t2 where a=(select a from t1) and a='x';
 ERROR 21000: Subquery returns more than 1 row
 drop table t1,t2;
-End of 5.1 tests
+# End of 5.1 tests
 #
 # Bug #11765713 58705:
 # OPTIMIZER LET ENGINE DEPEND ON UNINITIALIZED VALUES
@@ -6179,7 +6179,7 @@ WHERE (c_sq1_alias1.col_int_nokey != @var2
 OR c_sq1_alias1.pk != @var3)) ) AS alias3;
 pk	col_int_nokey	col_int_key	col_time_key	col_varchar_key	col_varchar_nokey
 DROP TABLE t1,t2;
-End of 5.2 tests
+# End of 5.2 tests
 #
 # BUG#779885: Crash in eliminate_item_equal with materialization=on in
 #
@@ -7136,9 +7136,10 @@ INSERT INTO t1 VALUES (1),(2);
 CREATE TABLE t2 (f2 INT, KEY(f2));
 INSERT INTO t2 VALUES (3);
 CREATE ALGORITHM=MERGE VIEW v2 AS SELECT * FROM t2;
+SELECT ( SELECT MIN(t2.f2) FROM t1 ) AS sq FROM t2 GROUP BY sq;
+ERROR 42000: Can't group on 'sq'
 SELECT ( SELECT MIN(v2.f2) FROM t1 ) AS sq FROM v2 GROUP BY sq;
-sq
-3
+ERROR 42000: Can't group on 'sq'
 SELECT * FROM v2 where ( SELECT MIN(v2.f2) FROM t1 ) > 0;
 f2
 3
@@ -7146,9 +7147,10 @@ SELECT count(*) FROM v2 group by ( SELECT MIN(v2.f2) FROM t1 );
 count(*)
 1
 delete from t1;
+SELECT ( SELECT MIN(t2.f2) FROM t1 ) AS sq FROM t2 GROUP BY sq;
+ERROR 42000: Can't group on 'sq'
 SELECT ( SELECT MIN(v2.f2) FROM t1 ) AS sq FROM v2 GROUP BY sq;
-sq
-NULL
+ERROR 42000: Can't group on 'sq'
 drop view v2;
 drop table t1,t2;
 #
@@ -7163,6 +7165,29 @@ f1	f2
 foo	bar
 DROP TABLE t1;
 #
+# MDEV-10146: Wrong result (or questionable result and behavior)
+# with aggregate function in uncorrelated SELECT subquery
+#
+CREATE TABLE t1 (f1 INT);
+CREATE VIEW v1 AS SELECT * FROM t1;
+INSERT INTO t1 VALUES (1),(2);
+CREATE TABLE t2 (f2 int);
+INSERT INTO t2 VALUES (3);
+SELECT ( SELECT MAX(f1) FROM t2 ) FROM t1;
+( SELECT MAX(f1) FROM t2 )
+2
+SELECT ( SELECT MAX(f1) FROM t2 ) FROM v1;
+( SELECT MAX(f1) FROM t2 )
+2
+INSERT INTO t2 VALUES (4);
+SELECT ( SELECT MAX(f1) FROM t2 ) FROM v1;
+ERROR 21000: Subquery returns more than 1 row
+SELECT ( SELECT MAX(f1) FROM t2 ) FROM t1;
+ERROR 21000: Subquery returns more than 1 row
+drop view v1;
+drop table t1,t2;
+# End of 10.0 tests
+#
 # MDEV-9487: Server crashes in Time_and_counter_tracker::incr_loops
 # with UNION in ALL subquery
 #
@@ -7189,5 +7214,5 @@ SELECT * FROM t1, t2 WHERE f3 = f2 AND f1 > ANY ( SELECT 'foo');
 f1	f2	f3
 DROP TABLE t1, t2;
 SET NAMES default;
-End of 10.1 tests
+# End of 10.1 tests
 set @optimizer_switch_for_subselect_test=null;
diff --git a/mysql-test/r/subselect_no_scache.result b/mysql-test/r/subselect_no_scache.result
index cc3d20ef163..9a37719c38a 100644
--- a/mysql-test/r/subselect_no_scache.result
+++ b/mysql-test/r/subselect_no_scache.result
@@ -4707,7 +4707,7 @@ pk
 SET SESSION sql_mode=@old_sql_mode;
 drop table t2, t1;
 drop view v1;
-End of 5.0 tests.
+# End of 5.0 tests.
 create table t_out (subcase char(3),
 a1 char(2), b1 char(2), c1 char(2));
 create table t_in  (a2 char(2), b2 char(2), c2 char(2));
@@ -5582,7 +5582,7 @@ insert into t2 values ('x'), ('y');
 select * from t2 where a=(select a from t1) and a='x';
 ERROR 21000: Subquery returns more than 1 row
 drop table t1,t2;
-End of 5.1 tests
+# End of 5.1 tests
 #
 # Bug #11765713 58705:
 # OPTIMIZER LET ENGINE DEPEND ON UNINITIALIZED VALUES
@@ -6194,7 +6194,7 @@ WHERE (c_sq1_alias1.col_int_nokey != @var2
 OR c_sq1_alias1.pk != @var3)) ) AS alias3;
 pk	col_int_nokey	col_int_key	col_time_key	col_varchar_key	col_varchar_nokey
 DROP TABLE t1,t2;
-End of 5.2 tests
+# End of 5.2 tests
 #
 # BUG#779885: Crash in eliminate_item_equal with materialization=on in
 #
@@ -7151,9 +7151,10 @@ INSERT INTO t1 VALUES (1),(2);
 CREATE TABLE t2 (f2 INT, KEY(f2));
 INSERT INTO t2 VALUES (3);
 CREATE ALGORITHM=MERGE VIEW v2 AS SELECT * FROM t2;
+SELECT ( SELECT MIN(t2.f2) FROM t1 ) AS sq FROM t2 GROUP BY sq;
+ERROR 42000: Can't group on 'sq'
 SELECT ( SELECT MIN(v2.f2) FROM t1 ) AS sq FROM v2 GROUP BY sq;
-sq
-3
+ERROR 42000: Can't group on 'sq'
 SELECT * FROM v2 where ( SELECT MIN(v2.f2) FROM t1 ) > 0;
 f2
 3
@@ -7161,9 +7162,10 @@ SELECT count(*) FROM v2 group by ( SELECT MIN(v2.f2) FROM t1 );
 count(*)
 1
 delete from t1;
+SELECT ( SELECT MIN(t2.f2) FROM t1 ) AS sq FROM t2 GROUP BY sq;
+ERROR 42000: Can't group on 'sq'
 SELECT ( SELECT MIN(v2.f2) FROM t1 ) AS sq FROM v2 GROUP BY sq;
-sq
-NULL
+ERROR 42000: Can't group on 'sq'
 drop view v2;
 drop table t1,t2;
 #
@@ -7178,6 +7180,29 @@ f1	f2
 foo	bar
 DROP TABLE t1;
 #
+# MDEV-10146: Wrong result (or questionable result and behavior)
+# with aggregate function in uncorrelated SELECT subquery
+#
+CREATE TABLE t1 (f1 INT);
+CREATE VIEW v1 AS SELECT * FROM t1;
+INSERT INTO t1 VALUES (1),(2);
+CREATE TABLE t2 (f2 int);
+INSERT INTO t2 VALUES (3);
+SELECT ( SELECT MAX(f1) FROM t2 ) FROM t1;
+( SELECT MAX(f1) FROM t2 )
+2
+SELECT ( SELECT MAX(f1) FROM t2 ) FROM v1;
+( SELECT MAX(f1) FROM t2 )
+2
+INSERT INTO t2 VALUES (4);
+SELECT ( SELECT MAX(f1) FROM t2 ) FROM v1;
+ERROR 21000: Subquery returns more than 1 row
+SELECT ( SELECT MAX(f1) FROM t2 ) FROM t1;
+ERROR 21000: Subquery returns more than 1 row
+drop view v1;
+drop table t1,t2;
+# End of 10.0 tests
+#
 # MDEV-9487: Server crashes in Time_and_counter_tracker::incr_loops
 # with UNION in ALL subquery
 #
@@ -7204,7 +7229,7 @@ SELECT * FROM t1, t2 WHERE f3 = f2 AND f1 > ANY ( SELECT 'foo');
 f1	f2	f3
 DROP TABLE t1, t2;
 SET NAMES default;
-End of 10.1 tests
+# End of 10.1 tests
 set optimizer_switch=default;
 select @@optimizer_switch like '%subquery_cache=on%';
 @@optimizer_switch like '%subquery_cache=on%'
diff --git a/mysql-test/r/subselect_no_semijoin.result b/mysql-test/r/subselect_no_semijoin.result
index 2bbde7b397f..c66fef2eb9b 100644
--- a/mysql-test/r/subselect_no_semijoin.result
+++ b/mysql-test/r/subselect_no_semijoin.result
@@ -4697,7 +4697,7 @@ pk
 SET SESSION sql_mode=@old_sql_mode;
 drop table t2, t1;
 drop view v1;
-End of 5.0 tests.
+# End of 5.0 tests.
 create table t_out (subcase char(3),
 a1 char(2), b1 char(2), c1 char(2));
 create table t_in  (a2 char(2), b2 char(2), c2 char(2));
@@ -5572,7 +5572,7 @@ insert into t2 values ('x'), ('y');
 select * from t2 where a=(select a from t1) and a='x';
 ERROR 21000: Subquery returns more than 1 row
 drop table t1,t2;
-End of 5.1 tests
+# End of 5.1 tests
 #
 # Bug #11765713 58705:
 # OPTIMIZER LET ENGINE DEPEND ON UNINITIALIZED VALUES
@@ -6179,7 +6179,7 @@ WHERE (c_sq1_alias1.col_int_nokey != @var2
 OR c_sq1_alias1.pk != @var3)) ) AS alias3;
 pk	col_int_nokey	col_int_key	col_time_key	col_varchar_key	col_varchar_nokey
 DROP TABLE t1,t2;
-End of 5.2 tests
+# End of 5.2 tests
 #
 # BUG#779885: Crash in eliminate_item_equal with materialization=on in
 #
@@ -7136,9 +7136,10 @@ INSERT INTO t1 VALUES (1),(2);
 CREATE TABLE t2 (f2 INT, KEY(f2));
 INSERT INTO t2 VALUES (3);
 CREATE ALGORITHM=MERGE VIEW v2 AS SELECT * FROM t2;
+SELECT ( SELECT MIN(t2.f2) FROM t1 ) AS sq FROM t2 GROUP BY sq;
+ERROR 42000: Can't group on 'sq'
 SELECT ( SELECT MIN(v2.f2) FROM t1 ) AS sq FROM v2 GROUP BY sq;
-sq
-3
+ERROR 42000: Can't group on 'sq'
 SELECT * FROM v2 where ( SELECT MIN(v2.f2) FROM t1 ) > 0;
 f2
 3
@@ -7146,9 +7147,10 @@ SELECT count(*) FROM v2 group by ( SELECT MIN(v2.f2) FROM t1 );
 count(*)
 1
 delete from t1;
+SELECT ( SELECT MIN(t2.f2) FROM t1 ) AS sq FROM t2 GROUP BY sq;
+ERROR 42000: Can't group on 'sq'
 SELECT ( SELECT MIN(v2.f2) FROM t1 ) AS sq FROM v2 GROUP BY sq;
-sq
-NULL
+ERROR 42000: Can't group on 'sq'
 drop view v2;
 drop table t1,t2;
 #
@@ -7163,6 +7165,29 @@ f1	f2
 foo	bar
 DROP TABLE t1;
 #
+# MDEV-10146: Wrong result (or questionable result and behavior)
+# with aggregate function in uncorrelated SELECT subquery
+#
+CREATE TABLE t1 (f1 INT);
+CREATE VIEW v1 AS SELECT * FROM t1;
+INSERT INTO t1 VALUES (1),(2);
+CREATE TABLE t2 (f2 int);
+INSERT INTO t2 VALUES (3);
+SELECT ( SELECT MAX(f1) FROM t2 ) FROM t1;
+( SELECT MAX(f1) FROM t2 )
+2
+SELECT ( SELECT MAX(f1) FROM t2 ) FROM v1;
+( SELECT MAX(f1) FROM t2 )
+2
+INSERT INTO t2 VALUES (4);
+SELECT ( SELECT MAX(f1) FROM t2 ) FROM v1;
+ERROR 21000: Subquery returns more than 1 row
+SELECT ( SELECT MAX(f1) FROM t2 ) FROM t1;
+ERROR 21000: Subquery returns more than 1 row
+drop view v1;
+drop table t1,t2;
+# End of 10.0 tests
+#
 # MDEV-9487: Server crashes in Time_and_counter_tracker::incr_loops
 # with UNION in ALL subquery
 #
@@ -7189,6 +7214,6 @@ SELECT * FROM t1, t2 WHERE f3 = f2 AND f1 > ANY ( SELECT 'foo');
 f1	f2	f3
 DROP TABLE t1, t2;
 SET NAMES default;
-End of 10.1 tests
+# End of 10.1 tests
 set @optimizer_switch_for_subselect_test=null;
 set @join_cache_level_for_subselect_test=NULL;
diff --git a/mysql-test/suite/parts/r/longname.result b/mysql-test/suite/parts/r/longname.result
new file mode 100644
index 00000000000..6424ba28297
--- /dev/null
+++ b/mysql-test/suite/parts/r/longname.result
@@ -0,0 +1,37 @@
+set names utf8;
+create database mysqltest1;
+select database_name, table_name, length(table_name) from mysql.innodb_table_stats where database_name = 'mysqltest1';
+database_name	table_name	length(table_name)
+CREATE TABLE mysqltest1.test_jfg_table_name_with_64_chars_123456789012345678901234567890 (
+id int(10) unsigned NOT NULL,
+id2 int(10) unsigned NOT NULL,
+PRIMARY KEY ( id, id2 )
+) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC
+PARTITION BY RANGE ( id )
+SUBPARTITION BY HASH ( id2 )
+SUBPARTITIONS 2 (
+PARTITION test_jfg_partition_name_with_60_chars_1234567890123456789012 VALUES LESS THAN (1000) ENGINE = InnoDB,
+PARTITION pmax VALUES LESS THAN MAXVALUE ENGINE = InnoDB);
+Warnings:
+Warning	1478	InnoDB: ROW_FORMAT=DYNAMIC requires innodb_file_format > Antelope.
+Warning	1478	InnoDB: assuming ROW_FORMAT=COMPACT.
+Warning	1478	InnoDB: ROW_FORMAT=DYNAMIC requires innodb_file_format > Antelope.
+Warning	1478	InnoDB: assuming ROW_FORMAT=COMPACT.
+Warning	1478	InnoDB: ROW_FORMAT=DYNAMIC requires innodb_file_format > Antelope.
+Warning	1478	InnoDB: assuming ROW_FORMAT=COMPACT.
+Warning	1478	InnoDB: ROW_FORMAT=DYNAMIC requires innodb_file_format > Antelope.
+Warning	1478	InnoDB: assuming ROW_FORMAT=COMPACT.
+select database_name, table_name, length(table_name) from mysql.innodb_table_stats where database_name = 'mysqltest1';
+database_name	table_name	length(table_name)
+CREATE TABLE mysqltest1.éééééééééééééééééééééééééééééééééééééééééééééééééééééééééééééééé (
+id int(10) unsigned NOT NULL,
+id2 int(10) unsigned NOT NULL,
+PRIMARY KEY ( id, id2 )
+) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC
+PARTITION BY RANGE ( id )
+SUBPARTITION BY HASH ( id2 )
+SUBPARTITIONS 2 (
+PARTITION çççççççççççççççççççççççççççççççççççççççççççççççççççççççççççç VALUES LESS THAN (1000) ENGINE = InnoDB,
+PARTITION pmax VALUES LESS THAN MAXVALUE ENGINE = InnoDB);
+ERROR HY000: The path specified for @0n@0n@0n@0n@0n@0n@0n@0n@0n@0n@0n@0n@0n@0n@0n@0n@0n@0n@0n@0n@0n@ is too long.
+drop database mysqltest1;
diff --git a/mysql-test/suite/parts/t/longname.test b/mysql-test/suite/parts/t/longname.test
new file mode 100644
index 00000000000..0f7378ef8e3
--- /dev/null
+++ b/mysql-test/suite/parts/t/longname.test
@@ -0,0 +1,32 @@
+source include/have_innodb.inc;
+source include/have_partition.inc;
+set names utf8;
+
+create database mysqltest1;
+select database_name, table_name, length(table_name) from mysql.innodb_table_stats where database_name = 'mysqltest1';
+CREATE TABLE mysqltest1.test_jfg_table_name_with_64_chars_123456789012345678901234567890 (
+  id int(10) unsigned NOT NULL,
+  id2 int(10) unsigned NOT NULL,
+  PRIMARY KEY ( id, id2 )
+) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC
+PARTITION BY RANGE ( id )
+  SUBPARTITION BY HASH ( id2 )
+  SUBPARTITIONS 2 (
+  PARTITION test_jfg_partition_name_with_60_chars_1234567890123456789012 VALUES LESS THAN (1000) ENGINE = InnoDB,
+  PARTITION pmax VALUES LESS THAN MAXVALUE ENGINE = InnoDB);
+
+select database_name, table_name, length(table_name) from mysql.innodb_table_stats where database_name = 'mysqltest1';
+
+--error ER_PATH_LENGTH
+CREATE TABLE mysqltest1.éééééééééééééééééééééééééééééééééééééééééééééééééééééééééééééééé (
+  id int(10) unsigned NOT NULL,
+  id2 int(10) unsigned NOT NULL,
+  PRIMARY KEY ( id, id2 )
+) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC
+PARTITION BY RANGE ( id )
+  SUBPARTITION BY HASH ( id2 )
+  SUBPARTITIONS 2 (
+  PARTITION çççççççççççççççççççççççççççççççççççççççççççççççççççççççççççç VALUES LESS THAN (1000) ENGINE = InnoDB,
+  PARTITION pmax VALUES LESS THAN MAXVALUE ENGINE = InnoDB);
+
+drop database mysqltest1;
diff --git a/mysql-test/suite/rpl/r/circular_serverid0.result b/mysql-test/suite/rpl/r/circular_serverid0.result
new file mode 100644
index 00000000000..899b4bd343b
--- /dev/null
+++ b/mysql-test/suite/rpl/r/circular_serverid0.result
@@ -0,0 +1,30 @@
+include/rpl_init.inc [topology=1->2->1]
+include/rpl_connect.inc [creating M4]
+include/rpl_connect.inc [creating M2]
+SET @old_debug= @@global.debug;
+STOP SLAVE;
+SET GLOBAL debug_dbug= "+d,dbug.rows_events_to_delay_relay_logging";
+START SLAVE IO_THREAD;
+include/wait_for_slave_io_to_start.inc
+CREATE TABLE t1 (a INT AUTO_INCREMENT PRIMARY KEY, b VARCHAR(30000)) ENGINE=innodb;
+INSERT INTO `t1` VALUES (null, repeat('a', 1024)), (null, repeat('b', 1024));
+SET debug_sync='now WAIT_FOR start_sql_thread';
+START SLAVE SQL_THREAD;
+include/show_binlog_events.inc
+Log_name	Pos	Event_type	Server_id	End_log_pos	Info
+slave-bin.000001	#	Gtid	#	#	GTID #-#-#
+slave-bin.000001	#	Query	#	#	use `test`; CREATE TABLE t1 (a INT AUTO_INCREMENT PRIMARY KEY, b VARCHAR(30000)) ENGINE=innodb
+SET debug_sync='now SIGNAL go_on_relay_logging';
+include/show_binlog_events.inc
+Log_name	Pos	Event_type	Server_id	End_log_pos	Info
+slave-bin.000001	#	Gtid	#	#	GTID #-#-#
+slave-bin.000001	#	Query	#	#	use `test`; CREATE TABLE t1 (a INT AUTO_INCREMENT PRIMARY KEY, b VARCHAR(30000)) ENGINE=innodb
+slave-bin.000001	#	Gtid	#	#	BEGIN GTID #-#-#
+slave-bin.000001	#	Table_map	#	#	table_id: # (test.t1)
+slave-bin.000001	#	Write_rows_v1	#	#	table_id: #
+slave-bin.000001	#	Write_rows_v1	#	#	table_id: # flags: STMT_END_F
+slave-bin.000001	#	Xid	#	#	COMMIT /* XID */
+drop table t1;
+SET GLOBAL debug_dbug= @old_debug;
+SET debug_sync='RESET';
+include/rpl_end.inc
diff --git a/mysql-test/suite/rpl/t/circular_serverid0.cnf b/mysql-test/suite/rpl/t/circular_serverid0.cnf
new file mode 100644
index 00000000000..277aac2869b
--- /dev/null
+++ b/mysql-test/suite/rpl/t/circular_serverid0.cnf
@@ -0,0 +1,30 @@
+!include ../my.cnf
+
+[mysqld.1]
+gtid-domain-id=4
+server-id=4
+#
+log-slave-updates
+slave-parallel-threads=0
+gtid-strict-mode=1
+gtid-ignore-duplicates=1
+
+#
+# Max-size row events to minimum with the idea to create
+# a number of Rows_log_event per Query.
+#
+binlog-row-event-max-size=1024
+
+[mysqld.2]
+gtid-domain-id=2
+server-id=2
+#
+log-slave-updates
+slave-parallel-threads=0
+gtid-strict-mode=1
+gtid-ignore-duplicates=1
+binlog-row-event-max-size=1024
+# The slave will be initialized with a @@global.dbug-var value
+skip-slave-start=1
+
+
diff --git a/mysql-test/suite/rpl/t/circular_serverid0.test b/mysql-test/suite/rpl/t/circular_serverid0.test
new file mode 100644
index 00000000000..20ad58e2c52
--- /dev/null
+++ b/mysql-test/suite/rpl/t/circular_serverid0.test
@@ -0,0 +1,104 @@
+#
+# Testing chain/circular replication scenario of MDEV-9670
+# The effect of the bug was that we got a commit with a GTID with server_id
+#
+
+--source include/have_binlog_format_row.inc
+--source include/have_innodb.inc
+--source include/have_debug.inc
+--source include/have_debug_sync.inc
+
+--let $rpl_topology= 1->2->1
+--source include/rpl_init.inc
+
+--let $rpl_connection_name= M4
+--let $rpl_server_number= 1
+--source include/rpl_connect.inc
+
+--let $rpl_connection_name= M2
+--let $rpl_server_number= 2
+--source include/rpl_connect.inc
+
+# The parameter reflects binlog-row-event-max-size @cnf.
+--let $row_size=1024
+
+SET @old_debug= @@global.debug;
+
+--connection M2
+STOP SLAVE;
+SET GLOBAL debug_dbug= "+d,dbug.rows_events_to_delay_relay_logging";
+START SLAVE IO_THREAD;
+--source include/wait_for_slave_io_to_start.inc
+
+--connection M2
+# This query also creates a Gtid event group whose Gtid will remain in
+# ignored status for too long causing a following group split.
+
+CREATE TABLE t1 (a INT AUTO_INCREMENT PRIMARY KEY, b VARCHAR(30000)) ENGINE=innodb;
+--sync_slave_with_master M4
+
+# This INSERT will be logged as two Write_log events which the buggy
+# slave applier would split.
+
+--connection M4
+eval INSERT INTO `t1` VALUES (null, repeat('a', $row_size)), (null, repeat('b', $row_size));
+
+# START M2 IO thread and wait for its signal to follow with the SQL
+# thread start.  At this moment the SQL thread shall be having 2 and
+# "half" groups to execute.  The "hafl" one would be committed by the
+# buggy applier after which the IO is released to queue the rest of
+# the 3rd group which the SQL thread commits separately to complete
+# the split.
+
+--connection M2
+
+# wait for IO signal to start the SQL thread. IO will be hanging upon that.
+SET debug_sync='now WAIT_FOR start_sql_thread';
+
+# Now the slave server has relay log whose last group is incomplete.
+# An unfixed slave server would go to "insert" a "fake"
+# Gtid_list_log_event event which actually would commit the incomplete
+# group. However before to actual commit do_apply_event() hits some assert.
+# In the fixed server the fake Gtid_list_log_event is *not* inserted
+# in the middle of a group.
+START SLAVE SQL_THREAD;
+
+# Sleep for a little time to give SQL thread a chance to commit while
+# the IO thread is hanging (see
+# DBUG_EXECUTE_IF("dbug.rows_events_to_delay_relay_logging"...) in
+# queue_event).  Alternatively to reproduce the case when buggy slave
+# wait for the 1st group commit
+
+#--let $count= 1
+#--let $table= t1
+#--source include/wait_until_rows_count.inc
+
+--sleep 2
+
+# Demonstrate either no split group in the correct slave or the 1nd
+# group in the buggy one
+--source include/show_binlog_events.inc
+
+# Release the IO thread
+SET debug_sync='now SIGNAL go_on_relay_logging';
+
+# Sync servers
+--sync_slave_with_master M4
+--connection M4
+--sync_slave_with_master M2
+--connection M2
+
+# Demonstrate replication goes correctly not to create any split, or
+# the 2nd group in the buggy slave
+--source include/show_binlog_events.inc
+
+#
+# Cleanup
+#
+--connection M4
+drop table t1;
+
+--connection M2
+SET GLOBAL debug_dbug= @old_debug;
+SET debug_sync='RESET';
+--source include/rpl_end.inc
diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test
index cfb5042299a..bc84b6b9e26 100644
--- a/mysql-test/t/subselect.test
+++ b/mysql-test/t/subselect.test
@@ -3600,7 +3600,7 @@ SET SESSION sql_mode=@old_sql_mode;
 drop table t2, t1;
 drop view v1;
 
---echo End of 5.0 tests.
+--echo # End of 5.0 tests.
 
 #
 # Test [NOT] IN truth table (both as top-level and general predicate).
@@ -4685,7 +4685,7 @@ select * from t2 where a=(select a from t1) and a='x';
 
 drop table t1,t2;
 
---echo End of 5.1 tests
+--echo # End of 5.1 tests
 
 --echo #
 --echo # Bug #11765713 58705:
@@ -5156,7 +5156,7 @@ eval SELECT * FROM ( $subq ) AS alias3;
 
 DROP TABLE t1,t2;
 
---echo End of 5.2 tests
+--echo # End of 5.2 tests
 
 --echo #
 --echo # BUG#779885: Crash in eliminate_item_equal with materialization=on in
@@ -6004,11 +6004,17 @@ INSERT INTO t2 VALUES (3);
 
 CREATE ALGORITHM=MERGE VIEW v2 AS SELECT * FROM t2;
 
+--error ER_WRONG_GROUP_FIELD
+SELECT ( SELECT MIN(t2.f2) FROM t1 ) AS sq FROM t2 GROUP BY sq;
+--error ER_WRONG_GROUP_FIELD
 SELECT ( SELECT MIN(v2.f2) FROM t1 ) AS sq FROM v2 GROUP BY sq;
 SELECT * FROM v2 where ( SELECT MIN(v2.f2) FROM t1 ) > 0;
 SELECT count(*) FROM v2 group by ( SELECT MIN(v2.f2) FROM t1 );
 
 delete from t1;
+--error ER_WRONG_GROUP_FIELD
+SELECT ( SELECT MIN(t2.f2) FROM t1 ) AS sq FROM t2 GROUP BY sq;
+--error ER_WRONG_GROUP_FIELD
 SELECT ( SELECT MIN(v2.f2) FROM t1 ) AS sq FROM v2 GROUP BY sq;
 
 drop view v2;
@@ -6024,6 +6030,32 @@ SELECT * FROM t1 WHERE f2 >= SOME ( SELECT f1 FROM t1 );
 SELECT * FROM t1 WHERE f2 <= SOME ( SELECT f1 FROM t1 );
 DROP TABLE t1;
 
+--echo #
+--echo # MDEV-10146: Wrong result (or questionable result and behavior)
+--echo # with aggregate function in uncorrelated SELECT subquery
+--echo #
+CREATE TABLE t1 (f1 INT);
+CREATE VIEW v1 AS SELECT * FROM t1;
+INSERT INTO t1 VALUES (1),(2);
+
+CREATE TABLE t2 (f2 int);
+
+INSERT INTO t2 VALUES (3);
+SELECT ( SELECT MAX(f1) FROM t2 ) FROM t1;
+
+SELECT ( SELECT MAX(f1) FROM t2 ) FROM v1;
+
+INSERT INTO t2 VALUES (4);
+
+--error ER_SUBQUERY_NO_1_ROW
+SELECT ( SELECT MAX(f1) FROM t2 ) FROM v1;
+--error ER_SUBQUERY_NO_1_ROW
+SELECT ( SELECT MAX(f1) FROM t2 ) FROM t1;
+
+drop view v1;
+drop table t1,t2;
+
+--echo # End of 10.0 tests
 
 --echo #
 --echo # MDEV-9487: Server crashes in Time_and_counter_tracker::incr_loops
@@ -6051,4 +6083,4 @@ SELECT * FROM t1, t2 WHERE f3 = f2 AND f1 > ANY ( SELECT 'foo');
 DROP TABLE t1, t2;
 SET NAMES default;
 
---echo End of 10.1 tests
+--echo # End of 10.1 tests