From f003cc8a35a4cdd9621621f95da889777b8b31b0 Mon Sep 17 00:00:00 2001
From: Igor Babaev <igor@askmonty.org>
Date: Wed, 18 Jan 2017 11:42:41 -0800
Subject: Fixed bug mdev-8603.

When building different range and index-merge trees the range optimizer
could build an index-merge tree with an index scan containing less ranges
then needed. This index-merge could be chosen as the best. Following this
index-merge the executioner missed some rows in the result set.
The invalid index scan was built due to an inconsistency in the code
back-ported from mysql into 5.3 that fixed mysql bug #11765831:
the code added to key_or() could change shared keys of the second
ored tree. Partially the problem was fixed in the patch for mariadb
bug #823301, but it turned out that only partially.
---
 mysql-test/r/range_vs_index_merge.result           | 46 +++++++++++++++++++--
 ...range_vs_index_merge_innodb,innodb_plugin.rdiff | 13 +++++-
 mysql-test/r/range_vs_index_merge_innodb.result    | 48 +++++++++++++++++++---
 3 files changed, 96 insertions(+), 11 deletions(-)

(limited to 'mysql-test/r')

diff --git a/mysql-test/r/range_vs_index_merge.result b/mysql-test/r/range_vs_index_merge.result
index cc8a345a2ff..0acaed37d22 100644
--- a/mysql-test/r/range_vs_index_merge.result
+++ b/mysql-test/r/range_vs_index_merge.result
@@ -60,11 +60,11 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 EXPLAIN
 SELECT * FROM City
 WHERE Population > 100000 AND Name LIKE 'Aba%' OR
-Country IN ('CAN', 'ARG') AND ID < 3800 OR
-Country < 'U' AND Name LIKE 'Zhu%' OR
-ID BETWEEN 3800 AND 3810;
+Country IN ('CAN', 'ARG') AND  ID BETWEEN 120 AND 130 OR
+Country <= 'ALB' AND Name LIKE 'L%' OR
+ID BETWEEN 3807 AND 3810;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	City	index_merge	PRIMARY,Population,Country,Name	Name,Country,PRIMARY	35,3,4	NULL	132	Using sort_union(Name,Country,PRIMARY); Using where
+1	SIMPLE	City	index_merge	PRIMARY,Population,Country,Name	Name,PRIMARY,Country	35,4,3	NULL	31	Using sort_union(Name,PRIMARY,Country); Using where
 EXPLAIN 
 SELECT * FROM City
 WHERE (Population > 101000 AND Population < 115000);
@@ -1763,4 +1763,42 @@ a	b
 167	9999
 168	10000
 DROP TABLE t1;
+#
+# MDEV-8603: Wrong result OR/AND condition over index fields
+#
+CREATE TABLE t1 (
+id INT NOT NULL,
+state VARCHAR(64),
+capital VARCHAR(64),
+UNIQUE KEY (id),
+KEY state (state,id),
+KEY capital (capital, id)
+);
+INSERT INTO t1 VALUES  
+(1,'Arizona','Phoenix'), 
+(2,'Hawaii','Honolulu'),
+(3,'Georgia','Atlanta'), 
+(4,'Florida','Tallahassee'), 
+(5,'Alaska','Juneau'),
+(6,'Michigan','Lansing'),
+(7,'Pennsylvania','Harrisburg'),
+(8,'Virginia','Richmond')
+;
+EXPLAIN
+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
+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	state	capital
+4	Florida	Tallahassee
+3	Georgia	Atlanta
+2	Hawaii	Honolulu
+6	Michigan	Lansing
+7	Pennsylvania	Harrisburg
+8	Virginia	Richmond
+DROP TABLE t1;
 set session optimizer_switch='index_merge_sort_intersection=default';
diff --git a/mysql-test/r/range_vs_index_merge_innodb,innodb_plugin.rdiff b/mysql-test/r/range_vs_index_merge_innodb,innodb_plugin.rdiff
index ecae2c809c1..7e3c4a33ad2 100644
--- a/mysql-test/r/range_vs_index_merge_innodb,innodb_plugin.rdiff
+++ b/mysql-test/r/range_vs_index_merge_innodb,innodb_plugin.rdiff
@@ -1,5 +1,5 @@
---- ./r/range_vs_index_merge_innodb.result	2012-11-21 19:35:14.000000000 +0100
-+++ ./r/range_vs_index_merge_innodb,innodb_plugin.reject	2012-11-21 20:56:00.000000000 +0100
+--- range_vs_index_merge_innodb.result	2017-01-17 15:00:18.039148421 -0800
++++ range_vs_index_merge_innodb,innodb_plugin.result	2017-01-17 14:58:45.129148312 -0800
 @@ -50,14 +50,14 @@
  WHERE (Population >= 100000 OR Name LIKE 'P%') AND Country='CAN' OR
  (Population < 100000 OR Name Like 'T%') AND Country='ARG';
@@ -278,3 +278,12 @@
  FLUSH STATUS;
  SELECT * FROM City
  WHERE ((Name > 'Ca' AND Name < 'Cf') OR (Country > 'E' AND Country < 'H'))
+@@ -1790,7 +1790,7 @@
+ 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	10	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 67e341192da..ff4940281ce 100644
--- a/mysql-test/r/range_vs_index_merge_innodb.result
+++ b/mysql-test/r/range_vs_index_merge_innodb.result
@@ -61,11 +61,11 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 EXPLAIN
 SELECT * FROM City
 WHERE Population > 100000 AND Name LIKE 'Aba%' OR
-Country IN ('CAN', 'ARG') AND ID < 3800 OR
-Country < 'U' AND Name LIKE 'Zhu%' OR
-ID BETWEEN 3800 AND 3810;
+Country IN ('CAN', 'ARG') AND  ID BETWEEN 120 AND 130 OR
+Country <= 'ALB' AND Name LIKE 'L%' OR
+ID BETWEEN 3807 AND 3810;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	City	index_merge	PRIMARY,Population,Country,Name	Name,Country,PRIMARY	35,3,4	NULL	125	Using sort_union(Name,Country,PRIMARY); Using where
+1	SIMPLE	City	index_merge	PRIMARY,Population,Country,Name	Name,Country,PRIMARY	35,3,4	NULL	33	Using sort_union(Name,Country,PRIMARY); Using where
 EXPLAIN 
 SELECT * FROM City
 WHERE (Population > 101000 AND Population < 115000);
@@ -369,7 +369,7 @@ WHERE ((ID < 200) AND (Name LIKE 'Ha%' OR (Country > 'A' AND Country < 'ARG')))
 OR ((ID BETWEEN 100 AND 200) AND 
 (Name LIKE 'Pa%' OR (Population > 103000 AND Population < 104000)));
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	City	range	PRIMARY,Population,Country,Name	PRIMARY	4	NULL	200	Using where
+1	SIMPLE	City	index_merge	PRIMARY,Population,Country,Name	Name,Country,PRIMARY	35,3,4	NULL	181	Using sort_union(Name,Country,PRIMARY); Using where
 SELECT * FROM City USE INDEX ()
 WHERE ((ID < 10) AND (Name LIKE 'H%' OR (Country > 'A' AND Country < 'ARG')))
 OR ((ID BETWEEN 100 AND 110) AND 
@@ -1764,5 +1764,43 @@ a	b
 167	9999
 168	10000
 DROP TABLE t1;
+#
+# MDEV-8603: Wrong result OR/AND condition over index fields
+#
+CREATE TABLE t1 (
+id INT NOT NULL,
+state VARCHAR(64),
+capital VARCHAR(64),
+UNIQUE KEY (id),
+KEY state (state,id),
+KEY capital (capital, id)
+);
+INSERT INTO t1 VALUES  
+(1,'Arizona','Phoenix'), 
+(2,'Hawaii','Honolulu'),
+(3,'Georgia','Atlanta'), 
+(4,'Florida','Tallahassee'), 
+(5,'Alaska','Juneau'),
+(6,'Michigan','Lansing'),
+(7,'Pennsylvania','Harrisburg'),
+(8,'Virginia','Richmond')
+;
+EXPLAIN
+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
+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	state	capital
+4	Florida	Tallahassee
+3	Georgia	Atlanta
+2	Hawaii	Honolulu
+6	Michigan	Lansing
+7	Pennsylvania	Harrisburg
+8	Virginia	Richmond
+DROP TABLE t1;
 set session optimizer_switch='index_merge_sort_intersection=default';
 SET SESSION STORAGE_ENGINE=DEFAULT;
-- 
cgit v1.2.1


From 46eef1ede2ddfceaa056a71ea52ecacdde2bc44e Mon Sep 17 00:00:00 2001
From: Igor Babaev <igor@askmonty.org>
Date: Mon, 23 Jan 2017 19:40:22 -0800
Subject: Fixed bug mdev-11859.

As the function Item_subselect::fix_fields does it the function
Item_subselect::update_used_tables must ignore UNCACHEABLE_EXPLAIN
when deciding whether the subquery item should be considered as a
constant item.
---
 mysql-test/r/ps.result | 70 ++++++++++++++++++++++++++++++++++++++++++++++++++
 1 file changed, 70 insertions(+)

(limited to 'mysql-test/r')

diff --git a/mysql-test/r/ps.result b/mysql-test/r/ps.result
index bb8b76faa49..f954583a097 100644
--- a/mysql-test/r/ps.result
+++ b/mysql-test/r/ps.result
@@ -4103,4 +4103,74 @@ NULL
 NULL
 deallocate prepare stmt;
 drop table t1,t2,t3,t4;
+#
+# MDEV-11859: the plans for the first and the second executions
+#             of PS are not the same
+#
+create table t1 (id int, c varchar(3), key idx(c))engine=myisam;
+insert into t1 values (3,'bar'), (1,'xxx'), (2,'foo'), (5,'yyy');
+prepare stmt1 from
+"explain extended
+ select * from t1 where (1, 2) in ( select 3, 4 ) or c = 'foo'";
+execute stmt1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	t1	ref	idx	idx	6	const	1	100.00	Using index condition
+2	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+Warnings:
+Note	1003	select `test`.`t1`.`id` AS `id`,`test`.`t1`.`c` AS `c` from `test`.`t1` where (`test`.`t1`.`c` = 'foo')
+execute stmt1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	t1	ref	idx	idx	6	const	1	100.00	Using index condition
+2	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+Warnings:
+Note	1003	select `test`.`t1`.`id` AS `id`,`test`.`t1`.`c` AS `c` from `test`.`t1` where (`test`.`t1`.`c` = 'foo')
+deallocate prepare stmt1;
+prepare stmt1 from
+"select * from t1 where (1, 2) in ( select 3, 4 ) or c = 'foo'";
+flush status;
+execute stmt1;
+id	c
+2	foo
+show status like '%Handler_read%';
+Variable_name	Value
+Handler_read_first	0
+Handler_read_key	1
+Handler_read_last	0
+Handler_read_next	1
+Handler_read_prev	0
+Handler_read_rnd	0
+Handler_read_rnd_deleted	0
+Handler_read_rnd_next	0
+flush status;
+execute stmt1;
+id	c
+2	foo
+show status like '%Handler_read%';
+Variable_name	Value
+Handler_read_first	0
+Handler_read_key	1
+Handler_read_last	0
+Handler_read_next	1
+Handler_read_prev	0
+Handler_read_rnd	0
+Handler_read_rnd_deleted	0
+Handler_read_rnd_next	0
+deallocate prepare stmt1;
+prepare stmt2 from
+"explain extended
+ select * from t1 where (1, 2) in ( select 3, 4 )";
+execute stmt2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
+2	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+Warnings:
+Note	1003	select `test`.`t1`.`id` AS `id`,`test`.`t1`.`c` AS `c` from `test`.`t1` where 0
+execute stmt2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
+2	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+Warnings:
+Note	1003	select `test`.`t1`.`id` AS `id`,`test`.`t1`.`c` AS `c` from `test`.`t1` where 0
+deallocate prepare stmt2;
+drop table t1;
 # End of 5.5 tests
-- 
cgit v1.2.1


From c2b217e2434e5a2903e85705f915f3c55ec2342c Mon Sep 17 00:00:00 2001
From: Varun Gupta <varunraiko1803@gmail.com>
Date: Thu, 9 Feb 2017 22:23:26 +0530
Subject: MDEV-10731: Wrong NULL match results in "Subquery returns more than 1
 row" (error code 1242)

NOT NULL predicate was not added to tables in case of an update query having a subquery.
---
 mysql-test/r/update_innodb.result | 26 ++++++++++++++++++++++++++
 1 file changed, 26 insertions(+)

(limited to 'mysql-test/r')

diff --git a/mysql-test/r/update_innodb.result b/mysql-test/r/update_innodb.result
index 88c86c50625..9ba6699a473 100644
--- a/mysql-test/r/update_innodb.result
+++ b/mysql-test/r/update_innodb.result
@@ -29,3 +29,29 @@ CREATE ALGORITHM=UNDEFINED VIEW `v1` AS select `t4`.`c1` AS `c1`,`t4`.`c2` AS `c
 UPDATE t1 a JOIN t2 b ON a.c1 = b.c1 JOIN v1 vw ON b.c2 = vw.c1 JOIN t3 del ON vw.c2 = del.c2 SET a.c2 = ( SELECT max(t.c1) FROM t3 t, v1 i WHERE del.c2 = t.c2 AND vw.c3 = i.c3 AND t.c3 = 4 ) WHERE a.c2 IS NULL OR a.c2 < '2011-05-01';
 drop view v1;
 drop table t1,t2,t3,t4;
+#
+# MDEV-10232 Scalar result of subquery changes after adding an outer select stmt
+#
+CREATE TABLE t1 (
+a_id INT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
+b_id INT(20) UNSIGNED NULL DEFAULT NULL,
+c_id VARCHAR(255) NULL DEFAULT NULL,
+PRIMARY KEY (a_id))COLLATE = 'utf8_general_ci' ENGINE = InnoDB;
+CREATE TABLE t2 (
+b_id INT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
+c_id VARCHAR(255) NULL DEFAULT NULL,
+PRIMARY KEY (b_id),
+INDEX idx_c_id (c_id))COLLATE = 'utf8_general_ci' ENGINE = InnoDB;
+INSERT INTO t1 (b_id, c_id) VALUES (NULL, NULL);
+INSERT INTO t2 (c_id) VALUES (NULL);
+INSERT INTO t2 (c_id) VALUES (NULL);
+SELECT * FROM T1;
+a_id	b_id	c_id
+1	NULL	NULL
+SELECT t2.b_id FROM t1,t2 WHERE t2.c_id = t1.c_id;
+b_id
+UPDATE t1 SET b_id = (SELECT t2.b_id FROM t2 t2 WHERE t2.c_id = t1.c_id);
+SELECT * FROM T1;
+a_id	b_id	c_id
+1	NULL	NULL
+drop table t1,t2;
-- 
cgit v1.2.1


From 66822f164ff197c564df12a4c384e37dab630ee3 Mon Sep 17 00:00:00 2001
From: Elena Stepanova <elenst@montyprogram.com>
Date: Sat, 11 Feb 2017 01:14:06 +0200
Subject: Follow-up to MDEV-10731 - fix the broken test

---
 mysql-test/r/update_innodb.result | 4 ++--
 1 file changed, 2 insertions(+), 2 deletions(-)

(limited to 'mysql-test/r')

diff --git a/mysql-test/r/update_innodb.result b/mysql-test/r/update_innodb.result
index 9ba6699a473..5fcc584035a 100644
--- a/mysql-test/r/update_innodb.result
+++ b/mysql-test/r/update_innodb.result
@@ -45,13 +45,13 @@ INDEX idx_c_id (c_id))COLLATE = 'utf8_general_ci' ENGINE = InnoDB;
 INSERT INTO t1 (b_id, c_id) VALUES (NULL, NULL);
 INSERT INTO t2 (c_id) VALUES (NULL);
 INSERT INTO t2 (c_id) VALUES (NULL);
-SELECT * FROM T1;
+SELECT * FROM t1;
 a_id	b_id	c_id
 1	NULL	NULL
 SELECT t2.b_id FROM t1,t2 WHERE t2.c_id = t1.c_id;
 b_id
 UPDATE t1 SET b_id = (SELECT t2.b_id FROM t2 t2 WHERE t2.c_id = t1.c_id);
-SELECT * FROM T1;
+SELECT * FROM t1;
 a_id	b_id	c_id
 1	NULL	NULL
 drop table t1,t2;
-- 
cgit v1.2.1


From e2d6760d8ae624da7a9470768fa5894a81c3d237 Mon Sep 17 00:00:00 2001
From: Daniel Black <daniel.black@au.ibm.com>
Date: Thu, 16 Feb 2017 16:42:25 +1100
Subject: MDEV-10515: Correct stat_tables_par test results

Test results are distorted by a small rounding error
during an intermediate stage of calculating the result.
By using the SQL ROUND function we stablise tests.

Signed-off-by: Daniel Black <daniel.black@au.ibm.com>
---
 mysql-test/r/stat_tables_par.result        | 12 ++++++------
 mysql-test/r/stat_tables_par_innodb.result | 12 ++++++------
 2 files changed, 12 insertions(+), 12 deletions(-)

(limited to 'mysql-test/r')

diff --git a/mysql-test/r/stat_tables_par.result b/mysql-test/r/stat_tables_par.result
index ed39b7af9a9..aaebb929c9d 100644
--- a/mysql-test/r/stat_tables_par.result
+++ b/mysql-test/r/stat_tables_par.result
@@ -46,19 +46,19 @@ dbt3_s001	supplier	PRIMARY	1	1.0000
 dbt3_s001	supplier	i_s_nationkey	1	1.1111
 flush table lineitem;
 set use_stat_tables='never';
-select sum(l_extendedprice*l_discount) as revenue
+select round(sum(l_extendedprice*l_discount),4) as revenue
 from lineitem
 where l_shipdate >= date '1994-01-01' 
 and l_shipdate < date '1994-01-01' + interval '1' year
 and l_discount between 0.06 - 0.01 and 0.06 + 0.01
 and l_quantity < 24;
 revenue
-77949.91860000002
+77949.9186
 set debug_sync='statistics_mem_alloc_start1  WAIT_FOR second_thread_started_too';
 set debug_sync='statistics_mem_alloc_start2  SIGNAL first_thread_working';
 use dbt3_s001;
 set use_stat_tables='preferably';
-select sum(l_extendedprice*l_discount) as revenue
+select round(sum(l_extendedprice*l_discount),4) as revenue
 from lineitem
 where l_shipdate >= date '1994-01-01' 
 and l_shipdate < date '1994-01-01' + interval '1' year
@@ -68,16 +68,16 @@ set debug_sync='statistics_mem_alloc_start1  SIGNAL second_thread_started_too';
 set debug_sync='statistics_mem_alloc_start2  WAIT_FOR first_thread_working';
 use dbt3_s001;
 set use_stat_tables='preferably';
-select sum(l_extendedprice*l_discount) as revenue
+select round(sum(l_extendedprice*l_discount),4) as revenue
 from lineitem
 where l_shipdate >= date '1994-01-01' 
 and l_shipdate < date '1994-01-01' + interval '1' year
 and l_discount between 0.06 - 0.01 and 0.06 + 0.01
 and l_quantity < 24;
 revenue
-77949.91860000002
+77949.9186
 revenue
-77949.91860000002
+77949.9186
 set use_stat_tables='preferably';
 set debug_sync='RESET';
 select * from mysql.index_stats where table_name='lineitem' order by index_name;
diff --git a/mysql-test/r/stat_tables_par_innodb.result b/mysql-test/r/stat_tables_par_innodb.result
index cb4e4b09827..e08392678c5 100644
--- a/mysql-test/r/stat_tables_par_innodb.result
+++ b/mysql-test/r/stat_tables_par_innodb.result
@@ -49,19 +49,19 @@ dbt3_s001	supplier	PRIMARY	1	1.0000
 dbt3_s001	supplier	i_s_nationkey	1	1.1111
 flush table lineitem;
 set use_stat_tables='never';
-select sum(l_extendedprice*l_discount) as revenue
+select round(sum(l_extendedprice*l_discount),4) as revenue
 from lineitem
 where l_shipdate >= date '1994-01-01' 
 and l_shipdate < date '1994-01-01' + interval '1' year
 and l_discount between 0.06 - 0.01 and 0.06 + 0.01
 and l_quantity < 24;
 revenue
-77949.91860000002
+77949.9186
 set debug_sync='statistics_mem_alloc_start1  WAIT_FOR second_thread_started_too';
 set debug_sync='statistics_mem_alloc_start2  SIGNAL first_thread_working';
 use dbt3_s001;
 set use_stat_tables='preferably';
-select sum(l_extendedprice*l_discount) as revenue
+select round(sum(l_extendedprice*l_discount),4) as revenue
 from lineitem
 where l_shipdate >= date '1994-01-01' 
 and l_shipdate < date '1994-01-01' + interval '1' year
@@ -71,16 +71,16 @@ set debug_sync='statistics_mem_alloc_start1  SIGNAL second_thread_started_too';
 set debug_sync='statistics_mem_alloc_start2  WAIT_FOR first_thread_working';
 use dbt3_s001;
 set use_stat_tables='preferably';
-select sum(l_extendedprice*l_discount) as revenue
+select round(sum(l_extendedprice*l_discount),4) as revenue
 from lineitem
 where l_shipdate >= date '1994-01-01' 
 and l_shipdate < date '1994-01-01' + interval '1' year
 and l_discount between 0.06 - 0.01 and 0.06 + 0.01
 and l_quantity < 24;
 revenue
-77949.91860000002
+77949.9186
 revenue
-77949.91860000002
+77949.9186
 set use_stat_tables='preferably';
 set debug_sync='RESET';
 select * from mysql.index_stats where table_name='lineitem' order by index_name;
-- 
cgit v1.2.1


From f49375fddfd83cfc23b83b5c00d37781bf0bb070 Mon Sep 17 00:00:00 2001
From: Igor Babaev <igor@askmonty.org>
Date: Thu, 16 Feb 2017 23:44:54 -0800
Subject: Fixed bug mdev-9028.

This patch is actually a complement for the fix of bug mdev-6892.
The procedure create_tmp_table() now must take into account
Item_direct_refs that wrap up constant fields of derived tables/views
that are used as inner tables in outer join operations.
---
 mysql-test/r/derived.result | 23 +++++++++++++++++++++++
 1 file changed, 23 insertions(+)

(limited to 'mysql-test/r')

diff --git a/mysql-test/r/derived.result b/mysql-test/r/derived.result
index a4d474c9cdf..33af7c61613 100644
--- a/mysql-test/r/derived.result
+++ b/mysql-test/r/derived.result
@@ -988,4 +988,27 @@ David	Yes	210
 Edward	Yes	150
 DROP TABLE example1463;
 set sql_mode= @save_sql_mode;
+#
+# MDEV-9028: SELECT DISTINCT constant column of  derived  table
+#            used as the second operand of LEFT JOIN
+#
+create table t1 (id int, data varchar(255));
+insert into t1 values (1,'yes'),(2,'yes');
+select distinct t1.id, tt.id, tt.data
+from t1
+left join
+(select t1.id, 'yes' as data from t1) as tt
+on t1.id = tt.id;
+id	id	data
+1	1	yes
+2	2	yes
+select distinct t1.id, tt.id, tt.data
+from t1
+left join
+(select t1.id, 'yes' as data from t1 where id > 1) as tt
+on t1.id = tt.id;
+id	id	data
+2	2	yes
+1	NULL	NULL
+drop table t1;
 # end of 5.5
-- 
cgit v1.2.1


From 5ddfcb05ca98a62b01da1c8b939e5303f900a5cc Mon Sep 17 00:00:00 2001
From: Oleksandr Byelkin <sanja@mariadb.com>
Date: Fri, 17 Feb 2017 13:37:18 +0100
Subject: MDEV-9455: [ERROR] mysqld got signal 11

Switch MEM_ROOT to non-prune_partitions() during optimizing subselect.
---
 mysql-test/r/partition_innodb.result | 89 ++++++++++++++++++++++++++++++++++++
 1 file changed, 89 insertions(+)

(limited to 'mysql-test/r')

diff --git a/mysql-test/r/partition_innodb.result b/mysql-test/r/partition_innodb.result
index 1da7dce22df..ed4aaf71a00 100644
--- a/mysql-test/r/partition_innodb.result
+++ b/mysql-test/r/partition_innodb.result
@@ -718,3 +718,92 @@ d
 1991-01-01
 DROP TABLE t1;
 set global default_storage_engine=default;
+#
+# MDEV-9455: [ERROR] mysqld got signal 11 
+#
+CREATE TABLE `t1` (
+`DIARY_TOTAL_DAY_SEQ` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
+`IMORY_ID` bigint(20) NOT NULL,
+`NAME` varchar(75) DEFAULT NULL,
+`DATETIME` varchar(10) NOT NULL DEFAULT '',
+`DAILY_CALL_CNT` int(11) DEFAULT NULL,
+`DAILY_SMS_CNT` int(11) DEFAULT NULL,
+`NUMBER` varchar(64) DEFAULT NULL,
+`DURATION` varchar(16) DEFAULT NULL,
+PRIMARY KEY (`DIARY_TOTAL_DAY_SEQ`,`DATETIME`),
+KEY `IDX_t1_01` (`IMORY_ID`,`DATETIME`)
+) AUTO_INCREMENT=328702514 DEFAULT CHARSET=utf8mb4
+PARTITION BY RANGE  COLUMNS(`DATETIME`)
+(PARTITION p0 VALUES LESS THAN ('2015-10-01') ENGINE = InnoDB,
+PARTITION p1 VALUES LESS THAN ('2015-11-01') ENGINE = InnoDB,
+PARTITION p2 VALUES LESS THAN ('2015-12-01') ENGINE = InnoDB,
+PARTITION p3 VALUES LESS THAN ('2016-01-01') ENGINE = InnoDB,
+PARTITION p4 VALUES LESS THAN ('2016-02-01') ENGINE = InnoDB,
+PARTITION p5 VALUES LESS THAN ('2016-03-01') ENGINE = InnoDB,
+PARTITION p6 VALUES LESS THAN ('2016-04-01') ENGINE = InnoDB,
+PARTITION p7 VALUES LESS THAN ('2016-05-01') ENGINE = InnoDB,
+PARTITION p8 VALUES LESS THAN ('2016-06-01') ENGINE = InnoDB,
+PARTITION p9 VALUES LESS THAN ('2016-07-01') ENGINE = InnoDB,
+PARTITION p10 VALUES LESS THAN ('2016-08-01') ENGINE = InnoDB)
+;
+CREATE TABLE `t2` (
+`DIARY_SEQ` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
+`IMORY_ID` bigint(20) NOT NULL,
+`CALL_TYPE` varchar(1) DEFAULT NULL,
+`DATA_TYPE` varchar(1) DEFAULT NULL,
+`FEATURES` varchar(1) DEFAULT NULL,
+`NAME` varchar(75) DEFAULT NULL,
+`NUMBER` varchar(64) DEFAULT NULL,
+`DATETIME` datetime NOT NULL,
+`REG_DATE` datetime NOT NULL,
+`TITLE` varchar(50) DEFAULT NULL,
+`BODY` varchar(4200) DEFAULT NULL,
+`MIME_TYPE` varchar(32) DEFAULT NULL,
+`DURATION` varchar(16) DEFAULT NULL,
+`DEVICE_ID` varchar(64) DEFAULT NULL,
+`DEVICE_NAME` varchar(32) DEFAULT NULL,
+PRIMARY KEY (`DIARY_SEQ`,`DATETIME`,`REG_DATE`),
+KEY `IDX_TB_DIARY_01` (`IMORY_ID`,`DATETIME`,`CALL_TYPE`,`NUMBER`),
+KEY `IDX_TB_DIARY_02` (`REG_DATE`)
+) AUTO_INCREMENT=688799006 DEFAULT CHARSET=utf8mb4
+PARTITION BY RANGE  COLUMNS(REG_DATE)
+(PARTITION p0 VALUES LESS THAN ('2015-10-01') ENGINE = InnoDB,
+PARTITION p1 VALUES LESS THAN ('2015-11-01') ENGINE = InnoDB,
+PARTITION p2 VALUES LESS THAN ('2015-12-01') ENGINE = InnoDB,
+PARTITION p3 VALUES LESS THAN ('2016-01-01') ENGINE = InnoDB,
+PARTITION p4 VALUES LESS THAN ('2016-02-01') ENGINE = InnoDB,
+PARTITION p5 VALUES LESS THAN ('2016-03-01') ENGINE = InnoDB,
+PARTITION p6 VALUES LESS THAN ('2016-04-01') ENGINE = InnoDB,
+PARTITION p7 VALUES LESS THAN ('2016-05-01') ENGINE = InnoDB,
+PARTITION p8 VALUES LESS THAN ('2016-06-01') ENGINE = InnoDB,
+PARTITION p9 VALUES LESS THAN ('2016-07-01') ENGINE = InnoDB,
+PARTITION p10 VALUES LESS THAN ('2016-08-01') ENGINE = InnoDB)
+;
+SELECT
+A.IMORY_ID,
+A.NUMBER,
+A.NAME,
+DATE_FORMAT(A.DATETIME, '%Y-%m-%d') AS TARGET_DATE,
+SUM( CASE WHEN A.DATA_TYPE='1' THEN 1 ELSE 0 END) AS CALL_CNT,
+SUM( CASE WHEN A.DATA_TYPE IN ('2', '3') THEN 1 ELSE 0 END) AS SMS_CNT,
+SUM(CAST(A.DURATION AS INT)) AS DURATION,
+( SELECT COUNT(*)
+FROM t1
+WHERE IMORY_ID=A.IMORY_ID
+AND NUMBER=A.NUMBER
+AND NAME=A.NAME 
+AND DATETIME = DATE_FORMAT(A.DATETIME, '%Y-%m-%d')
+) STATS_COUNT
+FROM t2 A
+WHERE A.IMORY_ID = 55094102
+AND A.DATETIME LIKE (
+SELECT CONCAT (DATE_FORMAT(DATETIME, '%Y-%m-%d') ,'%')
+FROM t2
+WHERE IMORY_ID=55094102
+AND DIARY_SEQ IN ( 608351221, 608351225, 608351229 )
+group by DATE_FORMAT(DATETIME, '%Y-%m-%d')
+)
+GROUP BY A.IMORY_ID, A.NUMBER, A.NAME, DATE_FORMAT(A.DATETIME, '%Y-%m-%d')
+;
+IMORY_ID	NUMBER	NAME	TARGET_DATE	CALL_CNT	SMS_CNT	DURATION	STATS_COUNT
+drop table t2, t1;
-- 
cgit v1.2.1


From cf673adee2d1e43d5cdeab91c7f1fc3e73fa3b37 Mon Sep 17 00:00:00 2001
From: Alexey Botchkov <holyfoot@askmonty.org>
Date: Wed, 22 Feb 2017 01:36:16 +0400
Subject: MDEV-10418 Assertion `m_extra_cache' failed in
 ha_partition::late_extra_cache(uint).

        m_extra_prepare_for_update should be cleaned in
        ha_partition::reset()
---
 mysql-test/r/partition_myisam.result | 16 ++++++++++++++++
 1 file changed, 16 insertions(+)

(limited to 'mysql-test/r')

diff --git a/mysql-test/r/partition_myisam.result b/mysql-test/r/partition_myisam.result
index f9bf3a9fb07..d54e2a2856a 100644
--- a/mysql-test/r/partition_myisam.result
+++ b/mysql-test/r/partition_myisam.result
@@ -248,3 +248,19 @@ PARTITION p1 VALUES LESS THAN (100) MAX_ROWS=100,
 PARTITION pMax VALUES LESS THAN MAXVALUE);
 INSERT INTO t1 VALUES (1, "Partition p1, first row");
 DROP TABLE t1;
