Fixed LP bug #806431.

The function generate_derived_keys_for_table incorrectly handled
the cases when a materialized view or derived table could be accessed
by different keys on the same fields if these keys depended on the
same tables.
  

3 files changed, 69 insertions, 13 deletions

diff --git a/mysql-test/r/derived_view.result b/mysql-test/r/derived_view.result
index 1e896b87ba2..bbc9a2fb3dd 100644
--- a/mysql-test/r/derived_view.result
+++ b/mysql-test/r/derived_view.result
@@ -794,3 +794,30 @@ WHERE t4.f2 = t3.f2  AND t4.f2 = t.f1 ORDER BY f;
 f
 x
 DROP TABLE t1,t2,t3,t4;
+#
+# LP bug #806431: join over materialized derived with key                    
+#             
+CREATE TABLE t1 (a int, b int);
+INSERT INTO t1 VALUES (0,0),(3,0),(1,0);
+CREATE ALGORITHM=TEMPTABLE VIEW v1 AS SELECT a,b FROM t1 ;
+SET SESSION optimizer_switch='derived_with_keys=off';
+SELECT * FROM t1 AS t JOIN v1 AS v WHERE t.a = v.b AND t.b = v.b;
+a	b	a	b
+0	0	0	0
+0	0	3	0
+0	0	1	0
+SET SESSION optimizer_switch='derived_with_keys=on';
+EXPLAIN
+SELECT * FROM t1 AS t JOIN v1 AS v WHERE t.a = v.b AND t.b = v.b;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t	ALL	NULL	NULL	NULL	NULL	3	Using where
+1	PRIMARY	<derived2>	ref	key0	key0	5	test.t.a	2	
+2	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	3	
+SELECT * FROM t1 AS t JOIN v1 AS v WHERE t.a = v.b AND t.b = v.b;
+a	b	a	b
+0	0	1	0
+0	0	3	0
+0	0	0	0
+SET SESSION optimizer_switch=default;
+DROP VIEW v1;
+DROP TABLE t1;
diff --git a/mysql-test/t/derived_view.test b/mysql-test/t/derived_view.test
index 33ba752cc2f..1c91acdf14a 100644
--- a/mysql-test/t/derived_view.test
+++ b/mysql-test/t/derived_view.test
@@ -414,3 +414,24 @@ SELECT t.f1 AS f
 
 DROP TABLE t1,t2,t3,t4;
 
+--echo #
+--echo # LP bug #806431: join over materialized derived with key                    
+--echo #             
+
+CREATE TABLE t1 (a int, b int);
+INSERT INTO t1 VALUES (0,0),(3,0),(1,0);
+
+CREATE ALGORITHM=TEMPTABLE VIEW v1 AS SELECT a,b FROM t1 ;
+
+SET SESSION optimizer_switch='derived_with_keys=off';
+SELECT * FROM t1 AS t JOIN v1 AS v WHERE t.a = v.b AND t.b = v.b;
+SET SESSION optimizer_switch='derived_with_keys=on';
+EXPLAIN
+SELECT * FROM t1 AS t JOIN v1 AS v WHERE t.a = v.b AND t.b = v.b;
+SELECT * FROM t1 AS t JOIN v1 AS v WHERE t.a = v.b AND t.b = v.b;
+
+SET SESSION optimizer_switch=default;
+
+DROP VIEW v1;
+DROP TABLE t1;
+
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index 7c82b5c368b..56aabb8589c 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -8342,17 +8342,29 @@ bool generate_derived_keys_for_table(KEYUSE *keyuse, uint count, uint keys)
     return TRUE;
   uint keyno= 0;
   KEYUSE *first_keyuse= keyuse;
-  uint prev_part= (uint) (-1);
+  uint prev_part= keyuse->keypart;
   uint parts= 0;
   uint i= 0;
-  do
+
+  for ( ; i < count && keyno < keys; )
   {
-    keyuse->key= keyno;
-    keyuse->keypart_map= (key_part_map) (1 << parts);
-    keyuse++;
-    if (++i == count || keyuse->used_tables != first_keyuse->used_tables)
+    do
+    {
+      keyuse->key= keyno;
+      keyuse->keypart_map= (key_part_map) (1 << parts);     
+      keyuse++;
+      i++;
+    } 
+    while (i < count && keyuse->used_tables == first_keyuse->used_tables &&
+           keyuse->keypart == prev_part);
+    parts++;
+    if (i < count && keyuse->used_tables == first_keyuse->used_tables)
+    {
+      prev_part= keyuse->keypart;
+    }
+    else
     {
-      if (table->add_tmp_key(keyno, ++parts, 
+      if (table->add_tmp_key(keyno, parts, 
                              get_next_field_for_derived_key, 
                              (uchar *) &first_keyuse,
                              FALSE))
@@ -8361,14 +8373,10 @@ bool generate_derived_keys_for_table(KEYUSE *keyuse, uint count, uint keys)
       first_keyuse= keyuse;
       keyno++;
       parts= 0;
-      prev_part= (uint) (-1);
-    }
-    else if (keyuse->keypart != prev_part)
-    {
-      parts++;
       prev_part= keyuse->keypart;
     }
-  } while (keyno < keys);
+  }             
+
   return FALSE;
 }