ONLY_FULL_GROUP_BY = ?

회사에서 일을 하다가 GROUP BY 를 사용하는 Query에서 데이터를 가져올 때 아래와 같은 문제가 발생했다.

Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column '컬럼명' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

검색을 해보니 mysql 5.7버전에서 sql_mode가 생겼고, 그 옵션 중 only_full_group_by 때문에 생긴 문제였다.

이 글을 보면 HAVING이나 ORDER BY목록이 GROUP BY절 에서 명명 되지 않았거나 기능적으로 종속되지 않은 집계되지 않은 열을 참조 하는 쿼리를 거부 한다고 한다.

이를 해결할 수 있는 방법은 두 가지 정도 있다.

Query

표준에 맞게 Query문을 작성하면 된다.

SELECT o.custid, c.name, MAX(o.payment)
  FROM orders AS o, customers AS c
  WHERE o.custid = c.custid
  GROUP BY o.custid;

위의 예시를 보면 GROUP BY 로 o.custid 를 그룹화하여 SELECT 하겠다는 Query문이다.
저런 형태로 사용하면 위의 에러를 볼 수 있을 것이다. 즉, 그룹으로 묶는건 o.custid 만으로는 부족하다는 얘기다.

GROUP BY 를 사용하는 경우, SELECT 할 수 있는 컬럼은 GROUP BY 에 나열된 컬럼과 SUM() , COUNT() 같은 집계 함수(Aggregation Function)으로 한정된다.

한마디로 o.custid, Max(o.payment) 는 문제없이 가져오지만 GROUP BY에 선언도 안된 c.name을 가져오려고 하니 문제가 발생하는 것이다. 문제를 해결하기 위해서는 아래와 같이 c.name을 GROUP BY에 추가하면 된다.

SELECT o.custid, c.name, MAX(o.payment)
  FROM orders AS o, customers AS c
  WHERE o.custid = c.custid
  GROUP BY o.custid, c.name;

SQL mode set

또 다른 방법으로는 sql_mode 에 설정되어있는 only_full_group_by 속성을 꺼주면 된다고 한다.

사용방법은 아래와 같다.

mysql> set global sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

mysql> set session sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

고쳐야 할 코드가 많다면 해당 방법이 좋아보이지만, 잘못된 GROUP BY를 사용하는 것을 방지할 수 있기에 Query 수정을 하자!