[TS] this is incompatible with sql_mode=only_full_group_by 해결 방법

본 포스팅은 Mysql에서 group by 절을 사용하다가 발생한 에러에 대한 기록이다.

 

 

발생한 에러

Error 1055: Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'a.author_name' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

Error 1055: Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'a.author_name' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

 

 

Mysql 5.7 이상 버전에서 ONLY_FULL_GROUP_BY 모드가 활성화되어 있을 때 위와 같은 에러가 발생한다.

 

 

group by를 하게 되면 모든 컬럼 값이 각 그룹에 대해 1개의 row만 반환이 된다.

이때에 반환되는 값의 기준은 아래와 같다.

 

1. 집계 함수가 사용된 컬럼:

sum(), count(), avg(), max(), min() 등의 집계 함수에 사용된 컬럼 값은 그룹 내의 모든 값을 고려하여 계산된 결과를 반환한다.

 

2. group by 절에 명시된 컬럼

해당 컬럼의 값은 그 그룹을 정의하는 데에 사용되는 값이므로, 각 그룹 내에서 동일하다.

 

3. 집계 함수나 group by에 포함되지 않은 컬럼

해당 컬럼 값들은 mysql 버전에 따라 반환되는 값이 다르다.

(1) Mysql 5.7 이전 버전에서는 그룹 내의 임의의 값을 선택하여 반환한다. 따라서 예측이 불가능하고 일관성이 없을 수 있다.

(2) Mysql 5.7 이상에서는 sql_mode가 ONLY_FULL_GROUP_BY 이므로, 쿼리 오류로 응답을 한다.

 

 

이런 경우에는

1) 임의의 값이 나올 수 있는 컬럼에 대해 group by 절에서 명시를 하거나

2) 집계 함수를 사용해서 비집계 컬럼의 값을 결정하거나

3) Mysql any_value() 함수를 이용하여 임의의 값이 반환되도록 한다(현재 querydsl에서는 미지원).

 

 

예시

아래의 쿼리의 경우 Mysql 버전에 따라 에러가 발생할수도, 발생하지 않을 수도 있다.

왜냐하면 a.another_name 컬럼이 group by절에 포함되지도, 집계함수가 사용되지도 않았기 때문이다.

SELECT a.author_id, a.author_name, b.category, sum(b.price * bs.sales) as total_sales
from book b
join author a
on b.author_id = a.author_id
join book_sales bs
on b.book_id = bs.book_id
where year(bs.sales_date) = 2022 and month(bs.sales_date) = 1
group by a.author_id, b.category

 

해결 방법

위 문제를 해결하기 위해서는 아래과 같은 방법을 사용할 수 있다.

1. a.another_name 컬럼에 group by 절에 컬럼 추가

SELECT a.author_id, a.author_name, b.category, sum(b.price * bs.sales) as total_sales
from book b
join author a on b.author_id = a.author_id
join book_sales bs on b.book_id = bs.book_id
where year(bs.sales_date) = 2022 and month(bs.sales_date) = 1
group by a.author_id, a.author_name, b.category

 

 

2. a.another_name 컬럼에 집계함수 사용

SELECT a.author_id, MAX(a.author_name) as author_name, b.category, sum(b.price * bs.sales) as total_sales
from book b
join author a on b.author_id = a.author_id
join book_sales bs on b.book_id = bs.book_id
where year(bs.sales_date) = 2022 and month(bs.sales_date) = 1
group by a.author_id, b.category

 

해당 예시에는 max() 집계함수를 사용했다.

 

3.  a.another_name 컬럼에 any_value() 사용 (querydsl에서는 미지원)

SELECT a.author_id, ANY_VALUE(a.author_name) as author_name, b.category, sum(b.price * bs.sales) as total_sales
from book b
join author a on b.author_id = a.author_id
join book_sales bs on b.book_id = bs.book_id
where year(bs.sales_date) = 2022 and month(bs.sales_date) = 1
group by a.author_id, b.category

 

a.another_name 컬럼 값에 대해서 임의의 값이 반환되도록 한 방법이다.

하지만 이는 queydsl에서 아직 지원하지 않는 것 같다 (공식문서에 없다).

 

 

참고

1. mysql any_value()

https://dev.mysql.com/doc/refman/8.4/en/miscellaneous-functions.html#function_any-value

 

2. mysql only_full_group_by

https://dev.mysql.com/doc/refman/8.4/en/sql-mode.html#sqlmode_only_full_group_by