缺少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;ITPUB个人空间d-v]1|o
SELECT OWNER, OBJECT_TYPE, COUNT(*) CN FROM T1 GROUP BY OBJECT_TYPE
#s(Tr E*P(ec'Q*P0       *
"{9XW
[T}l0第1行出现错误:ITPUB个人空间#Ieh7b3SF8Q6RW
ORA-00979:不是GROUP BY表达式

这个SQL由于GROUP BY语句中确少OWNER字段，因此执行报错。

但是把这个SQL内嵌到子查询中，居然可以得到结果：

SQL> SELECT USERNAME, OBJECT_TYPE, CN
n:G,Q*?Z/mO1jL0  2  FROM (SELECT OWNER, OBJECT_TYPE, COUNT(*) CN FROM T1 GROUP BY OBJECT_TYPE), T2
#Gt:d
A R
C,Y%E,M6m0  3  WHERE USERNAME = OWNERITPUB个人空间kwclm.J2yY
  4  AND USERNAME LIKE 'SYS%';

USERNAME                       OBJECT_TYPE                CNITPUB个人空间E(A}O5h3A;Z`
------------------------------ ------------------ ----------
Rg$[#p1~x(stQ0SYS                            LOB                        28
lf7v$n~sp0SYS                            TYPE                      478
!Q NvQO0\4Y!}0SYS                            VIEW                     2112
yT8`epd1B;DI-]0.ITPUB个人空间.z&T)p%~}*i.~!w
.ITPUB个人空间e;A,RK.o,@
.ITPUB个人空间3Ic S1u r)hN L(NO
SYSTEM                         INDEX PARTITION            48
,cyRiAp#d0SYSTEM                         TABLE PARTITION            53

已选择42行。

检查SQL的执行计划，发现是MERGE JOIN：

SQL> SELECT USERNAME, OBJECT_TYPE, CN
\(iJc[R8\-^0  2  FROM (SELECT OWNER, OBJECT_TYPE, COUNT(*) CN FROM T1 GROUP BY OBJECT_TYPE), T2
%B s n:m{ j'g'Fk0  3  WHERE USERNAME = OWNER
$A,sX`]0  4  AND USERNAME LIKE 'SYS%';

执行计划
VM.\r^.O4Y5jR0----------------------------------------------------------ITPUB个人空间sR\iUJ k
   0      SELECT STATEMENT ptimizer=CHOOSEITPUB个人空间Dq.P+iW]-@A
   1    0   SORT (GROUP BY)ITPUB个人空间 L5YE$F5n
   2    1     MERGE JOIN
b`
Z^&k9L:LbT2H m6^o0   3    2       SORT (JOIN)
*fc5l,|e&{^F:Z.p0   4    3         TABLE ACCESS (FULL) OF 'T2'ITPUB个人空间#v$G7u.U(r.D-{4U@a$W
   5    2       SORT (JOIN)
OgGU~eM,L0   6    5         TABLE ACCESS (FULL) OF 'T1'

想想也有道理，Oracle先对OWNER字段进行排序，进行MERGE JOIN连接后，再对OBJECT_TYPE字段进行GROUP BY。

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

SQL> SET AUTOT TRACE EXPITPUB个人空间_#W.m)[@VO2[)U
SQL> SELECT /*+ USE_HASH(T2) */ USERNAME, OBJECT_TYPE, CN
!b)];I;Z Dj&he0  2  FROM (SELECT OWNER, OBJECT_TYPE, COUNT(*) CN FROM T1 GROUP BY OBJECT_TYPE), T2
*` Otk3P0  3  WHERE USERNAME = OWNER
(k$nB.z
QT0  4  AND USERNAME LIKE 'SYS%';

执行计划ITPUB个人空间Zn Z/a#z{ Sq
----------------------------------------------------------ITPUB个人空间{Qeb,v
   0      SELECT STATEMENT ptimizer=CHOOSE (Cost=54 Card=1467 Bytes=76284)ITPUB个人空间\:B,W(R c%_
   1    0   SORT (GROUP BY) (Cost=54 Card=1467 Bytes=76284)ITPUB个人空间%I0Hh{(q.t A6yzf
   2    1     HASH JOIN (Cost=45 Card=1467 Bytes=76284)ITPUB个人空间7sa6@n8p'b
   3    2       TABLE ACCESS (FULL) OF 'T2' (Cost=2 Card=2 Bytes=48)ITPUB个人空间:z)o8EL/l1A4^
   4    2       TABLE ACCESS (FULL) OF 'T1' (Cost=42 Card=5867 Bytes=164276)

不过这个SQL也很有意思，只需要将上面的USERNAME改成OWNER，就会报错：

SQL> SELECT OWNER, OBJECT_TYPE, CNITPUB个人空间q-^9jmll]
  2  FROM (SELECT OWNER, OBJECT_TYPE, COUNT(*) CN FROM T1 GROUP BY OBJECT_TYPE), T2
Hp(e$c)qteK#n(_0B0  3  WHERE USERNAME = OWNER
,Z"J5tk)qZ M0  4  AND USERNAME LIKE 'SYS%';
S;`c:_8z)R2MG_0WHERE USERNAME = OWNER
'\)h4LD"\Uh)oz0                 *
"v9ZY4|X a0第3行出现错误:ITPUB个人空间&Jg r%D2u)Bu'e
ORA-00979:不是GROUP BY表达式

这时即使加上HINT也不行：

SQL> SELECT /*+ USE_HASH(T2) */ OWNER, OBJECT_TYPE, CNITPUB个人空间'R]kK*v-W"J7X
  2  FROM (SELECT OWNER, OBJECT_TYPE, COUNT(*) CN FROM T1 GROUP BY OBJECT_TYPE) T1, T2ITPUB个人空间.j:q#T KQb\
V
  3  WHERE USERNAME = OWNERITPUB个人空间)S4p1i,KV-aE
  4  AND USERNAME LIKE 'SYS%';ITPUB个人空间Er'Y1D:e5X
WHERE USERNAME = OWNER
n2aYDH:V{Hn\0                 *
}2R@8NG,`0第3行出现错误:ITPUB个人空间
^^`}6Be
ORA-00979:不是GROUP BY表达式

