缺少GROUP BY表示式可以順利執行的問題

Oracle9204上執行一個明顯語法錯誤的SQL，卻可以得到查詢結果。

 

 

首先重現一下問題：

SQL> CREATE TABLE T1 AS SELECT * FROM DBA_OBJECTS;

表已建立。

SQL> CREATE TABLE T2 AS SELECT * FROM DBA_USERS;

表已建立。

SQL> SELECT OWNER, OBJECT_TYPE, COUNT(*) CN FROM T1 GROUP BY OBJECT_TYPE;
SELECT OWNER, OBJECT_TYPE, COUNT(*) CN FROM T1 GROUP BY OBJECT_TYPE
       *
第 1 行出現錯誤:
ORA-00979: 不是 GROUP BY 表示式

這個SQL由於GROUP BY語句中確少OWNER欄位，因此執行報錯。

但是把這個SQL內嵌到子查詢中，居然可以得到結果：

SQL> SELECT USERNAME, OBJECT_TYPE, CN
  2  FROM (SELECT OWNER, OBJECT_TYPE, COUNT(*) CN FROM T1 GROUP BY OBJECT_TYPE), T2
  3  WHERE USERNAME = OWNER
  4  AND USERNAME LIKE 'SYS%';

USERNAME                       OBJECT_TYPE                CN
------------------------------ ------------------ ----------
SYS                            LOB                        28
SYS                            TYPE                      478
SYS                            VIEW                     2112
.
.
.
SYSTEM                         INDEX PARTITION            48
SYSTEM                         TABLE PARTITION            53

已選擇42行。

檢查SQL的執行計劃，發現是MERGE JOIN：

SQL> SELECT USERNAME, OBJECT_TYPE, CN
  2  FROM (SELECT OWNER, OBJECT_TYPE, COUNT(*) CN FROM T1 GROUP BY OBJECT_TYPE), T2
  3  WHERE USERNAME = OWNER
  4  AND USERNAME LIKE 'SYS%';

執行計劃
----------------------------------------------------------
   0      SELECT STATEMENT ptimizer=CHOOSE
   1    0   SORT (GROUP BY)
   2    1     MERGE JOIN
   3    2       SORT (JOIN)
   4    3         TABLE ACCESS (FULL) OF 'T2'
   5    2       SORT (JOIN)
   6    5         TABLE ACCESS (FULL) OF 'T1'

想想也有道理，Oracle先對OWNER欄位進行排序，進行MERGE JOIN連線後，再對OBJECT_TYPE欄位進行GROUP BY。

如果使用HASH_JOIN提示，Oracle也可以得到執行結果：

SQL> SET AUTOT TRACE EXP
SQL> SELECT /*+ USE_HASH(T2) */ USERNAME, OBJECT_TYPE, CN
  2  FROM (SELECT OWNER, OBJECT_TYPE, COUNT(*) CN FROM T1 GROUP BY OBJECT_TYPE), T2
  3  WHERE USERNAME = OWNER
  4  AND USERNAME LIKE 'SYS%';

執行計劃
----------------------------------------------------------
   0      SELECT STATEMENT ptimizer=CHOOSE (Cost=54 Card=1467 Bytes=76284)
   1    0   SORT (GROUP BY) (Cost=54 Card=1467 Bytes=76284)
   2    1     HASH JOIN (Cost=45 Card=1467 Bytes=76284)
   3    2       TABLE ACCESS (FULL) OF 'T2' (Cost=2 Card=2 Bytes=48)
   4    2       TABLE ACCESS (FULL) OF 'T1' (Cost=42 Card=5867 Bytes=164276)

不過這個SQL也很有意思，只需要將上面的USERNAME改成OWNER，就會報錯：

SQL> SELECT OWNER, OBJECT_TYPE, CN
  2  FROM (SELECT OWNER, OBJECT_TYPE, COUNT(*) CN FROM T1 GROUP BY OBJECT_TYPE), T2
  3  WHERE USERNAME = OWNER
  4  AND USERNAME LIKE 'SYS%';
WHERE USERNAME = OWNER
                 *
第 3 行出現錯誤:
ORA-00979: 不是 GROUP BY 表示式

這時即使加上HINT也不行：

SQL> SELECT /*+ USE_HASH(T2) */ OWNER, OBJECT_TYPE, CN
  2  FROM (SELECT OWNER, OBJECT_TYPE, COUNT(*) CN FROM T1 GROUP BY OBJECT_TYPE) T1, T2
  3  WHERE USERNAME = OWNER
  4  AND USERNAME LIKE 'SYS%';
