From deb9121fdf2152752346c767321e6e01aa5d6c69 Mon Sep 17 00:00:00 2001
From: Igor Babaev <igor@askmonty.org>
Date: Thu, 12 Sep 2019 23:00:49 -0700
Subject: MDEV-20576 A new assertion added to check validity of calculated     
       selectivity values fails

After having set the assertion that checks validity of selectivity values
returned by the function table_cond_selectivity() a test case from
order_by.tesst failed. The failure occurred because range optimizer could
return as an estimate of the cardinality of the ranges built for an index
a number exceeding the total number of records in the table.

The second bug is more subtle. It may happen when there are several
indexes with same prefix defined on the first joined table t accessed by
a constant ref access. In this case the range optimizer estimates the
number of accessed records of t for each usable index and these
estimates can be different. Only the first of these estimates is taken
into account when the selectivity of the ref access is calculated.
However the optimizer later can choose a different index that provides
a different estimate. The function table_condition_selectivity() could use
this estimate to discount the selectivity of the ref access. This could
lead to an selectivity value returned by this function that was greater
that 1.
---
 mysql-test/r/innodb_icp.result                  |  4 +-
 mysql-test/r/range_vs_index_merge.result        |  2 +-
 mysql-test/r/range_vs_index_merge_innodb.result |  2 +-
 mysql-test/r/selectivity.result                 | 85 +++++++++++++++++++++++++
 mysql-test/r/selectivity_innodb.result          | 85 +++++++++++++++++++++++++
 mysql-test/t/selectivity.test                   | 81 +++++++++++++++++++++++
 6 files changed, 255 insertions(+), 4 deletions(-)

(limited to 'mysql-test')

diff --git a/mysql-test/r/innodb_icp.result b/mysql-test/r/innodb_icp.result
index a5215bf9f0d..0c95f31ae95 100644
--- a/mysql-test/r/innodb_icp.result
+++ b/mysql-test/r/innodb_icp.result
@@ -679,7 +679,7 @@ EXPLAIN
 SELECT t1.b, t1.c FROM t1, t2 WHERE t1.a = t2.a AND t1.b != 0 
 HAVING t1.c != 5 ORDER BY t1.c;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	range	PRIMARY	PRIMARY	4	NULL	2	Using where; Using filesort
+1	SIMPLE	t1	range	PRIMARY	PRIMARY	4	NULL	1	Using where; Using filesort
 1	SIMPLE	t2	ref	a	a	515	test.t1.a	1	Using where
 SELECT t1.b, t1.c FROM t1, t2 WHERE t1.a = t2.a AND t1.b != 0
 HAVING t1.c != 5 ORDER BY t1.c;
@@ -690,7 +690,7 @@ EXPLAIN
 SELECT t1.b, t1.c FROM t1, t2 WHERE t1.a = t2.a AND t1.b != 0
 HAVING t1.c != 5 ORDER BY t1.c;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	range	PRIMARY	PRIMARY	4	NULL	2	Using where; Using filesort
+1	SIMPLE	t1	range	PRIMARY	PRIMARY	4	NULL	1	Using where; Using filesort
 1	SIMPLE	t2	ref	a	a	515	test.t1.a	1	Using where
 SELECT t1.b, t1.c FROM t1, t2 WHERE t1.a = t2.a AND t1.b != 0
 HAVING t1.c != 5 ORDER BY t1.c;
diff --git a/mysql-test/r/range_vs_index_merge.result b/mysql-test/r/range_vs_index_merge.result
index bc46a4fdd0b..4f3c36b7660 100644
--- a/mysql-test/r/range_vs_index_merge.result
+++ b/mysql-test/r/range_vs_index_merge.result
@@ -1795,7 +1795,7 @@ SELECT * FROM t1 FORCE KEY (state,capital)
 WHERE ( state = 'Alabama' OR state >= 'Colorado' ) AND id != 9 
 OR ( capital >= 'Topeka' OR state = 'Kansas' ) AND state != 'Texas';
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	range	state,capital	state	71	NULL	12	Using index condition; Using where
+1	SIMPLE	t1	range	state,capital	state	71	NULL	8	Using index condition; Using where
 SELECT * FROM t1 FORCE KEY (state,capital) 
 WHERE ( state = 'Alabama' OR state >= 'Colorado' ) AND id != 9 
 OR ( capital >= 'Topeka' OR state = 'Kansas' ) AND state != 'Texas';
diff --git a/mysql-test/r/range_vs_index_merge_innodb.result b/mysql-test/r/range_vs_index_merge_innodb.result
index a6ec200538d..08b7df66c67 100644
--- a/mysql-test/r/range_vs_index_merge_innodb.result
+++ b/mysql-test/r/range_vs_index_merge_innodb.result
@@ -1796,7 +1796,7 @@ SELECT * FROM t1 FORCE KEY (state,capital)
 WHERE ( state = 'Alabama' OR state >= 'Colorado' ) AND id != 9 
 OR ( capital >= 'Topeka' OR state = 'Kansas' ) AND state != 'Texas';
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	range	state,capital	state	71	NULL	10	Using index condition; Using where
+1	SIMPLE	t1	range	state,capital	state	71	NULL	8	Using index condition; Using where
 SELECT * FROM t1 FORCE KEY (state,capital) 
 WHERE ( state = 'Alabama' OR state >= 'Colorado' ) AND id != 9 
 OR ( capital >= 'Topeka' OR state = 'Kansas' ) AND state != 'Texas';
diff --git a/mysql-test/r/selectivity.result b/mysql-test/r/selectivity.result
index 3f5db42d341..d8b2d462952 100644
--- a/mysql-test/r/selectivity.result
+++ b/mysql-test/r/selectivity.result
@@ -1668,4 +1668,89 @@ Note	1003	select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1`
 drop table t1;
 set use_stat_tables= @save_use_stat_tables;
 set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity;
+#
+# MDEV-20576: failing assertion DBUG_ASSERT(0.0 < sel && sel <= 1)
+#
+set @@optimizer_use_condition_selectivity=2;
+set names utf8;
+CREATE DATABASE world;
+use world;
+CREATE TABLE Country (
+Code char(3) NOT NULL default '',
+Name char(52) NOT NULL default '',
+SurfaceArea float(10,2) NOT NULL default '0.00',
+Population int(11) NOT NULL default '0',
+Capital int(11) default NULL,
+PRIMARY KEY  (Code),
+UNIQUE INDEX (Name)
+);
+CREATE TABLE City (
+ID int(11) NOT NULL auto_increment,
+Name char(35) NOT NULL default '',
+Country char(3) NOT NULL default '',
+Population int(11) NOT NULL default '0',
+PRIMARY KEY  (ID),
+INDEX (Population),
+INDEX (Country) 
+);
+CREATE TABLE CountryLanguage (
+Country char(3) NOT NULL default '',
+Language char(30) NOT NULL default '',
+Percentage float(3,1) NOT NULL default '0.0',
+PRIMARY KEY  (Country, Language),
+INDEX (Percentage)
+);
+CREATE INDEX Name ON City(Name);
+CREATE INDEX CountryPopulation ON City(Country,Population);
+CREATE INDEX CountryName ON City(Country,Name);
+set @@optimizer_use_condition_selectivity=2;
+EXPLAIN
+SELECT * FROM City WHERE Country='FIN';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	City	ref	Country,CountryPopulation,CountryName	CountryName	3	const	5	Using index condition
+DROP DATABASE world;
+use test;
+CREATE TABLE t1 (
+a INT,
+b INT NOT NULL,
+c char(100),
+KEY (b, c),
+KEY (b, a, c)
+)
+DEFAULT CHARSET = utf8;
+INSERT INTO t1 VALUES
+(1,  1, 1),
+(2,  2, 2),
+(3,  3, 3),
+(4,  4, 4),
+(5,  5, 5),
+(6,  6, 6),
+(7,  7, 7),
+(8,  8, 8),
+(9,  9, 9);
+INSERT INTO t1 SELECT a + 10,  b, c FROM t1;
+INSERT INTO t1 SELECT a + 20,  b, c FROM t1;
+INSERT INTO t1 SELECT a + 40,  b, c FROM t1;
+INSERT INTO t1 SELECT a + 80,  b, c FROM t1;
+INSERT INTO t1 SELECT a + 160, b, c FROM t1;
+INSERT INTO t1 SELECT a + 320, b, c FROM t1;
+INSERT INTO t1 SELECT a + 640, b, c FROM t1;
+INSERT INTO t1 SELECT a + 1280, b, c FROM t1 LIMIT 80;
+EXPLAIN
+SELECT a FROM t1 WHERE b = 1 ORDER BY c DESC LIMIT 9;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	range	b,b_2	b	4	NULL	226	Using where
+SELECT a FROM t1 WHERE b = 1 ORDER BY c DESC LIMIT 9;
+a
+2071
+2061
+2051
+2041
+2031
+2021
+2011
+2001
+1991
+set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity;
+DROP TABLE t1;
 # End of 10.1 tests
diff --git a/mysql-test/r/selectivity_innodb.result b/mysql-test/r/selectivity_innodb.result
index 1d73c2f5d50..56a79001b13 100644
--- a/mysql-test/r/selectivity_innodb.result
+++ b/mysql-test/r/selectivity_innodb.result
@@ -1678,6 +1678,91 @@ Note	1003	select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1`
 drop table t1;
 set use_stat_tables= @save_use_stat_tables;
 set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity;
