Oracle9204上执行一个明显语法错误的SQL，却可以得到查询结果。
网友gclizh指出，使用MERGE提示可以在10g中避免错误的发生而得到执行结果。根据这个结果进一步分析问题。
缺少GROUP BY表达式可以顺利执行的问题：http://yangtingkun.itpub.net/post/468/451079
使用提示MERGE，可以在10g中重现这个问题。说明这个问题的引入是由于Oracle将内层子查询进行MERGE操作，把GROUP BY操作放在了最后。
10G中使用MERGE提示可以重现这个问题：
SQL> CONN YANGTK/YANGTK@YTK102已连接。
SQL> SELECT * FROM V$VERSION;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.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 /* MERGE(T) */ USERNAME, OBJECT_TYPE, CN
2 FROM (SELECT OWNER, OBJECT_TYPE, COUNT(*) CN FROM T1 GROUP BY OBJECT_TYPE) T, T2
3 WHERE USERNAME = OWNER
4 AND USERNAME LIKE 'SYS%';
USERNAME OBJECT_TYPE CN
------------------------------ ------------------- ----------
SYS PACKAGE BODY 497
SYSTEM VIEW 12
SYS LIBRARY 111
SYS RULE SET 11
SYSTEM INDEX PARTITION 32
.
.
.
SYS SCHEDULE 1
SYS JOB 4
SYSMAN TYPE 212
SYSMAN PROCEDURE 2
IXDBA.NET社区论坛已选择66行。
执行计划
----------------------------------------------------------
Plan hash value: 51733071
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 6813 | 379K| 167 (4)| 00:00:03 |
| 1 | HASH GROUP BY | | 6813 | 379K| 167 (4)| 00:00:03 |
|* 2 | HASH JOIN | | 6813 | 379K| 165 (3)| 00:00:02 |
|* 3 | TABLE ACCESS FULL| T2 | 3 | 87 | 3 (0)| 00:00:01 |
|* 4 | TABLE ACCESS FULL| T1 | 22710 | 620K| 162 (3)| 00:00:02 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("USERNAME"="OWNER")
3 - filter("USERNAME" LIKE 'SYS%')
