MDEV-28817 Derived table elimination issue for multiple occurencesbb-10.10-MDEV-28817

If the same field occurs more than once in the SELECT list
(using aliases) we need to store all those occurences
to be able to eliminate the derived table. For example,
  SELECT t1.* from t1 LEFT JOIN
    (SELECT a AS a1, MAX(c), b AS b1, b AS b2, a AS a2
      FROM t2 GROUP BY a, b) D
    ON D.a1=t1.a and D.b2=t1.b
Derived table D is joined using aliases a1 and b2 so the eliminating
algorithm must know that fields (a1,b2) are the same as (a,b)
which are on the GROUP BY list

3 files changed, 79 insertions, 11 deletions

diff --git a/mysql-test/main/table_elim.result b/mysql-test/main/table_elim.result
index 4f648f45895..ac07a8d2878 100644
--- a/mysql-test/main/table_elim.result
+++ b/mysql-test/main/table_elim.result
@@ -985,3 +985,30 @@ drop table t1, t11, t12, t13, t2;
 #
 # End of MDEV-26278: Table elimination does not work across derived tables
 #
+#
+# MDEV-28817 Derived table elimination does not work for 
+#            multiple occurencies of a field
+#
+create table t1 (a int, b int);
+insert into t1 select seq, seq+10 from seq_1_to_10;
+create table t2 (a int, b int, c int);
+insert into t2 select A.seq, B.seq, 123 from seq_1_to_3 A, seq_1_to_3 B;
+explain select t1.* from t1 left join
+(select a as a1, max(c), b as b1, b as b2, a as a2
+from t2 group by a, b) D
+on D.a1=t1.a and D.b2=t1.b;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	10	
+explain select t1.* from t1 left join
+(select a as a1, b as b1, b as b2, a as a2, count(*)
+from t2 group by a, b) D
+on D.a2=t1.a and D.b1=t1.b;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	10	
+explain select t1.* from t1 left join
+(select a as a1, b as b1, b as b2, min(a+b) as minab, a as a2
+from t2 group by a1, b2) D
+on D.a2=t1.a and D.b1=t1.b;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	10	
+drop table t1, t2;
diff --git a/mysql-test/main/table_elim.test b/mysql-test/main/table_elim.test
index a1f7ef91522..6b0a46f6e55 100644
--- a/mysql-test/main/table_elim.test
+++ b/mysql-test/main/table_elim.test
@@ -751,7 +751,6 @@ explain select t1.* from t1 left join
 --echo # must not hamper the elimination
 explain select t1.* from t1 left join 
   (select count(*) as cnt, b, a from t2 group by a, b) D on D.a=t1.a and D.b=t1.b;
-  
 
 drop view v2b, v2c, v2d, v2e;
 drop table t1, t11, t12, t13, t2;
@@ -759,3 +758,30 @@ drop table t1, t11, t12, t13, t2;
 --echo #
 --echo # End of MDEV-26278: Table elimination does not work across derived tables
 --echo #
