So I just updated to the latest MySQL, and I'm getting this error:
Expression #1 of SELECT list is not in GROUP BY clause and contains
nonaggregated column 'stats.playtime.date' which is not
functionally dependent on columns in GROUP BY clause; this is
incompatible with sql_mode=only_full_group_by
SELECT DATE(date) + INTERVAL 1 DAY, COUNT(DISTINCT id)
GROUP BY DATE(date)
SELECT course, course_difficulty, time_taken
WHERE user = 'abc'
GROUP BY course
ORDER BY time_taken LIMIT 50
In the first example, the MySQL optimizer isn't smart enough to recognize the the expression
DATE(date) + INTERVAL 1 DAY is functionally dependent on
DATE(date). The workaround for that is to include the entire expression in the GROUP BY clause.
In the second example, MySQL is griping about the non-aggregates in the SELECT list which aren't in the GROUP BY clause, again, because they aren't functionally dependent.
Here's some example table data that demonstrates the rationale behind the error:
id user course -- ---- ------ 11 fee win101 12 fi win101 13 fo win101
When we attempt to run this query:
SELECT t.course, t.user FROM mytable t GROUP BY t.course
There are three possible values that could be returned for
user... fee, fi or fo.
MySQL will allow the query to run if we use an aggregate expression in place of the column. In this example, we could use MIN(t.user) or MAX(t.user), and MySQL would return "fee" or "fo", respectively.
MySQL will run this query:
SELECT t.course , MIN(t.user) AS min_user , MAX(t.user) AS max_user , COUNT(1) FROM mytable t GROUP BY t.course