[Mysql] ONLY_FULL_GROUP_BY (Maria DB)

본인 테스트 VERSION : 10.1.47-MariaDB-0ubuntu0.18.04.1

 

mysql 5.7 version 부터 sql_mode 추가된것에 대한 피드백이다.

 

SQL-92 and earlier does not permit queries for which the select list, HAVING condition, or ORDER BY list refer to nonaggregated columns that are not named in the GROUP BY clause. For example, this query is illegal in standard SQL-92 because the nonaggregated name column in the select list does not appear in the GROUP BY:

 

SQL-92 및 이전 버전에서는 선택 목록, HAVING조건 또는 ORDER BY목록이 GROUP BY절 에서 명명되지 않은 집계되지 않은 열을 참조 하는 쿼리를 허용하지 않습니다 . 예를 들어,이 쿼리는 name선택 목록 의 집계 되지 않은 열이에 나타나지 않기 때문에 표준 SQL-92에서는 불법입니다 GROUP BY.

https://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html

MySQL :: MySQL 5.7 Reference Manual :: 12.20.3 MySQL Handling of GROUP BY

 

아래 쿼리에서 이상한점이 느껴지는가? 다른버전의 mysql 또는 oracle 등의 다른 언어를 먼저 접해본 분일 수 있다. ( 또는 ONLY_FULL_GROUP_BY 설정이 이미 되어있는 환경에서 서비스를 개발해본 분일 수 있다.)

코드

 

SELECT a, b
  FROM (
   SELECT 1 AS a, 6 AS b FROM DUAL UNION ALL
   SELECT 1 AS a, 2 AS b FROM DUAL UNION ALL
   SELECT 3 AS a, 1 AS b FROM DUAL UNION ALL
   SELECT 1 AS a, 2 AS b FROM DUAL UNION ALL
   SELECT 3 AS a, 2 AS b FROM DUAL UNION ALL
   SELECT 1 AS a, 3 AS b FROM DUAL
   ) aa
 GROUP BY a

 

보통 oracle에서 위와 같이 실행하는 경우 group 함수 처리가 되지않은 컬럼이 있다고 에러가 뜬다. (경험담)

 

ONLY_FULL_GROUP_BY

For SELECT ... GROUP BY queries, disallow SELECTing columns which are not referred to in the GROUP BY clause, unless they are passed to an aggregate function like COUNT() or MAX(). Produce a 1055 error.

https://mariadb.com/kb/en/sql-mode/#only_full_group_by

SQL_MODE

 

근데.. 안뜨더라..

 

일단 결론부터 말하면 기본값으로 group by 에 없는 경우 최근에 있는 항목 limit 1 해서 보여준다.

 

그래서 값을 보거나 변경하고 싶은 경우 sql_mode 확인 후 변경을 하면된다.

/* 확인 */
SELECT @@sql_mode;
SELECT @@GLOBAL.sql_mode;
SELECT @@SESSION.sql_mode;

/* 셋팅 */
SET GLOBAL sql_mode = 'modes';
SET SESSION sql_mode = 'modes';

/* ex */
SET @@SESSION.sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

/* 제거 */
SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));

 

더 자세한 내용은 sql mode를 검색해보거나 mysql Doc을 참조하자.

 

https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html

MySQL :: MySQL 5.7 Reference Manual :: 5.1.10 Server SQL Modes

https://java8.tistory.com/5

mysql group by 문제점 (vs oracle)

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

[DB] Server SQL Modes