[Commits] 88a80e92dc4: MDEV-9959: A serious MariaDB server performance bug

Varun varunraiko1803 at gmail.com
Wed Apr 24 11:01:38 EEST 2019


revision-id: 88a80e92dc444ce30718f3e08d3ab66fb02bcea4 (mariadb-10.3.10-284-g88a80e92dc4)
parent(s): 3032cd8e91f1e1ead8b6f941e75cd29e473e7eaa
author: Varun Gupta
committer: Varun Gupta
timestamp: 2019-04-24 13:31:24 +0530
message:

MDEV-9959: A serious MariaDB server performance bug

Step #2: If any field in the select list of the derived tables is present in the group by list also , then we are again guaranteed
that ref access to the derived table would always produce one row per key.

---
 mysql-test/main/cte_nonrecursive.result            |   6 +-
 mysql-test/main/derived.result                     |  43 +++++++++
 mysql-test/main/derived.test                       |  26 +++++
 mysql-test/main/derived_cond_pushdown.result       | 106 ++++++++++-----------
 mysql-test/main/derived_opt.result                 |   2 +-
 mysql-test/main/derived_split_innodb.result        |   4 +-
 mysql-test/main/derived_view.result                |  34 +++----
 mysql-test/main/join_cache.result                  |   6 +-
 mysql-test/main/selectivity.result                 |   4 +-
 mysql-test/main/selectivity_innodb.result          |   4 +-
 mysql-test/main/subselect_extra.result             |   2 +-
 mysql-test/main/subselect_extra_no_semijoin.result |   2 +-
 sql/sql_lex.cc                                     |  62 ++++++++++++
 sql/sql_lex.h                                      |   1 +
 sql/table.cc                                       |   3 +
 15 files changed, 220 insertions(+), 85 deletions(-)

diff --git a/mysql-test/main/cte_nonrecursive.result b/mysql-test/main/cte_nonrecursive.result
index d80d34ecc7f..b04b0335289 100644
--- a/mysql-test/main/cte_nonrecursive.result
+++ b/mysql-test/main/cte_nonrecursive.result
@@ -85,14 +85,14 @@ with t as (select a, count(*) from t1 where b >= 'c' group by a)
 select * from t2,t where t2.c=t.a;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	4	Using where
-1	PRIMARY	<derived2>	ref	key0	key0	5	test.t2.c	2	
+1	PRIMARY	<derived2>	ref	key0	key0	5	test.t2.c	1	
 2	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	8	Using where; Using temporary; Using filesort
 explain
 select * from t2, (select a, count(*) from t1 where b >= 'c' group by a) as t 
 where t2.c=t.a;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	4	Using where