+#
+# MDEV-20576: failing assertion DBUG_ASSERT(0.0 < sel && sel <= 1)
+#
+set @@optimizer_use_condition_selectivity=2;
+set names utf8;
+CREATE DATABASE world;
+use world;
+CREATE TABLE Country (
+Code char(3) NOT NULL default '',
+Name char(52) NOT NULL default '',
+SurfaceArea float(10,2) NOT NULL default '0.00',
+Population int(11) NOT NULL default '0',
+Capital int(11) default NULL,
+PRIMARY KEY  (Code),
+UNIQUE INDEX (Name)
+);
+CREATE TABLE City (
+ID int(11) NOT NULL auto_increment,
+Name char(35) NOT NULL default '',
+Country char(3) NOT NULL default '',
+Population int(11) NOT NULL default '0',
+PRIMARY KEY  (ID),
+INDEX (Population),
+INDEX (Country) 
+);
+CREATE TABLE CountryLanguage (
+Country char(3) NOT NULL default '',
+Language char(30) NOT NULL default '',
+Percentage float(3,1) NOT NULL default '0.0',
+PRIMARY KEY  (Country, Language),
+INDEX (Percentage)
+);
+CREATE INDEX Name ON City(Name);
+CREATE INDEX CountryPopulation ON City(Country,Population);
+CREATE INDEX CountryName ON City(Country,Name);
+set @@optimizer_use_condition_selectivity=2;
+EXPLAIN
+SELECT * FROM City WHERE Country='FIN';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	City	ref	Country,CountryPopulation,CountryName	Country	3	const	7	Using index condition
+DROP DATABASE world;
+use test;
+CREATE TABLE t1 (
+a INT,
+b INT NOT NULL,
+c char(100),
+KEY (b, c),
+KEY (b, a, c)
+)
+DEFAULT CHARSET = utf8;
+INSERT INTO t1 VALUES
+(1,  1, 1),
+(2,  2, 2),
+(3,  3, 3),
+(4,  4, 4),
+(5,  5, 5),
+(6,  6, 6),
+(7,  7, 7),
+(8,  8, 8),
+(9,  9, 9);
+INSERT INTO t1 SELECT a + 10,  b, c FROM t1;
+INSERT INTO t1 SELECT a + 20,  b, c FROM t1;
+INSERT INTO t1 SELECT a + 40,  b, c FROM t1;
+INSERT INTO t1 SELECT a + 80,  b, c FROM t1;
+INSERT INTO t1 SELECT a + 160, b, c FROM t1;
+INSERT INTO t1 SELECT a + 320, b, c FROM t1;
+INSERT INTO t1 SELECT a + 640, b, c FROM t1;
+INSERT INTO t1 SELECT a + 1280, b, c FROM t1 LIMIT 80;
+EXPLAIN
+SELECT a FROM t1 WHERE b = 1 ORDER BY c DESC LIMIT 9;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ref	b,b_2	b_2	4	const	207	Using where; Using index; Using filesort
+SELECT a FROM t1 WHERE b = 1 ORDER BY c DESC LIMIT 9;
+a
+2071
+81
+71
+61
+51
+41
+31
+21
+11
+set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity;
+DROP TABLE t1;
 # End of 10.1 tests
 set optimizer_switch=@save_optimizer_switch_for_selectivity_test;
 set @tmp_ust= @@use_stat_tables;
diff --git a/mysql-test/t/selectivity.test b/mysql-test/t/selectivity.test
index f1c9d6b31b8..6e93e60fff6 100644
--- a/mysql-test/t/selectivity.test
+++ b/mysql-test/t/selectivity.test
@@ -1124,5 +1124,86 @@ drop table t1;
 
 set use_stat_tables= @save_use_stat_tables;
 set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity;
+
+--echo #
+--echo # MDEV-20576: failing assertion DBUG_ASSERT(0.0 < sel && sel <= 1)
+--echo #
+
+set @@optimizer_use_condition_selectivity=2;
+
+set names utf8;
+
+CREATE DATABASE world;
+
+use world;
+
+--source include/world_schema.inc
+
+--disable_query_log
+--disable_result_log
+--disable_warnings
+--source include/world.inc
+--enable_warnings
+--enable_result_log
+--enable_query_log
+
+CREATE INDEX Name ON City(Name);
+CREATE INDEX CountryPopulation ON City(Country,Population);
+CREATE INDEX CountryName ON City(Country,Name);
+
+--disable_query_log
+--disable_result_log
+--disable_warnings
+ANALYZE TABLE City;
+--enable_warnings
+--enable_result_log
+--enable_query_log
+
+set @@optimizer_use_condition_selectivity=2;
+
+EXPLAIN
+SELECT * FROM City WHERE Country='FIN';
+
+DROP DATABASE world;
+
+use test;
+
+CREATE TABLE t1 (
+  a INT,
+  b INT NOT NULL,
+  c char(100),
+  KEY (b, c),
+  KEY (b, a, c)
+)
+DEFAULT CHARSET = utf8;
+
+INSERT INTO t1 VALUES
+(1,  1, 1),
+(2,  2, 2),
+(3,  3, 3),
+(4,  4, 4),
+(5,  5, 5),
+(6,  6, 6),
+(7,  7, 7),
+(8,  8, 8),
+(9,  9, 9);
+
+INSERT INTO t1 SELECT a + 10,  b, c FROM t1;
+INSERT INTO t1 SELECT a + 20,  b, c FROM t1;
+INSERT INTO t1 SELECT a + 40,  b, c FROM t1;
+INSERT INTO t1 SELECT a + 80,  b, c FROM t1;
+INSERT INTO t1 SELECT a + 160, b, c FROM t1;
+INSERT INTO t1 SELECT a + 320, b, c FROM t1;
+INSERT INTO t1 SELECT a + 640, b, c FROM t1;
+INSERT INTO t1 SELECT a + 1280, b, c FROM t1 LIMIT 80;
+
+EXPLAIN
+SELECT a FROM t1 WHERE b = 1 ORDER BY c DESC LIMIT 9;
+SELECT a FROM t1 WHERE b = 1 ORDER BY c DESC LIMIT 9;
+
+set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity;
+
+DROP TABLE t1;
+
 --echo # End of 10.1 tests
 
-- 
cgit v1.2.1


From 40beeb1402e1bf443eadb2986fd87cd3f0e9ff24 Mon Sep 17 00:00:00 2001
From: Teemu Ollakka <teemu.ollakka@galeracluster.com>
Date: Fri, 13 Sep 2019 09:18:11 +0300
Subject: MDEV-20561 Galera node shutdown fails in non-Primary (#1386)

Command COM_SHUTDOWN was rejected in non-Primary because
server_command_flags[COM_SHUTDOWN] had value CF_NO_COM_MULTI
instead of CF_SKIP_WSREP_CHECK.

As a fix removed assignment
server_command_flags[CF_NO_COM_MULTI]= CF_NO_COM_MULTI
which overwrote server_command_flags[COM_SHUTDOWN].
---
 .../suite/galera/r/galera_shutdown_nonprim.result  |  9 ++++++
 .../suite/galera/t/galera_shutdown_nonprim.test    | 36 ++++++++++++++++++++++
 2 files changed, 45 insertions(+)
 create mode 100644 mysql-test/suite/galera/r/galera_shutdown_nonprim.result
 create mode 100644 mysql-test/suite/galera/t/galera_shutdown_nonprim.test

(limited to 'mysql-test')

diff --git a/mysql-test/suite/galera/r/galera_shutdown_nonprim.result b/mysql-test/suite/galera/r/galera_shutdown_nonprim.result
new file mode 100644
index 00000000000..5353543e395
--- /dev/null
+++ b/mysql-test/suite/galera/r/galera_shutdown_nonprim.result
@@ -0,0 +1,9 @@
+connection node_1;
+connection node_2;
+connection node_1;
+SET GLOBAL wsrep_provider_options = 'pc.weight=2';
+connection node_2;
+SET GLOBAL wsrep_provider_options = 'gmcast.isolate = 1';
+SET SESSION wsrep_sync_wait = 0;
+connection node_1;
+SET GLOBAL wsrep_provider_options = 'pc.weight = 1';
diff --git a/mysql-test/suite/galera/t/galera_shutdown_nonprim.test b/mysql-test/suite/galera/t/galera_shutdown_nonprim.test
new file mode 100644
index 00000000000..cf7018cd751
--- /dev/null
+++ b/mysql-test/suite/galera/t/galera_shutdown_nonprim.test
@@ -0,0 +1,36 @@
+#
+# Check that server can be shut down in non-primary configuration.
+#
+
+--source include/galera_cluster.inc
+--source include/have_innodb.inc
+
+--let $node_1 = node_1
+--let $node_2 = node_2
+--source include/auto_increment_offset_save.inc
+
+--connection node_1
+# Set higher weight for node_1 to keep it in primary
+# while node_2 is isolated.
+SET GLOBAL wsrep_provider_options = 'pc.weight=2';
+
+--connection node_2
+# Isolate node_2 from the group and wait until wsrep_ready becomes OFF.
+SET GLOBAL wsrep_provider_options = 'gmcast.isolate = 1';
+SET SESSION wsrep_sync_wait = 0;
+--let $wait_condition = SELECT VARIABLE_VALUE = 'OFF' FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME = 'wsrep_ready'
+--source include/wait_condition.inc
+
+# Verify that graceful shutdown succeeds.
+--source include/shutdown_mysqld.inc
+--source include/start_mysqld.inc
+
+--let $wait_condition = SELECT VARIABLE_VALUE = 2 FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME = 'wsrep_cluster_size';
+--source include/wait_condition.inc
+
+--connection node_1
+--source include/wait_condition.inc
+
+# Restore original settings.
+SET GLOBAL wsrep_provider_options = 'pc.weight = 1';
+--source include/auto_increment_offset_restore.inc
-- 
cgit v1.2.1


From 3422c13ab748a9da688c00b1b1e522cbcd38d4a7 Mon Sep 17 00:00:00 2001
From: =?UTF-8?q?Jan=20Lindstr=C3=B6m?= <jan.lindstrom@mariadb.com>
Date: Wed, 11 Sep 2019 14:15:17 +0300
Subject: Try to fix galera.MW-336 test case.

---
 mysql-test/suite/galera/r/MW-336.result |  8 +++-----
 mysql-test/suite/galera/t/MW-336.test   | 13 ++++++-------
 2 files changed, 9 insertions(+), 12 deletions(-)

(limited to 'mysql-test')

diff --git a/mysql-test/suite/galera/r/MW-336.result b/mysql-test/suite/galera/r/MW-336.result
index ed2f0755f0d..b91e73aaada 100644
--- a/mysql-test/suite/galera/r/MW-336.result
+++ b/mysql-test/suite/galera/r/MW-336.result
@@ -6,10 +6,6 @@ SET GLOBAL wsrep_slave_threads = 1;
 connection node_2;
 INSERT INTO t1 VALUES (1);
 connection node_1;
-SET SESSION wsrep_sync_wait=15;
-SELECT COUNT(*) FROM t1;
-COUNT(*)
-1
 SET GLOBAL wsrep_slave_threads = 10;
 # Set slave threads to 10 step 2
 SET GLOBAL wsrep_slave_threads = 20;
@@ -43,9 +39,11 @@ INSERT INTO t1 VALUES (17);
 INSERT INTO t1 VALUES (18);
 INSERT INTO t1 VALUES (19);
 INSERT INTO t1 VALUES (20);
+INSERT INTO t1 VALUES (21);
+INSERT INTO t1 VALUES (22);
 connection node_1;
 SELECT COUNT(*) FROM t1;
 COUNT(*)
-21
+23
 SET GLOBAL wsrep_slave_threads = 1;
 DROP TABLE t1;
diff --git a/mysql-test/suite/galera/t/MW-336.test b/mysql-test/suite/galera/t/MW-336.test
index b6df78ac7a6..4b606d58772 100644
--- a/mysql-test/suite/galera/t/MW-336.test
+++ b/mysql-test/suite/galera/t/MW-336.test
@@ -13,7 +13,7 @@ SET GLOBAL wsrep_slave_threads = 10;
 
 # ensure that the threads have actually started running
 --echo # Set slave threads to 10 step 1
---let $wait_condition = SELECT COUNT(*) = 10 FROM INFORMATION_SCHEMA.PROCESSLIST WHERE USER = 'system user' AND STATE = 'wsrep applier idle';
+--let $wait_condition = SELECT VARIABLE_VALUE = 10 FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME = 'wsrep_applier_thread_count';
 --let $wait_condition_on_error_output = SELECT COUNT(*), 10 as EXPECTED_VALUE FROM INFORMATION_SCHEMA.PROCESSLIST WHERE USER = 'system user' AND STATE = 'wsrep applier idle'; show processlist
 --source include/wait_condition_with_debug.inc
 
@@ -23,20 +23,17 @@ SET GLOBAL wsrep_slave_threads = 1;
 INSERT INTO t1 VALUES (1);
 
 --connection node_1
-SET SESSION wsrep_sync_wait=15;
-SELECT COUNT(*) FROM t1;
-
 SET GLOBAL wsrep_slave_threads = 10;
 
 --echo # Set slave threads to 10 step 2
---let $wait_condition = SELECT COUNT(*) = 10 FROM INFORMATION_SCHEMA.PROCESSLIST WHERE USER = 'system user' AND STATE = 'wsrep applier idle';
+--let $wait_condition = SELECT VARIABLE_VALUE = 10 FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME = 'wsrep_applier_thread_count';
 --let $wait_condition_on_error_output = SELECT COUNT(*), 10 as EXPECTED_VALUE FROM INFORMATION_SCHEMA.PROCESSLIST WHERE USER = 'system user' AND STATE = 'wsrep applier idle'; show processlist
 --source include/wait_condition_with_debug.inc
 
 SET GLOBAL wsrep_slave_threads = 20;
 
 --echo # Set slave threads to 20
---let $wait_condition = SELECT COUNT(*) = 20 FROM INFORMATION_SCHEMA.PROCESSLIST WHERE USER = 'system user' AND STATE = 'wsrep applier idle';
+--let $wait_condition = SELECT VARIABLE_VALUE = 20 FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME = 'wsrep_applier_thread_count';
 --let $wait_condition_on_error_output = SELECT COUNT(*), 20 as EXPECTED_VALUE FROM INFORMATION_SCHEMA.PROCESSLIST WHERE USER = 'system user' AND STATE = 'wsrep applier idle'; show processlist
 --source include/wait_condition_with_debug.inc
 
@@ -58,7 +55,7 @@ INSERT INTO t1 VALUES (10);
 SET GLOBAL wsrep_slave_threads = 10;
 SELECT COUNT(*) FROM t1;
 --echo # Set slave threads to 10 step 3
---let $wait_condition = SELECT COUNT(*) = 10 FROM INFORMATION_SCHEMA.PROCESSLIST WHERE USER = 'system user' AND STATE = 'wsrep applier idle';
+--let $wait_condition = SELECT VARIABLE_VALUE = 10 FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME = 'wsrep_applier_thread_count';
 --let $wait_condition_on_error_output = SELECT COUNT(*), 10 as EXPECTED_VALUE FROM INFORMATION_SCHEMA.PROCESSLIST WHERE USER = 'system user' AND STATE = 'wsrep applier idle'; show processlist
 --source include/wait_condition_with_debug.inc
 
@@ -73,6 +70,8 @@ INSERT INTO t1 VALUES (17);
 INSERT INTO t1 VALUES (18);
 INSERT INTO t1 VALUES (19);
 INSERT INTO t1 VALUES (20);
+INSERT INTO t1 VALUES (21);
+INSERT INTO t1 VALUES (22);
 
 --connection node_1
 SELECT COUNT(*) FROM t1;
-- 
cgit v1.2.1


From 23657a21018d0b3d0464bbd55236113ebcd3d4b7 Mon Sep 17 00:00:00 2001
From: =?UTF-8?q?Marko=20M=C3=A4kel=C3=A4?= <marko.makela@mariadb.com>
Date: Fri, 13 Sep 2019 17:07:58 +0300
Subject: MDEV-13893/MDEV-12699: Enable encryption.innodb-redo-badkey

The test had been disabled in 10.2 due to frequent failures,
in 5ec9b88e11118c798ff2381771a72f76b2b72f9e.
After the problems were addressed, we failed to re-enable the test
until now.
---
 mysql-test/suite/encryption/disabled.def | 1 -
 1 file changed, 1 deletion(-)

(limited to 'mysql-test')

diff --git a/mysql-test/suite/encryption/disabled.def b/mysql-test/suite/encryption/disabled.def
index 746faf49873..d92d3495cb8 100644
--- a/mysql-test/suite/encryption/disabled.def
+++ b/mysql-test/suite/encryption/disabled.def
@@ -12,4 +12,3 @@
 
 innodb_scrub            : MDEV-8139 scrubbing does not work reliably
 innodb_scrub_background : MDEV-8139 scrubbing does not work reliably
-innodb-redo-badkey      : MDEV-13893/MDEV-12699 fix recovery of corrupted pages
-- 
cgit v1.2.1


From 0954bcb6639af47a8b57eb426aee9bba4036e5f1 Mon Sep 17 00:00:00 2001
From: Igor Babaev <igor@askmonty.org>
Date: Fri, 13 Sep 2019 15:09:28 -0700
Subject: Post fix after the patch for MDEV-20576.

Adjusted test results.
---
 mysql-test/r/selectivity_innodb.result | 20 ++++++++++----------
 mysql-test/t/selectivity.test          |  2 +-
 2 files changed, 11 insertions(+), 11 deletions(-)

(limited to 'mysql-test')

diff --git a/mysql-test/r/selectivity_innodb.result b/mysql-test/r/selectivity_innodb.result
index 56a79001b13..719156a77de 100644
--- a/mysql-test/r/selectivity_innodb.result
+++ b/mysql-test/r/selectivity_innodb.result
@@ -1726,7 +1726,7 @@ b INT NOT NULL,
 c char(100),
 KEY (b, c),
 KEY (b, a, c)
-)
+) ENGINE=MyISAM
 DEFAULT CHARSET = utf8;
 INSERT INTO t1 VALUES
 (1,  1, 1),
