[청하] 맞춤 & 핫한 정책 조회 기능 - MySQL의 ONLY_FULL_GROUP_BY 오류 해결

2024.09.30

문제 상황

Spring Boot 기반의 청년 정책 플랫폼에서 “맞춤 정책 추천”과 “핫한 정책 조회” 실행 시 다음과 같은 SQL 오류가 발생했다.

로그 내용 일부

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

• SELECT 절에 포함된 컬럼 중 일부가 GROUP BY 절에 없고, 집계 함수로 감싸져 있지 않기 때문에 오류가 발생했다.

→ MySQL에서 ONLY_FULL_GROUP_BY 모드가 활성화되어 있는 경우, 이러한 쿼리는 허용되지 않는다.

 

원인 분석

기존 쿼리 (YouthPolicyRepository)

@Query("SELECT p FROM YouthPolicy p " +
        "LEFT JOIN ViewPolicy v ON p.id = v.policyId " +
        "LEFT JOIN FavoritePolicy f ON p.id = f.policyId " +
        "GROUP BY p.id " +
        "ORDER BY (COALESCE(SUM(v.viewCount), 0) * 1 + COALESCE(COUNT(f), 0) * 3) DESC")
List<YouthPolicy> findHotPolicies();

이 쿼리는 각 정책별로 조회수 + 찜하기 수에 기반한 가중치를 계산해 핫한 정책을 정렬하는 로직이다. 하지만 SELECT 절에는 p.id 외에도 여러 컬럼이 포함되어 있는데, GROUP BY 절에는 p.id만 포함되어 있어서 ONLY_FULL_GROUP_BY 모드에서 실행 시 오류가 발생한다.

 

해결 방법

1. ONLY_FULL_GROUP_BY 비활성화 (사용X)

해당 모드를 MySQL 설정에서 끄는 것도 가능하지만, 이 방법은 데이터베이스 전체에 영향을 주는 설정이기 때문에 혹시나 발생할 문제를 방지하기 위해 사용하지 않았다.

 

2. 쿼리 수정

@Query("SELECT p FROM YouthPolicy p " +
       "LEFT JOIN ViewPolicy v ON p.id = v.policyId " +
       "LEFT JOIN FavoritePolicy f ON p.id = f.policyId " +
       "GROUP BY p.rnum, p.id, p.title, p.region, p.classification, f.id " +
       "ORDER BY (COALESCE(SUM(v.viewCount), 0) * 1 + COALESCE(COUNT(f.policyId), 0) * 3) DESC")
List<YouthPolicy> findHotPolicies();

• SELECT p는 YouthPolicy 엔티티의 모든 컬럼을 포함하는 쿼리이므로, ONLY_FULL_GROUP_BY 모드에서는 모든 컬럼을 GROUP BY 절에 명시하거나 집계 함수로 감싸야 한다.
• 하지만 실제 서비스에서는 DTO 변환 시 사용하는 필드만 필요하기 때문에 GROUP BY 절에는 rnum, id, title, region, classification 등 필요한 필드만 명시하는 방식으로 쿼리를 수정했다.
• COUNT(f.policyId)와 SUM(v.viewCount)는 모두 집계 함수이므로 GROUP BY에 포함하지 않아도 된다.
• f.id는 집계 대상이 아니지만, FavoritePolicy 테이블이 조인되어 있고, Hibernate가 내부적으로 참조하는 경우를 대비해 보수적인 관점에서 포함시켰다.
• ViewPolicy는 SUM(v.viewCount) 외에 직접 참조되는 필드가 없기 때문에 v.id는 생략하였다.

⇒ SELECT 대상 컬럼을 모두 GROUP BY 절에 포함하여 쿼리 수정

 

+ f.id를 GROUP BY에 포함한 이유

쿼리에서 f.id는 SELECT나 ORDER BY 절에 명시적으로 사용되진 않지만, FavoritePolicy 테이블과의 조인 결과가 여러 행(row)을 만들 수 있기 때문에, 정책(p) 하나에 여러 개의 찜(f)이 연결되는 상황에서는 내부적으로 f.id가 결과에 포함될 수 있다.

JPA에서는 SELECT p 처럼 엔티티 전체를 조회할 경우, Hibernate가 조인된 테이블(f)의 식별자(f.id)까지 추적하거나 결과 매핑 시 활용할 수 있다.

이때 f.id를 GROUP BY에 포함하지 않으면 MySQL의 ONLY_FULL_GROUP_BY 모드에서는 오류가 발생할 수 있다.

⇒ 조인된 테이블이 여러 row를 반환하는 경우에도 오류없이 동작하도록 하기 위해 f.id를 GROUP BY 절에 포함했다.