+
+--echo #
+--echo # MDEV-28817 Derived table elimination does not work for 
+--echo #            multiple occurencies of a field
+--echo #
+create table t1 (a int, b int);
+insert into t1 select seq, seq+10 from seq_1_to_10;
+
+create table t2 (a int, b int, c int);
+insert into t2 select A.seq, B.seq, 123 from seq_1_to_3 A, seq_1_to_3 B;
+
+explain select t1.* from t1 left join
+  (select a as a1, max(c), b as b1, b as b2, a as a2
+    from t2 group by a, b) D
+  on D.a1=t1.a and D.b2=t1.b;
+  
+explain select t1.* from t1 left join
+  (select a as a1, b as b1, b as b2, a as a2, count(*)
+    from t2 group by a, b) D
+  on D.a2=t1.a and D.b1=t1.b;
+  
+explain select t1.* from t1 left join
+  (select a as a1, b as b1, b as b2, min(a+b) as minab, a as a2
+    from t2 group by a1, b2) D
+  on D.a2=t1.a and D.b1=t1.b;
+  
+drop table t1, t2;
diff --git a/sql/opt_table_elimination.cc b/sql/opt_table_elimination.cc
index cb8ebe9c167..17de10c452f 100644
--- a/sql/opt_table_elimination.cc
+++ b/sql/opt_table_elimination.cc
@@ -472,11 +472,22 @@ const size_t Dep_module_key::iterator_size=
 class Dep_module_pseudo_key : public Dep_module
 {
 public:
+  /*
+  @param table_arg         Table for which the pseudo-key is being created
+  @param key_fields_cnt    Number of fields in GROUP BY expression
+                           i.e. parts of the pseudo-key
+  @param field_indexes     Indexes of GROUP BY fields on the SELECT list.
+                           heir count may be greater than key_fields_cnt, e.g.
+                           'SELECT a, b, max(c), a as a2, b as b2 FROM t1
+                              GROUP BY a,b'
+                           has key_fields_cnt=2 and field_indexes={0,1,3,4}
+  */
   Dep_module_pseudo_key(Dep_value_table *table_arg,
+                        uint key_fields_cnt,
                         std::set<field_index_t>&& field_indexes)
       : table(table_arg), derived_table_field_indexes(field_indexes)
   {
-    unbound_args= static_cast<uint>(field_indexes.size());
+    unbound_args= key_fields_cnt;
   }
 
   Dep_value_table *table;
@@ -588,7 +599,7 @@ public:
 private:
   void create_unique_pseudo_key_if_needed(TABLE_LIST *table_list,
                                           Dep_value_table *tbl_dep);
-  int find_field_in_list(List<Item> &fields_list, Item *field);
+  std::vector<int> find_field_in_list(List<Item> &fields_list, Item *field);
 };
 
 
@@ -1740,8 +1751,9 @@ void Dep_analysis_context::create_unique_pseudo_key_if_needed(
         valid= false;
         break;
       }
-      auto field_idx= find_field_in_list(first_select->join->fields_list, elem);
-      if (field_idx == -1)
+      auto field_indexes= find_field_in_list(first_select->join->fields_list,
+                                             elem);
+      if (field_indexes.empty())
       {
         /*
           This GROUP BY element is not present in the select list. This is a
@@ -1754,12 +1766,13 @@ void Dep_analysis_context::create_unique_pseudo_key_if_needed(
         valid= false;
         break;
       }
-      exposed_fields_indexes.insert(field_idx);
+      exposed_fields_indexes.insert(field_indexes.begin(), field_indexes.end());
     }
     if (valid)
     {
       Dep_module_pseudo_key *pseudo_key;
       pseudo_key= new Dep_module_pseudo_key(tbl_dep,
+                                            first_select->group_list.elements,
                                             std::move(exposed_fields_indexes));
       tbl_dep->pseudo_key= pseudo_key;
     }
@@ -1769,22 +1782,24 @@ void Dep_analysis_context::create_unique_pseudo_key_if_needed(
 
 /*
   Iterate the list of fields and look for the given field.
-  Returns the index of the field if it is found on the list
-  and -1 otherwise
+  Returns vector of indexes of the field in the list. If a single field
+  appears more than once then all occurences will be included into
+  the result
 */
 
-int Dep_analysis_context::find_field_in_list(List<Item> &fields_list,
+std::vector<int> Dep_analysis_context::find_field_in_list(List<Item> &fields_list,
                                              Item *field)
 {
   List_iterator<Item> it(fields_list);
   int field_idx= 0;
+  std::vector<int> indexes;
   while (auto next_field= it++)
   {
     if (next_field->eq(field, false))
-      return field_idx;
+      indexes.push_back(field_idx);
     field_idx++;
   }
-  return -1; /*not found*/
+  return indexes;
 }