Bug #80726 GROUP BY on derived table, expression of aggregate and group column: error
Submitted: 14 Mar 2016 10:49 Modified: 16 Nov 2016 19:23
Reporter: Guilhem Bichot Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:5.7 OS:Any
Assigned to: CPU Architecture:Any

[14 Mar 2016 10:49] Guilhem Bichot 
Description:
Found by Vemund Ostgaard.

CREATE TABLE `coll` (
  `doc` text
) ENGINE=InnoDB;

SELECT (je+1)+count(*) FROM (SELECT doc+1 AS je FROM coll) AS dt GROUP BY je;
Empty set (0,01 sec)

Move the (), it fails:
SELECT je+(1+count(*)) FROM (SELECT doc+1 AS je FROM coll) AS dt GROUP BY je;
ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'test.coll.doc' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

How to repeat:
see above
[16 Nov 2016 19:23] Paul DuBois 
Posted by developer:
 
Noted in 8.0.1 changelog.

If a query performed a GROUP BY on a column of a derived table and
the select list contained an expression mixing an aggregate function
and the group column, an error was raised if the ONLY_FULL_GROUP_BY
SQL mode was enabled.