MySQL Bugs: #90792: ONLY_FULL_GROUP_BY CONCAT and FUNCTIONS

Bug #90792	ONLY_FULL_GROUP_BY CONCAT and FUNCTIONS
Submitted:	8 May 2018 3:46	Modified:	8 May 2018 8:58
Reporter:	Geoffrey Wiseman	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S3 (Non-critical)
Version:	5.7	OS:	Fedora
Assigned to:		CPU Architecture:	x86

Description:
ONLY_FULL_GROUP_BY is in general something I like the sounds of, but it seems to complain about queries that, near as I can tell, it shouldn't. Notably, if you have functions and concat together, it seems to get lost.

By way of example, this query:

select 
  YEAR(applicationDate) as year, 
  MONTH(applicationDate) as month, 
  count(*) as applications,
  CONCAT( count(*), 'applications in ', YEAR(applicationDate), '-', MONTH(applicationDate))
from APPLICATION
group by YEAR(applicationDate), MONTH(applicationDate)

It works fine in MySQL 5.6 (without ONLY_FULL_GROUP_BY) but fails in MySQL 5.7 where ONLY_FULL_GROUP_BY id enabled by default.

See another example someone else posted to StackOverflow:
https://stackoverflow.com/questions/49447622/mysql-only-full-group-by-enabled-with-concat

Is this a bug? A limitation in how viable ONLY_FULL_GROUP_BY can be? If it can't handle a wider range of expressions, I'm not sure it should be on by default, although I would prefer it on and just ... better.

How to repeat:
https://stackoverflow.com/questions/49447622/mysql-only-full-group-by-enabled-with-concat

select 
  YEAR(applicationDate) as year, 
  MONTH(applicationDate) as month, 
  count(*) as applications,
  CONCAT( count(*), 'applications in ', YEAR(applicationDate), '-', MONTH(applicationDate))
from APPLICATION
group by YEAR(applicationDate), MONTH(applicationDate)

Suggested fix:
Better parsing of expressions for ONLY_FULL_GROUP_BY.

Hi, 

I will verify this as a bug as I do believe it is a bug but it's possible that something in the SQL standard requires us to behave like this! So we'll see what the team that need to fix this will say.

SELECT list is not in GROUP BY clause and contains nonaggregated column '...applicationDate' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

so we have year(x) and month(x) used in select and we are doping group by year(x), month(x) so this should work...

especially as without concat it works

mysql> select
    ->   YEAR(applicationDate) as year,
    ->   MONTH(applicationDate) as month,
    ->   count(*) as applications,
    ->   CONCAT( count(*), 'applications in ', YEAR(applicationDate), '-', MONTH(applicationDate))
    -> from t1
    -> group by YEAR(applicationDate), MONTH(applicationDate);
ERROR 1055 (42000): Expression #4 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'd1.t1.applicationDate' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
mysql> select
    ->   YEAR(applicationDate) as year,
    ->   MONTH(applicationDate) as month,
    ->   count(*) as applications
    ->   -- CONCAT( count(*), 'applications in ', YEAR(applicationDate), '-', MONTH(applicationDate))
    -> from t1
    -> group by YEAR(applicationDate), MONTH(applicationDate);
+------+-------+--------------+
| year | month | applications |
+------+-------+--------------+
| 2000 |    12 |            1 |
| 2018 |     5 |           11 |
+------+-------+--------------+
2 rows in set (0.00 sec)

mysql>

Hi Geoffrey,

This should solve your problem:

select year, month, applications,
       CONCAT(applications, 'applications in ', year, '-', month)
from
(SELECT YEAR(applicationDate) as year,
        MONTH(applicationDate) as month,
        count(*) as applications
 from APPLICATION
 group by YEAR(applicationDate), MONTH(applicationDate))
as derived;

It does the CONCAT on the outside, by using a derived table.
More background here:
https://mysqlserverteam.com/when-only_full_group_by-wont-see-the-query-is-deterministic/

For the query on stackoverflow, this should work:

SELECT CONCAT(week, '-', year) as date, model
FROM
(
SELECT WEEKOFYEAR(FROM_UNIXTIME(unix_time)) as week,
       YEAR(FROM_UNIXTIME(unix_time)) as year,
  AVG(price_usd) AS model
FROM price_avg
INNER JOIN reference ON reference.id_reference=price_avg.id_reference
WHERE price_avg.id_reference=1
GROUP BY WEEKOFYEAR(FROM_UNIXTIME(unix_time)), 
  YEAR(FROM_UNIXTIME(unix_time)),
  price_avg.id_reference
ORDER BY year, week ASC
)
AS derived;

It's the same trick. Note that I had to change ORDER BY, as unix_time isn't determined by the group expressions (week, year).

Another possible solution is, starting from the original query, to wrap in ANY_VALUE the expression which MySQL complains about:

https://dev.mysql.com/doc/refman/8.0/en/miscellaneous-functions.html#function_any-value
"This function is useful for GROUP BY queries when the ONLY_FULL_GROUP_BY SQL mode is enabled, for cases when MySQL rejects a query that you know is valid for reasons that MySQL cannot determine. "