[Mariadb] Window functions not working in ONLY_FULL_GROUP_BY mode II

세줄 요약

원인 : ONLY_FULL_GROUP_BY mode II 에서 Window functions 를 사용할 수 없음

해결방법 1 : sql mode에서 ONLY_FULL_GROUP_BY  제거

해결방법 2 : mariadb upgrade

---

 

oracle을 사용했던 유저로써 window function은 참으로 유혹적인 기능이다.

mysql과 mariadb에서도 사용이 가능하다는걸 알게 되고 너무나도 행복했다.

그래서 바로 사용을 하려고했으나, 

ERROR 1140 (42000): Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP

가 떴다.

https://mariadb.com/kb/en/mariadb-error-codes/

 

이 에러에 대해서 알고자 한다면 일단 only_full_group_by 옵션을 알아야한다.

 

ONLY_FULL_GROUP_BY

https://24hours-beginner.tistory.com/159

[Mysql] ONLY_FULL_GROUP_BY (Maria DB)

mysql과 mariadb에는 sql_mode를 지원한다.

database의 규칙을 커스터마이징 할 수 있다.

-- 세션 sql mode 조회
SELECT @@SQL_MODE, @@SESSION.sql_mode;
-- 글로벌 sql mode 조회
SELECT @@GLOBAL.SQL_MODE;

그 중 only_full_group_by 라는 옵션이 있는데

쉽게 말하면 그룹함수가 아닌 select절에 오는 모든 항목은 group by 절에 있어야하는 것이다.

예를 들어

select a, b, count(distinct c) as cnt from test_table;

이 있다고 하자.

ONLY_FULL_GROUP_BY	실행여부	비고
OFF	OK	1줄만 나오며 a와 b가 무슨 기준으로 나오는지 정확하진 않음
ON	NO	ERROR 1140 (42000): Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP

옵션에 따라서 다르게 실행된다.

 

즉 error 1140은 그룹함수가 아닌 항목이 group by 절에 없을 경우 나오는것이다.

위와 같이 쿼리를 실행하고자 한다면 아래와 같이 실행하여야 한다.

select a, b, count(distinct c) as cnt
  from test_table
group by a,b;

 

그런데 oracle에서 윈도우 함수는 group by절 에러에 영향을 받지 않기 때문에 당황하였다.

maria는 다른가?? 하고 검색을 했다.

아니다. 다들 에러가 맞다고 한다.

 

mariadb window function 이슈 (feat. ONLY_FULL_GROUP_BY mode)

1차 이슈 제기 및 fix

https://jira.mariadb.org/browse/MDEV-17525

[MDEV-17525] Window functions not working in ONLY_FULL_GROUP_BY mode - Jira

2018-10-23에 이슈가 제기 되었고 FIX 되었다.

Fix Version/s: 10.3.11, 10.2.19

 

관련 커밋 : http://lists.askmonty.org/pipermail/commits/2018-October/013038.html

여기서 문제점은 관련 커밋을 보면 알겠지만 AVG에 대한것만 FIX되었다. 😥

2차 이슈 제기 및 fix

https://jira.mariadb.org/browse/MDEV-17785

[MDEV-17785] Window functions not working in ONLY_FULL_GROUP_BY mode II - Jira

2018-11-20에 제기 되었으나 FIX는 2022-02-07날 되었다;; 최근이다 ㅠㅠ

Fix Version/s:10.2.43, 10.3.34, 10.4.24, 10.5.15, 10.6.7, 10.7.3

관련 커밋 : 

여기서 문제점은 FIX가 최근에 되어 내 버전(10.6.4-MariaDB-1:10.6.4+maria~focal-log)에서는 그대로 에러가 난다는것이였다.. ㅠㅠ

그래서 UPGRADE를 진행하였다.

mariadb upgrade

https://mariadb.com/kb/en/upgrading/

Upgrading MariaDB

https://mariadb.com/kb/en/upgrading-from-mariadb-106-to-mariadb-107/

Upgrading from MariaDB 10.6 to MariaDB 10.7

 

ONLY_FULL_GROUP_BY  제거하고 쓰기

https://24hours-beginner.tistory.com/159

[Mysql] ONLY_FULL_GROUP_BY (Maria DB)