Alternative to removing ONLY_FULL_GROUP_BY

After upgrading from MySQL 5.5 to MySQL 5.7 I'm getting an error with some of my queries:


ERROR 1055 (42000):


Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'grocery.Product_Category.category_id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

I did my research and found the cause of the problem and how to solve it, basically I just need to remove ONLY_FULL_GROUP_BY from @@sql_mode and everything will work again.

However I was wondering if that is the right course of action. Is there an alternative for this, maybe a better way to build the query?

This is my case (http://sqlfiddle.com/#!9/6f1bd):

I have two tables (I simplified their structure here but is basically the same): Product and Category and a many to many relationship table to allow products to belong to more than one category:

SELECT * FROM Product;

+------------+---------+
| product_id | name    |
+------------+---------+
|          1 | Tomato  |
|          2 | Orange  |
|          3 | Banana  |
|          4 | Lettuce |
|          5 | Carrot  |
+------------+---------+
5 rows in set (0,00 sec)

SELECT * FROM Category;

+-------------+------------+
| category_id | name       |
+-------------+------------+
|           1 | Fruits     |
|           2 | Vegetables |
+-------------+------------+
2 rows in set (0,00 sec)

I want to get the products from both categories so the simplest query wold be:

```
SELECT * FROM Product JOIN Product_Category USING(product_id)
JOIN Category USING(category_id);

+-------------+------------+---------+------------+
| category_id | product_id | name | name |
+-------------+------------+---------+------------+
| 1 | 1 | Tomato | Fruits |
| 1 | 2 | Orange | Fruits |
| 1 | 3 | Banana | Fruits |
| 2 | 1 | Tomato | Vegetables |
| 2 |

I would suggest rewriting the query in a way that minimises the number of columns necessary to put into GROUP BY. In your case you can do that by applying the grouping to the Product_Category table only.

According to your example, that table has the following entries:

+------------+-------------+
| product_id | category_id |
+------------+-------------+
|          1 |           1 |
|          2 |           1 |
|          3 |           1 |
|          1 |           2 |
|          4 |           2 |
|          5 |           2 |
+------------+-------------+

Since you want product names to be unique in the output, group this table by product_id, and for category_id select e.g. the minimum value per product:

SELECT
  product_id,
  MIN(category_id) AS category_id
FROM
  Product_Category
GROUP BY
  product_id

That will give you an output like this:

+------------+-------------+
| product_id | category_id |
+------------+-------------+
|          1 |           1 |
|          2 |           1 |
|          3 |           1 |
|          4 |           2 |
|          5 |           2 |
+------------+-------------+

You can see that each product is listed only once. Joining that table to the other two will not produce duplicates. Therefore, just substitute the above query, as a derived table, for the Product_Category in your query (also removing your GROUP BY from it, of course):

SELECT
  *
FROM
  Product
  JOIN (
    SELECT
      product_id,
      MIN(category_id) AS category_id
    FROM
      Product_Category
    GROUP BY
      product_id
  ) AS pc USING(product_id)
  JOIN Category USING(category_id)
;

StackExchange Database Administrators Q#156044, answer score: 5