diff options
| -rw-r--r-- | mysql-test/r/derived_cond_pushdown.result | 196 | ||||
| -rw-r--r-- | mysql-test/t/derived_cond_pushdown.test | 56 | ||||
| -rw-r--r-- | sql/item.cc | 11 | ||||
| -rw-r--r-- | sql/sql_lex.cc | 1 | ||||
| -rw-r--r-- | sql/sql_lex.h | 5 | ||||
| -rw-r--r-- | sql/sql_select.cc | 2 |
6 files changed, 267 insertions, 4 deletions
diff --git a/mysql-test/r/derived_cond_pushdown.result b/mysql-test/r/derived_cond_pushdown.result index 3fbc81019cc..83e70dd634c 100644 --- a/mysql-test/r/derived_cond_pushdown.result +++ b/mysql-test/r/derived_cond_pushdown.result @@ -9473,3 +9473,199 @@ 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),(3,4); +SELECT * FROM +( +SELECT t1.b AS a +FROM t1 +GROUP BY t1.a +) dt +WHERE (dt.a=2); +a +2 +EXPLAIN FORMAT=JSON SELECT * FROM +( +SELECT t1.b AS a +FROM t1 +GROUP BY t1.a +) dt +WHERE (dt.a=2); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "table_name": "<derived2>", + "access_type": "ALL", + "rows": 3, + "filtered": 100, + "attached_condition": "dt.a = 2", + "materialized": { + "query_block": { + "select_id": 2, + "having_condition": "a = 2", + "filesort": { + "sort_key": "t1.a", + "temporary_table": { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 3, + "filtered": 100 + } + } + } + } + } + } + } +} +SELECT * FROM +( +SELECT t1.b AS a +FROM t1 +GROUP BY t1.a +HAVING (t1.a<3) +) dt +WHERE (dt.a>1); +a +2 +3 +EXPLAIN FORMAT=JSON SELECT * FROM +( +SELECT t1.b AS a +FROM t1 +GROUP BY t1.a +HAVING (t1.a<3) +) dt +WHERE (dt.a>1); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "table_name": "<derived2>", + "access_type": "ALL", + "rows": 3, + "filtered": 100, + "attached_condition": "dt.a > 1", + "materialized": { + "query_block": { + "select_id": 2, + "having_condition": "t1.a < 3 and a > 1", + "filesort": { + "sort_key": "t1.a", + "temporary_table": { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 3, + "filtered": 100 + } + } + } + } + } + } + } +} +SELECT * FROM +( +SELECT 'ab' AS a +FROM t1 +GROUP BY t1.a +) dt +WHERE (dt.a='ab'); +a +ab +ab +ab +EXPLAIN FORMAT=JSON SELECT * FROM +( +SELECT 'ab' AS a +FROM t1 +GROUP BY t1.a +) dt +WHERE (dt.a='ab'); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "table_name": "<derived2>", + "access_type": "ALL", + "rows": 3, + "filtered": 100, + "attached_condition": "dt.a = 'ab'", + "materialized": { + "query_block": { + "select_id": 2, + "filesort": { + "sort_key": "t1.a", + "temporary_table": { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 3, + "filtered": 100 + } + } + } + } + } + } + } +} +SELECT * FROM +( +SELECT 1 AS a +FROM t1 +GROUP BY t1.a +) dt +WHERE (dt.a=1); +a +1 +1 +1 +EXPLAIN FORMAT=JSON SELECT * FROM +( +SELECT 1 AS a +FROM t1 +GROUP BY t1.a +) dt +WHERE (dt.a=1); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "table_name": "<derived2>", + "access_type": "ALL", + "rows": 3, + "filtered": 100, + "attached_condition": "dt.a = 1", + "materialized": { + "query_block": { + "select_id": 2, + "filesort": { + "sort_key": "t1.a", + "temporary_table": { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 3, + "filtered": 100 + } + } + } + } + } + } + } +} +DROP TABLE t1; diff --git a/mysql-test/t/derived_cond_pushdown.test b/mysql-test/t/derived_cond_pushdown.test index d3832ce1ec3..718140d3a77 100644 --- a/mysql-test/t/derived_cond_pushdown.test +++ b/mysql-test/t/derived_cond_pushdown.test @@ -1745,3 +1745,59 @@ 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),(3,4); + +LET $query= +SELECT * FROM +( + SELECT t1.b AS a + FROM t1 + GROUP BY t1.a +) dt +WHERE (dt.a=2); +EVAL $query; +EVAL EXPLAIN FORMAT=JSON $query; + +LET $query= +SELECT * FROM +( + SELECT t1.b AS a + FROM t1 + GROUP BY t1.a + HAVING (t1.a<3) +) dt +WHERE (dt.a>1); +EVAL $query; +EVAL EXPLAIN FORMAT=JSON $query; + +LET $query= +SELECT * FROM +( + SELECT 'ab' AS a + FROM t1 + GROUP BY t1.a +) dt +WHERE (dt.a='ab'); +EVAL $query; +EVAL EXPLAIN FORMAT=JSON $query; + +LET $query= +SELECT * FROM +( + SELECT 1 AS a + FROM t1 + GROUP BY t1.a +) dt +WHERE (dt.a=1); +EVAL $query; +EVAL EXPLAIN FORMAT=JSON $query; + +DROP TABLE t1; diff --git a/sql/item.cc b/sql/item.cc index f9200ccf56d..4bc6fe7e5bc 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 that uses this column name is pushed down into + the HAVING clause 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->having_fix_field_for_pushed_cond)) { ambiguous_fields= TRUE; push_warning_printf(thd, Sql_condition::WARN_LEVEL_WARN, diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc index 750fb75bfb9..d3ddd9e208c 100644 --- a/sql/sql_lex.cc +++ b/sql/sql_lex.cc @@ -2107,6 +2107,7 @@ void st_select_lex::init_query() cond_pushed_into_where= cond_pushed_into_having= 0; olap= UNSPECIFIED_OLAP_TYPE; having_fix_field= 0; + having_fix_field_for_pushed_cond= 0; context.select_lex= this; context.init(); /* diff --git a/sql/sql_lex.h b/sql/sql_lex.h index ed5e423fd5a..a1f6b202ae6 100644 --- a/sql/sql_lex.h +++ b/sql/sql_lex.h @@ -887,6 +887,11 @@ public: bool braces; /* SELECT ... UNION (SELECT ... ) <- this braces */ /* TRUE when having fix field called in processing of this SELECT */ bool having_fix_field; + /* + TRUE when fix field is called for a new condition pushed into the + HAVING clause of this SELECT + */ + bool having_fix_field_for_pushed_cond; /* List of references to fields referenced from inner selects */ List<Item_outer_ref> inner_refs_list; /* Number of Item_sum-derived objects in this SELECT */ diff --git a/sql/sql_select.cc b/sql/sql_select.cc index ee5ba4ade54..4ebd5ef4954 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -1350,9 +1350,11 @@ JOIN::optimize_inner() if (having) { select_lex->having_fix_field= 1; + select_lex->having_fix_field_for_pushed_cond= 1; if (having->fix_fields(thd, &having)) DBUG_RETURN(1); select_lex->having_fix_field= 0; + select_lex->having_fix_field_for_pushed_cond= 0; } } |