+#
+# MDEV-10418 Assertion `m_extra_cache' failed
+#    in ha_partition::late_extra_cache(uint)
+#
+CREATE TABLE t1 (f1 INT) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (1),(2);
+CREATE TABLE t2 (f2 INT) ENGINE=MyISAM PARTITION BY RANGE(f2) (PARTITION pmax VALUES LESS THAN MAXVALUE);
+INSERT INTO t2 VALUES (8);
+CREATE ALGORITHM = MERGE VIEW v AS SELECT f2 FROM t2, t1;
+UPDATE v SET f2 = 1;
+SELECT * FROM t2;
+f2
+1
+DROP VIEW v;
+DROP TABLE t2;
+DROP TABLE t1;
-- 
cgit v1.2.1


From ac78927aefa2bd0d869d999839480d69086a9882 Mon Sep 17 00:00:00 2001
From: Igor Babaev <igor@askmonty.org>
Date: Fri, 24 Feb 2017 00:10:08 -0800
Subject: Fixed bug mdev-7992.

'Not exists' optimization can be used for nested outer joins
only if IS NULL predicate from the WHERE condition is activated.
So we have to check that all guards that wrap this predicate
are in the 'open' state.
This patch supports usage of 'Not exists' optimization for any
outer join, no matter how it's nested in other outer joins.

This patch is also considered as a proper fix for bugs
#49322/#58490 and LP #817360.
---
 mysql-test/r/join_nested.result      | 95 ++++++++++++++++++++++++++++++++++++
 mysql-test/r/join_nested_jcl6.result | 95 ++++++++++++++++++++++++++++++++++++
 2 files changed, 190 insertions(+)

(limited to 'mysql-test/r')

diff --git a/mysql-test/r/join_nested.result b/mysql-test/r/join_nested.result
index 84b6ff640e9..6ddd39cbfec 100644
--- a/mysql-test/r/join_nested.result
+++ b/mysql-test/r/join_nested.result
@@ -1870,4 +1870,99 @@ f4
 NULL
 NULL
 DROP TABLE t1,t2,t3,t4,t5;
+#
+# MDEV-7992: Nested left joins + 'not exists' optimization
+#
+CREATE TABLE t1(
+K1 INT PRIMARY KEY,
+Name VARCHAR(15)
+);
+INSERT INTO t1 VALUES
+(1,'T1Row1'), (2,'T1Row2');
+CREATE TABLE t2(
+K2 INT PRIMARY KEY,
+K1r INT,
+rowTimestamp DATETIME,
+Event VARCHAR(15)
+);
+INSERT INTO t2 VALUES
+(1, 1, '2015-04-13 10:42:11' ,'T1Row1Event1'),
+(2, 1, '2015-04-13 10:42:12' ,'T1Row1Event2'),
+(3, 1, '2015-04-13 10:42:12' ,'T1Row1Event3');
+SELECT t1a.*, t2a.*,
+t2i.K2 AS K2B, t2i.K1r AS K1rB,
+t2i.rowTimestamp AS rowTimestampB, t2i.Event AS EventB
+FROM 
+t1 t1a JOIN t2 t2a ON t2a.K1r = t1a.K1
+LEFT JOIN 
+( t1 t1i LEFT JOIN t2 t2i ON  t2i.K1r = t1i.K1) 
+ON (t1i.K1 = 1) AND
+(((t2i.K1r = t1a.K1 AND t2i.rowTimestamp > t2a.rowTimestamp ) OR
+(t2i.rowTimestamp = t2a.rowTimestamp AND t2i.K2 > t2a.K2))
+OR (t2i.K2 IS NULL))
+WHERE 
+t2a.K1r = 1 AND t2i.K2 IS NULL;
+K1	Name	K2	K1r	rowTimestamp	Event	K2B	K1rB	rowTimestampB	EventB
+1	T1Row1	3	1	2015-04-13 10:42:12	T1Row1Event3	NULL	NULL	NULL	NULL
+EXPLAIN EXTENDED SELECT t1a.*, t2a.*,
+t2i.K2 AS K2B, t2i.K1r AS K1rB,
+t2i.rowTimestamp AS rowTimestampB, t2i.Event AS EventB
+FROM 
+t1 t1a JOIN t2 t2a ON t2a.K1r = t1a.K1
+LEFT JOIN 
+( t1 t1i LEFT JOIN t2 t2i ON  t2i.K1r = t1i.K1) 
+ON (t1i.K1 = 1) AND
+(((t2i.K1r = t1a.K1 AND t2i.rowTimestamp > t2a.rowTimestamp ) OR
+(t2i.rowTimestamp = t2a.rowTimestamp AND t2i.K2 > t2a.K2))
+OR (t2i.K2 IS NULL))
+WHERE 
+t2a.K1r = 1 AND t2i.K2 IS NULL;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1a	const	PRIMARY	PRIMARY	4	const	1	100.00	
+1	SIMPLE	t2a	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
+1	SIMPLE	t1i	const	PRIMARY	PRIMARY	4	const	1	100.00	Using index
+1	SIMPLE	t2i	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where; Not exists
+Warnings:
+Note	1003	select 1 AS `K1`,'T1Row1' AS `Name`,`test`.`t2a`.`K2` AS `K2`,`test`.`t2a`.`K1r` AS `K1r`,`test`.`t2a`.`rowTimestamp` AS `rowTimestamp`,`test`.`t2a`.`Event` AS `Event`,`test`.`t2i`.`K2` AS `K2B`,`test`.`t2i`.`K1r` AS `K1rB`,`test`.`t2i`.`rowTimestamp` AS `rowTimestampB`,`test`.`t2i`.`Event` AS `EventB` from `test`.`t1` `t1a` join `test`.`t2` `t2a` left join (`test`.`t1` `t1i` left join `test`.`t2` `t2i` on((`test`.`t2i`.`K1r` = 1))) on(((`test`.`t1i`.`K1` = 1) and (((`test`.`t2i`.`K1r` = 1) and (`test`.`t2i`.`rowTimestamp` > `test`.`t2a`.`rowTimestamp`)) or ((`test`.`t2i`.`rowTimestamp` = `test`.`t2a`.`rowTimestamp`) and (`test`.`t2i`.`K2` > `test`.`t2a`.`K2`)) or isnull(`test`.`t2i`.`K2`)))) where ((`test`.`t2a`.`K1r` = 1) and isnull(`test`.`t2i`.`K2`))
+CREATE VIEW v1 AS 
+SELECT t2i.*
+FROM t1 as t1i LEFT JOIN t2 as t2i ON t2i.K1r = t1i.K1
+WHERE t1i.K1 = 1 ;
+SELECT
+t1a.*, t2a.*, t2b.K2 as K2B, t2b.K1r as K1rB,
+t2b.rowTimestamp as rowTimestampB, t2b.Event as EventB
+FROM
+t1 as t1a JOIN t2 as t2a ON t2a.K1r = t1a.K1
+LEFT JOIN
+v1 as t2b
+ON ((t2b.K1r = t1a.K1 AND t2b.rowTimestamp > t2a.rowTimestamp) OR
+(t2b.rowTimestamp = t2a.rowTimestamp AND t2b.K2 > t2a.K2))
+OR (t2b.K2 IS NULL)
+WHERE
+t1a.K1 = 1 AND 
+t2b.K2 IS NULL;
+K1	Name	K2	K1r	rowTimestamp	Event	K2B	K1rB	rowTimestampB	EventB
+1	T1Row1	3	1	2015-04-13 10:42:12	T1Row1Event3	NULL	NULL	NULL	NULL
+EXPLAIN EXTENDED SELECT
+t1a.*, t2a.*, t2b.K2 as K2B, t2b.K1r as K1rB,
+t2b.rowTimestamp as rowTimestampB, t2b.Event as EventB
+FROM
+t1 as t1a JOIN t2 as t2a ON t2a.K1r = t1a.K1
+LEFT JOIN
+v1 as t2b
+ON ((t2b.K1r = t1a.K1 AND t2b.rowTimestamp > t2a.rowTimestamp) OR
+(t2b.rowTimestamp = t2a.rowTimestamp AND t2b.K2 > t2a.K2))
+OR (t2b.K2 IS NULL)
+WHERE
+t1a.K1 = 1 AND 
+t2b.K2 IS NULL;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1a	const	PRIMARY	PRIMARY	4	const	1	100.00	
+1	SIMPLE	t2a	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
+1	SIMPLE	t1i	const	PRIMARY	PRIMARY	4	const	1	100.00	Using index
+1	SIMPLE	t2i	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where; Not exists
+Warnings:
+Note	1003	select 1 AS `K1`,'T1Row1' AS `Name`,`t2a`.`K2` AS `K2`,`t2a`.`K1r` AS `K1r`,`t2a`.`rowTimestamp` AS `rowTimestamp`,`t2a`.`Event` AS `Event`,`test`.`t2i`.`K2` AS `K2B`,`test`.`t2i`.`K1r` AS `K1rB`,`test`.`t2i`.`rowTimestamp` AS `rowTimestampB`,`test`.`t2i`.`Event` AS `EventB` from `test`.`t1` `t1a` join `test`.`t2` `t2a` left join (`test`.`t1` `t1i` left join `test`.`t2` `t2i` on((`test`.`t2i`.`K1r` = 1))) on(((`test`.`t1i`.`K1` = 1) and (((`test`.`t2i`.`K1r` = 1) and (`test`.`t2i`.`rowTimestamp` > `t2a`.`rowTimestamp`)) or ((`test`.`t2i`.`rowTimestamp` = `t2a`.`rowTimestamp`) and (`test`.`t2i`.`K2` > `t2a`.`K2`)) or isnull(`test`.`t2i`.`K2`)))) where ((`t2a`.`K1r` = 1) and isnull(`test`.`t2i`.`K2`))
+DROP VIEW v1;
+DROP TABLE t1,t2;
 set optimizer_search_depth= @tmp_mdev621;
