diff options
| -rw-r--r-- | mysql-test/r/derived_cond_pushdown.result | 41 | ||||
| -rw-r--r-- | mysql-test/t/derived_cond_pushdown.test | 28 | ||||
| -rw-r--r-- | sql/item.h | 6 | ||||
| -rw-r--r-- | sql/sql_derived.cc | 22 |
4 files changed, 95 insertions, 2 deletions
diff --git a/mysql-test/r/derived_cond_pushdown.result b/mysql-test/r/derived_cond_pushdown.result index d4e8feff740..25237aa11a9 100644 --- a/mysql-test/r/derived_cond_pushdown.result +++ b/mysql-test/r/derived_cond_pushdown.result @@ -10593,4 +10593,45 @@ a abc DROP VIEW v1; DROP TABLE t1; +# +# MDEV-19179: pushdown into UNION of aggregation selects whose +# corresponding columns have different names +# +create table t1 (a int); +insert into t1 values (3), (7), (1); +select * +from (select min(a) as x from t1 union all select max(a) as y from t1) t +where x>0; +x +1 +7 +explain extended select * +from (select min(a) as x from t1 union all select max(a) as y from t1) t +where x>0; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY <derived2> ALL NULL NULL NULL NULL 6 100.00 Using where +2 DERIVED t1 ALL NULL NULL NULL NULL 3 100.00 +3 UNION t1 ALL NULL NULL NULL NULL 3 100.00 +Warnings: +Note 1003 select `t`.`x` AS `x` from (select min(`test`.`t1`.`a`) AS `x` from `test`.`t1` having `x` > 0 union all select max(`test`.`t1`.`a`) AS `x` from `test`.`t1` having `x` > 0) `t` where `t`.`x` > 0 +prepare stmt from "select * +from (select min(a) as x from t1 union all select max(a) as y from t1) t +where x>0"; +execute stmt; +x +1 +7 +execute stmt; +x +1 +7 +deallocate prepare stmt; +create view v1(m) as +select min(a) as x from t1 union all select max(a) as y from t1; +select * from v1 where m > 0; +m +1 +7 +drop view v1; +drop table t1; # End of 10.2 tests diff --git a/mysql-test/t/derived_cond_pushdown.test b/mysql-test/t/derived_cond_pushdown.test index a7df65f2951..31b49047bf1 100644 --- a/mysql-test/t/derived_cond_pushdown.test +++ b/mysql-test/t/derived_cond_pushdown.test @@ -2184,4 +2184,32 @@ SELECT * FROM v1 WHERE IF( a REGEXP 'def', 'foo', a ) IN ('abc', 'foobar'); DROP VIEW v1; DROP TABLE t1; +--echo # +--echo # MDEV-19179: pushdown into UNION of aggregation selects whose +--echo # corresponding columns have different names +--echo # + +create table t1 (a int); +insert into t1 values (3), (7), (1); + +let $q= +select * +from (select min(a) as x from t1 union all select max(a) as y from t1) t +where x>0; + +eval $q; +eval explain extended $q; + +eval prepare stmt from "$q"; +execute stmt; +execute stmt; +deallocate prepare stmt; + +create view v1(m) as +select min(a) as x from t1 union all select max(a) as y from t1; +select * from v1 where m > 0; + +drop view v1; +drop table t1; + --echo # End of 10.2 tests diff --git a/sql/item.h b/sql/item.h index a49f9e8e5e4..ed20074a8da 100644 --- a/sql/item.h +++ b/sql/item.h @@ -818,6 +818,12 @@ public: void set_name_for_rollback(THD *thd, const char *str, uint length, CHARSET_INFO *cs); void rename(char *new_name); + void share_name_with(Item *item) + { + name= item->name; + name_length= item->name_length; + is_autogenerated_name= item->is_autogenerated_name; + } void init_make_field(Send_field *tmp_field,enum enum_field_types type); virtual void cleanup(); virtual void make_field(THD *thd, Send_field *field); diff --git a/sql/sql_derived.cc b/sql/sql_derived.cc index 39499e6895f..5379dd45bfb 100644 --- a/sql/sql_derived.cc +++ b/sql/sql_derived.cc @@ -1199,7 +1199,8 @@ bool pushdown_cond_for_derived(THD *thd, Item *cond, TABLE_LIST *derived) DBUG_RETURN(false); st_select_lex_unit *unit= derived->get_unit(); - st_select_lex *sl= unit->first_select(); + st_select_lex *first_sl= unit->first_select(); + st_select_lex *sl= first_sl; if (derived->prohibit_cond_pushdown) DBUG_RETURN(false); @@ -1311,7 +1312,24 @@ bool pushdown_cond_for_derived(THD *thd, Item *cond, TABLE_LIST *derived) if (!extracted_cond_copy) continue; } - + + /* + Rename the columns of all non-first selects of a union to be compatible + by names with the columns of the first select. It will allow to use copies + of the same expression pushed into having clauses of different selects. + */ + if (sl != first_sl) + { + DBUG_ASSERT(sl->item_list.elements == first_sl->item_list.elements); + List_iterator_fast<Item> it(sl->item_list); + List_iterator_fast<Item> nm_it(unit->types); + Item * item; + while((item= it++)) + { + item->share_name_with(nm_it++); + } + } + /* Transform the references to the 'derived' columns from the condition pushed into the having clause of sl to make them usable in the new context |