diff options
| -rw-r--r-- | mysql-test/main/table_elim.result | 27 | ||||
| -rw-r--r-- | mysql-test/main/table_elim.test | 28 | ||||
| -rw-r--r-- | sql/opt_table_elimination.cc | 35 |
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; } |