[SQL] ONLY_FULL_GROUP_BY 모드는 에러가 아니다

📌 개요

---

🔹 SQL 쿼리문을 작성하다 보면 `this is incompatible with sql_mode=only_full_group_by` 라는 문장을 볼 수 있다. 바로 MySQL의 ONLY_FULL_GROUP_BY 모드에 의해 발생한 에러이다. 이러한 에러 코드를 보고 해결방법을 알기 위해 검색해본 결과 SQL_MODE의 옵션 중 only_full_group_by를 비활성화 하라고 나온다.

 

하지만, 이는 좋은 해결방법은 아니라고 생각한다. 그 순간의 해결은 가능하겠지만 비활성화 이후 잘못된 쿼리들로 인해 발생될 문제들을 무시하는 해결 방법이기 때문이다. 제대로 해결하고 싶다면 only_full_group_by에 위배되지 않는 정확한 쿼리문을 작성하는 것이 더 좋은 방법이라고 생각한다. 이런 생각을 하게 된 이유에 대해서 알아보자

 

🖋  GROUP BY & HAVING

---

특정 컬럼을 그룹화 하는 GROUP BY
특정 컬럼을 그룹화한 결과에 조건을 거는 HAVING

 

GROUP BY 와 함께 사용되는 집계함수

• COUNT : 총 갯수
• COUNT(DISTINCT) : 중복없이 총 갯수
• SUM : 합계
• AVG : 평균
• MAX : 최대
• MIN : 최소

[예제 쿼리]

 

idx	type	name
1	1	안중근
2	1	윤봉길
3	2	김유신
4	2	이순신
5	3	이성
6	3	왕건
7	4	세종대왕

 

☛ Type에 대한 그룹화를 실행하며 그 Type 그룹에 속한 이름이 몇개인지 구하는 쿼리문

SELECT type, COUNT(name) AS cnt FROM collection GROUP BY type;

type	cnt
1	2
2	2
3	2
4	1

 

☛ Type에 대한 그룹화를 실행하며 그룹화한 곳의 개수가 2 이상인 이름이 몇개인지 구하는 쿼리문

SELECT type, COUNT(name) AS cnt FROM collection GROUP BY type HAVING cnt >= 2;

type	cnt
1	2
2	2
3	2

 

🖋  ONLY_FULL_GROUP_BY 모드

---

[해당 옵션이 활성화되어있을 경우]

MySQL은 선택 목록, 조건 또는 목록이 절에 명명되지 않았거나 기능적으로 종속되지 않은 비집계 열을 참조하는 쿼리를 HAVING 거부한다.

즉, mysql이 지정한 group by 표준 규칙에 맞게 쿼리를 수정해야 한다는 의미이다.

 

[예제 쿼리]

select name, countrycode, sum(population)
from city
where countrycode='AFG';

 

☛ 위의 쿼리는 일반 컬럼인 name과 집계함수 sum(population)이 함께 수행되고 있어서 말이 안된다고 생각할 것이다. 하지만 기본적으로 mysql에서는 말이 된다. 이러한 말이 안되는 쿼리에 대해서 MySQL은 v5.7 이후 버전업되면서 SQL 문법이 이전보다 엄격해지며 ONLY_FULL_GROUP_BY 모드에 의해 에러가 발생하게 된다.

Error Code: 1140. In aggregated query without GROUP BY, 
expression #1 of SELECT list contains nonaggregated column 'world.city.Name'; 
this is incompatible with sql_mode=only_full_group_by

 

이러한 에러를 해결하기 위해서 알려진 방법으로는 두가지가 있다. 

1) Query 수정

GROUP BY를 사용할 경우, SELECT 할 수 있는 컬럼은

 

1. GROUP BY에 나열된 컬럼 

2. COUNT(), AVG(), SUM()과 같은 집계함수

 

위의 두가지 경우에만 사용할 수 있다.

따라서 문제를 해결하기 위해서는

 

1. 쿼리 상의 GROUP BY 절을 따로 분리해 준다.

ex)

SELECT A, (SELECT COUNT(*) FROM (SELECT B FROM C))
FROM F GROUP BY B

# GROUP BY B 추가
SELECT A, (SELECT COUNT(*) FROM (SELECT B FROM C GROUP BY B))
FROM F

 

2. 쿼리 상의 GROUP BY 절에 해당 에러가 발생하는 Column을 추가해준다.

ex) 

SELECT A, (SELECT COUNT(*) FROM (SELECT B FROM C GROUP BY D, E))
FROM F GROUP BY D

# GROUP BY에 B 추가
SELECT A, (SELECT COUNT(*) FROM (SELECT B FROM C GROUP BY D, E))
FROM F GROUP BY D, B

 

3. ANY_VALUE 를 사용.

ex) 

SELECT A, (SELECT COUNT(*) FROM (SELECT B FROM C GROUP BY D, E))
FROM F GROUP BY D

# B에 ANY_VALUE 추가
SELECT A, (SELECT COUNT(*) FROM (SELECT ANY_VALUE(B) FROM C GROUP BY D, E))
FROM F GROUP BY D

2) sql_mode 옵션 중 only_full_group_by 비활성화

첫번째, SET GLOBAL sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

두번째, SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));

세번째, SET @@sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

 

👻  결론

---

🔹위의 내용을 다 읽고난 후 개요를 보게 된다면 내용에 대한 이해가 더 수월할 것이다. 위의 내용처럼  ONLY_FULL_GROUP_BY 모드는 group by 표준 규칙에 맞지 않는 쿼리를 수정하라는 의미이고, 이를 단순 해결하기 위해 SQL mode를 비활성화 한다면 후에 문제가 생길 가능성이 크다는 이야기이다. 즉, 쿼리의 수정을 통해 올바르지 않은 쿼리문을 수정하여 문제를 해결하는 것이 바람직하다.

 

📸 참조

---

https://velog.io/@devjooj/MySQL-EP-1.-GROUP-BY