user3420034 user3420034 - 1 year ago 57
MySQL Question

After updating MySQL, getting strange JOIN errors from 'sql_mode=only_full_group_by'

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 '' which is not
functionally dependent on columns in GROUP BY clause; this is
incompatible with sql_mode=only_full_group_by

My table:

id int(255)
date datetime

My query:

FROM playtime

All it does is tell me how many users first joined on a particular date, allowing me to display this data in a graph.

I'm unsure exactly what's wrong with this query? I get that there must be new syntax rules in this new version of MySQL, but I'm not sure exactly how to follow them with this query. Any ideas?

Second example causing the same issue:


id int(11)
user varchar(255)
course varchar(255)
course_difficulty varchar(255)
time_taken int(255)


SELECT course, course_difficulty, time_taken
FROM minigame
WHERE user = 'abc'
GROUP BY course
ORDER BY time_taken LIMIT 50

Answer Source

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