缺少GROUP BY表达式可以顺利执行的问题 (2)

表已创建。

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), T2
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 表达式

www.ixdba.net

看来虽然Oracle10g的CBO已经修正了这个问题，但是RBO中依然存在，而且在Metalink中并没有看到类似的问题描述，怀疑是CBO优化器的升级使得这个bug不可能在新版中再现，而并非是Oracle有意去修正这个问题。