Merge 10.3 into 10.4

13 files changed, 68 insertions, 12 deletions

diff --git a/mysql-test/main/cte_recursive.result b/mysql-test/main/cte_recursive.result
index eb50b431548..7aa87fcbc6e 100644
--- a/mysql-test/main/cte_recursive.result
+++ b/mysql-test/main/cte_recursive.result
@@ -4235,7 +4235,9 @@ drop table t1;
 #
 # MDEV-24019: query with recursive CTE when no default database is set
 #
-drop database test;
+create database dummy;
+use dummy;
+drop database dummy;
 with recursive a as
 (select 1 from dual union select * from a as r)
 select * from a;
@@ -4274,7 +4276,6 @@ a
 1
 deallocate prepare stmt;
 drop database db1;
-create database test;
 use test;
 #
 # MDEV-23406: query with mutually recursive CTEs when big_tables=1
diff --git a/mysql-test/main/cte_recursive.test b/mysql-test/main/cte_recursive.test
index 0194caae25c..3cc3b66b9c3 100644
--- a/mysql-test/main/cte_recursive.test
+++ b/mysql-test/main/cte_recursive.test
@@ -2729,7 +2729,9 @@ drop table t1;
 --echo # MDEV-24019: query with recursive CTE when no default database is set
 --echo #
 
-drop database test;
+create database dummy;
+use dummy;
+drop database dummy;
 
 let $q=
 with recursive a as
@@ -2757,7 +2759,6 @@ deallocate prepare stmt;
 
 drop database db1;
 
-create database test;
 use test;
 
 --echo #
diff --git a/mysql-test/main/func_group.result b/mysql-test/main/func_group.result
index 5908b41c61a..3d521f2e00b 100644
--- a/mysql-test/main/func_group.result
+++ b/mysql-test/main/func_group.result
@@ -2553,5 +2553,19 @@ DROP TABLE t1;
 #
 SET STATEMENT sql_mode=ONLY_FULL_GROUP_BY FOR EXECUTE IMMEDIATE 'ALTER TABLE mysql.time_zone_transition ORDER BY Time_zone_id, Transition_time';
 #
+# MDEV-19071 Wrong results when using STDDEV_SAMP() and view
+#
+create table t1(i int);
+insert into t1 values  (1),(2),(3),(4),(5);
+create view v1 as select stddev_samp(i),stddev_pop(i),stddev(i),std(i) from t1;
+show create view v1;
+View	Create View	character_set_client	collation_connection
+v1	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select stddev_samp(`t1`.`i`) AS `stddev_samp(i)`,std(`t1`.`i`) AS `stddev_pop(i)`,std(`t1`.`i`) AS `stddev(i)`,std(`t1`.`i`) AS `std(i)` from `t1`	latin1	latin1_swedish_ci
+select * from v1;
+stddev_samp(i)	stddev_pop(i)	stddev(i)	std(i)
+1.5811	1.4142	1.4142	1.4142
+drop view v1;
+drop table t1;
+#
 # End of 10.3 tests
 #
diff --git a/mysql-test/main/func_group.test b/mysql-test/main/func_group.test
index b5e9b5d93df..ac4ca32e7f5 100644
--- a/mysql-test/main/func_group.test
+++ b/mysql-test/main/func_group.test
@@ -1791,5 +1791,16 @@ DROP TABLE t1;
 SET STATEMENT sql_mode=ONLY_FULL_GROUP_BY FOR EXECUTE IMMEDIATE 'ALTER TABLE mysql.time_zone_transition ORDER BY Time_zone_id, Transition_time';
 
 --echo #
+--echo # MDEV-19071 Wrong results when using STDDEV_SAMP() and view
+--echo #
+create table t1(i int);
+insert into t1 values  (1),(2),(3),(4),(5);
+create view v1 as select stddev_samp(i),stddev_pop(i),stddev(i),std(i) from t1;
+show create view v1;
+select * from v1;
+drop view v1;
+drop table t1;
+
+--echo #
 --echo # End of 10.3 tests
 --echo #
diff --git a/mysql-test/main/gis.test b/mysql-test/main/gis.test
index 6a1a02fe74d..a93ee736566 100644
--- a/mysql-test/main/gis.test
+++ b/mysql-test/main/gis.test
@@ -3092,12 +3092,15 @@ SELECT IS_USED_LOCK(POINT(1,1));
 --echo #
 --echo # MDEV-26161 crash in Gis_point::calculate_haversine
 --echo #
+#enable after fix MDEV-30229
+--disable_view_protocol
 --error ER_CANT_CREATE_GEOMETRY_OBJECT
 select st_distance_sphere(x'01030000000400000004000000000000', multipoint(point(124,204)), 10);
 --error ER_CANT_CREATE_GEOMETRY_OBJECT
 select st_distance_sphere(x'010300000004000000040000', multipoint(point(124,204)), 10);
 --error ER_CANT_CREATE_GEOMETRY_OBJECT
 select st_distance_sphere(x'010300000001000000040000', multipoint(point(124,204)), 10);
+--enable_view_protocol
 
 --echo #
 --echo # End of 10.3 tests
diff --git a/mysql-test/main/information_schema.result b/mysql-test/main/information_schema.result
index 649d1292b39..6686d451dd8 100644
--- a/mysql-test/main/information_schema.result
+++ b/mysql-test/main/information_schema.result
@@ -2234,8 +2234,9 @@ Warning	1931	Query execution was interrupted. The query examined at least ### ro
 # m_status == DA_OK_BULK' failed in Diagnostics_area::message()
 #
 call mtr.add_suppression("Sort aborted.*");