diff --git a/mysql-test/r/join_nested_jcl6.result b/mysql-test/r/join_nested_jcl6.result
index 3b47645ca79..bac8e1cb7db 100644
--- a/mysql-test/r/join_nested_jcl6.result
+++ b/mysql-test/r/join_nested_jcl6.result
@@ -1881,6 +1881,101 @@ f4
 NULL
 NULL
 DROP TABLE t1,t2,t3,t4,t5;
+#
+# MDEV-7992: Nested left joins + 'not exists' optimization
+#
+CREATE TABLE t1(
+K1 INT PRIMARY KEY,
+Name VARCHAR(15)
+);
+INSERT INTO t1 VALUES
+(1,'T1Row1'), (2,'T1Row2');
+CREATE TABLE t2(
+K2 INT PRIMARY KEY,
+K1r INT,
+rowTimestamp DATETIME,
+Event VARCHAR(15)
+);
+INSERT INTO t2 VALUES
+(1, 1, '2015-04-13 10:42:11' ,'T1Row1Event1'),
+(2, 1, '2015-04-13 10:42:12' ,'T1Row1Event2'),
+(3, 1, '2015-04-13 10:42:12' ,'T1Row1Event3');
+SELECT t1a.*, t2a.*,
+t2i.K2 AS K2B, t2i.K1r AS K1rB,
+t2i.rowTimestamp AS rowTimestampB, t2i.Event AS EventB
+FROM 
+t1 t1a JOIN t2 t2a ON t2a.K1r = t1a.K1
+LEFT JOIN 
+( t1 t1i LEFT JOIN t2 t2i ON  t2i.K1r = t1i.K1) 
+ON (t1i.K1 = 1) AND
+(((t2i.K1r = t1a.K1 AND t2i.rowTimestamp > t2a.rowTimestamp ) OR
+(t2i.rowTimestamp = t2a.rowTimestamp AND t2i.K2 > t2a.K2))
+OR (t2i.K2 IS NULL))
+WHERE 
+t2a.K1r = 1 AND t2i.K2 IS NULL;
+K1	Name	K2	K1r	rowTimestamp	Event	K2B	K1rB	rowTimestampB	EventB
+1	T1Row1	3	1	2015-04-13 10:42:12	T1Row1Event3	NULL	NULL	NULL	NULL
+EXPLAIN EXTENDED SELECT t1a.*, t2a.*,
+t2i.K2 AS K2B, t2i.K1r AS K1rB,
+t2i.rowTimestamp AS rowTimestampB, t2i.Event AS EventB
+FROM 
+t1 t1a JOIN t2 t2a ON t2a.K1r = t1a.K1
+LEFT JOIN 
+( t1 t1i LEFT JOIN t2 t2i ON  t2i.K1r = t1i.K1) 
+ON (t1i.K1 = 1) AND
+(((t2i.K1r = t1a.K1 AND t2i.rowTimestamp > t2a.rowTimestamp ) OR
+(t2i.rowTimestamp = t2a.rowTimestamp AND t2i.K2 > t2a.K2))
+OR (t2i.K2 IS NULL))
+WHERE 
+t2a.K1r = 1 AND t2i.K2 IS NULL;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1a	const	PRIMARY	PRIMARY	4	const	1	100.00	
+1	SIMPLE	t2a	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
+1	SIMPLE	t1i	const	PRIMARY	PRIMARY	4	const	1	100.00	Using index
+1	SIMPLE	t2i	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where; Not exists
+Warnings:
+Note	1003	select 1 AS `K1`,'T1Row1' AS `Name`,`test`.`t2a`.`K2` AS `K2`,`test`.`t2a`.`K1r` AS `K1r`,`test`.`t2a`.`rowTimestamp` AS `rowTimestamp`,`test`.`t2a`.`Event` AS `Event`,`test`.`t2i`.`K2` AS `K2B`,`test`.`t2i`.`K1r` AS `K1rB`,`test`.`t2i`.`rowTimestamp` AS `rowTimestampB`,`test`.`t2i`.`Event` AS `EventB` from `test`.`t1` `t1a` join `test`.`t2` `t2a` left join (`test`.`t1` `t1i` left join `test`.`t2` `t2i` on((`test`.`t2i`.`K1r` = 1))) on(((`test`.`t1i`.`K1` = 1) and (((`test`.`t2i`.`K1r` = 1) and (`test`.`t2i`.`rowTimestamp` > `test`.`t2a`.`rowTimestamp`)) or ((`test`.`t2i`.`rowTimestamp` = `test`.`t2a`.`rowTimestamp`) and (`test`.`t2i`.`K2` > `test`.`t2a`.`K2`)) or isnull(`test`.`t2i`.`K2`)))) where ((`test`.`t2a`.`K1r` = 1) and isnull(`test`.`t2i`.`K2`))
+CREATE VIEW v1 AS 
+SELECT t2i.*
+FROM t1 as t1i LEFT JOIN t2 as t2i ON t2i.K1r = t1i.K1
+WHERE t1i.K1 = 1 ;
+SELECT
+t1a.*, t2a.*, t2b.K2 as K2B, t2b.K1r as K1rB,
+t2b.rowTimestamp as rowTimestampB, t2b.Event as EventB
+FROM
+t1 as t1a JOIN t2 as t2a ON t2a.K1r = t1a.K1
+LEFT JOIN
+v1 as t2b
+ON ((t2b.K1r = t1a.K1 AND t2b.rowTimestamp > t2a.rowTimestamp) OR
+(t2b.rowTimestamp = t2a.rowTimestamp AND t2b.K2 > t2a.K2))
+OR (t2b.K2 IS NULL)
+WHERE
+t1a.K1 = 1 AND 
+t2b.K2 IS NULL;
+K1	Name	K2	K1r	rowTimestamp	Event	K2B	K1rB	rowTimestampB	EventB
+1	T1Row1	3	1	2015-04-13 10:42:12	T1Row1Event3	NULL	NULL	NULL	NULL
+EXPLAIN EXTENDED SELECT
+t1a.*, t2a.*, t2b.K2 as K2B, t2b.K1r as K1rB,
+t2b.rowTimestamp as rowTimestampB, t2b.Event as EventB
+FROM
+t1 as t1a JOIN t2 as t2a ON t2a.K1r = t1a.K1
+LEFT JOIN
+v1 as t2b
+ON ((t2b.K1r = t1a.K1 AND t2b.rowTimestamp > t2a.rowTimestamp) OR
+(t2b.rowTimestamp = t2a.rowTimestamp AND t2b.K2 > t2a.K2))
+OR (t2b.K2 IS NULL)
+WHERE
+t1a.K1 = 1 AND 
+t2b.K2 IS NULL;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1a	const	PRIMARY	PRIMARY	4	const	1	100.00	
+1	SIMPLE	t2a	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
+1	SIMPLE	t1i	const	PRIMARY	PRIMARY	4	const	1	100.00	Using index
+1	SIMPLE	t2i	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where; Not exists
+Warnings:
+Note	1003	select 1 AS `K1`,'T1Row1' AS `Name`,`t2a`.`K2` AS `K2`,`t2a`.`K1r` AS `K1r`,`t2a`.`rowTimestamp` AS `rowTimestamp`,`t2a`.`Event` AS `Event`,`test`.`t2i`.`K2` AS `K2B`,`test`.`t2i`.`K1r` AS `K1rB`,`test`.`t2i`.`rowTimestamp` AS `rowTimestampB`,`test`.`t2i`.`Event` AS `EventB` from `test`.`t1` `t1a` join `test`.`t2` `t2a` left join (`test`.`t1` `t1i` left join `test`.`t2` `t2i` on((`test`.`t2i`.`K1r` = 1))) on(((`test`.`t1i`.`K1` = 1) and (((`test`.`t2i`.`K1r` = 1) and (`test`.`t2i`.`rowTimestamp` > `t2a`.`rowTimestamp`)) or ((`test`.`t2i`.`rowTimestamp` = `t2a`.`rowTimestamp`) and (`test`.`t2i`.`K2` > `t2a`.`K2`)) or isnull(`test`.`t2i`.`K2`)))) where ((`t2a`.`K1r` = 1) and isnull(`test`.`t2i`.`K2`))
+DROP VIEW v1;
+DROP TABLE t1,t2;
 set optimizer_search_depth= @tmp_mdev621;
 CREATE TABLE t5 (a int, b int, c int, PRIMARY KEY(a), KEY b_i (b));
 CREATE TABLE t6 (a int, b int, c int, PRIMARY KEY(a), KEY b_i (b));
-- 
cgit v1.2.1


From fdeeab01c0b48b5f56e543b98decb76cb58bedbe Mon Sep 17 00:00:00 2001
From: Alexey Botchkov <holyfoot@askmonty.org>
Date: Sun, 26 Feb 2017 23:01:23 +0400
Subject: MDEV-6390 CONVERT TO CHARACTER SET utf8 doesn't change DEFAULT
 CHARSET.

        ALTER_CONVERT flag removed and replaced for ALTER_OPTIONS
        for the CONVERT TO CHARACTER SET command.
---
 mysql-test/r/alter_table.result | 21 +++++++++++++++++++++
 1 file changed, 21 insertions(+)

(limited to 'mysql-test/r')

diff --git a/mysql-test/r/alter_table.result b/mysql-test/r/alter_table.result
index 72e81895816..cac4c477b5a 100644
--- a/mysql-test/r/alter_table.result
+++ b/mysql-test/r/alter_table.result
@@ -2091,3 +2091,24 @@ Warnings:
 Note	1061	Duplicate key name 'id1'
 DROP TABLE t2;
 DROP TABLE t1;
+#
+# MDEV-6390 CONVERT TO CHARACTER SET utf8 doesn't change DEFAULT CHARSET.
+#
+CREATE TABLE t1 (id int(11) NOT NULL, a int(11) NOT NULL, b int(11))
+ENGINE=InnoDB DEFAULT CHARSET=latin1;
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `id` int(11) NOT NULL,
+  `a` int(11) NOT NULL,
+  `b` int(11) DEFAULT NULL
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+ALTER TABLE t1 CONVERT TO CHARACTER SET utf8;
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `id` int(11) NOT NULL,
+  `a` int(11) NOT NULL,
+  `b` int(11) DEFAULT NULL
+) ENGINE=InnoDB DEFAULT CHARSET=utf8
+DROP TABLE t1;
-- 
cgit v1.2.1


From e5b877ce2790bb88fca05ea1c5a455add50a5da9 Mon Sep 17 00:00:00 2001
From: Oleksandr Byelkin <sanja@mariadb.com>
Date: Thu, 23 Feb 2017 21:50:55 +0100
Subject: MDEV-11935: Queries in stored procedures with and EXISTS(SELECT *
 FROM VIEW) crashes and closes hte conneciton.

Use correct start point even for taken out from subselect items in process of exists2in conversion.
---
 mysql-test/r/sp.result | 38 ++++++++++++++++++++++++++++++++++++++
 1 file changed, 38 insertions(+)

(limited to 'mysql-test/r')

diff --git a/mysql-test/r/sp.result b/mysql-test/r/sp.result
index c059428be16..2cb1b701e2d 100644
--- a/mysql-test/r/sp.result
+++ b/mysql-test/r/sp.result
@@ -7952,3 +7952,41 @@ set global table_open_cache= @tmp_toc;
 set global table_definition_cache= @tmp_tdc;
 drop procedure p1;
 drop table t1,t2,t3,t4,t5,t6;
+#
+# MDEV-11935: Queries in stored procedures with and
+# EXISTS(SELECT * FROM VIEW) crashes and closes hte conneciton.
+#
+CREATE TABLE ANY_TABLE (
+ENTITY_UID    BIGINT NOT NULL
+);
+CREATE TABLE SECURITY_PATH(
+origid BIGINT UNSIGNED NOT NULL,
+destid BIGINT UNSIGNED NOT NULL,
+KEY (destid)
+);
+CREATE VIEW ENTITY_ACCESS (
+ENTITY_UID,
+OWNER_UID
+) AS
+SELECT SP1.origid,
+SP2.destid
+FROM SECURITY_PATH SP1
+JOIN SECURITY_PATH SP2 ON SP1.destid = SP2.origid
+;
+CREATE PROCEDURE SP_EXAMPLE_SELECT ()
+BEGIN
+SELECT *
+FROM ANY_TABLE AT1
+WHERE EXISTS ( SELECT *
+FROM ENTITY_ACCESS EA
+WHERE AT1.ENTITY_UID = EA.ENTITY_UID
+AND   EA.OWNER_UID IS NULL );
+END
+//
+CALL SP_EXAMPLE_SELECT ();
+ENTITY_UID
+CALL SP_EXAMPLE_SELECT ();
+ENTITY_UID
+drop procedure SP_EXAMPLE_SELECT;
+drop view ENTITY_ACCESS;
+drop table ANY_TABLE, SECURITY_PATH;
-- 
cgit v1.2.1


From b27fd90ad36f4194665744cc1dcdd05f2d0b47ef Mon Sep 17 00:00:00 2001
From: Sergei Golubchik <serg@mariadb.org>
Date: Wed, 15 Feb 2017 18:45:19 +0100
Subject: MDEV-11902 mi_open race condition

TOCTOU bug. The path is checked to be valid, symlinks are resolved.
Then the resolved path is opened. Between the check and the open,
there's a window when one can replace some path component with a
symlink, bypassing validity checks.

Fix: after we resolved all symlinks in the path, don't allow open()
to resolve symlinks, there should be none.

Compared to the old MyISAM/Aria code:
* fastpath. Opening of not-symlinked files is just one open(),
  no fn_format() and lstat() anymore.
* opening of symlinked tables doesn't do fn_format() and lstat() either.
  it also doesn't to realpath() (which was lstat-ing every path
  component), instead if opens every path component with O_PATH.
* share->data_file_name stores realpath(path) not readlink(path). So,
  SHOW CREATE TABLE needs to do lstat/readlink() now (see ::info()),
  and certain error messages (cannot open file "XXX") show the real
  file path with all symlinks resolved.
---
 mysql-test/r/symlink-aria-11902.result   | 39 ++++++++++++++++++++++++++++++++
 mysql-test/r/symlink-myisam-11902.result | 38 +++++++++++++++++++++++++++++++
 2 files changed, 77 insertions(+)
 create mode 100644 mysql-test/r/symlink-aria-11902.result
 create mode 100644 mysql-test/r/symlink-myisam-11902.result

(limited to 'mysql-test/r')

diff --git a/mysql-test/r/symlink-aria-11902.result b/mysql-test/r/symlink-aria-11902.result
new file mode 100644
index 00000000000..f704bb86ea7
--- /dev/null
+++ b/mysql-test/r/symlink-aria-11902.result
@@ -0,0 +1,39 @@
+set default_storage_engine=Aria;
+call mtr.add_suppression("File.*t1.* not found");
+create table mysql.t1 (a int, b char(16), index(a));
+insert mysql.t1 values (100, 'test'),(101,'test');
+create table t1 (a int, b char(16), index(a))
+data directory="MYSQLTEST_VARDIR/tmp/foo";
+insert t1 values (200, 'some'),(201,'some');
+select * from t1;
+a	b
+200	some
+201	some
+flush tables;
+set debug_sync='mi_open_datafile SIGNAL ok WAIT_FOR go';
+select * from t1;
+set debug_sync='now WAIT_FOR ok';
+set debug_sync='now SIGNAL go';
+ERROR HY000: File 'MYSQLTEST_VARDIR/tmp/foo/t1.MAD' not found (Errcode: 20)
+flush tables;
+drop table if exists t1;
+create table t1 (a int, b char(16), index (a))
+index directory="MYSQLTEST_VARDIR/tmp/foo";
+insert t1 values (200, 'some'),(201,'some');
+explain select a from t1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	index	NULL	a	5	NULL	2	Using index
+select a from t1;
+a
+200
+201
+flush tables;
+set debug_sync='mi_open_kfile SIGNAL waiting WAIT_FOR run';
+select a from t1;
+set debug_sync='now WAIT_FOR waiting';
+set debug_sync='now SIGNAL run';
+ERROR HY000: Can't find file: 't1' (errno: 20)
+flush tables;
+drop table if exists t1;
+drop table mysql.t1;
+set debug_sync='RESET';
diff --git a/mysql-test/r/symlink-myisam-11902.result b/mysql-test/r/symlink-myisam-11902.result
new file mode 100644
index 00000000000..74785bbcdd1
--- /dev/null
+++ b/mysql-test/r/symlink-myisam-11902.result
@@ -0,0 +1,38 @@
+call mtr.add_suppression("File.*t1.* not found");
+create table mysql.t1 (a int, b char(16), index(a));
+insert mysql.t1 values (100, 'test'),(101,'test');
+create table t1 (a int, b char(16), index(a))
+data directory="MYSQLTEST_VARDIR/tmp/foo";
+insert t1 values (200, 'some'),(201,'some');
+select * from t1;
+a	b
+200	some
+201	some
+flush tables;
+set debug_sync='mi_open_datafile SIGNAL ok WAIT_FOR go';
+select * from t1;
+set debug_sync='now WAIT_FOR ok';
+set debug_sync='now SIGNAL go';
+ERROR HY000: File 'MYSQLTEST_VARDIR/tmp/foo/t1.MYD' not found (Errcode: 20)
+flush tables;
+drop table if exists t1;
+create table t1 (a int, b char(16), index (a))
+index directory="MYSQLTEST_VARDIR/tmp/foo";
+insert t1 values (200, 'some'),(201,'some');
+explain select a from t1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	index	NULL	a	5	NULL	2	Using index
+select a from t1;
+a
+200
+201
+flush tables;
+set debug_sync='mi_open_kfile SIGNAL waiting WAIT_FOR run';
+select a from t1;
+set debug_sync='now WAIT_FOR waiting';
+set debug_sync='now SIGNAL run';
+ERROR HY000: Can't find file: 't1' (errno: 20)
+flush tables;
+drop table if exists t1;
+drop table mysql.t1;
+set debug_sync='RESET';
-- 
cgit v1.2.1


From 494a94158a6f509afad1078615679c51b723b7a6 Mon Sep 17 00:00:00 2001
From: Dmitry Lenev <Dmitry.Lenev@oracle.com>
Date: Thu, 23 Feb 2017 12:41:13 +0100
Subject: Fix for bug#11759114 - '51401: GRANT TREATS NONEXISTENT
 FUNCTIONS/PRIVILEGES DIFFERENTLY'.

The problem was that attempt to grant EXECUTE or ALTER
ROUTINE privilege on stored procedure which didn't exist
succeed instead of returning an appropriate error like
it happens in similar situation for stored functions or
tables.

The code which handles granting of privileges on individual
routine calls sp_exist_routines() function to check if routine
exists and assumes that the 3rd parameter of the latter
specifies whether it should check for existence of stored
procedure or function. In practice, this parameter had
completely different meaning and, as result, this check was
not done properly for stored procedures.