这个问题在10R2中只能通过RULE方式再现，通过使用USE_MERGE和USE_HASH提示已经无法再现了：

SQL> CONN TEST/TEST@TESTZJ
v#m4s$I!R\p0已连接。ITPUB个人空间7M| \c"[.wm6U&p5Q
SQL> SELECT * FROM V$VERSION;

BANNER
"quS\'\oBo0----------------------------------------------------------------
0LK(z@a%WD{0Oracle Database10gEnterpriseEdition Release10.2.0.3.0 - 64bi
F#d,u9JR e|0PL/SQL Release 10.2.0.3.0 - Production
2iF?*Y*C'B0CORE    10.2.0.3.0      Production
T$u3E q+F0TNS for Linux: Version 10.2.0.3.0 - ProductionITPUB个人空间Z"z j*h.im*D6H@%K
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
JA`.tI0  2  FROM (SELECT OWNER, OBJECT_TYPE, COUNT(*) CN FROM T1 GROUP BY OBJECT_TYPE), T2
,x&qz&m*UQ0  3  WHERE USERNAME = OWNERITPUB个人空间+d9\z!A;D
  4  AND USERNAME LIKE 'SYS%';
xbr9_E}me(C0FROM (SELECT OWNER, OBJECT_TYPE, COUNT(*) CN FROM T1 GROUP BY OBJECT_TYPE), T2ITPUB个人空间#IEpi#D5]k)Tq w
             *
%lz@9L&@Sx0第2行出现错误:
3s/m}#}]H7n'd&D@0ORA-00979:不是GROUP BY表达式

SQL> SET AUTOT ON EXP
~8A5Z
QV!WP0SQL> SELECT /*+ RULE */ USERNAME, OBJECT_TYPE, CN
awG,Nv'A1]0  2  FROM (SELECT OWNER, OBJECT_TYPE, COUNT(*) CN FROM T1 GROUP BY OBJECT_TYPE),
Ncl,o i
j~0  3  WHERE USERNAME = OWNER
&]P+M:?Ru0  4  AND USERNAME LIKE 'SYS%';

USERNAME                       OBJECT_TYPE                 CN
[.RO7QO\ aP0------------------------------ ------------------- ----------ITPUB个人空间!}W|3D-[^#tc
SYS                            JOB                          4ITPUB个人空间]p7{piF*? J
SYS                            LOB                         95
\|Ki5s,VN8K:ss |-w0SYS                            RULE                         4ITPUB个人空间
My,Gt8X JMb cJ
SYS                            TYPE                       933ITPUB个人空间k!MT&{?-hy8o5bA7K
.
$T^-p4Fx!g*]0.
[X/X4`n,s0.
G.YN dj*P!L0SYSTEM                         INDEX PARTITION             64ITPUB个人空间eC{l5gN A7z
SYSTEM                         TABLE PARTITION             53

已选择53行。

mu-x k
nu0执行计划ITPUB个人空间H7x"I|]&q(D
----------------------------------------------------------

-------------------------------------ITPUB个人空间2y*f3M;B,j3l
| Id  | Operation            | Name |

Tb9LL!^{+I%^Z4H0-------------------------------------ITPUB个人空间)v1oI(f]1n?(u
|   0 | SELECT STATEMENT     |      |ITPUB个人空间3Y?}2UUE
|   1 |  SORT GROUP BY       |      |ITPUB个人空间4KdMRW2]!c4x8TK
|   2 |   MERGE JOIN         |      |
g:z~|c0|   3 |    SORT JOIN         |      |ITPUB个人空间6f'MD,[ ]H/I G9V
|*  4 |     TABLE ACCESS FULL| T2   |ITPUB个人空间M/X&p)gA
|*  5 |    SORT JOIN         |      |
-e:Z:v} L3`8}&m v!i0|*  6 |     TABLE ACCESS FULL| T1   |ITPUB个人空间v+I~v3tr
-------------------------------------

Predicate Information (identified by operation id):
Q,L3g`[N/kQC0---------------------------------------------------

   4 - filter("USERNAME" LIKE 'SYS%')ITPUB个人空间9S-am#P&^~4cS
   5 - access("USERNAME"="OWNER")ITPUB个人空间 wA\V {'\8a)yb
       filter("USERNAME"="OWNER")ITPUB个人空间*_R D-t~w7@
   6 - filter("OWNER" LIKE 'SYS%')

SQL> SELECT /*+ USE_MERGE(T2) */ USERNAME, OBJECT_TYPE, CNITPUB个人空间yDA~I*ca
  2  FROM (SELECT OWNER, OBJECT_TYPE, COUNT(*) CN FROM T1 GROUP BY OBJECT_TYPE), T2ITPUB个人空间a
V|4{P*y
  3  WHERE USERNAME = OWNERITPUB个人空间S"i8~-kx7|LuR3|
  4  AND USERNAME LIKE 'SYS%';
+L#~ D\%{ V|7i0FROM (SELECT OWNER, OBJECT_TYPE, COUNT(*) CN FROM T1 GROUP BY OBJECT_TYPE), T2ITPUB个人空间Q#Yx(}}
             *ITPUB个人空间P"KmR*J
第2行出现错误:
-l]i qy)V E+o0ORA-00979:不是GROUP BY表达式

H~;T;Q3VW;j}N0SQL> SELECT /*+ USE_HASH(T2) */ USERNAME, OBJECT_TYPE, CNITPUB个人空间TgZlG8k0k3K(s9T
  2  FROM (SELECT OWNER, OBJECT_TYPE, COUNT(*) CN FROM T1 GROUP BY OBJECT_TYPE), T2ITPUB个人空间fb%o/s({X@Q5D"qX
  3  WHERE USERNAME = OWNERITPUB个人空间4M1t8Je+E0z`*C_'u
  4  AND USERNAME LIKE 'SYS%';
G$h2vF'{&~0FROM (SELECT OWNER, OBJECT_TYPE, COUNT(*) CN FROM T1 GROUP BY OBJECT_TYPE), T2ITPUB个人空间MY+jGJ*{2_Qy
             *ITPUB个人空间n5GnC9x4q8h
第2行出现错误:
Zr|Tq1C;fJ
q9u0ORA-00979:不是GROUP BY表达式

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