merge 10.0-base -> 10.0

2 files changed, 202 insertions, 12 deletions

diff --git a/mysql-test/r/query_cache.result b/mysql-test/r/query_cache.result
index 18f9db1743d..118c6785136 100644
--- a/mysql-test/r/query_cache.result
+++ b/mysql-test/r/query_cache.result
@@ -1933,6 +1933,152 @@ select @@query_cache_size, @@global.query_cache_type, @@local.query_cache_type;
 @@query_cache_size	@@global.query_cache_type	@@local.query_cache_type
 20971520	ON	ON
 #
+# MDEV-4981: Account for queries handled by query-cache in
+# USER_STATISTICS (and in HOST_STATISTICS)
+#
+SET GLOBAL userstat=1;
+set GLOBAL query_cache_size=1355776;
+FLUSH USER_STATISTICS;
+FLUSH CLIENT_STATISTICS;
+reset query cache;
+flush status;
+create table t1 (a int);
+insert into t1 values (1);
+select * from t1;
+a
+1
+select SELECT_COMMANDS,ROWS_SENT,EMPTY_QUERIES from INFORMATION_SCHEMA.CLIENT_STATISTICS
+where CLIENT="localhost";
+SELECT_COMMANDS	ROWS_SENT	EMPTY_QUERIES
+1	1	0
+select SELECT_COMMANDS,ROWS_SENT,EMPTY_QUERIES from INFORMATION_SCHEMA.USER_STATISTICS
+where USER="root";
+SELECT_COMMANDS	ROWS_SENT	EMPTY_QUERIES
+2	2	0
+show status like "Qcache_hits";
+Variable_name	Value
+Qcache_hits	0
+select * from t1;
+a
+1
+select * from t1;
+a
+1
+select SELECT_COMMANDS,ROWS_SENT,EMPTY_QUERIES from INFORMATION_SCHEMA.CLIENT_STATISTICS
+where CLIENT="localhost";
+SELECT_COMMANDS	ROWS_SENT	EMPTY_QUERIES
+5	5	0
+select SELECT_COMMANDS,ROWS_SENT,EMPTY_QUERIES from INFORMATION_SCHEMA.USER_STATISTICS
+where USER="root";
+SELECT_COMMANDS	ROWS_SENT	EMPTY_QUERIES
+6	6	0
+show status like "Qcache_hits";
+Variable_name	Value
+Qcache_hits	2
+drop table t1;
+FLUSH USER_STATISTICS;
+FLUSH CLIENT_STATISTICS;
+flush status;
+create table t1 (a int);
+select * from t1;
+a
+select SELECT_COMMANDS,ROWS_SENT,EMPTY_QUERIES from INFORMATION_SCHEMA.CLIENT_STATISTICS
+where CLIENT="localhost";
+SELECT_COMMANDS	ROWS_SENT	EMPTY_QUERIES
+1	0	1
+select SELECT_COMMANDS,ROWS_SENT,EMPTY_QUERIES from INFORMATION_SCHEMA.USER_STATISTICS
+where USER="root";
+SELECT_COMMANDS	ROWS_SENT	EMPTY_QUERIES
+2	1	1
+show status like "Qcache_hits";
+Variable_name	Value
+Qcache_hits	0
+select * from t1;
+a
+select * from t1;
+a
+select SELECT_COMMANDS,ROWS_SENT,EMPTY_QUERIES from INFORMATION_SCHEMA.CLIENT_STATISTICS
+where CLIENT="localhost";
+SELECT_COMMANDS	ROWS_SENT	EMPTY_QUERIES
+5	2	3
+select SELECT_COMMANDS,ROWS_SENT,EMPTY_QUERIES from INFORMATION_SCHEMA.USER_STATISTICS
+where USER="root";
+SELECT_COMMANDS	ROWS_SENT	EMPTY_QUERIES
+6	3	3
+show status like "Qcache_hits";
+Variable_name	Value
+Qcache_hits	2
+FLUSH USER_STATISTICS;
+FLUSH CLIENT_STATISTICS;
+flush status;
+create procedure p1()
+select * from t1;
+call p1;
+a
+select SELECT_COMMANDS,ROWS_SENT,EMPTY_QUERIES from INFORMATION_SCHEMA.CLIENT_STATISTICS
+where CLIENT="localhost";
+SELECT_COMMANDS	ROWS_SENT	EMPTY_QUERIES
+1	0	1
+select SELECT_COMMANDS,ROWS_SENT,EMPTY_QUERIES from INFORMATION_SCHEMA.USER_STATISTICS
+where USER="root";
+SELECT_COMMANDS	ROWS_SENT	EMPTY_QUERIES
+2	1	1
+show status like "Qcache_hits";
+Variable_name	Value
+Qcache_hits	0
+call p1;
+a
+call p1;
+a
+select SELECT_COMMANDS,ROWS_SENT,EMPTY_QUERIES from INFORMATION_SCHEMA.CLIENT_STATISTICS
+where CLIENT="localhost";
+SELECT_COMMANDS	ROWS_SENT	EMPTY_QUERIES
+5	2	3
+select SELECT_COMMANDS,ROWS_SENT,EMPTY_QUERIES from INFORMATION_SCHEMA.USER_STATISTICS
+where USER="root";
+SELECT_COMMANDS	ROWS_SENT	EMPTY_QUERIES
+6	3	3
+show status like "Qcache_hits";
+Variable_name	Value
+Qcache_hits	2
+FLUSH USER_STATISTICS;
+FLUSH CLIENT_STATISTICS;
+flush status;
+SET GLOBAL query_cache_size= 0;
+call p1;
+a
+select SELECT_COMMANDS,ROWS_SENT,EMPTY_QUERIES from INFORMATION_SCHEMA.CLIENT_STATISTICS
+where CLIENT="localhost";
+SELECT_COMMANDS	ROWS_SENT	EMPTY_QUERIES
+1	0	1
+select SELECT_COMMANDS,ROWS_SENT,EMPTY_QUERIES from INFORMATION_SCHEMA.USER_STATISTICS
+where USER="root";
+SELECT_COMMANDS	ROWS_SENT	EMPTY_QUERIES
+2	1	1
+show status like "Qcache_hits";
+Variable_name	Value
+Qcache_hits	0
+call p1;
+a
+call p1;
+a
+select SELECT_COMMANDS,ROWS_SENT,EMPTY_QUERIES from INFORMATION_SCHEMA.CLIENT_STATISTICS
+where CLIENT="localhost";
+SELECT_COMMANDS	ROWS_SENT	EMPTY_QUERIES
+5	2	3
+select SELECT_COMMANDS,ROWS_SENT,EMPTY_QUERIES from INFORMATION_SCHEMA.USER_STATISTICS
+where USER="root";
+SELECT_COMMANDS	ROWS_SENT	EMPTY_QUERIES
+6	3	3
+show status like "Qcache_hits";
+Variable_name	Value
+Qcache_hits	0
+drop procedure p1;
+drop table t1;
+set GLOBAL query_cache_size=1355776;
+SET GLOBAL userstat=default;
+End of 5.5 tests
+#
 # Bug#12977203: SERVER CRASH ON A DERIVED TABLE WITH QUERY CACHE ENABLED
 #
 SET @qc= @@query_cache_size;