This fix addresses this problem by bringing sp_exist_routines()
signature and code in line with expectation of its caller.
---
 mysql-test/r/grant.result | 23 +++++++++++++++++++++++
 1 file changed, 23 insertions(+)

(limited to 'mysql-test/r')

diff --git a/mysql-test/r/grant.result b/mysql-test/r/grant.result
index f3910b67dd3..399f8964c2f 100644
--- a/mysql-test/r/grant.result
+++ b/mysql-test/r/grant.result
@@ -1701,6 +1701,7 @@ Assigning privileges without procs_priv table.
 CREATE DATABASE mysqltest1;
 CREATE PROCEDURE mysqltest1.test() SQL SECURITY DEFINER
 SELECT 1;
+CREATE FUNCTION mysqltest1.test() RETURNS INT RETURN 1;
 GRANT EXECUTE ON FUNCTION mysqltest1.test TO mysqltest_1@localhost;
 ERROR 42S02: Table 'mysql.procs_priv' doesn't exist
 GRANT ALL PRIVILEGES ON test.* TO mysqltest_1@localhost;
@@ -2537,3 +2538,25 @@ DROP USER mysqltest_u1@localhost;
 
 # End of Bug#38347.
 
+#
+# BUG#11759114 - '51401: GRANT TREATS NONEXISTENT FUNCTIONS/PRIVILEGES
+#                 DIFFERENTLY'.
+#
+drop database if exists mysqltest_db1;
+create database mysqltest_db1;
+create user mysqltest_u1;
+# Both GRANT statements below should fail with the same error.
+grant execute on function mysqltest_db1.f1 to mysqltest_u1;
+ERROR 42000: FUNCTION or PROCEDURE f1 does not exist
+grant execute on procedure mysqltest_db1.p1 to mysqltest_u1;
+ERROR 42000: FUNCTION or PROCEDURE p1 does not exist
+# Let us show that GRANT behaviour for routines is consistent
+# with GRANT behaviour for tables. Attempt to grant privilege
+# on non-existent table also results in an error.
+grant select on mysqltest_db1.t1 to mysqltest_u1;
+ERROR 42S02: Table 'mysqltest_db1.t1' doesn't exist
+show grants for mysqltest_u1;
+Grants for mysqltest_u1@%
+GRANT USAGE ON *.* TO 'mysqltest_u1'@'%'
+drop database mysqltest_db1;
+drop user mysqltest_u1;
-- 
cgit v1.2.1


From 199f88cb9cfb08cefced1b51a7d98fe4c91b7a2e Mon Sep 17 00:00:00 2001
From: Sergei Golubchik <serg@mariadb.org>
Date: Thu, 23 Feb 2017 12:48:15 +0100
Subject: MDEV-5999 MySQL Bug#12766319 - 61865: RENAME USER DOES NOT WORK
 CORRECTLY - REQUIRES FLUSH PRIVILEGES

use update_hostname() to update the hostname.
test case comes from

  commit 0abdeed1d6d
  Author: gopal.shankar@oracle.com <>
  Date:   Thu Mar 29 00:20:54 2012 +0530

    Bug#12766319 - 61865: RENAME USER DOES NOT WORK CORRECTLY -
                          REQUIRES FLUSH PRIVILEGES
---
 mysql-test/r/grant.result | 26 ++++++++++++++++++++++++++
 1 file changed, 26 insertions(+)

(limited to 'mysql-test/r')

diff --git a/mysql-test/r/grant.result b/mysql-test/r/grant.result
index 399f8964c2f..94087393489 100644
--- a/mysql-test/r/grant.result
+++ b/mysql-test/r/grant.result
@@ -2560,3 +2560,29 @@ Grants for mysqltest_u1@%
 GRANT USAGE ON *.* TO 'mysqltest_u1'@'%'
 drop database mysqltest_db1;
 drop user mysqltest_u1;
+#
+# Bug#12766319 - 61865: RENAME USER DOES NOT WORK CORRECTLY -
+#                       REQUIRES FLUSH PRIVILEGES
+#
+CREATE USER foo@'127.0.0.1';
+GRANT ALL ON *.* TO foo@'127.0.0.1';
+# First attempt, should connect successfully
+SELECT user(), current_user();
+user()	current_user()
+foo@localhost	foo@127.0.0.1
+# Rename the user
+RENAME USER foo@'127.0.0.1' to foo@'127.0.0.0/255.0.0.0';
+# Second attempt, should connect successfully as its valid mask
+# This was failing without fix
+SELECT user(), current_user();
+user()	current_user()
+foo@localhost	foo@127.0.0.0/255.0.0.0
+# Rename the user back to original
+RENAME USER foo@'127.0.0.0/255.0.0.0' to foo@'127.0.0.1';
+# Third attempt, should connect successfully
+SELECT user(), current_user();
+user()	current_user()
+foo@localhost	foo@127.0.0.1
+# Clean-up
+DROP USER foo@'127.0.0.1';
+# End of Bug#12766319
-- 
cgit v1.2.1


From 5a0fff50f87e20c4e95a84143a0a3bb67e03e29e Mon Sep 17 00:00:00 2001
From: Igor Babaev <igor@askmonty.org>
Date: Sun, 26 Feb 2017 15:40:18 -0800
Subject: Fixed bug mdev-12099.

The function mysql_derived_merge() erroneously did not mark newly formed
AND formulas in ON conditions with the flag abort_on_null. As a result
not_null_tables() calculated incorrectly for these conditions. This
could prevent conversion of embedded outer joins into inner joins.

Changed a test case from table_elim.test to preserve the former execution
plan.
---
 mysql-test/r/table_elim.result |  5 ++-
 mysql-test/r/view.result       | 83 ++++++++++++++++++++++++++++++++++++++++++
 2 files changed, 86 insertions(+), 2 deletions(-)

(limited to 'mysql-test/r')

diff --git a/mysql-test/r/table_elim.result b/mysql-test/r/table_elim.result
index f475198ac02..4505adab443 100644
--- a/mysql-test/r/table_elim.result
+++ b/mysql-test/r/table_elim.result
@@ -597,7 +597,8 @@ CREATE TABLE t1 (a int(11), b varchar(1)) ;
 INSERT IGNORE INTO t1 VALUES (0,'g');
 CREATE TABLE t3 ( a varchar(1)) ;
 INSERT IGNORE INTO t3 VALUES ('g');
-CREATE TABLE t2 ( a int(11) NOT NULL, PRIMARY KEY (a)) ;
+CREATE TABLE t2 ( a int(11) NOT NULL, PRIMARY KEY (a));
+INSERT INTO t2 VALUES (9), (10);
 create view v1 as SELECT t1.* FROM t1 LEFT JOIN t2 ON ( t1.a = t2.a ) WHERE t2.a <> 0;
 SELECT alias1.* FROM t3 LEFT JOIN v1 as alias1 ON ( t3.a = alias1.b );
 a	b
@@ -606,7 +607,7 @@ EXPLAIN SELECT alias1.* FROM t3 LEFT JOIN v1 as alias1 ON ( t3.a = alias1.b );
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t3	system	NULL	NULL	NULL	NULL	1	
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	1	Using where
-1	SIMPLE	t2	eq_ref	PRIMARY	PRIMARY	4	test.t1.a	1	Using where; Using index
+1	SIMPLE	t2	eq_ref	PRIMARY	PRIMARY	4	test.t1.a	1	Using index
 drop view v1;
 DROP TABLE t1,t2,t3;
 #
diff --git a/mysql-test/r/view.result b/mysql-test/r/view.result
index 6848ba30245..414de5662f3 100644
--- a/mysql-test/r/view.result
+++ b/mysql-test/r/view.result
@@ -5535,6 +5535,89 @@ Warnings:
 Warning	1356	View 'test.v1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
 drop view v1;
 drop table t1,t2;
+#
+# MDEV-12099: usage of mergeable view with LEFT JOIN 
+#             that can be converted to INNER JOIN
+#
+create table t1 (a int, b int, key(a)) engine=myisam;
+insert into t1 values
+(3,20), (7,10), (2,10), (4,30), (8,70), 
+(7,70), (9,100), (9,60), (8,80), (7,60);
+create table t2 (c int, d int, key (c)) engine=myisam;
+insert into t2 values
+(50,100), (20, 200), (10,300),
+(150,100), (120, 200), (110,300),
+(250,100), (220, 200), (210,300);
+create table t3(e int, f int not null, key(e), unique (f)) engine=myisam;
+insert into t3 values
+(100, 3), (300, 5), (400, 4), (300,7),
+(300,2), (600, 13), (800, 15), (700, 14),
+(600, 23), (800, 25), (700, 24);
+create view v1 as
+select * from t2 left join t3 on t3.e=t2.d where t3.f is not null;
+select *
+from t1 left join v1 on v1.c=t1.b 
+where t1.a < 5;
+a	b	c	d	e	f
+2	10	10	300	300	5
+2	10	10	300	300	7
+2	10	10	300	300	2
+3	20	NULL	NULL	NULL	NULL
+4	30	NULL	NULL	NULL	NULL
+select *
+from t1 left join ( t2 left join t3 on t3.e=t2.d )
+on t2.c=t1.b and  t3.f is not null
+where t1.a < 5;
+a	b	c	d	e	f
+2	10	10	300	300	5
+2	10	10	300	300	7
+2	10	10	300	300	2
+3	20	NULL	NULL	NULL	NULL
+4	30	NULL	NULL	NULL	NULL
+explain extended
+select *
+from t1 left join v1 on v1.c=t1.b 
+where t1.a < 5;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	range	a	a	5	NULL	3	100.00	Using index condition
+1	SIMPLE	t2	ref	c	c	5	test.t1.b	2	100.00	Using where
+1	SIMPLE	t3	ref	f,e	e	5	test.t2.d	2	100.00	Using where
+Warnings:
+Note	1003	select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`c` AS `c`,`test`.`t2`.`d` AS `d`,`test`.`t3`.`e` AS `e`,`test`.`t3`.`f` AS `f` from `test`.`t1` left join (`test`.`t2` join `test`.`t3`) on(((`test`.`t2`.`c` = `test`.`t1`.`b`) and (`test`.`t3`.`e` = `test`.`t2`.`d`) and (`test`.`t3`.`f` is not null) and (`test`.`t1`.`b` is not null) and (`test`.`t2`.`d` is not null))) where (`test`.`t1`.`a` < 5)
+explain extended
+select *
+from t1 left join ( t2 left join t3 on t3.e=t2.d )
+on t2.c=t1.b and  t3.f is not null
+where t1.a < 5;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	range	a	a	5	NULL	3	100.00	Using index condition
+1	SIMPLE	t2	ref	c	c	5	test.t1.b	2	100.00	Using where
+1	SIMPLE	t3	ref	f,e	e	5	test.t2.d	2	100.00	Using where
+Warnings:
+Note	1003	select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`c` AS `c`,`test`.`t2`.`d` AS `d`,`test`.`t3`.`e` AS `e`,`test`.`t3`.`f` AS `f` from `test`.`t1` left join (`test`.`t2` join `test`.`t3`) on(((`test`.`t2`.`c` = `test`.`t1`.`b`) and (`test`.`t3`.`e` = `test`.`t2`.`d`) and (`test`.`t3`.`f` is not null) and (`test`.`t1`.`b` is not null) and (`test`.`t2`.`d` is not null))) where (`test`.`t1`.`a` < 5)
+explain extended
+select *
+from t1 left join v1 on v1.c=t1.b and v1.f=t1.a
+where t1.a < 5;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	range	a	a	5	NULL	3	100.00	Using index condition
+1	SIMPLE	t3	eq_ref	f,e	f	4	test.t1.a	1	100.00	Using where
+1	SIMPLE	t2	ref	c	c	5	test.t1.b	2	100.00	Using where
+Warnings:
+Note	1003	select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`c` AS `c`,`test`.`t2`.`d` AS `d`,`test`.`t3`.`e` AS `e`,`test`.`t3`.`f` AS `f` from `test`.`t1` left join (`test`.`t2` join `test`.`t3`) on(((`test`.`t2`.`c` = `test`.`t1`.`b`) and (`test`.`t3`.`f` = `test`.`t1`.`a`) and (`test`.`t2`.`d` = `test`.`t3`.`e`) and (`test`.`t1`.`a` is not null) and (`test`.`t1`.`a` is not null) and (`test`.`t1`.`b` is not null))) where (`test`.`t1`.`a` < 5)
+explain extended
+select *
+from t1 left join ( t2 left join t3 on t3.e=t2.d )
+on t2.c=t1.b and t3.f=t1.a and t3.f is not null
+where t1.a < 5;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	range	a	a	5	NULL	3	100.00	Using index condition
+1	SIMPLE	t3	eq_ref	f,e	f	4	test.t1.a	1	100.00	Using where
+1	SIMPLE	t2	ref	c	c	5	test.t1.b	2	100.00	Using where
+Warnings:
+Note	1003	select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`c` AS `c`,`test`.`t2`.`d` AS `d`,`test`.`t3`.`e` AS `e`,`test`.`t3`.`f` AS `f` from `test`.`t1` left join (`test`.`t2` join `test`.`t3`) on(((`test`.`t2`.`c` = `test`.`t1`.`b`) and (`test`.`t3`.`f` = `test`.`t1`.`a`) and (`test`.`t2`.`d` = `test`.`t3`.`e`) and (`test`.`t1`.`a` is not null) and (`test`.`t1`.`a` is not null) and (`test`.`t1`.`b` is not null))) where (`test`.`t1`.`a` < 5)
+drop view v1;
+drop table t1,t2,t3;
 # -----------------------------------------------------------------
 # -- End of 5.5 tests.
 # -----------------------------------------------------------------
-- 
cgit v1.2.1


From ce903428a8517f5207b1e236c9187fa299bc0213 Mon Sep 17 00:00:00 2001
From: iangilfillan <github@greenman.co.za>
Date: Thu, 23 Feb 2017 11:27:52 +0200
Subject: Update MariaDB Foundation sponsors

---
 mysql-test/r/contributors.result | 14 ++++++++------
 1 file changed, 8 insertions(+), 6 deletions(-)

(limited to 'mysql-test/r')

diff --git a/mysql-test/r/contributors.result b/mysql-test/r/contributors.result
index f3f5e227d3a..4a26d0f19dd 100644
--- a/mysql-test/r/contributors.result
+++ b/mysql-test/r/contributors.result
@@ -1,15 +1,17 @@
 SHOW CONTRIBUTORS;
 Name	Location	Comment
-Booking.com	http://www.booking.com	Founding member, Platinum Sponsor of the MariaDB Foundation
+Booking.com	https://www.booking.com	Founding member, Platinum Sponsor of the MariaDB Foundation
+Alibaba Cloud	https://intl.aliyun.com	Platinum Sponsor of the MariaDB Foundation
 MariaDB Corporation	https://mariadb.com	Founding member, Gold Sponsor of the MariaDB Foundation
-Visma	http://visma.com	Gold Sponsor of the MariaDB Foundation
-DBS	http://dbs.com	Gold Sponsor of the MariaDB Foundation
+Visma	https://visma.com	Gold Sponsor of the MariaDB Foundation
+DBS	https://dbs.com	Gold Sponsor of the MariaDB Foundation
 Nexedi	https://www.nexedi.com	Silver Sponsor of the MariaDB Foundation
 Acronis	http://www.acronis.com	Silver Sponsor of the MariaDB Foundation
 Auttomattic	https://automattic.com	Bronze Sponsor of the MariaDB Foundation
-Verkkokauppa.com	https://virtuozzo.com	Bronze Sponsor of the MariaDB Foundation
-Virtuozzo	https://virtuozzo.com/	Bronze Sponsor of the MariaDB Foundation
-Tencent Game DBA	http://tencentdba.com/about/	Bronze Sponsor of the MariaDB Foundation
+Verkkokauppa.com	https://www.verkkokauppa.com	Bronze Sponsor of the MariaDB Foundation
+Virtuozzo	https://virtuozzo.com	Bronze Sponsor of the MariaDB Foundation
+Tencent Game DBA	http://tencentdba.com/about	Bronze Sponsor of the MariaDB Foundation
+Tencent TDSQL	http://tdsql.org	Bronze Sponsor of the MariaDB Foundation
 Google	USA	Sponsoring encryption, parallel replication and GTID
 Facebook	USA	Sponsoring non-blocking API, LIMIT ROWS EXAMINED etc
 Ronald Bradford	Brisbane, Australia	EFF contribution for UC2006 Auction
-- 
cgit v1.2.1


From 75f6067e89d7a777e7c1a1580a833aacc501d290 Mon Sep 17 00:00:00 2001
From: Oleksandr Byelkin <sanja@mariadb.com>
Date: Tue, 28 Feb 2017 17:39:28 +0100
Subject: MDEV-9635: Server crashes in part_of_refkey  or assertion `!created
 && key_to_save < (int)s->keys' failed in TABLE::use_index(int) or with
 join_cache_level>2

Do not try to create index where ref is for hash join.
---
 mysql-test/r/subselect_innodb.result | 15 +++++++++++++++
 1 file changed, 15 insertions(+)

(limited to 'mysql-test/r')

diff --git a/mysql-test/r/subselect_innodb.result b/mysql-test/r/subselect_innodb.result
index e52a9078fca..e8a545778aa 100644
--- a/mysql-test/r/subselect_innodb.result
+++ b/mysql-test/r/subselect_innodb.result
@@ -454,3 +454,18 @@ HAVING SQ2_alias1 . col_int_key >= 7
 1
 drop table t1;
 set optimizer_switch=@subselect_innodb_tmp;
+#
+# MDEV-9635:Server crashes in part_of_refkey  or assertion
+# `!created && key_to_save < (int)s->keys' failed in
+# TABLE::use_index(int) or with join_cache_level>2
+#
+SET join_cache_level=3;
+CREATE TABLE t1 (f1 VARCHAR(1024)) ENGINE=InnoDB;
+CREATE ALGORITHM=TEMPTABLE VIEW v1 AS SELECT * FROM t1;
+CREATE TABLE t2 (f2 VARCHAR(4)) ENGINE=InnoDB;
+INSERT INTO t2 VALUES ('foo'),('bar');
+SELECT * FROM v1, t2 WHERE ( f1, f2 ) IN ( SELECT f1, f1 FROM t1 );
+f1	f2
+set join_cache_level = default;
+drop view v1;
+drop table t1,t2;
-- 
cgit v1.2.1


From 606a4a48475c95480751755e811e0b6c76ce1c3e Mon Sep 17 00:00:00 2001
From: =?UTF-8?q?Vicen=C8=9Biu=20Ciorbaru?= <vicentiu@mariadb.org>
Date: Fri, 3 Mar 2017 20:12:48 +0200
Subject: Post MDEV-11902 Fix test failures in maria and myisam storage engines

my_readline can fail due to missing file. Make my_readline report this
condition separately so that we can catch it and report an appropriate
error message to the user.
---
 mysql-test/r/myisam-system.result | 2 +-
 1 file changed, 1 insertion(+), 1 deletion(-)

(limited to 'mysql-test/r')

diff --git a/mysql-test/r/myisam-system.result b/mysql-test/r/myisam-system.result
index 65684a3c07b..af5de8f2749 100644
--- a/mysql-test/r/myisam-system.result
+++ b/mysql-test/r/myisam-system.result
@@ -5,7 +5,7 @@ Warnings:
 Warning	2	Can't find file: './test/t1.MYI' (errno: 2 "No such file or directory")
 create table t1 (a int) engine=myisam;
 select * from t1;
-ERROR HY000: Can't find file: './test/t1.MYI' (errno: 20 "Not a directory")
+ERROR HY000: Can't find file: './test/t1.MYI' (errno: 2 "No such file or directory")
 drop table t1;
 Warnings:
 Warning	2	Can't find file: './test/t1.MYI' (errno: 2 "No such file or directory")
-- 
cgit v1.2.1


From 43903745e5af676db1fe813ab8e2ba7190353f83 Mon Sep 17 00:00:00 2001
From: Varun Gupta <varunraiko1803@gmail.com>
Date: Sun, 5 Mar 2017 10:58:05 +0530
Subject: MDEV-11078: NULL NOT IN (non-empty subquery) should never return
 results