@@ -1749,18 +1749,18 @@ INSERT INTO t1 SELECT a + 1280, b, c FROM t1 LIMIT 80;
 EXPLAIN
 SELECT a FROM t1 WHERE b = 1 ORDER BY c DESC LIMIT 9;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	ref	b,b_2	b_2	4	const	207	Using where; Using index; Using filesort
+1	SIMPLE	t1	range	b,b_2	b	4	NULL	226	Using where
 SELECT a FROM t1 WHERE b = 1 ORDER BY c DESC LIMIT 9;
 a
 2071
-81
-71
-61
-51
-41
-31
-21
-11
+2061
+2051
+2041
+2031
+2021
+2011
+2001
+1991
 set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity;
 DROP TABLE t1;
 # End of 10.1 tests
diff --git a/mysql-test/t/selectivity.test b/mysql-test/t/selectivity.test
index 6e93e60fff6..0deacc390db 100644
--- a/mysql-test/t/selectivity.test
+++ b/mysql-test/t/selectivity.test
@@ -1174,7 +1174,7 @@ CREATE TABLE t1 (
   c char(100),
   KEY (b, c),
   KEY (b, a, c)
-)
+) ENGINE=MyISAM
 DEFAULT CHARSET = utf8;
 
 INSERT INTO t1 VALUES
-- 
cgit v1.2.1


From ae2b88ff3f94253921fed5c48422adeebe7e623d Mon Sep 17 00:00:00 2001
From: Igor Babaev <igor@askmonty.org>
Date: Fri, 13 Sep 2019 21:10:52 -0700
Subject: Adjusted test results after the change of a test case

---
 mysql-test/r/selectivity.result | 2 +-
 1 file changed, 1 insertion(+), 1 deletion(-)

(limited to 'mysql-test')

diff --git a/mysql-test/r/selectivity.result b/mysql-test/r/selectivity.result
index d8b2d462952..d0bbb46cb0a 100644
--- a/mysql-test/r/selectivity.result
+++ b/mysql-test/r/selectivity.result
@@ -1716,7 +1716,7 @@ b INT NOT NULL,
 c char(100),
 KEY (b, c),
 KEY (b, a, c)
-)
+) ENGINE=MyISAM
 DEFAULT CHARSET = utf8;
 INSERT INTO t1 VALUES
 (1,  1, 1),
-- 
cgit v1.2.1


From 2a98d0b5ca2324405d65415423258cc088fa2f8a Mon Sep 17 00:00:00 2001
From: =?UTF-8?q?Jan=20Lindstr=C3=B6m?= <jan.lindstrom@mariadb.com>
Date: Sat, 14 Sep 2019 12:14:20 +0300
Subject: Fix #2 for galera.MW-336

---
 mysql-test/suite/galera/t/MW-336.test | 5 +++--
 1 file changed, 3 insertions(+), 2 deletions(-)

(limited to 'mysql-test')

diff --git a/mysql-test/suite/galera/t/MW-336.test b/mysql-test/suite/galera/t/MW-336.test
index 4b606d58772..0e92094da6c 100644
--- a/mysql-test/suite/galera/t/MW-336.test
+++ b/mysql-test/suite/galera/t/MW-336.test
@@ -25,9 +25,10 @@ INSERT INTO t1 VALUES (1);
 --connection node_1
 SET GLOBAL wsrep_slave_threads = 10;
 
+# Note that above insert could be handled by one of the slave threads
 --echo # Set slave threads to 10 step 2
---let $wait_condition = SELECT VARIABLE_VALUE = 10 FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME = 'wsrep_applier_thread_count';
---let $wait_condition_on_error_output = SELECT COUNT(*), 10 as EXPECTED_VALUE FROM INFORMATION_SCHEMA.PROCESSLIST WHERE USER = 'system user' AND STATE = 'wsrep applier idle'; show processlist
+--let $wait_condition = SELECT VARIABLE_VALUE >= 9 FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME = 'wsrep_applier_thread_count';
+--let $wait_condition_on_error_output = SELECT COUNT(*), 9 as EXPECTED_VALUE FROM INFORMATION_SCHEMA.PROCESSLIST WHERE USER = 'system user' AND STATE = 'wsrep applier idle'; show processlist
 --source include/wait_condition_with_debug.inc
 
 SET GLOBAL wsrep_slave_threads = 20;
-- 
cgit v1.2.1


From 708f1e341915b9fe2968581c60fb6a224b185af9 Mon Sep 17 00:00:00 2001
From: Thirunarayanan Balathandayuthapani <thiru@mariadb.com>
Date: Tue, 17 Sep 2019 20:47:58 +0530
Subject: MDEV-19647 Server hangs after dropping full text indexes and restart

- There is no need to add the table in fts_optimize_wq if there is
no fts indexes associated with it.
---
 mysql-test/suite/innodb_fts/r/innodb_fts_misc.result | 14 ++++++++++----
 mysql-test/suite/innodb_fts/t/innodb_fts_misc.test   |  7 +++++--
 2 files changed, 15 insertions(+), 6 deletions(-)

(limited to 'mysql-test')

diff --git a/mysql-test/suite/innodb_fts/r/innodb_fts_misc.result b/mysql-test/suite/innodb_fts/r/innodb_fts_misc.result
index 628fe8519ed..71eded74be4 100644
--- a/mysql-test/suite/innodb_fts/r/innodb_fts_misc.result
+++ b/mysql-test/suite/innodb_fts/r/innodb_fts_misc.result
@@ -734,15 +734,21 @@ count(*)
 DROP TABLE t1;
 "----------Test27---------"
 CREATE TABLE t1 (id INT,char_column VARCHAR(60));
