ONLY_FULL_GROUP_BY + query 실행 순서

MySQL tutorial를 공부하던 중 발생한 에러에 대해 정리하는 것이 좋을 것 같아 블로그를 쓰게되었다.

아래의 코드는 MySQL 공식 사이트의 alias(별칭)와 관련된 튜토리얼에 나오는 코드이다(공식사이트).

SELECT orderNumber as 'Order no.', SUM(priceEach * quantityOrdered) as total
FROM orderdetails
GROUP BY 'Order no.'
HAVING total > 60000;

쿼리를 그냥 실행했더니 아래와 같은 에러가 발생했다.

[42000][1055] Expression 
1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'classicmodels.orderdetails.orderNumber' which is not functionally dependent on columns in GROUP BY clause;
this is incompatible with sql_mode=only_full_group_by

공식 튜토리얼에 나온 코드를 그대로 따라했는데 왜 에러가 발생했는지 알 수 없어서 에러 메시지에 나와있는 sql_mode=only_full_group_by에 대해 검색해보았다. ONLY_FULL_GROUP_BY는 아래와 같이 설명하고 있다.

 

"Reject queries for which the select list, HAVING condition, or ORDER BY list refer to nonaggregated columns that are neither named in the GROUP BY clause nor are functionally dependent on (uniquely determined by) GROUP BY columns."

의미가 어려워 내 마음대로 해석해보니 'select list, HAVING condition, ORDER BY list가 (GROUP BY에 등록되지 않은 구문이나 GROUP BY 열을 기능적으로 의존하는)비집계 열을 참조하는 것을 거부한다' 라고 되어있는듯 하다. 정확한 의미를 이해하진 못했지만 select 구문에서 사용된 이름이 group by에서 사용되어야 하는 것 같다.

 

저 문장을 이해하기 위해 코드를 이용해 테스트해 보았다.

# default sql_mode
set sql_mode ='';

SELECT orderNumber as 'Order no.', SUM(priceEach * quantityOrdered) as total
FROM orderdetails
GROUP BY orderNumber
HAVING total > 60000;  # 정상 출력

의도한 값이 출력 된다.

SELECT orderNumber as 'Order no.', SUM(priceEach * quantityOrdered) as total
FROM orderdetails
GROUP BY 'Order no.'
HAVING total > 60000;  # 의도하지 않은 결과 출력

의도하지 않은 값이 출력 된다.

# only_full_group_by
set sql_mode ='ONLY_FULL_GROUP_BY';

SELECT orderNumber as 'Order no.', SUM(priceEach * quantityOrdered) as total
FROM orderdetails
GROUP BY orderNumber
HAVING total > 60000;  # 정상 출력

SELECT orderNumber as 'Order no.', SUM(priceEach * quantityOrdered) as total
FROM orderdetails
GROUP BY 'Order no.'  
HAVING total > 60000;  # 에러 출력

 

이 문제에 대해 좀 더 검색하던 중 MySQL이 아래와 같은 순서로 실행됨을 알 수 있었다.

1. FROM
2. WHERE
3. GROUP BY
4. HAVING
5. SELECT
6. ORDER BY

 

이 순서대로 실행된다면 에러가 발생하거나 의도한 값이 출력하지 않은 이유를 알 수 있다.

• 위 예제에서 GROUP BY가 실행될 때, SELECT에 정의된 별칭은 사용될 수 없다.

  → SELECT에 정의되어 있는 별칭 'Order no.'는 GROUP BY가 실행될 시점에는 알 수 없는 값이다.

• '의도하지 않은 결과 출력' 코드는 GROUP BY를 생략해도 동일한 값을 출력한다.

  → GROUP BY가 동작하지 않는다.

  select orderNumber, sum(priceEach*quantityOrdered) total from orderdetails; 이것과 동일한 결과를 나타낸다.

 

추가로 공식 사이트에서 아래와 같은 사항을 주의하라고 말한다.

"Notice that you cannot use a column alias in the WHERE clause. The reason is that when MySQL evaluates the WHERE clause, the values of columns specified in the SELECT clause is not be evaluated yet."

 

이 역시 SELECT 구문이 평가되기 전에 WHERE 구문이 먼저 실행되기 때문에 WHERE 구문에서 SELECT에서 정의된 별칭을 사용할 수 없다는 것을 말한다.(GROUP BY와 동일한 이유)

 

ONLY_FULL_GROUP_BY는 GROUP BY 사용 시 잘못된 쿼리 사용을 미리 막을 수 있기 때문에 데이터베이스를 생성할 때 미리 설정하는 것이 좋다고 생각한다.

→ 만일 쿼리의 실행 결과를 모르는 상태에서 두 번째 코드를 실행 시켰다면 정상적으로 동작한다고 생각할 수 있다.

 

 

References

Jason Heo's Blog: MySQL GROUP BY 사용 시 주의점

MySQL :: MySQL 8.0 Reference Manual :: 5.1.11 Server SQL Modes