MySQL Bugs: #65798: ONLY_FULL_GROUP_BY documentation doesn't mention implicit group by

Bug #65798	ONLY_FULL_GROUP_BY documentation doesn't mention implicit group by
Submitted:	3 Jul 2012 15:16	Modified:	14 Nov 2012 20:54
Reporter:	Raghavendra Prabhu	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S3 (Non-critical)
Version:	5.5.26	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
i) The documentation makes reference to the error ER_WRONG_FIELD_WITH_GROUP which is "ERROR 1055 (42000): '<column-name>' isn't in GROUP BY" 

However, from the error I received and from the code, it seems it is also used for ER_MIX_OF_GROUP_FUNC_AND_FIELDS which is "Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause".   This needs to be added to documentation or a different sql_mode needs to be created for this.

ii)
Also, 
whereas ER_WRONG_FIELD_WITH_GROUP explicitly checks for Item::FIELD_ITEM type items, ER_MIX_OF_GROUP_FUNC_AND_FIELDS doesn't check that ie. select_lex->non_agg_field_used() also returns true even if the field is not Item::FIELD_ITEM (ie. it is like select '0', <agg-field> FROM ..)

How to repeat:
i) is a documentation fix, so no things to repeat.

ii)

[localhost] {root} (mysql) > create table test (x int(11) not null, y varchar(10) not null);
Query OK, 0 rows affected (0.01 sec)

[localhost] {root} (mysql) > select '0', count(y) from test order by x;
ERROR 1140 (42000): Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause
[localhost] {root} (mysql) >
[localhost] {root} (mysql) > show session variables like '%sql_mode%';
+---------------+--------------------+
| Variable_name | Value              |
+---------------+--------------------+
| sql_mode      | ONLY_FULL_GROUP_BY |
+---------------+--------------------+
1 row in set (0.00 sec)

Suggested fix:
Fix the documentation and fix the code for (ii) by making non_agg_field_used() not return true for non Item::FIELD_ITEM fields.

Thank you for the bug report. Not sure what exact manual page do you suggest to change, but I'd agree there is a code problem to fix:

macbook-pro:5.5 openxs$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 15
Server version: 5.5.26-debug Source distribution

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create table test (x int(11) not null, y varchar(10) not null);
Query OK, 0 rows affected (0.14 sec)

mysql> select '0', count(y) from test order by x;
+---+----------+
| 0 | count(y) |
+---+----------+
| 0 |        0 |
+---+----------+
1 row in set (0.28 sec)

mysql> select x, count(y) from test order by x;
+---+----------+
| x | count(y) |
+---+----------+
| NULL |        0 |
+---+----------+
1 row in set (0.02 sec)

mysql> select @@sql_mode;
+------------+
| @@sql_mode |
+------------+
|            |
+------------+
1 row in set (0.30 sec)

mysql> set session sql_mode='ONLY_FULL_GROUP_BY';
Query OK, 0 rows affected (0.06 sec)

mysql> select @@sql_mode;
+--------------------+
| @@sql_mode         |
+--------------------+
| ONLY_FULL_GROUP_BY |
+--------------------+
1 row in set (0.00 sec)

mysql> select '0', count(y) from test order by x;
ERROR 1140 (42000): Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause

The case above should be distinguished from the following:

mysql> select x, count(y) from test order by x;
ERROR 1140 (42000): Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause

@Valeriy,

The example you posted in previous comment is on (ii) point I made. 

The manual addition (to the documentation of sql_mode ONLY_FULL_GROUP_BY)  is about 

"select x, count(y) from test order by x;"

statement failing due to implicit group by. The documentation only makes reference to ER_WRONG_FIELD_WITH_GROUP but no mention of ER_MIX_OF_GROUP_FUNC_AND_FIELDS (Mixing of GROUP columns with no GROUP columns is illegal if there is no GROUP BY clause) is present.

Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant products.

http://dev.mysql.com/doc/refman/5.5/en/server-sql-mode.html#sqlmode_only_full_group_by already says:

"This mode also restricts references to nonaggregated columns in the HAVING clause that are not named in the GROUP BY clause."

I'll modify that to indicate the restriction applies to ORDER BY as well.

@Paul, thanks for that.

However, what about 

"""
Also, 
whereas ER_WRONG_FIELD_WITH_GROUP explicitly checks for Item::FIELD_ITEM type items, ER_MIX_OF_GROUP_FUNC_AND_FIELDS doesn't check that ie. select_lex->non_agg_field_used() also returns true even if the field is not Item::FIELD_ITEM (ie. it is like select '0', <agg-field> FROM ..)

"""

and the fix "by making non_agg_field_used() not return true for non Item::FIELD_ITEM fields"

Should I report a separate bug for this?

To Raghavendra:

The error ER_MIX_OF_GROUP_FUNC_AND_FIELDS that you are getting is not due to " '0' ", but to "order by x" (if I delete the ORDER BY, the query is accepted). The error makes sense: the SQL 2011 standard says that if the ORDER BY clause contains some columns which are not in the SELECT list, then the query should not contain aggregate functions. The proposed query does not comply with that, it's normal to reject it. By the way such ordering would be useless, as the
results surely has one single row (due to aggregation).

Now, to the unrelated documentation issue mentioned in this report, you are right, the documentation should be fixed; we're working on it.