-1	PRIMARY	<derived2>	ref	key0	key0	5	test.t2.c	2	
+1	PRIMARY	<derived2>	ref	key0	key0	5	test.t2.c	1	
 2	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	8	Using where; Using temporary; Using filesort
 # specivication of t contains having
 with t as (select a, count(*) from t1 where b >= 'c'
@@ -597,7 +597,7 @@ explain
 select * from v2;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	4	Using where
-1	PRIMARY	<derived3>	ref	key0	key0	5	test.t2.c	2	
+1	PRIMARY	<derived3>	ref	key0	key0	5	test.t2.c	1	
 3	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	8	Using where; Using temporary; Using filesort
 # with clause in the specification of a view that whose definition
 # table alias for a with table
diff --git a/mysql-test/main/derived.result b/mysql-test/main/derived.result
index 857246d68b4..e19b80678be 100644
--- a/mysql-test/main/derived.result
+++ b/mysql-test/main/derived.result
@@ -1249,3 +1249,46 @@ a	a
 4	4
 6	6
 drop table t1,t2,t3;
+create table t1(a int, b int);
+insert into t1 values (1,1),(2,2),(3,3);
+create table t2(a int, b int,c int);
+insert into t2(a,b,c) values (7,2,2),(8,1,1),(1,2,5);
+create table t3(a int, b int);
+insert into t3(a,b) values (7,1),(8,2), (1,3);
+# expects rows=1 for derived table as group by fields are a prefix of the keyparts involved in ref access
+explain select * from t1 , ((select  a,b,c from t2 group by a))q  where t1.a=q.a;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	Using where
+1	PRIMARY	<derived2>	ref	key0	key0	5	test.t1.a	1	
+2	DERIVED	t2	ALL	NULL	NULL	NULL	NULL	3	Using temporary; Using filesort
+select * from t1 , ((select  a,b,c from t2 group by a))q  where t1.a=q.a;
+a	b	a	b	c
+1	1	1	2	5
+# expects rows=1 for derived table as group by fields are a prefix of the keyparts involved in ref access, this case
+# involves use of multiple equalities to check the prefix condition for rows=1
+explain select * from t1 , ((select  a,b from t2 where a=c group by c))q  where t1.a=q.a;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	Using where
+1	PRIMARY	<derived2>	ref	key0	key0	5	test.t1.a	1	
+2	DERIVED	t2	ALL	NULL	NULL	NULL	NULL	3	Using where; Using temporary; Using filesort
+select * from t1 , ((select  a,b from t2 where a=c group by c))q  where t1.a=q.a;
+a	b	a	b
+# rows should not be 1 for the derived table as group by is not a prefix of the keyparts for ref access
+explain select * from t1 , ((select  a,b from t2 where b=c group by c))q  where t1.a=q.a;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	Using where
+1	PRIMARY	<derived2>	ref	key0	key0	5	test.t1.a	2	
+2	DERIVED	t2	ALL	NULL	NULL	NULL	NULL	3	Using where; Using temporary; Using filesort
+select * from t1 , ((select  a,b from t2 where b=c group by c))q  where t1.a=q.a;
+a	b	a	b
+# rows should not be 1 for the derived table as group by is not a prefix of the keyparts for ref access
+explain select * from t1 , ((select  a,b,c from t2 group by a))q  where t1.a=q.b and t1.b=q.c;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	Using where
+1	PRIMARY	<derived2>	ref	key0	key0	10	test.t1.a,test.t1.b	2	
+2	DERIVED	t2	ALL	NULL	NULL	NULL	NULL	3	Using temporary; Using filesort
+select * from t1 , ((select  a,b,c from t2 group by a))q  where t1.a=q.b and t1.b=q.c;
+a	b	a	b	c
+1	1	8	1	1
+2	2	7	2	2
+drop table t1,t2,t3;
diff --git a/mysql-test/main/derived.test b/mysql-test/main/derived.test
index 990f955450a..494500a1c8a 100644
--- a/mysql-test/main/derived.test
+++ b/mysql-test/main/derived.test
@@ -1062,3 +1062,29 @@ analyze select * from t1 , ( (select t2.a from t2 order by c) union all (select
 select * from t1 , ( (select t2.a from t2 order by c) union all (select  t2.a from t2 order by c) except(select t3.a from t3 order by b))q  where t1.a=q.a;
 
 drop table t1,t2,t3;
+
+create table t1(a int, b int);
+insert into t1 values (1,1),(2,2),(3,3);
+create table t2(a int, b int,c int);
+insert into t2(a,b,c) values (7,2,2),(8,1,1),(1,2,5);
+create table t3(a int, b int);
+insert into t3(a,b) values (7,1),(8,2), (1,3);
+
+--echo # expects rows=1 for derived table as group by fields are a prefix of the keyparts involved in ref access
+explain select * from t1 , ((select  a,b,c from t2 group by a))q  where t1.a=q.a;
+select * from t1 , ((select  a,b,c from t2 group by a))q  where t1.a=q.a;
+
+--echo # expects rows=1 for derived table as group by fields are a prefix of the keyparts involved in ref access, this case
+--echo # involves use of multiple equalities to check the prefix condition for rows=1
+explain select * from t1 , ((select  a,b from t2 where a=c group by c))q  where t1.a=q.a; 
+select * from t1 , ((select  a,b from t2 where a=c group by c))q  where t1.a=q.a; 
+
+--echo # rows should not be 1 for the derived table as group by is not a prefix of the keyparts for ref access
+explain select * from t1 , ((select  a,b from t2 where b=c group by c))q  where t1.a=q.a;
+select * from t1 , ((select  a,b from t2 where b=c group by c))q  where t1.a=q.a;
+
+--echo # rows should not be 1 for the derived table as group by is not a prefix of the keyparts for ref access
+explain select * from t1 , ((select  a,b,c from t2 group by a))q  where t1.a=q.b and t1.b=q.c;
+select * from t1 , ((select  a,b,c from t2 group by a))q  where t1.a=q.b and t1.b=q.c;
+drop table t1,t2,t3;
+
diff --git a/mysql-test/main/derived_cond_pushdown.result b/mysql-test/main/derived_cond_pushdown.result
index 8086c4480f6..2f46e7eeeed 100644
--- a/mysql-test/main/derived_cond_pushdown.result
+++ b/mysql-test/main/derived_cond_pushdown.result
@@ -1551,7 +1551,7 @@ a	b	max_c	avg_c	a	b	c	d
 explain select * from v1,t2 where (v1.a=v1.b) and (v1.a=t2.a);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	9	Using where
-1	PRIMARY	<derived2>	ref	key0	key0	10	test.t2.a,test.t2.a	2	
+1	PRIMARY	<derived2>	ref	key0	key0	10	test.t2.a,test.t2.a	1	
 2	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	20	Using where; Using temporary; Using filesort
 explain format=json select * from v1,t2 where (v1.a=v1.b) and (v1.a=t2.a);
 EXPLAIN
@@ -1573,7 +1573,7 @@ EXPLAIN
       "key_length": "10",
       "used_key_parts": ["a", "b"],
       "ref": ["test.t2.a", "test.t2.a"],
-      "rows": 2,
+      "rows": 1,
       "filtered": 100,
       "materialized": {
         "query_block": {
@@ -1839,7 +1839,7 @@ explain select * from v_decimal as v,t2_decimal as t where
 (v.a=v.b) and (v.b=t.b) and ((t.b>1) or (v.a=1));
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t	ALL	NULL	NULL	NULL	NULL	9	Using where
-1	PRIMARY	<derived2>	ref	key0	key0	6	test.t.b,test.t.b	2	
+1	PRIMARY	<derived2>	ref	key0	key0	6	test.t.b,test.t.b	1	
 2	DERIVED	t1_decimal	ALL	NULL	NULL	NULL	NULL	9	Using where; Using temporary; Using filesort
 explain format=json select * from v_decimal as v,t2_decimal as t where
 (v.a=v.b) and (v.b=t.b) and ((t.b>1) or (v.a=1));
@@ -1862,7 +1862,7 @@ EXPLAIN
       "key_length": "6",
       "used_key_parts": ["a", "b"],
       "ref": ["test.t.b", "test.t.b"],
-      "rows": 2,
+      "rows": 1,
       "filtered": 100,
       "materialized": {
         "query_block": {
@@ -2157,7 +2157,7 @@ explain select * from v_double as v,t2_double as t where
 (v.b=v.c) and (v.c=t.c) and ((t.c>10) or (v.a=1));
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t	ALL	NULL	NULL	NULL	NULL	9	Using where
-1	PRIMARY	<derived2>	ref	key0	key0	18	test.t.c,test.t.c	2	Using where
+1	PRIMARY	<derived2>	ref	key0	key0	18	test.t.c,test.t.c	1	Using where
 2	DERIVED	t1_double	ALL	NULL	NULL	NULL	NULL	9	Using where; Using temporary; Using filesort
 explain format=json select * from v_double as v,t2_double as t where
 (v.b=v.c) and (v.c=t.c) and ((t.c>10) or (v.a=1));
@@ -2180,7 +2180,7 @@ EXPLAIN
       "key_length": "18",
       "used_key_parts": ["b", "c"],
       "ref": ["test.t.c", "test.t.c"],
-      "rows": 2,
+      "rows": 1,
       "filtered": 100,
       "attached_condition": "t.c > 10 or v.a = 1",
       "materialized": {
@@ -2380,7 +2380,7 @@ where t1.a>5 group by a,b having max_c < 707) v1,
 t2 where (v1.a=t2.a) and (v1.max_c>300) and (v1.a=v1.b);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	9	Using where
-1	PRIMARY	<derived2>	ref	key0	key0	10	test.t2.a,test.t2.a	2	Using where
+1	PRIMARY	<derived2>	ref	key0	key0	10	test.t2.a,test.t2.a	1	Using where
 2	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	20	Using where; Using temporary; Using filesort
 explain format=json select * from
 (select a, b, max(c) as max_c, avg(c) as avg_c from t1
@@ -2405,7 +2405,7 @@ EXPLAIN
       "key_length": "10",
       "used_key_parts": ["a", "b"],
       "ref": ["test.t2.a", "test.t2.a"],
-      "rows": 2,
+      "rows": 1,
       "filtered": 100,
       "attached_condition": "v1.max_c > 300",
       "materialized": {
@@ -2506,7 +2506,7 @@ a	b	max_c	avg_c	a	b	c	d
 explain select * from v1,t2 where (v1.a=t2.a) and (v1.b=t2.b);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	9	Using where
-1	PRIMARY	<derived2>	ref	key0	key0	10	test.t2.a,test.t2.b	2	
+1	PRIMARY	<derived2>	ref	key0	key0	10	test.t2.a,test.t2.b	1	
 2	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	20	Using temporary; Using filesort
 explain format=json select * from v1,t2 where (v1.a=t2.a) and (v1.b=t2.b);
 EXPLAIN
@@ -2528,7 +2528,7 @@ EXPLAIN
       "key_length": "10",
       "used_key_parts": ["a", "b"],
       "ref": ["test.t2.a", "test.t2.b"],
-      "rows": 2,
+      "rows": 1,
       "filtered": 100,
       "materialized": {
         "query_block": {
@@ -3204,7 +3204,7 @@ explain select * from v1,v2,t2 where
 (v1.a=t2.a) and (v1.a=v1.b) and (v1.a=v2.a) and (v2.max_c<300);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	9	Using where
-1	PRIMARY	<derived2>	ref	key1	key1	10	test.t2.a,test.t2.a	2	
+1	PRIMARY	<derived2>	ref	key1	key1	10	test.t2.a,test.t2.a	1	
 1	PRIMARY	<derived3>	ref	key0	key0	5	test.t2.a	2	Using where
 3	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	20	Using where; Using temporary; Using filesort
 2	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	20	Using where; Using temporary; Using filesort
@@ -3229,7 +3229,7 @@ EXPLAIN
       "key_length": "10",
       "used_key_parts": ["a", "b"],
       "ref": ["test.t2.a", "test.t2.a"],
-      "rows": 2,
+      "rows": 1,
       "filtered": 100,
       "materialized": {
         "query_block": {
@@ -7207,7 +7207,7 @@ SELECT d FROM v4 WHERE s > a
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	system	NULL	NULL	NULL	NULL	1	
 1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where; FirstMatch(t1)
-3	DEPENDENT SUBQUERY	<derived5>	index_subquery	key0	key0	5	func	2	Using where
+3	DEPENDENT SUBQUERY	<derived5>	index_subquery	key0	key0	5	func	1	Using where
 5	DERIVED	t4	ALL	NULL	NULL	NULL	NULL	5	Using temporary; Using filesort
 explain format=json SELECT * FROM t1 WHERE a IN (
 SELECT b FROM v2 WHERE b < a OR b IN (
@@ -7245,7 +7245,7 @@ EXPLAIN
             "key_length": "5",
             "used_key_parts": ["d"],
             "ref": ["func"],
-            "rows": 2,
+            "rows": 1,
             "filtered": 100,
             "materialized": {
               "query_block": {
@@ -8514,7 +8514,7 @@ EXPLAIN
       "key_length": "5",
       "used_key_parts": ["b"],
       "ref": ["test.t1.a"],
-      "rows": 2,
+      "rows": 1,
       "filtered": 100,
       "materialized": {
         "query_block": {
@@ -8564,7 +8564,7 @@ EXPLAIN
       "key_length": "5",
       "used_key_parts": ["b"],
       "ref": ["test.t1.a"],
-      "rows": 2,
+      "rows": 1,
       "filtered": 100,
       "materialized": {
         "query_block": {
@@ -8848,9 +8848,21 @@ EXPLAIN
   "query_block": {
     "select_id": 1,
     "table": {
-      "table_name": "<derived2>",
+      "table_name": "t1",
       "access_type": "ALL",
-      "rows": 3,
+      "rows": 4,
+      "filtered": 100,
+      "attached_condition": "t1.id2 is not null"
+    },
+    "table": {
+      "table_name": "<derived2>",
+      "access_type": "ref",
+      "possible_keys": ["key0"],
+      "key": "key0",
+      "key_length": "5",
+      "used_key_parts": ["id2"],
+      "ref": ["test.t1.id2"],
+      "rows": 1,
       "filtered": 100,
       "attached_condition": "vc.ct > 0",
       "materialized": {
@@ -8870,18 +8882,6 @@ EXPLAIN
           }
         }
       }
-    },
-    "block-nl-join": {
-      "table": {
-        "table_name": "t1",
-        "access_type": "ALL",
-        "rows": 4,
-        "filtered": 100
-      },
-      "buffer_type": "flat",
-      "buffer_size": "256Kb",
-      "join_type": "BNL",
-      "attached_condition": "t1.id2 = vc.id2"
     }
   }
 }
@@ -14986,7 +14986,7 @@ on t1.a=t.a
 where t1.b < 3;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t1	range	idx_b	idx_b	5	NULL	4	100.00	Using index condition; Using where
-1	PRIMARY	<derived2>	ref	key0	key0	5	test.t1.a	2	100.00	
+1	PRIMARY	<derived2>	ref	key0	key0	5	test.t1.a	1	100.00	
 2	LATERAL DERIVED	t2	ref	idx_a	idx_a	5	test.t1.a	2	100.00	
 Warnings:
 Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a`,`t`.`s` AS `s`,`t`.`m` AS `m` from `test`.`t1` join (/* select#2 */ select `test`.`t2`.`a` AS `a`,sum(`test`.`t2`.`b`) AS `s`,min(`test`.`t2`.`c`) AS `m` from `test`.`t2` where `test`.`t2`.`a` = `test`.`t1`.`a` group by `test`.`t2`.`a`) `t` where `t`.`a` = `test`.`t1`.`a` and `test`.`t1`.`b` < 3
@@ -15019,7 +15019,7 @@ EXPLAIN
       "key_length": "5",
       "used_key_parts": ["a"],
       "ref": ["test.t1.a"],
-      "rows": 2,
+      "rows": 1,
       "filtered": 100,
       "materialized": {
         "query_block": {
@@ -15096,7 +15096,7 @@ on t1.a=t.a
 where t1.b <= 5;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t1	ALL	idx_b	NULL	NULL	NULL	12	75.00	Using where
-1	PRIMARY	<derived2>	ref	key0	key0	5	test.t1.a	9	100.00	
+1	PRIMARY	<derived2>	ref	key0	key0	5	test.t1.a	1	100.00	
 2	DERIVED	t2	ALL	idx_a	NULL	NULL	NULL	90	100.00	Using temporary; Using filesort
 Warnings:
 Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a`,`t`.`s` AS `s`,`t`.`m` AS `m` from `test`.`t1` join (/* select#2 */ select `test`.`t2`.`a` AS `a`,sum(`test`.`t2`.`b`) AS `s`,min(`test`.`t2`.`b`) AS `m` from `test`.`t2` group by `test`.`t2`.`a`) `t` where `t`.`a` = `test`.`t1`.`a` and `test`.`t1`.`b` <= 5
@@ -15125,7 +15125,7 @@ EXPLAIN
       "key_length": "5",
       "used_key_parts": ["a"],
       "ref": ["test.t1.a"],
-      "rows": 9,
+      "rows": 1,
       "filtered": 100,
       "materialized": {
         "query_block": {
@@ -15200,7 +15200,7 @@ from t1 left join
 on t1.a=t.a;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	4	100.00	
-1	PRIMARY	<derived2>	ref	key0	key0	5	test.t1.a	2	100.00	Using where
+1	PRIMARY	<derived2>	ref	key0	key0	5	test.t1.a	1	100.00	Using where
 2	LATERAL DERIVED	t2	ref	idx_a	idx_a	5	test.t1.a	2	100.00	
 Warnings:
 Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a`,`t`.`max` AS `max`,`t`.`min` AS `min` from `test`.`t1` left join (/* select#2 */ select `test`.`t2`.`a` AS `a`,max(`test`.`t2`.`b`) AS `max`,min(`test`.`t2`.`b`) AS `min` from `test`.`t2` where `test`.`t2`.`a` = `test`.`t1`.`a` group by `test`.`t2`.`a`) `t` on(`t`.`a` = `test`.`t1`.`a` and `test`.`t1`.`a` is not null) where 1
@@ -15227,7 +15227,7 @@ EXPLAIN
       "key_length": "5",
       "used_key_parts": ["a"],
       "ref": ["test.t1.a"],
-      "rows": 2,
+      "rows": 1,
       "filtered": 100,
       "attached_condition": "trigcond(trigcond(t1.a is not null))",
       "materialized": {
@@ -15289,7 +15289,7 @@ on t3.a=t.a and t3.c=t.c
 where t3.b > 15;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t3	range	idx_b	idx_b	5	NULL	3	100.00	Using index condition; Using where
-1	PRIMARY	<derived2>	ref	key0	key0	133	test.t3.a,test.t3.c	2	100.00	
+1	PRIMARY	<derived2>	ref	key0	key0	133	test.t3.a,test.t3.c	1	100.00	
 2	LATERAL DERIVED	t4	ref	idx	idx	133	test.t3.a,test.t3.c	1	100.00	
 Warnings:
 Note	1003	/* select#1 */ select `test`.`t3`.`a` AS `a`,`test`.`t3`.`c` AS `c`,`t`.`max` AS `max`,`t`.`min` AS `min` from `test`.`t3` join (/* select#2 */ select `test`.`t4`.`a` AS `a`,`test`.`t4`.`c` AS `c`,max(`test`.`t4`.`b`) AS `max`,min(`test`.`t4`.`b`) AS `min` from `test`.`t4` where `test`.`t4`.`a` = `test`.`t3`.`a` and `test`.`t4`.`c` = `test`.`t3`.`c` group by `test`.`t4`.`a`,`test`.`t4`.`c`) `t` where `t`.`a` = `test`.`t3`.`a` and `t`.`c` = `test`.`t3`.`c` and `test`.`t3`.`b` > 15
@@ -15322,7 +15322,7 @@ EXPLAIN
       "key_length": "133",
       "used_key_parts": ["a", "c"],
       "ref": ["test.t3.a", "test.t3.c"],
-      "rows": 2,
+      "rows": 1,
       "filtered": 100,
       "materialized": {
         "query_block": {
@@ -15367,7 +15367,7 @@ on t3.a=t.a and t3.c=t.c
 where t3.b <= 15;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t3	ALL	idx_b	NULL	NULL	NULL	12	75.00	Using where
-1	PRIMARY	<derived2>	ref	key0	key0	133	test.t3.a,test.t3.c	4	100.00	
+1	PRIMARY	<derived2>	ref	key0	key0	133	test.t3.a,test.t3.c	1	100.00	
 2	DERIVED	t4	ALL	idx	NULL	NULL	NULL	40	100.00	Using temporary; Using filesort
 Warnings:
 Note	1003	/* select#1 */ select `test`.`t3`.`a` AS `a`,`test`.`t3`.`c` AS `c`,`t`.`max` AS `max`,`t`.`min` AS `min` from `test`.`t3` join (/* select#2 */ select `test`.`t4`.`a` AS `a`,`test`.`t4`.`c` AS `c`,max(`test`.`t4`.`b`) AS `max`,min(`test`.`t4`.`b`) AS `min` from `test`.`t4` group by `test`.`t4`.`a`,`test`.`t4`.`c`) `t` where `t`.`a` = `test`.`t3`.`a` and `t`.`c` = `test`.`t3`.`c` and `test`.`t3`.`b` <= 15
@@ -15396,7 +15396,7 @@ EXPLAIN
       "key_length": "133",
       "used_key_parts": ["a", "c"],
       "ref": ["test.t3.a", "test.t3.c"],
-      "rows": 4,
+      "rows": 1,
       "filtered": 100,
       "materialized": {
         "query_block": {
@@ -15441,7 +15441,7 @@ on t3.a=t.a and t3.c=t.c
 where t3.b > 15;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t3	range	idx_b	idx_b	5	NULL	3	100.00	Using index condition; Using where
-1	PRIMARY	<derived2>	ref	key0	key0	133	test.t3.a,test.t3.c	2	100.00	
+1	PRIMARY	<derived2>	ref	key0	key0	133	test.t3.a,test.t3.c	1	100.00	
 2	LATERAL DERIVED	t4	ref	idx	idx	133	test.t3.a,test.t3.c	1	100.00	
 Warnings:
 Note	1003	/* select#1 */ select `test`.`t3`.`a` AS `a`,`test`.`t3`.`c` AS `c`,`t`.`max` AS `max`,`t`.`min` AS `min` from `test`.`t3` join (/* select#2 */ select `test`.`t4`.`a` AS `a`,`test`.`t4`.`c` AS `c`,max(`test`.`t4`.`b`) AS `max`,min(`test`.`t4`.`b`) AS `min` from `test`.`t4` where `test`.`t4`.`a` = `test`.`t3`.`a` and `test`.`t4`.`c` = `test`.`t3`.`c` group by `test`.`t4`.`c`,`test`.`t4`.`a`) `t` where `t`.`a` = `test`.`t3`.`a` and `t`.`c` = `test`.`t3`.`c` and `test`.`t3`.`b` > 15
@@ -15474,7 +15474,7 @@ EXPLAIN
       "key_length": "133",
       "used_key_parts": ["a", "c"],
       "ref": ["test.t3.a", "test.t3.c"],
-      "rows": 2,
+      "rows": 1,
       "filtered": 100,
       "materialized": {
         "query_block": {
@@ -15519,7 +15519,7 @@ on t3.a=t.a and t3.c=t.c
 where t3.b <= 15;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t3	ALL	idx_b	NULL	NULL	NULL	12	75.00	Using where
-1	PRIMARY	<derived2>	ref	key0	key0	133	test.t3.a,test.t3.c	4	100.00	
+1	PRIMARY	<derived2>	ref	key0	key0	133	test.t3.a,test.t3.c	1	100.00	
 2	DERIVED	t4	ALL	idx	NULL	NULL	NULL	40	100.00	Using temporary; Using filesort
 Warnings:
 Note	1003	/* select#1 */ select `test`.`t3`.`a` AS `a`,`test`.`t3`.`c` AS `c`,`t`.`max` AS `max`,`t`.`min` AS `min` from `test`.`t3` join (/* select#2 */ select `test`.`t4`.`a` AS `a`,`test`.`t4`.`c` AS `c`,max(`test`.`t4`.`b`) AS `max`,min(`test`.`t4`.`b`) AS `min` from `test`.`t4` group by `test`.`t4`.`c`,`test`.`t4`.`a`) `t` where `t`.`a` = `test`.`t3`.`a` and `t`.`c` = `test`.`t3`.`c` and `test`.`t3`.`b` <= 15
@@ -15548,7 +15548,7 @@ EXPLAIN
       "key_length": "133",
       "used_key_parts": ["a", "c"],
       "ref": ["test.t3.a", "test.t3.c"],
-      "rows": 4,
+      "rows": 1,
       "filtered": 100,
       "materialized": {
         "query_block": {
@@ -15603,7 +15603,7 @@ where t2.b between 80 and 85 and t2.c in ('y','z') and t2.a=t3.a and t3.c=t.c;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t2	range	idx	idx	133	NULL	2	100.00	Using index condition; Using where
 1	PRIMARY	t3	ref	idx_a	idx_a	5	test.t2.a	2	100.00	Using where
-1	PRIMARY	<derived2>	ref	key0	key0	128	test.t3.c	2	100.00	
+1	PRIMARY	<derived2>	ref	key0	key0	128	test.t3.c	1	100.00	
 2	LATERAL DERIVED	t4	ref	idx_c	idx_c	128	test.t3.c	3	100.00	
 Warnings:
 Note	1003	/* select#1 */ select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c`,`t`.`c` AS `t_c`,`t`.`max` AS `max`,`t`.`min` AS `min` from `test`.`t2` join `test`.`t3` join (/* select#2 */ select `test`.`t4`.`c` AS `c`,max(`test`.`t4`.`b`) AS `max`,min(`test`.`t4`.`b`) AS `min` from `test`.`t4` where `test`.`t4`.`c` = `test`.`t3`.`c` group by `test`.`t4`.`c`) `t` where `test`.`t3`.`a` = `test`.`t2`.`a` and `t`.`c` = `test`.`t3`.`c` and `test`.`t2`.`b` between 80 and 85 and `test`.`t2`.`c` in ('y','z')
@@ -15646,7 +15646,7 @@ EXPLAIN
       "key_length": "128",
       "used_key_parts": ["c"],
       "ref": ["test.t3.c"],
-      "rows": 2,
+      "rows": 1,
       "filtered": 100,
       "materialized": {
         "query_block": {
@@ -15750,7 +15750,7 @@ where t2.b < 40 and t2.a=t3.a and t3.c=t.c;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	90	100.00	Using where
 1	PRIMARY	t3	ref	idx_a	idx_a	5	test.t2.a	2	100.00	Using where
-1	PRIMARY	<derived2>	ref	key0	key0	128	test.t3.c	10	100.00	
+1	PRIMARY	<derived2>	ref	key0	key0	128	test.t3.c	1	100.00	
 2	DERIVED	t4	ALL	idx_c	NULL	NULL	NULL	160	100.00	Using temporary; Using filesort
 Warnings:
 Note	1003	/* select#1 */ select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c`,`t`.`c` AS `t_c`,`t`.`max` AS `max`,`t`.`min` AS `min` from `test`.`t2` join `test`.`t3` join (/* select#2 */ select `test`.`t4`.`c` AS `c`,max(`test`.`t4`.`b`) AS `max`,min(`test`.`t4`.`b`) AS `min` from `test`.`t4` group by `test`.`t4`.`c`) `t` where `test`.`t3`.`a` = `test`.`t2`.`a` and `t`.`c` = `test`.`t3`.`c` and `test`.`t2`.`b` < 40
@@ -15788,7 +15788,7 @@ EXPLAIN
       "key_length": "128",
       "used_key_parts": ["c"],
       "ref": ["test.t3.c"],
-      "rows": 10,
+      "rows": 1,
       "filtered": 100,
       "materialized": {
         "query_block": {
@@ -16344,7 +16344,7 @@ a	c
 explain extended SELECT * FROM t4 WHERE c IN ( SELECT c FROM v1 ) and a < 2;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t4	range	a	a	5	NULL	1	100.00	Using index condition; Using where
-1	PRIMARY	<derived3>	ref	key0	key0	128	test.t4.c	2	100.00	FirstMatch(t4)
+1	PRIMARY	<derived3>	ref	key0	key0	128	test.t4.c	1	100.00	FirstMatch(t4)
 3	LATERAL DERIVED	t3	ref	c	c	128	test.t4.c	2	100.00	
 3	LATERAL DERIVED	<subquery4>	eq_ref	distinct_key	distinct_key	4	func	1	100.00	
 4	MATERIALIZED	t1	ALL	NULL	NULL	NULL	NULL	3	100.00	
@@ -16376,7 +16376,7 @@ EXPLAIN
       "key_length": "128",
       "used_key_parts": ["c"],
       "ref": ["test.t4.c"],
-      "rows": 2,
+      "rows": 1,
       "filtered": 100,
       "first_match": "t4",
       "materialized": {
@@ -16498,7 +16498,7 @@ pk1	f	pk2	cnt
 EXPLAIN EXTENDED SELECT * FROM t1 INNER JOIN v2 ON pk1 = pk2 WHERE f <> 5;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t1	ALL	PRIMARY	NULL	NULL	NULL	2	100.00	Using where
-1	PRIMARY	<derived2>	ref	key0	key0	4	test.t1.pk1	2	100.00	
+1	PRIMARY	<derived2>	ref	key0	key0	4	test.t1.pk1	1	100.00	
 2	LATERAL DERIVED	t2	eq_ref	PRIMARY	PRIMARY	4	test.t1.pk1	1	100.00	Using index
 Warnings:
 Note	1003	/* select#1 */ select `test`.`t1`.`pk1` AS `pk1`,`test`.`t1`.`f` AS `f`,`v2`.`pk2` AS `pk2`,`v2`.`cnt` AS `cnt` from `test`.`t1` join `test`.`v2` where `v2`.`pk2` = `test`.`t1`.`pk1` and `test`.`t1`.`f` <> 5
@@ -16523,7 +16523,7 @@ EXPLAIN
       "key_length": "4",
       "used_key_parts": ["pk2"],
       "ref": ["test.t1.pk1"],
-      "rows": 2,
+      "rows": 1,
       "filtered": 100,
       "materialized": {
         "query_block": {
@@ -16737,7 +16737,7 @@ left join
 on u.id=auditlastlogin.userid;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	u	ALL	NULL	NULL	NULL	NULL	2	
-1	PRIMARY	<derived2>	ref	key0	key0	5	test.u.id	2	
+1	PRIMARY	<derived2>	ref	key0	key0	5	test.u.id	1	
 2	DERIVED	au	ALL	NULL	NULL	NULL	NULL	4	Using temporary; Using filesort
 select * from t1 as u
 left join
diff --git a/mysql-test/main/derived_opt.result b/mysql-test/main/derived_opt.result
index 48ac7e62653..40e034e5c61 100644
--- a/mysql-test/main/derived_opt.result
+++ b/mysql-test/main/derived_opt.result
@@ -535,7 +535,7 @@ ON t2.id=t.id
 WHERE t2.id < 3;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t2	range	PRIMARY	PRIMARY	4	NULL	3	Using index condition
-1	PRIMARY	<derived2>	ref	key0	key0	5	test.t2.id	2	
+1	PRIMARY	<derived2>	ref	key0	key0	5	test.t2.id	1	
 2	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	8	Using temporary; Using filesort
 set join_cache_level=default;
 set optimizer_switch= @save_optimizer_switch;
diff --git a/mysql-test/main/derived_split_innodb.result b/mysql-test/main/derived_split_innodb.result
index b9ed016429b..6625bd7df0d 100644
--- a/mysql-test/main/derived_split_innodb.result
+++ b/mysql-test/main/derived_split_innodb.result
@@ -16,7 +16,7 @@ EXPLAIN SELECT t1.n1 FROM t1, (SELECT n1, n2 FROM t1 WHERE c1 = 'a' GROUP BY n1)
 WHERE t.n1 = t1.n1 AND t.n2 = t1.n2 AND c1 = 'a' GROUP BY n1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	index	c1,n1_c1_n2	n1_c1_n2	9	NULL	2	Using where; Using index
-1	PRIMARY	<derived2>	ref	key0	key0	8	test.t1.n1,test.t1.n2	2	
+1	PRIMARY	<derived2>	ref	key0	key0	8	test.t1.n1,test.t1.n2	1	
 2	LATERAL DERIVED	t1	ref	c1,n1_c1_n2	n1_c1_n2	4	test.t1.n1	1	Using where; Using index
 SELECT t1.n1 FROM t1, (SELECT n1, n2 FROM t1 WHERE c1 = 'a' GROUP BY n1) as t
 WHERE t.n1 = t1.n1 AND t.n2 = t1.n2 AND c1 = 'a' GROUP BY n1;
@@ -95,7 +95,7 @@ ON t2.id=t.id
 WHERE t2.id < 3;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t2	range	PRIMARY	PRIMARY	4	NULL	2	Using where
-1	PRIMARY	<derived2>	ref	key0	key0	5	test.t2.id	2	
+1	PRIMARY	<derived2>	ref	key0	key0	5	test.t2.id	1	
 2	LATERAL DERIVED	t1	eq_ref	PRIMARY	PRIMARY	4	test.t2.id	1	
 set join_cache_level=default;
 DROP TABLE t1,t2;
diff --git a/mysql-test/main/derived_view.result b/mysql-test/main/derived_view.result
index 30831e75341..242f8514bf4 100644
--- a/mysql-test/main/derived_view.result
+++ b/mysql-test/main/derived_view.result
@@ -213,7 +213,7 @@ explain extended
 select * from t1 join (select * from t2 group by f2) tt on f1=f2;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	11	100.00	Using where
-1	PRIMARY	<derived2>	ref	key0	key0	5	test.t1.f1	2	100.00	
+1	PRIMARY	<derived2>	ref	key0	key0	5	test.t1.f1	1	100.00	
 2	DERIVED	t2	ALL	NULL	NULL	NULL	NULL	11	100.00	Using temporary; Using filesort
 Warnings:
 Note	1003	/* select#1 */ select `test`.`t1`.`f1` AS `f1`,`test`.`t1`.`f11` AS `f11`,`tt`.`f2` AS `f2`,`tt`.`f22` AS `f22` from `test`.`t1` join (/* select#2 */ select `test`.`t2`.`f2` AS `f2`,`test`.`t2`.`f22` AS `f22` from `test`.`t2` group by `test`.`t2`.`f2`) `tt` where `tt`.`f2` = `test`.`t1`.`f1`
@@ -227,7 +227,7 @@ flush status;
 explain select * from t1 join (select * from t2 group by f2) tt on f1=f2;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	11	Using where
-1	PRIMARY	<derived2>	ref	key0	key0	5	test.t1.f1	2	
+1	PRIMARY	<derived2>	ref	key0	key0	5	test.t1.f1	1	
 2	DERIVED	t2	ALL	NULL	NULL	NULL	NULL	11	Using temporary; Using filesort
 show status like 'Handler_read%';
 Variable_name	Value
@@ -287,7 +287,7 @@ explain showing created indexes
 explain extended select * from t1 join v2 on f1=f2;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	11	100.00	Using where
-1	PRIMARY	<derived2>	ref	key0	key0	5	test.t1.f1	2	100.00	
+1	PRIMARY	<derived2>	ref	key0	key0	5	test.t1.f1	1	100.00	
 2	DERIVED	t2	ALL	NULL	NULL	NULL	NULL	11	100.00	Using temporary; Using filesort
 Warnings:
 Note	1003	/* select#1 */ select `test`.`t1`.`f1` AS `f1`,`test`.`t1`.`f11` AS `f11`,`v2`.`f2` AS `f2`,`v2`.`f22` AS `f22` from `test`.`t1` join `test`.`v2` where `v2`.`f2` = `test`.`t1`.`f1`
@@ -338,7 +338,7 @@ flush status;
 explain select * from t1 join v2 on f1=f2;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	11	Using where
-1	PRIMARY	<derived2>	ref	key0	key0	5	test.t1.f1	2	
+1	PRIMARY	<derived2>	ref	key0	key0	5	test.t1.f1	1	
 2	DERIVED	t2	ALL	NULL	NULL	NULL	NULL	11	Using temporary; Using filesort
 show status like 'Handler_read%';
 Variable_name	Value
@@ -371,7 +371,7 @@ Handler_read_rnd_next	36
 explain extended select * from v1 join v4 on f1=f2;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	11	100.00	Using where
-1	PRIMARY	<derived2>	ref	key0	key0	5	test.t2.f2	2	100.00	
+1	PRIMARY	<derived2>	ref	key0	key0	5	test.t2.f2	1	100.00	
 2	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	11	100.00	Using where; Using temporary; Using filesort
 Warnings:
 Note	1003	/* select#1 */ select `v1`.`f1` AS `f1`,`v1`.`f11` AS `f11`,`test`.`t2`.`f2` AS `f2`,`test`.`t2`.`f22` AS `f22` from `test`.`v1` join `test`.`t2` where `v1`.`f1` = `test`.`t2`.`f2` and `test`.`t2`.`f2` in (2,3)
@@ -395,7 +395,7 @@ EXPLAIN
       "key_length": "5",
       "used_key_parts": ["f1"],
       "ref": ["test.t2.f2"],
-      "rows": 2,
+      "rows": 1,
       "filtered": 100,
       "materialized": {
         "query_block": {
@@ -529,7 +529,7 @@ join
 on x.f1 = z.f1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	<derived3>	ALL	NULL	NULL	NULL	NULL	11	100.00	Using where
-1	PRIMARY	<derived5>	ref	key0	key0	5	tt.f1	2	100.00	
+1	PRIMARY	<derived5>	ref	key0	key0	5	tt.f1	1	100.00	
 5	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	11	100.00	Using where; Using temporary; Using filesort
 3	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	11	100.00	Using where; Using temporary; Using filesort
 Warnings:
@@ -575,7 +575,7 @@ EXPLAIN
       "key_length": "5",
       "used_key_parts": ["f1"],
       "ref": ["tt.f1"],
-      "rows": 2,
+      "rows": 1,
       "filtered": 100,
       "materialized": {
         "query_block": {
@@ -652,7 +652,7 @@ join
 on x.f1 = z.f1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	11	100.00	Using where
-1	PRIMARY	<derived4>	ref	key0	key0	5	x.f1	2	100.00	
+1	PRIMARY	<derived4>	ref	key0	key0	5	x.f1	1	100.00	
 4	DERIVED	<derived5>	ALL	NULL	NULL	NULL	NULL	11	100.00	Using where; Using temporary; Using filesort
 5	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	11	100.00	Using where; Using temporary; Using filesort
 2	DERIVED	<derived3>	ALL	NULL	NULL	NULL	NULL	11	100.00	Using where; Using temporary; Using filesort
@@ -719,7 +719,7 @@ EXPLAIN
       "key_length": "5",
       "used_key_parts": ["f1"],
       "ref": ["x.f1"],
-      "rows": 2,
+      "rows": 1,
       "filtered": 100,
       "materialized": {
         "query_block": {
@@ -863,7 +863,7 @@ join of above two
 explain extended select * from v6 join v7 on f2=f1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	11	100.00	Using where
-1	PRIMARY	<derived5>	ref	key0	key0	5	test.t2.f2	2	100.00	
+1	PRIMARY	<derived5>	ref	key0	key0	5	test.t2.f2	1	100.00	
 5	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	11	100.00	Using where; Using temporary; Using filesort
 Warnings:
 Note	1003	/* select#1 */ select `test`.`t2`.`f2` AS `f2`,`test`.`t2`.`f22` AS `f22`,`v1`.`f1` AS `f1`,`v1`.`f11` AS `f11` from `test`.`t2` join `test`.`v1` where `v1`.`f1` = `test`.`t2`.`f2` and `test`.`t2`.`f2` < 7 and `test`.`t2`.`f2` in (2,3)
@@ -887,7 +887,7 @@ EXPLAIN
       "key_length": "5",
       "used_key_parts": ["f1"],
       "ref": ["test.t2.f2"],
-      "rows": 2,
+      "rows": 1,
       "filtered": 100,
       "materialized": {
         "query_block": {
@@ -917,7 +917,7 @@ test two keys
 explain select * from t1 join (select * from t2 group by f2) tt on t1.f1=tt.f2 join t1 xx on tt.f22=xx.f1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	11	Using where
-1	PRIMARY	<derived2>	ref	key0	key0	5	test.t1.f1	2	
+1	PRIMARY	<derived2>	ref	key0	key0	5	test.t1.f1	1	
 1	PRIMARY	xx	ALL	NULL	NULL	NULL	NULL	11	Using where; Using join buffer (flat, BNL join)
 2	DERIVED	t2	ALL	NULL	NULL	NULL	NULL	11	Using temporary; Using filesort
 select * from t1 join (select * from t2 group by f2) tt on t1.f1=tt.f2 join t1 xx on tt.f22=xx.f1;
@@ -1106,7 +1106,7 @@ SELECT * FROM t1, t2, v1 WHERE t2.a=t1.a AND t2.a=v1.a AND t2.a=v1.b;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	system	NULL	NULL	NULL	NULL	1	
 1	PRIMARY	t2	ref	a	a	4	const	1	Using index
-1	PRIMARY	<derived2>	ref	key0	key0	8	const,const	1	
+1	PRIMARY	<derived2>	ref	key1	key1	8	func,func	1	
 2	DERIVED	t3	ALL	NULL	NULL	NULL	NULL	12	Using temporary; Using filesort
 SELECT * FROM t1, t2, v1 WHERE t2.a=t1.a AND t2.a=v1.a AND t2.a=v1.b;
 a	a	a	b
@@ -1139,7 +1139,7 @@ SELECT * FROM t3
 WHERE t3.a IN (SELECT v1.a FROM v1, t2 WHERE t2.a = v1.b);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	4	100.00	Using where
-2	DEPENDENT SUBQUERY	<derived3>	ref	key1	key1	5	func	2	100.00	
+2	DEPENDENT SUBQUERY	<derived3>	ref	key1	key1	5	func	1	100.00	
 2	DEPENDENT SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where; Using join buffer (flat, BNL join)
 3	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	3	100.00	Using temporary; Using filesort
 Warnings:
@@ -1505,7 +1505,7 @@ EXPLAIN
 SELECT a FROM t1 WHERE (a,b) IN (SELECT * FROM v1);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	Using where
-1	PRIMARY	<derived3>	ref	key0	key0	10	test.t1.a,test.t1.b	2	FirstMatch(t1)
+1	PRIMARY	<derived3>	ref	key0	key0	10	test.t1.a,test.t1.b	1	FirstMatch(t1)
 3	DERIVED	t2	ALL	NULL	NULL	NULL	NULL	6	Using temporary; Using filesort
 SELECT * FROM v2;
 a	b
@@ -1932,7 +1932,7 @@ EXPLAIN
 SELECT v1.a FROM v1,v2 WHERE v2.b = v1.b ORDER BY 1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	3	Using where; Using filesort
-1	PRIMARY	<derived3>	ref	key0	key0	4	v1.b	2	
+1	PRIMARY	<derived3>	ref	key0	key0	4	v1.b	1	
 3	DERIVED	t2	ALL	NULL	NULL	NULL	NULL	5	Using temporary; Using filesort
 2	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	3	Using temporary; Using filesort
 DROP VIEW v1,v2;
diff --git a/mysql-test/main/join_cache.result b/mysql-test/main/join_cache.result
index fde6e0fec6b..23396d22876 100644
--- a/mysql-test/main/join_cache.result
+++ b/mysql-test/main/join_cache.result
@@ -5197,7 +5197,7 @@ SELECT * FROM (SELECT DISTINCT * FROM t1) t
 WHERE t.a IN (SELECT t2.a FROM t2);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	4	Using where; Start temporary
-1	PRIMARY	<derived2>	ref	key0	key0	5	test.t2.a	2	End temporary
+1	PRIMARY	<derived2>	ref	key0	key0	5	test.t2.a	1	End temporary
 2	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	3	Using temporary
 SELECT * FROM (SELECT DISTINCT * FROM t1) t
 WHERE t.a IN (SELECT t2.a FROM t2);
@@ -5208,8 +5208,8 @@ EXPLAIN
 SELECT * FROM (SELECT DISTINCT * FROM t1) t
 WHERE t.a  IN (SELECT t2.a FROM t2);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	3	
-1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	4	Using where; Start temporary; End temporary; Using join buffer (flat, BNL join)
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	4	Using where; Start temporary
+1	PRIMARY	<derived2>	ref	key0	key0	5	test.t2.a	1	End temporary
 2	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	3	Using temporary
 SELECT * FROM (SELECT DISTINCT * FROM t1) t
 WHERE t.a  IN (SELECT t2.a FROM t2);
diff --git a/mysql-test/main/selectivity.result b/mysql-test/main/selectivity.result
index 00907235ecc..69f4be54414 100644
--- a/mysql-test/main/selectivity.result
+++ b/mysql-test/main/selectivity.result
@@ -140,7 +140,7 @@ and total_revenue = (select max(total_revenue) from revenue0)
 order by s_suppkey;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	supplier	ALL	PRIMARY	NULL	NULL	NULL	10	100.00	Using filesort
-1	PRIMARY	<derived3>	ref	key0	key0	5	dbt3_s001.supplier.s_suppkey	10	100.00	Using where
+1	PRIMARY	<derived3>	ref	key0	key0	5	dbt3_s001.supplier.s_suppkey	1	100.00	Using where
 3	DERIVED	lineitem	range	i_l_shipdate,i_l_suppkey	i_l_shipdate	4	NULL	268	100.00	Using where; Using temporary; Using filesort
 2	SUBQUERY	<derived4>	ALL	NULL	NULL	NULL	NULL	268	100.00	
 4	DERIVED	lineitem	range	i_l_shipdate	i_l_shipdate	4	NULL	268	100.00	Using where; Using temporary; Using filesort
@@ -161,7 +161,7 @@ and total_revenue = (select max(total_revenue) from revenue0)
 order by s_suppkey;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	supplier	ALL	PRIMARY	NULL	NULL	NULL	10	100.00	Using filesort
-1	PRIMARY	<derived3>	ref	key0	key0	5	dbt3_s001.supplier.s_suppkey	10	100.00	Using where
+1	PRIMARY	<derived3>	ref	key0	key0	5	dbt3_s001.supplier.s_suppkey	1	100.00	Using where
 3	DERIVED	lineitem	range	i_l_shipdate,i_l_suppkey	i_l_shipdate	4	NULL	268	100.00	Using where; Using temporary; Using filesort
 2	SUBQUERY	<derived4>	ALL	NULL	NULL	NULL	NULL	268	100.00	
 4	DERIVED	lineitem	range	i_l_shipdate	i_l_shipdate	4	NULL	268	100.00	Using where; Using temporary; Using filesort
diff --git a/mysql-test/main/selectivity_innodb.result b/mysql-test/main/selectivity_innodb.result
index 93917065722..be5348a2a15 100644
--- a/mysql-test/main/selectivity_innodb.result
+++ b/mysql-test/main/selectivity_innodb.result
@@ -143,7 +143,7 @@ and total_revenue = (select max(total_revenue) from revenue0)
 order by s_suppkey;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	supplier	index	PRIMARY	PRIMARY	4	NULL	10	100.00	
-1	PRIMARY	<derived3>	ref	key0	key0	5	dbt3_s001.supplier.s_suppkey	10	100.00	Using where
+1	PRIMARY	<derived3>	ref	key0	key0	5	dbt3_s001.supplier.s_suppkey	1	100.00	Using where
 3	DERIVED	lineitem	range	i_l_shipdate,i_l_suppkey	i_l_shipdate	4	NULL	229	100.00	Using where; Using temporary; Using filesort
 2	SUBQUERY	<derived4>	ALL	NULL	NULL	NULL	NULL	229	100.00	
 4	DERIVED	lineitem	range	i_l_shipdate	i_l_shipdate	4	NULL	229	100.00	Using where; Using temporary; Using filesort
@@ -164,7 +164,7 @@ and total_revenue = (select max(total_revenue) from revenue0)
 order by s_suppkey;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	supplier	index	PRIMARY	PRIMARY	4	NULL	10	100.00	
-1	PRIMARY	<derived3>	ref	key0	key0	5	dbt3_s001.supplier.s_suppkey	10	100.00	Using where
+1	PRIMARY	<derived3>	ref	key0	key0	5	dbt3_s001.supplier.s_suppkey	1	100.00	Using where
 3	DERIVED	lineitem	range	i_l_shipdate,i_l_suppkey	i_l_shipdate	4	NULL	229	100.00	Using where; Using temporary; Using filesort
 2	SUBQUERY	<derived4>	ALL	NULL	NULL	NULL	NULL	228	100.00	
 4	DERIVED	lineitem	range	i_l_shipdate	i_l_shipdate	4	NULL	229	100.00	Using where; Using temporary; Using filesort
diff --git a/mysql-test/main/subselect_extra.result b/mysql-test/main/subselect_extra.result
index dbcf00268c2..6bcea113759 100644
--- a/mysql-test/main/subselect_extra.result
+++ b/mysql-test/main/subselect_extra.result
@@ -389,7 +389,7 @@ EXPLAIN
 SELECT a FROM t1 WHERE (a,b) IN (SELECT * FROM v1);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	Using where
-1	PRIMARY	<derived3>	ref	key0	key0	10	test.t1.a,test.t1.b	2	FirstMatch(t1)
+1	PRIMARY	<derived3>	ref	key0	key0	10	test.t1.a,test.t1.b	1	FirstMatch(t1)
 3	DERIVED	t2	ALL	NULL	NULL	NULL	NULL	6	Using temporary; Using filesort
 SELECT * FROM v2;
 a	b
diff --git a/mysql-test/main/subselect_extra_no_semijoin.result b/mysql-test/main/subselect_extra_no_semijoin.result
index 49a1431eb9b..7e257cb9291 100644
--- a/mysql-test/main/subselect_extra_no_semijoin.result
+++ b/mysql-test/main/subselect_extra_no_semijoin.result
@@ -391,7 +391,7 @@ EXPLAIN
 SELECT a FROM t1 WHERE (a,b) IN (SELECT * FROM v1);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	Using where
-2	DEPENDENT SUBQUERY	<derived3>	index_subquery	key0	key0	10	func,func	2	Using where
+2	DEPENDENT SUBQUERY	<derived3>	index_subquery	key0	key0	10	func,func	1	Using where
 3	DERIVED	t2	ALL	NULL	NULL	NULL	NULL	6	Using temporary; Using filesort
 SELECT * FROM v2;
 a	b
diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc
index c52005e7683..aa645ac00ca 100644
--- a/sql/sql_lex.cc
+++ b/sql/sql_lex.cc
@@ -7617,6 +7617,68 @@ Item *st_select_lex::build_cond_for_grouping_fields(THD *thd, Item *cond,
 }
 
 
+/**
+  Check if any any item in the group by list is also present in the select_list
+  @retval true: All elements common between select and group by list
+*/
+
+void st_select_lex::is_group_by_prefix(KEY *keyinfo)
+{
+  uint key_parts= keyinfo->usable_key_parts;
+  KEY_PART_INFO *key_part_info= keyinfo->key_part;
+  bool found= FALSE;
+
+  if (key_parts < group_list.elements)
+    return;
+
+  uint matched_fields=0, i, j;
+  Item *item;
+
+  for (i= 0; i < key_parts; key_part_info++, i++)
+  {
+    uint fld_idx= key_part_info->fieldnr - 1;
+    item= join->fields_list.elem(fld_idx);
+    for (ORDER *order= group_list.first; order; order= order->next)
+    {
+      Item *ord_item= order->item[0]->real_item();
+      Item_equal *item_equal= ord_item->get_item_equal();
+
+      if (item_equal)
+      {
+        Item_equal_fields_iterator it(*item_equal);
+        Item *equal_item;
+        while ((equal_item= it++))
+        {
+          if (equal_item->eq(item, 0))
+          {
+            matched_fields++;
+            found= TRUE;
+            break;
+          }
+        }
+      }
+      else
+      {
+        if (item->eq(ord_item, 0))
+        {
+          matched_fields++;
+          found= TRUE;
+        }
+      }
+      if (found)
+        break;
+    }
+
+    if (matched_fields == group_list.elements)
+    {
+      for (j=matched_fields - 1; j < key_parts; j++)
+        keyinfo->rec_per_key[j]= 1;
+      return;
+    }
+    found= FALSE;
+  }
+}
+
 int set_statement_var_if_exists(THD *thd, const char *var_name,
                                 size_t var_name_length, ulonglong value)
 {
diff --git a/sql/sql_lex.h b/sql/sql_lex.h
index 4eaec7d062b..6f5d289f3be 100644
--- a/sql/sql_lex.h
+++ b/sql/sql_lex.h
@@ -1387,6 +1387,7 @@ class st_select_lex: public st_select_lex_node
 
   bool cond_pushdown_is_allowed() const
   { return !olap && !explicit_limit && !tvc; }
+  void is_group_by_prefix(KEY *keyinfo);
   
 private:
   bool m_non_agg_field_used;
diff --git a/sql/table.cc b/sql/table.cc
index c4494c9ae4b..a4d050748ba 100644
--- a/sql/table.cc
+++ b/sql/table.cc
@@ -7287,6 +7287,9 @@ bool TABLE::add_tmp_key(uint key, uint key_parts,
           derived->check_distinct_in_union())
         keyinfo->rec_per_key[key_parts - 1]= 1;
     }
+
+    if (!first->is_part_of_union() && first->group_list.elements)
+      first->is_group_by_prefix(keyinfo);
   }
 
   set_if_bigger(s->max_key_length, keyinfo->key_length);



More information about the commits mailing list