@@ -1944,8 +2090,6 @@ COUNT(*)
 DROP TABLE t1;
 SET GLOBAL query_cache_size= @qc;
 #
-End of 5.5 tests
-#
 # MDEV-617 LP:671189 - Query cache is not used for tables or
 # databases with dots in their names
 #
diff --git a/mysql-test/r/view.result b/mysql-test/r/view.result
index 3969d9d4a09..2200534b2a1 100644
--- a/mysql-test/r/view.result
+++ b/mysql-test/r/view.result
@@ -4873,6 +4873,52 @@ execute s;
 deallocate prepare s;
 drop view v1;
 drop tables t1,t2;
+#
+# MDEV-5034 (duplicate of MDEV-5107):
+# Left Join Yields All Nulls Instead of Appropriate Matches
+#
+# test #1
+CREATE TABLE t1 (state VARCHAR(32), INDEX(state));
+INSERT INTO t1 VALUES ('Indiana'),('Vermont');
+CREATE TABLE t2 (state VARCHAR(32));
+INSERT INTO t2 VALUES ('Hawaii'),('Oregon'),('Vermont');
+CREATE ALGORITHM=MERGE VIEW v1 AS SELECT t1.* FROM t2, t1;
+SELECT * FROM t1 AS outer_t1 LEFT JOIN v1 AS joined_t1
+ON (joined_t1.state = outer_t1.state AND joined_t1.state IN ( SELECT 'Vermont' UNION  SELECT 'Florida' ) );
+state	state
+Indiana	NULL
+Vermont	Vermont
+Vermont	Vermont
+Vermont	Vermont
+SELECT * FROM t1 AS outer_t1 LEFT JOIN (SELECT t1.* FROM t2, t1) AS joined_t1 ON (joined_t1.state = outer_t1.state AND joined_t1.state IN ( SELECT 'Vermont' UNION  SELECT 'Florida' ) );
+state	state
+Indiana	NULL
+Vermont	Vermont
+Vermont	Vermont
+Vermont	Vermont
+drop view v1;
+drop table t1, t2;
+# test #1
+CREATE TABLE t1 (a INT, b VARCHAR(1), INDEX(b,a));
+INSERT INTO t1 VALUES (4,'p'),(1,'q'),(9,'w');
+CREATE TABLE t2 (c VARCHAR(1), INDEX(c));
+INSERT INTO t2 VALUES ('q'),('a');
+CREATE ALGORITHM=MERGE VIEW v1 AS SELECT t1a.* FROM t1, t1 AS t1a;
+SELECT * FROM t2 LEFT JOIN v1 ON ( c=b AND a IN ( 1,6 ) );
+c	a	b
+a	NULL	NULL
+q	1	q
+q	1	q
+q	1	q
+CREATE OR REPLACE ALGORITHM=TEMPTABLE VIEW v1 AS SELECT t1a.* FROM t1, t1 AS t1a;
+SELECT * FROM t2 LEFT JOIN v1 ON ( c=b AND a IN ( 1,6 ) );
+c	a	b
+a	NULL	NULL
+q	1	q
+q	1	q
+q	1	q
+drop view v1;
+drop table t1,t2;
 # -----------------------------------------------------------------
 # -- End of 5.3 tests.
 # -----------------------------------------------------------------
