diff options
| author | Igor Babaev <igor@askmonty.org> | 2017-11-11 11:45:59 -0800 |
|---|---|---|
| committer | Igor Babaev <igor@askmonty.org> | 2017-11-11 11:45:59 -0800 |
| commit | b5cb4ae470c80e21cc340c2fa13683d7d31b3da6 (patch) | |
| tree | 962f53b8fde85aa2ab1848e68e6c25561e94e96c | |
| parent | 36f84744031430747d27bfb2087c7ae51019a70a (diff) | |
| download | mariadb-git-b5cb4ae470c80e21cc340c2fa13683d7d31b3da6.tar.gz | |
Fixed bug MDEV-14368 Improper error for a grouping query that
uses alias in HAVING when sql_mode = 'ONLY_FULL_GROUP_BY'
This patch corrects the patch for bug#18739: non-standard
HAVING extension was allowed in strict ANSI sql mode
added in 2006 by commit 4b7c4cd27f68b9aac1970b9f21c50d4eee35df7d.
As a result of incompleteness of the fix in the above commit
if a query with GROUP BY contained an aggregate function with an
alias and this alias was used in the HAVING clause of the query
the server reported an error when sql_mode was set to
'ONLY_FULL_GROUP_BY'.
| -rw-r--r-- | mysql-test/r/having.result | 15 | ||||
| -rw-r--r-- | mysql-test/t/having.test | 17 | ||||
| -rw-r--r-- | sql/item.cc | 3 |
3 files changed, 34 insertions, 1 deletions
diff --git a/mysql-test/r/having.result b/mysql-test/r/having.result index 514abbf5522..18915da1a09 100644 --- a/mysql-test/r/having.result +++ b/mysql-test/r/having.result @@ -697,3 +697,18 @@ id column_1 1 80a12660d24a72460e5e292fe33f870276d7f40a expected -- 1 row(s) returned not ER_BAD_FIELD_ERROR drop table t1; +# +# mdev-14368: grouping query with alias for aggregate function in HAVING +# when sql_mode = 'ONLY_FULL_GROUP_BY' +set @save_sql_mode= @@sql_mode; +set sql_mode = 'ONLY_FULL_GROUP_BY'; +create table t1(a int); +insert t1 values (4),(1),(2),(1), (3),(4); +SELECT a, COUNT(a) as ct FROM t1 GROUP BY a HAVING ct>0; +a ct +1 2 +2 1 +3 1 +4 2 +set sql_mode=@save_sql_mode; +drop table t1; diff --git a/mysql-test/t/having.test b/mysql-test/t/having.test index 505fb9ad3cf..a470f462d6a 100644 --- a/mysql-test/t/having.test +++ b/mysql-test/t/having.test @@ -727,3 +727,20 @@ HAVING UPPER(`column_1`) LIKE '8%'; --echo expected -- 1 row(s) returned not ER_BAD_FIELD_ERROR drop table t1; + +--echo # +--echo # mdev-14368: grouping query with alias for aggregate function in HAVING +--echo # when sql_mode = 'ONLY_FULL_GROUP_BY' + + +set @save_sql_mode= @@sql_mode; +set sql_mode = 'ONLY_FULL_GROUP_BY'; + +create table t1(a int); +insert t1 values (4),(1),(2),(1), (3),(4); + +SELECT a, COUNT(a) as ct FROM t1 GROUP BY a HAVING ct>0; + +set sql_mode=@save_sql_mode; + +drop table t1; diff --git a/sql/item.cc b/sql/item.cc index fa2e52bfd4a..332e027adf1 100644 --- a/sql/item.cc +++ b/sql/item.cc @@ -4771,7 +4771,8 @@ resolve_ref_in_select_and_group(THD *thd, Item_ident *ref, SELECT_LEX *select) if (thd->variables.sql_mode & MODE_ONLY_FULL_GROUP_BY && select->having_fix_field && - select_ref != not_found_item && !group_by_ref) + select_ref != not_found_item && !group_by_ref && + !ref->alias_name_used) { /* Report the error if fields was found only in the SELECT item list and |