MariaDB 에서 GROUP BY 구문 사용시 주의할 점

MariaDB 에서 GROUP BY 사용 시 주의 점

 

개요

MSSQL 로만 쿼리문을 작성하다가 신규 프로젝트 때문에 MariaDB을 처음 접하게 되었을 때의 일이었다.
다른 건 비슷비슷하다고 생각했었는데, 아니 다른 개발자들의 쿼리문을 봤을 때 신기한 점을 발견하였다.

SELECT H_CODE, AREA1, AREA2, AREA3 FROM 행정동
GROUP BY H_CODE

 
위의 쿼리를 보면 이상한 점이 있어 보이는가?
 
나의 관점에서는 GROUP BY 로 선언된 필드 외에 다른 필드를 노출할 때는 집계 함수인 MIN, MAX, AVG 가 나와야 하는데 없던 것이었다. 당연히 문법 오류로 실행이 안될 거라 생각됐는데, 웬일 실행이 되는 게 아닌가!!!
알고 보니 MariaDB 에서는 데이터베이스 설정에 따라 강제할 수도 있고 아닐 수도 있었다.
 
그럼 데이터베이스 설정을 통해 GROUP BY 절 사용시 집계 함수에 묶이지 않은 칼럼에 대해 오류를 발생토록 해보겠다.
 

SQL MODE 설정

글로벌 설정

[mysqld]
sql_mode = ONLY_FULL_GROUP_BY

 

특정 세션 설정

SET SESSION sql_mode = 'ONLY_FULL_GROUP_BY';

 
위의 설정을 적용하고, 다시 위의 쿼리를 실행해보았다.
 

 
위의 예제처럼 sql_mode 설정을 통해 강제할 수 있었다. 
MariaDB에서는 위의 설정 말고 어떤 sql_mode 가 있을까? ChatGPT의 도움으로 정리를 해보았다.
 

sql_mode 종류

• ONLY_FULL_GROUP_BY : GROUP BY 절에서 그룹화되지 않은 칼럼을 집계 함수로 묶지 않을 경우 오류를 발생시킵니다.
• STRICT_TRNAS_TABLES : 트랜잭션 테이블에서 엄격한 SQL 모드를 활성화합니다. 데이터 유효성 검사 실패 시 경고가 아닌 오류를 발생시킵니다.
• STRICT_ALL_TABLES : 모든 테이블에 대해 엄격한 SQL 모드를 활성화합니다. 데이터 유효성 검사 실패 시 경고가 아닌 오류를 발생시킵니다.
• NO_ZERO_IN_DATE : 0000-00-00과 같은 날짜를 허용하지 않습니다. 날짜와 시간의 각 부분이 0이 아닌지 확인합니다.
• NO_ZERO_DATE : 0000-00-00 날짜를 허용하지 않습니다. 날짜 필드에 잘못된 날짜가 들어갈 경우 오류를 발생시킵니다.
• ERROR_FOR_DIVISION_BY_ZERO : 0으로 나누기를 시도할 때 경고 대신 오류를 발생시킵니다.
• NO_AUTO_CREATE_USER : GRANT 문을 사용하여 사용자 생성 시 암호 없이 사용자를 생성하지 못하도록 합니다.
• NO_ENGINE_SUBSTITUTION : 지정된 스토리지 엔진이 없을 경우 기본 엔진으로 대체하는 것을 금지합니다. 지정된 엔진이 없으면 오류를 발생시킵니다.
• ANSI_QUOTES : 큰따옴표(") 식별된 구분자로 사용합니다. 이는 SQL 표준과 호환성을 유지하기 위한 모드입니다.
• PIPES_AS_CONCAT : 파이프 연산자(||)를 문자열 연결 연산자를 해결합니다.

위의 sql_mode 설정은 최초 데이터베이스 서버 구축 시에 설정을 해야 좋을 것 같다.
중간에 변경하면 기존엔 경고로 나왔던 내용들이 오류를 발생시킬 거라 해당 애플리케이션에 많은 문제가 있을 것이다.
 

데이터 조회 기준

그렇다면, GROUP BY 절로 지정되지 않은 필드들은 어떤 데이터를 조회할까?
MIN 값일까? 아니면 MAX 값일까, 아니면 랜덤으로 데이터를 조회할까?
 
그것은 바로 MIN 도, MAX 도, 랜덤도 아닌 첫 번째 데이터를 조회한다고 한다.

#수정

위와 같이 결론을 내렸었는데.. 블로그 본 글을 작성하고 다시 또 실행해보니 무슨 기준인지 모르겠다.. -_-

그래서 반드시 집계함수를 쓰도록 해야겠다.
 
아래의 예제를 통해 확인해 보도록 하겠다.

 
AREA2 필드가 칠곡군인 데이터만 조회해 보았다.
위의 데이터를 기준으로 AREA1, AREA2 필드로 GROUP BY로 묶고, AREA3 필드에 어떤 데이터가 조회되는지 확인해 보겠다.
아마 가장 첫 데이터인 빈값이 조회될 것이다.

 

결론

개인적으로는 ONLY_FULL_GROUP_BY 모드를 사용하는 것이 좋을 것 같다.
GROUP BY에 포함되지 않은 필드는 반드시 집계함수를 통해 명확하게 조회하는 것이다.
 
하지만, 이미 운영 중인 데이터베이스일 경우 많은 고민이 필요하다.
혹시 GROUP BY 구문시 집계 함수 없이 사용한 필드로 개발한 쿼리가 있을지도 모르기 때문에 전체 검수가 필요하다.