@@ -4917,7 +4963,7 @@ create algorithm=merge view v1 as select t1.a as a, (select max(b) from t2 where
 explain extended
 select * from v1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	6	100.00	
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	6	100.00	
 3	DEPENDENT SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	6	100.00	Using where
 Warnings:
 Note	1276	Field or reference 'test.t1.a' of SELECT #3 was resolved in SELECT #2
@@ -4933,8 +4979,8 @@ a	c
 explain extended
 select * from t2, v1 where t2.a=v1.a;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	6	100.00	
-1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	6	100.00	Using where; Using join buffer (flat, BNL join)
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	6	100.00	
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	6	100.00	Using where; Using join buffer (flat, BNL join)
 3	DEPENDENT SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	6	100.00	Using where
 Warnings:
 Note	1276	Field or reference 'test.t1.a' of SELECT #3 was resolved in SELECT #2
@@ -4952,8 +4998,8 @@ a	b	a	c
 explain extended
 select * from t1, v1 where t1.a=v1.a;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	6	100.00	
-1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	6	100.00	Using where; Using join buffer (flat, BNL join)
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	6	100.00	
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	6	100.00	Using where; Using join buffer (flat, BNL join)
 3	DEPENDENT SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	6	100.00	Using where
 Warnings:
 Note	1276	Field or reference 'test.t1.a' of SELECT #3 was resolved in SELECT #2
@@ -4971,8 +5017,8 @@ a	b	a	c
 explain extended
 select * from t1, v1 where t1.b=v1.c;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	6	100.00	
-1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	6	100.00	Using where; Using join buffer (flat, BNL join)
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	6	100.00	
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	6	100.00	Using where; Using join buffer (flat, BNL join)
 3	DEPENDENT SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	6	100.00	Using where
 Warnings:
 Note	1276	Field or reference 'test.t1.a' of SELECT #3 was resolved in SELECT #2
@@ -4988,9 +5034,9 @@ a	b	a	c
 explain extended
 select * from t2, t1, v1 where t1.a=t2.a and t1.a=v1.a;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	6	100.00	
-1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	6	100.00	Using where; Using join buffer (flat, BNL join)
-1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	6	100.00	Using where; Using join buffer (incremental, BNL join)
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	6	100.00	
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	6	100.00	Using where; Using join buffer (flat, BNL join)
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	6	100.00	Using where; Using join buffer (incremental, BNL join)
 3	DEPENDENT SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	6	100.00	Using where
 Warnings:
 Note	1276	Field or reference 'test.t1.a' of SELECT #3 was resolved in SELECT #2