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
Triage: Needs Triage: D5 (Feature request)

[26 May 2017 10:51] Arnaud Adant
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.
[29 May 2017 7:10] Guilhem Bichot
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 .
[30 May 2017 10:03] Arnaud Adant
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
[16 Aug 2017 18:12] Dag Wanvik
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.