Disabling the cond guards during the creation of Tricond Item for
constant and NULL left expression items
---
 mysql-test/r/subselect4.result | 34 ++++++++++++++++++++++++++++++++++
 1 file changed, 34 insertions(+)

(limited to 'mysql-test/r')

diff --git a/mysql-test/r/subselect4.result b/mysql-test/r/subselect4.result
index 89fb0902f53..2a229675817 100644
--- a/mysql-test/r/subselect4.result
+++ b/mysql-test/r/subselect4.result
@@ -2449,5 +2449,39 @@ EXECUTE stmt;
 i
 6
 drop table t1, t2, t3;
+#
+# MDEV-11078: NULL NOT IN (non-empty subquery) should never return results
+#
+create table t1(a int,b int);
+create table t2(a int,b int);
+insert into t1 value (1,2);
+select (NULL)  in (select 1 from t1);
+(NULL)  in (select 1 from t1)
+NULL
+select (null)  in (select 1 from t2);
+(null)  in (select 1 from t2)
+0
+select 1 in (select 1 from t1);
+1 in (select 1 from t1)
+1
+select 1 in (select 1 from t2);
+1 in (select 1 from t2)
+0
+select 1 from dual where null in (select 1 from t1);
+1
+select 1 from dual where null in (select 1 from t2);
+1
+select (null,null) in (select * from t1);
+(null,null) in (select * from t1)
+NULL
+select (null,null) in (select * from t2);
+(null,null) in (select * from t2)
+0
+select 1 from dual where null not in (select 1 from t1);
+1
+select 1 from dual where null not in (select 1 from t2);
+1
+1
+drop table t1,t2;
 SET optimizer_switch= @@global.optimizer_switch;
 set @@tmp_table_size= @@global.tmp_table_size;
-- 
cgit v1.2.1


From e823023e4bb48b0db9ee91477076fc20db704769 Mon Sep 17 00:00:00 2001
From: Tor Didriksen <tor.didriksen@oracle.com>
Date: Tue, 21 Apr 2015 08:32:31 +0200
Subject: Bug#18411494 WRONG COMPARSION ON BIG DECIMAL VALUES
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit

Problem: integer literals may be converted to floats for
comparison with decimal data. If the integers are large,
we may lose precision, and give wrong results.

Fix: for
  <non-const decimal expression> <cmp> <const string expression>
  or
  <const string expression> <cmp> <non-const decimal expression>
we override the compare_type chosen by item_cmp_type(), and
do comparison as decimal rather than float.

(cherry picked from commit https://github.com/mysql/mysql-server/commit/1cf3489ba42df1f29c1f3e269443254087505166 and edited by Johannes Weißl <jargon@molb.org>)
---
 mysql-test/r/type_newdecimal.result | 39 +++++++++++++++++++++++++++++++++++++
 1 file changed, 39 insertions(+)

(limited to 'mysql-test/r')

diff --git a/mysql-test/r/type_newdecimal.result b/mysql-test/r/type_newdecimal.result
index 2f6a3ea2a61..56a04971fa1 100644
--- a/mysql-test/r/type_newdecimal.result
+++ b/mysql-test/r/type_newdecimal.result
@@ -2243,3 +2243,42 @@ DROP TABLE t1;
 #
 # End of 10.1 tests
 #
+#
+# Bug#18408499 UNSIGNED BIGINT HIGH VALUES 
+#              WRONG NUMERICAL COMPARISON RESULTS
+#
+CREATE TABLE t1(value DECIMAL(24,0) NOT NULL);
+INSERT INTO t1(value) 
+VALUES('100000000000000000000001'),
+('100000000000000000000002'), 
+('100000000000000000000003');
+SELECT * FROM t1 WHERE value = '100000000000000000000002';
+value
+100000000000000000000002
+SELECT * FROM t1 WHERE '100000000000000000000002' = value;
+value
+100000000000000000000002
+SELECT * FROM t1 WHERE value + 0 = '100000000000000000000002';
+value
+100000000000000000000002
+SELECT * FROM t1 WHERE value = 100000000000000000000002;
+value
+100000000000000000000002
+SELECT * FROM t1 WHERE value + 0 = 100000000000000000000002;
+value
+100000000000000000000002
+PREPARE stmt FROM 'SELECT * FROM t1 WHERE value = ?';
+set @a="100000000000000000000002";
+EXECUTE stmt using @a;
+value
+100000000000000000000002
+set @a=100000000000000000000002;
+EXECUTE stmt using @a;
+value
+100000000000000000000002
+DEALLOCATE PREPARE stmt;
+ALTER TABLE t1 ADD INDEX value (value);
+SELECT * FROM t1 WHERE value = '100000000000000000000002';
+value
+100000000000000000000002
+DROP TABLE t1;
-- 
cgit v1.2.1


From aeff61ee5857f2351030c604f62dc7628543209a Mon Sep 17 00:00:00 2001
From: Alexey Botchkov <holyfoot@askmonty.org>
Date: Tue, 7 Mar 2017 17:27:27 +0400
Subject: MDEV-12064 Bug#18411494 WRONG COMPARSION ON BIG DECIMAL VALUES.

        Test results updated.
---
 mysql-test/r/select.result           |  2 +-
 mysql-test/r/select_jcl6.result      |  2 +-
 mysql-test/r/select_pkeycache.result |  2 +-
 mysql-test/r/type_num.result         | 29 +++++++++++++++--------------
 4 files changed, 18 insertions(+), 17 deletions(-)

(limited to 'mysql-test/r')

diff --git a/mysql-test/r/select.result b/mysql-test/r/select.result
index ca8b235ba31..787e321dd57 100644
--- a/mysql-test/r/select.result
+++ b/mysql-test/r/select.result
@@ -2956,7 +2956,7 @@ insert into t1 values (1,'x',5);
 select * from t1 natural join v1;
 s1	s2	s3
 Warnings:
-Warning	1292	Truncated incorrect DOUBLE value: 'x'
+Warning	1292	Truncated incorrect DECIMAL value: 'x'
 drop table t1;
 drop view v1;
 create table t1(a1 int);
diff --git a/mysql-test/r/select_jcl6.result b/mysql-test/r/select_jcl6.result
index 4659b0753ed..10d8a05ad40 100644
--- a/mysql-test/r/select_jcl6.result
+++ b/mysql-test/r/select_jcl6.result
@@ -2967,7 +2967,7 @@ insert into t1 values (1,'x',5);
 select * from t1 natural join v1;
 s1	s2	s3
 Warnings:
-Warning	1292	Truncated incorrect DOUBLE value: 'x'
+Warning	1292	Truncated incorrect DECIMAL value: 'x'
 drop table t1;
 drop view v1;
 create table t1(a1 int);
diff --git a/mysql-test/r/select_pkeycache.result b/mysql-test/r/select_pkeycache.result
index ca8b235ba31..787e321dd57 100644
--- a/mysql-test/r/select_pkeycache.result
+++ b/mysql-test/r/select_pkeycache.result
@@ -2956,7 +2956,7 @@ insert into t1 values (1,'x',5);
 select * from t1 natural join v1;
 s1	s2	s3
 Warnings:
-Warning	1292	Truncated incorrect DOUBLE value: 'x'
+Warning	1292	Truncated incorrect DECIMAL value: 'x'
 drop table t1;
 drop view v1;
 create table t1(a1 int);
diff --git a/mysql-test/r/type_num.result b/mysql-test/r/type_num.result
index 9ee67ac8f33..2eed7890e75 100644
--- a/mysql-test/r/type_num.result
+++ b/mysql-test/r/type_num.result
@@ -570,7 +570,7 @@ SELECT COUNT(*) FROM t1 WHERE d='1 ';
 COUNT(*)
 2
 Warnings:
-Note	1292	Truncated incorrect DOUBLE value: '1 '
+Note	1292	Truncated incorrect DECIMAL value: '1 '
 SELECT COUNT(*) FROM t1 WHERE f4='';
 COUNT(*)
 2
@@ -605,7 +605,7 @@ SELECT COUNT(*) FROM t1 WHERE d='';
 COUNT(*)
 2
 Warnings:
-Warning	1292	Truncated incorrect DOUBLE value: ''
+Warning	1292	Truncated incorrect DECIMAL value: ''
 SELECT COUNT(*) FROM t1 WHERE f4='x';
 COUNT(*)
 2
@@ -640,7 +640,7 @@ SELECT COUNT(*) FROM t1 WHERE d='x';
 COUNT(*)
 2
 Warnings:
-Warning	1292	Truncated incorrect DOUBLE value: 'x'
+Warning	1292	Truncated incorrect DECIMAL value: 'x'
 SELECT COUNT(*) FROM t1 WHERE f4=' x';
 COUNT(*)
 2
@@ -675,7 +675,7 @@ SELECT COUNT(*) FROM t1 WHERE d=' x';
 COUNT(*)
 2
 Warnings:
-Warning	1292	Truncated incorrect DOUBLE value: ' x'
+Warning	1292	Truncated incorrect DECIMAL value: ' x'
 SELECT COUNT(*) FROM t1 WHERE f4='.';
 COUNT(*)
 2
@@ -710,7 +710,7 @@ SELECT COUNT(*) FROM t1 WHERE d='.';
 COUNT(*)
 2
 Warnings:
-Warning	1292	Truncated incorrect DOUBLE value: '.'
+Warning	1292	Truncated incorrect DECIMAL value: '.'
 SELECT COUNT(*) FROM t1 WHERE f4='-';
 COUNT(*)
 2
@@ -745,7 +745,7 @@ SELECT COUNT(*) FROM t1 WHERE d='-';
 COUNT(*)
 2
 Warnings:
-Warning	1292	Truncated incorrect DOUBLE value: '-'
+Warning	1292	Truncated incorrect DECIMAL value: '-'
 SELECT COUNT(*) FROM t1 WHERE f4='+';
 COUNT(*)
 2
@@ -780,7 +780,7 @@ SELECT COUNT(*) FROM t1 WHERE d='+';
 COUNT(*)
 2
 Warnings:
-Warning	1292	Truncated incorrect DOUBLE value: '+'
+Warning	1292	Truncated incorrect DECIMAL value: '+'
 SELECT COUNT(*) FROM t1 WHERE f4='1x';
 COUNT(*)
 2
@@ -815,7 +815,7 @@ SELECT COUNT(*) FROM t1 WHERE d='1x';
 COUNT(*)
 2
 Warnings:
-Warning	1292	Truncated incorrect DOUBLE value: '1x'
+Warning	1292	Truncated incorrect DECIMAL value: '1x'
 SELECT COUNT(*) FROM t1 WHERE f4='1e';
 COUNT(*)
 2
@@ -850,7 +850,7 @@ SELECT COUNT(*) FROM t1 WHERE d='1e';
 COUNT(*)
 2
 Warnings:
-Warning	1292	Truncated incorrect DOUBLE value: '1e'
+Warning	1292	Truncated incorrect DECIMAL value: '1e'
 SELECT COUNT(*) FROM t1 WHERE f4='1e+';
 COUNT(*)
 2
@@ -885,7 +885,7 @@ SELECT COUNT(*) FROM t1 WHERE d='1e+';
 COUNT(*)
 2
 Warnings:
-Warning	1292	Truncated incorrect DOUBLE value: '1e+'
+Warning	1292	Truncated incorrect DECIMAL value: '1e+'
 SELECT COUNT(*) FROM t1 WHERE f4='1E-';
 COUNT(*)
 2
@@ -920,7 +920,7 @@ SELECT COUNT(*) FROM t1 WHERE d='1E-';
 COUNT(*)
 2
 Warnings:
-Warning	1292	Truncated incorrect DOUBLE value: '1E-'
+Warning	1292	Truncated incorrect DECIMAL value: '1E-'
 SELECT COUNT(*) FROM t1 WHERE f4='1Ex';
 COUNT(*)
 2
@@ -955,7 +955,7 @@ SELECT COUNT(*) FROM t1 WHERE d='1Ex';
 COUNT(*)
 2
 Warnings:
-Warning	1292	Truncated incorrect DOUBLE value: '1Ex'
+Warning	1292	Truncated incorrect DECIMAL value: '1Ex'
 SELECT COUNT(*) FROM t1 WHERE f4='1e+x';
 COUNT(*)
 2
@@ -990,7 +990,7 @@ SELECT COUNT(*) FROM t1 WHERE d='1e+x';
 COUNT(*)
 2
 Warnings:
-Warning	1292	Truncated incorrect DOUBLE value: '1e+x'
+Warning	1292	Truncated incorrect DECIMAL value: '1e+x'
 SELECT COUNT(*) FROM t1 WHERE f4='1e1000';
 COUNT(*)
 0
@@ -1025,7 +1025,8 @@ SELECT COUNT(*) FROM t1 WHERE d='1e1000';
 COUNT(*)
 0
 Warnings:
-Warning	1292	Truncated incorrect DOUBLE value: '1e1000'
+Warning	1916	Got overflow when converting '' to DECIMAL. Value truncated.
+Warning	1292	Truncated incorrect DECIMAL value: '1e1000'
 ALTER TABLE t1
 ADD KEY f4(f4),
 ADD KEY f8(f8),
-- 
cgit v1.2.1


From 814d0507604192d1da18eb2e30529e5559ce140e Mon Sep 17 00:00:00 2001
From: =?UTF-8?q?Vicen=C8=9Biu=20Ciorbaru?= <vicentiu@mariadb.org>
Date: Fri, 10 Mar 2017 14:07:22 +0200
Subject: MDEV-12215: main.repair_symlink-5543 fails in buildbot

If openat is present on the system and it tries to open a symlink with
O_NOFOLLOW, we get errno 40.

If openat is not present on the system, we use the alternative open call, with
slightly different logic. IF the symlink doesn't point to a valid file,
we get errno 20. This test uses an invalid symlink on the table t1.MYD.
---
 mysql-test/r/repair_symlink-5543.result | 6 ++++--
 1 file changed, 4 insertions(+), 2 deletions(-)

(limited to 'mysql-test/r')

diff --git a/mysql-test/r/repair_symlink-5543.result b/mysql-test/r/repair_symlink-5543.result
index e827c7b7f73..f46e5833468 100644
--- a/mysql-test/r/repair_symlink-5543.result
+++ b/mysql-test/r/repair_symlink-5543.result
@@ -1,9 +1,11 @@
 create table t1 (a int) engine=myisam data directory='MYSQL_TMP_DIR';
 insert t1 values (1);
+# Some systems fail with errcode 40, when doing openat, while others
+# don't have openat and fail with errcode 20.
 repair table t1;
 Table	Op	Msg_type	Msg_text
-test.t1	repair	error	40 for record at pos 0
-test.t1	repair	Error	File 'MYSQL_TMP_DIR/t1.MYD' not found (Errcode: 40 "Too many levels of symbolic links")
+test.t1	repair	error	20 for record at pos 0
+test.t1	repair	Error	File 'MYSQL_TMP_DIR/t1.MYD' not found (Errcode: 20 "<errmsg>")
 test.t1	repair	status	Operation failed
 drop table t1;
 create table t2 (a int) engine=aria data directory='MYSQL_TMP_DIR';
-- 
cgit v1.2.1


From 5fa04aae9ec94428a68ef4f6f681564111933c11 Mon Sep 17 00:00:00 2001
From: Sergei Golubchik <serg@mariadb.org>
Date: Wed, 1 Mar 2017 23:52:35 +0100
Subject: MDEV-11842 Fail to insert on a table where a field has no default

has_no_default_value() should only fail the insert in the strict mode.

Additionally, don't check for "all fields are given values" twice,
it'll produce duplicate warnings.
---
 mysql-test/r/trigger_no_defaults-11698.result | 18 ++++++++++++++++++
 1 file changed, 18 insertions(+)

(limited to 'mysql-test/r')

diff --git a/mysql-test/r/trigger_no_defaults-11698.result b/mysql-test/r/trigger_no_defaults-11698.result
index 40546cee41d..93672092180 100644
--- a/mysql-test/r/trigger_no_defaults-11698.result
+++ b/mysql-test/r/trigger_no_defaults-11698.result
@@ -20,3 +20,21 @@ a	b
 10	10
 0	30
 drop table t1;
+set sql_mode=default;
+create table t1 (
+id int(11) not null auto_increment primary key,
+data1 varchar(10) not null,
+data2 varchar(10) not null
+);
+insert into t1 (data2) values ('x');
+Warnings:
+Warning	1364	Field 'data1' doesn't have a default value
+create trigger test_trigger before insert on t1 for each row begin end;
+insert into t1 (data2) values ('y');
+Warnings:
+Warning	1364	Field 'data1' doesn't have a default value
+select * from t1;
+id	data1	data2
+1		x
+2		y
+drop table t1;
-- 
cgit v1.2.1


From 48b1d175345caa3c644597044751906a04106b91 Mon Sep 17 00:00:00 2001
From: Sergei Golubchik <serg@mariadb.org>
Date: Thu, 2 Mar 2017 15:36:18 +0100
Subject: MDEV-11943 I_S.TABLES inconsistencies with tables with unknown
 storage engine

Make SELECT <columns> FROM I_S.TABLES behave identically independently
from whether <columns> require opening the table in engine or
<columns> can be filled with only opening the frm.

In particular, fill_schema_table_from_frm() should not silently skip
frms with unknown engine, but should fill the I_S.TABLES row
with NULLs just like fill_schema_table_by_open() does.
---
 mysql-test/r/derived_view.result     |  5 +++++
 mysql-test/r/drop_bad_db_type.result | 21 +++++++++++++++++++++
 mysql-test/r/gis.result              |  4 ++--
 mysql-test/r/join.result             |  3 +++
 mysql-test/r/partition_column.result |  2 +-
 mysql-test/r/ps.result               |  9 +++++++++
 6 files changed, 41 insertions(+), 3 deletions(-)

(limited to 'mysql-test/r')

diff --git a/mysql-test/r/derived_view.result b/mysql-test/r/derived_view.result
index 0a80a16b508..bd07fba6d4f 100644
--- a/mysql-test/r/derived_view.result
+++ b/mysql-test/r/derived_view.result
@@ -1979,6 +1979,11 @@ GROUP BY TABLE_SCHEMA) AS UNIQUES
 ON ( COLUMNS.TABLE_SCHEMA = UNIQUES.TABLE_SCHEMA);
 COUNT(*) > 0
 1
+Warnings:
+Warning	1286	Unknown storage engine 'InnoDB'
+Warning	1286	Unknown storage engine 'InnoDB'
+Warning	1286	Unknown storage engine 'InnoDB'
+Warning	1286	Unknown storage engine 'InnoDB'
 DROP TABLE t1;
 SET SESSION optimizer_switch= @save_optimizer_switch;
 #
diff --git a/mysql-test/r/drop_bad_db_type.result b/mysql-test/r/drop_bad_db_type.result
index 6a125cdccf5..9bdf9468d70 100644
--- a/mysql-test/r/drop_bad_db_type.result
+++ b/mysql-test/r/drop_bad_db_type.result
@@ -4,6 +4,27 @@ create table t1 (a int) engine=archive;
 insert t1 values (1),(2),(3);
 flush tables;
 uninstall soname 'ha_archive';
+select table_schema, table_name from information_schema.tables where table_name like 't1';
+table_schema	test
+table_name	t1
+select table_schema, table_name, engine, version from information_schema.tables where table_name like 't1';
+table_schema	test
+table_name	t1
+engine	NULL
+version	NULL
+Warnings:
+Level	Warning
+Code	1286
+Message	Unknown storage engine 'ARCHIVE'
+select table_schema, table_name, engine, row_format from information_schema.tables where table_name like 't1';
+table_schema	test
+table_name	t1
+engine	NULL
+row_format	NULL
+Warnings:
+Level	Warning
+Code	1286
+Message	Unknown storage engine 'ARCHIVE'
 install soname 'ha_archive';
 t1.ARZ
 t1.frm
diff --git a/mysql-test/r/gis.result b/mysql-test/r/gis.result
index 33080509114..660a72fc26c 100644
--- a/mysql-test/r/gis.result
+++ b/mysql-test/r/gis.result
@@ -1732,7 +1732,7 @@ SPATIAL_REF_SYS	CREATE TEMPORARY TABLE `SPATIAL_REF_SYS` (
 ) ENGINE=MEMORY DEFAULT CHARSET=utf8
 create table t1(g GEOMETRY, pt POINT);
 create table t2(g LINESTRING, pl POLYGON);
