Incompatible With sql_mode=only_full_group

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 show this error when i try 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.

i need your help guys.

CodePudding user response：

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