WHERE USERNAME = OWNER
                 *
第 3 行出現錯誤:
ORA-00979: 不是 GROUP BY 表示式

這個問題在10R2中只能透過RULE方式再現，透過使用USE_MERGE和USE_HASH提示已經無法再現了：

SQL> CONN TEST/TEST@TESTZJ
已連線。
SQL> SELECT * FROM V$VERSION;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi
PL/SQL Release 10.2.0.3.0 - Production
CORE    10.2.0.3.0      Production
TNS for Linux: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production

SQL> CREATE TABLE T1 AS SELECT * FROM DBA_OBJECTS;

表已建立。

SQL> CREATE TABLE T2 AS SELECT * FROM DBA_USERS;

表已建立。

SQL> SELECT USERNAME, OBJECT_TYPE, CN
  2  FROM (SELECT OWNER, OBJECT_TYPE, COUNT(*) CN FROM T1 GROUP BY OBJECT_TYPE), T2
  3  WHERE USERNAME = OWNER
  4  AND USERNAME LIKE 'SYS%';
FROM (SELECT OWNER, OBJECT_TYPE, COUNT(*) CN FROM T1 GROUP BY OBJECT_TYPE), T2
             *
第 2 行出現錯誤:
ORA-00979: 不是 GROUP BY 表示式

SQL> SET AUTOT ON EXP
SQL> SELECT /*+ RULE */ USERNAME, OBJECT_TYPE, CN
  2  FROM (SELECT OWNER, OBJECT_TYPE, COUNT(*) CN FROM T1 GROUP BY OBJECT_TYPE),
  3  WHERE USERNAME = OWNER
  4  AND USERNAME LIKE 'SYS%';

USERNAME                       OBJECT_TYPE                 CN
------------------------------ ------------------- ----------
SYS                            JOB                          4
SYS                            LOB                         95
SYS                            RULE                         4
SYS                            TYPE                       933
.
.
.
SYSTEM                         INDEX PARTITION             64
SYSTEM                         TABLE PARTITION             53

已選擇53行。

執行計劃
----------------------------------------------------------

-------------------------------------
| Id  | Operation            | Name |
-------------------------------------
|   0 | SELECT STATEMENT     |      |
|   1 |  SORT GROUP BY       |      |
|   2 |   MERGE JOIN         |      |
|   3 |    SORT JOIN         |      |
|*  4 |     TABLE ACCESS FULL| T2   |
|*  5 |    SORT JOIN         |      |
|*  6 |     TABLE ACCESS FULL| T1   |
-------------------------------------

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

   4 - filter("USERNAME" LIKE 'SYS%')
   5 - access("USERNAME"="OWNER")
       filter("USERNAME"="OWNER")
   6 - filter("OWNER" LIKE 'SYS%')

SQL> SELECT /*+ USE_MERGE(T2) */ USERNAME, OBJECT_TYPE, CN
  2  FROM (SELECT OWNER, OBJECT_TYPE, COUNT(*) CN FROM T1 GROUP BY OBJECT_TYPE), T2
  3  WHERE USERNAME = OWNER
  4  AND USERNAME LIKE 'SYS%';
FROM (SELECT OWNER, OBJECT_TYPE, COUNT(*) CN FROM T1 GROUP BY OBJECT_TYPE), T2
             *
第 2 行出現錯誤:
ORA-00979: 不是 GROUP BY 表示式

SQL> SELECT /*+ USE_HASH(T2) */ USERNAME, OBJECT_TYPE, CN
  2  FROM (SELECT OWNER, OBJECT_TYPE, COUNT(*) CN FROM T1 GROUP BY OBJECT_TYPE), T2
  3  WHERE USERNAME = OWNER
  4  AND USERNAME LIKE 'SYS%';
FROM (SELECT OWNER, OBJECT_TYPE, COUNT(*) CN FROM T1 GROUP BY OBJECT_TYPE), T2
             *
第 2 行出現錯誤:
ORA-00979: 不是 GROUP BY 表示式

看來雖然Oracle10g的CBO已經修正了這個問題，但是RBO中依然存在，而且在Metalink中並沒有看到類似的問題描述，懷疑是CBO最佳化器的升級使得這個bug不可能在新版中再現，而並非是Oracle有意去修正這個問題。