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

已选择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%')
4 - filter("OWNER" LIKE 'SYS%')

Note
-----
- dynamic sampling used for this statement

从执行计划上看，确实这是造成问题的真正原因。而且在9i中，如果使用NO_MERGE的提示也确实可以避免这个问题：

SQL> SELECT * FROM V$VERSION;

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production PL/SQL Release 9.2.0.4.0 - Production
CORE 9.2.0.3.0 Production
TNS for 32-bit Windows: Version 9.2.0.4.0 - Production
NLSRTL Version 9.2.0.4.0 - Production

SQL> CREATE TABLE T1 AS SELECT * FROM DBA_OBJECTS;

表已创建。

SQL> CREATE TABLE T2 AS SELECT * FROM DBA_USERS;

表已创建。

SQL> SET AUTOT ON EXP
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 31
SYS TYPE 644
SYS VIEW 2090
SYS INDEX 315
SYS QUEUE 4
SYS TABLE 347
.
.
.
SYSTEM PACKAGE BODY 1
SYSTEM INDEX PARTITION 24
SYSTEM TABLE PARTITION 27

已选择40行。

执行计划
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=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'

SQL> SELECT /*+ NO_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%';
FROM (SELECT OWNER, OBJECT_TYPE, COUNT(*) CN FROM T1 GROUP BY OBJECT_TYPE) T, T2
*第 2 行出现错误:
ORA-00979: 不是 GROUP BY 表达式

对于Oracle来说，先执行关联操作，然后对关联的结果执行GROUP BY操作是可以得到正确的答案的，但是对于这种写法显然是有问题的。