SQL Query in MySQL 5.7.17

Question

I've just upgraded MySQL to 5.7.17 because of the JSON support. Some changes in the project required this upgrade. Just one SQL Query is not working as expected. I get an error: ER_WRONG_FIELD_WITH_GROUP

I've googled it, and most answers were: Disable ONLY_FULL_GROUP_BY in the sql mode. But that's just working around the problem instead of fixing it. Correct me if I'm wrong.

My SQL that is giving problems:

SELECT users.*, 
       GROUP_CONCAT(d_user_link.public_key ORDER BY d_user_link.id) AS linked_devices, 
       permission_users.group_id, 
       permission_users.custom_permissions, 
       permission_groups.group_name, 
       permission_groups.group_permissions 
FROM users 
INNER JOIN permission_users 
  ON permission_users.user_id = users.id 
INNER JOIN permission_groups 
  ON permission_groups.id = permission_users.group_id 
LEFT JOIN d_user_link
  ON d_user_link.user_id = users.id 
  AND d_user_link.disabled = 0 
WHERE users.id = ? 
GROUP BY users.id

The d_user_link doesn't have results for every user, because some users can see every device (d_user_link is device-user-link), or don't have any linked devices.

I hope you can help me with this problem. I couldn't get to an answer by googling it other that disabling ONLY_FULL_GROUP_BY.

Thanks!

Possible duplicate of [MySQL : isn't in GROUP BY](http://stackoverflow.com/questions/25800411/mysql-isnt-in-group-by) — Shadow, Mar 16 '17 at 09:29
`users.id` appears twice in your select, once in `users.*` and again as `user_group_id`. This isn't the only issue, btw — JohnHC, Mar 16 '17 at 09:29
Ah, that was for testing purposes. It isn't in the query anymore @JohnHC. Thanks! Which issues do you see? — Bart Versluijs, Mar 16 '17 at 09:32
I think you need to `group by` columns from `permission_groups` that are part of your select. Not sure in what version of MySQL this change was introduced, but could be this one. MySQL until recently had non standard implementation of GROUP BY. — Łukasz Kamiński, Mar 16 '17 at 09:37
@BartVersluijs the duplicate topic describes what you need to do to get rid of the error in the question. — Shadow, Mar 16 '17 at 09:37
@ŁukaszKamiński MySQL only changed the **default** setting for handling group by clause. You could configure earlier versions of MySQL to comply with the sql standard before as well. — Shadow, Mar 16 '17 at 09:39
@Shadow I've added `permission_users.id` to my GROUP_BY. That helped to fix the problem. I hope this was the only issue. I couldn't find that answer on Google. — Bart Versluijs, Mar 16 '17 at 09:44

Gordon Linoff · Answer 1 · 2017-03-16T11:43:55.997

The fundamental problem with this query is the permissions. In earlier versions of MySQL, your query would be returning one row per user with arbitrary permissions on it. That is, it arbitrarily discarded permissions for a user. I would consider this a bug.

That makes it hard to upgrade the query.

When you add permissions to the group by, you will get multiple rows per user. That is probably not the intention. One option is to remove permissions entirely from the query. Another is to group them together:

SELECT u.*, 
       GROUP_CONCAT(ul.public_key ORDER BY ul.id) AS linked_devices, 
       GROUP_CONCAT(pu.group_id ORDER BY pu.group_id) as group_ids, 
       GROUP_CONCAT(pu.custom_permissions ORDER BY pu.group_id) as custom_permissions, 
       GROUP_CONCAT(pg.group_name ORDER BY pu.group_id) as group_name, 
       GROUP_CONCAT(pg.group_permissions  ORDER BY pu.group_id) as group_permissions
FROM users u INNER JOIN
     permission_users pu
     ON pu.user_id = u.id INNER JOIN
     permission_groups pg
     ON pg.id = pu.group_id LEFT JOIN
     d_user_link ul
     ON ul.user_id = u.id AND ul.disabled = 0 
WHERE u.id = ? ;

Thanks for the sql! It works like expected. Only typo is that you used `dl` in first group_concat, but defined it as `ul` later in the sql. Couldn't edit it because of 6 character minimum. The permission_groups only has 1 row for each user, so that wasn't a problem for me. I'll use this SQL from now on. — Bart Versluijs, Mar 16 '17 at 10:39

SQL Query in MySQL 5.7.17

1 Answers1