MySQL Bugs: #86475: Error with functions and group by with ONLY_FULL_GROUP

Bug #86475	Error with functions and group by with ONLY_FULL_GROUP_BY
Submitted:	26 May 2017 10:51	Modified:	30 May 2017 10:03
Reporter:	Arnaud Adant	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S4 (Feature request)
Version:	5.7.18	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
When a function is used in the group by clause, calculation should be possible on the select clause from the grouped expressions.

For example using coalesce, pow, mod functions :

mysql> select a,coalesce(b,1)*a from t group by a,coalesce(b,1);
ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'test.t.b' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

How to repeat:
create table t(id int auto_increment primary key, a int, b int);
select a,coalesce(b,1)*a from t group by a,coalesce(b,1);

Suggested fix:
Support this use case or document it does not work.

Hi Arnaud. It looks like you're hitting this:
http://mysqlserverteam.com/when-only_full_group_by-wont-see-the-query-is-deterministic/

I'm making this report as a feature request, and asking our Docs team to clarify.
at the end of https://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html .

Hi Guilhem,

Thank you for your reply. Indeed, any_value is a workaround :

select a,any_value(coalesce(b,1)*a) from t group by a,coalesce(b,1);

I guess this could be documented here :

https://dev.mysql.com/doc/refman/5.7/en/group-by-functional-dependence.html#functional-dep...

Maybe this is more a documentation request. 

The feature request looks like a duplicate of this one :

https://bugs.mysql.com/bug.php?id=80660

Posted by developer:
 
This is also a problem for window functions:

SELECT first_value(abs(a)) OVER () FROM t GROUP BY abs(a);
ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'mysql.t.a' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

Workaround: use ANY_VALUE.