-select * from information_schema.geometry_columns;
+select * from information_schema.geometry_columns where f_table_schema='test';
 F_TABLE_CATALOG	F_TABLE_SCHEMA	F_TABLE_NAME	F_GEOMETRY_COLUMN	G_TABLE_CATALOG	G_TABLE_SCHEMA	G_TABLE_NAME	G_GEOMETRY_COLUMN	STORAGE_TYPE	GEOMETRY_TYPE	COORD_DIMENSION	MAX_PPR	SRID
 def	test	t1		def	test	t1	g	1	0	2	0	0
 def	test	t1		def	test	t1	pt	1	1	2	0	0
@@ -1741,7 +1741,7 @@ def	test	t2		def	test	t2	pl	1	3	2	0	0
 drop table t1, t2;
 10.1 tests
 create table t1(g GEOMETRY(9,4) REF_SYSTEM_ID=101, pt POINT(8,2), pg GEOMETRY REF_SYSTEM_ID=102);
-SELECT SRID from information_schema.geometry_columns WHERE G_TABLE_NAME='t1';
+SELECT SRID from information_schema.geometry_columns WHERE f_table_schema='test' and G_TABLE_NAME='t1';
 SRID
 101
 0
diff --git a/mysql-test/r/join.result b/mysql-test/r/join.result
index d500b38a8dc..3e39c95fe16 100644
--- a/mysql-test/r/join.result
+++ b/mysql-test/r/join.result
@@ -757,6 +757,9 @@ from information_schema.statistics join information_schema.columns using(table_n
 TABLE_NAME	COLUMN_NAME	TABLE_CATALOG	TABLE_SCHEMA	NON_UNIQUE	INDEX_SCHEMA	INDEX_NAME	SEQ_IN_INDEX	COLLATION	SUB_PART	PACKED	NULLABLE	INDEX_TYPE	COMMENT	TABLE_CATALOG	TABLE_SCHEMA	COLUMN_DEFAULT	IS_NULLABLE	DATA_TYPE	CHARACTER_MAXIMUM_LENGTH	CHARACTER_OCTET_LENGTH	NUMERIC_PRECISION	NUMERIC_SCALE	CHARACTER_SET_NAME	COLLATION_NAME	COLUMN_TYPE	COLUMN_KEY	EXTRA	COLUMN_COMMENT
 user	Host	def	mysql	0	mysql	PRIMARY	1	A	NULL	NULL		BTREE		def	mysql		NO	char	60	180	NULL	NULL	utf8	utf8_bin	char(60)	PRI		
 user	User	def	mysql	0	mysql	PRIMARY	2	A	NULL	NULL		BTREE		def	mysql		NO	char	80	240	NULL	NULL	utf8	utf8_bin	char(80)	PRI		
+Warnings:
+Warning	1286	Unknown storage engine 'InnoDB'
+Warning	1286	Unknown storage engine 'InnoDB'
 drop table t1;
 drop table t2;
 drop table t3;
diff --git a/mysql-test/r/partition_column.result b/mysql-test/r/partition_column.result
index a494656a6a6..f18e9b39e1e 100644
--- a/mysql-test/r/partition_column.result
+++ b/mysql-test/r/partition_column.result
@@ -540,7 +540,7 @@ a	b
 drop table t1;
 create table t1 as select to_seconds(null) as to_seconds;
 select data_type from information_schema.columns
-where column_name='to_seconds';
+where table_schema='test' and column_name='to_seconds';
 data_type
 int
 drop table t1;
diff --git a/mysql-test/r/ps.result b/mysql-test/r/ps.result
index 1e63ac57768..b24fe55e1b2 100644
--- a/mysql-test/r/ps.result
+++ b/mysql-test/r/ps.result
@@ -1203,12 +1203,21 @@ prepare my_stmt from @aux;
 execute my_stmt;
 COUNT(*)
 46
+Warnings:
+Warning	1286	Unknown storage engine 'InnoDB'
+Warning	1286	Unknown storage engine 'InnoDB'
 execute my_stmt;
 COUNT(*)
 46
+Warnings:
+Warning	1286	Unknown storage engine 'InnoDB'
+Warning	1286	Unknown storage engine 'InnoDB'
 execute my_stmt;
 COUNT(*)
 46
+Warnings:
+Warning	1286	Unknown storage engine 'InnoDB'
+Warning	1286	Unknown storage engine 'InnoDB'
 deallocate prepare my_stmt;
 drop procedure if exists p1|
 drop table if exists t1|
-- 
cgit v1.2.1


From 8f1ca5e311bda92d4e901aae8ec5c47597d0f934 Mon Sep 17 00:00:00 2001
From: Sergei Golubchik <serg@mariadb.org>
Date: Fri, 3 Mar 2017 15:27:19 +0100
Subject: MDEV-11943 I_S.TABLES inconsistencies with tables with unknown
 storage engine

Try harder to show the table's engine.

If the table's engine is not loaded, the table won't open.
But we can still read the engine name from frm as a string.
---
 mysql-test/r/drop_bad_db_type.result | 4 ++--
 1 file changed, 2 insertions(+), 2 deletions(-)

(limited to 'mysql-test/r')

diff --git a/mysql-test/r/drop_bad_db_type.result b/mysql-test/r/drop_bad_db_type.result
index 9bdf9468d70..de22373e0fd 100644
--- a/mysql-test/r/drop_bad_db_type.result
+++ b/mysql-test/r/drop_bad_db_type.result
@@ -10,7 +10,7 @@ table_name	t1
 select table_schema, table_name, engine, version from information_schema.tables where table_name like 't1';
 table_schema	test
 table_name	t1
-engine	NULL
+engine	ARCHIVE
 version	NULL
 Warnings:
 Level	Warning
@@ -19,7 +19,7 @@ Message	Unknown storage engine 'ARCHIVE'
 select table_schema, table_name, engine, row_format from information_schema.tables where table_name like 't1';
 table_schema	test
 table_name	t1
-engine	NULL
+engine	ARCHIVE
 row_format	NULL
 Warnings:
 Level	Warning
-- 
cgit v1.2.1


From 3d06f0f72cbabe833036f920e9452cd6ef46e5aa Mon Sep 17 00:00:00 2001
From: Sergei Golubchik <serg@mariadb.org>
Date: Sat, 4 Mar 2017 17:17:00 +0100
Subject: MDEV-11942 BLACKHOLE is no longer active in 10.1 by default,
 mysql_upgrade not handling the situation

fix the patch. add tests
---
 mysql-test/r/ctype_upgrade.result           |  26 +--
 mysql-test/r/log_tables_upgrade.result      |  13 +-
 mysql-test/r/mysql_upgrade-6984.result      |  13 +-
 mysql-test/r/mysql_upgrade.result           | 130 ++++++------
 mysql-test/r/mysql_upgrade_no_innodb.result |  13 +-
 mysql-test/r/mysql_upgrade_noengine.result  | 297 ++++++++++++++++++++++++++++
 mysql-test/r/mysql_upgrade_ssl.result       |  13 +-
 mysql-test/r/mysql_upgrade_view.result      |  39 ++--
 8 files changed, 430 insertions(+), 114 deletions(-)
 create mode 100644 mysql-test/r/mysql_upgrade_noengine.result

(limited to 'mysql-test/r')

diff --git a/mysql-test/r/ctype_upgrade.result b/mysql-test/r/ctype_upgrade.result
index b317be42d5c..53cb858035b 100644
--- a/mysql-test/r/ctype_upgrade.result
+++ b/mysql-test/r/ctype_upgrade.result
@@ -227,7 +227,7 @@ DROP TABLE mysql050614_xxx_croatian_ci;
 # Checking mysql_upgrade
 #
 # Running mysql_upgrade
-Phase 1/6: Checking and upgrading mysql database
+Phase 1/7: Checking and upgrading mysql database
 Processing databases
 mysql
 mysql.column_stats                                 OK
@@ -258,10 +258,11 @@ mysql.time_zone_name                               OK
 mysql.time_zone_transition                         OK
 mysql.time_zone_transition_type                    OK
 mysql.user                                         OK
-Phase 2/6: Fixing views
-Phase 3/6: Running 'mysql_fix_privilege_tables'
-Phase 4/6: Fixing table and database names
-Phase 5/6: Checking and upgrading tables
+Phase 2/7: Installing used storage engines... Skipped
+Phase 3/7: Fixing views
+Phase 4/7: Running 'mysql_fix_privilege_tables'
+Phase 5/7: Fixing table and database names
+Phase 6/7: Checking and upgrading tables
 Processing databases
 information_schema
 mtr
@@ -280,11 +281,11 @@ test.maria050313_ucs2_croatian_ci_def              OK
 test.maria050313_utf8_croatian_ci                  OK
 test.maria050533_xxx_croatian_ci                   OK
 test.maria100004_xxx_croatian_ci                   OK
-Phase 6/6: Running 'FLUSH PRIVILEGES'
+Phase 7/7: Running 'FLUSH PRIVILEGES'
 OK
 # Running mysql_upgrade for the second time
 # This should report OK for all tables
-Phase 1/6: Checking and upgrading mysql database
+Phase 1/7: Checking and upgrading mysql database
 Processing databases
 mysql
 mysql.column_stats                                 OK
@@ -315,10 +316,11 @@ mysql.time_zone_name                               OK
 mysql.time_zone_transition                         OK
 mysql.time_zone_transition_type                    OK
 mysql.user                                         OK
-Phase 2/6: Fixing views
-Phase 3/6: Running 'mysql_fix_privilege_tables'
-Phase 4/6: Fixing table and database names
-Phase 5/6: Checking and upgrading tables
+Phase 2/7: Installing used storage engines... Skipped
+Phase 3/7: Fixing views
+Phase 4/7: Running 'mysql_fix_privilege_tables'
+Phase 5/7: Fixing table and database names
+Phase 6/7: Checking and upgrading tables
 Processing databases
 information_schema
 mtr
@@ -331,7 +333,7 @@ test.maria050313_utf8_croatian_ci                  OK
 test.maria050533_xxx_croatian_ci                   OK
 test.maria100004_xxx_croatian_ci                   OK
 test.mysql050614_xxx_croatian_ci                   OK
-Phase 6/6: Running 'FLUSH PRIVILEGES'
+Phase 7/7: Running 'FLUSH PRIVILEGES'
 OK
 SHOW CREATE TABLE maria050313_ucs2_croatian_ci_def;
 Table	Create Table
diff --git a/mysql-test/r/log_tables_upgrade.result b/mysql-test/r/log_tables_upgrade.result
index 6cbb25bd1d4..a56d067c2cd 100644
--- a/mysql-test/r/log_tables_upgrade.result
+++ b/mysql-test/r/log_tables_upgrade.result
@@ -11,7 +11,7 @@ Table	Op	Msg_type	Msg_text
 test.bug49823	repair	status	OK
 RENAME TABLE general_log TO renamed_general_log;
 RENAME TABLE test.bug49823 TO general_log;
-Phase 1/6: Checking and upgrading mysql database
+Phase 1/7: Checking and upgrading mysql database
 Processing databases
 mysql
 mysql.column_stats                                 OK
@@ -43,10 +43,11 @@ mysql.time_zone_name                               OK
 mysql.time_zone_transition                         OK
 mysql.time_zone_transition_type                    OK
 mysql.user                                         OK
-Phase 2/6: Fixing views
-Phase 3/6: Running 'mysql_fix_privilege_tables'
-Phase 4/6: Fixing table and database names
-Phase 5/6: Checking and upgrading tables
+Phase 2/7: Installing used storage engines... Skipped
+Phase 3/7: Fixing views
+Phase 4/7: Running 'mysql_fix_privilege_tables'
+Phase 5/7: Fixing table and database names
+Phase 6/7: Checking and upgrading tables
 Processing databases
 information_schema
 mtr
@@ -54,7 +55,7 @@ mtr.global_suppressions                            OK
 mtr.test_suppressions                              OK
 performance_schema
 test
-Phase 6/6: Running 'FLUSH PRIVILEGES'
+Phase 7/7: Running 'FLUSH PRIVILEGES'
 OK
 DROP TABLE general_log;
 RENAME TABLE renamed_general_log TO general_log;
diff --git a/mysql-test/r/mysql_upgrade-6984.result b/mysql-test/r/mysql_upgrade-6984.result
index 6aea4806ddb..dacea61d094 100644
--- a/mysql-test/r/mysql_upgrade-6984.result
+++ b/mysql-test/r/mysql_upgrade-6984.result
@@ -1,5 +1,5 @@
 update mysql.user set password=password("foo") where user='root';
-Phase 1/6: Checking and upgrading mysql database
+Phase 1/7: Checking and upgrading mysql database
 Processing databases
 mysql
 mysql.column_stats                                 OK
@@ -42,10 +42,11 @@ error    : Corrupt
 mysql.innodb_table_stats
 Error    : Unknown storage engine 'InnoDB'
 error    : Corrupt
-Phase 2/6: Fixing views
-Phase 3/6: Running 'mysql_fix_privilege_tables'
-Phase 4/6: Fixing table and database names
-Phase 5/6: Checking and upgrading tables
+Phase 2/7: Installing used storage engines... Skipped
+Phase 3/7: Fixing views
+Phase 4/7: Running 'mysql_fix_privilege_tables'
+Phase 5/7: Fixing table and database names
+Phase 6/7: Checking and upgrading tables
 Processing databases
 information_schema
 mtr
@@ -53,7 +54,7 @@ mtr.global_suppressions                            OK
 mtr.test_suppressions                              OK
 performance_schema
 test
-Phase 6/6: Running 'FLUSH PRIVILEGES'
+Phase 7/7: Running 'FLUSH PRIVILEGES'
 OK
 update mysql.user set password='' where user='root';
 flush privileges;
diff --git a/mysql-test/r/mysql_upgrade.result b/mysql-test/r/mysql_upgrade.result
index d10e042729f..46725055952 100644
--- a/mysql-test/r/mysql_upgrade.result
+++ b/mysql-test/r/mysql_upgrade.result
@@ -1,6 +1,6 @@
 set sql_mode="";
 Run mysql_upgrade once
-Phase 1/6: Checking and upgrading mysql database
+Phase 1/7: Checking and upgrading mysql database
 Processing databases
 mysql
 mysql.column_stats                                 OK
@@ -31,10 +31,11 @@ mysql.time_zone_name                               OK
 mysql.time_zone_transition                         OK
 mysql.time_zone_transition_type                    OK
 mysql.user                                         OK
-Phase 2/6: Fixing views
-Phase 3/6: Running 'mysql_fix_privilege_tables'
-Phase 4/6: Fixing table and database names
-Phase 5/6: Checking and upgrading tables
+Phase 2/7: Installing used storage engines... Skipped
+Phase 3/7: Fixing views
+Phase 4/7: Running 'mysql_fix_privilege_tables'
+Phase 5/7: Fixing table and database names
+Phase 6/7: Checking and upgrading tables
 Processing databases
 information_schema
 mtr
@@ -42,12 +43,12 @@ mtr.global_suppressions                            OK
 mtr.test_suppressions                              OK
 performance_schema
 test
-Phase 6/6: Running 'FLUSH PRIVILEGES'
+Phase 7/7: Running 'FLUSH PRIVILEGES'
 OK
 Run it again - should say already completed
 This installation of MySQL is already upgraded to VERSION, use --force if you still need to run mysql_upgrade
 Force should run it regardless of whether it has been run before
-Phase 1/6: Checking and upgrading mysql database
+Phase 1/7: Checking and upgrading mysql database
 Processing databases
 mysql
 mysql.column_stats                                 OK
@@ -78,10 +79,11 @@ mysql.time_zone_name                               OK
 mysql.time_zone_transition                         OK
 mysql.time_zone_transition_type                    OK
 mysql.user                                         OK
-Phase 2/6: Fixing views
-Phase 3/6: Running 'mysql_fix_privilege_tables'
-Phase 4/6: Fixing table and database names
-Phase 5/6: Checking and upgrading tables
+Phase 2/7: Installing used storage engines... Skipped
+Phase 3/7: Fixing views
+Phase 4/7: Running 'mysql_fix_privilege_tables'
+Phase 5/7: Fixing table and database names
+Phase 6/7: Checking and upgrading tables
 Processing databases
 information_schema
 mtr
@@ -89,12 +91,12 @@ mtr.global_suppressions                            OK
 mtr.test_suppressions                              OK
 performance_schema
 test
-Phase 6/6: Running 'FLUSH PRIVILEGES'
+Phase 7/7: Running 'FLUSH PRIVILEGES'
 OK
 CREATE USER mysqltest1@'%' IDENTIFIED by 'sakila';
 GRANT ALL ON *.* TO mysqltest1@'%';
 Run mysql_upgrade with password protected account
-Phase 1/6: Checking and upgrading mysql database
+Phase 1/7: Checking and upgrading mysql database
 Processing databases
 mysql
 mysql.column_stats                                 OK
@@ -125,10 +127,11 @@ mysql.time_zone_name                               OK
 mysql.time_zone_transition                         OK
 mysql.time_zone_transition_type                    OK
 mysql.user                                         OK
-Phase 2/6: Fixing views
-Phase 3/6: Running 'mysql_fix_privilege_tables'
-Phase 4/6: Fixing table and database names
-Phase 5/6: Checking and upgrading tables
+Phase 2/7: Installing used storage engines... Skipped
+Phase 3/7: Fixing views
+Phase 4/7: Running 'mysql_fix_privilege_tables'
+Phase 5/7: Fixing table and database names
+Phase 6/7: Checking and upgrading tables
 Processing databases
 information_schema
 mtr
@@ -136,7 +139,7 @@ mtr.global_suppressions                            OK
 mtr.test_suppressions                              OK
 performance_schema
 test
-Phase 6/6: Running 'FLUSH PRIVILEGES'
+Phase 7/7: Running 'FLUSH PRIVILEGES'
 OK
 DROP USER mysqltest1@'%';
 Version check failed. Got the following error when calling the 'mysql' command line client
@@ -146,7 +149,7 @@ Run mysql_upgrade with a non existing server socket
 mysqlcheck: Got error: 2005: Unknown MySQL server host 'not_existing_host' (errno) when trying to connect
 FATAL ERROR: Upgrade failed
 set GLOBAL sql_mode='STRICT_ALL_TABLES,ANSI_QUOTES,NO_ZERO_DATE';
-Phase 1/6: Checking and upgrading mysql database
+Phase 1/7: Checking and upgrading mysql database
 Processing databases
 mysql
 mysql.column_stats                                 OK
@@ -177,10 +180,11 @@ mysql.time_zone_name                               OK
 mysql.time_zone_transition                         OK
 mysql.time_zone_transition_type                    OK
 mysql.user                                         OK
-Phase 2/6: Fixing views
-Phase 3/6: Running 'mysql_fix_privilege_tables'
-Phase 4/6: Fixing table and database names
-Phase 5/6: Checking and upgrading tables
+Phase 2/7: Installing used storage engines... Skipped
+Phase 3/7: Fixing views
+Phase 4/7: Running 'mysql_fix_privilege_tables'
+Phase 5/7: Fixing table and database names
+Phase 6/7: Checking and upgrading tables
 Processing databases
 information_schema
 mtr
@@ -188,7 +192,7 @@ mtr.global_suppressions                            OK
 mtr.test_suppressions                              OK
 performance_schema
 test
-Phase 6/6: Running 'FLUSH PRIVILEGES'
+Phase 7/7: Running 'FLUSH PRIVILEGES'
 OK
 set GLOBAL sql_mode=default;
 #
@@ -199,7 +203,7 @@ CREATE PROCEDURE testproc() BEGIN END;
 UPDATE mysql.proc SET character_set_client = NULL WHERE name LIKE 'testproc';
 UPDATE mysql.proc SET collation_connection = NULL WHERE name LIKE 'testproc';
 UPDATE mysql.proc SET db_collation = NULL WHERE name LIKE 'testproc';
-Phase 1/6: Checking and upgrading mysql database
+Phase 1/7: Checking and upgrading mysql database
 Processing databases
 mysql
 mysql.column_stats                                 OK
@@ -230,10 +234,11 @@ mysql.time_zone_name                               OK
 mysql.time_zone_transition                         OK
 mysql.time_zone_transition_type                    OK
 mysql.user                                         OK
-Phase 2/6: Fixing views
-Phase 3/6: Running 'mysql_fix_privilege_tables'
-Phase 4/6: Fixing table and database names
-Phase 5/6: Checking and upgrading tables
+Phase 2/7: Installing used storage engines... Skipped
+Phase 3/7: Fixing views
+Phase 4/7: Running 'mysql_fix_privilege_tables'
+Phase 5/7: Fixing table and database names
+Phase 6/7: Checking and upgrading tables
 Processing databases
 information_schema
 mtr
@@ -241,7 +246,7 @@ mtr.global_suppressions                            OK
 mtr.test_suppressions                              OK
 performance_schema
 test
-Phase 6/6: Running 'FLUSH PRIVILEGES'
+Phase 7/7: Running 'FLUSH PRIVILEGES'
 OK
 CALL testproc();
 DROP PROCEDURE testproc;
@@ -255,7 +260,7 @@ WARNING: NULL values of the 'db_collation' column ('mysql.proc' table) have been
 GRANT USAGE ON *.* TO 'user3'@'%';
 GRANT ALL PRIVILEGES ON `roelt`.`test2` TO 'user3'@'%';
 Run mysql_upgrade with all privileges on a user
-Phase 1/6: Checking and upgrading mysql database
+Phase 1/7: Checking and upgrading mysql database
 Processing databases
 mysql
 mysql.column_stats                                 OK
@@ -286,10 +291,11 @@ mysql.time_zone_name                               OK
 mysql.time_zone_transition                         OK
 mysql.time_zone_transition_type                    OK
 mysql.user                                         OK
-Phase 2/6: Fixing views
-Phase 3/6: Running 'mysql_fix_privilege_tables'
-Phase 4/6: Fixing table and database names
-Phase 5/6: Checking and upgrading tables
+Phase 2/7: Installing used storage engines... Skipped
+Phase 3/7: Fixing views
+Phase 4/7: Running 'mysql_fix_privilege_tables'
+Phase 5/7: Fixing table and database names
+Phase 6/7: Checking and upgrading tables
 Processing databases
 information_schema
 mtr
@@ -297,7 +303,7 @@ mtr.global_suppressions                            OK
 mtr.test_suppressions                              OK
 performance_schema
 test
-Phase 6/6: Running 'FLUSH PRIVILEGES'
+Phase 7/7: Running 'FLUSH PRIVILEGES'
 OK
 SHOW GRANTS FOR 'user3'@'%';
 Grants for user3@%
@@ -306,7 +312,7 @@ GRANT ALL PRIVILEGES ON `roelt`.`test2` TO 'user3'@'%'
 DROP USER 'user3'@'%';
 End of 5.1 tests
 The --upgrade-system-tables option was used, user tables won't be touched.
-Phase 1/6: Checking and upgrading mysql database
+Phase 1/7: Checking and upgrading mysql database
 Processing databases
 mysql
 mysql.column_stats                                 OK
@@ -337,11 +343,12 @@ mysql.time_zone_name                               OK
 mysql.time_zone_transition                         OK
 mysql.time_zone_transition_type                    OK
 mysql.user                                         OK
-Phase 2/6: Fixing views... Skipped
-Phase 3/6: Running 'mysql_fix_privilege_tables'
-Phase 4/6: Fixing table and database names ... Skipped
-Phase 5/6: Checking and upgrading tables... Skipped
-Phase 6/6: Running 'FLUSH PRIVILEGES'
+Phase 2/7: Installing used storage engines... Skipped
+Phase 3/7: Fixing views... Skipped
+Phase 4/7: Running 'mysql_fix_privilege_tables'
+Phase 5/7: Fixing table and database names ... Skipped
+Phase 6/7: Checking and upgrading tables... Skipped
+Phase 7/7: Running 'FLUSH PRIVILEGES'
 OK
 #
 # Bug#11827359 60223: MYSQL_UPGRADE PROBLEM WITH OPTION
@@ -349,7 +356,7 @@ OK
 #
 # Droping the previously created mysql_upgrade_info file..
 # Running mysql_upgrade with --skip-write-binlog..
-Phase 1/6: Checking and upgrading mysql database
+Phase 1/7: Checking and upgrading mysql database
 Processing databases
 mysql
 mysql.column_stats                                 OK
@@ -380,10 +387,11 @@ mysql.time_zone_name                               OK
 mysql.time_zone_transition                         OK
 mysql.time_zone_transition_type                    OK
 mysql.user                                         OK
-Phase 2/6: Fixing views
-Phase 3/6: Running 'mysql_fix_privilege_tables'
-Phase 4/6: Fixing table and database names
-Phase 5/6: Checking and upgrading tables
+Phase 2/7: Installing used storage engines... Skipped
+Phase 3/7: Fixing views
+Phase 4/7: Running 'mysql_fix_privilege_tables'
+Phase 5/7: Fixing table and database names
+Phase 6/7: Checking and upgrading tables
 Processing databases
 information_schema
 mtr
@@ -391,7 +399,7 @@ mtr.global_suppressions                            OK
 mtr.test_suppressions                              OK
 performance_schema
 test
-Phase 6/6: Running 'FLUSH PRIVILEGES'
+Phase 7/7: Running 'FLUSH PRIVILEGES'
 OK
 #
 # Bug #21489398: MYSQL_UPGRADE: FATAL ERROR: UPGRADE FAILED - IMPROVE ERROR
@@ -412,7 +420,7 @@ GRANT INSERT ON mysql.user TO very_long_user_name_number_2;
 GRANT UPDATE (User) ON mysql.db TO very_long_user_name_number_1;
 GRANT UPDATE (User) ON mysql.db TO very_long_user_name_number_2;
 CREATE PROCEDURE test.pr() BEGIN END;
-Phase 1/6: Checking and upgrading mysql database
+Phase 1/7: Checking and upgrading mysql database
 Processing databases
 mysql
 mysql.column_stats                                 OK
@@ -443,10 +451,11 @@ mysql.time_zone_name                               OK
 mysql.time_zone_transition                         OK
 mysql.time_zone_transition_type                    OK
 mysql.user                                         OK
-Phase 2/6: Fixing views
-Phase 3/6: Running 'mysql_fix_privilege_tables'
-Phase 4/6: Fixing table and database names
-Phase 5/6: Checking and upgrading tables
+Phase 2/7: Installing used storage engines... Skipped
+Phase 3/7: Fixing views
+Phase 4/7: Running 'mysql_fix_privilege_tables'
+Phase 5/7: Fixing table and database names
+Phase 6/7: Checking and upgrading tables
 Processing databases
 information_schema
 mtr
@@ -454,7 +463,7 @@ mtr.global_suppressions                            OK
 mtr.test_suppressions                              OK
 performance_schema
 test
-Phase 6/6: Running 'FLUSH PRIVILEGES'
+Phase 7/7: Running 'FLUSH PRIVILEGES'
 OK
 SELECT definer FROM mysql.proc WHERE db = 'test' AND name = 'pr';
 definer
@@ -470,7 +479,7 @@ set sql_mode=default;
 create table test.t1(a int) engine=MyISAM;
 # Trying to enforce InnoDB for all tables
 SET GLOBAL enforce_storage_engine=InnoDB;
-Phase 1/6: Checking and upgrading mysql database
+Phase 1/7: Checking and upgrading mysql database
 Processing databases
 mysql
 mysql.column_stats                                 OK
@@ -501,10 +510,11 @@ mysql.time_zone_name                               OK
 mysql.time_zone_transition                         OK
 mysql.time_zone_transition_type                    OK
 mysql.user                                         OK
-Phase 2/6: Fixing views
-Phase 3/6: Running 'mysql_fix_privilege_tables'
-Phase 4/6: Fixing table and database names
-Phase 5/6: Checking and upgrading tables
+Phase 2/7: Installing used storage engines... Skipped
+Phase 3/7: Fixing views
+Phase 4/7: Running 'mysql_fix_privilege_tables'
+Phase 5/7: Fixing table and database names
+Phase 6/7: Checking and upgrading tables
 Processing databases
 information_schema
 mtr
@@ -513,7 +523,7 @@ mtr.test_suppressions                              OK
 performance_schema
 test
 test.t1                                            OK
-Phase 6/6: Running 'FLUSH PRIVILEGES'
+Phase 7/7: Running 'FLUSH PRIVILEGES'
 OK
 # Should return 2
 SELECT count(*) FROM information_schema.tables where ENGINE="InnoDB";
diff --git a/mysql-test/r/mysql_upgrade_no_innodb.result b/mysql-test/r/mysql_upgrade_no_innodb.result
index acbca131587..6ad818278f8 100644
--- a/mysql-test/r/mysql_upgrade_no_innodb.result
+++ b/mysql-test/r/mysql_upgrade_no_innodb.result
@@ -1,5 +1,5 @@
 The --upgrade-system-tables option was used, user tables won't be touched.
-Phase 1/6: Checking and upgrading mysql database
+Phase 1/7: Checking and upgrading mysql database
 Processing databases
 mysql
 mysql.column_stats                                 OK
@@ -42,9 +42,10 @@ error    : Corrupt
 mysql.innodb_table_stats
 Error    : Unknown storage engine 'InnoDB'
 error    : Corrupt
-Phase 2/6: Fixing views... Skipped
-Phase 3/6: Running 'mysql_fix_privilege_tables'
-Phase 4/6: Fixing table and database names ... Skipped
-Phase 5/6: Checking and upgrading tables... Skipped
-Phase 6/6: Running 'FLUSH PRIVILEGES'
+Phase 2/7: Installing used storage engines... Skipped
+Phase 3/7: Fixing views... Skipped
+Phase 4/7: Running 'mysql_fix_privilege_tables'
+Phase 5/7: Fixing table and database names ... Skipped
+Phase 6/7: Checking and upgrading tables... Skipped
+Phase 7/7: Running 'FLUSH PRIVILEGES'
 OK
diff --git a/mysql-test/r/mysql_upgrade_noengine.result b/mysql-test/r/mysql_upgrade_noengine.result
new file mode 100644
index 00000000000..09e705abb69
--- /dev/null
+++ b/mysql-test/r/mysql_upgrade_noengine.result
@@ -0,0 +1,297 @@
+install soname 'ha_blackhole';
+install soname 'ha_archive';
+create table t1 (a int) engine=blackhole;
+create table t2 (a int) engine=archive;
+select table_catalog, table_schema, table_name, table_type, engine, row_format, table_rows, data_length, table_comment from information_schema.tables where table_schema='test';
+table_catalog	def
+table_schema	test
+table_name	t1
+table_type	BASE TABLE
+engine	BLACKHOLE
+row_format	Fixed
+table_rows	0
+data_length	0
+table_comment	
+table_catalog	def
+table_schema	test
+table_name	t2
+table_type	BASE TABLE
+engine	ARCHIVE
+row_format	Compressed
+table_rows	0
+data_length	521
+table_comment	
+flush tables;
+uninstall plugin blackhole;
+uninstall plugin archive;
+select table_catalog, table_schema, table_name, table_type, engine, row_format, table_rows, data_length, table_comment from information_schema.tables where table_schema='test';
+table_catalog	def
+table_schema	test
+table_name	t1
+table_type	BASE TABLE
+engine	BLACKHOLE
+row_format	NULL
+table_rows	NULL
+data_length	NULL
+table_comment	Unknown storage engine 'BLACKHOLE'
+table_catalog	def
+table_schema	test
+table_name	t2
+table_type	BASE TABLE
+engine	ARCHIVE
+row_format	NULL
+table_rows	NULL
+data_length	NULL
+table_comment	Unknown storage engine 'ARCHIVE'
+Warnings:
+Level	Warning
+Code	1286
+Message	Unknown storage engine 'BLACKHOLE'
+Level	Warning
+Code	1286
+Message	Unknown storage engine 'ARCHIVE'
+Phase 1/7: Checking and upgrading mysql database
+Processing databases
+mysql
+mysql.column_stats                                 OK
+mysql.columns_priv                                 OK
+mysql.db                                           OK
+mysql.event                                        OK
+mysql.func                                         OK
+mysql.gtid_slave_pos                               OK
+mysql.help_category                                OK
+mysql.help_keyword                                 OK
+mysql.help_relation                                OK
+mysql.help_topic                                   OK
+mysql.host                                         OK
+mysql.index_stats                                  OK
+mysql.innodb_index_stats                           OK
+mysql.innodb_table_stats                           OK
+mysql.plugin                                       OK
+mysql.proc                                         OK
+mysql.procs_priv                                   OK
+mysql.proxies_priv                                 OK
+mysql.roles_mapping                                OK
+mysql.servers                                      OK
+mysql.table_stats                                  OK
+mysql.tables_priv                                  OK
+mysql.time_zone                                    OK
+mysql.time_zone_leap_second                        OK
+mysql.time_zone_name                               OK
+mysql.time_zone_transition                         OK
+mysql.time_zone_transition_type                    OK
+mysql.user                                         OK
+Phase 2/7: Installing used storage engines... Skipped
+Phase 3/7: Fixing views
+Phase 4/7: Running 'mysql_fix_privilege_tables'
+Phase 5/7: Fixing table and database names
+Phase 6/7: Checking and upgrading tables
+Processing databases
+information_schema
+mtr
+mtr.global_suppressions                            OK
+mtr.test_suppressions                              OK
+performance_schema
+test
+test.t1
+Error    : Unknown storage engine 'BLACKHOLE'
+error    : Corrupt
+test.t2
+Error    : Unknown storage engine 'ARCHIVE'
+error    : Corrupt
+
+Repairing tables
+test.t1
+Error    : Unknown storage engine 'BLACKHOLE'
+error    : Corrupt
+test.t2
+Error    : Unknown storage engine 'ARCHIVE'
+error    : Corrupt
+Phase 7/7: Running 'FLUSH PRIVILEGES'
+OK
+select table_catalog, table_schema, table_name, table_type, engine, row_format, table_rows, data_length, table_comment from information_schema.tables where table_schema='test';
+table_catalog	def
+table_schema	test
+table_name	t1
+table_type	BASE TABLE
+engine	BLACKHOLE
+row_format	NULL
+table_rows	NULL
+data_length	NULL
+table_comment	Unknown storage engine 'BLACKHOLE'
+table_catalog	def
+table_schema	test
+table_name	t2
+table_type	BASE TABLE
+engine	ARCHIVE
+row_format	NULL
+table_rows	NULL
+data_length	NULL
+table_comment	Unknown storage engine 'ARCHIVE'
+Warnings:
+Level	Warning
+Code	1286
+Message	Unknown storage engine 'BLACKHOLE'
+Level	Warning
+Code	1286
+Message	Unknown storage engine 'ARCHIVE'
+alter table mysql.user drop column default_role, drop column max_statement_time;
+Phase 1/7: Checking and upgrading mysql database
+Processing databases
+mysql
+mysql.column_stats                                 OK
+mysql.columns_priv                                 OK
+mysql.db                                           OK
+mysql.event                                        OK
+mysql.func                                         OK
+mysql.gtid_slave_pos                               OK
+mysql.help_category                                OK
+mysql.help_keyword                                 OK
+mysql.help_relation                                OK
+mysql.help_topic                                   OK
+mysql.host                                         OK
+mysql.index_stats                                  OK
+mysql.innodb_index_stats                           OK
+mysql.innodb_table_stats                           OK
+mysql.plugin                                       OK
+mysql.proc                                         OK
+mysql.procs_priv                                   OK
+mysql.proxies_priv                                 OK
+mysql.roles_mapping                                OK
+mysql.servers                                      OK
+mysql.table_stats                                  OK
+mysql.tables_priv                                  OK
+mysql.time_zone                                    OK
+mysql.time_zone_leap_second                        OK
+mysql.time_zone_name                               OK
+mysql.time_zone_transition                         OK
+mysql.time_zone_transition_type                    OK
+mysql.user                                         OK
+Phase 2/7: Installing used storage engines... Skipped
+Phase 3/7: Fixing views
+Phase 4/7: Running 'mysql_fix_privilege_tables'
+Phase 5/7: Fixing table and database names
+Phase 6/7: Checking and upgrading tables
+Processing databases
+information_schema
+mtr
+mtr.global_suppressions                            OK
+mtr.test_suppressions                              OK
+performance_schema
+test
+test.t1
+Error    : Unknown storage engine 'BLACKHOLE'
+error    : Corrupt
+test.t2
+Error    : Unknown storage engine 'ARCHIVE'
+error    : Corrupt
+
+Repairing tables
+test.t1
+Error    : Unknown storage engine 'BLACKHOLE'
+error    : Corrupt
+test.t2
+Error    : Unknown storage engine 'ARCHIVE'
+error    : Corrupt
+Phase 7/7: Running 'FLUSH PRIVILEGES'
+OK
+select table_catalog, table_schema, table_name, table_type, engine, row_format, table_rows, data_length, table_comment from information_schema.tables where table_schema='test';
+table_catalog	def
+table_schema	test
+table_name	t1
+table_type	BASE TABLE
+engine	BLACKHOLE
+row_format	NULL
+table_rows	NULL
+data_length	NULL
+table_comment	Unknown storage engine 'BLACKHOLE'
+table_catalog	def
+table_schema	test
+table_name	t2
+table_type	BASE TABLE
+engine	ARCHIVE
+row_format	NULL
+table_rows	NULL
+data_length	NULL
+table_comment	Unknown storage engine 'ARCHIVE'
+Warnings:
+Level	Warning
+Code	1286
+Message	Unknown storage engine 'BLACKHOLE'
+Level	Warning
+Code	1286
+Message	Unknown storage engine 'ARCHIVE'
+alter table mysql.user drop column default_role, drop column max_statement_time;
+Phase 1/7: Checking and upgrading mysql database
+Processing databases
+mysql
+mysql.column_stats                                 OK
+mysql.columns_priv                                 OK
+mysql.db                                           OK
+mysql.event                                        OK
+mysql.func                                         OK
+mysql.gtid_slave_pos                               OK
+mysql.help_category                                OK
+mysql.help_keyword                                 OK
+mysql.help_relation                                OK
+mysql.help_topic                                   OK
+mysql.host                                         OK
+mysql.index_stats                                  OK
+mysql.innodb_index_stats                           OK
+mysql.innodb_table_stats                           OK
+mysql.plugin                                       OK
+mysql.proc                                         OK
+mysql.procs_priv                                   OK
+mysql.proxies_priv                                 OK
+mysql.roles_mapping                                OK
+mysql.servers                                      OK
+mysql.table_stats                                  OK
+mysql.tables_priv                                  OK
+mysql.time_zone                                    OK
+mysql.time_zone_leap_second                        OK
+mysql.time_zone_name                               OK
+mysql.time_zone_transition                         OK
+mysql.time_zone_transition_type                    OK
+mysql.user                                         OK
+Upgrading from a version before MariaDB-10.1
+Phase 2/7: Installing used storage engines
+Checking for tables with unknown storage engine
+installing plugin for 'blackhole' storage engine
+installing plugin for 'archive' storage engine
+Phase 3/7: Fixing views
+Phase 4/7: Running 'mysql_fix_privilege_tables'
+Phase 5/7: Fixing table and database names
+Phase 6/7: Checking and upgrading tables
+Processing databases
+information_schema
+mtr
+mtr.global_suppressions                            OK
+mtr.test_suppressions                              OK
+performance_schema
+test
+test.t1                                            OK
+test.t2                                            OK
+Phase 7/7: Running 'FLUSH PRIVILEGES'
+OK
+select table_catalog, table_schema, table_name, table_type, engine, row_format, table_rows, data_length, table_comment from information_schema.tables where table_schema='test';
+table_catalog	def
+table_schema	test
+table_name	t1
+table_type	BASE TABLE
+engine	BLACKHOLE
+row_format	Fixed
+table_rows	0
+data_length	0
+table_comment	
+table_catalog	def
+table_schema	test
+table_name	t2
+table_type	BASE TABLE
+engine	ARCHIVE
+row_format	Compressed
+table_rows	0
+data_length	521
+table_comment	
+drop table t1, t2;
+uninstall plugin blackhole;
+uninstall plugin archive;
diff --git a/mysql-test/r/mysql_upgrade_ssl.result b/mysql-test/r/mysql_upgrade_ssl.result
index e06d1bb1671..918a24ffc71 100644
--- a/mysql-test/r/mysql_upgrade_ssl.result
+++ b/mysql-test/r/mysql_upgrade_ssl.result
@@ -1,7 +1,7 @@
 #
 # Bug#55672 mysql_upgrade dies with internal error 
 #
-Phase 1/6: Checking and upgrading mysql database
+Phase 1/7: Checking and upgrading mysql database
 Processing databases
 mysql
 mysql.column_stats                                 OK
@@ -32,10 +32,11 @@ mysql.time_zone_name                               OK
 mysql.time_zone_transition                         OK
 mysql.time_zone_transition_type                    OK
 mysql.user                                         OK
-Phase 2/6: Fixing views
-Phase 3/6: Running 'mysql_fix_privilege_tables'
-Phase 4/6: Fixing table and database names
-Phase 5/6: Checking and upgrading tables
+Phase 2/7: Installing used storage engines... Skipped
+Phase 3/7: Fixing views
+Phase 4/7: Running 'mysql_fix_privilege_tables'
+Phase 5/7: Fixing table and database names
+Phase 6/7: Checking and upgrading tables
 Processing databases
 information_schema
 mtr
@@ -43,5 +44,5 @@ mtr.global_suppressions                            OK
 mtr.test_suppressions                              OK
 performance_schema
 test
-Phase 6/6: Running 'FLUSH PRIVILEGES'
+Phase 7/7: Running 'FLUSH PRIVILEGES'
 OK
diff --git a/mysql-test/r/mysql_upgrade_view.result b/mysql-test/r/mysql_upgrade_view.result
index f43f42f97fd..dc31592566a 100644
--- a/mysql-test/r/mysql_upgrade_view.result
+++ b/mysql-test/r/mysql_upgrade_view.result
@@ -63,7 +63,7 @@ test.v2	check	error	Upgrade required. Please do "REPAIR VIEW `v2`" or dump/reloa
 check view v3 for upgrade;
 Table	Op	Msg_type	Msg_text
 test.v3	check	error	Upgrade required. Please do "REPAIR VIEW `v3`" or dump/reload to fix it!
-Phase 1/6: Checking and upgrading mysql database
+Phase 1/7: Checking and upgrading mysql database
 Processing databases
 mysql
 mysql.column_stats                                 OK
@@ -106,14 +106,15 @@ error    : Corrupt
 mysql.innodb_table_stats
 Error    : Unknown storage engine 'InnoDB'
 error    : Corrupt
-Phase 2/6: Fixing views
+Phase 2/7: Installing used storage engines... Skipped
+Phase 3/7: Fixing views
 test.v1                                            OK
 test.v1badcheck                                    OK
 test.v2                                            OK
 test.v3                                            OK
-Phase 3/6: Running 'mysql_fix_privilege_tables'
-Phase 4/6: Fixing table and database names
-Phase 5/6: Checking and upgrading tables
+Phase 4/7: Running 'mysql_fix_privilege_tables'
+Phase 5/7: Fixing table and database names
+Phase 6/7: Checking and upgrading tables
 Processing databases
 information_schema
 mtr
@@ -123,7 +124,7 @@ performance_schema
 test
 test.kv                                            OK
 test.t1                                            OK
-Phase 6/6: Running 'FLUSH PRIVILEGES'
+Phase 7/7: Running 'FLUSH PRIVILEGES'
 OK
 show create view v1;
 View	Create View	character_set_client	collation_connection
@@ -205,7 +206,7 @@ show create view v4;
 View	Create View	character_set_client	collation_connection
 v4	CREATE ALGORITHM=TEMPTABLE DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v4` AS select `t1`.`a` AS `a` from `t1`	latin1	latin1_swedish_ci
 MySQL upgrade detected
-Phase 1/6: Checking and upgrading mysql database
+Phase 1/7: Checking and upgrading mysql database
 Processing databases
 mysql
 mysql.column_stats                                 OK
@@ -249,14 +250,15 @@ error    : Corrupt
 mysql.innodb_table_stats
 Error    : Unknown storage engine 'InnoDB'
 error    : Corrupt
-Phase 2/6: Fixing views from mysql
+Phase 2/7: Installing used storage engines... Skipped
+Phase 3/7: Fixing views from mysql
 test.v1                                            OK
 test.v2                                            OK
 test.v3                                            OK
 test.v4                                            OK
-Phase 3/6: Running 'mysql_fix_privilege_tables'
-Phase 4/6: Fixing table and database names
-Phase 5/6: Checking and upgrading tables
+Phase 4/7: Running 'mysql_fix_privilege_tables'
+Phase 5/7: Fixing table and database names
+Phase 6/7: Checking and upgrading tables
 Processing databases
 information_schema
 mtr
@@ -266,7 +268,7 @@ performance_schema
 test
 test.kv                                            OK
 test.t1                                            OK
-Phase 6/6: Running 'FLUSH PRIVILEGES'
+Phase 7/7: Running 'FLUSH PRIVILEGES'
 OK
 flush tables;
 show create view v1;
@@ -323,7 +325,7 @@ rename table mysql.event to mysql.ev_bk;
 flush tables;
 The --upgrade-system-tables option was used, user tables won't be touched.
 MySQL upgrade detected
-Phase 1/6: Checking and upgrading mysql database
+Phase 1/7: Checking and upgrading mysql database
 Processing databases
 mysql
 mysql.column_stats                                 OK
@@ -367,14 +369,15 @@ error    : Corrupt
 mysql.innodb_table_stats
 Error    : Unknown storage engine 'InnoDB'
 error    : Corrupt
-Phase 2/6: Fixing views from mysql
+Phase 2/7: Installing used storage engines... Skipped
+Phase 3/7: Fixing views from mysql
 test.v1                                            OK
 test.v2                                            OK
 test.v3                                            OK
-Phase 3/6: Running 'mysql_fix_privilege_tables'
-Phase 4/6: Fixing table and database names ... Skipped
-Phase 5/6: Checking and upgrading tables... Skipped
-Phase 6/6: Running 'FLUSH PRIVILEGES'
+Phase 4/7: Running 'mysql_fix_privilege_tables'
+Phase 5/7: Fixing table and database names ... Skipped
+Phase 6/7: Checking and upgrading tables... Skipped
+Phase 7/7: Running 'FLUSH PRIVILEGES'
 OK
 drop table mysql.event;
 rename table mysql.ev_bk to mysql.event;
-- 
cgit v1.2.1


From d6a7aece0826e0c115eb21912527c77596c1305e Mon Sep 17 00:00:00 2001
From: Sergei Golubchik <serg@mariadb.org>
Date: Mon, 6 Mar 2017 13:06:03 +0100
Subject: my_sha2 service

---
 mysql-test/r/handlersocket.result | 2 +-
 mysql-test/r/plugin.result        | 6 +++---
 2 files changed, 4 insertions(+), 4 deletions(-)

(limited to 'mysql-test/r')

diff --git a/mysql-test/r/handlersocket.result b/mysql-test/r/handlersocket.result
index e38de6bf5c2..26c77813b26 100644
--- a/mysql-test/r/handlersocket.result
+++ b/mysql-test/r/handlersocket.result
@@ -5,7 +5,7 @@ plugin_version	1.0
 plugin_status	ACTIVE
 plugin_type	DAEMON
 plugin_library	handlersocket.so
-plugin_library_version	1.11
+plugin_library_version	1.12
 plugin_author	higuchi dot akira at dena dot jp
 plugin_description	Direct access into InnoDB
 plugin_license	BSD
diff --git a/mysql-test/r/plugin.result b/mysql-test/r/plugin.result
index c23c4f2d8a2..f278724cc9a 100644
--- a/mysql-test/r/plugin.result
+++ b/mysql-test/r/plugin.result
@@ -12,7 +12,7 @@ PLUGIN_STATUS	ACTIVE
 PLUGIN_TYPE	STORAGE ENGINE
 PLUGIN_TYPE_VERSION	#
 PLUGIN_LIBRARY	ha_example.so
-PLUGIN_LIBRARY_VERSION	1.11
+PLUGIN_LIBRARY_VERSION	1.12
 PLUGIN_AUTHOR	Brian Aker, MySQL AB
 PLUGIN_DESCRIPTION	Example storage engine
 PLUGIN_LICENSE	GPL
@@ -25,7 +25,7 @@ PLUGIN_STATUS	ACTIVE
 PLUGIN_TYPE	DAEMON
 PLUGIN_TYPE_VERSION	#
 PLUGIN_LIBRARY	ha_example.so
-PLUGIN_LIBRARY_VERSION	1.11
+PLUGIN_LIBRARY_VERSION	1.12
 PLUGIN_AUTHOR	Sergei Golubchik
 PLUGIN_DESCRIPTION	Unusable Daemon
 PLUGIN_LICENSE	GPL
@@ -64,7 +64,7 @@ PLUGIN_STATUS	DELETED
 PLUGIN_TYPE	STORAGE ENGINE
 PLUGIN_TYPE_VERSION	#
 PLUGIN_LIBRARY	ha_example.so
-PLUGIN_LIBRARY_VERSION	1.11
+PLUGIN_LIBRARY_VERSION	1.12
 PLUGIN_AUTHOR	Brian Aker, MySQL AB
 PLUGIN_DESCRIPTION	Example storage engine
 PLUGIN_LICENSE	GPL
-- 
cgit v1.2.1


From 20338443197590beb943e8e1eed17cf95d2a51e0 Mon Sep 17 00:00:00 2001
From: Sergei Golubchik <serg@mariadb.org>
Date: Sat, 11 Mar 2017 12:42:55 +0100
Subject: test failures in buildbot

---
 mysql-test/r/repair_symlink-5543.result | 4 ++--
 1 file changed, 2 insertions(+), 2 deletions(-)

(limited to 'mysql-test/r')

diff --git a/mysql-test/r/repair_symlink-5543.result b/mysql-test/r/repair_symlink-5543.result
index f46e5833468..c77e7162a51 100644
--- a/mysql-test/r/repair_symlink-5543.result
+++ b/mysql-test/r/repair_symlink-5543.result
@@ -12,7 +12,7 @@ create table t2 (a int) engine=aria data directory='MYSQL_TMP_DIR';
 insert t2 values (1);
 repair table t2;
 Table	Op	Msg_type	Msg_text
-test.t2	repair	error	40 for record at pos 256
-test.t2	repair	Error	File 'MYSQL_TMP_DIR/t2.MAD' not found (Errcode: 40 "Too many levels of symbolic links")
+test.t2	repair	error	20 for record at pos 256
+test.t2	repair	Error	File 'MYSQL_TMP_DIR/t2.MAD' not found (Errcode: 20 "<errmsg>")
 test.t2	repair	status	Operation failed
 drop table t2;
-- 
cgit v1.2.1


From 06f1f1aa6e3dbc1efcd623378fe415b27c32b944 Mon Sep 17 00:00:00 2001
From: =?UTF-8?q?Vicen=C8=9Biu=20Ciorbaru?= <vicentiu@mariadb.org>
Date: Tue, 14 Mar 2017 00:24:06 +0200
Subject: Make ELOOP be considered a File Not Found error when it comes from
 handlerton

Fix symlink-aria && symlink-myisam to account for this possibility.
---
 mysql-test/r/symlink-aria-11902.result   | 4 ++--
 mysql-test/r/symlink-myisam-11902.result | 4 ++--
 2 files changed, 4 insertions(+), 4 deletions(-)

(limited to 'mysql-test/r')

diff --git a/mysql-test/r/symlink-aria-11902.result b/mysql-test/r/symlink-aria-11902.result
index 66405b1c25f..e563780b3ec 100644
--- a/mysql-test/r/symlink-aria-11902.result
+++ b/mysql-test/r/symlink-aria-11902.result
@@ -14,7 +14,7 @@ set debug_sync='mi_open_datafile SIGNAL ok WAIT_FOR go';
 select * from t1;
 set debug_sync='now WAIT_FOR ok';
 set debug_sync='now SIGNAL go';
-ERROR HY000: File 'MYSQLTEST_VARDIR/tmp/foo/t1.MAD' not found (Errcode: 20 "Not a directory")
+ERROR HY000: File 'MYSQLTEST_VARDIR/tmp/foo/t1.MAD' not found (Errcode: 20 <errmsg>)
 flush tables;
 drop table if exists t1;
 create table t1 (a int, b char(16), index (a))
@@ -32,7 +32,7 @@ set debug_sync='mi_open_kfile SIGNAL waiting WAIT_FOR run';
 select a from t1;
 set debug_sync='now WAIT_FOR waiting';
 set debug_sync='now SIGNAL run';
-ERROR HY000: Can't find file: './test/t1.MAI' (errno: 20 "Not a directory")
+ERROR HY000: Can't find file: './test/t1.MAI' (errno: 20 <errmsg>)
 flush tables;
 drop table if exists t1;
 drop table mysql.t1;
diff --git a/mysql-test/r/symlink-myisam-11902.result b/mysql-test/r/symlink-myisam-11902.result
index 4b07aa3f4a7..33357a80700 100644
--- a/mysql-test/r/symlink-myisam-11902.result
+++ b/mysql-test/r/symlink-myisam-11902.result
@@ -13,7 +13,7 @@ set debug_sync='mi_open_datafile SIGNAL ok WAIT_FOR go';
 select * from t1;
 set debug_sync='now WAIT_FOR ok';
 set debug_sync='now SIGNAL go';
-ERROR HY000: File 'MYSQLTEST_VARDIR/tmp/foo/t1.MYD' not found (Errcode: 20 "Not a directory")
+ERROR HY000: File 'MYSQLTEST_VARDIR/tmp/foo/t1.MYD' not found (Errcode: 20 <errmsg>)
 flush tables;
 drop table if exists t1;
 create table t1 (a int, b char(16), index (a))
@@ -31,7 +31,7 @@ set debug_sync='mi_open_kfile SIGNAL waiting WAIT_FOR run';
 select a from t1;
 set debug_sync='now WAIT_FOR waiting';
 set debug_sync='now SIGNAL run';
-ERROR HY000: Can't find file: './test/t1.MYI' (errno: 20 "Not a directory")
+ERROR HY000: Can't find file: './test/t1.MYI' (errno: 20 <errmsg>)
 flush tables;
 drop table if exists t1;
 drop table mysql.t1;
-- 
cgit v1.2.1


From adbe1c5fe986392e5a6159e4711b99d2275be81b Mon Sep 17 00:00:00 2001
From: Varun Gupta <varunraiko1803@gmail.com>
Date: Tue, 14 Mar 2017 17:31:29 +0530
Subject: MDEV-6486: Assertion `!table || (!table->read_set ||
 bitmap_is_set(table->read_set, field_index))' failed with SELECT SQ, TEXT
 field

The functon find_all_keys does call Item_subselect::walk, which calls walk() for the subquery
The issue is that when a field is represented by Item_outer_ref(Item_direct_ref(Item_copy_string( ...))).
Item_copy_string does have a pointer to an Item_field in Item_copy::item but does not implement Item::walk method, so we are not
able to set the bitmap for that field. This is the reason why the assert fails.

Fixed by adding the walk method to Item_copy class.
---
 mysql-test/r/subselect4.result | 13 +++++++++++++
 1 file changed, 13 insertions(+)

(limited to 'mysql-test/r')

diff --git a/mysql-test/r/subselect4.result b/mysql-test/r/subselect4.result
index 2a229675817..2228609b82c 100644
--- a/mysql-test/r/subselect4.result
+++ b/mysql-test/r/subselect4.result
@@ -2483,5 +2483,18 @@ select 1 from dual where null not in (select 1 from t2);
 1
 1
 drop table t1,t2;
+#
+# MDEV-6486: Assertion `!table || (!table->read_set || bitmap_is_set(table->read_set, field_index))'
+# failed with SELECT SQ, TEXT field
+#
+CREATE TABLE t1 (a VARCHAR(8), KEY(a)) ENGINE=MyISAM;
+INSERT INTO t1 VALUES ('foo'),( 'bar');
+CREATE TABLE t2 (b VARCHAR(8), c TINYTEXT, KEY(b)) ENGINE=MyISAM;
+INSERT INTO t2 VALUES ('baz','baz'),('qux', 'qux');
+SELECT ( SELECT COUNT(*) FROM t1 WHERE a = c ) AS field, COUNT(DISTINCT c)
+FROM t2 WHERE b <= 'quux' GROUP BY field;
+field	COUNT(DISTINCT c)
+0	1
+drop table t1,t2;
 SET optimizer_switch= @@global.optimizer_switch;
 set @@tmp_table_size= @@global.tmp_table_size;
-- 
cgit v1.2.1


From 3c422e60bbee79bb636e65910c26ac193de70a84 Mon Sep 17 00:00:00 2001
From: Jacob Mathew <jacob.mathew@mariadb.com>
Date: Tue, 28 Mar 2017 16:56:18 -0700
Subject: MDEV-11115 CHECK constraints are not shown in I_S.TABLE_CONSTRAINTS

Added CHECK constraints to I_S.TABLE_CONSTRAINTS.
Fixed a bug regarding virtual column definitions whose name is the field name.
Added test case: check_constraint_show
---
 mysql-test/r/check_constraint_show.result | 17 +++++++++++++++++
 1 file changed, 17 insertions(+)
 create mode 100644 mysql-test/r/check_constraint_show.result

(limited to 'mysql-test/r')

diff --git a/mysql-test/r/check_constraint_show.result b/mysql-test/r/check_constraint_show.result
new file mode 100644
index 00000000000..def1c488758
--- /dev/null
+++ b/mysql-test/r/check_constraint_show.result
@@ -0,0 +1,17 @@
+create or replace table t1( c1 int check( c1 > 0 ), c2 int check( c2 > 0 ), c3 int, constraint `range`  check( ( c3 >= c1 ) and ( c3 <= c2 ) ), primary key( c1 ) );
+show create table t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `c1` int(11) NOT NULL CHECK (`c1` > 0),
+  `c2` int(11) DEFAULT NULL CHECK (`c2` > 0),
+  `c3` int(11) DEFAULT NULL,
+  PRIMARY KEY (`c1`),
+  CONSTRAINT `range` CHECK (`c3` >= `c1` and `c3` <= `c2`)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+select * from information_schema.table_constraints where table_name = 't1';
+CONSTRAINT_CATALOG	CONSTRAINT_SCHEMA	CONSTRAINT_NAME	TABLE_SCHEMA	TABLE_NAME	CONSTRAINT_TYPE
+def	test	PRIMARY	test	t1	PRIMARY KEY
+def	test	c1	test	t1	CHECK
+def	test	c2	test	t1	CHECK
+def	test	range	test	t1	CHECK
+drop table t1;
-- 
cgit v1.2.1


From 2f3d4bd566b5e377fe8a1749c14050b0a0e083d0 Mon Sep 17 00:00:00 2001
From: Alexander Barkov <bar@mariadb.org>
Date: Fri, 31 Mar 2017 15:18:28 +0400
Subject: MDEV-12416 OOM in create_virtual_tmp_table() makes the server crash

---
 mysql-test/r/error_simulation.result | 9 +++++++++
 1 file changed, 9 insertions(+)

(limited to 'mysql-test/r')

diff --git a/mysql-test/r/error_simulation.result b/mysql-test/r/error_simulation.result
index edb97d4ab2e..e0ec26b2d1c 100644
--- a/mysql-test/r/error_simulation.result
+++ b/mysql-test/r/error_simulation.result
@@ -121,3 +121,12 @@ a
 2
 #cleanup
 DROP TABLE t1, pid_table;
+#
+# MDEV-12416 OOM in create_virtual_tmp_table() makes the server crash
+#
+CREATE FUNCTION f1(a INT) RETURNS INT RETURN a;
+SET SESSION debug_dbug="+d,simulate_create_virtual_tmp_table_out_of_memory";
+SELECT f1(1);
+Got one of the listed errors
+DROP FUNCTION f1;
+SET SESSION debug_dbug=DEFAULT;
-- 
cgit v1.2.1