+CREATE TABLE t2 (FTS_DOC_ID BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, a TEXT)ENGINE=InnoDB;
+ALTER TABLE t2 DROP a;
 SET @@autocommit=0;
 CREATE FULLTEXT INDEX i ON t1 (char_column);
 INSERT INTO t1 values (1,'aaa');
 "restart server..."
-# Restart the server
---source include/restart_mysqld.inc
-DELETE FROM t1 WHERE MATCH(char_column) AGAINST ('bbb')
+SHOW CREATE TABLE t2;
+Table	Create Table
+t2	CREATE TABLE `t2` (
+  `FTS_DOC_ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
+  PRIMARY KEY (`FTS_DOC_ID`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+DELETE FROM t1 WHERE MATCH(char_column) AGAINST ('bbb');
 SET @@autocommit=1;
-DROP TABLE t1;
+DROP TABLE t1, t2;
 "----------Test28---------"
 drop table if exists `fts_test`;
 Warnings:
diff --git a/mysql-test/suite/innodb_fts/t/innodb_fts_misc.test b/mysql-test/suite/innodb_fts/t/innodb_fts_misc.test
index 68ca8974512..e226b276966 100644
--- a/mysql-test/suite/innodb_fts/t/innodb_fts_misc.test
+++ b/mysql-test/suite/innodb_fts/t/innodb_fts_misc.test
@@ -667,15 +667,18 @@ DROP TABLE t1;
 --echo "----------Test27---------"
 #27 Crash after server restart
 CREATE TABLE t1 (id INT,char_column VARCHAR(60));
+CREATE TABLE t2 (FTS_DOC_ID BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, a TEXT)ENGINE=InnoDB;
+ALTER TABLE t2 DROP a;
 SET @@autocommit=0;
 CREATE FULLTEXT INDEX i ON t1 (char_column);
 INSERT INTO t1 values (1,'aaa');
-echo "restart server..."
+echo "restart server...";
 # Restart the server
 --source include/restart_mysqld.inc
+SHOW CREATE TABLE t2;
 DELETE FROM t1 WHERE MATCH(char_column) AGAINST ('bbb');
 SET @@autocommit=1;
-DROP TABLE t1;
+DROP TABLE t1, t2;
 
 --echo "----------Test28---------"
 drop table if exists `fts_test`;
-- 
cgit v1.2.1


From c471bfb34e970075d8649247ab088b19a097eaf4 Mon Sep 17 00:00:00 2001
From: Varun Gupta <varun.gupta@mariadb.com>
Date: Wed, 18 Sep 2019 01:44:36 +0530
Subject: Fixing a test to reset to original state

---
 mysql-test/r/stat_tables.result        | 3 ++-
 mysql-test/r/stat_tables_innodb.result | 3 ++-
 mysql-test/t/stat_tables.test          | 5 +++--
 3 files changed, 7 insertions(+), 4 deletions(-)

(limited to 'mysql-test')

diff --git a/mysql-test/r/stat_tables.result b/mysql-test/r/stat_tables.result
index 50767d7e3dc..c47a47cea31 100644
--- a/mysql-test/r/stat_tables.result
+++ b/mysql-test/r/stat_tables.result
@@ -797,9 +797,10 @@ col1
 2004-01-01
 2004-02-29
 0000-10-31
-drop table t1;
 set @@sql_mode= @save_sql_mode;
 set use_stat_tables=@save_use_stat_tables;
 set @@histogram_size= @save_histogram_size;
 set @@optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity;
+drop table t1;
+# please keep this at the last
 set @@global.histogram_size=@save_histogram_size;
diff --git a/mysql-test/r/stat_tables_innodb.result b/mysql-test/r/stat_tables_innodb.result
index 70c8808efbb..dcfdb55f9c9 100644
--- a/mysql-test/r/stat_tables_innodb.result
+++ b/mysql-test/r/stat_tables_innodb.result
@@ -824,11 +824,12 @@ col1
 2004-01-01
 2004-02-29
 0000-10-31
-drop table t1;
 set @@sql_mode= @save_sql_mode;
 set use_stat_tables=@save_use_stat_tables;
 set @@histogram_size= @save_histogram_size;
 set @@optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity;
+drop table t1;
+# please keep this at the last
 set @@global.histogram_size=@save_histogram_size;
 set optimizer_switch=@save_optimizer_switch_for_stat_tables_test;
 SET SESSION STORAGE_ENGINE=DEFAULT;
diff --git a/mysql-test/t/stat_tables.test b/mysql-test/t/stat_tables.test
index 4c49df82ca6..880e6378ca5 100644
--- a/mysql-test/t/stat_tables.test
+++ b/mysql-test/t/stat_tables.test
@@ -540,10 +540,11 @@ analyze table t1;
 update mysql.column_stats set min_value='2004-0-31123' where db_name='test' and table_name='t1';
 select min_value from mysql.column_stats where db_name='test' and table_name='t1';
 select * from t1;
-drop table t1;
-
 set @@sql_mode= @save_sql_mode;
 set use_stat_tables=@save_use_stat_tables;
 set @@histogram_size= @save_histogram_size;
 set @@optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity;
+drop table t1;
+
+--echo # please keep this at the last
 set @@global.histogram_size=@save_histogram_size;
-- 
cgit v1.2.1


From 8a79fa0e4d0385818da056f7a4a39fde95d62fe3 Mon Sep 17 00:00:00 2001
From: Thirunarayanan Balathandayuthapani <thiru@mariadb.com>
Date: Wed, 18 Sep 2019 13:22:08 +0530
Subject: MDEV-19529	InnoDB hang on DROP FULLTEXT INDEX

Problem:
=======
  During dropping of fts index, InnoDB waits for fts_optimize_remove_table()
and it holds dict_sys->mutex and dict_operaiton_lock even though the
table id is not present in the queue. But fts_optimize_thread does wait
for dict_sys->mutex to process the unrelated table id from the slot.

Solution:
========
  Whenever table is added to fts_optimize_wq, update the fts_status
of in-memory fts subsystem to TABLE_IN_QUEUE. Whenever drop index
wants to remove table from the queue, it can check the fts_status
to decide whether it should send the MSG_DELETE_TABLE to the queue.

Removed the following functions because these are all deadcode.
dict_table_wait_for_bg_threads_to_exit(),
fts_wait_for_background_thread_to_start(),fts_start_shutdown(), fts_shudown().
---
 .../suite/innodb_fts/r/concurrent_insert.result    | 18 +++++++++++++
 .../suite/innodb_fts/t/concurrent_insert.test      | 31 ++++++++++++++++++++++
 2 files changed, 49 insertions(+)

(limited to 'mysql-test')

diff --git a/mysql-test/suite/innodb_fts/r/concurrent_insert.result b/mysql-test/suite/innodb_fts/r/concurrent_insert.result
index b9798ca1a74..416e8bf432c 100644
--- a/mysql-test/suite/innodb_fts/r/concurrent_insert.result
+++ b/mysql-test/suite/innodb_fts/r/concurrent_insert.result
@@ -6,3 +6,21 @@ REPLACE INTO t1(a) VALUES('aaa');
 SET DEBUG_SYNC = 'now SIGNAL race';
 SET DEBUG_SYNC = 'RESET';
 DROP TABLE t1;
+#
+# MDEV-19529 InnoDB hang on DROP FULLTEXT INDEX
+#
+CREATE TABLE t1(f1 CHAR(100), FULLTEXT(f1))ENGINE=InnoDB;
+INSERT INTO t1 VALUES('test');
+CREATE TABLE t2 (f1 char(100), FULLTEXT idx1(f1))ENGINE=InnoDB;
+INSERT INTO t2 VALUES('mariadb');
+SET GLOBAL debug_dbug ='+d,fts_instrument_sync_request,ib_optimize_wq_hang';
+SET DEBUG_SYNC= 'fts_instrument_sync_request
+		 SIGNAL drop_index_start WAIT_FOR sync_op';
+INSERT INTO t1 VALUES('Keyword');
+SET DEBUG_SYNC='now WAIT_FOR drop_index_start';
+SET DEBUG_SYNC= 'norebuild_fts_drop SIGNAL sync_op WAIT_FOR fts_drop_index';
+ALTER TABLE t2 drop index idx1;
+set DEBUG_SYNC= 'now SIGNAL fts_drop_index';
+SET global DEBUG_DBUG=RESET;
+drop table t1, t2;
+set DEBUG_SYNC=RESET;
diff --git a/mysql-test/suite/innodb_fts/t/concurrent_insert.test b/mysql-test/suite/innodb_fts/t/concurrent_insert.test
index e5d61cd8b05..77097d44dc5 100644
--- a/mysql-test/suite/innodb_fts/t/concurrent_insert.test
+++ b/mysql-test/suite/innodb_fts/t/concurrent_insert.test
@@ -18,3 +18,34 @@ reap;
 SET DEBUG_SYNC = 'RESET';
 
 DROP TABLE t1;
+
+--echo #
+--echo # MDEV-19529 InnoDB hang on DROP FULLTEXT INDEX
+--echo #
+
+CREATE TABLE t1(f1 CHAR(100), FULLTEXT(f1))ENGINE=InnoDB;
+INSERT INTO t1 VALUES('test');
+CREATE TABLE t2 (f1 char(100), FULLTEXT idx1(f1))ENGINE=InnoDB;
+INSERT INTO t2 VALUES('mariadb');
+
+connection default;
+SET GLOBAL debug_dbug ='+d,fts_instrument_sync_request,ib_optimize_wq_hang';
+SET DEBUG_SYNC= 'fts_instrument_sync_request
+		 SIGNAL drop_index_start WAIT_FOR sync_op';
+send INSERT INTO t1 VALUES('Keyword');
+
+connect(con1,localhost,root,,,);
+SET DEBUG_SYNC='now WAIT_FOR drop_index_start';
+SET DEBUG_SYNC= 'norebuild_fts_drop SIGNAL sync_op WAIT_FOR fts_drop_index';
+send ALTER TABLE t2 drop index idx1;
+
+connection default;
+reap;
+set DEBUG_SYNC= 'now SIGNAL fts_drop_index';
+
+connection con1;
+reap;
+SET global DEBUG_DBUG=RESET;
+drop table t1, t2;
+connection default;
+set DEBUG_SYNC=RESET;
-- 
cgit v1.2.1


From 273d8eb12c40a6dcd05a8148bdfba3f1fd96e764 Mon Sep 17 00:00:00 2001
From: Varun Gupta <varun.gupta@mariadb.com>
Date: Wed, 18 Sep 2019 01:59:29 +0530
Subject: MDEV-20589: Server still crashes in
 Field::set_warning_truncated_wrong_value

The flag is_stat_field is not set for the min_value and max_value of field items
inside table share. This is a must requirement as we don't want to throw
warnings of truncation when we read values from the statistics table to the column
statistics of table share fields.
---
 mysql-test/r/stat_tables.result        | 23 +++++++++++++++++++++++
 mysql-test/r/stat_tables_innodb.result | 23 +++++++++++++++++++++++
 mysql-test/t/stat_tables.test          | 22 ++++++++++++++++++++++
 3 files changed, 68 insertions(+)

(limited to 'mysql-test')

diff --git a/mysql-test/r/stat_tables.result b/mysql-test/r/stat_tables.result
index c47a47cea31..bb3a0a80f7e 100644
--- a/mysql-test/r/stat_tables.result
+++ b/mysql-test/r/stat_tables.result
@@ -802,5 +802,28 @@ set use_stat_tables=@save_use_stat_tables;
 set @@histogram_size= @save_histogram_size;
 set @@optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity;
 drop table t1;
+#
+# MDEV-20589: Server still crashes in Field::set_warning_truncated_wrong_value
+#
+set names utf8;
+create table t1 ( a varchar(255) character set utf8);
+insert into t1 values (REPEAT('ӥ',255)), (REPEAT('ç',255));
+set use_stat_tables='preferably';
+analyze table t1 persistent for all;
+Table	Op	Msg_type	Msg_text
+test.t1	analyze	status	Engine-independent statistics collected
+test.t1	analyze	status	OK
+set @save_sql_mode= @@sql_mode;
+set sql_mode='ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
+update mysql.column_stats set min_value= REPEAT('ӥ',256) where db_name='test' and table_name='t1';
+Warnings:
+Warning	1265	Data truncated for column 'min_value' at row 1
+set @@sql_mode= @save_sql_mode;
+select length(a) from t1 where a=REPEAT('ӥ',255);
+length(a)
+510
+set names latin1;
+set @@use_stat_tables=@save_use_stat_tables;
+drop table t1;
 # please keep this at the last
 set @@global.histogram_size=@save_histogram_size;
diff --git a/mysql-test/r/stat_tables_innodb.result b/mysql-test/r/stat_tables_innodb.result
index dcfdb55f9c9..a436efb5fea 100644
--- a/mysql-test/r/stat_tables_innodb.result
+++ b/mysql-test/r/stat_tables_innodb.result
@@ -829,6 +829,29 @@ set use_stat_tables=@save_use_stat_tables;
 set @@histogram_size= @save_histogram_size;
 set @@optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity;
 drop table t1;
+#
+# MDEV-20589: Server still crashes in Field::set_warning_truncated_wrong_value
+#
+set names utf8;
+create table t1 ( a varchar(255) character set utf8);
+insert into t1 values (REPEAT('ӥ',255)), (REPEAT('ç',255));
+set use_stat_tables='preferably';
+analyze table t1 persistent for all;
+Table	Op	Msg_type	Msg_text
+test.t1	analyze	status	Engine-independent statistics collected
+test.t1	analyze	status	OK
+set @save_sql_mode= @@sql_mode;
+set sql_mode='ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
+update mysql.column_stats set min_value= REPEAT('ӥ',256) where db_name='test' and table_name='t1';
+Warnings:
+Warning	1265	Data truncated for column 'min_value' at row 1
+set @@sql_mode= @save_sql_mode;
+select length(a) from t1 where a=REPEAT('ӥ',255);
+length(a)
+510
+set names latin1;
+set @@use_stat_tables=@save_use_stat_tables;
+drop table t1;
 # please keep this at the last
 set @@global.histogram_size=@save_histogram_size;
 set optimizer_switch=@save_optimizer_switch_for_stat_tables_test;
diff --git a/mysql-test/t/stat_tables.test b/mysql-test/t/stat_tables.test
index 880e6378ca5..4c21e21ea70 100644
--- a/mysql-test/t/stat_tables.test
+++ b/mysql-test/t/stat_tables.test
@@ -546,5 +546,27 @@ set @@histogram_size= @save_histogram_size;
 set @@optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity;
 drop table t1;
 
+--echo #
+--echo # MDEV-20589: Server still crashes in Field::set_warning_truncated_wrong_value
+--echo #
+
+set names utf8;
+create table t1 ( a varchar(255) character set utf8);
+insert into t1 values (REPEAT('ӥ',255)), (REPEAT('ç',255));
+
+set use_stat_tables='preferably';
+analyze table t1 persistent for all;
+
+set @save_sql_mode= @@sql_mode;
+set sql_mode='ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
+update mysql.column_stats set min_value= REPEAT('ӥ',256) where db_name='test' and table_name='t1';
+set @@sql_mode= @save_sql_mode;
+
+select length(a) from t1 where a=REPEAT('ӥ',255);
+
+set names latin1;
+set @@use_stat_tables=@save_use_stat_tables;
+drop table t1;
+
 --echo # please keep this at the last
 set @@global.histogram_size=@save_histogram_size;
-- 
cgit v1.2.1


From 24859049c6b2d0b6f83f1f93ced15414d7266fb6 Mon Sep 17 00:00:00 2001
From: =?UTF-8?q?Marko=20M=C3=A4kel=C3=A4?= <marko.makela@mariadb.com>
Date: Wed, 18 Sep 2019 16:04:43 +0300
Subject: MDEV-20485: Disable galera.galera_var_node_address

---
 mysql-test/suite/galera/disabled.def | 3 ++-
 1 file changed, 2 insertions(+), 1 deletion(-)

(limited to 'mysql-test')

diff --git a/mysql-test/suite/galera/disabled.def b/mysql-test/suite/galera/disabled.def
index d97b7a1ff14..4af2513076e 100644
--- a/mysql-test/suite/galera/disabled.def
+++ b/mysql-test/suite/galera/disabled.def
@@ -26,6 +26,7 @@ galera_ist_mariabackup : MDEV-18829 test leaves port open
 galera_ist_progress : MDEV-15236 fails when trying to read transfer status
 galera_migrate : MariaDB does not support START SLAVE USER
 galera_ssl_upgrade : MDEV-19950 Galera test failure on galera_ssl_upgrade
+galera_var_node_address : MDEV-20485 Galera test failure
 galera_wan : MDEV-17259 Test failure on galera.galera_wan
 partition : MDEV-19958 Galera test failure on galera.partition
-query_cache: MDEV-15805 Test failure on galera.query_cache
\ No newline at end of file
+query_cache: MDEV-15805 Test failure on galera.query_cache
-- 
cgit v1.2.1


From b3a7c07eae000f20c62812ecf7a8b9349aedc8a1 Mon Sep 17 00:00:00 2001
From: Stepan Patryshev <stepan@cnt7glr11.localdomain>
Date: Wed, 18 Sep 2019 16:50:46 +0300
Subject: Enable galera.MW-286 test case.

---
 mysql-test/suite/galera/disabled.def | 3 +--
 1 file changed, 1 insertion(+), 2 deletions(-)

(limited to 'mysql-test')

diff --git a/mysql-test/suite/galera/disabled.def b/mysql-test/suite/galera/disabled.def
index 4af2513076e..f6853e35ca3 100644
--- a/mysql-test/suite/galera/disabled.def
+++ b/mysql-test/suite/galera/disabled.def
@@ -5,12 +5,11 @@
 #  Separate the test case name and the comment with ':'.
 #
 #    <testcasename> : MDEV-<xxxx> <comment>
-#
+# 
 #  Do not use any TAB characters for whitespace.
 #
 ##############################################################################
 
-MW-286 : MDEV-19992 Galera test failure on MW-286
 MW-329 : MDEV-19962 Galera test failure on MW-329
 MW-388: MDEV-19803 Long semaphore wait error on galera.MW-388	
 galera_account_management : MariaDB 10.0 does not support ALTER USER
-- 
cgit v1.2.1


From c0db3fe6dabd2cb0df9bf532f54f9e8d233fa499 Mon Sep 17 00:00:00 2001
From: Simon Lipp <sloonz@gmail.com>
Date: Mon, 27 May 2019 13:52:27 +0200
Subject: MDEV-18438 Don't stream xtrabackup_info of extra-lsndir

---
 mysql-test/suite/mariabackup/extra_lsndir_stream.result |  2 ++
 mysql-test/suite/mariabackup/extra_lsndir_stream.test   |  7 +++++++
 mysql-test/suite/mariabackup/mdev-18438.result          |  1 +
 mysql-test/suite/mariabackup/mdev-18438.test            | 11 +++++++++++
 4 files changed, 21 insertions(+)
 create mode 100644 mysql-test/suite/mariabackup/extra_lsndir_stream.result
 create mode 100644 mysql-test/suite/mariabackup/extra_lsndir_stream.test
 create mode 100644 mysql-test/suite/mariabackup/mdev-18438.result
 create mode 100644 mysql-test/suite/mariabackup/mdev-18438.test

(limited to 'mysql-test')

diff --git a/mysql-test/suite/mariabackup/extra_lsndir_stream.result b/mysql-test/suite/mariabackup/extra_lsndir_stream.result
new file mode 100644
index 00000000000..a25c45a13d1
--- /dev/null
+++ b/mysql-test/suite/mariabackup/extra_lsndir_stream.result
@@ -0,0 +1,2 @@
+xtrabackup_checkpoints
+xtrabackup_info
diff --git a/mysql-test/suite/mariabackup/extra_lsndir_stream.test b/mysql-test/suite/mariabackup/extra_lsndir_stream.test
new file mode 100644
index 00000000000..97023cb179d
--- /dev/null
+++ b/mysql-test/suite/mariabackup/extra_lsndir_stream.test
@@ -0,0 +1,7 @@
+let $extra_lsndir=$MYSQLTEST_VARDIR/tmp/extra_lsndir;
+mkdir $extra_lsndir;
+--disable_result_log
+exec $XTRABACKUP --defaults-file=$MYSQLTEST_VARDIR/my.cnf --backup --stream=xbstream --extra-lsndir=$extra_lsndir > /dev/null;
+--enable_result_log
+list_files $extra_lsndir;
+rmdir $extra_lsndir;
diff --git a/mysql-test/suite/mariabackup/mdev-18438.result b/mysql-test/suite/mariabackup/mdev-18438.result
new file mode 100644
index 00000000000..ab3b81bd484
--- /dev/null
+++ b/mysql-test/suite/mariabackup/mdev-18438.result
@@ -0,0 +1 @@
+stream.xb
diff --git a/mysql-test/suite/mariabackup/mdev-18438.test b/mysql-test/suite/mariabackup/mdev-18438.test
new file mode 100644
index 00000000000..a6ec45476ff
--- /dev/null
+++ b/mysql-test/suite/mariabackup/mdev-18438.test
@@ -0,0 +1,11 @@
+let $basedir=$MYSQLTEST_VARDIR/tmp/mdev-18438;
+mkdir $basedir;
+exec $XTRABACKUP --defaults-file=$MYSQLTEST_VARDIR/my.cnf --backup --extra-lsndir=$basedir/extra_lsndir --stream=xbstream > $basedir/stream.xb;
+mkdir $basedir/backup;
+rmdir $basedir/extra_lsndir;
+--disable_result_log
+exec $XBSTREAM -x -C $basedir/backup  < $basedir/stream.xb;
+--enable_result_log
+rmdir $basedir/backup;
+list_files $basedir;
+rmdir $basedir;
-- 
cgit v1.2.1


From 4afe9d4b6dbffcebe8f9e5842fb79f0ff22b5aa0 Mon Sep 17 00:00:00 2001
From: =?UTF-8?q?Marko=20M=C3=A4kel=C3=A4?= <marko.makela@mariadb.com>
Date: Fri, 20 Sep 2019 08:30:31 +0300
Subject: MDEV-16222: Move the test to gcol.innodb_virtual_debug_purge

---
 .../suite/gcol/r/innodb_virtual_debug_purge.result | 27 +++++++++
 .../suite/gcol/t/innodb_virtual_debug_purge.opt    |  1 +
 .../suite/gcol/t/innodb_virtual_debug_purge.test   | 61 ++++++++++++++++++++
 .../innodb/r/purge_secondary_mdev-16222.result     | 30 ----------
 .../suite/innodb/t/purge_secondary_mdev-16222.opt  |  1 -
 .../suite/innodb/t/purge_secondary_mdev-16222.test | 67 ----------------------
 6 files changed, 89 insertions(+), 98 deletions(-)
 delete mode 100644 mysql-test/suite/innodb/r/purge_secondary_mdev-16222.result
 delete mode 100644 mysql-test/suite/innodb/t/purge_secondary_mdev-16222.opt
 delete mode 100644 mysql-test/suite/innodb/t/purge_secondary_mdev-16222.test

(limited to 'mysql-test')

diff --git a/mysql-test/suite/gcol/r/innodb_virtual_debug_purge.result b/mysql-test/suite/gcol/r/innodb_virtual_debug_purge.result
index 08f8d2098f6..647ba47a880 100644
--- a/mysql-test/suite/gcol/r/innodb_virtual_debug_purge.result
+++ b/mysql-test/suite/gcol/r/innodb_virtual_debug_purge.result
@@ -204,5 +204,32 @@ connection truncate;
 disconnect truncate;
 connection default;
 DROP TABLE t1, t2;
+#
+# MDEV-16222 Assertion `0' failed in row_purge_remove_sec_if_poss_leaf
+# on table with virtual columns and indexes
+#
+set @saved_dbug= @@global.debug_dbug;
+set global debug_dbug= "+d,ib_purge_virtual_mdev_16222_1,ib_purge_virtual_mdev_16222_2";
+create table t1 (
+pk serial, vb tinyblob as (b) virtual, b tinyblob,
+primary key(pk), index (vb(64)))
+engine innodb;
+insert ignore into t1 (b) values ('foo');
+select * into outfile 'load.data' from t1;
+load data infile 'load.data' replace into table t1;
+set debug_sync= "now WAIT_FOR latch_released TIMEOUT 1";
+set global debug_dbug= "-d,ib_purge_virtual_mdev_16222_1";
+drop table t1;
+set debug_sync= "now SIGNAL drop_started WAIT_FOR got_no_such_table TIMEOUT 1";
+create table t1 (
+pk serial, vb tinyblob as (b) virtual, b tinyblob,
+primary key(pk), index (vb(64)))
+engine innodb;
+insert ignore into t1 (b) values ('foo');
+select * into outfile 'load.data' from t1;
+load data infile 'load.data' replace into table t1;
+set debug_sync= "now WAIT_FOR got_no_such_table TIMEOUT 1";
+set global debug_dbug= @saved_dbug;
+drop table t1;
 set debug_sync=reset;
 SET GLOBAL innodb_purge_rseg_truncate_frequency = @saved_frequency;
diff --git a/mysql-test/suite/gcol/t/innodb_virtual_debug_purge.opt b/mysql-test/suite/gcol/t/innodb_virtual_debug_purge.opt
index 9622465d71f..98a5a463ce4 100644
--- a/mysql-test/suite/gcol/t/innodb_virtual_debug_purge.opt
+++ b/mysql-test/suite/gcol/t/innodb_virtual_debug_purge.opt
@@ -1 +1,2 @@
 --loose-innodb-safe-truncate=1
+--innodb-purge-threads=1
diff --git a/mysql-test/suite/gcol/t/innodb_virtual_debug_purge.test b/mysql-test/suite/gcol/t/innodb_virtual_debug_purge.test
index 1541920d3ae..ee88789dec4 100644
--- a/mysql-test/suite/gcol/t/innodb_virtual_debug_purge.test
+++ b/mysql-test/suite/gcol/t/innodb_virtual_debug_purge.test
@@ -259,6 +259,67 @@ disconnect truncate;
 connection default;
 DROP TABLE t1, t2;
 
+--echo #
+--echo # MDEV-16222 Assertion `0' failed in row_purge_remove_sec_if_poss_leaf
+--echo # on table with virtual columns and indexes
+--echo #
+
+--let $datadir= `select @@datadir`
+set @saved_dbug= @@global.debug_dbug;
+set global debug_dbug= "+d,ib_purge_virtual_mdev_16222_1,ib_purge_virtual_mdev_16222_2";
+
+create table t1 (
+  pk serial, vb tinyblob as (b) virtual, b tinyblob,
+  primary key(pk), index (vb(64)))
+engine innodb;
+
+insert ignore into t1 (b) values ('foo');
+
+select * into outfile 'load.data' from t1;
+load data infile 'load.data' replace into table t1;
+
+# FIXME: case does not work on 10.2 as it does not pass this condition:
+#
+#	if (!*table)
+#		*table= innodb_find_table_for_vc(thd, index->table);
+#
+# in innobase_allocate_row_for_vcol()
+
+--disable_warnings
+set debug_sync= "now WAIT_FOR latch_released TIMEOUT 1";
+--enable_warnings
+set global debug_dbug= "-d,ib_purge_virtual_mdev_16222_1";
+drop table t1;
+--remove_file $datadir/test/load.data
+
+--disable_warnings
+set debug_sync= "now SIGNAL drop_started WAIT_FOR got_no_such_table TIMEOUT 1";
+--enable_warnings
+
+create table t1 (
+  pk serial, vb tinyblob as (b) virtual, b tinyblob,
+  primary key(pk), index (vb(64)))
+engine innodb;
+
+insert ignore into t1 (b) values ('foo');
+
+select * into outfile 'load.data' from t1;
+load data infile 'load.data' replace into table t1;
+
+--disable_warnings
+set debug_sync= "now WAIT_FOR got_no_such_table TIMEOUT 1";
+--enable_warnings
+
+# FIXME: Race condition here:
+# 1. purge thread goes into sending got_no_such_table
+# 2. test thread finishes debug_sync= "RESET" below
+# 3. purge thread sends got_no_such_table
+set global debug_dbug= @saved_dbug;
+
+# cleanup
+drop table t1;
+--remove_file $datadir/test/load.data
+
 --source include/wait_until_count_sessions.inc
 set debug_sync=reset;
 SET GLOBAL innodb_purge_rseg_truncate_frequency = @saved_frequency;
diff --git a/mysql-test/suite/innodb/r/purge_secondary_mdev-16222.result b/mysql-test/suite/innodb/r/purge_secondary_mdev-16222.result
deleted file mode 100644
index 6efadc16792..00000000000
--- a/mysql-test/suite/innodb/r/purge_secondary_mdev-16222.result
+++ /dev/null
@@ -1,30 +0,0 @@
-#
-# MDEV-16222 Assertion `0' failed in row_purge_remove_sec_if_poss_leaf on table with virtual columns and indexes
-#
-set @saved_frequency= @@global.innodb_purge_rseg_truncate_frequency;
-set global innodb_purge_rseg_truncate_frequency= 1;
-set @saved_dbug= @@global.debug_dbug;
-set global debug_dbug= "+d,ib_purge_virtual_mdev_16222_1,ib_purge_virtual_mdev_16222_2";
-create table t1 (
-pk serial, vb tinyblob as (b) virtual, b tinyblob,
-primary key(pk), index (vb(64)))
-engine innodb;
-insert ignore into t1 (b) values ('foo');
-select * into outfile 'load.data' from t1;
-load data infile 'load.data' replace into table t1;
-set debug_sync= "now WAIT_FOR latch_released TIMEOUT 1";
-set global debug_dbug= "-d,ib_purge_virtual_mdev_16222_1";
-drop table t1;
-set debug_sync= "now SIGNAL drop_started WAIT_FOR got_no_such_table TIMEOUT 1";
-create table t1 (
-pk serial, vb tinyblob as (b) virtual, b tinyblob,
-primary key(pk), index (vb(64)))
-engine innodb;
-insert ignore into t1 (b) values ('foo');
-select * into outfile 'load.data' from t1;
-load data infile 'load.data' replace into table t1;
-set debug_sync= "now WAIT_FOR got_no_such_table TIMEOUT 1";
-set global debug_dbug= @saved_dbug;
-drop table t1;
-set global innodb_purge_rseg_truncate_frequency= @saved_frequency;
-set debug_sync= "RESET";
diff --git a/mysql-test/suite/innodb/t/purge_secondary_mdev-16222.opt b/mysql-test/suite/innodb/t/purge_secondary_mdev-16222.opt
deleted file mode 100644
index a1207721427..00000000000
--- a/mysql-test/suite/innodb/t/purge_secondary_mdev-16222.opt
+++ /dev/null
@@ -1 +0,0 @@
---innodb-purge-threads=1
diff --git a/mysql-test/suite/innodb/t/purge_secondary_mdev-16222.test b/mysql-test/suite/innodb/t/purge_secondary_mdev-16222.test
deleted file mode 100644
index ca0e4fad133..00000000000
--- a/mysql-test/suite/innodb/t/purge_secondary_mdev-16222.test
+++ /dev/null
@@ -1,67 +0,0 @@
---source include/have_debug.inc
---source include/have_innodb.inc
-
---echo #
---echo # MDEV-16222 Assertion `0' failed in row_purge_remove_sec_if_poss_leaf on table with virtual columns and indexes
---echo #
-
---let $datadir= `select @@datadir`
-set @saved_frequency= @@global.innodb_purge_rseg_truncate_frequency;
-set global innodb_purge_rseg_truncate_frequency= 1;
-set @saved_dbug= @@global.debug_dbug;
-set global debug_dbug= "+d,ib_purge_virtual_mdev_16222_1,ib_purge_virtual_mdev_16222_2";
-
-create table t1 (
-  pk serial, vb tinyblob as (b) virtual, b tinyblob,
-  primary key(pk), index (vb(64)))
-engine innodb;
-
-insert ignore into t1 (b) values ('foo');
-
-select * into outfile 'load.data' from t1;
-load data infile 'load.data' replace into table t1;
-
-# FIXME: case does not work on 10.2 as it does not pass this condition:
-#
-#	if (!*table)
-#		*table= innodb_find_table_for_vc(thd, index->table);
-#
-# in innobase_allocate_row_for_vcol()
-
---disable_warnings
-set debug_sync= "now WAIT_FOR latch_released TIMEOUT 1";
---enable_warnings
-set global debug_dbug= "-d,ib_purge_virtual_mdev_16222_1";
-drop table t1;
---remove_file $datadir/test/load.data
-
---disable_warnings
-set debug_sync= "now SIGNAL drop_started WAIT_FOR got_no_such_table TIMEOUT 1";
---enable_warnings
-
-create table t1 (
-  pk serial, vb tinyblob as (b) virtual, b tinyblob,
-  primary key(pk), index (vb(64)))
-engine innodb;
-
-insert ignore into t1 (b) values ('foo');
-
-select * into outfile 'load.data' from t1;
-load data infile 'load.data' replace into table t1;
-
---disable_warnings
-set debug_sync= "now WAIT_FOR got_no_such_table TIMEOUT 1";
---enable_warnings
-
-# FIXME: Racing condition here:
-# 1. purge thread goes into sending got_no_such_table
-# 2. test thread finishes debug_sync= "RESET" below
-# 3. purge thread sends got_no_such_table
-set global debug_dbug= @saved_dbug;
-
-# cleanup
-drop table t1;
---remove_file $datadir/test/load.data
-
-set global innodb_purge_rseg_truncate_frequency= @saved_frequency;
-set debug_sync= "RESET";
-- 
cgit v1.2.1


From 1ad79c818780aafe1aaf1c25bbf975eb31a38a40 Mon Sep 17 00:00:00 2001
From: Anel Husakovic <anel@mariadb.org>
Date: Tue, 18 Jun 2019 06:58:41 -0700
Subject: MDEV-19679 - CREATE SERVER needs tweaks for compatibility with
 CONNECT engine

---
 mysql-test/r/create_drop_server.result                      | 4 ++--
 mysql-test/r/system_mysql_db.result                         | 4 ++--
 mysql-test/r/system_mysql_db_fix40123.result                | 4 ++--
 mysql-test/suite/funcs_1/r/is_columns_mysql.result          | 8 ++++----
 mysql-test/suite/funcs_1/r/is_columns_mysql_embedded.result | 8 ++++----
 mysql-test/suite/funcs_1/r/is_tables_mysql.result           | 2 +-
 mysql-test/suite/funcs_1/r/is_tables_mysql_embedded.result  | 4 ++--
 7 files changed, 17 insertions(+), 17 deletions(-)

(limited to 'mysql-test')

diff --git a/mysql-test/r/create_drop_server.result b/mysql-test/r/create_drop_server.result
index 29c4fe7e123..4f5d13b3541 100644
--- a/mysql-test/r/create_drop_server.result
+++ b/mysql-test/r/create_drop_server.result
@@ -38,9 +38,9 @@ DROP SERVER server_1;
 CREATE SERVER server_1 FOREIGN DATA WRAPPER mysql OPTIONS (USER 'Remote', HOST 'Server.Example.Com', DATABASE 'test');
 SELECT Host FROM mysql.servers WHERE Server_Name = 'server_1';
 Host
-server.example.com
+Server.Example.Com
 ALTER SERVER server_1 OPTIONS(HOST 'Server.Example.Org');
 SELECT Host FROM mysql.servers WHERE Server_Name = 'server_1';
 Host
-server.example.org
+Server.Example.Org
 DROP SERVER server_1;
diff --git a/mysql-test/r/system_mysql_db.result b/mysql-test/r/system_mysql_db.result
index b88497dbd9b..444072fe23c 100644
--- a/mysql-test/r/system_mysql_db.result
+++ b/mysql-test/r/system_mysql_db.result
@@ -187,14 +187,14 @@ show create table servers;
 Table	Create Table
 servers	CREATE TABLE `servers` (
   `Server_name` char(64) NOT NULL DEFAULT '',
-  `Host` char(64) NOT NULL DEFAULT '',
+  `Host` varchar(2048) NOT NULL DEFAULT '',
   `Db` char(64) NOT NULL DEFAULT '',
   `Username` char(80) NOT NULL DEFAULT '',
   `Password` char(64) NOT NULL DEFAULT '',
   `Port` int(4) NOT NULL DEFAULT 0,
   `Socket` char(64) NOT NULL DEFAULT '',
   `Wrapper` char(64) NOT NULL DEFAULT '',
-  `Owner` char(64) NOT NULL DEFAULT '',
+  `Owner` varchar(512) NOT NULL DEFAULT '',
   PRIMARY KEY (`Server_name`)
 ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='MySQL Foreign Servers table'
 show create table proc;
diff --git a/mysql-test/r/system_mysql_db_fix40123.result b/mysql-test/r/system_mysql_db_fix40123.result
index b88497dbd9b..444072fe23c 100644
--- a/mysql-test/r/system_mysql_db_fix40123.result
+++ b/mysql-test/r/system_mysql_db_fix40123.result
@@ -187,14 +187,14 @@ show create table servers;
 Table	Create Table
 servers	CREATE TABLE `servers` (
   `Server_name` char(64) NOT NULL DEFAULT '',
-  `Host` char(64) NOT NULL DEFAULT '',
+  `Host` varchar(2048) NOT NULL DEFAULT '',
   `Db` char(64) NOT NULL DEFAULT '',
   `Username` char(80) NOT NULL DEFAULT '',
   `Password` char(64) NOT NULL DEFAULT '',
   `Port` int(4) NOT NULL DEFAULT 0,
   `Socket` char(64) NOT NULL DEFAULT '',
   `Wrapper` char(64) NOT NULL DEFAULT '',
-  `Owner` char(64) NOT NULL DEFAULT '',
+  `Owner` varchar(512) NOT NULL DEFAULT '',
   PRIMARY KEY (`Server_name`)
 ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='MySQL Foreign Servers table'
 show create table proc;
diff --git a/mysql-test/suite/funcs_1/r/is_columns_mysql.result b/mysql-test/suite/funcs_1/r/is_columns_mysql.result
index 25dbb185e44..69972256078 100644
--- a/mysql-test/suite/funcs_1/r/is_columns_mysql.result
+++ b/mysql-test/suite/funcs_1/r/is_columns_mysql.result
@@ -173,8 +173,8 @@ def	mysql	roles_mapping	Host	1	''	NO	char	60	180	NULL	NULL	NULL	utf8	utf8_bin	ch
 def	mysql	roles_mapping	Role	3	''	NO	char	80	240	NULL	NULL	NULL	utf8	utf8_bin	char(80)	PRI		select,insert,update,references		NEVER	NULL
 def	mysql	roles_mapping	User	2	''	NO	char	80	240	NULL	NULL	NULL	utf8	utf8_bin	char(80)	PRI		select,insert,update,references		NEVER	NULL
 def	mysql	servers	Db	3	''	NO	char	64	192	NULL	NULL	NULL	utf8	utf8_general_ci	char(64)			select,insert,update,references		NEVER	NULL
-def	mysql	servers	Host	2	''	NO	char	64	192	NULL	NULL	NULL	utf8	utf8_general_ci	char(64)			select,insert,update,references		NEVER	NULL
-def	mysql	servers	Owner	9	''	NO	char	64	192	NULL	NULL	NULL	utf8	utf8_general_ci	char(64)			select,insert,update,references		NEVER	NULL
+def	mysql	servers	Host	2	''	NO	varchar	2048	6144	NULL	NULL	NULL	utf8	utf8_general_ci	varchar(2048)			select,insert,update,references		NEVER	NULL
+def	mysql	servers	Owner	9	''	NO	varchar	512	1536	NULL	NULL	NULL	utf8	utf8_general_ci	varchar(512)			select,insert,update,references		NEVER	NULL
 def	mysql	servers	Password	5	''	NO	char	64	192	NULL	NULL	NULL	utf8	utf8_general_ci	char(64)			select,insert,update,references		NEVER	NULL
 def	mysql	servers	Port	6	0	NO	int	NULL	NULL	10	0	NULL	NULL	NULL	int(4)			select,insert,update,references		NEVER	NULL
 def	mysql	servers	Server_name	1	''	NO	char	64	192	NULL	NULL	NULL	utf8	utf8_general_ci	char(64)	PRI		select,insert,update,references		NEVER	NULL
@@ -508,14 +508,14 @@ NULL	mysql	proxies_priv	Timestamp	timestamp	NULL	NULL	NULL	NULL	timestamp
 3.0000	mysql	roles_mapping	Role	char	80	240	utf8	utf8_bin	char(80)
 3.0000	mysql	roles_mapping	Admin_option	enum	1	3	utf8	utf8_general_ci	enum('N','Y')
 3.0000	mysql	servers	Server_name	char	64	192	utf8	utf8_general_ci	char(64)
-3.0000	mysql	servers	Host	char	64	192	utf8	utf8_general_ci	char(64)
+3.0000	mysql	servers	Host	varchar	2048	6144	utf8	utf8_general_ci	varchar(2048)
 3.0000	mysql	servers	Db	char	64	192	utf8	utf8_general_ci	char(64)
 3.0000	mysql	servers	Username	char	80	240	utf8	utf8_general_ci	char(80)
 3.0000	mysql	servers	Password	char	64	192	utf8	utf8_general_ci	char(64)
 NULL	mysql	servers	Port	int	NULL	NULL	NULL	NULL	int(4)
 3.0000	mysql	servers	Socket	char	64	192	utf8	utf8_general_ci	char(64)
 3.0000	mysql	servers	Wrapper	char	64	192	utf8	utf8_general_ci	char(64)
-3.0000	mysql	servers	Owner	char	64	192	utf8	utf8_general_ci	char(64)
+3.0000	mysql	servers	Owner	varchar	512	1536	utf8	utf8_general_ci	varchar(512)
 NULL	mysql	slow_log	start_time	timestamp	NULL	NULL	NULL	NULL	timestamp(6)
 1.0000	mysql	slow_log	user_host	mediumtext	16777215	16777215	utf8	utf8_general_ci	mediumtext
 NULL	mysql	slow_log	query_time	time	NULL	NULL	NULL	NULL	time(6)
diff --git a/mysql-test/suite/funcs_1/r/is_columns_mysql_embedded.result b/mysql-test/suite/funcs_1/r/is_columns_mysql_embedded.result
index 22b4ddfc311..87ff00d346f 100644
--- a/mysql-test/suite/funcs_1/r/is_columns_mysql_embedded.result
+++ b/mysql-test/suite/funcs_1/r/is_columns_mysql_embedded.result
@@ -159,8 +159,8 @@ def	mysql	roles_mapping	Host	1	''	NO	char	60	180	NULL	NULL	NULL	utf8	utf8_bin	ch
 def	mysql	roles_mapping	Role	3	''	NO	char	80	240	NULL	NULL	NULL	utf8	utf8_bin	char(80)	PRI				NEVER	NULL
 def	mysql	roles_mapping	User	2	''	NO	char	80	240	NULL	NULL	NULL	utf8	utf8_bin	char(80)	PRI				NEVER	NULL
 def	mysql	servers	Db	3	''	NO	char	64	192	NULL	NULL	NULL	utf8	utf8_general_ci	char(64)					NEVER	NULL
-def	mysql	servers	Host	2	''	NO	char	64	192	NULL	NULL	NULL	utf8	utf8_general_ci	char(64)					NEVER	NULL
-def	mysql	servers	Owner	9	''	NO	char	64	192	NULL	NULL	NULL	utf8	utf8_general_ci	char(64)					NEVER	NULL
+def	mysql	servers	Host	2	''	NO	varchar	2048	6144	NULL	NULL	NULL	utf8	utf8_general_ci	varchar(2048)					NEVER	NULL
+def	mysql	servers	Owner	9	''	NO	varchar	512	1536	NULL	NULL	NULL	utf8	utf8_general_ci	varchar(512)					NEVER	NULL
 def	mysql	servers	Password	5	''	NO	char	64	192	NULL	NULL	NULL	utf8	utf8_general_ci	char(64)					NEVER	NULL
 def	mysql	servers	Port	6	0	NO	int	NULL	NULL	10	0	NULL	NULL	NULL	int(4)					NEVER	NULL
 def	mysql	servers	Server_name	1	''	NO	char	64	192	NULL	NULL	NULL	utf8	utf8_general_ci	char(64)	PRI				NEVER	NULL
@@ -492,14 +492,14 @@ NULL	mysql	proxies_priv	Timestamp	timestamp	NULL	NULL	NULL	NULL	timestamp
 3.0000	mysql	roles_mapping	Role	char	80	240	utf8	utf8_bin	char(80)
 3.0000	mysql	roles_mapping	Admin_option	enum	1	3	utf8	utf8_general_ci	enum('N','Y')
 3.0000	mysql	servers	Server_name	char	64	192	utf8	utf8_general_ci	char(64)
-3.0000	mysql	servers	Host	char	64	192	utf8	utf8_general_ci	char(64)
+3.0000	mysql	servers	Host	varchar	2048	6144	utf8	utf8_general_ci	varchar(2048)
 3.0000	mysql	servers	Db	char	64	192	utf8	utf8_general_ci	char(64)
 3.0000	mysql	servers	Username	char	80	240	utf8	utf8_general_ci	char(80)
 3.0000	mysql	servers	Password	char	64	192	utf8	utf8_general_ci	char(64)
 NULL	mysql	servers	Port	int	NULL	NULL	NULL	NULL	int(4)
 3.0000	mysql	servers	Socket	char	64	192	utf8	utf8_general_ci	char(64)
 3.0000	mysql	servers	Wrapper	char	64	192	utf8	utf8_general_ci	char(64)
-3.0000	mysql	servers	Owner	char	64	192	utf8	utf8_general_ci	char(64)
+3.0000	mysql	servers	Owner	varchar	512	1536	utf8	utf8_general_ci	varchar(512)
 NULL	mysql	slow_log	start_time	timestamp	NULL	NULL	NULL	NULL	timestamp(6)
 1.0000	mysql	slow_log	user_host	mediumtext	16777215	16777215	utf8	utf8_general_ci	mediumtext
 NULL	mysql	slow_log	query_time	time	NULL	NULL	NULL	NULL	time(6)
diff --git a/mysql-test/suite/funcs_1/r/is_tables_mysql.result b/mysql-test/suite/funcs_1/r/is_tables_mysql.result
index 8e0c9b64dab..81eca31f35d 100644
--- a/mysql-test/suite/funcs_1/r/is_tables_mysql.result
+++ b/mysql-test/suite/funcs_1/r/is_tables_mysql.result
@@ -476,7 +476,7 @@ TABLE_NAME	servers
 TABLE_TYPE	BASE TABLE
 ENGINE	MYISAM_OR_MARIA
 VERSION	10
-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_mysql_embedded.result b/mysql-test/suite/funcs_1/r/is_tables_mysql_embedded.result
index f40294491cc..d81b16e6e8e 100644
--- a/mysql-test/suite/funcs_1/r/is_tables_mysql_embedded.result
+++ b/mysql-test/suite/funcs_1/r/is_tables_mysql_embedded.result
@@ -476,7 +476,7 @@ TABLE_NAME	servers
 TABLE_TYPE	BASE TABLE
 ENGINE	MYISAM_OR_MARIA
 VERSION	10
-ROW_FORMAT	Fixed
+ROW_FORMAT	DYNAMIC_OR_PAGE
 TABLE_ROWS	#TBLR#
 AVG_ROW_LENGTH	#ARL#
 DATA_LENGTH	#DL#
@@ -1180,7 +1180,7 @@ TABLE_NAME	servers
 TABLE_TYPE	BASE TABLE
 ENGINE	MYISAM_OR_MARIA
 VERSION	10
-ROW_FORMAT	Fixed
+ROW_FORMAT	DYNAMIC_OR_PAGE
 TABLE_ROWS	#TBLR#
 AVG_ROW_LENGTH	#ARL#
 DATA_LENGTH	#DL#
-- 
cgit v1.2.1


From 896974fc3d721aabe1afbf637a566cab856a731d Mon Sep 17 00:00:00 2001
From: Varun Gupta <varun.gupta@mariadb.com>
Date: Sat, 21 Sep 2019 12:14:05 +0530
Subject: MDEV-18094: Query with order by limit picking index scan over
 filesort

In the function test_if_cheaper_ordering we make a decision if using an index is better than
using filesort for ordering. If we chose to do range access then in test_quick_select we
should make sure that cost for table scan is set to DBL_MAX so that it is not picked.
---
 mysql-test/r/group_min_max.result   |  4 ++--
 mysql-test/r/order_by_innodb.result | 25 +++++++++++++++++++++++++
 mysql-test/suite/maria/icp.result   |  2 +-
 mysql-test/t/order_by_innodb.test   | 24 ++++++++++++++++++++++++
 4 files changed, 52 insertions(+), 3 deletions(-)

(limited to 'mysql-test')

diff --git a/mysql-test/r/group_min_max.result b/mysql-test/r/group_min_max.result
index 777780f8400..c535550d5f9 100644
--- a/mysql-test/r/group_min_max.result
+++ b/mysql-test/r/group_min_max.result
@@ -2119,12 +2119,12 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	index	NULL	idx_t1_2	147	NULL	128	Using index
 explain extended select a1,a2,count(a2) from t1 where (a1 > 'a') group by a1,a2,b;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	SIMPLE	t1	index	idx_t1_0,idx_t1_1,idx_t1_2	idx_t1_2	147	NULL	128	75.00	Using where; Using index
+1	SIMPLE	t1	range	idx_t1_0,idx_t1_1,idx_t1_2	idx_t1_2	65	NULL	102	94.12	Using where; Using index
 Warnings:
 Note	1003	select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2`,count(`test`.`t1`.`a2`) AS `count(a2)` from `test`.`t1` where (`test`.`t1`.`a1` > 'a') group by `test`.`t1`.`a1`,`test`.`t1`.`a2`,`test`.`t1`.`b`
 explain extended select sum(ord(a1)) from t1 where (a1 > 'a') group by a1,a2,b;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	SIMPLE	t1	index	idx_t1_0,idx_t1_1,idx_t1_2	idx_t1_2	147	NULL	128	75.00	Using where; Using index
+1	SIMPLE	t1	range	idx_t1_0,idx_t1_1,idx_t1_2	idx_t1_2	65	NULL	102	94.12	Using where; Using index
 Warnings:
 Note	1003	select sum(ord(`test`.`t1`.`a1`)) AS `sum(ord(a1))` from `test`.`t1` where (`test`.`t1`.`a1` > 'a') group by `test`.`t1`.`a1`,`test`.`t1`.`a2`,`test`.`t1`.`b`
 create table t4 as select distinct a1, a2, b, c from t1;
diff --git a/mysql-test/r/order_by_innodb.result b/mysql-test/r/order_by_innodb.result
index 4f59a2f8c20..10c041c0c28 100644
--- a/mysql-test/r/order_by_innodb.result
+++ b/mysql-test/r/order_by_innodb.result
@@ -48,3 +48,28 @@ where key1<3 or key2<3;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	index_merge	key1,key2	key1,key2	5,5	NULL	#	Using sort_union(key1,key2); Using where
 drop table t0, t1;
+#
+# MDEV-18094: Query with order by limit picking index scan over filesort
+#
+create table t0 (a int);
+INSERT INTO t0 VALUES (0),(0),(0),(0),(2),(0),(0),(1),(1),(0);
+CREATE TABLE t1 (
+a int(11),
+b int(11),
+c int(11),
+KEY a_c (a,c),
+KEY a_b (a,b)
+) ENGINE=InnoDB;
+insert into t1 select A.a , B.a, C.a from t0 A, t0 B, t0 C;
+# should use ref access
+explain select a,b,c from t1 where a=1 and c=2 order by b;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ref	a_c,a_b	a_c	10	const,const	20	Using where; Using filesort
+# both should use range access
+explain select a,b,c from t1 where a=1 and c=2 order by b limit 1000;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	range	a_c,a_b	a_b	5	NULL	200	Using where
+explain select a,b,c from t1 where a=1 and c=2 order by b limit 2000;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	range	a_c,a_b	a_b	5	NULL	200	Using where
+drop table t1,t0;
diff --git a/mysql-test/suite/maria/icp.result b/mysql-test/suite/maria/icp.result
index e37877c8527..49eaed9dd6d 100644
--- a/mysql-test/suite/maria/icp.result
+++ b/mysql-test/suite/maria/icp.result
@@ -167,7 +167,7 @@ WHERE ts BETWEEN '0000-00-00' AND '2010-00-01 00:00:00'
 ORDER BY ts DESC
 LIMIT 2;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	index	PRIMARY	PRIMARY	4	NULL	2	Using where
+1	SIMPLE	t1	range	PRIMARY	PRIMARY	4	NULL	4	Using where
 
 DROP TABLE t1;
 #
diff --git a/mysql-test/t/order_by_innodb.test b/mysql-test/t/order_by_innodb.test
index 097eddd24f1..10769367c39 100644
--- a/mysql-test/t/order_by_innodb.test
+++ b/mysql-test/t/order_by_innodb.test
@@ -61,3 +61,27 @@ from t1
 where key1<3 or key2<3;
 
 drop table t0, t1;
+
+--echo #
+--echo # MDEV-18094: Query with order by limit picking index scan over filesort
+--echo #
+
+create table t0 (a int);
+INSERT INTO t0 VALUES (0),(0),(0),(0),(2),(0),(0),(1),(1),(0);
+
+CREATE TABLE t1 (
+a int(11),
+b int(11),
+c int(11),
+KEY a_c (a,c),
+KEY a_b (a,b)
+) ENGINE=InnoDB;
+insert into t1 select A.a , B.a, C.a from t0 A, t0 B, t0 C;
+
+--echo # should use ref access
+explain select a,b,c from t1 where a=1 and c=2 order by b;
+
+--echo # both should use range access
+explain select a,b,c from t1 where a=1 and c=2 order by b limit 1000;
+explain select a,b,c from t1 where a=1 and c=2 order by b limit 2000;
+drop table t1,t0;
-- 
cgit v1.2.1