oracle group by 表达式,缺少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有意去修正这个问题。