summaryrefslogtreecommitdiff
path: root/mysql-test
diff options
context:
space:
mode:
Diffstat (limited to 'mysql-test')
-rw-r--r--mysql-test/include/ctype_utf8mb4.inc2
-rw-r--r--mysql-test/main/brackets.result12
-rw-r--r--mysql-test/main/cte_cycle.result2
-rw-r--r--mysql-test/main/cte_nonrecursive.result43
-rw-r--r--mysql-test/main/cte_nonrecursive.test29
-rw-r--r--mysql-test/main/cte_nonrecursive_not_embedded.result48
-rw-r--r--mysql-test/main/cte_nonrecursive_not_embedded.test58
-rw-r--r--mysql-test/main/cte_recursive.result271
-rw-r--r--mysql-test/main/cte_recursive.test129
-rw-r--r--mysql-test/main/ctype_utf16.result2
-rw-r--r--mysql-test/main/ctype_utf16le.result2
-rw-r--r--mysql-test/main/ctype_utf32.result2
-rw-r--r--mysql-test/main/ctype_utf8.result4
-rw-r--r--mysql-test/main/ctype_utf8mb4.result4
-rw-r--r--mysql-test/main/ctype_utf8mb4.test2
-rw-r--r--mysql-test/main/ctype_utf8mb4_heap.result4
-rw-r--r--mysql-test/main/ctype_utf8mb4_innodb.result4
-rw-r--r--mysql-test/main/ctype_utf8mb4_myisam.result4
-rw-r--r--mysql-test/main/drop_combinations.result17
-rw-r--r--mysql-test/main/func_gconcat.result12
-rw-r--r--mysql-test/main/func_gconcat.test10
-rw-r--r--mysql-test/main/func_like.result18
-rw-r--r--mysql-test/main/func_like.test29
-rw-r--r--mysql-test/main/information_schema.result14
-rw-r--r--mysql-test/main/information_schema.test3
-rw-r--r--mysql-test/main/lock_view.result34
-rw-r--r--mysql-test/main/lock_view.test17
-rw-r--r--mysql-test/main/mysql_upgrade.result2
-rw-r--r--mysql-test/main/mysql_upgrade.test2
-rw-r--r--mysql-test/main/order_by.result22
-rw-r--r--mysql-test/main/order_by.test8
-rw-r--r--mysql-test/main/precedence.result4
-rw-r--r--mysql-test/main/precedence.test3
-rw-r--r--mysql-test/main/query_cache.result28
-rw-r--r--mysql-test/main/query_cache.test10
-rw-r--r--mysql-test/main/range.result25
-rw-r--r--mysql-test/main/range.test34
-rw-r--r--mysql-test/main/range_mrr_icp.result25
-rw-r--r--mysql-test/main/range_notembedded.result37
-rw-r--r--mysql-test/main/range_notembedded.test33
-rw-r--r--mysql-test/main/set_password.result8
-rw-r--r--mysql-test/main/set_password.test7
-rw-r--r--mysql-test/main/type_year.result75
-rw-r--r--mysql-test/main/type_year.test38
-rw-r--r--mysql-test/main/view.result35
-rw-r--r--mysql-test/main/view.test30
-rwxr-xr-xmysql-test/mysql-test-run.pl1
-rw-r--r--mysql-test/suite/binlog/disabled.def2
-rw-r--r--mysql-test/suite/binlog/r/binlog_spurious_ddl_errors.result7
-rw-r--r--mysql-test/suite/binlog/r/binlog_truncate_innodb.result185
-rw-r--r--mysql-test/suite/binlog/t/binlog_spurious_ddl_errors-master.opt1
-rw-r--r--mysql-test/suite/binlog/t/binlog_spurious_ddl_errors.test1
-rw-r--r--mysql-test/suite/binlog/t/binlog_truncate_innodb-master.opt1
-rw-r--r--mysql-test/suite/binlog/t/binlog_truncate_innodb.test3
-rw-r--r--mysql-test/suite/binlog_encryption/rpl_semi_sync.result19
-rw-r--r--mysql-test/suite/federated/federatedx.result26
-rw-r--r--mysql-test/suite/federated/federatedx.test29
-rw-r--r--mysql-test/suite/funcs_1/r/is_check_constraints.result5
-rw-r--r--mysql-test/suite/funcs_1/r/is_columns_is.result4
-rw-r--r--mysql-test/suite/funcs_1/r/is_columns_is_embedded.result4
-rw-r--r--mysql-test/suite/funcs_1/r/is_tables_is.result8
-rw-r--r--mysql-test/suite/funcs_1/r/is_tables_is_embedded.result8
-rw-r--r--mysql-test/suite/funcs_1/t/is_check_constraints.test4
-rw-r--r--mysql-test/suite/galera/r/MDEV-24327.result35
-rw-r--r--mysql-test/suite/galera/r/MENT-1047.result4
-rw-r--r--mysql-test/suite/galera/r/galera#500.result2
-rw-r--r--mysql-test/suite/galera/t/MDEV-24327.cnf6
-rw-r--r--mysql-test/suite/galera/t/MDEV-24327.test87
-rw-r--r--mysql-test/suite/galera/t/MENT-1047.test7
-rw-r--r--mysql-test/suite/galera/t/galera#500.test5
-rw-r--r--mysql-test/suite/gcol/r/innodb_virtual_fk.result28
-rw-r--r--mysql-test/suite/gcol/t/innodb_virtual_fk.test37
-rw-r--r--mysql-test/suite/innodb/r/innodb_multi_update.result1
-rw-r--r--mysql-test/suite/innodb/r/innodb_stats.result10
-rw-r--r--mysql-test/suite/innodb/t/innodb_multi_update.test1
-rw-r--r--mysql-test/suite/innodb/t/innodb_stats.test2
-rw-r--r--mysql-test/suite/plugins/r/server_audit.result2
-rw-r--r--mysql-test/suite/rpl/include/rpl_semi_sync.inc72
-rw-r--r--mysql-test/suite/rpl/r/rpl_drop_temp_table_invalid_lex.result (renamed from mysql-test/suite/rpl/r/rpl_drop_temp_table_invaid_lex.result)5
-rw-r--r--mysql-test/suite/rpl/r/rpl_semi_sync.result19
-rw-r--r--mysql-test/suite/rpl/r/rpl_semi_sync_after_sync.result19
-rw-r--r--mysql-test/suite/rpl/r/rpl_semi_sync_after_sync_row.result19
-rw-r--r--mysql-test/suite/rpl/t/rpl_drop_temp_table_invalid_lex.test (renamed from mysql-test/suite/rpl/t/rpl_drop_temp_table_invaid_lex.test)14
-rw-r--r--mysql-test/suite/rpl/t/rpl_parallel_retry.test3
-rw-r--r--mysql-test/suite/sql_sequence/other.result58
-rw-r--r--mysql-test/suite/sql_sequence/other.test62
-rw-r--r--mysql-test/suite/sys_vars/r/max_sort_length_basic.result199
-rw-r--r--mysql-test/suite/sys_vars/r/max_sort_length_func.result442
-rw-r--r--mysql-test/suite/sys_vars/r/sysvars_server_embedded.result6
-rw-r--r--mysql-test/suite/sys_vars/r/sysvars_server_notembedded.result6
-rw-r--r--mysql-test/suite/sys_vars/t/max_sort_length_basic.test225
-rw-r--r--mysql-test/suite/sys_vars/t/max_sort_length_func.test157
-rw-r--r--mysql-test/suite/versioning/common.inc21
-rw-r--r--mysql-test/suite/versioning/common_finish.inc2
-rw-r--r--mysql-test/suite/versioning/r/delete.result19
-rw-r--r--mysql-test/suite/versioning/r/foreign.result16
-rw-r--r--mysql-test/suite/versioning/r/partition.result7
-rw-r--r--mysql-test/suite/versioning/r/trx_id.result8
-rw-r--r--mysql-test/suite/versioning/r/update.result49
-rw-r--r--mysql-test/suite/versioning/t/delete.test15
-rw-r--r--mysql-test/suite/versioning/t/foreign.test18
-rw-r--r--mysql-test/suite/versioning/t/partition.test9
-rw-r--r--mysql-test/suite/versioning/t/trx_id.test6
-rw-r--r--mysql-test/suite/versioning/t/update.test40
104 files changed, 2169 insertions, 1084 deletions
diff --git a/mysql-test/include/ctype_utf8mb4.inc b/mysql-test/include/ctype_utf8mb4.inc
index 10d4f99efba..d24ee2fafeb 100644
--- a/mysql-test/include/ctype_utf8mb4.inc
+++ b/mysql-test/include/ctype_utf8mb4.inc
@@ -1587,7 +1587,7 @@ drop table t1;
--echo #
--echo # Check strnxfrm() with odd length
--echo #
-set max_sort_length=9;
+set max_sort_length=65;
select @@max_sort_length;
eval create table t1 (a varchar(128) character set utf8mb4 collate utf8mb4_general_ci) engine $engine;
insert into t1 values ('a'),('b'),('c');
diff --git a/mysql-test/main/brackets.result b/mysql-test/main/brackets.result
index 3de7d902335..a71a4c1edcf 100644
--- a/mysql-test/main/brackets.result
+++ b/mysql-test/main/brackets.result
@@ -4397,7 +4397,7 @@ with t as (select * from t1 where a <=3)
select * from t;
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 with t as (select `t1`.`a` AS `a` from `t1` where `t1`.`a` <= 3)select `t`.`a` AS `a` from `t` latin1 latin1_swedish_ci
+v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS with t as (select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` <= 3)select `t`.`a` AS `a` from `t` latin1 latin1_swedish_ci
select * from v1;
a
3
@@ -4413,7 +4413,7 @@ order by a desc limit 3 )
select a from t1 where a=4 union select a from t where a=7;
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 with t as (select `t1`.`a` AS `a` from `t1` where `t1`.`a` < 3 union select `t1`.`a` AS `a` from `t1` where `t1`.`a` > 3 order by `a` desc limit 3)select `t1`.`a` AS `a` from `t1` where `t1`.`a` = 4 union select `t`.`a` AS `a` from `t` where `t`.`a` = 7 latin1 latin1_swedish_ci
+v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS with t as (select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` < 3 union select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` > 3 order by `a` desc limit 3)select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = 4 union select `t`.`a` AS `a` from `t` where `t`.`a` = 7 latin1 latin1_swedish_ci
select * from v1;
a
4
@@ -4429,7 +4429,7 @@ order by a desc limit 3 )
(select a from t1 where a=4 union select a from t where a=7 order by a desc);
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 with t as ((select `t1`.`a` AS `a` from `t1` where `t1`.`a` < 3) union (select `t1`.`a` AS `a` from `t1` where `t1`.`a` > 3) order by `a` desc limit 3)select `t1`.`a` AS `a` from `t1` where `t1`.`a` = 4 union select `t`.`a` AS `a` from `t` where `t`.`a` = 7 order by `a` desc latin1 latin1_swedish_ci
+v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS with t as ((select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` < 3) union (select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` > 3) order by `a` desc limit 3)select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = 4 union select `t`.`a` AS `a` from `t` where `t`.`a` = 7 order by `a` desc latin1 latin1_swedish_ci
select * from v1;
a
7
@@ -4469,7 +4469,7 @@ order by 1 desc limit 3 )
select a from t1 where a=4 union select a from t where a=7 order by a desc;
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 with t(`a`) as (values (2),(1) union (values (4),(7)) order by 1 desc limit 3)select `t1`.`a` AS `a` from `t1` where `t1`.`a` = 4 union select `t`.`a` AS `a` from `t` where `t`.`a` = 7 order by `a` desc latin1 latin1_swedish_ci
+v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS with t(`a`) as (values (2),(1) union (values (4),(7)) order by 1 desc limit 3)select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = 4 union select `t`.`a` AS `a` from `t` where `t`.`a` = 7 order by `a` desc latin1 latin1_swedish_ci
select * from v1;
a
7
@@ -4484,7 +4484,7 @@ order by 1 desc limit 3 )
select a from t1 where a=1 union select a from t where a=7 order by a desc;
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 with t(`a`) as ((values (2),(1)) union (values (4),(7) order by 1 desc) order by 1 desc limit 3)select `t1`.`a` AS `a` from `t1` where `t1`.`a` = 1 union select `t`.`a` AS `a` from `t` where `t`.`a` = 7 order by `a` desc latin1 latin1_swedish_ci
+v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS with t(`a`) as ((values (2),(1)) union (values (4),(7) order by 1 desc) order by 1 desc limit 3)select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = 1 union select `t`.`a` AS `a` from `t` where `t`.`a` = 7 order by `a` desc latin1 latin1_swedish_ci
select * from v1;
a
7
@@ -4496,7 +4496,7 @@ s as (select * from t1 where a > 3)
select a from t where a=1 union select a from s where a=7 order by a desc;
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 with t as (select `t1`.`a` AS `a` from `t1` where `t1`.`a` < 3), s as (select `t1`.`a` AS `a` from `t1` where `t1`.`a` > 3)select `t`.`a` AS `a` from `t` where `t`.`a` = 1 union select `s`.`a` AS `a` from `s` where `s`.`a` = 7 order by `a` desc latin1 latin1_swedish_ci
+v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS with t as (select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` < 3), s as (select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` > 3)select `t`.`a` AS `a` from `t` where `t`.`a` = 1 union select `s`.`a` AS `a` from `s` where `s`.`a` = 7 order by `a` desc latin1 latin1_swedish_ci
select * from v1;
a
7
diff --git a/mysql-test/main/cte_cycle.result b/mysql-test/main/cte_cycle.result
index e66d090b3c7..e7e0cc5f801 100644
--- a/mysql-test/main/cte_cycle.result
+++ b/mysql-test/main/cte_cycle.result
@@ -67,7 +67,7 @@ FROM t1, cte WHERE t1.from_ = cte.to_
select * from cte;
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 with recursive cte(`depth`,`from_`,`to_`) as (select 0 AS `depth`,1 AS `from_`,1 AS `to_` union select `cte`.`depth` + 1 AS `depth+1`,`t1`.`from_` AS `from_`,`t1`.`to_` AS `to_` from (`t1` join `cte`) where `t1`.`from_` = `cte`.`to_`) CYCLE `from_`,`to_` RESTRICT select `cte`.`depth` AS `depth`,`cte`.`from_` AS `from_`,`cte`.`to_` AS `to_` from `cte` latin1 latin1_swedish_ci
+v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS with recursive cte(`depth`,`from_`,`to_`) as (select 0 AS `depth`,1 AS `from_`,1 AS `to_` union select `cte`.`depth` + 1 AS `depth+1`,`test`.`t1`.`from_` AS `from_`,`test`.`t1`.`to_` AS `to_` from (`test`.`t1` join `cte`) where `test`.`t1`.`from_` = `cte`.`to_`) CYCLE `from_`,`to_` RESTRICT select `cte`.`depth` AS `depth`,`cte`.`from_` AS `from_`,`cte`.`to_` AS `to_` from `cte` latin1 latin1_swedish_ci
select * from v1;
depth from_ to_
0 1 1
diff --git a/mysql-test/main/cte_nonrecursive.result b/mysql-test/main/cte_nonrecursive.result
index 6ff33e6da29..e773caa7614 100644
--- a/mysql-test/main/cte_nonrecursive.result
+++ b/mysql-test/main/cte_nonrecursive.result
@@ -571,7 +571,7 @@ with t as (select a from t1 where b >= 'c')
select * from t2,t where t2.c=t.a;
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 with t as (select `t1`.`a` AS `a` from `t1` where `t1`.`b` >= 'c')select `t2`.`c` AS `c`,`t`.`a` AS `a` from (`t2` join `t`) where `t2`.`c` = `t`.`a` latin1 latin1_swedish_ci
+v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS with t as (select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`b` >= 'c')select `test`.`t2`.`c` AS `c`,`t`.`a` AS `a` from (`test`.`t2` join `t`) where `test`.`t2`.`c` = `t`.`a` latin1 latin1_swedish_ci
select * from v1;
c a
4 4
@@ -588,7 +588,7 @@ with t as (select a, count(*) from t1 where b >= 'c' group by a)
select * from t2,t where t2.c=t.a;
show create view v2;
View Create View character_set_client collation_connection
-v2 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v2` AS with t as (select `t1`.`a` AS `a`,count(0) AS `count(*)` from `t1` where `t1`.`b` >= 'c' group by `t1`.`a`)select `t2`.`c` AS `c`,`t`.`a` AS `a`,`t`.`count(*)` AS `count(*)` from (`t2` join `t`) where `t2`.`c` = `t`.`a` latin1 latin1_swedish_ci
+v2 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v2` AS with t as (select `test`.`t1`.`a` AS `a`,count(0) AS `count(*)` from `test`.`t1` where `test`.`t1`.`b` >= 'c' group by `test`.`t1`.`a`)select `test`.`t2`.`c` AS `c`,`t`.`a` AS `a`,`t`.`count(*)` AS `count(*)` from (`test`.`t2` join `t`) where `test`.`t2`.`c` = `t`.`a` latin1 latin1_swedish_ci
select * from v2;
c a count(*)
4 4 2
@@ -606,7 +606,7 @@ with t(c) as (select a from t1 where b >= 'c')
select * from t r1 where r1.c=4;
show create view v3;
View Create View character_set_client collation_connection
-v3 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v3` AS with t(`c`) as (select `t1`.`a` AS `c` from `t1` where `t1`.`b` >= 'c')select `r1`.`c` AS `c` from `t` `r1` where `r1`.`c` = 4 latin1 latin1_swedish_ci
+v3 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v3` AS with t(`c`) as (select `test`.`t1`.`a` AS `c` from `test`.`t1` where `test`.`t1`.`b` >= 'c')select `r1`.`c` AS `c` from `t` `r1` where `r1`.`c` = 4 latin1 latin1_swedish_ci
select * from v3;
c
4
@@ -1349,7 +1349,7 @@ r.r_regionkey in
select r_regionkey from t where r_name <> "ASIA");
show create view v;
View Create View character_set_client collation_connection
-v CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v` AS select `n`.`n_nationkey` AS `n_nationkey`,`n`.`n_name` AS `n_name`,`n`.`n_regionkey` AS `n_regionkey`,`r`.`r_regionkey` AS `r_regionkey`,`r`.`r_name` AS `r_name` from (`nation` `n` join `region` `r`) where `n`.`n_regionkey` = `r`.`r_regionkey` and `r`.`r_regionkey` in (with t as (select `region`.`r_regionkey` AS `r_regionkey`,`region`.`r_name` AS `r_name` from `region` where `region`.`r_regionkey` <= 3)select `t`.`r_regionkey` from `t` where `t`.`r_name` <> 'ASIA') latin1 latin1_swedish_ci
+v CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v` AS select `n`.`n_nationkey` AS `n_nationkey`,`n`.`n_name` AS `n_name`,`n`.`n_regionkey` AS `n_regionkey`,`r`.`r_regionkey` AS `r_regionkey`,`r`.`r_name` AS `r_name` from (`test`.`nation` `n` join `test`.`region` `r`) where `n`.`n_regionkey` = `r`.`r_regionkey` and `r`.`r_regionkey` in (with t as (select `test`.`region`.`r_regionkey` AS `r_regionkey`,`test`.`region`.`r_name` AS `r_name` from `test`.`region` where `test`.`region`.`r_regionkey` <= 3)select `t`.`r_regionkey` from `t` where `t`.`r_name` <> 'ASIA') latin1 latin1_swedish_ci
select * from v;
n_nationkey n_name n_regionkey r_regionkey r_name
0 ALGERIA 0 0 AFRICA
@@ -1690,6 +1690,41 @@ ERROR 3D000: No database selected
DROP TABLE test.t;
connection default;
disconnect con1;
+#
+# MDEV-22781: create view with CTE without default database
+#
+drop database test;
+create database db1;
+create table db1.t1 (a int);
+insert into db1.t1 values (3),(7),(1);
+create view db1.v1 as with t as (select * from db1.t1) select * from t;
+show create view db1.v1;
+View Create View character_set_client collation_connection
+v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `db1`.`v1` AS with t as (select `db1`.`t1`.`a` AS `a` from `db1`.`t1`)select `t`.`a` AS `a` from `t` latin1 latin1_swedish_ci
+select * from db1.v1;
+a
+3
+7
+1
+drop view db1.v1;
+prepare stmt from "
+create view db1.v1 as with t as (select * from db1.t1) select * from t;
+";
+execute stmt;
+deallocate prepare stmt;
+show create view db1.v1;
+View Create View character_set_client collation_connection
+v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `db1`.`v1` AS with t as (select `db1`.`t1`.`a` AS `a` from `db1`.`t1`)select `t`.`a` AS `a` from `t` latin1 latin1_swedish_ci
+select * from db1.v1;
+a
+3
+7
+1
+drop view db1.v1;
+drop table db1.t1;
+drop database db1;
+create database test;
+use test;
# End of 10.2 tests
#
# MDEV-21673: several references to CTE that uses
diff --git a/mysql-test/main/cte_nonrecursive.test b/mysql-test/main/cte_nonrecursive.test
index 807e2e08bac..870f898f239 100644
--- a/mysql-test/main/cte_nonrecursive.test
+++ b/mysql-test/main/cte_nonrecursive.test
@@ -1213,6 +1213,35 @@ DROP TABLE test.t;
--connection default
--disconnect con1
+--echo #
+--echo # MDEV-22781: create view with CTE without default database
+--echo #
+
+drop database test;
+create database db1;
+create table db1.t1 (a int);
+insert into db1.t1 values (3),(7),(1);
+
+create view db1.v1 as with t as (select * from db1.t1) select * from t;
+show create view db1.v1;
+select * from db1.v1;
+drop view db1.v1;
+
+prepare stmt from "
+create view db1.v1 as with t as (select * from db1.t1) select * from t;
+";
+execute stmt;
+deallocate prepare stmt;
+show create view db1.v1;
+select * from db1.v1;
+drop view db1.v1;
+
+drop table db1.t1;
+drop database db1;
+
+create database test;
+use test;
+
--echo # End of 10.2 tests
--echo #
diff --git a/mysql-test/main/cte_nonrecursive_not_embedded.result b/mysql-test/main/cte_nonrecursive_not_embedded.result
new file mode 100644
index 00000000000..c96a1ec2849
--- /dev/null
+++ b/mysql-test/main/cte_nonrecursive_not_embedded.result
@@ -0,0 +1,48 @@
+#
+# MDEV-20751: query using many CTEs with grant_tables enabled
+#
+connection default;
+CREATE DATABASE db;
+USE db;
+CREATE TABLE t1 (a int) ENGINE=MYISAM;
+INSERT INTO t1 VALUES (3), (7), (1);
+CREATE TABLE t2 (a int) ENGINE=MYISAM;
+INSERT INTO t2 VALUES (2), (8), (4);
+CREATE USER 'u1'@'localhost';
+GRANT USAGE ON db.* TO 'u1'@'localhost';
+GRANT SELECT ON db.t1 TO 'u1'@'localhost';
+FLUSH PRIVILEGES;
+connect u1,'localhost',u1,,;
+connection u1;
+USE db;
+WITH
+cte1 AS
+(SELECT a FROM t1),
+cte2 AS
+(SELECT cte1.a FROM t1,cte1 WHERE cte1.a = t1.a),
+cte3 AS
+(SELECT cte2.a FROM t1,cte1,cte2 WHERE cte1.a = t1.a AND t1.a = cte2.a),
+cte4 AS
+(SELECT cte2.a FROM t1,cte2 WHERE cte2.a = t1.a)
+SELECT * FROM cte4 as r;
+a
+3
+7
+1
+WITH
+cte1 AS
+(SELECT a FROM t2),
+cte2 AS
+(SELECT cte1.a FROM t2,cte1 WHERE cte1.a = t2.a),
+cte3 AS
+(SELECT cte2.a FROM t2,cte1,cte2 WHERE cte1.a = t2.a AND t2.a = cte2.a),
+cte4 AS
+(SELECT cte2.a FROM t2,cte2 WHERE cte2.a = t2.a)
+SELECT * FROM cte4 as r;
+ERROR 42000: SELECT command denied to user 'u1'@'localhost' for table 't2'
+disconnect u1;
+connection default;
+DROP USER 'u1'@'localhost';
+DROP DATABASE db;
+USE test;
+# End of 10.2 tests
diff --git a/mysql-test/main/cte_nonrecursive_not_embedded.test b/mysql-test/main/cte_nonrecursive_not_embedded.test
new file mode 100644
index 00000000000..e80baeaf591
--- /dev/null
+++ b/mysql-test/main/cte_nonrecursive_not_embedded.test
@@ -0,0 +1,58 @@
+-- source include/not_embedded.inc
+
+--echo #
+--echo # MDEV-20751: query using many CTEs with grant_tables enabled
+--echo #
+
+--connection default
+
+CREATE DATABASE db;
+USE db;
+
+CREATE TABLE t1 (a int) ENGINE=MYISAM;
+INSERT INTO t1 VALUES (3), (7), (1);
+CREATE TABLE t2 (a int) ENGINE=MYISAM;
+INSERT INTO t2 VALUES (2), (8), (4);
+
+
+CREATE USER 'u1'@'localhost';
+GRANT USAGE ON db.* TO 'u1'@'localhost';
+GRANT SELECT ON db.t1 TO 'u1'@'localhost';
+FLUSH PRIVILEGES;
+
+--connect (u1,'localhost',u1,,)
+--connection u1
+USE db;
+
+WITH
+cte1 AS
+(SELECT a FROM t1),
+cte2 AS
+(SELECT cte1.a FROM t1,cte1 WHERE cte1.a = t1.a),
+cte3 AS
+(SELECT cte2.a FROM t1,cte1,cte2 WHERE cte1.a = t1.a AND t1.a = cte2.a),
+cte4 AS
+(SELECT cte2.a FROM t1,cte2 WHERE cte2.a = t1.a)
+SELECT * FROM cte4 as r;
+
+--error ER_TABLEACCESS_DENIED_ERROR
+WITH
+cte1 AS
+(SELECT a FROM t2),
+cte2 AS
+(SELECT cte1.a FROM t2,cte1 WHERE cte1.a = t2.a),
+cte3 AS
+(SELECT cte2.a FROM t2,cte1,cte2 WHERE cte1.a = t2.a AND t2.a = cte2.a),
+cte4 AS
+(SELECT cte2.a FROM t2,cte2 WHERE cte2.a = t2.a)
+SELECT * FROM cte4 as r;
+
+--disconnect u1
+--connection default
+
+DROP USER 'u1'@'localhost';
+DROP DATABASE db;
+
+USE test;
+
+--echo # End of 10.2 tests
diff --git a/mysql-test/main/cte_recursive.result b/mysql-test/main/cte_recursive.result
index d8ea858ad5a..decb1b47f13 100644
--- a/mysql-test/main/cte_recursive.result
+++ b/mysql-test/main/cte_recursive.result
@@ -818,7 +818,7 @@ where p.id = a.father or p.id = a.mother
select * from ancestors;
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 with recursive ancestors as (select `folks`.`id` AS `id`,`folks`.`name` AS `name`,`folks`.`dob` AS `dob`,`folks`.`father` AS `father`,`folks`.`mother` AS `mother` from `folks` where `folks`.`name` = 'Me' and `folks`.`dob` = '2000-01-01' union select `p`.`id` AS `id`,`p`.`name` AS `name`,`p`.`dob` AS `dob`,`p`.`father` AS `father`,`p`.`mother` AS `mother` from (`folks` `p` join `ancestors` `a`) where `p`.`id` = `a`.`father` or `p`.`id` = `a`.`mother`)select `ancestors`.`id` AS `id`,`ancestors`.`name` AS `name`,`ancestors`.`dob` AS `dob`,`ancestors`.`father` AS `father`,`ancestors`.`mother` AS `mother` from `ancestors` latin1 latin1_swedish_ci
+v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS with recursive ancestors as (select `test`.`folks`.`id` AS `id`,`test`.`folks`.`name` AS `name`,`test`.`folks`.`dob` AS `dob`,`test`.`folks`.`father` AS `father`,`test`.`folks`.`mother` AS `mother` from `test`.`folks` where `test`.`folks`.`name` = 'Me' and `test`.`folks`.`dob` = '2000-01-01' union select `p`.`id` AS `id`,`p`.`name` AS `name`,`p`.`dob` AS `dob`,`p`.`father` AS `father`,`p`.`mother` AS `mother` from (`test`.`folks` `p` join `ancestors` `a`) where `p`.`id` = `a`.`father` or `p`.`id` = `a`.`mother`)select `ancestors`.`id` AS `id`,`ancestors`.`name` AS `name`,`ancestors`.`dob` AS `dob`,`ancestors`.`father` AS `father`,`ancestors`.`mother` AS `mother` from `ancestors` latin1 latin1_swedish_ci
select * from v1;
id name dob father mother
100 Me 2000-01-01 20 30
@@ -849,7 +849,7 @@ where p.id = ma.mother
select * from ancestors;
show create view v2;
View Create View character_set_client collation_connection
-v2 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v2` AS with recursive ancestors as (select `folks`.`id` AS `id`,`folks`.`name` AS `name`,`folks`.`dob` AS `dob`,`folks`.`father` AS `father`,`folks`.`mother` AS `mother` from `folks` where `folks`.`name` = 'Me' union select `p`.`id` AS `id`,`p`.`name` AS `name`,`p`.`dob` AS `dob`,`p`.`father` AS `father`,`p`.`mother` AS `mother` from (`folks` `p` join `ancestors` `fa`) where `p`.`id` = `fa`.`father` union select `p`.`id` AS `id`,`p`.`name` AS `name`,`p`.`dob` AS `dob`,`p`.`father` AS `father`,`p`.`mother` AS `mother` from (`folks` `p` join `ancestors` `ma`) where `p`.`id` = `ma`.`mother`)select `ancestors`.`id` AS `id`,`ancestors`.`name` AS `name`,`ancestors`.`dob` AS `dob`,`ancestors`.`father` AS `father`,`ancestors`.`mother` AS `mother` from `ancestors` latin1 latin1_swedish_ci
+v2 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v2` AS with recursive ancestors as (select `test`.`folks`.`id` AS `id`,`test`.`folks`.`name` AS `name`,`test`.`folks`.`dob` AS `dob`,`test`.`folks`.`father` AS `father`,`test`.`folks`.`mother` AS `mother` from `test`.`folks` where `test`.`folks`.`name` = 'Me' union select `p`.`id` AS `id`,`p`.`name` AS `name`,`p`.`dob` AS `dob`,`p`.`father` AS `father`,`p`.`mother` AS `mother` from (`test`.`folks` `p` join `ancestors` `fa`) where `p`.`id` = `fa`.`father` union select `p`.`id` AS `id`,`p`.`name` AS `name`,`p`.`dob` AS `dob`,`p`.`father` AS `father`,`p`.`mother` AS `mother` from (`test`.`folks` `p` join `ancestors` `ma`) where `p`.`id` = `ma`.`mother`)select `ancestors`.`id` AS `id`,`ancestors`.`name` AS `name`,`ancestors`.`dob` AS `dob`,`ancestors`.`father` AS `father`,`ancestors`.`mother` AS `mother` from `ancestors` latin1 latin1_swedish_ci
select * from v2;
id name dob father mother
100 Me 2000-01-01 20 30
@@ -4232,6 +4232,273 @@ a b c
deallocate prepare stmt;
drop table t1;
#
+# MDEV-24019: query with recursive CTE when no default database is set
+#
+drop database test;
+with recursive a as
+(select 1 from dual union select * from a as r)
+select * from a;
+1
+1
+create database db1;
+create table db1.t1 (a int);
+insert into db1.t1 values (3), (7), (1);
+with recursive cte as
+(select * from db1.t1 union select * from (select * from cte) as t)
+select * from cte;
+a
+3
+7
+1
+explain with recursive cte as
+(select * from db1.t1 union select * from (select * from cte) as t)
+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 RECURSIVE UNION <derived2> ALL NULL NULL NULL NULL 3
+NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL
+prepare stmt from "with recursive cte as
+(select * from db1.t1 union select * from (select * from cte) as t)
+select * from cte";
+execute stmt;
+a
+3
+7
+1
+execute stmt;
+a
+3
+7
+1
+deallocate prepare stmt;
+drop database db1;
+create database test;
+use test;
+#
+# MDEV-23406: query with mutually recursive CTEs when big_tables=1
+#
+set @save_big_tables=@@big_tables;
+set big_tables=1;
+Warnings:
+Warning 1287 '@@big_tables' is deprecated and will be removed in a future release
+create table folks(id int, name char(32), dob date, father int, mother int);
+insert into folks values
+(100, 'Me', '2000-01-01', 20, 30),
+(20, 'Dad', '1970-02-02', 10, 9),
+(30, 'Mom', '1975-03-03', 8, 7),
+(10, 'Grandpa Bill', '1940-04-05', null, null),
+(9, 'Grandma Ann', '1941-10-15', null, null),
+(25, 'Uncle Jim', '1968-11-18', 8, 7),
+(98, 'Sister Amy', '2001-06-20', 20, 30),
+(7, 'Grandma Sally', '1943-08-23', null, 6),
+(8, 'Grandpa Ben', '1940-10-21', null, null),
+(6, 'Grandgrandma Martha', '1923-05-17', null, null),
+(67, 'Cousin Eddie', '1992-02-28', 25, 27),
+(27, 'Auntie Melinda', '1971-03-29', null, null);
+with recursive
+ancestor_couples(h_id, h_name, h_dob, h_father, h_mother,
+w_id, w_name, w_dob, w_father, w_mother)
+as
+(
+select h.*, w.*
+from folks h, folks w, coupled_ancestors a
+where a.father = h.id AND a.mother = w.id
+union
+select h.*, w.*
+from folks v, folks h, folks w
+where v.name = 'Me' and
+(v.father = h.id AND v.mother= w.id)
+),
+coupled_ancestors (id, name, dob, father, mother)
+as
+(
+select h_id, h_name, h_dob, h_father, h_mother
+from ancestor_couples
+union
+select w_id, w_name, w_dob, w_father, w_mother
+from ancestor_couples
+)
+select h_name, h_dob, w_name, w_dob
+from ancestor_couples;
+h_name h_dob w_name w_dob
+Dad 1970-02-02 Mom 1975-03-03
+Grandpa Bill 1940-04-05 Grandma Ann 1941-10-15
+Grandpa Ben 1940-10-21 Grandma Sally 1943-08-23
+explain with recursive
+ancestor_couples(h_id, h_name, h_dob, h_father, h_mother,
+w_id, w_name, w_dob, w_father, w_mother)
+as
+(
+select h.*, w.*
+from folks h, folks w, coupled_ancestors a
+where a.father = h.id AND a.mother = w.id
+union
+select h.*, w.*
+from folks v, folks h, folks w
+where v.name = 'Me' and
+(v.father = h.id AND v.mother= w.id)
+),
+coupled_ancestors (id, name, dob, father, mother)
+as
+(
+select h_id, h_name, h_dob, h_father, h_mother
+from ancestor_couples
+union
+select w_id, w_name, w_dob, w_father, w_mother
+from ancestor_couples
+)
+select h_name, h_dob, w_name, w_dob
+from ancestor_couples;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY <derived3> ALL NULL NULL NULL NULL 1728
+4 DERIVED <derived3> ALL NULL NULL NULL NULL 1728
+5 RECURSIVE UNION <derived3> ALL NULL NULL NULL NULL 1728
+NULL UNION RESULT <union4,5> ALL NULL NULL NULL NULL NULL
+3 DERIVED v ALL NULL NULL NULL NULL 12 Using where
+3 DERIVED h ALL NULL NULL NULL NULL 12 Using where; Using join buffer (flat, BNL join)
+3 DERIVED w ALL NULL NULL NULL NULL 12 Using where; Using join buffer (incremental, BNL join)
+2 RECURSIVE UNION <derived4> ALL NULL NULL NULL NULL 2
+2 RECURSIVE UNION h ALL NULL NULL NULL NULL 12 Using where; Using join buffer (flat, BNL join)
+2 RECURSIVE UNION w ALL NULL NULL NULL NULL 12 Using where; Using join buffer (incremental, BNL join)
+NULL UNION RESULT <union3,2> ALL NULL NULL NULL NULL NULL
+prepare stmt from "with recursive
+ancestor_couples(h_id, h_name, h_dob, h_father, h_mother,
+w_id, w_name, w_dob, w_father, w_mother)
+as
+(
+select h.*, w.*
+from folks h, folks w, coupled_ancestors a
+where a.father = h.id AND a.mother = w.id
+union
+select h.*, w.*
+from folks v, folks h, folks w
+where v.name = 'Me' and
+(v.father = h.id AND v.mother= w.id)
+),
+coupled_ancestors (id, name, dob, father, mother)
+as
+(
+select h_id, h_name, h_dob, h_father, h_mother
+from ancestor_couples
+union
+select w_id, w_name, w_dob, w_father, w_mother
+from ancestor_couples
+)
+select h_name, h_dob, w_name, w_dob
+from ancestor_couples";
+execute stmt;
+h_name h_dob w_name w_dob
+Dad 1970-02-02 Mom 1975-03-03
+Grandpa Bill 1940-04-05 Grandma Ann 1941-10-15
+Grandpa Ben 1940-10-21 Grandma Sally 1943-08-23
+execute stmt;
+h_name h_dob w_name w_dob
+Dad 1970-02-02 Mom 1975-03-03
+Grandpa Bill 1940-04-05 Grandma Ann 1941-10-15
+Grandpa Ben 1940-10-21 Grandma Sally 1943-08-23
+deallocate prepare stmt;
+with recursive
+ancestor_couples(h_id, h_name, h_dob, h_father, h_mother,
+w_id, w_name, w_dob, w_father, w_mother)
+as
+(
+select h.*, w.*
+from folks h, folks w, coupled_ancestors a
+where a.father = h.id AND a.mother = w.id
+),
+coupled_ancestors (id, name, dob, father, mother)
+as
+(
+select *
+from folks
+where name = 'Me'
+ union all
+select h_id, h_name, h_dob, h_father, h_mother
+from ancestor_couples
+union all
+select w_id, w_name, w_dob, w_father, w_mother
+from ancestor_couples
+)
+select h_name, h_dob, w_name, w_dob
+from ancestor_couples;
+h_name h_dob w_name w_dob
+Dad 1970-02-02 Mom 1975-03-03
+Grandpa Bill 1940-04-05 Grandma Ann 1941-10-15
+Grandpa Ben 1940-10-21 Grandma Sally 1943-08-23
+explain with recursive
+ancestor_couples(h_id, h_name, h_dob, h_father, h_mother,
+w_id, w_name, w_dob, w_father, w_mother)
+as
+(
+select h.*, w.*
+from folks h, folks w, coupled_ancestors a
+where a.father = h.id AND a.mother = w.id
+),
+coupled_ancestors (id, name, dob, father, mother)
+as
+(
+select *
+from folks
+where name = 'Me'
+ union all
+select h_id, h_name, h_dob, h_father, h_mother
+from ancestor_couples
+union all
+select w_id, w_name, w_dob, w_father, w_mother
+from ancestor_couples
+)
+select h_name, h_dob, w_name, w_dob
+from ancestor_couples;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY <derived2> ALL NULL NULL NULL NULL 2
+3 DERIVED folks ALL NULL NULL NULL NULL 12 Using where
+4 RECURSIVE UNION <derived2> ALL NULL NULL NULL NULL 2
+5 RECURSIVE UNION <derived2> ALL NULL NULL NULL NULL 2
+NULL UNION RESULT <union3,4,5> ALL NULL NULL NULL NULL NULL
+2 DERIVED h ALL NULL NULL NULL NULL 12
+2 DERIVED w ALL NULL NULL NULL NULL 12 Using join buffer (flat, BNL join)
+2 DERIVED <derived3> ALL NULL NULL NULL NULL 12 Using where; Using join buffer (incremental, BNL join)
+prepare stmt from "with recursive
+ancestor_couples(h_id, h_name, h_dob, h_father, h_mother,
+w_id, w_name, w_dob, w_father, w_mother)
+as
+(
+select h.*, w.*
+from folks h, folks w, coupled_ancestors a
+where a.father = h.id AND a.mother = w.id
+),
+coupled_ancestors (id, name, dob, father, mother)
+as
+(
+select *
+from folks
+where name = 'Me'
+ union all
+select h_id, h_name, h_dob, h_father, h_mother
+from ancestor_couples
+union all
+select w_id, w_name, w_dob, w_father, w_mother
+from ancestor_couples
+)
+select h_name, h_dob, w_name, w_dob
+from ancestor_couples";
+execute stmt;
+h_name h_dob w_name w_dob
+Dad 1970-02-02 Mom 1975-03-03
+Grandpa Bill 1940-04-05 Grandma Ann 1941-10-15
+Grandpa Ben 1940-10-21 Grandma Sally 1943-08-23
+execute stmt;
+h_name h_dob w_name w_dob
+Dad 1970-02-02 Mom 1975-03-03
+Grandpa Bill 1940-04-05 Grandma Ann 1941-10-15
+Grandpa Ben 1940-10-21 Grandma Sally 1943-08-23
+deallocate prepare stmt;
+drop table folks;
+set big_tables=@save_big_tables;
+Warnings:
+Warning 1287 '@@big_tables' is deprecated and will be removed in a future release
+#
# End of 10.2 tests
#
#
diff --git a/mysql-test/main/cte_recursive.test b/mysql-test/main/cte_recursive.test
index 0e29824e97e..125e22ebfa6 100644
--- a/mysql-test/main/cte_recursive.test
+++ b/mysql-test/main/cte_recursive.test
@@ -2726,6 +2726,135 @@ deallocate prepare stmt;
drop table t1;
--echo #
+--echo # MDEV-24019: query with recursive CTE when no default database is set
+--echo #
+
+drop database test;
+
+let $q=
+with recursive a as
+ (select 1 from dual union select * from a as r)
+select * from a;
+
+eval $q;
+
+create database db1;
+create table db1.t1 (a int);
+insert into db1.t1 values (3), (7), (1);
+
+let $q=
+with recursive cte as
+ (select * from db1.t1 union select * from (select * from cte) as t)
+select * from cte;
+
+eval $q;
+eval explain $q;
+
+eval prepare stmt from "$q";
+execute stmt;
+execute stmt;
+deallocate prepare stmt;
+
+drop database db1;
+
+create database test;
+use test;
+
+--echo #
+--echo # MDEV-23406: query with mutually recursive CTEs when big_tables=1
+--echo #
+
+set @save_big_tables=@@big_tables;
+set big_tables=1;
+
+create table folks(id int, name char(32), dob date, father int, mother int);
+
+insert into folks values
+(100, 'Me', '2000-01-01', 20, 30),
+(20, 'Dad', '1970-02-02', 10, 9),
+(30, 'Mom', '1975-03-03', 8, 7),
+(10, 'Grandpa Bill', '1940-04-05', null, null),
+(9, 'Grandma Ann', '1941-10-15', null, null),
+(25, 'Uncle Jim', '1968-11-18', 8, 7),
+(98, 'Sister Amy', '2001-06-20', 20, 30),
+(7, 'Grandma Sally', '1943-08-23', null, 6),
+(8, 'Grandpa Ben', '1940-10-21', null, null),
+(6, 'Grandgrandma Martha', '1923-05-17', null, null),
+(67, 'Cousin Eddie', '1992-02-28', 25, 27),
+(27, 'Auntie Melinda', '1971-03-29', null, null);
+
+let q=
+with recursive
+ancestor_couples(h_id, h_name, h_dob, h_father, h_mother,
+ w_id, w_name, w_dob, w_father, w_mother)
+as
+(
+ select h.*, w.*
+ from folks h, folks w, coupled_ancestors a
+ where a.father = h.id AND a.mother = w.id
+ union
+ select h.*, w.*
+ from folks v, folks h, folks w
+ where v.name = 'Me' and
+ (v.father = h.id AND v.mother= w.id)
+),
+coupled_ancestors (id, name, dob, father, mother)
+as
+(
+ select h_id, h_name, h_dob, h_father, h_mother
+ from ancestor_couples
+ union
+ select w_id, w_name, w_dob, w_father, w_mother
+ from ancestor_couples
+)
+select h_name, h_dob, w_name, w_dob
+ from ancestor_couples;
+
+eval $q;
+eval explain $q;
+eval prepare stmt from "$q";
+execute stmt;
+execute stmt;
+deallocate prepare stmt;
+
+let $q=
+with recursive
+ancestor_couples(h_id, h_name, h_dob, h_father, h_mother,
+ w_id, w_name, w_dob, w_father, w_mother)
+as
+(
+ select h.*, w.*
+ from folks h, folks w, coupled_ancestors a
+ where a.father = h.id AND a.mother = w.id
+),
+coupled_ancestors (id, name, dob, father, mother)
+as
+(
+ select *
+ from folks
+ where name = 'Me'
+ union all
+ select h_id, h_name, h_dob, h_father, h_mother
+ from ancestor_couples
+ union all
+ select w_id, w_name, w_dob, w_father, w_mother
+ from ancestor_couples
+)
+select h_name, h_dob, w_name, w_dob
+ from ancestor_couples;
+
+eval $q;
+eval explain $q;
+eval prepare stmt from "$q";
+execute stmt;
+execute stmt;
+deallocate prepare stmt;
+
+drop table folks;
+
+set big_tables=@save_big_tables;
+
+--echo #
--echo # End of 10.2 tests
--echo #
diff --git a/mysql-test/main/ctype_utf16.result b/mysql-test/main/ctype_utf16.result
index 653f18e0ce6..36dedc87321 100644
--- a/mysql-test/main/ctype_utf16.result
+++ b/mysql-test/main/ctype_utf16.result
@@ -1492,6 +1492,8 @@ ab
AE
AE
SET max_sort_length=8;
+Warnings:
+Warning 1292 Truncated incorrect max_sort_length value: '8'
SELECT * FROM t1 ORDER BY s1;
s1
ab
diff --git a/mysql-test/main/ctype_utf16le.result b/mysql-test/main/ctype_utf16le.result
index 38d61bd4f1b..c43106aa042 100644
--- a/mysql-test/main/ctype_utf16le.result
+++ b/mysql-test/main/ctype_utf16le.result
@@ -1765,6 +1765,8 @@ ab
AE
AE
SET max_sort_length=8;
+Warnings:
+Warning 1292 Truncated incorrect max_sort_length value: '8'
SELECT * FROM t1 ORDER BY s1;
s1
ab
diff --git a/mysql-test/main/ctype_utf32.result b/mysql-test/main/ctype_utf32.result
index 5cbc1b66e40..563c8aa6bad 100644
--- a/mysql-test/main/ctype_utf32.result
+++ b/mysql-test/main/ctype_utf32.result
@@ -1505,6 +1505,8 @@ ab
AE
AE
SET max_sort_length=8;
+Warnings:
+Warning 1292 Truncated incorrect max_sort_length value: '8'
SELECT * FROM t1 ORDER BY s1;
s1
ab
diff --git a/mysql-test/main/ctype_utf8.result b/mysql-test/main/ctype_utf8.result
index f9d99d7f246..266292edab6 100644
--- a/mysql-test/main/ctype_utf8.result
+++ b/mysql-test/main/ctype_utf8.result
@@ -6760,9 +6760,11 @@ DFFFDFFF9CFF9DFF9EFF
# Checking strnxfrm() with odd length
#
set max_sort_length=9;
+Warnings:
+Warning 1292 Truncated incorrect max_sort_length value: '9'
select @@max_sort_length;
@@max_sort_length
-9
+64
create table t1 (a varchar(128) character set utf8 collate utf8_general_ci);
insert into t1 values ('a'),('b'),('c');
select * from t1 order by a;
diff --git a/mysql-test/main/ctype_utf8mb4.result b/mysql-test/main/ctype_utf8mb4.result
index 20842b37abb..dfb5c7049c6 100644
--- a/mysql-test/main/ctype_utf8mb4.result
+++ b/mysql-test/main/ctype_utf8mb4.result
@@ -2371,10 +2371,10 @@ drop table t1;
#
# Check strnxfrm() with odd length
#
-set max_sort_length=9;
+set max_sort_length=65;
select @@max_sort_length;
@@max_sort_length
-9
+65
create table t1 (a varchar(128) character set utf8mb4 collate utf8mb4_general_ci);
insert into t1 values ('a'),('b'),('c');
select * from t1 order by a;
diff --git a/mysql-test/main/ctype_utf8mb4.test b/mysql-test/main/ctype_utf8mb4.test
index 532729dafde..8fbbee872ce 100644
--- a/mysql-test/main/ctype_utf8mb4.test
+++ b/mysql-test/main/ctype_utf8mb4.test
@@ -1520,7 +1520,7 @@ drop table t1;
--echo #
--echo # Check strnxfrm() with odd length
--echo #
-set max_sort_length=9;
+set max_sort_length=65;
select @@max_sort_length;
create table t1 (a varchar(128) character set utf8mb4 collate utf8mb4_general_ci);
insert into t1 values ('a'),('b'),('c');
diff --git a/mysql-test/main/ctype_utf8mb4_heap.result b/mysql-test/main/ctype_utf8mb4_heap.result
index 4aef2d8cb66..9eb7d48370f 100644
--- a/mysql-test/main/ctype_utf8mb4_heap.result
+++ b/mysql-test/main/ctype_utf8mb4_heap.result
@@ -2203,10 +2203,10 @@ drop table t1;
#
# Check strnxfrm() with odd length
#
-set max_sort_length=9;
+set max_sort_length=65;
select @@max_sort_length;
@@max_sort_length
-9
+65
create table t1 (a varchar(128) character set utf8mb4 collate utf8mb4_general_ci) engine heap;
insert into t1 values ('a'),('b'),('c');
select * from t1 order by a;
diff --git a/mysql-test/main/ctype_utf8mb4_innodb.result b/mysql-test/main/ctype_utf8mb4_innodb.result
index 3c7d0ba2fb7..fc2a368f3f0 100644
--- a/mysql-test/main/ctype_utf8mb4_innodb.result
+++ b/mysql-test/main/ctype_utf8mb4_innodb.result
@@ -2329,10 +2329,10 @@ drop table t1;
#
# Check strnxfrm() with odd length
#
-set max_sort_length=9;
+set max_sort_length=65;
select @@max_sort_length;
@@max_sort_length
-9
+65
create table t1 (a varchar(128) character set utf8mb4 collate utf8mb4_general_ci) engine InnoDB;
insert into t1 values ('a'),('b'),('c');
select * from t1 order by a;
diff --git a/mysql-test/main/ctype_utf8mb4_myisam.result b/mysql-test/main/ctype_utf8mb4_myisam.result
index fd8d7adf3a0..8940d10fc78 100644
--- a/mysql-test/main/ctype_utf8mb4_myisam.result
+++ b/mysql-test/main/ctype_utf8mb4_myisam.result
@@ -2336,10 +2336,10 @@ drop table t1;
#
# Check strnxfrm() with odd length
#
-set max_sort_length=9;
+set max_sort_length=65;
select @@max_sort_length;
@@max_sort_length
-9
+65
create table t1 (a varchar(128) character set utf8mb4 collate utf8mb4_general_ci) engine MyISAM;
insert into t1 values ('a'),('b'),('c');
select * from t1 order by a;
diff --git a/mysql-test/main/drop_combinations.result b/mysql-test/main/drop_combinations.result
index 140665efd19..a939bcd2b51 100644
--- a/mysql-test/main/drop_combinations.result
+++ b/mysql-test/main/drop_combinations.result
@@ -599,7 +599,8 @@ Note 4091 Unknown SEQUENCE: 'test.s4,test.s3'
# Generating test tables
drop temporary sequence if exists s1,t1,t2,s2;
Warnings:
-Note 4091 Unknown SEQUENCE: 'test.t1,test.t2'
+Note 4090 'test.t1' is not a SEQUENCE
+Note 4090 'test.t2' is not a SEQUENCE
# Generating test tables
drop temporary sequence if exists s1,v1,v2,s2;
Warnings:
@@ -607,18 +608,24 @@ Note 4091 Unknown SEQUENCE: 'test.v1,test.v2'
# Generating test tables
drop temporary sequence if exists v1,t1,t2,v2,s2;
Warnings:
-Note 4091 Unknown SEQUENCE: 'test.v1,test.t1,test.t2,test.v2'
+Note 4090 'test.t1' is not a SEQUENCE
+Note 4090 'test.t2' is not a SEQUENCE
+Note 4091 Unknown SEQUENCE: 'test.v1,test.v2'
# Generating test tables
drop temporary sequence if exists s1,t1,v1,t3,s4;
Warnings:
-Note 4091 Unknown SEQUENCE: 'test.t1,test.v1,test.t3,test.s4'
+Note 4090 'test.t1' is not a SEQUENCE
+Note 4091 Unknown SEQUENCE: 'test.v1,test.t3,test.s4'
drop temporary sequence if exists t2,v2,s2,s1;
Warnings:
-Note 4091 Unknown SEQUENCE: 'test.t2,test.v2,test.s1'
+Note 4090 'test.t2' is not a SEQUENCE
+Note 4091 Unknown SEQUENCE: 'test.v2,test.s1'
# Generating test tables
drop temporary sequence if exists t1,v1,t2,v2;
Warnings:
-Note 4091 Unknown SEQUENCE: 'test.t1,test.v1,test.t2,test.v2'
+Note 4090 'test.t1' is not a SEQUENCE
+Note 4090 'test.t2' is not a SEQUENCE
+Note 4091 Unknown SEQUENCE: 'test.v1,test.v2'
# Generating test tables
drop temporary table t1,t2;
drop temporary sequence s1,s2;
diff --git a/mysql-test/main/func_gconcat.result b/mysql-test/main/func_gconcat.result
index 01bf45056c3..328bbc953ab 100644
--- a/mysql-test/main/func_gconcat.result
+++ b/mysql-test/main/func_gconcat.result
@@ -1280,6 +1280,18 @@ Name_exp_1
DROP VIEW v1;
DROP TABLE t1;
#
+# MDEV-4677 GROUP_CONCAT not showing any output with group_concat_max_len >= 4Gb
+#
+set group_concat_max_len=1024*1024*1024*4;
+Warnings:
+Warning 1292 Truncated incorrect group_concat_max_len value: '4294967296'
+create table t1 (i int, j int);
+insert into t1 values (1,1),(1,2);
+select i, group_concat(j) from t1 group by i;
+i group_concat(j)
+1 1,2
+drop table t1;
+#
# End of 10.2 tests
#
#
diff --git a/mysql-test/main/func_gconcat.test b/mysql-test/main/func_gconcat.test
index 2ab856e0edd..3c21aa04ffc 100644
--- a/mysql-test/main/func_gconcat.test
+++ b/mysql-test/main/func_gconcat.test
@@ -939,6 +939,16 @@ DROP VIEW v1;
DROP TABLE t1;
+
+--echo #
+--echo # MDEV-4677 GROUP_CONCAT not showing any output with group_concat_max_len >= 4Gb
+--echo #
+set group_concat_max_len=1024*1024*1024*4;
+create table t1 (i int, j int);
+insert into t1 values (1,1),(1,2);
+select i, group_concat(j) from t1 group by i;
+drop table t1;
+
--echo #
--echo # End of 10.2 tests
--echo #
diff --git a/mysql-test/main/func_like.result b/mysql-test/main/func_like.result
index 0ec6724d7c0..200cb20c4de 100644
--- a/mysql-test/main/func_like.result
+++ b/mysql-test/main/func_like.result
@@ -289,6 +289,24 @@ a b c d
3 f_ 1 0 1
3 f\_ 0 1 0
drop table t1;
+create table t1 (f int);
+insert t1 values (1),(2);
+select 1 from (select distinct * from t1) as x where f < (select 1 like 2 escape (3=1));
+1
+drop table t1;
+create table t1(f1 int);
+insert into t1 values(1);
+update (select 1 like 2 escape (1 in (select 1 from t1))) x, t1 as d set d.f1 = 1;
+ERROR HY000: Incorrect arguments to ESCAPE
+select * from (select 1 like 2 escape (1 in (select 1 from t1))) x;
+1 like 2 escape (1 in (select 1 from t1))
+0
+drop table t1;
+create table t1 (f int);
+insert t1 values (1),(2);
+create view v1 as select * from t1 where (1 like 2 escape (3 in (('h', 'b') in (select 'k', 'k' union select 'g', 'j'))) and f >= 0);
+drop view v1;
+drop table t1;
#
# MDEV-17359 - Extend expression supported by like (| & << >> || + - * / DIV MOD ^ )
#
diff --git a/mysql-test/main/func_like.test b/mysql-test/main/func_like.test
index cb50fb91879..ef13d46c268 100644
--- a/mysql-test/main/func_like.test
+++ b/mysql-test/main/func_like.test
@@ -187,7 +187,7 @@ DROP TABLE t1;
--echo #
#
-# Item_func_line::print()
+# Item_func_like::print()
#
create view v1 as select 'foo!' like 'foo!!', 'foo!' like 'foo!!' escape '!';
show create view v1;
@@ -208,6 +208,33 @@ set sql_mode=default;
select * from t1;
drop table t1;
+#
+# Item_func_like::fix_fields()
+#
+create table t1 (f int);
+insert t1 values (1),(2);
+select 1 from (select distinct * from t1) as x where f < (select 1 like 2 escape (3=1));
+drop table t1;
+
+#
+# Item_func_like::fix_fields, ESCAPE, const_item()
+#
+create table t1(f1 int);
+insert into t1 values(1);
+--error ER_WRONG_ARGUMENTS
+update (select 1 like 2 escape (1 in (select 1 from t1))) x, t1 as d set d.f1 = 1;
+select * from (select 1 like 2 escape (1 in (select 1 from t1))) x;
+drop table t1;
+
+#
+# Item_func_like::walk
+#
+create table t1 (f int);
+insert t1 values (1),(2);
+create view v1 as select * from t1 where (1 like 2 escape (3 in (('h', 'b') in (select 'k', 'k' union select 'g', 'j'))) and f >= 0);
+drop view v1;
+drop table t1;
+
--echo #
--echo # MDEV-17359 - Extend expression supported by like (| & << >> || + - * / DIV MOD ^ )
--echo #
diff --git a/mysql-test/main/information_schema.result b/mysql-test/main/information_schema.result
index 627fb9e4569..004167f9c8f 100644
--- a/mysql-test/main/information_schema.result
+++ b/mysql-test/main/information_schema.result
@@ -863,6 +863,7 @@ where data_type = 'longtext' and table_schema != 'performance_schema'
order by binary table_name, ordinal_position;
table_schema table_name column_name
information_schema ALL_PLUGINS PLUGIN_DESCRIPTION
+information_schema CHECK_CONSTRAINTS CHECK_CLAUSE
information_schema COLUMNS COLUMN_DEFAULT
information_schema COLUMNS COLUMN_TYPE
information_schema COLUMNS GENERATION_EXPRESSION
@@ -2222,17 +2223,10 @@ SCHEMA_NAME
# MDEV-14836: Assertion `m_status == DA_ERROR' failed in
# Diagnostics_area::sql_errno upon query from I_S with LIMIT ROWS EXAMINED
#
-SELECT * FROM seq_1_to_100 LIMIT ROWS EXAMINED 10;
-seq
-1
-2
-3
-4
-5
-6
-7
+SELECT * FROM INFORMATION_SCHEMA.`COLUMNS` LIMIT ROWS EXAMINED 10;
+TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME ORDINAL_POSITION COLUMN_DEFAULT IS_NULLABLE DATA_TYPE CHARACTER_MAXIMUM_LENGTH CHARACTER_OCTET_LENGTH NUMERIC_PRECISION NUMERIC_SCALE DATETIME_PRECISION CHARACTER_SET_NAME COLLATION_NAME COLUMN_TYPE COLUMN_KEY EXTRA PRIVILEGES COLUMN_COMMENT IS_GENERATED GENERATION_EXPRESSION
Warnings:
-Warning 1931 Query execution was interrupted. The query examined at least 11 rows, which exceeds LIMIT ROWS EXAMINED (10). The query result may be incomplete
+Warning 1931 Query execution was interrupted. The query examined at least ### rows, which exceeds LIMIT ROWS EXAMINED (10). The query result may be incomplete
#
# End of 10.2 Test
#
diff --git a/mysql-test/main/information_schema.test b/mysql-test/main/information_schema.test
index ca49091ef76..e0ab022cd61 100644
--- a/mysql-test/main/information_schema.test
+++ b/mysql-test/main/information_schema.test
@@ -1928,7 +1928,8 @@ SELECT SCHEMA_NAME from information_schema.schemata where schema_name=REPEAT('a'
--echo # Diagnostics_area::sql_errno upon query from I_S with LIMIT ROWS EXAMINED
--echo #
-SELECT * FROM seq_1_to_100 LIMIT ROWS EXAMINED 10;
+replace_regex /at least \d+ rows/at least ### rows/;
+SELECT * FROM INFORMATION_SCHEMA.`COLUMNS` LIMIT ROWS EXAMINED 10;
--echo #
--echo # End of 10.2 Test
diff --git a/mysql-test/main/lock_view.result b/mysql-test/main/lock_view.result
index 4d375bace42..364c2cddf60 100644
--- a/mysql-test/main/lock_view.result
+++ b/mysql-test/main/lock_view.result
@@ -229,3 +229,37 @@ drop user definer@localhost;
drop database mysqltest1;
drop database mysqltest2;
drop database mysqltest3;
+#
+# MDEV-24331 mysqldump fails with "Got error: 1356" if the database contains a view with a subquery
+#
+create user u1@localhost;
+grant all privileges on test.* to u1@localhost;
+connect con1,localhost,u1;
+use test;
+create table t1 (id int not null);
+create view v1 as select * from (select * from t1) dt;
+lock table v1 read;
+disconnect con1;
+connection default;
+SET @saved_cs_client = @@character_set_client;
+SET character_set_client = utf8;
+/*!50001 CREATE TABLE `v1` (
+ `id` tinyint NOT NULL
+) ENGINE=MyISAM */;
+SET character_set_client = @saved_cs_client;
+/*!50001 DROP TABLE IF EXISTS `v1`*/;
+/*!50001 SET @saved_cs_client = @@character_set_client */;
+/*!50001 SET @saved_cs_results = @@character_set_results */;
+/*!50001 SET @saved_col_connection = @@collation_connection */;
+/*!50001 SET character_set_client = latin1 */;
+/*!50001 SET character_set_results = latin1 */;
+/*!50001 SET collation_connection = latin1_swedish_ci */;
+/*!50001 CREATE ALGORITHM=UNDEFINED */
+/*!50013 DEFINER=`u1`@`localhost` SQL SECURITY DEFINER */
+/*!50001 VIEW `v1` AS select `dt`.`id` AS `id` from (select `test`.`t1`.`id` AS `id` from `test`.`t1`) `dt` */;
+/*!50001 SET character_set_client = @saved_cs_client */;
+/*!50001 SET character_set_results = @saved_cs_results */;
+/*!50001 SET collation_connection = @saved_col_connection */;
+drop view v1;
+drop table t1;
+drop user u1@localhost;
diff --git a/mysql-test/main/lock_view.test b/mysql-test/main/lock_view.test
index 4b1adac5be1..abb8d317946 100644
--- a/mysql-test/main/lock_view.test
+++ b/mysql-test/main/lock_view.test
@@ -75,3 +75,20 @@ drop user definer@localhost;
drop database mysqltest1;
drop database mysqltest2;
drop database mysqltest3;
+
+--echo #
+--echo # MDEV-24331 mysqldump fails with "Got error: 1356" if the database contains a view with a subquery
+--echo #
+create user u1@localhost;
+grant all privileges on test.* to u1@localhost;
+connect con1,localhost,u1;
+use test;
+create table t1 (id int not null);
+create view v1 as select * from (select * from t1) dt;
+lock table v1 read;
+disconnect con1;
+connection default;
+exec $MYSQL_DUMP test v1 -uu1 --compact;
+drop view v1;
+drop table t1;
+drop user u1@localhost;
diff --git a/mysql-test/main/mysql_upgrade.result b/mysql-test/main/mysql_upgrade.result
index 539fe19f072..f7deb74dc7f 100644
--- a/mysql-test/main/mysql_upgrade.result
+++ b/mysql-test/main/mysql_upgrade.result
@@ -484,8 +484,6 @@ even_longer_user_name_number_3_to_test_the_grantor_and_definer_field_length@loca
DROP USER very_long_user_name_number_1, very_long_user_name_number_2, even_longer_user_name_number_3_to_test_the_grantor_and_definer_field_length@localhost;
DROP PROCEDURE test.pr;
use test;
-call mtr.add_suppression('Column last_update in table `mysql`.`innodb_table_stats` is INT NOT NULL but should be');
-alter table mysql.innodb_table_stats modify last_update int not null;
create table extralongname_extralongname_extralongname_extralongname_ext (
id int(10) unsigned not null,
created_date date not null,
diff --git a/mysql-test/main/mysql_upgrade.test b/mysql-test/main/mysql_upgrade.test
index a6345af937d..0200f77797d 100644
--- a/mysql-test/main/mysql_upgrade.test
+++ b/mysql-test/main/mysql_upgrade.test
@@ -172,8 +172,6 @@ DROP PROCEDURE test.pr;
# MDEV-13274 mysql_upgrade fails if dbname+tablename+partioname > 64 chars
#
use test;
-call mtr.add_suppression('Column last_update in table `mysql`.`innodb_table_stats` is INT NOT NULL but should be');
-alter table mysql.innodb_table_stats modify last_update int not null;
create table extralongname_extralongname_extralongname_extralongname_ext (
id int(10) unsigned not null,
diff --git a/mysql-test/main/order_by.result b/mysql-test/main/order_by.result
index b1e510c5377..81127121cde 100644
--- a/mysql-test/main/order_by.result
+++ b/mysql-test/main/order_by.result
@@ -845,18 +845,20 @@ col2 col col
2 2 2
1 3 3
drop table t1, t2;
-create table t1 (a char(25));
+create table t1 (a char(70));
insert into t1 set a = repeat('x', 20);
-insert into t1 set a = concat(repeat('x', 19), 'z');
-insert into t1 set a = concat(repeat('x', 19), 'ab');
-insert into t1 set a = concat(repeat('x', 19), 'aa');
+insert into t1 set a = concat(repeat('x', 63), 'z');
+insert into t1 set a = concat(repeat('x', 63), 'ab');
+insert into t1 set a = concat(repeat('x', 63), 'aa');
set max_sort_length=20;
+Warnings:
+Warning 1292 Truncated incorrect max_sort_length value: '20'
select a from t1 order by a;
a
-xxxxxxxxxxxxxxxxxxxab
-xxxxxxxxxxxxxxxxxxxaa
xxxxxxxxxxxxxxxxxxxx
-xxxxxxxxxxxxxxxxxxxz
+xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxab
+xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxaa
+xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxz
drop table t1;
create table t1 (
`sid` decimal(8,0) default null,
@@ -3317,6 +3319,8 @@ drop table t1;
SET @save_sort_buffer_size= @@sort_buffer_size;
SET @save_max_sort_length= @@max_sort_length;
SET max_sort_length=8;
+Warnings:
+Warning 1292 Truncated incorrect max_sort_length value: '8'
SET sort_buffer_size=1024;
CREATE TABLE t1(a INT, b DECIMAL(65), c BLOB);
INSERT INTO t1 SELECT seq, seq, seq from seq_1_to_25;
@@ -3384,6 +3388,8 @@ SET @save_max_sort_length= @@max_sort_length;
SET @save_sort_buffer_size= @@sort_buffer_size;
SET @save_max_length_for_sort_data= @@max_length_for_sort_data;
SET max_sort_length=8;
+Warnings:
+Warning 1292 Truncated incorrect max_sort_length value: '8'
SET sort_buffer_size=1024;
SET max_length_for_sort_data=7000;
CREATE TABLE t1(a VARCHAR(64), b VARCHAR(2048))DEFAULT CHARSET=utf8;
@@ -3520,6 +3526,8 @@ INSERT INTO t1 VALUES (1,1), (2,2), (3,3), (4,4);
SET max_length_for_sort_data= 30;
SET sql_select_limit = 3;
SET max_sort_length=8;
+Warnings:
+Warning 1292 Truncated incorrect max_sort_length value: '8'
SELECT * FROM t1 ORDER BY a+1;
a b
1 1
diff --git a/mysql-test/main/order_by.test b/mysql-test/main/order_by.test
index 9d0eaef5ea4..fbdf193269f 100644
--- a/mysql-test/main/order_by.test
+++ b/mysql-test/main/order_by.test
@@ -533,11 +533,11 @@ drop table t1, t2;
# Bug #5428: a problem with small max_sort_length value
#
-create table t1 (a char(25));
+create table t1 (a char(70));
insert into t1 set a = repeat('x', 20);
-insert into t1 set a = concat(repeat('x', 19), 'z');
-insert into t1 set a = concat(repeat('x', 19), 'ab');
-insert into t1 set a = concat(repeat('x', 19), 'aa');
+insert into t1 set a = concat(repeat('x', 63), 'z');
+insert into t1 set a = concat(repeat('x', 63), 'ab');
+insert into t1 set a = concat(repeat('x', 63), 'aa');
set max_sort_length=20;
select a from t1 order by a;
drop table t1;
diff --git a/mysql-test/main/precedence.result b/mysql-test/main/precedence.result
index d5d4e662a8e..fc6579651b4 100644
--- a/mysql-test/main/precedence.result
+++ b/mysql-test/main/precedence.result
@@ -8016,4 +8016,8 @@ create or replace view v1 as select 1 IS TRUE IS FALSE, 2 IS FALSE IS UNKNOWN, 3
Select view_definition from information_schema.views where table_schema='test' and table_name='v1';
view_definition
select 1 is true is false AS `1 IS TRUE IS FALSE`,/*always not null*/ 1 is null AS `2 IS FALSE IS UNKNOWN`,/*always not null*/ 1 is null AS `3 IS UNKNOWN IS NULL`,/*always not null*/ 1 is null is true AS `4 IS NULL IS TRUE`
+create or replace view v1 as select 2 IS TRUE = 3, 2 IS FALSE = 3, 2 IS UNKNOWN = 3, 2 IS NULL = 3, ISNULL(2) = 1;
+Select view_definition from information_schema.views where table_schema='test' and table_name='v1';
+view_definition
+select 2 is true = 3 AS `2 IS TRUE = 3`,2 is false = 3 AS `2 IS FALSE = 3`,/*always not null*/ 1 is null = 3 AS `2 IS UNKNOWN = 3`,/*always not null*/ 1 is null = 3 AS `2 IS NULL = 3`,/*always not null*/ 1 is null = 1 AS `ISNULL(2) = 1`
drop view v1;
diff --git a/mysql-test/main/precedence.test b/mysql-test/main/precedence.test
index ad367c23603..cd7cee4f911 100644
--- a/mysql-test/main/precedence.test
+++ b/mysql-test/main/precedence.test
@@ -4785,4 +4785,7 @@ Select view_definition from information_schema.views where table_schema='test' a
create or replace view v1 as select 1 IS TRUE IS FALSE, 2 IS FALSE IS UNKNOWN, 3 IS UNKNOWN IS NULL, 4 IS NULL IS TRUE;
Select view_definition from information_schema.views where table_schema='test' and table_name='v1';
+create or replace view v1 as select 2 IS TRUE = 3, 2 IS FALSE = 3, 2 IS UNKNOWN = 3, 2 IS NULL = 3, ISNULL(2) = 1;
+Select view_definition from information_schema.views where table_schema='test' and table_name='v1';
+
drop view v1;
diff --git a/mysql-test/main/query_cache.result b/mysql-test/main/query_cache.result
index 4615c63feb2..fc7ca726c48 100644
--- a/mysql-test/main/query_cache.result
+++ b/mysql-test/main/query_cache.result
@@ -818,33 +818,33 @@ select @@character_set_results;
NULL
set character_set_results=default;
set GLOBAL query_cache_size=1355776;
-create table t1 (id int auto_increment primary key, c char(25));
+create table t1 (id int auto_increment primary key, c char(65));
insert into t1 set c = repeat('x',24);
-insert into t1 set c = concat(repeat('x',24),'x');
-insert into t1 set c = concat(repeat('x',24),'w');
-insert into t1 set c = concat(repeat('x',24),'y');
+insert into t1 set c = concat(repeat('x',64),'x');
+insert into t1 set c = concat(repeat('x',64),'w');
+insert into t1 set c = concat(repeat('x',64),'y');
set max_sort_length=200;
select c from t1 order by c, id;
c
xxxxxxxxxxxxxxxxxxxxxxxx
-xxxxxxxxxxxxxxxxxxxxxxxxw
-xxxxxxxxxxxxxxxxxxxxxxxxx
-xxxxxxxxxxxxxxxxxxxxxxxxy
+xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxw
+xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
+xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxy
reset query cache;
-set max_sort_length=20;
+set max_sort_length=64;
select c from t1 order by c, id;
c
xxxxxxxxxxxxxxxxxxxxxxxx
-xxxxxxxxxxxxxxxxxxxxxxxxx
-xxxxxxxxxxxxxxxxxxxxxxxxw
-xxxxxxxxxxxxxxxxxxxxxxxxy
+xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
+xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxw
+xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxy
set max_sort_length=200;
select c from t1 order by c, id;
c
xxxxxxxxxxxxxxxxxxxxxxxx
-xxxxxxxxxxxxxxxxxxxxxxxxw
-xxxxxxxxxxxxxxxxxxxxxxxxx
-xxxxxxxxxxxxxxxxxxxxxxxxy
+xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxw
+xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
+xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxy
set max_sort_length=default;
select '1' || '3' from t1;
'1' || '3'
diff --git a/mysql-test/main/query_cache.test b/mysql-test/main/query_cache.test
index 118ebbf1703..6e113f0cdb7 100644
--- a/mysql-test/main/query_cache.test
+++ b/mysql-test/main/query_cache.test
@@ -606,15 +606,15 @@ set character_set_results=default;
#
# max_sort_length
set GLOBAL query_cache_size=1355776;
-create table t1 (id int auto_increment primary key, c char(25));
+create table t1 (id int auto_increment primary key, c char(65));
insert into t1 set c = repeat('x',24);
-insert into t1 set c = concat(repeat('x',24),'x');
-insert into t1 set c = concat(repeat('x',24),'w');
-insert into t1 set c = concat(repeat('x',24),'y');
+insert into t1 set c = concat(repeat('x',64),'x');
+insert into t1 set c = concat(repeat('x',64),'w');
+insert into t1 set c = concat(repeat('x',64),'y');
set max_sort_length=200;
select c from t1 order by c, id;
reset query cache;
-set max_sort_length=20;
+set max_sort_length=64;
select c from t1 order by c, id;
set max_sort_length=200;
select c from t1 order by c, id;
diff --git a/mysql-test/main/range.result b/mysql-test/main/range.result
index 7fa6058b892..c7bf8948d3a 100644
--- a/mysql-test/main/range.result
+++ b/mysql-test/main/range.result
@@ -3226,6 +3226,31 @@ pk a b
1 5 50
65 5 50
drop table t1;
+create table t1(a int);
+insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+create table t2 (
+pk int primary key,
+key1 int,
+col1 varchar(255),
+key (key1, pk)
+);
+insert into t2 (pk, key1)
+select A.a+10 *B.a + 100*C.a, A.a+10 *B.a +100*C.a from t1 A, t1 B, t1 C;
+# This must use ALL, not range:
+explain select * from t2 force index (primary) where pk not in (1,2,3);
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t2 ALL PRIMARY NULL NULL NULL 1000 Using where
+drop table t1,t2;
+#
+# MDEV-24444: ASAN use-after-poison in Item_func_in::get_func_mm_tree with NOT IN
+#
+CREATE TABLE t1 (id INT, a CHAR(3), b INT, PRIMARY KEY(id), KEY(b), KEY(a));
+INSERT INTO t1 VALUES (1,'foo',10),(2,'bar',20);
+CREATE TABLE t2 (code CHAR(8), num INT, PRIMARY KEY (code));
+INSERT INTO t2 VALUES ('100',1),('111',2);
+SELECT * FROM t1 JOIN t2 ON (t2.code = t1.b) WHERE t1.a NOT IN ('baz', 'qux') OR t2.num != 3;
+id a b code num
+DROP TABLE t1, t2;
#
# End of 10.2 tests
#
diff --git a/mysql-test/main/range.test b/mysql-test/main/range.test
index b3a0bfe7ebc..4f228e5dce3 100644
--- a/mysql-test/main/range.test
+++ b/mysql-test/main/range.test
@@ -2180,6 +2180,40 @@ eval $q4;
drop table t1;
+#
+# MDEV-21958: Query having many NOT-IN clauses running forever (testcase 2)
+#
+create table t1(a int);
+insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+
+create table t2 (
+ pk int primary key,
+ key1 int,
+ col1 varchar(255),
+ key (key1, pk)
+);
+
+insert into t2 (pk, key1)
+select A.a+10 *B.a + 100*C.a, A.a+10 *B.a +100*C.a from t1 A, t1 B, t1 C;
+
+--echo # This must use ALL, not range:
+explain select * from t2 force index (primary) where pk not in (1,2,3);
+
+drop table t1,t2;
+
+--echo #
+--echo # MDEV-24444: ASAN use-after-poison in Item_func_in::get_func_mm_tree with NOT IN
+--echo #
+CREATE TABLE t1 (id INT, a CHAR(3), b INT, PRIMARY KEY(id), KEY(b), KEY(a));
+INSERT INTO t1 VALUES (1,'foo',10),(2,'bar',20);
+
+CREATE TABLE t2 (code CHAR(8), num INT, PRIMARY KEY (code));
+INSERT INTO t2 VALUES ('100',1),('111',2);
+
+SELECT * FROM t1 JOIN t2 ON (t2.code = t1.b) WHERE t1.a NOT IN ('baz', 'qux') OR t2.num != 3;
+
+DROP TABLE t1, t2;
+
--echo #
--echo # End of 10.2 tests
--echo #
diff --git a/mysql-test/main/range_mrr_icp.result b/mysql-test/main/range_mrr_icp.result
index e94c2598aad..3c5b7164163 100644
--- a/mysql-test/main/range_mrr_icp.result
+++ b/mysql-test/main/range_mrr_icp.result
@@ -3215,6 +3215,31 @@ pk a b
70 4 40
71 2 20
drop table t1;
+create table t1(a int);
+insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+create table t2 (
+pk int primary key,
+key1 int,
+col1 varchar(255),
+key (key1, pk)
+);
+insert into t2 (pk, key1)
+select A.a+10 *B.a + 100*C.a, A.a+10 *B.a +100*C.a from t1 A, t1 B, t1 C;
+# This must use ALL, not range:
+explain select * from t2 force index (primary) where pk not in (1,2,3);
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t2 ALL PRIMARY NULL NULL NULL 1000 Using where
+drop table t1,t2;
+#
+# MDEV-24444: ASAN use-after-poison in Item_func_in::get_func_mm_tree with NOT IN
+#
+CREATE TABLE t1 (id INT, a CHAR(3), b INT, PRIMARY KEY(id), KEY(b), KEY(a));
+INSERT INTO t1 VALUES (1,'foo',10),(2,'bar',20);
+CREATE TABLE t2 (code CHAR(8), num INT, PRIMARY KEY (code));
+INSERT INTO t2 VALUES ('100',1),('111',2);
+SELECT * FROM t1 JOIN t2 ON (t2.code = t1.b) WHERE t1.a NOT IN ('baz', 'qux') OR t2.num != 3;
+id a b code num
+DROP TABLE t1, t2;
#
# End of 10.2 tests
#
diff --git a/mysql-test/main/range_notembedded.result b/mysql-test/main/range_notembedded.result
new file mode 100644
index 00000000000..4973f449631
--- /dev/null
+++ b/mysql-test/main/range_notembedded.result
@@ -0,0 +1,37 @@
+drop table if exists t1,t2;
+#
+# MDEV-21958: Query having many NOT-IN clauses running forever
+#
+create table t2 (
+pk int primary key,
+key1 int,
+col1 int,
+key (key1, pk)
+);
+insert into t2 (pk, key1) values (1,1),(2,2),(3,3),(4,4),(5,5);
+set @tmp_21958=@@optimizer_trace;
+set optimizer_trace=1;
+explain select * from t2 where key1 in (1,2,3) and pk not in (1,2,3);
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t2 range PRIMARY,key1 key1 5 NULL 3 Using index condition
+# This should show only ranges in form "(1) <= (key1) <= (1)"
+# ranges over "pk" should not be constructed.
+select json_detailed(JSON_EXTRACT(trace, '$**.ranges'))
+from information_schema.optimizer_trace;
+json_detailed(JSON_EXTRACT(trace, '$**.ranges'))
+[
+
+ [
+ "(1) <= (key1) <= (1)",
+ "(2) <= (key1) <= (2)",
+ "(3) <= (key1) <= (3)"
+ ],
+
+ [
+ "(1) <= (key1) <= (1)",
+ "(2) <= (key1) <= (2)",
+ "(3) <= (key1) <= (3)"
+ ]
+]
+set optimizer_trace=@tmp_21958;
+drop table t2;
diff --git a/mysql-test/main/range_notembedded.test b/mysql-test/main/range_notembedded.test
new file mode 100644
index 00000000000..4dc49429ff1
--- /dev/null
+++ b/mysql-test/main/range_notembedded.test
@@ -0,0 +1,33 @@
+#
+# Range tests without embedded server.
+# The first reason to have them is that embedded server doesn't have
+# optimizer trace.
+#
+--source include/not_embedded.inc
+--disable_warnings
+drop table if exists t1,t2;
+--enable_warnings
+
+--echo #
+--echo # MDEV-21958: Query having many NOT-IN clauses running forever
+--echo #
+create table t2 (
+ pk int primary key,
+ key1 int,
+ col1 int,
+ key (key1, pk)
+);
+
+insert into t2 (pk, key1) values (1,1),(2,2),(3,3),(4,4),(5,5);
+
+set @tmp_21958=@@optimizer_trace;
+set optimizer_trace=1;
+explain select * from t2 where key1 in (1,2,3) and pk not in (1,2,3);
+
+--echo # This should show only ranges in form "(1) <= (key1) <= (1)"
+--echo # ranges over "pk" should not be constructed.
+select json_detailed(JSON_EXTRACT(trace, '$**.ranges'))
+from information_schema.optimizer_trace;
+set optimizer_trace=@tmp_21958;
+
+drop table t2;
diff --git a/mysql-test/main/set_password.result b/mysql-test/main/set_password.result
index 6a4e0da937e..a3d8683ee64 100644
--- a/mysql-test/main/set_password.result
+++ b/mysql-test/main/set_password.result
@@ -11,9 +11,11 @@ create user oldauth@localhost identified with 'mysql_old_password' using '378b24
create user oldpass@localhost identified by password '378b243e220ca493';
create user oldpassold@localhost identified with 'mysql_old_password';
set password for oldpassold@localhost = '378b243e220ca493';
+create user invalidmysql57auth@localhost identified via 'mysql_native_password' using '*THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE';
select user, host, password, plugin, authentication_string from mysql.user where user != 'root';
User Host Password plugin authentication_string
invalidauth localhost invalid mysql_native_password invalid
+invalidmysql57auth localhost *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE mysql_native_password *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE
invalidpass localhost invalid mysql_native_password invalid
invalidpassnat localhost invalid mysql_native_password invalid
mariadb.sys localhost mysql_native_password
@@ -95,6 +97,7 @@ set password for oldpassold@localhost = PASSWORD('test2');
select user, host, password, plugin, authentication_string from mysql.user where user != 'root';
User Host Password plugin authentication_string
invalidauth localhost invalid mysql_native_password invalid
+invalidmysql57auth localhost *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE mysql_native_password *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE
invalidpass localhost invalid mysql_native_password invalid
invalidpassnat localhost invalid mysql_native_password invalid
mariadb.sys localhost mysql_native_password
@@ -160,6 +163,9 @@ ERROR 28000: Access denied for user 'invalidpass'@'localhost' (using password: Y
connect(localhost,invalidpassnat,invalid,test,MASTER_PORT,MASTER_SOCKET);
connect con,localhost,invalidpassnat,invalid,;
ERROR 28000: Access denied for user 'invalidpassnat'@'localhost' (using password: YES)
+connect(localhost,invalidmysql57auth,invalid,test,MASTER_PORT,MASTER_SOCKET);
+connect con,localhost,invalidmysql57auth,invalid,;
+ERROR 28000: Access denied for user 'invalidmysql57auth'@'localhost' (using password: YES)
connect con,localhost,oldauth,test2,;
select current_user();
current_user()
@@ -177,7 +183,7 @@ oldpassold@localhost
disconnect con;
connection default;
drop user natauth@localhost, newpass@localhost, newpassnat@localhost;
-drop user invalidauth@localhost, invalidpass@localhost, invalidpassnat@localhost;
+drop user invalidauth@localhost, invalidpass@localhost, invalidpassnat@localhost,invalidmysql57auth@localhost;
drop user oldauth@localhost, oldpass@localhost, oldpassold@localhost;
set global secure_auth=default;
# switching from mysql.global_priv to mysql.user
diff --git a/mysql-test/main/set_password.test b/mysql-test/main/set_password.test
index dd5e261346b..ec652a09274 100644
--- a/mysql-test/main/set_password.test
+++ b/mysql-test/main/set_password.test
@@ -31,6 +31,8 @@ create user oldpass@localhost identified by password '378b243e220ca493';
create user oldpassold@localhost identified with 'mysql_old_password';
set password for oldpassold@localhost = '378b243e220ca493';
+create user invalidmysql57auth@localhost identified via 'mysql_native_password' using '*THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE';
+
--sorted_result
select user, host, password, plugin, authentication_string from mysql.user where user != 'root';
@@ -131,6 +133,9 @@ select current_user();
--replace_result $MASTER_MYSOCK MASTER_SOCKET $MASTER_MYPORT MASTER_PORT
--error ER_ACCESS_DENIED_ERROR
--connect(con,localhost,invalidpassnat,invalid,)
+--replace_result $MASTER_MYSOCK MASTER_SOCKET $MASTER_MYPORT MASTER_PORT
+--error ER_ACCESS_DENIED_ERROR
+--connect(con,localhost,invalidmysql57auth,invalid,)
--connect(con,localhost,oldauth,test2,)
select current_user();
@@ -144,7 +149,7 @@ select current_user();
--connection default
drop user natauth@localhost, newpass@localhost, newpassnat@localhost;
-drop user invalidauth@localhost, invalidpass@localhost, invalidpassnat@localhost;
+drop user invalidauth@localhost, invalidpass@localhost, invalidpassnat@localhost,invalidmysql57auth@localhost;
drop user oldauth@localhost, oldpass@localhost, oldpassold@localhost;
set global secure_auth=default;
diff --git a/mysql-test/main/type_year.result b/mysql-test/main/type_year.result
index 4fb38bdf798..a3918580e1f 100644
--- a/mysql-test/main/type_year.result
+++ b/mysql-test/main/type_year.result
@@ -1,4 +1,3 @@
-drop table if exists t1;
create table t1 (y year,y2 year(2));
Warnings:
Note 1287 'YEAR(2)' is deprecated and will be removed in a future release. Please use YEAR(4) instead
@@ -28,6 +27,9 @@ y y2
2001 01
2069 69
drop table t1;
+#
+# Bug 2335
+#
create table t1 (y year);
insert ignore into t1 values (now());
Warnings:
@@ -36,6 +38,9 @@ select if(y = now(), 1, 0) from t1;
if(y = now(), 1, 0)
1
drop table t1;
+#
+# Bug #27176: Assigning a string to an year column has unexpected results
+#
create table t1(a year);
insert into t1 values (2000.5), ('2000.5'), ('2001a'), ('2.001E3');
Warnings:
@@ -47,7 +52,9 @@ a
2001
2001
drop table t1;
-End of 5.0 tests
+#
+# End of 5.0 tests
+#
#
# Bug #49480: WHERE using YEAR columns returns unexpected results
#
@@ -373,7 +380,8 @@ Note 1287 'YEAR(2)' is deprecated and will be removed in a future release. Pleas
Note 1287 'YEAR(2)' is deprecated and will be removed in a future release. Please use YEAR(4) instead
DROP TABLE t1;
#
-End of 5.1 tests
+# End of 5.1 tests
+#
create function y2k() returns int deterministic return 2000;
create table t1 (a year(2), b int);
Warnings:
@@ -456,9 +464,6 @@ DROP TABLE t1;
# End of 10.1 tests
#
#
-# Start of 10.2 tests
-#
-#
# MDEV-9392 Copying from DECIMAL to YEAR is not consistent about warnings
#
CREATE TABLE t1 (a YEAR);
@@ -500,6 +505,64 @@ Warnings:
Warning 1264 Out of range value for column 'a' at row 1
DROP TABLE t1;
#
+# Various widths of the YEAR
+#
+create or replace table t1 (a YEAR(0));
+Warnings:
+Note 1287 'YEAR(0)' is deprecated and will be removed in a future release. Please use YEAR(4) instead
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `a` year(4) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+create or replace table t1 (a YEAR(1));
+Warnings:
+Note 1287 'YEAR(1)' is deprecated and will be removed in a future release. Please use YEAR(4) instead
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `a` year(4) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+create or replace table t1 (a YEAR(2));
+Warnings:
+Note 1287 'YEAR(2)' is deprecated and will be removed in a future release. Please use YEAR(4) instead
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `a` year(2) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+create or replace table t1 (a YEAR(3));
+Warnings:
+Note 1287 'YEAR(3)' is deprecated and will be removed in a future release. Please use YEAR(4) instead
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `a` year(4) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+create or replace table t1 (a YEAR(4));
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `a` year(4) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+create or replace table t1 (a YEAR(5));
+Warnings:
+Note 1287 'YEAR(5)' is deprecated and will be removed in a future release. Please use YEAR(4) instead
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `a` year(4) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+create or replace table t1 (a YEAR(100));
+Warnings:
+Note 1287 'YEAR(100)' is deprecated and will be removed in a future release. Please use YEAR(4) instead
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `a` year(4) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+drop table t1;
+#
# End of 10.2 tests
#
#
diff --git a/mysql-test/main/type_year.test b/mysql-test/main/type_year.test
index 1eb528d7af3..0f1f49be5d0 100644
--- a/mysql-test/main/type_year.test
+++ b/mysql-test/main/type_year.test
@@ -1,9 +1,6 @@
#
# Test year
#
---disable_warnings
-drop table if exists t1;
---enable_warnings
create table t1 (y year,y2 year(2));
insert into t1 values (0,0),(1999,1999),(2000,2000),(2001,2001),(70,70),(69,69);
@@ -12,24 +9,27 @@ select * from t1 order by y;
select * from t1 order by y2;
drop table t1;
-#
-# Bug 2335
-#
+--echo #
+--echo # Bug 2335
+--echo #
create table t1 (y year);
insert ignore into t1 values (now());
select if(y = now(), 1, 0) from t1;
drop table t1;
-#
-# Bug #27176: Assigning a string to an year column has unexpected results
-#
+--echo #
+--echo # Bug #27176: Assigning a string to an year column has unexpected results
+--echo #
+
create table t1(a year);
insert into t1 values (2000.5), ('2000.5'), ('2001a'), ('2.001E3');
select * from t1;
drop table t1;
---echo End of 5.0 tests
+--echo #
+--echo # End of 5.0 tests
+--echo #
--echo #
--echo # Bug #49480: WHERE using YEAR columns returns unexpected results
@@ -169,8 +169,9 @@ ALTER TABLE t1 MODIFY COLUMN c2 YEAR(2);
DROP TABLE t1;
--echo #
+--echo # End of 5.1 tests
+--echo #
---echo End of 5.1 tests
#
# fun with convert_const_to_int
# in some cases 00 is equal to 2000, in others it is not.
@@ -230,10 +231,6 @@ DROP TABLE t1;
--echo #
--echo #
---echo # Start of 10.2 tests
---echo #
-
---echo #
--echo # MDEV-9392 Copying from DECIMAL to YEAR is not consistent about warnings
--echo #
CREATE TABLE t1 (a YEAR);
@@ -268,6 +265,17 @@ SET STATEMENT sql_mode = 'NO_ENGINE_SUBSTITUTION' FOR
ALTER TABLE t1 MODIFY a YEAR;
DROP TABLE t1;
+--echo #
+--echo # Various widths of the YEAR
+--echo #
+create or replace table t1 (a YEAR(0)); SHOW CREATE TABLE t1;
+create or replace table t1 (a YEAR(1)); SHOW CREATE TABLE t1;
+create or replace table t1 (a YEAR(2)); SHOW CREATE TABLE t1;
+create or replace table t1 (a YEAR(3)); SHOW CREATE TABLE t1;
+create or replace table t1 (a YEAR(4)); SHOW CREATE TABLE t1;
+create or replace table t1 (a YEAR(5)); SHOW CREATE TABLE t1;
+create or replace table t1 (a YEAR(100)); SHOW CREATE TABLE t1;
+drop table t1;
--echo #
--echo # End of 10.2 tests
diff --git a/mysql-test/main/view.result b/mysql-test/main/view.result
index 1891e3c44f7..e007e8a07e9 100644
--- a/mysql-test/main/view.result
+++ b/mysql-test/main/view.result
@@ -6726,6 +6726,41 @@ DROP PROCEDURE sp;
DROP VIEW v1;
DROP TABLE t1;
#
+# MDEV-24314: create view with derived table without default database
+#
+drop database test;
+create database db1;
+create table db1.t1 (a int);
+insert into db1.t1 values (3),(7),(1);
+create view db1.v1 as select * from (select * from db1.t1) t;
+show create view db1.v1;
+View Create View character_set_client collation_connection
+v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `db1`.`v1` AS select `t`.`a` AS `a` from (select `db1`.`t1`.`a` AS `a` from `db1`.`t1`) `t` latin1 latin1_swedish_ci
+select * from db1.v1;
+a
+3
+7
+1
+drop view db1.v1;
+prepare stmt from "
+create view db1.v1 as select * from (select * from db1.t1) t;
+";
+execute stmt;
+deallocate prepare stmt;
+show create view db1.v1;
+View Create View character_set_client collation_connection
+v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `db1`.`v1` AS select `t`.`a` AS `a` from (select `db1`.`t1`.`a` AS `a` from `db1`.`t1`) `t` latin1 latin1_swedish_ci
+select * from db1.v1;
+a
+3
+7
+1
+drop view db1.v1;
+drop table db1.t1;
+drop database db1;
+create database test;
+use test;
+#
# End of 10.2 tests
#
#
diff --git a/mysql-test/main/view.test b/mysql-test/main/view.test
index 9b2baa69e8c..40cc05fd9c2 100644
--- a/mysql-test/main/view.test
+++ b/mysql-test/main/view.test
@@ -6433,6 +6433,36 @@ DROP VIEW v1;
DROP TABLE t1;
--echo #
+--echo # MDEV-24314: create view with derived table without default database
+--echo #
+
+drop database test;
+
+create database db1;
+create table db1.t1 (a int);
+insert into db1.t1 values (3),(7),(1);
+
+create view db1.v1 as select * from (select * from db1.t1) t;
+show create view db1.v1;
+select * from db1.v1;
+drop view db1.v1;
+
+prepare stmt from "
+create view db1.v1 as select * from (select * from db1.t1) t;
+";
+execute stmt;
+deallocate prepare stmt;
+show create view db1.v1;
+select * from db1.v1;
+drop view db1.v1;
+
+drop table db1.t1;
+drop database db1;
+
+create database test;
+use test;
+
+--echo #
--echo # End of 10.2 tests
--echo #
diff --git a/mysql-test/mysql-test-run.pl b/mysql-test/mysql-test-run.pl
index b2f948fe31d..726712dbd2d 100755
--- a/mysql-test/mysql-test-run.pl
+++ b/mysql-test/mysql-test-run.pl
@@ -3259,6 +3259,7 @@ sub mysql_install_db {
mtr_add_arg($args, "--tmpdir=%s", "$opt_vardir/tmp/");
mtr_add_arg($args, "--core-file");
mtr_add_arg($args, "--console");
+ mtr_add_arg($args, "--character-set-server=latin1");
if ( $opt_debug )
{
diff --git a/mysql-test/suite/binlog/disabled.def b/mysql-test/suite/binlog/disabled.def
index 424e5549541..888298bbb09 100644
--- a/mysql-test/suite/binlog/disabled.def
+++ b/mysql-test/suite/binlog/disabled.def
@@ -9,5 +9,3 @@
# Do not use any TAB characters for whitespace.
#
##############################################################################
-binlog_truncate_innodb : BUG#11764459 2010-10-20 anitha Originally disabled due to BUG#42643. Product bug fixed, but test changes needed
-binlog_spurious_ddl_errors : BUG#11761680 2013-01-18 astha Fixed on mysql-5.6 and trunk
diff --git a/mysql-test/suite/binlog/r/binlog_spurious_ddl_errors.result b/mysql-test/suite/binlog/r/binlog_spurious_ddl_errors.result
index 1a81eee1a58..798bd8ab853 100644
--- a/mysql-test/suite/binlog/r/binlog_spurious_ddl_errors.result
+++ b/mysql-test/suite/binlog/r/binlog_spurious_ddl_errors.result
@@ -1,5 +1,5 @@
SET @old_binlog_format= @@global.binlog_format;
-INSTALL PLUGIN example SONAME 'ha_example.so';
+INSTALL PLUGIN example SONAME 'ha_example';
################################################################################
# Verifies if ER_BINLOG_STMT_MODE_AND_ROW_ENGINE happens by setting the binlog
# format to STATEMENT and the transaction isolation level to READ COMMITTED as
@@ -18,7 +18,7 @@ ALTER TABLE t_row ADD COLUMN b INT;
CREATE TRIGGER trig_row BEFORE INSERT ON t_row FOR EACH ROW INSERT INTO t_stmt VALUES (1);
CREATE INDEX i ON t_row(a);
CREATE TABLE t_row_new ENGINE = InnoDB SELECT * FROM t_row;
-ERROR HY000: Cannot execute statement: impossible to write to binary log since BINLOG_FORMAT = STATEMENT and at least one table uses a storage engine limited to row-based logging. InnoDB is limited to row-logging when transaction isolation level is READ COMMITTED or READ UNCOMMITTED.
+ERROR HY000: Cannot execute statement: impossible to write to binary log since BINLOG_FORMAT = STATEMENT and at least one table uses a storage engine limited to row-based logging.
DROP TABLE t_row;
@@ -36,12 +36,11 @@ DROP TABLE t_row;
SET binlog_format = ROW;
CREATE TABLE t_stmt (a VARCHAR(100)) ENGINE = EXAMPLE;
ALTER TABLE t_stmt ADD COLUMN b INT;
-ERROR 42000: This version of MySQL doesn't yet support 'ALTER TABLE'
CREATE TRIGGER trig_stmt BEFORE INSERT ON t_stmt FOR EACH ROW INSERT INTO t_stmt VALUES (1);
CREATE INDEX i ON t_stmt(a);
ERROR 42000: Too many key parts specified; max 0 parts allowed
CREATE TABLE t_stmt_new ENGINE = EXAMPLE SELECT * FROM t_stmt;
-ERROR HY000: Cannot execute statement: impossible to write to binary log since BINLOG_FORMAT = ROW and at least one table uses a storage engine limited to statement-based logging.
+ERROR HY000: Cannot execute statement: impossible to write to binary log since BINLOG_FORMAT = ROW and at least one table uses a storage engine limited to statement-based logging
DROP TABLE t_stmt;
diff --git a/mysql-test/suite/binlog/r/binlog_truncate_innodb.result b/mysql-test/suite/binlog/r/binlog_truncate_innodb.result
index 8beeeb1a428..87ce8e30dee 100644
--- a/mysql-test/suite/binlog/r/binlog_truncate_innodb.result
+++ b/mysql-test/suite/binlog/r/binlog_truncate_innodb.result
@@ -7,9 +7,11 @@ INSERT INTO t2 VALUES (1),(2),(3);
**** Truncate of empty table shall be logged
TRUNCATE TABLE t1;
TRUNCATE TABLE t2;
-show binlog events from <binlog_start>;
+include/show_binlog_events.inc
Log_name Pos Event_type Server_id End_log_pos Info
+master-bin.000001 # Gtid # # GTID #-#-#
master-bin.000001 # Query # # use `test`; TRUNCATE TABLE t1
+master-bin.000001 # Gtid # # GTID #-#-#
master-bin.000001 # Query # # use `test`; TRUNCATE TABLE t2
DROP TABLE t1,t2;
#
@@ -18,18 +20,17 @@ DROP TABLE t1,t2;
CREATE TABLE t1 (a INT) ENGINE=InnoDB;
CREATE TABLE t2 (a INT) ENGINE=InnoDB;
INSERT INTO t1 VALUES (1),(2);
-# Connection: default
BEGIN;
INSERT INTO t2 SELECT * FROM t1;
-# Connection: truncate
+connect truncate,localhost,root,,;
TRUNCATE TABLE t1;
-# Connection: default
+connection default;
INSERT INTO t2 SELECT * FROM t1;
SELECT COUNT(*) FROM t2;
COUNT(*)
4
COMMIT;
-# Connection: truncate
+connection truncate;
# Reaping TRUNCATE TABLE
SELECT COUNT(*) FROM t1;
COUNT(*)
@@ -37,16 +38,20 @@ COUNT(*)
SELECT COUNT(*) FROM t2;
COUNT(*)
4
-# Connection: default
-show binlog events from <binlog_start>;
+connection default;
+include/show_binlog_events.inc
Log_name Pos Event_type Server_id End_log_pos Info
-master-bin.000001 # Query # # BEGIN
+master-bin.000001 # Gtid # # BEGIN GTID #-#-#
+master-bin.000001 # Annotate_rows # # INSERT INTO t2 SELECT * FROM t1
master-bin.000001 # Table_map # # table_id: # (test.t2)
-master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F
+master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F
+master-bin.000001 # Annotate_rows # # INSERT INTO t2 SELECT * FROM t1
master-bin.000001 # Table_map # # table_id: # (test.t2)
-master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F
+master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F
master-bin.000001 # Xid # # COMMIT /* XID */
+master-bin.000001 # Gtid # # GTID #-#-#
master-bin.000001 # Query # # use `test`; TRUNCATE TABLE t1
+disconnect truncate;
DROP TABLE t1,t2;
# Even though the isolation level might be permissive, truncate
# table follows a stricter isolation as its locking is based on
@@ -59,9 +64,11 @@ SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
TRUNCATE TABLE t1;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
TRUNCATE TABLE t2;
-show binlog events from <binlog_start>;
+include/show_binlog_events.inc
Log_name Pos Event_type Server_id End_log_pos Info
+master-bin.000001 # Gtid # # GTID #-#-#
master-bin.000001 # Query # # use `test`; TRUNCATE TABLE t1
+master-bin.000001 # Gtid # # GTID #-#-#
master-bin.000001 # Query # # use `test`; TRUNCATE TABLE t2
DROP TABLE t1,t2;
#
@@ -71,18 +78,17 @@ CREATE TABLE t1 (a INT) ENGINE=InnoDB;
CREATE TABLE t2 (a INT) ENGINE=InnoDB;
INSERT INTO t1 VALUES (1),(2);
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
-# Connection: default
BEGIN;
INSERT INTO t2 SELECT * FROM t1;
-# Connection: truncate
+connect truncate,localhost,root,,;
TRUNCATE TABLE t1;
-# Connection: default
+connection default;
INSERT INTO t2 SELECT * FROM t1;
SELECT COUNT(*) FROM t2;
COUNT(*)
4
COMMIT;
-# Connection: truncate
+connection truncate;
# Reaping TRUNCATE TABLE
SELECT COUNT(*) FROM t1;
COUNT(*)
@@ -90,16 +96,20 @@ COUNT(*)
SELECT COUNT(*) FROM t2;
COUNT(*)
4
-# Connection: default
-show binlog events from <binlog_start>;
+connection default;
+include/show_binlog_events.inc
Log_name Pos Event_type Server_id End_log_pos Info
-master-bin.000001 # Query # # BEGIN
+master-bin.000001 # Gtid # # BEGIN GTID #-#-#
+master-bin.000001 # Annotate_rows # # INSERT INTO t2 SELECT * FROM t1
master-bin.000001 # Table_map # # table_id: # (test.t2)
-master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F
+master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F
+master-bin.000001 # Annotate_rows # # INSERT INTO t2 SELECT * FROM t1
master-bin.000001 # Table_map # # table_id: # (test.t2)
-master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F
+master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F
master-bin.000001 # Xid # # COMMIT /* XID */
+master-bin.000001 # Gtid # # GTID #-#-#
master-bin.000001 # Query # # use `test`; TRUNCATE TABLE t1
+disconnect truncate;
DROP TABLE t1,t2;
CREATE TABLE t1 (a INT) ENGINE=InnoDB;
CREATE TABLE t2 (a INT) ENGINE=InnoDB;
@@ -109,9 +119,11 @@ SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
TRUNCATE TABLE t1;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
TRUNCATE TABLE t2;
-show binlog events from <binlog_start>;
+include/show_binlog_events.inc
Log_name Pos Event_type Server_id End_log_pos Info
+master-bin.000001 # Gtid # # GTID #-#-#
master-bin.000001 # Query # # use `test`; TRUNCATE TABLE t1
+master-bin.000001 # Gtid # # GTID #-#-#
master-bin.000001 # Query # # use `test`; TRUNCATE TABLE t2
DROP TABLE t1,t2;
#
@@ -121,18 +133,17 @@ CREATE TABLE t1 (a INT) ENGINE=InnoDB;
CREATE TABLE t2 (a INT) ENGINE=InnoDB;
INSERT INTO t1 VALUES (1),(2);
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
-# Connection: default
BEGIN;
INSERT INTO t2 SELECT * FROM t1;
-# Connection: truncate
+connect truncate,localhost,root,,;
TRUNCATE TABLE t1;
-# Connection: default
+connection default;
INSERT INTO t2 SELECT * FROM t1;
SELECT COUNT(*) FROM t2;
COUNT(*)
4
COMMIT;
-# Connection: truncate
+connection truncate;
# Reaping TRUNCATE TABLE
SELECT COUNT(*) FROM t1;
COUNT(*)
@@ -140,16 +151,20 @@ COUNT(*)
SELECT COUNT(*) FROM t2;
COUNT(*)
4
-# Connection: default
-show binlog events from <binlog_start>;
+connection default;
+include/show_binlog_events.inc
Log_name Pos Event_type Server_id End_log_pos Info
-master-bin.000001 # Query # # BEGIN
+master-bin.000001 # Gtid # # BEGIN GTID #-#-#
+master-bin.000001 # Annotate_rows # # INSERT INTO t2 SELECT * FROM t1
master-bin.000001 # Table_map # # table_id: # (test.t2)
-master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F
+master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F
+master-bin.000001 # Annotate_rows # # INSERT INTO t2 SELECT * FROM t1
master-bin.000001 # Table_map # # table_id: # (test.t2)
-master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F
+master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F
master-bin.000001 # Xid # # COMMIT /* XID */
+master-bin.000001 # Gtid # # GTID #-#-#
master-bin.000001 # Query # # use `test`; TRUNCATE TABLE t1
+disconnect truncate;
DROP TABLE t1,t2;
CREATE TABLE t1 (a INT) ENGINE=InnoDB;
CREATE TABLE t2 (a INT) ENGINE=InnoDB;
@@ -159,9 +174,11 @@ SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
TRUNCATE TABLE t1;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
TRUNCATE TABLE t2;
-show binlog events from <binlog_start>;
+include/show_binlog_events.inc
Log_name Pos Event_type Server_id End_log_pos Info
+master-bin.000001 # Gtid # # GTID #-#-#
master-bin.000001 # Query # # use `test`; TRUNCATE TABLE t1
+master-bin.000001 # Gtid # # GTID #-#-#
master-bin.000001 # Query # # use `test`; TRUNCATE TABLE t2
DROP TABLE t1,t2;
#
@@ -171,18 +188,17 @@ CREATE TABLE t1 (a INT) ENGINE=InnoDB;
CREATE TABLE t2 (a INT) ENGINE=InnoDB;
INSERT INTO t1 VALUES (1),(2);
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
-# Connection: default
BEGIN;
INSERT INTO t2 SELECT * FROM t1;
-# Connection: truncate
+connect truncate,localhost,root,,;
TRUNCATE TABLE t1;
-# Connection: default
+connection default;
INSERT INTO t2 SELECT * FROM t1;
SELECT COUNT(*) FROM t2;
COUNT(*)
4
COMMIT;
-# Connection: truncate
+connection truncate;
# Reaping TRUNCATE TABLE
SELECT COUNT(*) FROM t1;
COUNT(*)
@@ -190,16 +206,20 @@ COUNT(*)
SELECT COUNT(*) FROM t2;
COUNT(*)
4
-# Connection: default
-show binlog events from <binlog_start>;
+connection default;
+include/show_binlog_events.inc
Log_name Pos Event_type Server_id End_log_pos Info
-master-bin.000001 # Query # # BEGIN
+master-bin.000001 # Gtid # # BEGIN GTID #-#-#
+master-bin.000001 # Annotate_rows # # INSERT INTO t2 SELECT * FROM t1
master-bin.000001 # Table_map # # table_id: # (test.t2)
-master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F
+master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F
+master-bin.000001 # Annotate_rows # # INSERT INTO t2 SELECT * FROM t1
master-bin.000001 # Table_map # # table_id: # (test.t2)
-master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F
+master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F
master-bin.000001 # Xid # # COMMIT /* XID */
+master-bin.000001 # Gtid # # GTID #-#-#
master-bin.000001 # Query # # use `test`; TRUNCATE TABLE t1
+disconnect truncate;
DROP TABLE t1,t2;
CREATE TABLE t1 (a INT) ENGINE=InnoDB;
CREATE TABLE t2 (a INT) ENGINE=InnoDB;
@@ -209,9 +229,11 @@ SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
TRUNCATE TABLE t1;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
TRUNCATE TABLE t2;
-show binlog events from <binlog_start>;
+include/show_binlog_events.inc
Log_name Pos Event_type Server_id End_log_pos Info
+master-bin.000001 # Gtid # # GTID #-#-#
master-bin.000001 # Query # # use `test`; TRUNCATE TABLE t1
+master-bin.000001 # Gtid # # GTID #-#-#
master-bin.000001 # Query # # use `test`; TRUNCATE TABLE t2
DROP TABLE t1,t2;
#
@@ -221,18 +243,17 @@ CREATE TABLE t1 (a INT) ENGINE=InnoDB;
CREATE TABLE t2 (a INT) ENGINE=InnoDB;
INSERT INTO t1 VALUES (1),(2);
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
-# Connection: default
BEGIN;
INSERT INTO t2 SELECT * FROM t1;
-# Connection: truncate
+connect truncate,localhost,root,,;
TRUNCATE TABLE t1;
-# Connection: default
+connection default;
INSERT INTO t2 SELECT * FROM t1;
SELECT COUNT(*) FROM t2;
COUNT(*)
4
COMMIT;
-# Connection: truncate
+connection truncate;
# Reaping TRUNCATE TABLE
SELECT COUNT(*) FROM t1;
COUNT(*)
@@ -240,16 +261,20 @@ COUNT(*)
SELECT COUNT(*) FROM t2;
COUNT(*)
4
-# Connection: default
-show binlog events from <binlog_start>;
+connection default;
+include/show_binlog_events.inc
Log_name Pos Event_type Server_id End_log_pos Info
-master-bin.000001 # Query # # BEGIN
+master-bin.000001 # Gtid # # BEGIN GTID #-#-#
+master-bin.000001 # Annotate_rows # # INSERT INTO t2 SELECT * FROM t1
master-bin.000001 # Table_map # # table_id: # (test.t2)
-master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F
+master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F
+master-bin.000001 # Annotate_rows # # INSERT INTO t2 SELECT * FROM t1
master-bin.000001 # Table_map # # table_id: # (test.t2)
-master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F
+master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F
master-bin.000001 # Xid # # COMMIT /* XID */
+master-bin.000001 # Gtid # # GTID #-#-#
master-bin.000001 # Query # # use `test`; TRUNCATE TABLE t1
+disconnect truncate;
DROP TABLE t1,t2;
SET BINLOG_FORMAT=STATEMENT;
RESET MASTER;
@@ -261,9 +286,11 @@ SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
TRUNCATE TABLE t1;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
TRUNCATE TABLE t2;
-show binlog events from <binlog_start>;
+include/show_binlog_events.inc
Log_name Pos Event_type Server_id End_log_pos Info
+master-bin.000001 # Gtid # # GTID #-#-#
master-bin.000001 # Query # # use `test`; TRUNCATE TABLE t1
+master-bin.000001 # Gtid # # GTID #-#-#
master-bin.000001 # Query # # use `test`; TRUNCATE TABLE t2
DROP TABLE t1,t2;
#
@@ -273,18 +300,17 @@ CREATE TABLE t1 (a INT) ENGINE=InnoDB;
CREATE TABLE t2 (a INT) ENGINE=InnoDB;
INSERT INTO t1 VALUES (1),(2);
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
-# Connection: default
BEGIN;
INSERT INTO t2 SELECT * FROM t1;
-# Connection: truncate
+connect truncate,localhost,root,,;
TRUNCATE TABLE t1;
-# Connection: default
+connection default;
INSERT INTO t2 SELECT * FROM t1;
SELECT COUNT(*) FROM t2;
COUNT(*)
4
COMMIT;
-# Connection: truncate
+connection truncate;
# Reaping TRUNCATE TABLE
SELECT COUNT(*) FROM t1;
COUNT(*)
@@ -292,14 +318,16 @@ COUNT(*)
SELECT COUNT(*) FROM t2;
COUNT(*)
4
-# Connection: default
-show binlog events from <binlog_start>;
+connection default;
+include/show_binlog_events.inc
Log_name Pos Event_type Server_id End_log_pos Info
-master-bin.000001 # Query # # BEGIN
+master-bin.000001 # Gtid # # BEGIN GTID #-#-#
master-bin.000001 # Query # # use `test`; INSERT INTO t2 SELECT * FROM t1
master-bin.000001 # Query # # use `test`; INSERT INTO t2 SELECT * FROM t1
master-bin.000001 # Xid # # COMMIT /* XID */
+master-bin.000001 # Gtid # # GTID #-#-#
master-bin.000001 # Query # # use `test`; TRUNCATE TABLE t1
+disconnect truncate;
DROP TABLE t1,t2;
# Truncate is not supported for SBR if the isolation level is
# READ UNCOMMITTED or READ COMMITTED. These specific isolation
@@ -312,9 +340,11 @@ SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
TRUNCATE TABLE t1;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
TRUNCATE TABLE t2;
-show binlog events from <binlog_start>;
+include/show_binlog_events.inc
Log_name Pos Event_type Server_id End_log_pos Info
+master-bin.000001 # Gtid # # GTID #-#-#
master-bin.000001 # Query # # use `test`; TRUNCATE TABLE t1
+master-bin.000001 # Gtid # # GTID #-#-#
master-bin.000001 # Query # # use `test`; TRUNCATE TABLE t2
DROP TABLE t1,t2;
#
@@ -324,18 +354,17 @@ CREATE TABLE t1 (a INT) ENGINE=InnoDB;
CREATE TABLE t2 (a INT) ENGINE=InnoDB;
INSERT INTO t1 VALUES (1),(2);
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
-# Connection: default
BEGIN;
INSERT INTO t2 SELECT * FROM t1;
-# Connection: truncate
+connect truncate,localhost,root,,;
TRUNCATE TABLE t1;
-# Connection: default
+connection default;
INSERT INTO t2 SELECT * FROM t1;
SELECT COUNT(*) FROM t2;
COUNT(*)
4
COMMIT;
-# Connection: truncate
+connection truncate;
# Reaping TRUNCATE TABLE
SELECT COUNT(*) FROM t1;
COUNT(*)
@@ -343,14 +372,16 @@ COUNT(*)
SELECT COUNT(*) FROM t2;
COUNT(*)
4
-# Connection: default
-show binlog events from <binlog_start>;
+connection default;
+include/show_binlog_events.inc
Log_name Pos Event_type Server_id End_log_pos Info
-master-bin.000001 # Query # # BEGIN
+master-bin.000001 # Gtid # # BEGIN GTID #-#-#
master-bin.000001 # Query # # use `test`; INSERT INTO t2 SELECT * FROM t1
master-bin.000001 # Query # # use `test`; INSERT INTO t2 SELECT * FROM t1
master-bin.000001 # Xid # # COMMIT /* XID */
+master-bin.000001 # Gtid # # GTID #-#-#
master-bin.000001 # Query # # use `test`; TRUNCATE TABLE t1
+disconnect truncate;
DROP TABLE t1,t2;
CREATE TABLE t1 (a INT) ENGINE=InnoDB;
CREATE TABLE t2 (a INT) ENGINE=InnoDB;
@@ -360,9 +391,11 @@ SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
TRUNCATE TABLE t1;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
TRUNCATE TABLE t2;
-show binlog events from <binlog_start>;
+include/show_binlog_events.inc
Log_name Pos Event_type Server_id End_log_pos Info
+master-bin.000001 # Gtid # # GTID #-#-#
master-bin.000001 # Query # # use `test`; TRUNCATE TABLE t1
+master-bin.000001 # Gtid # # GTID #-#-#
master-bin.000001 # Query # # use `test`; TRUNCATE TABLE t2
DROP TABLE t1,t2;
#
@@ -372,18 +405,17 @@ CREATE TABLE t1 (a INT) ENGINE=InnoDB;
CREATE TABLE t2 (a INT) ENGINE=InnoDB;
INSERT INTO t1 VALUES (1),(2);
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
-# Connection: default
BEGIN;
INSERT INTO t2 SELECT * FROM t1;
-# Connection: truncate
+connect truncate,localhost,root,,;
TRUNCATE TABLE t1;
-# Connection: default
+connection default;
INSERT INTO t2 SELECT * FROM t1;
SELECT COUNT(*) FROM t2;
COUNT(*)
4
COMMIT;
-# Connection: truncate
+connection truncate;
# Reaping TRUNCATE TABLE
SELECT COUNT(*) FROM t1;
COUNT(*)
@@ -391,13 +423,16 @@ COUNT(*)
SELECT COUNT(*) FROM t2;
COUNT(*)
4
-# Connection: default
-show binlog events from <binlog_start>;
+connection default;
+include/show_binlog_events.inc
Log_name Pos Event_type Server_id End_log_pos Info
-master-bin.000001 # Query # # BEGIN
+master-bin.000001 # Gtid # # BEGIN GTID #-#-#
master-bin.000001 # Query # # use `test`; INSERT INTO t2 SELECT * FROM t1
master-bin.000001 # Query # # use `test`; INSERT INTO t2 SELECT * FROM t1
master-bin.000001 # Xid # # COMMIT /* XID */
+master-bin.000001 # Gtid # # GTID #-#-#
master-bin.000001 # Query # # use `test`; TRUNCATE TABLE t1
+disconnect truncate;
DROP TABLE t1,t2;
-SET BINLOG_FORMAT=@old_binlog_format;
+SET @@global.binlog_format = @old_binlog_format;
+SET @@session.binlog_format = @old_binlog_format;
diff --git a/mysql-test/suite/binlog/t/binlog_spurious_ddl_errors-master.opt b/mysql-test/suite/binlog/t/binlog_spurious_ddl_errors-master.opt
deleted file mode 100644
index 627becdbfb5..00000000000
--- a/mysql-test/suite/binlog/t/binlog_spurious_ddl_errors-master.opt
+++ /dev/null
@@ -1 +0,0 @@
---innodb
diff --git a/mysql-test/suite/binlog/t/binlog_spurious_ddl_errors.test b/mysql-test/suite/binlog/t/binlog_spurious_ddl_errors.test
index e64e7838a31..29a860764a9 100644
--- a/mysql-test/suite/binlog/t/binlog_spurious_ddl_errors.test
+++ b/mysql-test/suite/binlog/t/binlog_spurious_ddl_errors.test
@@ -71,7 +71,6 @@ DROP TABLE t_row;
SET binlog_format = ROW;
CREATE TABLE t_stmt (a VARCHAR(100)) ENGINE = EXAMPLE;
---error ER_NOT_SUPPORTED_YET
ALTER TABLE t_stmt ADD COLUMN b INT;
CREATE TRIGGER trig_stmt BEFORE INSERT ON t_stmt FOR EACH ROW INSERT INTO t_stmt VALUES (1);
diff --git a/mysql-test/suite/binlog/t/binlog_truncate_innodb-master.opt b/mysql-test/suite/binlog/t/binlog_truncate_innodb-master.opt
deleted file mode 100644
index 69cc489a969..00000000000
--- a/mysql-test/suite/binlog/t/binlog_truncate_innodb-master.opt
+++ /dev/null
@@ -1 +0,0 @@
---loose-innodb \ No newline at end of file
diff --git a/mysql-test/suite/binlog/t/binlog_truncate_innodb.test b/mysql-test/suite/binlog/t/binlog_truncate_innodb.test
index 54a32b96ef1..511b82bd717 100644
--- a/mysql-test/suite/binlog/t/binlog_truncate_innodb.test
+++ b/mysql-test/suite/binlog/t/binlog_truncate_innodb.test
@@ -41,4 +41,5 @@ source include/binlog_truncate.test;
let $before_truncate = SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
source include/binlog_truncate.test;
-SET BINLOG_FORMAT=@old_binlog_format;
+SET @@global.binlog_format = @old_binlog_format;
+SET @@session.binlog_format = @old_binlog_format; \ No newline at end of file
diff --git a/mysql-test/suite/binlog_encryption/rpl_semi_sync.result b/mysql-test/suite/binlog_encryption/rpl_semi_sync.result
index 106efb555d3..d18bd1efda7 100644
--- a/mysql-test/suite/binlog_encryption/rpl_semi_sync.result
+++ b/mysql-test/suite/binlog_encryption/rpl_semi_sync.result
@@ -164,20 +164,15 @@ connection slave;
connection slave;
include/stop_slave.inc
connection master;
+include/kill_binlog_dump_threads.inc
set global rpl_semi_sync_master_timeout= 5000;
[ master status should be ON ]
-show status like 'Rpl_semi_sync_master_status';
-Variable_name Value
-Rpl_semi_sync_master_status ON
show status like 'Rpl_semi_sync_master_no_tx';
Variable_name Value
Rpl_semi_sync_master_no_tx 0
show status like 'Rpl_semi_sync_master_yes_tx';
Variable_name Value
Rpl_semi_sync_master_yes_tx 14
-show status like 'Rpl_semi_sync_master_clients';
-Variable_name Value
-Rpl_semi_sync_master_clients 0
[ semi-sync replication of these transactions will fail ]
insert into t1 values (500);
[ master status should be OFF ]
@@ -235,9 +230,6 @@ max(a)
500
connection master;
[ master status should be ON again after slave catches up ]
-show status like 'Rpl_semi_sync_master_status';
-Variable_name Value
-Rpl_semi_sync_master_status ON
show status like 'Rpl_semi_sync_master_no_tx';
Variable_name Value
Rpl_semi_sync_master_no_tx 12
@@ -304,8 +296,6 @@ connection master;
create table t1 (a int) engine = ENGINE_TYPE;
drop table t1;
connection slave;
-show status like 'Rpl_relay%';
-Variable_name Value
[ test reset master ]
connection master;
reset master;
@@ -321,7 +311,7 @@ Rpl_semi_sync_master_yes_tx 0
connection slave;
include/stop_slave.inc
reset slave;
-connection master;
+include/kill_binlog_dump_threads.inc
connection slave;
include/start_slave.inc
connection master;
@@ -353,6 +343,7 @@ include/stop_slave.inc
reset slave;
connection master;
reset master;
+include/kill_binlog_dump_threads.inc
set sql_log_bin=0;
grant replication slave on *.* to rpl@127.0.0.1 identified by 'rpl_password';
flush privileges;
@@ -403,10 +394,8 @@ SHOW STATUS LIKE 'Rpl_semi_sync_slave_status';
Variable_name Value
Rpl_semi_sync_slave_status OFF
connection master;
+include/kill_binlog_dump_threads.inc
[ Semi-sync status on master should be ON ]
-show status like 'Rpl_semi_sync_master_clients';
-Variable_name Value
-Rpl_semi_sync_master_clients 0
show status like 'Rpl_semi_sync_master_status';
Variable_name Value
Rpl_semi_sync_master_status ON
diff --git a/mysql-test/suite/federated/federatedx.result b/mysql-test/suite/federated/federatedx.result
index 8345f56dba9..2c21e31afe9 100644
--- a/mysql-test/suite/federated/federatedx.result
+++ b/mysql-test/suite/federated/federatedx.result
@@ -2284,6 +2284,32 @@ connection master;
CREATE TABLE t1 (a INT) ENGINE=FEDERATED CONNECTION='mysql://@127.0.0.1:SLAVE_PORT/federated/t1';
ERROR HY000: Can't create federated table. Foreign data src error: database: 'federated' username: '' hostname: '127.0.0.1'
#
+# MDEV-17573 Assertion in federatedx on multi-update
+#
+create table t1 (
+x int,
+d datetime);
+create table t1f engine=FEDERATED connection='mysql://root@127.0.0.1:MASTER_MYPORT/test/t1';
+create table t2 (
+x int, y int,
+d datetime);
+create table t2f engine=FEDERATED connection='mysql://root@127.0.0.1:MASTER_MYPORT/test/t2';
+create table t3 (
+x int, y int, z int,
+d datetime);
+create table t3f engine=FEDERATED connection='mysql://root@127.0.0.1:MASTER_MYPORT/test/t3';
+insert into t1 values (1, "1990-01-01 00:00");
+insert into t1 values (1, "1991-01-01 11:11");
+insert into t2 values (2, 2, "1992-02-02 22:22");
+insert into t3 values (3, 3, 3, "1993-03-03 23:33");
+update t1f, t2f, t3f set t1f.x= 11, t2f.y= 22, t3f.z= 33;
+drop table t1f;
+drop table t2f;
+drop table t3f;
+drop table t1;
+drop table t2;
+drop table t3;
+#
# MDEV-21049 Segfault in create federatedx table with empty hostname
#
connection master;
diff --git a/mysql-test/suite/federated/federatedx.test b/mysql-test/suite/federated/federatedx.test
index fcc0178c024..51d34298626 100644
--- a/mysql-test/suite/federated/federatedx.test
+++ b/mysql-test/suite/federated/federatedx.test
@@ -2011,6 +2011,35 @@ connection master;
eval CREATE TABLE t1 (a INT) ENGINE=FEDERATED CONNECTION='mysql://@127.0.0.1:$SLAVE_MYPORT/federated/t1';
--echo #
+--echo # MDEV-17573 Assertion in federatedx on multi-update
+--echo #
+create table t1 (
+ x int,
+ d datetime);
+--replace_result $MASTER_MYPORT MASTER_MYPORT
+eval create table t1f engine=FEDERATED connection='mysql://root@127.0.0.1:$MASTER_MYPORT/test/t1';
+
+create table t2 (
+ x int, y int,
+ d datetime);
+--replace_result $MASTER_MYPORT MASTER_MYPORT
+eval create table t2f engine=FEDERATED connection='mysql://root@127.0.0.1:$MASTER_MYPORT/test/t2';
+
+create table t3 (
+ x int, y int, z int,
+ d datetime);
+--replace_result $MASTER_MYPORT MASTER_MYPORT
+eval create table t3f engine=FEDERATED connection='mysql://root@127.0.0.1:$MASTER_MYPORT/test/t3';
+
+insert into t1 values (1, "1990-01-01 00:00");
+insert into t1 values (1, "1991-01-01 11:11");
+insert into t2 values (2, 2, "1992-02-02 22:22");
+insert into t3 values (3, 3, 3, "1993-03-03 23:33");
+update t1f, t2f, t3f set t1f.x= 11, t2f.y= 22, t3f.z= 33;
+
+drop table t1f; drop table t2f; drop table t3f; drop table t1; drop table t2; drop table t3;
+
+--echo #
--echo # MDEV-21049 Segfault in create federatedx table with empty hostname
--echo #
connection master;
diff --git a/mysql-test/suite/funcs_1/r/is_check_constraints.result b/mysql-test/suite/funcs_1/r/is_check_constraints.result
index 37fd191d979..2436be8142f 100644
--- a/mysql-test/suite/funcs_1/r/is_check_constraints.result
+++ b/mysql-test/suite/funcs_1/r/is_check_constraints.result
@@ -90,7 +90,9 @@ CREATE TABLE t3
(
a int,
b int check (b>0), # field constraint named 'b'
-CONSTRAINT b check (b>10) # table constraint
+CONSTRAINT b check (b>10), # table constraint
+# `CHECK_CLAUSE` should allow more then `var(64)` constraints
+CONSTRAINT b1 check (b<123456789012345678901234567890123456789012345678901234567890123456789)
) ENGINE=InnoDB;
SELECT * from information_schema.check_constraints;
CONSTRAINT_CATALOG CONSTRAINT_SCHEMA TABLE_NAME CONSTRAINT_NAME CHECK_CLAUSE
@@ -104,6 +106,7 @@ def foo t2 CHK_dates `start_date` is null
def foo t2 name char_length(`name`) > 2
def foo t3 b `b` > 0
def foo t3 b `b` > 10
+def foo t3 b1 `b` < 123456789012345678901234567890123456789012345678901234567890123456789
disconnect con1;
CONNECT con2, localhost, boo2,, test;
SELECT * from information_schema.check_constraints;
diff --git a/mysql-test/suite/funcs_1/r/is_columns_is.result b/mysql-test/suite/funcs_1/r/is_columns_is.result
index cd548f426e1..cdacf7a3d1e 100644
--- a/mysql-test/suite/funcs_1/r/is_columns_is.result
+++ b/mysql-test/suite/funcs_1/r/is_columns_is.result
@@ -24,7 +24,7 @@ def information_schema CHARACTER_SETS CHARACTER_SET_NAME 1 '' NO varchar 32 96 N
def information_schema CHARACTER_SETS DEFAULT_COLLATE_NAME 2 '' NO varchar 32 96 NULL NULL NULL utf8 utf8_general_ci varchar(32) select NEVER NULL
def information_schema CHARACTER_SETS DESCRIPTION 3 '' NO varchar 60 180 NULL NULL NULL utf8 utf8_general_ci varchar(60) select NEVER NULL
def information_schema CHARACTER_SETS MAXLEN 4 0 NO bigint NULL NULL 19 0 NULL NULL NULL bigint(3) select NEVER NULL
-def information_schema CHECK_CONSTRAINTS CHECK_CLAUSE 5 '' NO varchar 64 192 NULL NULL NULL utf8 utf8_general_ci varchar(64) select NEVER NULL
+def information_schema CHECK_CONSTRAINTS CHECK_CLAUSE 5 '' NO longtext 4294967295 4294967295 NULL NULL NULL utf8 utf8_general_ci longtext select NEVER NULL
def information_schema CHECK_CONSTRAINTS CONSTRAINT_CATALOG 1 '' NO varchar 512 1536 NULL NULL NULL utf8 utf8_general_ci varchar(512) select NEVER NULL
def information_schema CHECK_CONSTRAINTS CONSTRAINT_NAME 4 '' NO varchar 64 192 NULL NULL NULL utf8 utf8_general_ci varchar(64) select NEVER NULL
def information_schema CHECK_CONSTRAINTS CONSTRAINT_SCHEMA 2 '' NO varchar 64 192 NULL NULL NULL utf8 utf8_general_ci varchar(64) select NEVER NULL
@@ -572,7 +572,7 @@ NULL information_schema CHARACTER_SETS MAXLEN bigint NULL NULL NULL NULL bigint(
3.0000 information_schema CHECK_CONSTRAINTS CONSTRAINT_SCHEMA varchar 64 192 utf8 utf8_general_ci varchar(64)
3.0000 information_schema CHECK_CONSTRAINTS TABLE_NAME varchar 64 192 utf8 utf8_general_ci varchar(64)
3.0000 information_schema CHECK_CONSTRAINTS CONSTRAINT_NAME varchar 64 192 utf8 utf8_general_ci varchar(64)
-3.0000 information_schema CHECK_CONSTRAINTS CHECK_CLAUSE varchar 64 192 utf8 utf8_general_ci varchar(64)
+1.0000 information_schema CHECK_CONSTRAINTS CHECK_CLAUSE longtext 4294967295 4294967295 utf8 utf8_general_ci longtext
3.0000 information_schema CLIENT_STATISTICS CLIENT varchar 64 192 utf8 utf8_general_ci varchar(64)
NULL information_schema CLIENT_STATISTICS TOTAL_CONNECTIONS bigint NULL NULL NULL NULL bigint(21)
NULL information_schema CLIENT_STATISTICS CONCURRENT_CONNECTIONS bigint NULL NULL NULL NULL bigint(21)
diff --git a/mysql-test/suite/funcs_1/r/is_columns_is_embedded.result b/mysql-test/suite/funcs_1/r/is_columns_is_embedded.result
index b86897af8a4..5c941d279e3 100644
--- a/mysql-test/suite/funcs_1/r/is_columns_is_embedded.result
+++ b/mysql-test/suite/funcs_1/r/is_columns_is_embedded.result
@@ -24,7 +24,7 @@ def information_schema CHARACTER_SETS CHARACTER_SET_NAME 1 '' NO varchar 32 96 N
def information_schema CHARACTER_SETS DEFAULT_COLLATE_NAME 2 '' NO varchar 32 96 NULL NULL NULL utf8 utf8_general_ci varchar(32) NEVER NULL
def information_schema CHARACTER_SETS DESCRIPTION 3 '' NO varchar 60 180 NULL NULL NULL utf8 utf8_general_ci varchar(60) NEVER NULL
def information_schema CHARACTER_SETS MAXLEN 4 0 NO bigint NULL NULL 19 0 NULL NULL NULL bigint(3) NEVER NULL
-def information_schema CHECK_CONSTRAINTS CHECK_CLAUSE 5 '' NO varchar 64 192 NULL NULL NULL utf8 utf8_general_ci varchar(64) NEVER NULL
+def information_schema CHECK_CONSTRAINTS CHECK_CLAUSE 5 '' NO longtext 4294967295 4294967295 NULL NULL NULL utf8 utf8_general_ci longtext NEVER NULL
def information_schema CHECK_CONSTRAINTS CONSTRAINT_CATALOG 1 '' NO varchar 512 1536 NULL NULL NULL utf8 utf8_general_ci varchar(512) NEVER NULL
def information_schema CHECK_CONSTRAINTS CONSTRAINT_NAME 4 '' NO varchar 64 192 NULL NULL NULL utf8 utf8_general_ci varchar(64) NEVER NULL
def information_schema CHECK_CONSTRAINTS CONSTRAINT_SCHEMA 2 '' NO varchar 64 192 NULL NULL NULL utf8 utf8_general_ci varchar(64) NEVER NULL
@@ -572,7 +572,7 @@ NULL information_schema CHARACTER_SETS MAXLEN bigint NULL NULL NULL NULL bigint(
3.0000 information_schema CHECK_CONSTRAINTS CONSTRAINT_SCHEMA varchar 64 192 utf8 utf8_general_ci varchar(64)
3.0000 information_schema CHECK_CONSTRAINTS TABLE_NAME varchar 64 192 utf8 utf8_general_ci varchar(64)
3.0000 information_schema CHECK_CONSTRAINTS CONSTRAINT_NAME varchar 64 192 utf8 utf8_general_ci varchar(64)
-3.0000 information_schema CHECK_CONSTRAINTS CHECK_CLAUSE varchar 64 192 utf8 utf8_general_ci varchar(64)
+1.0000 information_schema CHECK_CONSTRAINTS CHECK_CLAUSE longtext 4294967295 4294967295 utf8 utf8_general_ci longtext
3.0000 information_schema CLIENT_STATISTICS CLIENT varchar 64 192 utf8 utf8_general_ci varchar(64)
NULL information_schema CLIENT_STATISTICS TOTAL_CONNECTIONS bigint NULL NULL NULL NULL bigint(21)
NULL information_schema CLIENT_STATISTICS CONCURRENT_CONNECTIONS bigint NULL NULL NULL NULL bigint(21)
diff --git a/mysql-test/suite/funcs_1/r/is_tables_is.result b/mysql-test/suite/funcs_1/r/is_tables_is.result
index 9af3aa860a0..65dae5a0d6b 100644
--- a/mysql-test/suite/funcs_1/r/is_tables_is.result
+++ b/mysql-test/suite/funcs_1/r/is_tables_is.result
@@ -91,9 +91,9 @@ TABLE_CATALOG def
TABLE_SCHEMA information_schema
TABLE_NAME CHECK_CONSTRAINTS
TABLE_TYPE SYSTEM VIEW
-ENGINE MEMORY
+ENGINE MYISAM_OR_MARIA
VERSION 11
-ROW_FORMAT Fixed
+ROW_FORMAT DYNAMIC_OR_PAGE
TABLE_ROWS #TBLR#
AVG_ROW_LENGTH #ARL#
DATA_LENGTH #DL#
@@ -1157,9 +1157,9 @@ TABLE_CATALOG def
TABLE_SCHEMA information_schema
TABLE_NAME CHECK_CONSTRAINTS
TABLE_TYPE SYSTEM VIEW
-ENGINE MEMORY
+ENGINE MYISAM_OR_MARIA
VERSION 11
-ROW_FORMAT Fixed
+ROW_FORMAT DYNAMIC_OR_PAGE
TABLE_ROWS #TBLR#
AVG_ROW_LENGTH #ARL#
DATA_LENGTH #DL#
diff --git a/mysql-test/suite/funcs_1/r/is_tables_is_embedded.result b/mysql-test/suite/funcs_1/r/is_tables_is_embedded.result
index 9af3aa860a0..65dae5a0d6b 100644
--- a/mysql-test/suite/funcs_1/r/is_tables_is_embedded.result
+++ b/mysql-test/suite/funcs_1/r/is_tables_is_embedded.result
@@ -91,9 +91,9 @@ TABLE_CATALOG def
TABLE_SCHEMA information_schema
TABLE_NAME CHECK_CONSTRAINTS
TABLE_TYPE SYSTEM VIEW
-ENGINE MEMORY
+ENGINE MYISAM_OR_MARIA
VERSION 11
-ROW_FORMAT Fixed
+ROW_FORMAT DYNAMIC_OR_PAGE
TABLE_ROWS #TBLR#
AVG_ROW_LENGTH #ARL#
DATA_LENGTH #DL#
@@ -1157,9 +1157,9 @@ TABLE_CATALOG def
TABLE_SCHEMA information_schema
TABLE_NAME CHECK_CONSTRAINTS
TABLE_TYPE SYSTEM VIEW
-ENGINE MEMORY
+ENGINE MYISAM_OR_MARIA
VERSION 11
-ROW_FORMAT Fixed
+ROW_FORMAT DYNAMIC_OR_PAGE
TABLE_ROWS #TBLR#
AVG_ROW_LENGTH #ARL#
DATA_LENGTH #DL#
diff --git a/mysql-test/suite/funcs_1/t/is_check_constraints.test b/mysql-test/suite/funcs_1/t/is_check_constraints.test
index b539de67f73..dbd286e6239 100644
--- a/mysql-test/suite/funcs_1/t/is_check_constraints.test
+++ b/mysql-test/suite/funcs_1/t/is_check_constraints.test
@@ -69,7 +69,9 @@ CREATE TABLE t3
(
a int,
b int check (b>0), # field constraint named 'b'
-CONSTRAINT b check (b>10) # table constraint
+CONSTRAINT b check (b>10), # table constraint
+# `CHECK_CLAUSE` should allow more then `var(64)` constraints
+CONSTRAINT b1 check (b<123456789012345678901234567890123456789012345678901234567890123456789)
) ENGINE=InnoDB;
--sorted_result
SELECT * from information_schema.check_constraints;
diff --git a/mysql-test/suite/galera/r/MDEV-24327.result b/mysql-test/suite/galera/r/MDEV-24327.result
new file mode 100644
index 00000000000..d309749efa4
--- /dev/null
+++ b/mysql-test/suite/galera/r/MDEV-24327.result
@@ -0,0 +1,35 @@
+connection node_2;
+connection node_1;
+CREATE TABLE t1 (f1 INTEGER PRIMARY KEY, f2 CHAR(1));
+INSERT INTO t1 VALUES (1, 'f');
+INSERT INTO t1 VALUES (2, 'g');
+connection node_1;
+SET AUTOCOMMIT=ON;
+START TRANSACTION;
+UPDATE t1 SET f2 = '1' WHERE f1 = 1;
+connect node_1a, 127.0.0.1, root, , test, $NODE_MYPORT_1;
+SET SESSION wsrep_sync_wait=0;
+connection node_1a;
+SET SESSION wsrep_on = 0;
+SET SESSION wsrep_on = 1;
+SET GLOBAL wsrep_provider_options = 'dbug=';
+SET GLOBAL wsrep_provider_options = 'dbug=d,commit_monitor_master_enter_sync';
+connection node_1;
+COMMIT;
+connection node_1a;
+SET SESSION wsrep_on = 0;
+SET SESSION wsrep_on = 1;
+SET GLOBAL wsrep_provider_options = 'dbug=';
+connection node_2;
+UPDATE t1 SET f2 = '2' WHERE f1 = 2;
+connection node_1a;
+SET GLOBAL wsrep_provider_options = 'signal=commit_monitor_master_enter_sync';
+SET GLOBAL wsrep_provider_options = 'dbug=';
+connection node_1;
+SELECT * FROM t1;
+f1 f2
+1 1
+2 2
+"node 1 is complete now"
+connection node_2;
+DROP TABLE t1;
diff --git a/mysql-test/suite/galera/r/MENT-1047.result b/mysql-test/suite/galera/r/MENT-1047.result
new file mode 100644
index 00000000000..76d58f6d52f
--- /dev/null
+++ b/mysql-test/suite/galera/r/MENT-1047.result
@@ -0,0 +1,4 @@
+connection node_2;
+connection node_1;
+XA START 'trx';
+ERROR 42000: This version of MariaDB doesn't yet support 'XA transactions with Galera replication'
diff --git a/mysql-test/suite/galera/r/galera#500.result b/mysql-test/suite/galera/r/galera#500.result
index a5ab0b19718..d983e844d76 100644
--- a/mysql-test/suite/galera/r/galera#500.result
+++ b/mysql-test/suite/galera/r/galera#500.result
@@ -1,3 +1,5 @@
+connection node_1;
+connection node_2;
connection node_2;
connection node_1;
connection node_1;
diff --git a/mysql-test/suite/galera/t/MDEV-24327.cnf b/mysql-test/suite/galera/t/MDEV-24327.cnf
new file mode 100644
index 00000000000..390a9aab0f4
--- /dev/null
+++ b/mysql-test/suite/galera/t/MDEV-24327.cnf
@@ -0,0 +1,6 @@
+!include ../galera_2nodes.cnf
+
+[mysqld.1]
+log-bin=mariadb-bin
+log-slave-updates=OFF
+
diff --git a/mysql-test/suite/galera/t/MDEV-24327.test b/mysql-test/suite/galera/t/MDEV-24327.test
new file mode 100644
index 00000000000..7aeffea11fa
--- /dev/null
+++ b/mysql-test/suite/galera/t/MDEV-24327.test
@@ -0,0 +1,87 @@
+#
+# MDEV-24327 wsrep XID checkpointing order violation with log_slave_updates=OFF
+#
+# Here we have configure two node cluster with --log-bin=ON and --log-slave_-updates=OFF
+#
+# a transaction in node executes so far that it has replicated and reached
+# commit phase, We have sync point before entering commit order monitor and
+# the transaction is parked there
+#
+# Then another transaction is executed in node 2, it replicates and commits in node 2
+# and is received and applied in node 1. After applying it will remain waiting for
+# commit order monitor, as it has later seqno than the first transaction in node 1.
+#
+# control connection in node 1 waits to see the
+#
+# With the buggy version of MDEV-24327, the applier has however, already synced the
+# wsrep XID checkpoint
+#
+#
+#
+
+--source include/galera_cluster.inc
+--source include/have_innodb.inc
+--source include/have_debug_sync.inc
+--source include/galera_have_debug_sync.inc
+
+CREATE TABLE t1 (f1 INTEGER PRIMARY KEY, f2 CHAR(1));
+INSERT INTO t1 VALUES (1, 'f');
+INSERT INTO t1 VALUES (2, 'g');
+
+--connection node_1
+SET AUTOCOMMIT=ON;
+START TRANSACTION;
+
+UPDATE t1 SET f2 = '1' WHERE f1 = 1;
+
+--connect node_1a, 127.0.0.1, root, , test, $NODE_MYPORT_1
+SET SESSION wsrep_sync_wait=0;
+--connection node_1a
+--let $expected_wsrep_received = `SELECT VARIABLE_VALUE+1 FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME = 'wsrep_received'`
+--source include/galera_wait_sync_point.inc
+--source include/galera_clear_sync_point.inc
+
+# Block the commit, send the COMMIT and wait until it gets blocked
+
+--let $galera_sync_point = commit_monitor_master_enter_sync
+--source include/galera_set_sync_point.inc
+
+--connection node_1
+--send COMMIT
+
+--connection node_1a
+
+# wait for the commit to block in sync point
+
+--let $galera_sync_point = commit_monitor_master_enter_sync
+--source include/galera_wait_sync_point.inc
+--source include/galera_clear_sync_point.inc
+
+#
+# replicate non conflicting transaction from node 2
+# it will get later seqno and should sync XID checkpoint after transaction in node 1
+#
+--connection node_2
+UPDATE t1 SET f2 = '2' WHERE f1 = 2;
+
+#
+# wait until update from node 2 has been committed
+# if XID checkpointing order was violated, node 1 would crash for assert
+#
+
+--connection node_1a
+--let $wait_condition = SELECT VARIABLE_VALUE = $expected_wsrep_received FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME = 'wsrep_received'
+--source include/wait_condition.inc
+
+--let $galera_sync_point = commit_monitor_master_enter_sync
+--source include/galera_signal_sync_point.inc
+--source include/galera_clear_sync_point.inc
+
+--connection node_1
+--reap
+SELECT * FROM t1;
+--echo "node 1 is complete now"
+
+
+--connection node_2
+DROP TABLE t1;
diff --git a/mysql-test/suite/galera/t/MENT-1047.test b/mysql-test/suite/galera/t/MENT-1047.test
new file mode 100644
index 00000000000..1431818f806
--- /dev/null
+++ b/mysql-test/suite/galera/t/MENT-1047.test
@@ -0,0 +1,7 @@
+#
+# MENT-1047 - Reject XA with Galera replication
+#
+--source include/galera_cluster.inc
+
+--error ER_NOT_SUPPORTED_YET
+XA START 'trx';
diff --git a/mysql-test/suite/galera/t/galera#500.test b/mysql-test/suite/galera/t/galera#500.test
index 60f303b7103..471620b32c1 100644
--- a/mysql-test/suite/galera/t/galera#500.test
+++ b/mysql-test/suite/galera/t/galera#500.test
@@ -8,9 +8,10 @@
--source include/galera_cluster.inc
--source include/galera_have_debug_sync.inc
+# Save original auto_increment_offset values.
--let $node_1=node_1
--let $node_2=node_2
---source suite/galera/include/auto_increment_offset_save.inc
+--source include/auto_increment_offset_save.inc
# Force node_2 gcomm background thread to terminate via exception.
--connection node_2
@@ -41,4 +42,4 @@ SET SESSION wsrep_on=0;
--connection node_2
CALL mtr.add_suppression("WSREP: exception from gcomm, backend must be restarted: Gcomm backend termination was requested by setting gmcast.isolate=2.");
---source suite/galera/include/auto_increment_offset_restore.inc
+--source include/auto_increment_offset_restore.inc
diff --git a/mysql-test/suite/gcol/r/innodb_virtual_fk.result b/mysql-test/suite/gcol/r/innodb_virtual_fk.result
index 50685e04a69..68601823e31 100644
--- a/mysql-test/suite/gcol/r/innodb_virtual_fk.result
+++ b/mysql-test/suite/gcol/r/innodb_virtual_fk.result
@@ -790,3 +790,31 @@ t1 CREATE TABLE `t1` (
ALTER TABLE t1 DROP INDEX f1;
ALTER TABLE t1 DROP f3;
DROP TABLE t1;
+#
+# MDEV-24041 Generated column DELETE with FOREIGN KEY crash InnoDB
+#
+CREATE TABLE emails (
+id int,
+PRIMARY KEY (id)
+) ENGINE=InnoDB;
+CREATE TABLE email_stats (
+id int,
+email_id int,
+date_sent char(4),
+generated_email_id int as (email_id),
+PRIMARY KEY (id),
+KEY mautic_generated_sent_date_email_id (generated_email_id),
+FOREIGN KEY (email_id) REFERENCES emails (id) ON DELETE SET NULL
+) ENGINE=InnoDB;
+CREATE TABLE emails_metadata (
+email_id int,
+PRIMARY KEY (email_id),
+CONSTRAINT FK FOREIGN KEY (email_id) REFERENCES emails (id) ON DELETE CASCADE
+) ENGINE=InnoDB;
+INSERT INTO emails VALUES (1);
+INSERT INTO email_stats (id, email_id, date_sent) VALUES (1,1,'Jan');
+INSERT INTO emails_metadata VALUES (1);
+DELETE FROM emails;
+DROP TABLE email_stats;
+DROP TABLE emails_metadata;
+DROP TABLE emails;
diff --git a/mysql-test/suite/gcol/t/innodb_virtual_fk.test b/mysql-test/suite/gcol/t/innodb_virtual_fk.test
index 23d3ee97290..da20612f0a1 100644
--- a/mysql-test/suite/gcol/t/innodb_virtual_fk.test
+++ b/mysql-test/suite/gcol/t/innodb_virtual_fk.test
@@ -649,3 +649,40 @@ SHOW CREATE TABLE t1;
ALTER TABLE t1 DROP INDEX f1;
ALTER TABLE t1 DROP f3;
DROP TABLE t1;
+
+--echo #
+--echo # MDEV-24041 Generated column DELETE with FOREIGN KEY crash InnoDB
+--echo #
+
+CREATE TABLE emails (
+ id int,
+ PRIMARY KEY (id)
+) ENGINE=InnoDB;
+
+CREATE TABLE email_stats (
+ id int,
+ email_id int,
+ date_sent char(4),
+ generated_email_id int as (email_id),
+ PRIMARY KEY (id),
+ KEY mautic_generated_sent_date_email_id (generated_email_id),
+ FOREIGN KEY (email_id) REFERENCES emails (id) ON DELETE SET NULL
+) ENGINE=InnoDB;
+
+
+CREATE TABLE emails_metadata (
+ email_id int,
+ PRIMARY KEY (email_id),
+ CONSTRAINT FK FOREIGN KEY (email_id) REFERENCES emails (id) ON DELETE CASCADE
+) ENGINE=InnoDB;
+
+
+INSERT INTO emails VALUES (1);
+INSERT INTO email_stats (id, email_id, date_sent) VALUES (1,1,'Jan');
+INSERT INTO emails_metadata VALUES (1);
+
+DELETE FROM emails;
+
+DROP TABLE email_stats;
+DROP TABLE emails_metadata;
+DROP TABLE emails;
diff --git a/mysql-test/suite/innodb/r/innodb_multi_update.result b/mysql-test/suite/innodb/r/innodb_multi_update.result
index 64f9ebc2fc2..93bd4e6716c 100644
--- a/mysql-test/suite/innodb/r/innodb_multi_update.result
+++ b/mysql-test/suite/innodb/r/innodb_multi_update.result
@@ -81,4 +81,5 @@ CREATE TABLE t1(f1 INT) ENGINE=INNODB;
INSERT INTO t1 VALUES(1);
UPDATE (SELECT ((SELECT 1 FROM t1), 1) FROM t1 WHERE (SELECT 1 FROM t1)) x, (SELECT 1) AS d SET d.f1 = 1;
ERROR 21000: Operand should contain 1 column(s)
+UPDATE (SELECT ((SELECT 1 FROM t1),1) = (1,1) FROM t1 WHERE (SELECT 1 FROM t1)) x, t1 AS d SET d.f1 = 1;
DROP TABLE t1;
diff --git a/mysql-test/suite/innodb/r/innodb_stats.result b/mysql-test/suite/innodb/r/innodb_stats.result
index eadce75318b..d2c3bd0127e 100644
--- a/mysql-test/suite/innodb/r/innodb_stats.result
+++ b/mysql-test/suite/innodb/r/innodb_stats.result
@@ -4,7 +4,6 @@ dummy INSERT, the table should be empty
dummy INSERT, the table should be empty
ANALYZE TABLE test_innodb_stats;
Table Op Msg_type Msg_text
-test.test_innodb_stats analyze status Engine-independent statistics collected
test.test_innodb_stats analyze status OK
SELECT
stat_name,
@@ -57,7 +56,6 @@ TRUNCATE TABLE test_innodb_stats;
INSERT INTO test_innodb_stats (a) VALUES (1);
ANALYZE TABLE test_innodb_stats;
Table Op Msg_type Msg_text
-test.test_innodb_stats analyze status Engine-independent statistics collected
test.test_innodb_stats analyze status OK
SELECT
stat_name,
@@ -110,7 +108,6 @@ TRUNCATE TABLE test_innodb_stats;
INSERT INTO test_innodb_stats (a) VALUES (1), (1);
ANALYZE TABLE test_innodb_stats;
Table Op Msg_type Msg_text
-test.test_innodb_stats analyze status Engine-independent statistics collected
test.test_innodb_stats analyze status OK
SELECT
stat_name,
@@ -163,7 +160,6 @@ TRUNCATE TABLE test_innodb_stats;
INSERT INTO test_innodb_stats (a) VALUES (1), (1), (1);
ANALYZE TABLE test_innodb_stats;
Table Op Msg_type Msg_text
-test.test_innodb_stats analyze status Engine-independent statistics collected
test.test_innodb_stats analyze status OK
SELECT
stat_name,
@@ -216,7 +212,6 @@ TRUNCATE TABLE test_innodb_stats;
INSERT INTO test_innodb_stats (a) VALUES (1), (1), (1), (1), (1), (1), (1), (1), (1), (1);
ANALYZE TABLE test_innodb_stats;
Table Op Msg_type Msg_text
-test.test_innodb_stats analyze status Engine-independent statistics collected
test.test_innodb_stats analyze status OK
SELECT
stat_name,
@@ -269,7 +264,6 @@ TRUNCATE TABLE test_innodb_stats;
INSERT INTO test_innodb_stats (a) VALUES (1), (2);
ANALYZE TABLE test_innodb_stats;
Table Op Msg_type Msg_text
-test.test_innodb_stats analyze status Engine-independent statistics collected
test.test_innodb_stats analyze status OK
SELECT
stat_name,
@@ -322,7 +316,6 @@ TRUNCATE TABLE test_innodb_stats;
INSERT INTO test_innodb_stats (a) VALUES (1), (1), (2);
ANALYZE TABLE test_innodb_stats;
Table Op Msg_type Msg_text
-test.test_innodb_stats analyze status Engine-independent statistics collected
test.test_innodb_stats analyze status OK
SELECT
stat_name,
@@ -375,7 +368,6 @@ TRUNCATE TABLE test_innodb_stats;
INSERT INTO test_innodb_stats (a) VALUES (1), (2), (3);
ANALYZE TABLE test_innodb_stats;
Table Op Msg_type Msg_text
-test.test_innodb_stats analyze status Engine-independent statistics collected
test.test_innodb_stats analyze status OK
SELECT
stat_name,
@@ -428,7 +420,6 @@ TRUNCATE TABLE test_innodb_stats;
INSERT INTO test_innodb_stats (a) VALUES (1), (1), (2), (3), (3);
ANALYZE TABLE test_innodb_stats;
Table Op Msg_type Msg_text
-test.test_innodb_stats analyze status Engine-independent statistics collected
test.test_innodb_stats analyze status OK
SELECT
stat_name,
@@ -481,7 +472,6 @@ TRUNCATE TABLE test_innodb_stats;
INSERT INTO test_innodb_stats (a) VALUES (1), (2), (3), (4), (5), (1), (2), (3), (4), (5);
ANALYZE TABLE test_innodb_stats;
Table Op Msg_type Msg_text
-test.test_innodb_stats analyze status Engine-independent statistics collected
test.test_innodb_stats analyze status OK
SELECT
stat_name,
diff --git a/mysql-test/suite/innodb/t/innodb_multi_update.test b/mysql-test/suite/innodb/t/innodb_multi_update.test
index 8d5283a9ed5..74a7aea7d13 100644
--- a/mysql-test/suite/innodb/t/innodb_multi_update.test
+++ b/mysql-test/suite/innodb/t/innodb_multi_update.test
@@ -35,4 +35,5 @@ CREATE TABLE t1(f1 INT) ENGINE=INNODB;
INSERT INTO t1 VALUES(1);
--error ER_OPERAND_COLUMNS
UPDATE (SELECT ((SELECT 1 FROM t1), 1) FROM t1 WHERE (SELECT 1 FROM t1)) x, (SELECT 1) AS d SET d.f1 = 1;
+UPDATE (SELECT ((SELECT 1 FROM t1),1) = (1,1) FROM t1 WHERE (SELECT 1 FROM t1)) x, t1 AS d SET d.f1 = 1;
DROP TABLE t1;
diff --git a/mysql-test/suite/innodb/t/innodb_stats.test b/mysql-test/suite/innodb/t/innodb_stats.test
index 09515ec9720..1a962e3568e 100644
--- a/mysql-test/suite/innodb/t/innodb_stats.test
+++ b/mysql-test/suite/innodb/t/innodb_stats.test
@@ -10,7 +10,7 @@
DROP TABLE IF EXISTS test_innodb_stats;
set @save_use_stat_tables= @@use_stat_tables;
-set @@use_stat_tables= COMPLEMENTARY;
+set @@use_stat_tables= NEVER;
CREATE TABLE test_innodb_stats (
a INT,
diff --git a/mysql-test/suite/plugins/r/server_audit.result b/mysql-test/suite/plugins/r/server_audit.result
index ab726a0e22f..6ded4f687a6 100644
--- a/mysql-test/suite/plugins/r/server_audit.result
+++ b/mysql-test/suite/plugins/r/server_audit.result
@@ -452,8 +452,8 @@ TIME,HOSTNAME,plug,localhost,ID,0,DISCONNECT,,,0
TIME,HOSTNAME,root,localhost,ID,ID,WRITE,mysql,proxies_priv,
TIME,HOSTNAME,root,localhost,ID,ID,WRITE,mysql,global_priv,
TIME,HOSTNAME,root,localhost,ID,ID,QUERY,sa_db,'GRANT PROXY ON plug_dest TO plug',0
-TIME,HOSTNAME,plug,localhost,ID,0,PROXY_CONNECT,test,`plug_dest`@`%`,0
TIME,HOSTNAME,plug,localhost,ID,0,CONNECT,test,,0
+TIME,HOSTNAME,plug,localhost,ID,0,PROXY_CONNECT,test,`plug_dest`@`%`,0
TIME,HOSTNAME,plug,localhost,ID,0,DISCONNECT,test,,0
TIME,HOSTNAME,root,localhost,ID,ID,WRITE,mysql,db,
TIME,HOSTNAME,root,localhost,ID,ID,WRITE,mysql,tables_priv,
diff --git a/mysql-test/suite/rpl/include/rpl_semi_sync.inc b/mysql-test/suite/rpl/include/rpl_semi_sync.inc
index 393b49372e1..c3cd918b5fc 100644
--- a/mysql-test/suite/rpl/include/rpl_semi_sync.inc
+++ b/mysql-test/suite/rpl/include/rpl_semi_sync.inc
@@ -9,7 +9,6 @@ source include/have_innodb.inc;
source include/master-slave.inc;
let $engine_type= InnoDB;
-#let $engine_type= MyISAM;
# Suppress warnings that might be generated during the test
connection master;
@@ -94,7 +93,6 @@ enable_query_log;
echo [ status of semi-sync on master should be OFF ];
show status like 'Rpl_semi_sync_master_clients';
show status like 'Rpl_semi_sync_master_status';
---replace_result 305 304
show status like 'Rpl_semi_sync_master_yes_tx';
# reset master to make sure the following test will start with a clean environment
@@ -201,16 +199,23 @@ connection slave;
source include/stop_slave.inc;
connection master;
+--source include/kill_binlog_dump_threads.inc
set global rpl_semi_sync_master_timeout= 5000;
# The first semi-sync check should be on because after slave stop,
# there are no transactions on the master.
echo [ master status should be ON ];
-show status like 'Rpl_semi_sync_master_status';
+
+let $status_var= Rpl_semi_sync_master_status;
+let $status_var_value= ON;
+source include/wait_for_status_var.inc;
+
+let $status_var= Rpl_semi_sync_master_clients;
+let $status_var_value= 0;
+source include/wait_for_status_var.inc;
+
show status like 'Rpl_semi_sync_master_no_tx';
---replace_result 305 304
show status like 'Rpl_semi_sync_master_yes_tx';
-show status like 'Rpl_semi_sync_master_clients';
echo [ semi-sync replication of these transactions will fail ];
insert into t1 values (500);
@@ -225,7 +230,6 @@ source include/wait_for_status_var.inc;
echo [ master status should be OFF ];
show status like 'Rpl_semi_sync_master_status';
show status like 'Rpl_semi_sync_master_no_tx';
---replace_result 305 304
show status like 'Rpl_semi_sync_master_yes_tx';
# Semi-sync status on master is now OFF, so all these transactions
@@ -246,7 +250,6 @@ insert into t1 values (100);
echo [ master status should be OFF ];
show status like 'Rpl_semi_sync_master_status';
show status like 'Rpl_semi_sync_master_no_tx';
---replace_result 305 304
show status like 'Rpl_semi_sync_master_yes_tx';
--echo #
@@ -274,9 +277,11 @@ connection master;
# The master semi-sync status should be on again after slave catches up.
echo [ master status should be ON again after slave catches up ];
-show status like 'Rpl_semi_sync_master_status';
+
+let $status_var= Rpl_semi_sync_master_status;
+let $status_var_value= ON;
+source include/wait_for_status_var.inc;
show status like 'Rpl_semi_sync_master_no_tx';
---replace_result 305 304
show status like 'Rpl_semi_sync_master_yes_tx';
show status like 'Rpl_semi_sync_master_clients';
@@ -332,11 +337,7 @@ replace_result $engine_type ENGINE_TYPE;
eval create table t1 (a int) engine = $engine_type;
drop table t1;
-##show status like 'Rpl_semi_sync_master_status';
-
sync_slave_with_master;
---replace_column 2 #
-show status like 'Rpl_relay%';
echo [ test reset master ];
connection master;
@@ -353,19 +354,7 @@ source include/stop_slave.inc;
reset slave;
# Kill the dump thread on master for previous slave connection and
-# wait for it to exit
-connection master;
-let $_tid= `select id from information_schema.processlist where command = 'Binlog Dump' limit 1`;
-if ($_tid)
-{
- --replace_result $_tid _tid
- eval kill query $_tid;
-
- # After dump thread exit, Rpl_semi_sync_master_clients will be 0
- let $status_var= Rpl_semi_sync_master_clients;
- let $status_var_value= 0;
- source include/wait_for_status_var.inc;
-}
+--source include/kill_binlog_dump_threads.inc
connection slave;
source include/start_slave.inc;
@@ -404,17 +393,7 @@ connection master;
reset master;
# Kill the dump thread on master for previous slave connection and wait for it to exit
-let $_tid= `select id from information_schema.processlist where command = 'Binlog Dump' limit 1`;
-if ($_tid)
-{
- --replace_result $_tid _tid
- eval kill query $_tid;
-
- # After dump thread exit, Rpl_semi_sync_master_clients will be 0
- let $status_var= Rpl_semi_sync_master_clients;
- let $status_var_value= 0;
- source include/wait_for_status_var.inc;
-}
+--source include/kill_binlog_dump_threads.inc
# Do not binlog the following statement because it will generate
# different events for ROW and STATEMENT format
@@ -459,21 +438,16 @@ SHOW STATUS LIKE 'Rpl_semi_sync_slave_status';
connection master;
# Kill the dump thread on master for previous slave connection and wait for it to exit
-let $_tid= `select id from information_schema.processlist where command = 'Binlog Dump' limit 1`;
-if ($_tid)
-{
- --replace_result $_tid _tid
- eval kill query $_tid;
-
- # After dump thread exit, Rpl_semi_sync_master_clients will be 0
- let $status_var= Rpl_semi_sync_master_clients;
- let $status_var_value= 0;
- source include/wait_for_status_var.inc;
-}
+--source include/kill_binlog_dump_threads.inc
echo [ Semi-sync status on master should be ON ];
-show status like 'Rpl_semi_sync_master_clients';
+let $status_var= Rpl_semi_sync_master_clients;
+let $status_var_value= 0;
+source include/wait_for_status_var.inc;
show status like 'Rpl_semi_sync_master_status';
+let $status_var= Rpl_semi_sync_master_status;
+let $status_var_value= ON;
+source include/wait_for_status_var.inc;
set global rpl_semi_sync_master_enabled= 0;
connection slave;
diff --git a/mysql-test/suite/rpl/r/rpl_drop_temp_table_invaid_lex.result b/mysql-test/suite/rpl/r/rpl_drop_temp_table_invalid_lex.result
index 49e9195ed35..f3c4cb4def2 100644
--- a/mysql-test/suite/rpl/r/rpl_drop_temp_table_invaid_lex.result
+++ b/mysql-test/suite/rpl/r/rpl_drop_temp_table_invalid_lex.result
@@ -4,5 +4,8 @@ connect con1,localhost,root,,;
CREATE TEMPORARY TABLE tmp (a INT);
CREATE TABLE non_existing_db.t SELECT 1 AS b;
disconnect con1;
-connection default;
+connection master;
+connection slave;
+connection slave;
+include/assert.inc ["Slave_open_temp_tables count should be 0"]
include/rpl_end.inc
diff --git a/mysql-test/suite/rpl/r/rpl_semi_sync.result b/mysql-test/suite/rpl/r/rpl_semi_sync.result
index 106efb555d3..d18bd1efda7 100644
--- a/mysql-test/suite/rpl/r/rpl_semi_sync.result
+++ b/mysql-test/suite/rpl/r/rpl_semi_sync.result
@@ -164,20 +164,15 @@ connection slave;
connection slave;
include/stop_slave.inc
connection master;
+include/kill_binlog_dump_threads.inc
set global rpl_semi_sync_master_timeout= 5000;
[ master status should be ON ]
-show status like 'Rpl_semi_sync_master_status';
-Variable_name Value
-Rpl_semi_sync_master_status ON
show status like 'Rpl_semi_sync_master_no_tx';
Variable_name Value
Rpl_semi_sync_master_no_tx 0
show status like 'Rpl_semi_sync_master_yes_tx';
Variable_name Value
Rpl_semi_sync_master_yes_tx 14
-show status like 'Rpl_semi_sync_master_clients';
-Variable_name Value
-Rpl_semi_sync_master_clients 0
[ semi-sync replication of these transactions will fail ]
insert into t1 values (500);
[ master status should be OFF ]
@@ -235,9 +230,6 @@ max(a)
500
connection master;
[ master status should be ON again after slave catches up ]
-show status like 'Rpl_semi_sync_master_status';
-Variable_name Value
-Rpl_semi_sync_master_status ON
show status like 'Rpl_semi_sync_master_no_tx';
Variable_name Value
Rpl_semi_sync_master_no_tx 12
@@ -304,8 +296,6 @@ connection master;
create table t1 (a int) engine = ENGINE_TYPE;
drop table t1;
connection slave;
-show status like 'Rpl_relay%';
-Variable_name Value
[ test reset master ]
connection master;
reset master;
@@ -321,7 +311,7 @@ Rpl_semi_sync_master_yes_tx 0
connection slave;
include/stop_slave.inc
reset slave;
-connection master;
+include/kill_binlog_dump_threads.inc
connection slave;
include/start_slave.inc
connection master;
@@ -353,6 +343,7 @@ include/stop_slave.inc
reset slave;
connection master;
reset master;
+include/kill_binlog_dump_threads.inc
set sql_log_bin=0;
grant replication slave on *.* to rpl@127.0.0.1 identified by 'rpl_password';
flush privileges;
@@ -403,10 +394,8 @@ SHOW STATUS LIKE 'Rpl_semi_sync_slave_status';
Variable_name Value
Rpl_semi_sync_slave_status OFF
connection master;
+include/kill_binlog_dump_threads.inc
[ Semi-sync status on master should be ON ]
-show status like 'Rpl_semi_sync_master_clients';
-Variable_name Value
-Rpl_semi_sync_master_clients 0
show status like 'Rpl_semi_sync_master_status';
Variable_name Value
Rpl_semi_sync_master_status ON
diff --git a/mysql-test/suite/rpl/r/rpl_semi_sync_after_sync.result b/mysql-test/suite/rpl/r/rpl_semi_sync_after_sync.result
index c61340f3967..f2240817489 100644
--- a/mysql-test/suite/rpl/r/rpl_semi_sync_after_sync.result
+++ b/mysql-test/suite/rpl/r/rpl_semi_sync_after_sync.result
@@ -165,20 +165,15 @@ connection slave;
connection slave;
include/stop_slave.inc
connection master;
+include/kill_binlog_dump_threads.inc
set global rpl_semi_sync_master_timeout= 5000;
[ master status should be ON ]
-show status like 'Rpl_semi_sync_master_status';
-Variable_name Value
-Rpl_semi_sync_master_status ON
show status like 'Rpl_semi_sync_master_no_tx';
Variable_name Value
Rpl_semi_sync_master_no_tx 0
show status like 'Rpl_semi_sync_master_yes_tx';
Variable_name Value
Rpl_semi_sync_master_yes_tx 16
-show status like 'Rpl_semi_sync_master_clients';
-Variable_name Value
-Rpl_semi_sync_master_clients 0
[ semi-sync replication of these transactions will fail ]
insert into t1 values (500);
[ master status should be OFF ]
@@ -236,9 +231,6 @@ max(a)
500
connection master;
[ master status should be ON again after slave catches up ]
-show status like 'Rpl_semi_sync_master_status';
-Variable_name Value
-Rpl_semi_sync_master_status ON
show status like 'Rpl_semi_sync_master_no_tx';
Variable_name Value
Rpl_semi_sync_master_no_tx 12
@@ -305,8 +297,6 @@ connection master;
create table t1 (a int) engine = ENGINE_TYPE;
drop table t1;
connection slave;
-show status like 'Rpl_relay%';
-Variable_name Value
[ test reset master ]
connection master;
reset master;
@@ -322,7 +312,7 @@ Rpl_semi_sync_master_yes_tx 0
connection slave;
include/stop_slave.inc
reset slave;
-connection master;
+include/kill_binlog_dump_threads.inc
connection slave;
include/start_slave.inc
connection master;
@@ -354,6 +344,7 @@ include/stop_slave.inc
reset slave;
connection master;
reset master;
+include/kill_binlog_dump_threads.inc
set sql_log_bin=0;
grant replication slave on *.* to rpl@127.0.0.1 identified by 'rpl_password';
flush privileges;
@@ -404,10 +395,8 @@ SHOW STATUS LIKE 'Rpl_semi_sync_slave_status';
Variable_name Value
Rpl_semi_sync_slave_status OFF
connection master;
+include/kill_binlog_dump_threads.inc
[ Semi-sync status on master should be ON ]
-show status like 'Rpl_semi_sync_master_clients';
-Variable_name Value
-Rpl_semi_sync_master_clients 0
show status like 'Rpl_semi_sync_master_status';
Variable_name Value
Rpl_semi_sync_master_status ON
diff --git a/mysql-test/suite/rpl/r/rpl_semi_sync_after_sync_row.result b/mysql-test/suite/rpl/r/rpl_semi_sync_after_sync_row.result
index 6a23f24b66d..fcced801d65 100644
--- a/mysql-test/suite/rpl/r/rpl_semi_sync_after_sync_row.result
+++ b/mysql-test/suite/rpl/r/rpl_semi_sync_after_sync_row.result
@@ -165,20 +165,15 @@ connection slave;
connection slave;
include/stop_slave.inc
connection master;
+include/kill_binlog_dump_threads.inc
set global rpl_semi_sync_master_timeout= 5000;
[ master status should be ON ]
-show status like 'Rpl_semi_sync_master_status';
-Variable_name Value
-Rpl_semi_sync_master_status ON
show status like 'Rpl_semi_sync_master_no_tx';
Variable_name Value
Rpl_semi_sync_master_no_tx 0
show status like 'Rpl_semi_sync_master_yes_tx';
Variable_name Value
Rpl_semi_sync_master_yes_tx 14
-show status like 'Rpl_semi_sync_master_clients';
-Variable_name Value
-Rpl_semi_sync_master_clients 0
[ semi-sync replication of these transactions will fail ]
insert into t1 values (500);
[ master status should be OFF ]
@@ -236,9 +231,6 @@ max(a)
500
connection master;
[ master status should be ON again after slave catches up ]
-show status like 'Rpl_semi_sync_master_status';
-Variable_name Value
-Rpl_semi_sync_master_status ON
show status like 'Rpl_semi_sync_master_no_tx';
Variable_name Value
Rpl_semi_sync_master_no_tx 12
@@ -305,8 +297,6 @@ connection master;
create table t1 (a int) engine = ENGINE_TYPE;
drop table t1;
connection slave;
-show status like 'Rpl_relay%';
-Variable_name Value
[ test reset master ]
connection master;
reset master;
@@ -322,7 +312,7 @@ Rpl_semi_sync_master_yes_tx 0
connection slave;
include/stop_slave.inc
reset slave;
-connection master;
+include/kill_binlog_dump_threads.inc
connection slave;
include/start_slave.inc
connection master;
@@ -354,6 +344,7 @@ include/stop_slave.inc
reset slave;
connection master;
reset master;
+include/kill_binlog_dump_threads.inc
set sql_log_bin=0;
grant replication slave on *.* to rpl@127.0.0.1 identified by 'rpl_password';
flush privileges;
@@ -404,10 +395,8 @@ SHOW STATUS LIKE 'Rpl_semi_sync_slave_status';
Variable_name Value
Rpl_semi_sync_slave_status OFF
connection master;
+include/kill_binlog_dump_threads.inc
[ Semi-sync status on master should be ON ]
-show status like 'Rpl_semi_sync_master_clients';
-Variable_name Value
-Rpl_semi_sync_master_clients 0
show status like 'Rpl_semi_sync_master_status';
Variable_name Value
Rpl_semi_sync_master_status ON
diff --git a/mysql-test/suite/rpl/t/rpl_drop_temp_table_invaid_lex.test b/mysql-test/suite/rpl/t/rpl_drop_temp_table_invalid_lex.test
index 2d3031da544..475afef12fc 100644
--- a/mysql-test/suite/rpl/t/rpl_drop_temp_table_invaid_lex.test
+++ b/mysql-test/suite/rpl/t/rpl_drop_temp_table_invalid_lex.test
@@ -17,15 +17,25 @@
# MDEV-19716: ASAN use-after-poison in Query_log_event::Query_log_event /
# THD::log_events_and_free_tmp_shares
+--source include/have_binlog_format_mixed_or_statement.inc
--source include/master-slave.inc
--connect (con1,localhost,root,,)
-
CREATE TEMPORARY TABLE tmp (a INT);
--send CREATE TABLE non_existing_db.t SELECT 1 AS b
--disconnect con1
+--source include/wait_until_disconnected.inc
+
+--connection master
+--let $wait_binlog_event= DROP
+--source include/wait_for_binlog_event.inc
+sync_slave_with_master;
---connection default
+--connection slave
+--let $open_temp_tbl_count=query_get_value(show status like 'Slave_open_temp_tables', Value, 1)
+--let $assert_cond= "open_temp_tbl_count" = 0
+--let $assert_text= "Slave_open_temp_tables count should be 0"
+--source include/assert.inc
--source include/rpl_end.inc
diff --git a/mysql-test/suite/rpl/t/rpl_parallel_retry.test b/mysql-test/suite/rpl/t/rpl_parallel_retry.test
index 55da54e3c8c..97a3a709eb5 100644
--- a/mysql-test/suite/rpl/t/rpl_parallel_retry.test
+++ b/mysql-test/suite/rpl/t/rpl_parallel_retry.test
@@ -436,6 +436,9 @@ SET @@DEBUG_SYNC='now SIGNAL proceed_by_1000';
--connection spoiler_21
ROLLBACK;
+--let $wait_condition= SELECT count(*)=1 FROM information_schema.processlist WHERE state LIKE '%debug sync point%';
+--source include/wait_condition.inc
+
--echo # Release the 2nd worker to proceed
--connection spoiler_22
ROLLBACK;
diff --git a/mysql-test/suite/sql_sequence/other.result b/mysql-test/suite/sql_sequence/other.result
index abc101b3c00..5226ce78789 100644
--- a/mysql-test/suite/sql_sequence/other.result
+++ b/mysql-test/suite/sql_sequence/other.result
@@ -300,4 +300,62 @@ update t1 set p_first_name='Yunxi' where p_id=1;
drop view v2;
drop table t1,t2;
drop sequence s1;
+#
+# MDEV-19273:Server crash in MDL_ticket::has_stronger_or_equal_type or
+# Assertion `thd->mdl_context.is_lock_owner(MDL_key::TABLE,
+# table->db.str, table->table_name.str, MDL_SHARED)' failed
+# in mysql_rm_table_no_locks
+#
+CREATE TABLE t1 (a INT);
+CREATE TEMPORARY TABLE tmp (b INT);
+LOCK TABLE t1 READ;
+DROP SEQUENCE tmp;
+ERROR 42S02: Unknown SEQUENCE: 'test.tmp'
+DROP TEMPORARY SEQUENCE tmp;
+ERROR 42S02: Unknown SEQUENCE: 'test.tmp'
+DROP SEQUENCE t1;
+ERROR HY000: Table 't1' was locked with a READ lock and can't be updated
+DROP TEMPORARY SEQUENCE t1;
+ERROR 42S02: Unknown SEQUENCE: 'test.t1'
+UNLOCK TABLES;
+DROP SEQUENCE t1;
+ERROR 42S02: 'test.t1' is not a SEQUENCE
+DROP TEMPORARY SEQUENCE t1;
+ERROR 42S02: Unknown SEQUENCE: 'test.t1'
+DROP TABLE t1;
+CREATE TABLE t (a INT);
+CREATE SEQUENCE s;
+LOCK TABLE t WRITE;
+DROP SEQUENCE s;
+ERROR HY000: Table 's' was not locked with LOCK TABLES
+DROP TEMPORARY SEQUENCE s;
+ERROR 42S02: Unknown SEQUENCE: 'test.s'
+UNLOCK TABLES;
+CREATE TEMPORARY SEQUENCE s;
+LOCK TABLE t WRITE;
+DROP TEMPORARY SEQUENCE s;
+UNLOCK TABLES;
+DROP SEQUENCE s;
+create table s(a INT);
+CREATE TEMPORARY TABLE s (f INT);
+LOCK TABLE t WRITE;
+DROP TEMPORARY TABLE s;
+CREATE TEMPORARY TABLE s (f INT);
+DROP TABLE s;
+DROP TABLE s;
+ERROR HY000: Table 's' was not locked with LOCK TABLES
+UNLOCK TABLES;
+DROP TABLE s;
+CREATE VIEW v1 as SELECT * FROM t;
+CREATE SEQUENCE s;
+DROP SEQUENCE IF EXISTS v1;
+Warnings:
+Note 1965 'test.v1' is a view
+DROP VIEW IF EXISTS s;
+Warnings:
+Warning 1347 'test.s' is not of type 'VIEW'
+Note 4092 Unknown VIEW: 'test.s'
+DROP VIEW v1;
+DROP SEQUENCE s;
+DROP TABLE t;
# End of 10.3 tests
diff --git a/mysql-test/suite/sql_sequence/other.test b/mysql-test/suite/sql_sequence/other.test
index 70c4efa40e5..639cc5c36b0 100644
--- a/mysql-test/suite/sql_sequence/other.test
+++ b/mysql-test/suite/sql_sequence/other.test
@@ -315,4 +315,66 @@ drop view v2;
drop table t1,t2;
drop sequence s1;
+--echo #
+--echo # MDEV-19273:Server crash in MDL_ticket::has_stronger_or_equal_type or
+--echo # Assertion `thd->mdl_context.is_lock_owner(MDL_key::TABLE,
+--echo # table->db.str, table->table_name.str, MDL_SHARED)' failed
+--echo # in mysql_rm_table_no_locks
+--echo #
+
+CREATE TABLE t1 (a INT);
+CREATE TEMPORARY TABLE tmp (b INT);
+LOCK TABLE t1 READ;
+--error ER_UNKNOWN_SEQUENCES
+DROP SEQUENCE tmp;
+--error ER_UNKNOWN_SEQUENCES
+DROP TEMPORARY SEQUENCE tmp;
+--error ER_TABLE_NOT_LOCKED_FOR_WRITE
+DROP SEQUENCE t1;
+--error ER_UNKNOWN_SEQUENCES
+DROP TEMPORARY SEQUENCE t1;
+UNLOCK TABLES;
+--error ER_NOT_SEQUENCE2
+DROP SEQUENCE t1;
+--error ER_UNKNOWN_SEQUENCES
+DROP TEMPORARY SEQUENCE t1;
+
+# Cleanup
+DROP TABLE t1;
+
+
+CREATE TABLE t (a INT);
+CREATE SEQUENCE s;
+LOCK TABLE t WRITE;
+--error ER_TABLE_NOT_LOCKED
+DROP SEQUENCE s;
+--error ER_UNKNOWN_SEQUENCES
+DROP TEMPORARY SEQUENCE s;
+UNLOCK TABLES;
+CREATE TEMPORARY SEQUENCE s;
+LOCK TABLE t WRITE;
+DROP TEMPORARY SEQUENCE s;
+UNLOCK TABLES;
+DROP SEQUENCE s;
+
+create table s(a INT);
+CREATE TEMPORARY TABLE s (f INT);
+LOCK TABLE t WRITE;
+DROP TEMPORARY TABLE s;
+CREATE TEMPORARY TABLE s (f INT);
+DROP TABLE s;
+--error ER_TABLE_NOT_LOCKED
+DROP TABLE s;
+UNLOCK TABLES;
+DROP TABLE s;
+
+CREATE VIEW v1 as SELECT * FROM t;
+CREATE SEQUENCE s;
+
+DROP SEQUENCE IF EXISTS v1;
+DROP VIEW IF EXISTS s;
+
+DROP VIEW v1;
+DROP SEQUENCE s;
+DROP TABLE t;
--echo # End of 10.3 tests
diff --git a/mysql-test/suite/sys_vars/r/max_sort_length_basic.result b/mysql-test/suite/sys_vars/r/max_sort_length_basic.result
deleted file mode 100644
index b48b045897c..00000000000
--- a/mysql-test/suite/sys_vars/r/max_sort_length_basic.result
+++ /dev/null
@@ -1,199 +0,0 @@
-SET @start_global_value = @@global.max_sort_length;
-SELECT @start_global_value;
-@start_global_value
-1024
-SET @start_session_value = @@session.max_sort_length;
-SELECT @start_session_value;
-@start_session_value
-1024
-'#--------------------FN_DYNVARS_084_01-------------------------#'
-SET @@global.max_sort_length = 1000;
-SET @@global.max_sort_length = DEFAULT;
-SELECT @@global.max_sort_length;
-@@global.max_sort_length
-1024
-SET @@session.max_sort_length = 1000;
-SET @@session.max_sort_length = DEFAULT;
-SELECT @@session.max_sort_length;
-@@session.max_sort_length
-1024
-'#--------------------FN_DYNVARS_084_02-------------------------#'
-SET @@global.max_sort_length = DEFAULT;
-SELECT @@global.max_sort_length = 1024;
-@@global.max_sort_length = 1024
-1
-SET @@session.max_sort_length = DEFAULT;
-SELECT @@session.max_sort_length = 1024;
-@@session.max_sort_length = 1024
-1
-'#--------------------FN_DYNVARS_084_03-------------------------#'
-SET @@global.max_sort_length = 8;
-SELECT @@global.max_sort_length;
-@@global.max_sort_length
-8
-SET @@global.max_sort_length = 9;
-SELECT @@global.max_sort_length;
-@@global.max_sort_length
-9
-SET @@global.max_sort_length = 8388608;
-SELECT @@global.max_sort_length;
-@@global.max_sort_length
-8388608
-SET @@global.max_sort_length = 8388607;
-SELECT @@global.max_sort_length;
-@@global.max_sort_length
-8388607
-SET @@global.max_sort_length = 65536;
-SELECT @@global.max_sort_length;
-@@global.max_sort_length
-65536
-'#--------------------FN_DYNVARS_084_04-------------------------#'
-SET @@session.max_sort_length = 8;
-SELECT @@session.max_sort_length;
-@@session.max_sort_length
-8
-SET @@session.max_sort_length = 9;
-SELECT @@session.max_sort_length;
-@@session.max_sort_length
-9
-SET @@session.max_sort_length = 8388608;
-SELECT @@session.max_sort_length;
-@@session.max_sort_length
-8388608
-SET @@session.max_sort_length = 8388607;
-SELECT @@session.max_sort_length;
-@@session.max_sort_length
-8388607
-SET @@session.max_sort_length = 65536;
-SELECT @@session.max_sort_length;
-@@session.max_sort_length
-65536
-'#------------------FN_DYNVARS_084_05-----------------------#'
-SET @@global.max_sort_length = -1024;
-Warnings:
-Warning 1292 Truncated incorrect max_sort_length value: '-1024'
-SELECT @@global.max_sort_length;
-@@global.max_sort_length
-8
-SET @@global.max_sort_length = 3;
-Warnings:
-Warning 1292 Truncated incorrect max_sort_length value: '3'
-SELECT @@global.max_sort_length;
-@@global.max_sort_length
-8
-SET @@global.max_sort_length = 8388609;
-Warnings:
-Warning 1292 Truncated incorrect max_sort_length value: '8388609'
-SELECT @@global.max_sort_length;
-@@global.max_sort_length
-8388608
-SET @@global.max_sort_length = 0;
-Warnings:
-Warning 1292 Truncated incorrect max_sort_length value: '0'
-SELECT @@global.max_sort_length;
-@@global.max_sort_length
-8
-SET @@global.max_sort_length = 65530.34;
-ERROR 42000: Incorrect argument type to variable 'max_sort_length'
-SELECT @@global.max_sort_length;
-@@global.max_sort_length
-8
-SET @@global.max_sort_length = test;
-ERROR 42000: Incorrect argument type to variable 'max_sort_length'
-SELECT @@global.max_sort_length;
-@@global.max_sort_length
-8
-SET @@session.max_sort_length = 8388610;
-Warnings:
-Warning 1292 Truncated incorrect max_sort_length value: '8388610'
-SELECT @@session.max_sort_length;
-@@session.max_sort_length
-8388608
-SET @@session.max_sort_length = -1;
-Warnings:
-Warning 1292 Truncated incorrect max_sort_length value: '-1'
-SELECT @@session.max_sort_length;
-@@session.max_sort_length
-8
-SET @@session.max_sort_length = 3;
-Warnings:
-Warning 1292 Truncated incorrect max_sort_length value: '3'
-SELECT @@session.max_sort_length;
-@@session.max_sort_length
-8
-SET @@session.max_sort_length = 0;
-Warnings:
-Warning 1292 Truncated incorrect max_sort_length value: '0'
-SELECT @@session.max_sort_length;
-@@session.max_sort_length
-8
-SET @@session.max_sort_length = 65530.34;
-ERROR 42000: Incorrect argument type to variable 'max_sort_length'
-SET @@session.max_sort_length = 10737418241;
-Warnings:
-Warning 1292 Truncated incorrect max_sort_length value: '10737418241'
-SELECT @@session.max_sort_length;
-@@session.max_sort_length
-8388608
-SET @@session.max_sort_length = test;
-ERROR 42000: Incorrect argument type to variable 'max_sort_length'
-SELECT @@session.max_sort_length;
-@@session.max_sort_length
-8388608
-'#------------------FN_DYNVARS_084_06-----------------------#'
-SELECT @@global.max_sort_length = VARIABLE_VALUE
-FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES
-WHERE VARIABLE_NAME='max_sort_length';
-@@global.max_sort_length = VARIABLE_VALUE
-1
-'#------------------FN_DYNVARS_084_07-----------------------#'
-SELECT @@session.max_sort_length = VARIABLE_VALUE
-FROM INFORMATION_SCHEMA.SESSION_VARIABLES
-WHERE VARIABLE_NAME='max_sort_length';
-@@session.max_sort_length = VARIABLE_VALUE
-1
-'#------------------FN_DYNVARS_084_08-----------------------#'
-SET @@global.max_sort_length = TRUE;
-Warnings:
-Warning 1292 Truncated incorrect max_sort_length value: '1'
-SELECT @@global.max_sort_length;
-@@global.max_sort_length
-8
-SET @@global.max_sort_length = FALSE;
-Warnings:
-Warning 1292 Truncated incorrect max_sort_length value: '0'
-SELECT @@global.max_sort_length;
-@@global.max_sort_length
-8
-'#---------------------FN_DYNVARS_084_09----------------------#'
-SET @@global.max_sort_length = 2048;
-SELECT @@max_sort_length = @@global.max_sort_length;
-@@max_sort_length = @@global.max_sort_length
-0
-'#---------------------FN_DYNVARS_084_10----------------------#'
-SET @@max_sort_length = 100000;
-SELECT @@max_sort_length = @@local.max_sort_length;
-@@max_sort_length = @@local.max_sort_length
-1
-SELECT @@local.max_sort_length = @@session.max_sort_length;
-@@local.max_sort_length = @@session.max_sort_length
-1
-'#---------------------FN_DYNVARS_084_11----------------------#'
-SET max_sort_length = 1024;
-SELECT @@max_sort_length;
-@@max_sort_length
-1024
-SELECT local.max_sort_length;
-ERROR 42S02: Unknown table 'local' in field list
-SELECT session.max_sort_length;
-ERROR 42S02: Unknown table 'session' in field list
-SELECT max_sort_length = @@session.max_sort_length;
-ERROR 42S22: Unknown column 'max_sort_length' in 'field list'
-SET @@global.max_sort_length = @start_global_value;
-SELECT @@global.max_sort_length;
-@@global.max_sort_length
-1024
-SET @@session.max_sort_length = @start_session_value;
-SELECT @@session.max_sort_length;
-@@session.max_sort_length
-1024
diff --git a/mysql-test/suite/sys_vars/r/max_sort_length_func.result b/mysql-test/suite/sys_vars/r/max_sort_length_func.result
index 36f5518287a..3ec8faf1b7e 100644
--- a/mysql-test/suite/sys_vars/r/max_sort_length_func.result
+++ b/mysql-test/suite/sys_vars/r/max_sort_length_func.result
@@ -1,301 +1,279 @@
SET @start_value= @@global.max_sort_length;
-SET @session_max_sort_length = @@Session.max_sort_length;
-DROP TABLE IF EXISTS t;
** creating tables **
-CREATE TABLE t
-(
-id INT AUTO_INCREMENT PRIMARY KEY,
-c TEXT(30)
-);
-CREATE TABLE t1
-(
-id INT AUTO_INCREMENT PRIMARY KEY,
-c BLOB(30)
-);
-CREATE TABLE t2
-(
-id INT AUTO_INCREMENT PRIMARY KEY,
-c TEXT(30)
-);
+CREATE TABLE t (id INT AUTO_INCREMENT PRIMARY KEY, c TEXT);
+CREATE TABLE t1 (id INT AUTO_INCREMENT PRIMARY KEY, c BLOB);
+CREATE TABLE t2 (id INT AUTO_INCREMENT PRIMARY KEY, c TEXT);
'#--------------------FN_DYNVARS_098_01-------------------------#'
connect test_con1,localhost,root,,;
-connection test_con1;
-SELECT @@global.max_sort_length = 10;
-@@global.max_sort_length = 10
-0
-SELECT @@session.max_sort_length = 10;
-@@session.max_sort_length = 10
-0
-** Setting value to 30 and inserting data **
-SET @@global.max_sort_length = 30;
+** Setting value to 70 and inserting data **
+SET @@global.max_sort_length = 70;
SELECT @@global.max_sort_length;
@@global.max_sort_length
-30
-INSERT INTO t set c = repeat('x',29);
-INSERT INTO t set c = concat(repeat('x',28),'r','x');
-INSERT INTO t set c = concat(repeat('x',28),'s','y');
-INSERT INTO t set c = concat(repeat('x',28),'g','w');
+70
+INSERT INTO t set c = repeat('x',69);
+INSERT INTO t set c = concat(repeat('x',68),'r','x');
+INSERT INTO t set c = concat(repeat('x',68),'s','y');
+INSERT INTO t set c = concat(repeat('x',68),'g','w');
SELECT c from t ORDER BY c, id;
c
-xxxxxxxxxxxxxxxxxxxxxxxxxxxxgw
-xxxxxxxxxxxxxxxxxxxxxxxxxxxxrx
-xxxxxxxxxxxxxxxxxxxxxxxxxxxxsy
-xxxxxxxxxxxxxxxxxxxxxxxxxxxxx
+xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxgw
+xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxrx
+xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxsy
+xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
** Results should be sorted **
-SET @@session.max_sort_length = 29;
+SET @@session.max_sort_length = 69;
SELECT @@session.max_sort_length;
@@session.max_sort_length
-29
-INSERT INTO t set c = repeat('x',29);
-INSERT INTO t set c = concat(repeat('x',28),'r','x');
-INSERT INTO t set c = concat(repeat('x',28),'s','y');
-INSERT INTO t set c = concat(repeat('x',28),'g','w');
+69
+INSERT INTO t set c = repeat('x',69);
+INSERT INTO t set c = concat(repeat('x',68),'r','x');
+INSERT INTO t set c = concat(repeat('x',68),'s','y');
+INSERT INTO t set c = concat(repeat('x',68),'g','w');
SELECT c from t ORDER BY c, id;
c
-xxxxxxxxxxxxxxxxxxxxxxxxxxxxgw
-xxxxxxxxxxxxxxxxxxxxxxxxxxxxgw
-xxxxxxxxxxxxxxxxxxxxxxxxxxxxrx
-xxxxxxxxxxxxxxxxxxxxxxxxxxxxrx
-xxxxxxxxxxxxxxxxxxxxxxxxxxxxsy
-xxxxxxxxxxxxxxxxxxxxxxxxxxxxsy
-xxxxxxxxxxxxxxxxxxxxxxxxxxxxx
-xxxxxxxxxxxxxxxxxxxxxxxxxxxxx
+xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxgw
+xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxgw
+xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxrx
+xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxrx
+xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxsy
+xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxsy
+xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
+xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
** Results should be sorted **
'#--------------------FN_DYNVARS_098_02-------------------------#'
connect test_con2,localhost,root,,;
-connection test_con2;
-SET @@global.max_sort_length = 30;
+SET @@global.max_sort_length = 70;
SELECT @@global.max_sort_length;
@@global.max_sort_length
-30
-INSERT INTO t set c = repeat('x',29);
-INSERT INTO t set c = concat(repeat('x',28),'r','x');
-INSERT INTO t set c = concat(repeat('x',28),'s','y');
-INSERT INTO t set c = concat(repeat('x',28),'g','w');
+70
+INSERT INTO t set c = repeat('x',69);
+INSERT INTO t set c = concat(repeat('x',68),'r','x');
+INSERT INTO t set c = concat(repeat('x',68),'s','y');
+INSERT INTO t set c = concat(repeat('x',68),'g','w');
SELECT c from t ORDER BY c, id;
c
-xxxxxxxxxxxxxxxxxxxxxxxxxxxxgw
-xxxxxxxxxxxxxxxxxxxxxxxxxxxxgw
-xxxxxxxxxxxxxxxxxxxxxxxxxxxxgw
-xxxxxxxxxxxxxxxxxxxxxxxxxxxxrx
-xxxxxxxxxxxxxxxxxxxxxxxxxxxxrx
-xxxxxxxxxxxxxxxxxxxxxxxxxxxxrx
-xxxxxxxxxxxxxxxxxxxxxxxxxxxxsy
-xxxxxxxxxxxxxxxxxxxxxxxxxxxxsy
-xxxxxxxxxxxxxxxxxxxxxxxxxxxxsy
-xxxxxxxxxxxxxxxxxxxxxxxxxxxxx
-xxxxxxxxxxxxxxxxxxxxxxxxxxxxx
-xxxxxxxxxxxxxxxxxxxxxxxxxxxxx
+xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxgw
+xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxgw
+xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxgw
+xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxrx
+xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxrx
+xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxrx
+xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxsy
+xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxsy
+xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxsy
+xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
+xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
+xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
** Results should be sorted **
-SET @@session.max_sort_length = 20;
+SET @@session.max_sort_length = 64;
SELECT @@session.max_sort_length;
@@session.max_sort_length
-20
-INSERT INTO t set c = repeat('x',29);
-INSERT INTO t set c = concat(repeat('x',28),'r','x');
-INSERT INTO t set c = concat(repeat('x',28),'s','y');
-INSERT INTO t set c = concat(repeat('x',28),'g','w');
+64
+INSERT INTO t set c = repeat('x',69);
+INSERT INTO t set c = concat(repeat('x',68),'r','x');
+INSERT INTO t set c = concat(repeat('x',68),'s','y');
+INSERT INTO t set c = concat(repeat('x',68),'g','w');
SELECT c from t ORDER BY c, id;
c
-xxxxxxxxxxxxxxxxxxxxxxxxxxxxx
-xxxxxxxxxxxxxxxxxxxxxxxxxxxxrx
-xxxxxxxxxxxxxxxxxxxxxxxxxxxxsy
-xxxxxxxxxxxxxxxxxxxxxxxxxxxxgw
-xxxxxxxxxxxxxxxxxxxxxxxxxxxxx
-xxxxxxxxxxxxxxxxxxxxxxxxxxxxrx
-xxxxxxxxxxxxxxxxxxxxxxxxxxxxsy
-xxxxxxxxxxxxxxxxxxxxxxxxxxxxgw
-xxxxxxxxxxxxxxxxxxxxxxxxxxxxx
-xxxxxxxxxxxxxxxxxxxxxxxxxxxxrx
-xxxxxxxxxxxxxxxxxxxxxxxxxxxxsy
-xxxxxxxxxxxxxxxxxxxxxxxxxxxxgw
-xxxxxxxxxxxxxxxxxxxxxxxxxxxxx
-xxxxxxxxxxxxxxxxxxxxxxxxxxxxrx
-xxxxxxxxxxxxxxxxxxxxxxxxxxxxsy
-xxxxxxxxxxxxxxxxxxxxxxxxxxxxgw
+xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
+xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxrx
+xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxsy
+xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxgw
+xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
+xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxrx
+xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxsy
+xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxgw
+xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
+xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxrx
+xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxsy
+xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxgw
+xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
+xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxrx
+xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxsy
+xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxgw
** Results should not be sorted **
'#--------------------FN_DYNVARS_098_03-------------------------#'
-SET max_sort_length=20;
-INSERT INTO t set c = repeat('x',29);
-INSERT INTO t set c = concat(repeat('x',28),'r','x');
-INSERT INTO t set c = concat(repeat('x',28),'s','y');
-INSERT INTO t set c = concat(repeat('x',28),'g','w');
+SET max_sort_length=64;
+INSERT INTO t set c = repeat('x',69);
+INSERT INTO t set c = concat(repeat('x',68),'r','x');
+INSERT INTO t set c = concat(repeat('x',68),'s','y');
+INSERT INTO t set c = concat(repeat('x',68),'g','w');
SELECT c from t ORDER BY c, id;
c
-xxxxxxxxxxxxxxxxxxxxxxxxxxxxx
-xxxxxxxxxxxxxxxxxxxxxxxxxxxxrx
-xxxxxxxxxxxxxxxxxxxxxxxxxxxxsy
-xxxxxxxxxxxxxxxxxxxxxxxxxxxxgw
-xxxxxxxxxxxxxxxxxxxxxxxxxxxxx
-xxxxxxxxxxxxxxxxxxxxxxxxxxxxrx
-xxxxxxxxxxxxxxxxxxxxxxxxxxxxsy
-xxxxxxxxxxxxxxxxxxxxxxxxxxxxgw
-xxxxxxxxxxxxxxxxxxxxxxxxxxxxx
-xxxxxxxxxxxxxxxxxxxxxxxxxxxxrx
-xxxxxxxxxxxxxxxxxxxxxxxxxxxxsy
-xxxxxxxxxxxxxxxxxxxxxxxxxxxxgw
-xxxxxxxxxxxxxxxxxxxxxxxxxxxxx
-xxxxxxxxxxxxxxxxxxxxxxxxxxxxrx
-xxxxxxxxxxxxxxxxxxxxxxxxxxxxsy
-xxxxxxxxxxxxxxxxxxxxxxxxxxxxgw
-xxxxxxxxxxxxxxxxxxxxxxxxxxxxx
-xxxxxxxxxxxxxxxxxxxxxxxxxxxxrx
-xxxxxxxxxxxxxxxxxxxxxxxxxxxxsy
-xxxxxxxxxxxxxxxxxxxxxxxxxxxxgw
+xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
+xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxrx
+xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxsy
+xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxgw
+xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
+xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxrx
+xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxsy
+xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxgw
+xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
+xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxrx
+xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxsy
+xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxgw
+xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
+xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxrx
+xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxsy
+xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxgw
+xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
+xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxrx
+xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxsy
+xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxgw
** Results should not be sorted **
RESET QUERY CACHE;
'#--------------------FN_DYNVARS_098_04-------------------------#'
-SET max_sort_length=29;
+SET max_sort_length=69;
SELECT c from t ORDER BY c, id;
c
-xxxxxxxxxxxxxxxxxxxxxxxxxxxxgw
-xxxxxxxxxxxxxxxxxxxxxxxxxxxxgw
-xxxxxxxxxxxxxxxxxxxxxxxxxxxxgw
-xxxxxxxxxxxxxxxxxxxxxxxxxxxxgw
-xxxxxxxxxxxxxxxxxxxxxxxxxxxxgw
-xxxxxxxxxxxxxxxxxxxxxxxxxxxxrx
-xxxxxxxxxxxxxxxxxxxxxxxxxxxxrx
-xxxxxxxxxxxxxxxxxxxxxxxxxxxxrx
-xxxxxxxxxxxxxxxxxxxxxxxxxxxxrx
-xxxxxxxxxxxxxxxxxxxxxxxxxxxxrx
-xxxxxxxxxxxxxxxxxxxxxxxxxxxxsy
-xxxxxxxxxxxxxxxxxxxxxxxxxxxxsy
-xxxxxxxxxxxxxxxxxxxxxxxxxxxxsy
-xxxxxxxxxxxxxxxxxxxxxxxxxxxxsy
-xxxxxxxxxxxxxxxxxxxxxxxxxxxxsy
-xxxxxxxxxxxxxxxxxxxxxxxxxxxxx
-xxxxxxxxxxxxxxxxxxxxxxxxxxxxx
-xxxxxxxxxxxxxxxxxxxxxxxxxxxxx
-xxxxxxxxxxxxxxxxxxxxxxxxxxxxx
-xxxxxxxxxxxxxxxxxxxxxxxxxxxxx
+xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxgw
+xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxgw
+xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxgw
+xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxgw
+xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxgw
+xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxrx
+xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxrx
+xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxrx
+xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxrx
+xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxrx
+xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxsy
+xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxsy
+xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxsy
+xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxsy
+xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxsy
+xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
+xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
+xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
+xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
+xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
** Results should be sorted **
'#--------------------FN_DYNVARS_098_05-------------------------#'
-SET max_sort_length=30;
+SET max_sort_length=70;
SELECT c from t ORDER BY c, id;
c
-xxxxxxxxxxxxxxxxxxxxxxxxxxxxgw
-xxxxxxxxxxxxxxxxxxxxxxxxxxxxgw
-xxxxxxxxxxxxxxxxxxxxxxxxxxxxgw
-xxxxxxxxxxxxxxxxxxxxxxxxxxxxgw
-xxxxxxxxxxxxxxxxxxxxxxxxxxxxgw
-xxxxxxxxxxxxxxxxxxxxxxxxxxxxrx
-xxxxxxxxxxxxxxxxxxxxxxxxxxxxrx
-xxxxxxxxxxxxxxxxxxxxxxxxxxxxrx
-xxxxxxxxxxxxxxxxxxxxxxxxxxxxrx
-xxxxxxxxxxxxxxxxxxxxxxxxxxxxrx
-xxxxxxxxxxxxxxxxxxxxxxxxxxxxsy
-xxxxxxxxxxxxxxxxxxxxxxxxxxxxsy
-xxxxxxxxxxxxxxxxxxxxxxxxxxxxsy
-xxxxxxxxxxxxxxxxxxxxxxxxxxxxsy
-xxxxxxxxxxxxxxxxxxxxxxxxxxxxsy
-xxxxxxxxxxxxxxxxxxxxxxxxxxxxx
-xxxxxxxxxxxxxxxxxxxxxxxxxxxxx
-xxxxxxxxxxxxxxxxxxxxxxxxxxxxx
-xxxxxxxxxxxxxxxxxxxxxxxxxxxxx
-xxxxxxxxxxxxxxxxxxxxxxxxxxxxx
+xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxgw
+xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxgw
+xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxgw
+xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxgw
+xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxgw
+xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxrx
+xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxrx
+xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxrx
+xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxrx
+xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxrx
+xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxsy
+xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxsy
+xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxsy
+xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxsy
+xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxsy
+xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
+xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
+xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
+xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
+xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
** Results should be sorted **
'#--------------------FN_DYNVARS_098_06-------------------------#'
SET max_sort_length=default;
+SELECT @@max_sort_length;
+@@max_sort_length
+70
SELECT c from t ORDER BY c, id;
c
-xxxxxxxxxxxxxxxxxxxxxxxxxxxxgw
-xxxxxxxxxxxxxxxxxxxxxxxxxxxxgw
-xxxxxxxxxxxxxxxxxxxxxxxxxxxxgw
-xxxxxxxxxxxxxxxxxxxxxxxxxxxxgw
-xxxxxxxxxxxxxxxxxxxxxxxxxxxxgw
-xxxxxxxxxxxxxxxxxxxxxxxxxxxxrx
-xxxxxxxxxxxxxxxxxxxxxxxxxxxxrx
-xxxxxxxxxxxxxxxxxxxxxxxxxxxxrx
-xxxxxxxxxxxxxxxxxxxxxxxxxxxxrx
-xxxxxxxxxxxxxxxxxxxxxxxxxxxxrx
-xxxxxxxxxxxxxxxxxxxxxxxxxxxxsy
-xxxxxxxxxxxxxxxxxxxxxxxxxxxxsy
-xxxxxxxxxxxxxxxxxxxxxxxxxxxxsy
-xxxxxxxxxxxxxxxxxxxxxxxxxxxxsy
-xxxxxxxxxxxxxxxxxxxxxxxxxxxxsy
-xxxxxxxxxxxxxxxxxxxxxxxxxxxxx
-xxxxxxxxxxxxxxxxxxxxxxxxxxxxx
-xxxxxxxxxxxxxxxxxxxxxxxxxxxxx
-xxxxxxxxxxxxxxxxxxxxxxxxxxxxx
-xxxxxxxxxxxxxxxxxxxxxxxxxxxxx
+xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxgw
+xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxgw
+xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxgw
+xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxgw
+xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxgw
+xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxrx
+xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxrx
+xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxrx
+xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxrx
+xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxrx
+xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxsy
+xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxsy
+xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxsy
+xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxsy
+xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxsy
+xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
+xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
+xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
+xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
+xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
** Results should be sorted **
'#--------------------FN_DYNVARS_098_07-------------------------#'
Testing type BLOB
-SET @@global.max_sort_length = 30;
-SELECT @@global.max_sort_length;
-@@global.max_sort_length
-30
-INSERT INTO t1 set c = repeat('x',29);
-INSERT INTO t1 set c = concat(repeat('x',28),'r','x');
-INSERT INTO t1 set c = concat(repeat('x',28),'s','y');
-INSERT INTO t1 set c = concat(repeat('x',28),'g','w');
-SELECT c from t1 ORDER BY c, id;
+SET @@max_sort_length = 70;
+SELECT @@max_sort_length;
+@@max_sort_length
+70
+INSERT INTO t1 set c = repeat('x',69);
+INSERT INTO t1 set c = concat(repeat('x',68),'r','x');
+INSERT INTO t1 set c = concat(repeat('x',68),'s','y');
+INSERT INTO t1 set c = concat(repeat('x',68),'g','w');
+SELECT c from t1 ORDER BY c, id DESC;
c
-xxxxxxxxxxxxxxxxxxxxxxxxxxxxgw
-xxxxxxxxxxxxxxxxxxxxxxxxxxxxrx
-xxxxxxxxxxxxxxxxxxxxxxxxxxxxsy
-xxxxxxxxxxxxxxxxxxxxxxxxxxxxx
+xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
+xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxgw
+xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxsy
+xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxrx
** Results should be sorted **
-SET @@session.max_sort_length = 20;
+SET @@session.max_sort_length = 64;
SELECT @@session.max_sort_length;
@@session.max_sort_length
-20
-INSERT INTO t1 set c = repeat('x',29);
-INSERT INTO t1 set c = concat(repeat('x',28),'r','x');
-INSERT INTO t1 set c = concat(repeat('x',28),'s','y');
-INSERT INTO t1 set c = concat(repeat('x',28),'g','w');
+64
+INSERT INTO t1 set c = repeat('x',69);
+INSERT INTO t1 set c = concat(repeat('x',68),'r','x');
+INSERT INTO t1 set c = concat(repeat('x',68),'s','y');
+INSERT INTO t1 set c = concat(repeat('x',68),'g','w');
SELECT c from t1 ORDER BY c, id;
c
-xxxxxxxxxxxxxxxxxxxxxxxxxxxxx
-xxxxxxxxxxxxxxxxxxxxxxxxxxxxx
-xxxxxxxxxxxxxxxxxxxxxxxxxxxxrx
-xxxxxxxxxxxxxxxxxxxxxxxxxxxxsy
-xxxxxxxxxxxxxxxxxxxxxxxxxxxxgw
-xxxxxxxxxxxxxxxxxxxxxxxxxxxxrx
-xxxxxxxxxxxxxxxxxxxxxxxxxxxxsy
-xxxxxxxxxxxxxxxxxxxxxxxxxxxxgw
+xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
+xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
+xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxrx
+xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxsy
+xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxgw
+xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxrx
+xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxsy
+xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxgw
** Results should not be sorted **
'#--------------------FN_DYNVARS_098_08-------------------------#'
Testing type CHAR
-SET @@global.max_sort_length = 30;
+SET @@global.max_sort_length = 70;
SELECT @@global.max_sort_length;
@@global.max_sort_length
-30
-INSERT INTO t2 set c = repeat('x',29);
-INSERT INTO t2 set c = concat(repeat('x',28),'r','x');
-INSERT INTO t2 set c = concat(repeat('x',28),'s','y');
-INSERT INTO t2 set c = concat(repeat('x',28),'g','w');
+70
+INSERT INTO t2 set c = repeat('x',69);
+INSERT INTO t2 set c = concat(repeat('x',68),'r','x');
+INSERT INTO t2 set c = concat(repeat('x',68),'s','y');
+INSERT INTO t2 set c = concat(repeat('x',68),'g','w');
SELECT c from t2 ORDER BY c, id;
c
-xxxxxxxxxxxxxxxxxxxxxxxxxxxxx
-xxxxxxxxxxxxxxxxxxxxxxxxxxxxrx
-xxxxxxxxxxxxxxxxxxxxxxxxxxxxsy
-xxxxxxxxxxxxxxxxxxxxxxxxxxxxgw
+xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
+xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxrx
+xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxsy
+xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxgw
** Results should not be sorted **
-SET @@session.max_sort_length = 20;
+SET @@session.max_sort_length = 64;
SELECT @@session.max_sort_length;
@@session.max_sort_length
-20
-INSERT INTO t2 set c = repeat('x',29);
-INSERT INTO t2 set c = concat(repeat('x',28),'r','x');
-INSERT INTO t2 set c = concat(repeat('x',28),'s','y');
-INSERT INTO t2 set c = concat(repeat('x',28),'g','w');
+64
+INSERT INTO t2 set c = repeat('x',69);
+INSERT INTO t2 set c = concat(repeat('x',68),'r','x');
+INSERT INTO t2 set c = concat(repeat('x',68),'s','y');
+INSERT INTO t2 set c = concat(repeat('x',68),'g','w');
SELECT c from t2 ORDER BY c, id;
c
-xxxxxxxxxxxxxxxxxxxxxxxxxxxxx
-xxxxxxxxxxxxxxxxxxxxxxxxxxxxrx
-xxxxxxxxxxxxxxxxxxxxxxxxxxxxsy
-xxxxxxxxxxxxxxxxxxxxxxxxxxxxgw
-xxxxxxxxxxxxxxxxxxxxxxxxxxxxx
-xxxxxxxxxxxxxxxxxxxxxxxxxxxxrx
-xxxxxxxxxxxxxxxxxxxxxxxxxxxxsy
-xxxxxxxxxxxxxxxxxxxxxxxxxxxxgw
+xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
+xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxrx
+xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxsy
+xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxgw
+xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
+xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxrx
+xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxsy
+xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxgw
** Results should not be sorted **
connection default;
disconnect test_con1;
disconnect test_con2;
-SET @@SESSION.max_sort_length = @session_max_sort_length;
-DROP TABLE IF EXISTS t;
-DROP TABLE IF EXISTS t1;
-DROP TABLE IF EXISTS t2;
+DROP TABLE t, t1, t2;
SET @@global.max_sort_length= @start_value;
diff --git a/mysql-test/suite/sys_vars/r/sysvars_server_embedded.result b/mysql-test/suite/sys_vars/r/sysvars_server_embedded.result
index 7bfa2f626f0..0648284e580 100644
--- a/mysql-test/suite/sys_vars/r/sysvars_server_embedded.result
+++ b/mysql-test/suite/sys_vars/r/sysvars_server_embedded.result
@@ -1055,10 +1055,10 @@ READ_ONLY NO
COMMAND_LINE_ARGUMENT REQUIRED
VARIABLE_NAME GROUP_CONCAT_MAX_LEN
VARIABLE_SCOPE SESSION
-VARIABLE_TYPE BIGINT UNSIGNED
+VARIABLE_TYPE INT UNSIGNED
VARIABLE_COMMENT The maximum length of the result of function GROUP_CONCAT()
NUMERIC_MIN_VALUE 4
-NUMERIC_MAX_VALUE 18446744073709551615
+NUMERIC_MAX_VALUE 4294967295
NUMERIC_BLOCK_SIZE 1
ENUM_VALUE_LIST NULL
READ_ONLY NO
@@ -1947,7 +1947,7 @@ VARIABLE_NAME MAX_SORT_LENGTH
VARIABLE_SCOPE SESSION
VARIABLE_TYPE BIGINT UNSIGNED
VARIABLE_COMMENT The number of bytes to use when sorting BLOB or TEXT values (only the first max_sort_length bytes of each value are used; the rest are ignored)
-NUMERIC_MIN_VALUE 8
+NUMERIC_MIN_VALUE 64
NUMERIC_MAX_VALUE 8388608
NUMERIC_BLOCK_SIZE 1
ENUM_VALUE_LIST NULL
diff --git a/mysql-test/suite/sys_vars/r/sysvars_server_notembedded.result b/mysql-test/suite/sys_vars/r/sysvars_server_notembedded.result
index 8854fab4209..78b8ca0263d 100644
--- a/mysql-test/suite/sys_vars/r/sysvars_server_notembedded.result
+++ b/mysql-test/suite/sys_vars/r/sysvars_server_notembedded.result
@@ -1075,10 +1075,10 @@ READ_ONLY NO
COMMAND_LINE_ARGUMENT REQUIRED
VARIABLE_NAME GROUP_CONCAT_MAX_LEN
VARIABLE_SCOPE SESSION
-VARIABLE_TYPE BIGINT UNSIGNED
+VARIABLE_TYPE INT UNSIGNED
VARIABLE_COMMENT The maximum length of the result of function GROUP_CONCAT()
NUMERIC_MIN_VALUE 4
-NUMERIC_MAX_VALUE 18446744073709551615
+NUMERIC_MAX_VALUE 4294967295
NUMERIC_BLOCK_SIZE 1
ENUM_VALUE_LIST NULL
READ_ONLY NO
@@ -2107,7 +2107,7 @@ VARIABLE_NAME MAX_SORT_LENGTH
VARIABLE_SCOPE SESSION
VARIABLE_TYPE BIGINT UNSIGNED
VARIABLE_COMMENT The number of bytes to use when sorting BLOB or TEXT values (only the first max_sort_length bytes of each value are used; the rest are ignored)
-NUMERIC_MIN_VALUE 8
+NUMERIC_MIN_VALUE 64
NUMERIC_MAX_VALUE 8388608
NUMERIC_BLOCK_SIZE 1
ENUM_VALUE_LIST NULL
diff --git a/mysql-test/suite/sys_vars/t/max_sort_length_basic.test b/mysql-test/suite/sys_vars/t/max_sort_length_basic.test
deleted file mode 100644
index fcd6db017f1..00000000000
--- a/mysql-test/suite/sys_vars/t/max_sort_length_basic.test
+++ /dev/null
@@ -1,225 +0,0 @@
-############## mysql-test\t\max_sort_length_basic.test ###############
-# #
-# Variable Name: max_sort_length #
-# Scope: GLOBAL | SESSION #
-# Access Type: Dynamic #
-# Data Type: numeric #
-# Default Value: 1024 #
-# Range: 4-8388608 #
-# #
-# #
-# Creation Date: 2008-02-07 #
-# Author: Salman #
-# #
-# Description: Test Cases of Dynamic System Variable max_sort_length #
-# that checks the behavior of this variable in the following ways#
-# * Default Value #
-# * Valid & Invalid values #
-# * Scope & Access method #
-# * Data Integrity #
-# #
-# Reference: http://dev.mysql.com/doc/refman/5.1/en/ #
-# server-system-variables.html #
-# #
-###############################################################################
-
---source include/load_sysvars.inc
-
-
-############################################
-# START OF max_sort_length TESTS #
-############################################
-
-
-#############################################################
-# Save initial value #
-#############################################################
-
-SET @start_global_value = @@global.max_sort_length;
-SELECT @start_global_value;
-SET @start_session_value = @@session.max_sort_length;
-SELECT @start_session_value;
-
-
---echo '#--------------------FN_DYNVARS_084_01-------------------------#'
-#######################################################
-# Display the DEFAULT value of max_sort_length #
-#######################################################
-
-SET @@global.max_sort_length = 1000;
-SET @@global.max_sort_length = DEFAULT;
-SELECT @@global.max_sort_length;
-
-
-SET @@session.max_sort_length = 1000;
-SET @@session.max_sort_length = DEFAULT;
-SELECT @@session.max_sort_length;
-
-
---echo '#--------------------FN_DYNVARS_084_02-------------------------#'
-#######################################################
-# Check the DEFAULT value of max_sort_length #
-#######################################################
-
-SET @@global.max_sort_length = DEFAULT;
-SELECT @@global.max_sort_length = 1024;
-
-SET @@session.max_sort_length = DEFAULT;
-SELECT @@session.max_sort_length = 1024;
-
-
-
---echo '#--------------------FN_DYNVARS_084_03-------------------------#'
-#########################################################################
-# Change the value of max_sort_length to a valid value for GLOBAL Scope #
-#########################################################################
-
-SET @@global.max_sort_length = 8;
-SELECT @@global.max_sort_length;
-SET @@global.max_sort_length = 9;
-SELECT @@global.max_sort_length;
-SET @@global.max_sort_length = 8388608;
-SELECT @@global.max_sort_length;
-SET @@global.max_sort_length = 8388607;
-SELECT @@global.max_sort_length;
-SET @@global.max_sort_length = 65536;
-SELECT @@global.max_sort_length;
-
---echo '#--------------------FN_DYNVARS_084_04-------------------------#'
-##########################################################################
-# Change the value of max_sort_length to a valid value for SESSION Scope #
-##########################################################################
-
-SET @@session.max_sort_length = 8;
-SELECT @@session.max_sort_length;
-
-SET @@session.max_sort_length = 9;
-SELECT @@session.max_sort_length;
-
-SET @@session.max_sort_length = 8388608;
-SELECT @@session.max_sort_length;
-
-SET @@session.max_sort_length = 8388607;
-SELECT @@session.max_sort_length;
-
-SET @@session.max_sort_length = 65536;
-SELECT @@session.max_sort_length;
-
-
---echo '#------------------FN_DYNVARS_084_05-----------------------#'
-###########################################################
-# Change the value of max_sort_length to an invalid value #
-###########################################################
-
-SET @@global.max_sort_length = -1024;
-SELECT @@global.max_sort_length;
-SET @@global.max_sort_length = 3;
-SELECT @@global.max_sort_length;
-SET @@global.max_sort_length = 8388609;
-SELECT @@global.max_sort_length;
-SET @@global.max_sort_length = 0;
-SELECT @@global.max_sort_length;
---Error ER_WRONG_TYPE_FOR_VAR
-SET @@global.max_sort_length = 65530.34;
-SELECT @@global.max_sort_length;
---Error ER_WRONG_TYPE_FOR_VAR
-SET @@global.max_sort_length = test;
-SELECT @@global.max_sort_length;
-
-SET @@session.max_sort_length = 8388610;
-SELECT @@session.max_sort_length;
-SET @@session.max_sort_length = -1;
-SELECT @@session.max_sort_length;
-SET @@session.max_sort_length = 3;
-SELECT @@session.max_sort_length;
-SET @@session.max_sort_length = 0;
-SELECT @@session.max_sort_length;
---Error ER_WRONG_TYPE_FOR_VAR
-SET @@session.max_sort_length = 65530.34;
-SET @@session.max_sort_length = 10737418241;
-SELECT @@session.max_sort_length;
---Error ER_WRONG_TYPE_FOR_VAR
-SET @@session.max_sort_length = test;
-SELECT @@session.max_sort_length;
-
-
---echo '#------------------FN_DYNVARS_084_06-----------------------#'
-####################################################################
-# Check if the value in GLOBAL Table matches value in variable #
-####################################################################
-
-
-SELECT @@global.max_sort_length = VARIABLE_VALUE
-FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES
-WHERE VARIABLE_NAME='max_sort_length';
-
---echo '#------------------FN_DYNVARS_084_07-----------------------#'
-####################################################################
-# Check if the value in SESSION Table matches value in variable #
-####################################################################
-
-SELECT @@session.max_sort_length = VARIABLE_VALUE
-FROM INFORMATION_SCHEMA.SESSION_VARIABLES
-WHERE VARIABLE_NAME='max_sort_length';
-
-
---echo '#------------------FN_DYNVARS_084_08-----------------------#'
-####################################################################
-# Check if TRUE and FALSE values can be used on variable #
-####################################################################
-
-SET @@global.max_sort_length = TRUE;
-SELECT @@global.max_sort_length;
-SET @@global.max_sort_length = FALSE;
-SELECT @@global.max_sort_length;
-
-
---echo '#---------------------FN_DYNVARS_084_09----------------------#'
-#################################################################################
-# Check if accessing variable with and without GLOBAL point to same variable #
-#################################################################################
-
-SET @@global.max_sort_length = 2048;
-SELECT @@max_sort_length = @@global.max_sort_length;
-
-
---echo '#---------------------FN_DYNVARS_084_10----------------------#'
-########################################################################################################
-# Check if accessing variable with SESSION,LOCAL and without SCOPE points to same session variable #
-########################################################################################################
-
-SET @@max_sort_length = 100000;
-SELECT @@max_sort_length = @@local.max_sort_length;
-SELECT @@local.max_sort_length = @@session.max_sort_length;
-
-
---echo '#---------------------FN_DYNVARS_084_11----------------------#'
-##########################################################################
-# Check if max_sort_length can be accessed with and without @@ sign #
-##########################################################################
-
-
-SET max_sort_length = 1024;
-SELECT @@max_sort_length;
---Error ER_UNKNOWN_TABLE
-SELECT local.max_sort_length;
---Error ER_UNKNOWN_TABLE
-SELECT session.max_sort_length;
---Error ER_BAD_FIELD_ERROR
-SELECT max_sort_length = @@session.max_sort_length;
-
-
-####################################
-# Restore initial value #
-####################################
-
-SET @@global.max_sort_length = @start_global_value;
-SELECT @@global.max_sort_length;
-SET @@session.max_sort_length = @start_session_value;
-SELECT @@session.max_sort_length;
-
-
-####################################################
-# END OF max_sort_length TESTS #
-####################################################
-
diff --git a/mysql-test/suite/sys_vars/t/max_sort_length_func.test b/mysql-test/suite/sys_vars/t/max_sort_length_func.test
index fd0b87750a1..d54453a57b6 100644
--- a/mysql-test/suite/sys_vars/t/max_sort_length_func.test
+++ b/mysql-test/suite/sys_vars/t/max_sort_length_func.test
@@ -26,70 +26,40 @@
SET @start_value= @@global.max_sort_length;
-SET @session_max_sort_length = @@Session.max_sort_length;
-
-
---disable_warnings
-DROP TABLE IF EXISTS t;
---enable_warnings
-
#########################
# Creating new table #
#########################
-
--echo ** creating tables **
-CREATE TABLE t
-(
-id INT AUTO_INCREMENT PRIMARY KEY,
-c TEXT(30)
-);
-
-CREATE TABLE t1
-(
-id INT AUTO_INCREMENT PRIMARY KEY,
-c BLOB(30)
-);
-
-CREATE TABLE t2
-(
-id INT AUTO_INCREMENT PRIMARY KEY,
-c TEXT(30)
-);
-
-
+CREATE TABLE t (id INT AUTO_INCREMENT PRIMARY KEY, c TEXT);
+CREATE TABLE t1 (id INT AUTO_INCREMENT PRIMARY KEY, c BLOB);
+CREATE TABLE t2 (id INT AUTO_INCREMENT PRIMARY KEY, c TEXT);
--echo '#--------------------FN_DYNVARS_098_01-------------------------#'
##########################################################
# Test behavior of variable on new connection # 01 #
##########################################################
-
connect (test_con1,localhost,root,,);
-connection test_con1;
-
-# Value of session & global vairable here should be 10
-SELECT @@global.max_sort_length = 10;
-SELECT @@session.max_sort_length = 10;
# Setting global value of variable and inserting data in table
---echo ** Setting value to 30 and inserting data **
-SET @@global.max_sort_length = 30;
+--echo ** Setting value to 70 and inserting data **
+SET @@global.max_sort_length = 70;
SELECT @@global.max_sort_length;
-INSERT INTO t set c = repeat('x',29);
-INSERT INTO t set c = concat(repeat('x',28),'r','x');
-INSERT INTO t set c = concat(repeat('x',28),'s','y');
-INSERT INTO t set c = concat(repeat('x',28),'g','w');
+INSERT INTO t set c = repeat('x',69);
+INSERT INTO t set c = concat(repeat('x',68),'r','x');
+INSERT INTO t set c = concat(repeat('x',68),'s','y');
+INSERT INTO t set c = concat(repeat('x',68),'g','w');
SELECT c from t ORDER BY c, id;
--echo ** Results should be sorted **
# Setting session value of variable and inserting data in table
-SET @@session.max_sort_length = 29;
+SET @@session.max_sort_length = 69;
SELECT @@session.max_sort_length;
-INSERT INTO t set c = repeat('x',29);
-INSERT INTO t set c = concat(repeat('x',28),'r','x');
-INSERT INTO t set c = concat(repeat('x',28),'s','y');
-INSERT INTO t set c = concat(repeat('x',28),'g','w');
+INSERT INTO t set c = repeat('x',69);
+INSERT INTO t set c = concat(repeat('x',68),'r','x');
+INSERT INTO t set c = concat(repeat('x',68),'s','y');
+INSERT INTO t set c = concat(repeat('x',68),'g','w');
SELECT c from t ORDER BY c, id;
--echo ** Results should be sorted **
@@ -99,72 +69,69 @@ SELECT c from t ORDER BY c, id;
##########################################################
connect (test_con2,localhost,root,,);
-connection test_con2;
-
## Setting global value of variable and inserting data in table
-SET @@global.max_sort_length = 30;
+SET @@global.max_sort_length = 70;
SELECT @@global.max_sort_length;
-INSERT INTO t set c = repeat('x',29);
-INSERT INTO t set c = concat(repeat('x',28),'r','x');
-INSERT INTO t set c = concat(repeat('x',28),'s','y');
-INSERT INTO t set c = concat(repeat('x',28),'g','w');
+INSERT INTO t set c = repeat('x',69);
+INSERT INTO t set c = concat(repeat('x',68),'r','x');
+INSERT INTO t set c = concat(repeat('x',68),'s','y');
+INSERT INTO t set c = concat(repeat('x',68),'g','w');
SELECT c from t ORDER BY c, id;
--echo ** Results should be sorted **
# Setting session value of variable and inserting data in table
-SET @@session.max_sort_length = 20;
+SET @@session.max_sort_length = 64;
SELECT @@session.max_sort_length;
-INSERT INTO t set c = repeat('x',29);
-INSERT INTO t set c = concat(repeat('x',28),'r','x');
-INSERT INTO t set c = concat(repeat('x',28),'s','y');
-INSERT INTO t set c = concat(repeat('x',28),'g','w');
+INSERT INTO t set c = repeat('x',69);
+INSERT INTO t set c = concat(repeat('x',68),'r','x');
+INSERT INTO t set c = concat(repeat('x',68),'s','y');
+INSERT INTO t set c = concat(repeat('x',68),'g','w');
SELECT c from t ORDER BY c, id;
--echo ** Results should not be sorted **
--echo '#--------------------FN_DYNVARS_098_03-------------------------#'
#########################################################
-#Check if sorting is applied with the max_sort_length=20#
+#Check if sorting is applied with the max_sort_length=64#
#########################################################
###########################################
# Setting new value for max_sort_length #
###########################################
-SET max_sort_length=20;
+SET max_sort_length=64;
###################################
# Inserting values in table t #
###################################
-INSERT INTO t set c = repeat('x',29);
-INSERT INTO t set c = concat(repeat('x',28),'r','x');
-INSERT INTO t set c = concat(repeat('x',28),'s','y');
-INSERT INTO t set c = concat(repeat('x',28),'g','w');
+INSERT INTO t set c = repeat('x',69);
+INSERT INTO t set c = concat(repeat('x',68),'r','x');
+INSERT INTO t set c = concat(repeat('x',68),'s','y');
+INSERT INTO t set c = concat(repeat('x',68),'g','w');
SELECT c from t ORDER BY c, id;
--echo ** Results should not be sorted **
RESET QUERY CACHE;
-
--echo '#--------------------FN_DYNVARS_098_04-------------------------#'
#########################################################
-#Check if sorting is applied with the max_sort_length=29#
+#Check if sorting is applied with the max_sort_length=69#
#########################################################
-SET max_sort_length=29;
+SET max_sort_length=69;
SELECT c from t ORDER BY c, id;
--echo ** Results should be sorted **
--echo '#--------------------FN_DYNVARS_098_05-------------------------#'
#########################################################
-#Check if sorting is applied with the max_sort_length=30#
+#Check if sorting is applied with the max_sort_length=70#
#########################################################
-SET max_sort_length=30;
+SET max_sort_length=70;
SELECT c from t ORDER BY c, id;
--echo ** Results should be sorted **
@@ -173,34 +140,33 @@ SELECT c from t ORDER BY c, id;
#Check if sorting is applied with the max_sort_length=Default#
##############################################################
-
SET max_sort_length=default;
+SELECT @@max_sort_length;
SELECT c from t ORDER BY c, id;
--echo ** Results should be sorted **
-
--echo '#--------------------FN_DYNVARS_098_07-------------------------#'
###########################################
#Check if sorting is applied on BLOB type #
###########################################
--echo Testing type BLOB
# Setting global value of variable and inserting data in table
-SET @@global.max_sort_length = 30;
-SELECT @@global.max_sort_length;
-INSERT INTO t1 set c = repeat('x',29);
-INSERT INTO t1 set c = concat(repeat('x',28),'r','x');
-INSERT INTO t1 set c = concat(repeat('x',28),'s','y');
-INSERT INTO t1 set c = concat(repeat('x',28),'g','w');
-SELECT c from t1 ORDER BY c, id;
+SET @@max_sort_length = 70;
+SELECT @@max_sort_length;
+INSERT INTO t1 set c = repeat('x',69);
+INSERT INTO t1 set c = concat(repeat('x',68),'r','x');
+INSERT INTO t1 set c = concat(repeat('x',68),'s','y');
+INSERT INTO t1 set c = concat(repeat('x',68),'g','w');
+SELECT c from t1 ORDER BY c, id DESC;
--echo ** Results should be sorted **
# Setting session value of variable and inserting data in table
-SET @@session.max_sort_length = 20;
+SET @@session.max_sort_length = 64;
SELECT @@session.max_sort_length;
-INSERT INTO t1 set c = repeat('x',29);
-INSERT INTO t1 set c = concat(repeat('x',28),'r','x');
-INSERT INTO t1 set c = concat(repeat('x',28),'s','y');
-INSERT INTO t1 set c = concat(repeat('x',28),'g','w');
+INSERT INTO t1 set c = repeat('x',69);
+INSERT INTO t1 set c = concat(repeat('x',68),'r','x');
+INSERT INTO t1 set c = concat(repeat('x',68),'s','y');
+INSERT INTO t1 set c = concat(repeat('x',68),'g','w');
SELECT c from t1 ORDER BY c, id;
--echo ** Results should not be sorted **
@@ -210,26 +176,25 @@ SELECT c from t1 ORDER BY c, id;
###########################################
--echo Testing type CHAR
# Setting global value of variable and inserting data in table
-SET @@global.max_sort_length = 30;
+SET @@global.max_sort_length = 70;
SELECT @@global.max_sort_length;
-INSERT INTO t2 set c = repeat('x',29);
-INSERT INTO t2 set c = concat(repeat('x',28),'r','x');
-INSERT INTO t2 set c = concat(repeat('x',28),'s','y');
-INSERT INTO t2 set c = concat(repeat('x',28),'g','w');
+INSERT INTO t2 set c = repeat('x',69);
+INSERT INTO t2 set c = concat(repeat('x',68),'r','x');
+INSERT INTO t2 set c = concat(repeat('x',68),'s','y');
+INSERT INTO t2 set c = concat(repeat('x',68),'g','w');
SELECT c from t2 ORDER BY c, id;
--echo ** Results should not be sorted **
# Setting session value of variable and inserting data in table
-SET @@session.max_sort_length = 20;
+SET @@session.max_sort_length = 64;
SELECT @@session.max_sort_length;
-INSERT INTO t2 set c = repeat('x',29);
-INSERT INTO t2 set c = concat(repeat('x',28),'r','x');
-INSERT INTO t2 set c = concat(repeat('x',28),'s','y');
-INSERT INTO t2 set c = concat(repeat('x',28),'g','w');
+INSERT INTO t2 set c = repeat('x',69);
+INSERT INTO t2 set c = concat(repeat('x',68),'r','x');
+INSERT INTO t2 set c = concat(repeat('x',68),'s','y');
+INSERT INTO t2 set c = concat(repeat('x',68),'g','w');
SELECT c from t2 ORDER BY c, id;
--echo ** Results should not be sorted **
-
#
# Cleanup
#
@@ -239,12 +204,6 @@ connection default;
disconnect test_con1;
disconnect test_con2;
-SET @@SESSION.max_sort_length = @session_max_sort_length;
-
---disable_warnings
-DROP TABLE IF EXISTS t;
-DROP TABLE IF EXISTS t1;
-DROP TABLE IF EXISTS t2;
---enable_warnings
+DROP TABLE t, t1, t2;
SET @@global.max_sort_length= @start_value;
diff --git a/mysql-test/suite/versioning/common.inc b/mysql-test/suite/versioning/common.inc
index efb081a02e4..25adf15dd50 100644
--- a/mysql-test/suite/versioning/common.inc
+++ b/mysql-test/suite/versioning/common.inc
@@ -70,6 +70,11 @@ returns int
deterministic
return sys_trx_end = $sys_datatype_max;
+eval create or replace function current_row_ts(sys_trx_end timestamp(6))
+returns int
+deterministic
+ return convert_tz(sys_trx_end, '+00:00', @@time_zone) = TIMESTAMP'2038-01-19 03:14:07.999999';
+
delimiter ~~;
eval create or replace function check_row(row_start $sys_datatype_expl, row_end $sys_datatype_expl)
returns varchar(255)
@@ -86,4 +91,20 @@ begin
end~~
delimiter ;~~
+delimiter ~~;
+eval create or replace function check_row_ts(row_start timestamp(6), row_end timestamp(6))
+returns varchar(255)
+deterministic
+begin
+ if row_end < row_start then
+ return "ERROR: row_end < row_start";
+ elseif row_end = row_start then
+ return "ERROR: row_end == row_start";
+ elseif current_row_ts(row_end) then
+ return "CURRENT ROW";
+ end if;
+ return "HISTORICAL ROW";
+end~~
+delimiter ;~~
+
--enable_query_log
diff --git a/mysql-test/suite/versioning/common_finish.inc b/mysql-test/suite/versioning/common_finish.inc
index 61641c6c5ce..3c4e7b66ff3 100644
--- a/mysql-test/suite/versioning/common_finish.inc
+++ b/mysql-test/suite/versioning/common_finish.inc
@@ -4,5 +4,7 @@ drop procedure if exists verify_trt;
drop procedure if exists verify_trt_dummy;
drop function if exists current_row;
drop function if exists check_row;
+drop function if exists current_row_ts;
+drop function if exists check_row_ts;
--enable_warnings
--enable_query_log
diff --git a/mysql-test/suite/versioning/r/delete.result b/mysql-test/suite/versioning/r/delete.result
index 5aa239b9cb8..0f9e2c22130 100644
--- a/mysql-test/suite/versioning/r/delete.result
+++ b/mysql-test/suite/versioning/r/delete.result
@@ -130,3 +130,22 @@ ERROR 42S02: Table 'test.xx' doesn't exist
drop procedure pr;
drop trigger tr;
drop table t1;
+#
+# MDEV-21138 Assertion `col->ord_part' or `f.col->ord_part' failed in row_build_index_entry_low
+#
+create table t1 (
+f1 int, f2 text, f3 int, fulltext (f2), key(f1), key(f3),
+foreign key r (f3) references t1 (f1) on delete set null)
+with system versioning engine innodb;
+insert into t1 values (1, repeat('a', 8193), 1), (1, repeat('b', 8193), 1);
+select f1, f3, check_row_ts(row_start, row_end) from t1;
+f1 f3 check_row_ts(row_start, row_end)
+1 1 CURRENT ROW
+1 1 CURRENT ROW
+delete from t1;
+select f1, f3, check_row_ts(row_start, row_end) from t1 for system_time all;
+f1 f3 check_row_ts(row_start, row_end)
+1 1 HISTORICAL ROW
+1 NULL ERROR: row_end == row_start
+1 1 HISTORICAL ROW
+drop table t1;
diff --git a/mysql-test/suite/versioning/r/foreign.result b/mysql-test/suite/versioning/r/foreign.result
index 5234a1ad616..07e133e1eeb 100644
--- a/mysql-test/suite/versioning/r/foreign.result
+++ b/mysql-test/suite/versioning/r/foreign.result
@@ -400,6 +400,8 @@ Warning 1265 Data truncated for column 'f12' at row 7
SET timestamp = 9;
REPLACE INTO t2 SELECT * FROM t2;
DROP TABLE t1, t2;
+set timestamp= default;
+set time_zone='+00:00';
#
# MDEV-16210 FK constraints on versioned tables use historical rows, which may cause constraint violation
#
@@ -430,6 +432,20 @@ insert into t2 values (1), (1);
delete from t2;
drop tables t2, t1;
#
+# MDEV-23644 Assertion on evaluating foreign referential action for self-reference in system versioned table
+#
+create table t1 (pk int primary key, f1 int,f2 int, f3 text,
+key(f1), fulltext(f3), key(f3(10)),
+foreign key (f2) references t1 (f1) on delete set null
+) engine=innodb with system versioning;
+insert into t1 values (1, 8, 8, 'SHORT'), (2, 8, 8, repeat('LONG', 8071));
+delete from t1;
+select pk, f1, f2, left(f3, 4), check_row_ts(row_start, row_end) from t1 for system_time all order by pk;
+pk f1 f2 left(f3, 4) check_row_ts(row_start, row_end)
+1 8 8 SHOR HISTORICAL ROW
+2 8 8 LONG HISTORICAL ROW
+drop table t1;
+#
# MDEV-20729 Fix REFERENCES constraint in column definition
#
create or replace table t1(
diff --git a/mysql-test/suite/versioning/r/partition.result b/mysql-test/suite/versioning/r/partition.result
index cc9891a1f6e..99d1c01cc49 100644
--- a/mysql-test/suite/versioning/r/partition.result
+++ b/mysql-test/suite/versioning/r/partition.result
@@ -1080,6 +1080,13 @@ create table t1 (a int) with system versioning partition by system_time
alter table t1 add partition (partition p2);
ERROR HY000: Wrong partitioning type, expected type: `SYSTEM_TIME`
drop table t1;
+#
+# MDEV-22178 Assertion `info->alias.str' failed in partition_info::check_partition_info instead of ER_VERS_WRONG_PARTS
+#
+create or replace table t1 (a int) with system versioning;
+alter table t1 partition by system_time (partition pn current);
+ERROR HY000: Wrong partitions for `t1`: must have at least one HISTORY and exactly one last CURRENT
+drop table t1;
# End of 10.3 tests
#
# MDEV-22283 Server crashes in key_copy or unexpected error 156: The table already existed in the storage engine
diff --git a/mysql-test/suite/versioning/r/trx_id.result b/mysql-test/suite/versioning/r/trx_id.result
index e8d550cefa6..b2bb59a4150 100644
--- a/mysql-test/suite/versioning/r/trx_id.result
+++ b/mysql-test/suite/versioning/r/trx_id.result
@@ -5,7 +5,15 @@ sys_trx_start bigint(20) unsigned as row start invisible,
sys_trx_end bigint(20) unsigned as row end invisible,
period for system_time (sys_trx_start, sys_trx_end)
) with system versioning;
+# No history inside the transaction
+start transaction;
insert into t1 (x) values (1);
+update t1 set x= x + 1;
+update t1 set x= x + 1;
+commit;
+select *, sys_trx_start > 1, sys_trx_end from t1 for system_time all;
+x sys_trx_start > 1 sys_trx_end
+3 1 18446744073709551615
# ALTER ADD SYSTEM VERSIONING should write to mysql.transaction_registry
set @@system_versioning_alter_history=keep;
create or replace table t1 (x int);
diff --git a/mysql-test/suite/versioning/r/update.result b/mysql-test/suite/versioning/r/update.result
index cd26c341113..fbb9f541b06 100644
--- a/mysql-test/suite/versioning/r/update.result
+++ b/mysql-test/suite/versioning/r/update.result
@@ -241,6 +241,26 @@ B2 salary
1 2500
drop table t1;
drop table t2;
+# Ensure FTS retains correct history
+create table t1 (
+x int, y text, fulltext (y),
+row_start SYS_DATATYPE as row start invisible,
+row_end SYS_DATATYPE as row end invisible,
+period for system_time (row_start, row_end))
+with system versioning engine innodb;
+insert into t1 values (1, repeat('LONG', 2048));
+update t1 set x= x + 1;
+select x, left(y, 4), length(y), check_row(row_start, row_end) from t1 for system_time all order by x, y;
+x left(y, 4) length(y) check_row(row_start, row_end)
+1 LONG 8192 HISTORICAL ROW
+2 LONG 8192 CURRENT ROW
+update t1 set y= 'SHORT';
+select x, left(y, 4), length(y), check_row(row_start, row_end) from t1 for system_time all order by x, y;
+x left(y, 4) length(y) check_row(row_start, row_end)
+1 LONG 8192 HISTORICAL ROW
+2 LONG 8192 HISTORICAL ROW
+2 SHOR 5 CURRENT ROW
+drop tables t1;
### Issue tempesta-tech/mariadb#365, bug 7 (duplicate of historical row)
create or replace table t1 (a int primary key, b int)
with system versioning engine myisam;
@@ -350,3 +370,32 @@ insert into t1 (a) values (1), (2);
update ignore t1 set a= 3;
delete history from t1;
drop table t1;
+#
+# MDEV-23446 UPDATE does not insert history row if the row is not changed
+#
+create table t1 (
+a int,
+row_start SYS_DATATYPE as row start invisible,
+row_end SYS_DATATYPE as row end invisible,
+period for system_time (row_start, row_end)) with system versioning;
+insert into t1 values (1);
+update t1 set a= 1;
+select *, check_row(row_start, row_end) from t1 for system_time all order by row_end;
+a check_row(row_start, row_end)
+1 HISTORICAL ROW
+1 CURRENT ROW
+# multi-update
+create or replace table t2 like t1;
+create or replace table t3 like t1;
+insert into t2 values (1);
+insert into t3 values (1);
+update t2, t3 set t2.a= 1, t3.a= 1 where t2.a = t3.a;
+select *, check_row(row_start, row_end) from t2 for system_time all order by row_end;
+a check_row(row_start, row_end)
+1 HISTORICAL ROW
+1 CURRENT ROW
+select *, check_row(row_start, row_end) from t2 for system_time all order by row_end;
+a check_row(row_start, row_end)
+1 HISTORICAL ROW
+1 CURRENT ROW
+drop tables t1, t2, t3;
diff --git a/mysql-test/suite/versioning/t/delete.test b/mysql-test/suite/versioning/t/delete.test
index 492463f9395..a5a0497fef2 100644
--- a/mysql-test/suite/versioning/t/delete.test
+++ b/mysql-test/suite/versioning/t/delete.test
@@ -94,4 +94,19 @@ drop procedure pr;
drop trigger tr;
drop table t1;
+--echo #
+--echo # MDEV-21138 Assertion `col->ord_part' or `f.col->ord_part' failed in row_build_index_entry_low
+--echo #
+create table t1 (
+ f1 int, f2 text, f3 int, fulltext (f2), key(f1), key(f3),
+ foreign key r (f3) references t1 (f1) on delete set null)
+with system versioning engine innodb;
+insert into t1 values (1, repeat('a', 8193), 1), (1, repeat('b', 8193), 1);
+select f1, f3, check_row_ts(row_start, row_end) from t1;
+delete from t1;
+select f1, f3, check_row_ts(row_start, row_end) from t1 for system_time all;
+
+# cleanup
+drop table t1;
+
--source suite/versioning/common_finish.inc
diff --git a/mysql-test/suite/versioning/t/foreign.test b/mysql-test/suite/versioning/t/foreign.test
index 831ba518337..ed2ed4dd122 100644
--- a/mysql-test/suite/versioning/t/foreign.test
+++ b/mysql-test/suite/versioning/t/foreign.test
@@ -421,6 +421,8 @@ REPLACE INTO t2 SELECT * FROM t2;
# Cleanup
DROP TABLE t1, t2;
+set timestamp= default;
+set time_zone='+00:00';
--let $datadir= `select @@datadir`
--remove_file $datadir/test/t1.data
--remove_file $datadir/test/t1.data.2
@@ -459,6 +461,22 @@ delete from t2;
drop tables t2, t1;
--echo #
+--echo # MDEV-23644 Assertion on evaluating foreign referential action for self-reference in system versioned table
+--echo #
+create table t1 (pk int primary key, f1 int,f2 int, f3 text,
+ key(f1), fulltext(f3), key(f3(10)),
+ foreign key (f2) references t1 (f1) on delete set null
+) engine=innodb with system versioning;
+
+insert into t1 values (1, 8, 8, 'SHORT'), (2, 8, 8, repeat('LONG', 8071));
+
+delete from t1;
+select pk, f1, f2, left(f3, 4), check_row_ts(row_start, row_end) from t1 for system_time all order by pk;
+
+# cleanup
+drop table t1;
+
+--echo #
--echo # MDEV-20729 Fix REFERENCES constraint in column definition
--echo #
create or replace table t1(
diff --git a/mysql-test/suite/versioning/t/partition.test b/mysql-test/suite/versioning/t/partition.test
index a154cc37ae2..b4c2a058147 100644
--- a/mysql-test/suite/versioning/t/partition.test
+++ b/mysql-test/suite/versioning/t/partition.test
@@ -939,6 +939,15 @@ alter table t1 add partition (partition p2);
# Cleanup
drop table t1;
+--echo #
+--echo # MDEV-22178 Assertion `info->alias.str' failed in partition_info::check_partition_info instead of ER_VERS_WRONG_PARTS
+--echo #
+create or replace table t1 (a int) with system versioning;
+--error ER_VERS_WRONG_PARTS
+alter table t1 partition by system_time (partition pn current);
+# Cleanup
+drop table t1;
+
--echo # End of 10.3 tests
--echo #
diff --git a/mysql-test/suite/versioning/t/trx_id.test b/mysql-test/suite/versioning/t/trx_id.test
index 216f2082bed..db691a7ec19 100644
--- a/mysql-test/suite/versioning/t/trx_id.test
+++ b/mysql-test/suite/versioning/t/trx_id.test
@@ -14,7 +14,13 @@ create or replace table t1 (
period for system_time (sys_trx_start, sys_trx_end)
) with system versioning;
+--echo # No history inside the transaction
+start transaction;
insert into t1 (x) values (1);
+update t1 set x= x + 1;
+update t1 set x= x + 1;
+commit;
+select *, sys_trx_start > 1, sys_trx_end from t1 for system_time all;
--echo # ALTER ADD SYSTEM VERSIONING should write to mysql.transaction_registry
set @@system_versioning_alter_history=keep;
diff --git a/mysql-test/suite/versioning/t/update.test b/mysql-test/suite/versioning/t/update.test
index 06f81ea9064..7f99e307942 100644
--- a/mysql-test/suite/versioning/t/update.test
+++ b/mysql-test/suite/versioning/t/update.test
@@ -147,6 +147,21 @@ select @tmp2 = sys_trx_start as B2, salary from t2;
drop table t1;
drop table t2;
+--echo # Ensure FTS retains correct history
+replace_result $sys_datatype_expl SYS_DATATYPE;
+eval create table t1 (
+ x int, y text, fulltext (y),
+ row_start $sys_datatype_expl as row start invisible,
+ row_end $sys_datatype_expl as row end invisible,
+ period for system_time (row_start, row_end))
+with system versioning engine innodb;
+insert into t1 values (1, repeat('LONG', 2048));
+update t1 set x= x + 1;
+select x, left(y, 4), length(y), check_row(row_start, row_end) from t1 for system_time all order by x, y;
+update t1 set y= 'SHORT';
+select x, left(y, 4), length(y), check_row(row_start, row_end) from t1 for system_time all order by x, y;
+drop tables t1;
+
--echo ### Issue tempesta-tech/mariadb#365, bug 7 (duplicate of historical row)
create or replace table t1 (a int primary key, b int)
with system versioning engine myisam;
@@ -286,4 +301,29 @@ delete history from t1;
# cleanup
drop table t1;
+--echo #
+--echo # MDEV-23446 UPDATE does not insert history row if the row is not changed
+--echo #
+replace_result $sys_datatype_expl SYS_DATATYPE;
+eval create table t1 (
+ a int,
+ row_start $sys_datatype_expl as row start invisible,
+ row_end $sys_datatype_expl as row end invisible,
+ period for system_time (row_start, row_end)) with system versioning;
+insert into t1 values (1);
+update t1 set a= 1;
+select *, check_row(row_start, row_end) from t1 for system_time all order by row_end;
+
+--echo # multi-update
+create or replace table t2 like t1;
+create or replace table t3 like t1;
+insert into t2 values (1);
+insert into t3 values (1);
+update t2, t3 set t2.a= 1, t3.a= 1 where t2.a = t3.a;
+select *, check_row(row_start, row_end) from t2 for system_time all order by row_end;
+select *, check_row(row_start, row_end) from t2 for system_time all order by row_end;
+
+# cleanup
+drop tables t1, t2, t3;
+
source suite/versioning/common_finish.inc;