diff options
| -rw-r--r-- | mysql-test/r/having.result | 13 | ||||
| -rw-r--r-- | mysql-test/t/having.test | 13 | ||||
| -rw-r--r-- | sql/item.cc | 18 | ||||
| -rw-r--r-- | sql/share/errmsg.txt | 2 | ||||
| -rw-r--r-- | sql/sql_select.cc | 4 |
5 files changed, 48 insertions, 2 deletions
diff --git a/mysql-test/r/having.result b/mysql-test/r/having.result index fe918e4c3ff..a37f260ff31 100644 --- a/mysql-test/r/having.result +++ b/mysql-test/r/having.result @@ -392,3 +392,16 @@ HAVING HU.PROJ.CITY = HU.STAFF.CITY); EMPNUM GRADE*1000 E3 13000 DROP SCHEMA HU; +USE test; +create table t1(f1 int); +select f1 from t1 having max(f1)=f1; +f1 +select f1 from t1 group by f1 having max(f1)=f1; +f1 +set session sql_mode='ONLY_FULL_GROUP_BY'; +select f1 from t1 having max(f1)=f1; +ERROR 42000: non-grouping field 'f1' is used in HAVING clause +select f1 from t1 group by f1 having max(f1)=f1; +f1 +set session sql_mode=''; +drop table t1; diff --git a/mysql-test/t/having.test b/mysql-test/t/having.test index 9b21e544657..779b694987b 100644 --- a/mysql-test/t/having.test +++ b/mysql-test/t/having.test @@ -393,3 +393,16 @@ SELECT EMPNUM, GRADE*1000 HAVING HU.PROJ.CITY = HU.STAFF.CITY); DROP SCHEMA HU; +USE test; +# +# Bug#18739: non-standard HAVING extension was allowed in strict ANSI sql mode. +# +create table t1(f1 int); +select f1 from t1 having max(f1)=f1; +select f1 from t1 group by f1 having max(f1)=f1; +set session sql_mode='ONLY_FULL_GROUP_BY'; +--error 1461 +select f1 from t1 having max(f1)=f1; +select f1 from t1 group by f1 having max(f1)=f1; +set session sql_mode=''; +drop table t1; diff --git a/sql/item.cc b/sql/item.cc index e3da950ceef..9498bf3fcd8 100644 --- a/sql/item.cc +++ b/sql/item.cc @@ -3153,7 +3153,8 @@ static Item** find_field_in_group_list(Item *find_item, ORDER *group_list) both clauses contain different fields with the same names, a warning is issued that name of 'ref' is ambiguous. We extend ANSI SQL in that when no GROUP BY column is found, then a HAVING name is resolved as a possibly - derived SELECT column. + derived SELECT column. This extension is allowed only if the + MODE_ONLY_FULL_GROUP_BY sql mode isn't enabled. NOTES The resolution procedure is: @@ -3163,7 +3164,9 @@ static Item** find_field_in_group_list(Item *find_item, ORDER *group_list) 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 return the found SELECT column. + - otherwise + - if the MODE_ONLY_FULL_GROUP_BY mode is enabled return error + - else return the found SELECT column. RETURN @@ -3208,6 +3211,17 @@ 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_ref != not_found_item && !group_by_ref) + { + /* + Report the error if fields was found only in the SELECT item list and + the strict mode is enabled. + */ + my_error(ER_NON_GROUPING_FIELD_USED, MYF(0), + ref->name, "HAVING"); + return NULL; + } if (select_ref != not_found_item || group_by_ref) { if (select_ref != not_found_item && !ambiguous_fields) diff --git a/sql/share/errmsg.txt b/sql/share/errmsg.txt index 37487c245a9..e0adb3fc408 100644 --- a/sql/share/errmsg.txt +++ b/sql/share/errmsg.txt @@ -5611,3 +5611,5 @@ ER_TABLE_NEEDS_UPGRADE eng "Table upgrade required. Please do \"REPAIR TABLE `%-.32s`\" to fix it!" ER_SP_NO_AGGREGATE 42000 eng "AGGREGATE is not supported for stored functions" +ER_NON_GROUPING_FIELD_USED 42000 + eng "non-grouping field '%-.64s' is used in %-.64s clause" diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 0211539e784..8daed345433 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -12613,6 +12613,10 @@ setup_group(THD *thd, Item **ref_pointer_array, TABLE_LIST *tables, if (item->type() != Item::SUM_FUNC_ITEM && !item->marker && !item->const_item()) { + /* + TODO: change ER_WRONG_FIELD_WITH_GROUP to more detailed + ER_NON_GROUPING_FIELD_USED + */ my_error(ER_WRONG_FIELD_WITH_GROUP, MYF(0), item->full_name()); return 1; } |