[MySQL] only_full_group_by 모드 관련 트러블슈팅: which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

only_full_group_by 모드 - MySQL 트러블슈팅

기본 환경: docker image mysql:latest

 

상황

Clients : Groups = N : M 관계로, 각 클라이언트와 그룹은 여러 개에 속할 수 있음

클라이언트 ID를 기준으로 그룹 정보와 클라이언트 정보를 조인으로 조회하고자 함

 

문제

아래와 같은 쿼리문 실행

SELECT cs.clientId, cs.clientName, cg.groupId, g.groupName
    FROM Clients AS cs
    LEFT OUTER JOIN ClientGroups cg ON cs.clientId = cg.clientId
    LEFT OUTER JOIN `Groups` g ON g.groupId = cg.groupId GROUP BY cs.clientId

에러 메시지 발생

[42000][1055] Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'database.cg.groupId' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

 

시도 및 해결

1. sql_mode 설정으로 해결하기

- sql_mode 조회

SELECT @@sql_mode;

SELECT @@session.sql_mode;

-- 기본 : ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

- sql_mode 변경: ONLY_FULL_GROUP_BY 제외한 값으로 설정

-- 모든 설정 초기화
SET GLOBAL sql_mode ='';
SET SESSION sql_mode = '';

-- ONLY_FULL_GROUP_BY 를 제외하고 설정
SET SESSION sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

 

2. ANY_VALUE() 함수를 활용해 쿼리로 해결하기

SELECT cs.clientId, cs.clientName, cg.groupId, g.groupName
    FROM Clients AS cs
    LEFT OUTER JOIN ClientGroups cg ON cs.clientId = cg.clientId
    LEFT OUTER JOIN `Groups` g ON g.groupId = cg.groupId GROUP BY cs.clientId

 

 

 

 

알게 된 점

SQL only_full_group_by 모드

MySQL에서 사용되는 SQL_MODE

GROUP BY 절에서 선택되지 않은 컬럼이나 함수를 사용할 때 발생하는 에러를 방지함

 

만약 only_full_group_by 모드가 설정되어 있지 않으면, 위에서 말한 에러가 발생하지 않음. 하지만 해당 모드를 해제하면 데이터베이스에서 잘못된 결과를 출력할 수 있기 때문에, 데이터 일관성에 문제가 생길 수 있음.

예시:

SELECT column1, column2, AVG(column3)
FROM table1
GROUP BY column1

위 SQL 쿼리에서 column2는 GROUP BY 절에 넣지 않은 컬럼

only_full_group_by 모드가 설정되어 있지 않은 경우, 문제 없이 실행되나 column2는 column1 별로 다른 값을 가질 수 있어 결과가 불일치할 가능성이 높음

따라서, only_full_group_by 모드는 데이터 일관성을 유지하기 위해 필요한 설정임

 

only_full_group_by 를 설정 방법:

SET GLOBAL sql_mode=(SELECT CONCAT(@@sql_mode, ',ONLY_FULL_GROUP_BY'));

 

cf.

각 RDMS 환경마다 sql모드 기본값이 다름

Docker MySQL에서는 위에서 나왔던 것처럼 모드에 여러 값이 지정되어 있었으나, AWS RDS에서는 가장 낮은 모드 (1~2개)가 기본값이었음 

 

 

참고자료

• only_full_group_by | MySQL :: MySQL 5.7 Reference Manual :: 12.20.3 MySQL Handling of GROUP BY
• MySQL :: MySQL 5.7 Reference Manual :: 5.1.10 Server SQL Modes
• 유사 문제 해결 사례