mysql - How to fix query group with only_full_group_by

Question

I have a basic key-value table, that has some data in it for each user. With the updated mysql it has the sql_mode set to only_full_group_by (new default) when you do a group by. When I try to run this simple query:

select * from user_features
where user_id = 1
group by feature_key

I get the following error:

SQL Error (1055): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'date.user_features.user_id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

With this example data, I want to group based on the feature_key (I will add a group_concat once the group error is fixed).

| user_id | feature_key | feature_value |
+---------+-------------+---------------+
| 1       | color       | red           |
+---------+-------------+---------------+
| 1       | age         | 15            |
+---------+-------------+---------------+
| 1       | color       | blue          |
+---------+-------------+---------------+

The table looks like this:

CREATE TABLE `user_features` (
  `user_id` int(10) unsigned NOT NULL,
  `feature_key` varchar(50) NOT NULL,
  `feature_value` varchar(50) NOT NULL,
  UNIQUE KEY `user_id_feature_key_feature_value` (`user_id`,`feature_key`,`feature_value`)
)

What query can I run to fix this or what index(es) do I need to add?

Answer 1

This is a common error for MySQL users. In MySQL 5.7, by default the database enforces the standard semantics that most other SQL databases have been enforcing for years.

The rule is that every column in your select-list must be one of:

• Named in the GROUP BY clause; i.e. it's what you are grouping be.
• Inside an aggregate function like MIN, MAX(), SUM(), GROUP_CONCAT(), etc.
• Functionally dependent on the column you are grouping by (this is MySQL's extension to standard SQL behavior, and other SQL databases don't necessarily support this).

In your query (I'll expand your SELECT *):

select user_id, feature_key, feature_value from user_features
where user_id = 1
group by feature_key

You are grouping by feature_key, but this means the other columns don't comply with the rules I described above.

Here's a way to fix it:

select MAX(user_id), feature_key, GROUP_CONCAT(feature_value)
from user_features
where user_id = 1
group by feature_key

It might seem redundant to use MAX(user_id) since there is only one value possible based on the WHERE clause condition. But there's no harm either. MIN(user_id) would also work.

See also my past answers on this same error:

• https://stackoverflow.com/a/47701851/20860
• https://stackoverflow.com/a/38705647/20860