revision-id: f339c6b18f37f26a6a92a1f2a55458bc31fa604d (mariadb-10.2.15-52-gf339c6b18f3) parent(s): 8662015c90718501d504f4c7aeb94b8626902a9c author: Galina Shalygina committer: Galina Shalygina timestamp: 2018-06-13 16:32:25 +0200 message: MDEV-16386: Wrong result when pushdown into the HAVING clause of the materialized derived table/view that uses aliases is done The problem appears when a column alias inside the materialized derived table/view t1 definition coincide with the column name used in the GROUP BY clause of t1. If the condition that can be pushed into t1 uses that ambiguous column name this column is determined as a column that is used in the GROUP BY clause instead of the alias used in the projection list of t1. That causes wrong result. To prevent it resolve_ref_in_select_and_group() was changed. --- mysql-test/r/derived_cond_pushdown.result | 36 +++++++++++++++++++++++++++++++ mysql-test/t/derived_cond_pushdown.test | 34 +++++++++++++++++++++++++++++ sql/item.cc | 11 ++++++---- 3 files changed, 77 insertions(+), 4 deletions(-) diff --git a/mysql-test/r/derived_cond_pushdown.result b/mysql-test/r/derived_cond_pushdown.result index 3fbc81019cc..91f7546300b 100644 --- a/mysql-test/r/derived_cond_pushdown.result +++ b/mysql-test/r/derived_cond_pushdown.result @@ -9473,3 +9473,39 @@ WHERE (a>0 AND a<2 OR a IN (2,3)) AND a 2 DROP TABLE t1; +# +# MDEV-16386: pushing condition into the HAVING clause when ambiguous +# fields warning appears +# +CREATE TABLE t1 (a INT, b INT); +INSERT INTO t1 VALUES (1,2),(2,3); +SELECT * FROM +( +SELECT b AS a +FROM t1 +GROUP BY t1.a +) der_tab +WHERE (der_tab.a=2); +a +2 +SELECT * FROM +( +SELECT 'a' AS a +FROM t1 +GROUP BY t1.a +) der_tab +WHERE (der_tab.a='a'); +a +a +a +SELECT * FROM +( +SELECT 1 AS a +FROM t1 +GROUP BY t1.a +) der_tab +WHERE (der_tab.a=1); +a +1 +1 +DROP TABLE t1; diff --git a/mysql-test/t/derived_cond_pushdown.test b/mysql-test/t/derived_cond_pushdown.test index d3832ce1ec3..b4214a06435 100644 --- a/mysql-test/t/derived_cond_pushdown.test +++ b/mysql-test/t/derived_cond_pushdown.test @@ -1745,3 +1745,37 @@ WHERE (a>0 AND a<2 OR a IN (2,3)) AND (a=2 OR 0); DROP TABLE t1; + +--echo # +--echo # MDEV-16386: pushing condition into the HAVING clause when ambiguous +--echo # fields warning appears +--echo # + +CREATE TABLE t1 (a INT, b INT); +INSERT INTO t1 VALUES (1,2),(2,3); + +SELECT * FROM +( + SELECT b AS a + FROM t1 + GROUP BY t1.a +) der_tab +WHERE (der_tab.a=2); + +SELECT * FROM +( + SELECT 'a' AS a + FROM t1 + GROUP BY t1.a +) der_tab +WHERE (der_tab.a='a'); + +SELECT * FROM +( + SELECT 1 AS a + FROM t1 + GROUP BY t1.a +) der_tab +WHERE (der_tab.a=1); + +DROP TABLE t1; diff --git a/sql/item.cc b/sql/item.cc index f9200ccf56d..ad0d40501ae 100644 --- a/sql/item.cc +++ b/sql/item.cc @@ -4984,9 +4984,11 @@ static Item** find_field_in_group_list(Item *find_item, ORDER *group_list) in the SELECT clause of Q. - Search for a column named col_ref_i [in table T_j] in the GROUP BY clause of Q. - - If found different columns with the same name in GROUP BY and SELECT - - issue a warning and return the GROUP BY column, - - otherwise + - If found different columns with the same name in GROUP BY and SELECT: + - if the condition using this column name is pushed into the HAVING clause + of the materialized table/view return the SELECT column + - else issue a warning and return the GROUP BY column. + - Otherwise - if the MODE_ONLY_FULL_GROUP_BY mode is enabled return error - else return the found SELECT column. @@ -5025,7 +5027,8 @@ resolve_ref_in_select_and_group(THD *thd, Item_ident *ref, SELECT_LEX *select) /* Check if the fields found in SELECT and GROUP BY are the same field. */ if (group_by_ref && (select_ref != not_found_item) && - !((*group_by_ref)->eq(*select_ref, 0))) + !((*group_by_ref)->eq(*select_ref, 0)) && + (!select->cond_pushed_into_having)) { ambiguous_fields= TRUE; push_warning_printf(thd, Sql_condition::WARN_LEVEL_WARN,