From 4b7c4cd27f68b9aac1970b9f21c50d4eee35df7d Mon Sep 17 00:00:00 2001 From: unknown Date: Fri, 21 Apr 2006 01:52:59 +0400 Subject: Fixed bug#18739: non-standard HAVING extension was allowed in strict ANSI sql mode. The SQL standard doesn't allow to use in HAVING clause fields that are not present in GROUP BY clause and not under any aggregate function in the HAVING clause. However, mysql allows using such fields. This extension assume that the non-grouping fields will have the same group-wise values. Otherwise, the result will be unpredictable. This extension allowed in strict MODE_ONLY_FULL_GROUP_BY sql mode results in misunderstanding of HAVING capabilities. The new error message ER_NON_GROUPING_FIELD_USED message is added. It says "non-grouping field '%-.64s' is used in %-.64s clause". This message is supposed to be used for reporting errors when some field is not found in the GROUP BY clause but have to be present there. Use cases for this message are this bug and when a field is present in a SELECT item list not under any aggregate function and there is GROUP BY clause present which doesn't mention that field. It renders the ER_WRONG_FIELD_WITH_GROUP error message obsolete as being more descriptive. The resolve_ref_in_select_and_group() function now reports the ER_NON_GROUPING_FIELD_FOUND error if the strict mode is set and the field for HAVING clause is found in the SELECT item list only. sql/share/errmsg.txt: Added the new ER_NON_GROUPING_FIELD_USED error message for the bug#14169. mysql-test/t/having.test: Added test case for the bug#18739: non-standard HAVING extension was allowed in strict ANSI sql mode. mysql-test/r/having.result: Added test case for the bug#18739: non-standard HAVING extension was allowed in strict ANSI sql mode. sql/sql_select.cc: Added TODO comment to change the ER_WRONG_FIELD_WITH_GROUP to more detailed ER_NON_GROUPING_FIELD_USED message. sql/item.cc: Fixed bug#18739: non-standard HAVING extension was allowed in strict ANSI sql mode. The resolve_ref_in_select_and_group() function now reports the ER_NON_GROUPING_FIELD_FOUND error if the strict MODE_ONLY_FULL_GROUP_BY mode is set and the field for HAVING clause is found in the SELECT item list only. --- mysql-test/r/having.result | 13 +++++++++++++ mysql-test/t/having.test | 13 +++++++++++++ 2 files changed, 26 insertions(+) (limited to 'mysql-test') 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; -- cgit v1.2.1