ORA-00937 에러

12.1 버전부터 DUAL 테이블을 아우터 기준으로 사용하면 ORA-00937 에러가 발생한다.

테스트를 위해 아래와 같이 테이블을 생성하자.

-- 1
DROP TABLE t1 PURGE;
DROP TABLE t2 PURGE;

CREATE TABLE t1 (c1 NUMBER, c2 NUMBER);
CREATE TABLE t2 (c1 NUMBER, c2 NUMBER);

CREATE INDEX t1_x1 ON t1 (c1);

11.2 버전까지는 에러가 발생하지 않는다.

-- 2
SELECT a.c1
     , NVL (MIN (DECODE (b.c1, 1, b.c2)), a.c2) AS c2
  FROM (SELECT 1 AS c1, 1 AS c2 FROM DUAL) a
     , t1 b
 WHERE b.c1(+) = a.c1;

-----------------------------------------------
| Id  | Operation                     | Name  |
-----------------------------------------------
|   0 | SELECT STATEMENT              |       |
|   1 |  SORT AGGREGATE               |       |
|   2 |   NESTED LOOPS OUTER          |       |
|   3 |    FAST DUAL                  |       |
|   4 |    TABLE ACCESS BY INDEX ROWID| T1    |
|*  5 |     INDEX RANGE SCAN          | T1_X1 |
-----------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------

   5 - access("B"."C1"(+)=1)

12.1 버전부터는 에러가 발생한다. OPTIMIZER_FEATURES_ENABLE 힌트로 11.2 버전을 지정하면 에러가 발생하지 않는 것을 확인할 수 있다. FAST DUAL로 수행되지 않는 점이 흥미롭다.

-- 3-1
SELECT a.c1
     , NVL (MIN (DECODE (b.c1, 1, b.c2)), a.c2) AS c2
  FROM (SELECT 1 AS c1, 1 AS c2 FROM DUAL) a
     , t1 b
 WHERE b.c1(+) = a.c1;

ORA-00937: 단일 그룹의 그룹 함수가 아닙니다

-- 3-2
SELECT   /*+ OPTIMIZER_FEATURES_ENABLE('11.2.0.1') */
         a.c1
       , NVL (MIN (DECODE (b.c1, 1, b.c2)), a.c2) AS c2
    FROM (SELECT 1 AS c1, 1 AS c2 FROM DUAL) a
       , t1 b
   WHERE b.c1(+) = a.c1;

-----------------------------------------------
| Id  | Operation                     | Name  |
-----------------------------------------------
|   0 | SELECT STATEMENT              |       |
|   1 |  SORT AGGREGATE               |       |
|   2 |   NESTED LOOPS OUTER          |       |
|   3 |    TABLE ACCESS FULL          | DUAL  |
|   4 |    TABLE ACCESS BY INDEX ROWID| T1    |
|*  5 |     INDEX RANGE SCAN          | T1_X1 |
-----------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   5 - access("B"."C1"(+)=CASE  WHEN (ROWID IS NOT NULL) THEN 1 ELSE 1 END )

V$SYSTEM_FIX_CONTROL 뷰에서 관련 버그를 확인할 수 있다. 왜 버그인지는 조금 애매한 면이 있다.

-- 4
SELECT * FROM v$system_fix_control WHERE bugno = 17760755;

   BUGNO VALUE SQL_FEATURE        DESCRIPTION                                    OPTIMIZER_FEATURE_ENABLE
-------- ----- ------------------ ---------------------------------------------- ------------------------
17760755     1 QKSFM_CVM_17760755 do not add rowid case expr for dual outer join 12.1.0.2

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

버그 내용을 확인했으니 OPT_PARAM 힌트로도 제어가 가능하다.

-- 5
SELECT   /*+ OPT_PARAM('_fix_control', '17760755:0') */
         a.c1
       , NVL (MIN (DECODE (b.c1, 1, b.c2)), a.c2) AS c2
    FROM (SELECT 1 AS c1, 1 AS c2 FROM DUAL) a
       , t1 b
   WHERE b.c1(+) = a.c1;

-------------------------------------------------------
| Id  | Operation                             | Name  |
-------------------------------------------------------
|   0 | SELECT STATEMENT                      |       |
|   1 |  SORT AGGREGATE                       |       |
|   2 |   NESTED LOOPS OUTER                  |       |
|   3 |    TABLE ACCESS FULL                  | DUAL  |
|   4 |    TABLE ACCESS BY INDEX ROWID BATCHED| T1    |
|*  5 |     INDEX RANGE SCAN                  | T1_X1 |
-------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   5 - access("B"."C1"(+)=CASE  WHEN (ROWID IS NOT NULL) THEN 1 ELSE 1 END )

DUAL OUTER JOIN으로 인한 에러이므로 일반 테이블을 사용하거나, 이너 조인으로 조인하면 에러가 발생하지 않는 것을 확인할 수 있다.

-- 6-1
SELECT   a.c1
       , NVL (MIN (DECODE (b.c1, 1, b.c2)), a.c2) AS c2
    FROM (SELECT 1 AS c1, 1 AS c2 FROM t2) a
       , t1 b
   WHERE b.c1(+) = a.c1;

-------------------------------------------------------
| Id  | Operation                             | Name  |
-------------------------------------------------------
|   0 | SELECT STATEMENT                      |       |
|   1 |  SORT AGGREGATE                       |       |
|   2 |   NESTED LOOPS OUTER                  |       |
|   3 |    TABLE ACCESS FULL                  | T2    |
|   4 |    TABLE ACCESS BY INDEX ROWID BATCHED| T1    |
|*  5 |     INDEX RANGE SCAN                  | T1_X1 |
-------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   5 - access("B"."C1"(+)=CASE  WHEN (ROWID IS NOT NULL) THEN 1 ELSE 1 END )

-- 6-2
SELECT   a.c1
       , NVL (MIN (DECODE (b.c1, 1, b.c2)), a.c2) AS c2
    FROM (SELECT 1 AS c1, 1 AS c2 FROM DUAL) a
       , t1 b
   WHERE b.c1 = a.c1;

-------------------------------------------------------
| Id  | Operation                             | Name  |
-------------------------------------------------------
|   0 | SELECT STATEMENT                      |       |
|   1 |  SORT AGGREGATE                       |       |
|   2 |   NESTED LOOPS                        |       |
|   3 |    FAST DUAL                          |       |
|   4 |    TABLE ACCESS BY INDEX ROWID BATCHED| T1    |
|*  5 |     INDEX RANGE SCAN                  | T1_X1 |
-------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   5 - access("B"."C1"=1)