Create View 생성 Query문의 오류

어제 개발자분이 이전까지는 동일한 Create View Query문으로 Error 없이 잘 생성해서 사용해 왔는데,

Oracle 11g R2에서 동일한 Query문을 가져다 하니, Error가 발생하면서 View가 생성이 안된다고 하더군요.

그래서, Create View Query문을 받아서 확인해봤습니다.

눈으로만 딱 보니, Group By절에 Define되어 있는 Column이 Select 절에는 존재하지 않았습니다.

예를 들면 아래와 같은 형식이지요.

select  ename, job, mgr

from     emp

group  ename, job;

대부분 위의 Query문을 보고, 아 mgr Column에 대해서 집계 함수를 이용해서 표시를 해주거나,

Group by 절에 mgr을 포함시켜주어야 겠구나 하는 생각이 드실 것입니다.

하지만, 아래와 같은 경우에서 그 생각이 산산히 부서집니다. (동일한 형태로 SCOTT 계정을 이용해서 재현해보았습니다.)

============================================================================================================

1. 아래와 같이 scott 계정의 emp Table을 Hiredate column을 참조하는 View를 하나 생성합니다.

SCOTT@david11gR1>create or replace view v_emp_hiredate

  2  (hiredttm, hiredate, hiretime)

  3  as

  4  select hiredate, to_char(hiredate,'yyyymmdd') as hiredate, to_char(hiredate, 'hh24:mi:ss') as hitetime

  5  from emp

  6  with read only;

뷰가 생성되었습니다.

2. 생성된 View가 정상적으로 잘 작동하는지 확인해 봅니다.

SCOTT@david11gR1>select * from v_emp_hiredate;

HIREDTTM HIREDATE HIRETIME

-------- -------- --------

80/12/17 19801217 00:00:00

81/02/20 19810220 00:00:00

81/02/22 19810222 00:00:00

81/04/02 19810402 00:00:00

81/09/28 19810928 00:00:00

81/05/01 19810501 00:00:00

81/06/09 19810609 00:00:00

87/04/19 19870419 00:00:00

81/11/17 19811117 00:00:00

81/09/08 19810908 00:00:00

87/05/23 19870523 00:00:00

81/12/03 19811203 00:00:00

81/12/03 19811203 00:00:00

82/01/23 19820123 00:00:00

14 개의 행이 선택되었습니다.

3. 위와 같이 정상적으로 생성된 View를 참조하는 다른 View를 생성합니다.

    (Group By 절에서 의도적으로 hiredttm만 define하고, hiredate와 hiretime은 누락시킵니다.)

SCOTT@david11gR1>create or replace view v_test

  2  (hiredttm, hiredate, hiretime, tot)

  3  as

  4  select hiredttm, hiredate, hiretime, count(0) as tot

  5  from v_emp_hiredate

  6  group by hiredttm

  7  with read only;

뷰가 생성되었습니다.

Select 절의 구문 오류로 인해서 Error가 발생하면서, View가 생성되지 않을 줄 알았는데,

정상적으로 생성이 됩니다.

그래서, Data는 정상적으로 되는지 조회해 봅니다.

SCOTT@david11gR1>select * from v_test;

HIREDTTM HIREDATE HIRETIME        TOT

-------- -------- -------- ----------

80/12/17 19801217 00:00:00          1

81/02/20 19810220 00:00:00          1

81/02/22 19810222 00:00:00          1

81/04/02 19810402 00:00:00          1

81/05/01 19810501 00:00:00          1

81/06/09 19810609 00:00:00          1

81/09/08 19810908 00:00:00          1

81/09/28 19810928 00:00:00          1

81/11/17 19811117 00:00:00          1

81/12/03 19811203 00:00:00          2

82/01/23 19820123 00:00:00          1

87/04/19 19870419 00:00:00          1

87/05/23 19870523 00:00:00          1

13 개의 행이 선택되었습니다.

결과상에 문제 없이 정상적으로 조회가 되었습니다.

============================================================================================================

이 현상은 Oracle 11gR1이하 버전에서 동일하게 나타납니다. (Oracle 8i, 9i, 10g, 11gR1 여기까지만 확인해봄)

그래서 이번에는 위와 동일한 내용으로 Oracle 11gR2에서 확인해보도록 하겠습니다.

============================================================================================================

1. 먼저 위의 테스트와 동일하게 scott 계정의 emp Table을 Hiredate column을 참조하는 View를 하나 생성합니다.

SCOTT@david11gR2>create or replace view v_emp_hiredate

  2  (hiredttm, hiredate, hiretime)

  3  as

  4  select hiredate, to_char(hiredate,'yyyymmdd') as hiredate, to_char(hiredate, 'hh24:mi:ss') as hitetime

  5  from emp

  6  with read only;

뷰가 생성되었습니다.

2. 이제 문제의 View를 생성해봅니다.

SCOTT@david11gR2>create or replace view v_test

  2  (hiredttm, hiredate, hiretime, tot)

  3  as

  4  select hiredttm, hiredate, hiretime, count(0) as tot

  5  from v_emp_hiredate

  6  group by hiredttm

  7  with read only;

select hiredttm, hiredate, hiretime, count(0) as tot

                 *

4행에 오류:

ORA-00979: GROUP BY 표현식이 아닙니다.

우리가 일반적으로 생각하던 오류를 발생시키면서, View를 생성하지 못합니다.

============================================================================================================

왜 이와 같은 현상이 발생하는지 그에 대한 명확한 원인은 모르겠습니다.

단순히, Oracle의 Bug일까요?? 아니면, hiredate와 hiretime의 값 자체가 emp.hiredate의 값으로 부터 파생된 값이기

때문에 이를 Check할 필요가 없다고 판단한 것일까요?? (이건 좀 억지스럽군요)

아무튼 Oracle 11g R2의 Compiler는 좀 더 똑똑해진듯 합니다. 우리의 생각처럼 제대로 오류를 발생시켜주니 말이죠.

혹시라도, 위의 증상에 대해서 원인을 설명해주실 수 있으신 분이 있으시면 답변 부탁드리겠습니다.