-DROP DATABASE test;
-CREATE DATABASE test;
+create database dummy;
+use dummy;
+drop database dummy;
 USE test;
 CREATE VIEW v AS SELECT table_schema  AS object_schema, table_name  AS object_name, table_type AS object_type FROM information_schema.tables ORDER BY object_schema;
 SELECT * FROM v LIMIT ROWS EXAMINED 9;
diff --git a/mysql-test/main/information_schema.test b/mysql-test/main/information_schema.test
index 77d0df59e2f..b9ed6326350 100644
--- a/mysql-test/main/information_schema.test
+++ b/mysql-test/main/information_schema.test
@@ -1941,8 +1941,9 @@ SELECT * FROM INFORMATION_SCHEMA.`COLUMNS` LIMIT ROWS EXAMINED 10;
 
 call mtr.add_suppression("Sort aborted.*");
 
-DROP DATABASE test;
-CREATE DATABASE test;
+create database dummy;
+use dummy;
+drop database dummy;
 USE test;
 CREATE VIEW v AS SELECT table_schema  AS object_schema, table_name  AS object_name, table_type AS object_type FROM information_schema.tables ORDER BY object_schema;
 
diff --git a/mysql-test/main/mysqldump.result b/mysql-test/main/mysqldump.result
index 9345b5c6d2b..25597d46b75 100644
--- a/mysql-test/main/mysqldump.result
+++ b/mysql-test/main/mysqldump.result
@@ -4471,6 +4471,7 @@ Db	Name	Definer	Time zone	Type	Execute at	Interval value	Interval field	Starts	E
 # MDEV-13336: add ignore-database option
 #           with --all-databases
 #
+SET GLOBAL innodb_max_purge_lag_wait=0;
 DROP DATABASE test;
 SHOW DATABASES LIKE 'test';
 Database (test)
diff --git a/mysql-test/main/mysqldump.test b/mysql-test/main/mysqldump.test
index d1b0a7d8c84..78f0976ce6a 100644
--- a/mysql-test/main/mysqldump.test
+++ b/mysql-test/main/mysqldump.test
@@ -1922,6 +1922,8 @@ SHOW EVENTS;
 --echo #           with --all-databases
 --echo #
 --exec $MYSQL_DUMP --default-character-set=utf8mb4 --ignore-database test --all-databases > $MYSQLTEST_VARDIR/tmp/mysqldump-MDEV-13336.sql
+# Starting with MariaDB 10.6, ensure that DDL recovery will have completed.
+SET GLOBAL innodb_max_purge_lag_wait=0;
 DROP DATABASE test;
 --exec $MYSQL < $MYSQLTEST_VARDIR/tmp/mysqldump-MDEV-13336.sql
 SHOW DATABASES LIKE 'test';
diff --git a/mysql-test/main/parser.result b/mysql-test/main/parser.result
index 6f05caec0bd..32cc70bf50c 100644
--- a/mysql-test/main/parser.result
+++ b/mysql-test/main/parser.result
@@ -1870,8 +1870,19 @@ ERROR 42000: You have an error in your SQL syntax; check the manual that corresp
 EXECUTE IMMEDIATE 'CREATE PROCEDURE p() UPDATE t SET c=\'\'"abc';
 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '"abc' at line 1
 SET @@sql_mode=@save_sql_mode;
+#
+# MDEV-30151 parse error 1=2 not between/in
+#
+select 1=2 not in (3,4);
+1=2 not in (3,4)
+1
+select 1=2 not between 3 and 4;
+1=2 not between 3 and 4
+1
+#
 # End of 10.3 tests
 #
+#
 # MDEV-19540: 10.4 allow lock options with SELECT in brackets
 # which previous version do not
 #
diff --git a/mysql-test/main/parser.test b/mysql-test/main/parser.test
index e4ca1823c60..13126b77f78 100644
--- a/mysql-test/main/parser.test
+++ b/mysql-test/main/parser.test
@@ -1678,7 +1678,15 @@ EXECUTE IMMEDIATE 'CREATE PROCEDURE p() UPDATE t SET c=\'\'"abc';
 
 SET @@sql_mode=@save_sql_mode;
 
+--echo #
+--echo # MDEV-30151 parse error 1=2 not between/in
+--echo #
+select 1=2 not in (3,4);
+select 1=2 not between 3 and 4;
+
+--echo #
 --echo # End of 10.3 tests
+--echo #
 
 --echo #
 --echo # MDEV-19540: 10.4 allow lock options with SELECT in brackets
diff --git a/mysql-test/main/view.result b/mysql-test/main/view.result
index 12214ca8bc8..8c31545eb84 100644
--- a/mysql-test/main/view.result
+++ b/mysql-test/main/view.result
@@ -6735,7 +6735,9 @@ DROP TABLE t1;
 #
 # MDEV-24314: create view with derived table without default database
 #
-drop database test;
+create database dummy;
+use dummy;
+drop database dummy;
 create database db1;
 create table db1.t1 (a int);
 insert into db1.t1 values (3),(7),(1);
@@ -6765,7 +6767,6 @@ a
 drop view db1.v1;
 drop table db1.t1;
 drop database db1;
-create database test;
 use test;
 #
 # MDEV-16940: update of multi-table view returning error used in SP
diff --git a/mysql-test/main/view.test b/mysql-test/main/view.test
index 78a2c946491..0e2dce1fb70 100644
--- a/mysql-test/main/view.test
+++ b/mysql-test/main/view.test
@@ -6438,7 +6438,9 @@ DROP TABLE t1;
 --echo # MDEV-24314: create view with derived table without default database
 --echo #
 
-drop database test;
+create database dummy;
+use dummy;
+drop database dummy;
 
 create database db1;
 create table db1.t1 (a int);
@@ -6461,7 +6463,6 @@ drop view db1.v1;
 drop table db1.t1;
 drop database db1;
 
-create database test;
 use test;
 
 --echo #