Incompatible With sql_mode=only_full_group_by in docker

Question

First of all i have this query:

SELECT stores.*,
       AVG(reviews.rating) AS rating,
       SQRT(POW(69.1 * (store_address.latitude - 0.0), 2) + POW(69.1 * (0.0 - store_address.longitude) * COS(store_address.latitude / 57.3), 2)) AS distance
FROM stores
LEFT JOIN store_address ON store_address.store_id = stores.id
LEFT JOIN products ON products.store_id = stores.id
LEFT JOIN reviews ON reviews.product_id = products.id
LEFT JOIN store_product_promotions ON store_product_promotions.store_id = stores.id
AND store_product_promotions.start_date <= :startDate
AND store_product_promotions.end_date >= :endDate
AND store_product_promotions.active = 1
WHERE stores.site_id = :siteId
GROUP BY stores.id
ORDER BY stores.created_at DESC
LIMIT :limit
OFFSET :offset

When i run in navicat, it worked perfectly, but shows this error when i tried this in postman:

nested exception is io.r2dbc.spi.R2dbcBadGrammarException: [1055] [42000] Expression #13 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'ecommerce.store_address.latitude' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

I usually and was tried to remove sql_mode by run this SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY','')); and somehow this error still occured.

What can be done to solve the issue?

See [why you shouldn't disable `ONLY_FULL_GROUP_BY`](https://stackoverflow.com/questions/64824498/why-should-not-disable-only-full-group-by/64831540#64831540). — danblack, Jul 08 '22 at 00:11
As you are grouping by the `store_id`, and stores can have multiple addresses, which address long/lat do you wish to be used in the result? — danblack, Jul 08 '22 at 00:14
@danblack You're assuming there's a 1-to-many relationship between `stores` and `store_address`. I suspect it's 1-to-1. — Barmar, Jul 08 '22 at 00:17

score 1 · Accepted Answer · answered Jul 08 '22 at 00:20

Even if a store can only have one address, the SQL engine doesn't know this. So when you join with store_address, it assumes it can be a 1-to-many relationship, and doesn't allow you to use columns from store_address without aggregation -- it doesn't know that the address is uniquely determined from stores.id.

You can solve this by using a trivial aggregation with MAX().

SELECT stores.*,
       AVG(reviews.rating) AS rating,
       SQRT(POW(69.1 * (MAX(store_address.latitude) - 0.0), 2) + POW(69.1 * (0.0 - MAX(store_address.longitude)) * COS(MAX(store_address.latitude) / 57.3), 2)) AS distance

Incompatible With sql_mode=only_full_group_by in docker

1 Answers1