From c562ccf796c085211461386510ea5f7a8137cb96 Mon Sep 17 00:00:00 2001 From: =?UTF-8?q?Marko=20M=C3=A4kel=C3=A4?= Date: Thu, 15 Dec 2022 11:14:23 +0200 Subject: MDEV-30233 DROP DATABASE test fails: Directory not empty Some tests drop the default mtr database "test". This may fail due to the directory not being empty. InnoDB may not delete all tables immediately, due to the "background drop table queue" or its replacement in commit 1bd681c8b3c5213ce1f7976940a7dc38b48a0d39 (the purge of history would clean up after a DDL operation during which the server was killed). Let us try to avoid "drop database test" whenever it is easily possible. Where it is not, SET GLOBAL innodb_max_purge_lag_wait=0 will ensure that the replacement of the "background drop table queue" will have completed its job. --- mysql-test/main/cte_recursive.result | 5 +++-- mysql-test/main/cte_recursive.test | 5 +++-- mysql-test/main/information_schema.result | 5 +++-- mysql-test/main/information_schema.test | 5 +++-- mysql-test/main/mysqldump.result | 1 + mysql-test/main/mysqldump.test | 2 ++ mysql-test/main/view.result | 5 +++-- mysql-test/main/view.test | 5 +++-- 8 files changed, 21 insertions(+), 12 deletions(-) (limited to 'mysql-test/main') diff --git a/mysql-test/main/cte_recursive.result b/mysql-test/main/cte_recursive.result index 7f535fc3125..c4b66794e3d 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 f5babc6cb65..270a5023d31 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/information_schema.result b/mysql-test/main/information_schema.result index fba61b354f3..82d34002617 100644 --- a/mysql-test/main/information_schema.result +++ b/mysql-test/main/information_schema.result @@ -2217,8 +2217,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 2fac02d2fe0..469be44ef94 100644 --- a/mysql-test/main/information_schema.test +++ b/mysql-test/main/information_schema.test @@ -1940,8 +1940,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 afaafb4907c..a517a5e3c48 100644 --- a/mysql-test/main/mysqldump.result +++ b/mysql-test/main/mysqldump.result @@ -4469,6 +4469,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 89155a435b1..a099727ba38 100644 --- a/mysql-test/main/mysqldump.test +++ b/mysql-test/main/mysqldump.test @@ -1921,6 +1921,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/view.result b/mysql-test/main/view.result index 7354e1dbb91..b042e27997f 100644 --- a/mysql-test/main/view.result +++ b/mysql-test/main/view.result @@ -6723,7 +6723,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); @@ -6753,7 +6755,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 987e96fc79d..11355c211ca 100644 --- a/mysql-test/main/view.test +++ b/mysql-test/main/view.test @@ -6437,7 +6437,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); @@ -6460,7 +6462,6 @@ drop view db1.v1; drop table db1.t1; drop database db1; -create database test; use test; --echo # -- cgit v1.2.1 From 0ca3aaa75fe084daabacfed0924598621dffec15 Mon Sep 17 00:00:00 2001 From: Lena Startseva Date: Thu, 15 Dec 2022 16:12:49 +0000 Subject: MDEV-27691: make working view-protocol Excluded one case from view-protocol in gis.test --- mysql-test/main/gis.test | 3 +++ 1 file changed, 3 insertions(+) (limited to 'mysql-test/main') diff --git a/mysql-test/main/gis.test b/mysql-test/main/gis.test index 716fab9bfeb..72b4661ad24 100644 --- a/mysql-test/main/gis.test +++ b/mysql-test/main/gis.test @@ -3093,12 +3093,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 -- cgit v1.2.1 From f8adc47b698ef8d347fd36bffff90b237491eceb Mon Sep 17 00:00:00 2001 From: Sergei Golubchik Date: Tue, 6 Dec 2022 15:48:13 +0100 Subject: MDEV-19071 Wrong results when using STDDEV_SAMP() and view --- mysql-test/main/func_group.result | 14 ++++++++++++++ mysql-test/main/func_group.test | 11 +++++++++++ 2 files changed, 25 insertions(+) (limited to 'mysql-test/main') diff --git a/mysql-test/main/func_group.result b/mysql-test/main/func_group.result index 0f3169e330f..0f80d14b603 100644 --- a/mysql-test/main/func_group.result +++ b/mysql-test/main/func_group.result @@ -2552,5 +2552,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 862ea453b46..6b3a15fd45e 100644 --- a/mysql-test/main/func_group.test +++ b/mysql-test/main/func_group.test @@ -1789,6 +1789,17 @@ 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 # -- cgit v1.2.1 From eba099184e1f6704894694ea41f97f216eae5f21 Mon Sep 17 00:00:00 2001 From: Sergei Golubchik Date: Mon, 5 Dec 2022 00:21:28 +0100 Subject: MDEV-30151 parse error 1=2 not between/in the parser couldn't parse `1=2 not between 3 and 5` after `2` it expected only NOT2_SYM, but not NOT_SYM (visible from the sql_yacc.output file), which resulted in Syntax error ... near 'not between 3 and 4' The parser was confused by a rather low NOT_SYM precedence and %prec BETWEEN_SYM didn't resolve this confusion. As a fix, let's remove any %precedence from NOT_SYM and specify %prec explicitly in the only place where it matters for NOT_SYM. In other places, such as for NOT BETWEEN, NOT_SYM won't have a precedence, so bison won't be confused about it. --- mysql-test/main/parser.result | 11 +++++++++++ mysql-test/main/parser.test | 8 ++++++++ 2 files changed, 19 insertions(+) (limited to 'mysql-test/main') diff --git a/mysql-test/main/parser.result b/mysql-test/main/parser.result index 0bb4e82c8b8..f44478727ae 100644 --- a/mysql-test/main/parser.result +++ b/mysql-test/main/parser.result @@ -1866,4 +1866,15 @@ 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 +# diff --git a/mysql-test/main/parser.test b/mysql-test/main/parser.test index 9df18c50ee3..cfe4f9d6f53 100644 --- a/mysql-test/main/parser.test +++ b/mysql-test/main/parser.test @@ -1673,4 +1673,12 @@ 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 # -- cgit v1.2.1