缺少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个人空间~:n/C@ Cqk
t
SELECT OWNER, OBJECT_TYPE, COUNT(*) CN FROM T1 GROUP BY OBJECT_TYPEITPUB个人空间%F*}\4W{$Y
       *
J o+?Q@3F6b/C4227第1行出现错误:
7s-A5_toVoPe6}X4227ORA-00979:不是GROUP BY表达式

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

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

SQL> SELECT USERNAME, OBJECT_TYPE, CN
*z0MMT1j}hO4227  2  FROM (SELECT OWNER, OBJECT_TYPE, COUNT(*) CN FROM T1 GROUP BY OBJECT_TYPE), T2ITPUB个人空间$^:F"S(s*?5n
  3  WHERE USERNAME = OWNER
n)GRTe`[4227  4  AND USERNAME LIKE 'SYS%';

USERNAME                       OBJECT_TYPE                CNITPUB个人空间x8QQ0ITG
------------------------------ ------------------ ----------
*c3E?M4Y(?-S7`K4227SYS                            LOB                        28
b)m(Fc;B4227SYS                            TYPE                      478ITPUB个人空间)?yN[6S-J i#e
SYS                            VIEW                     2112
yl})QudPQ4227.
Y U-nh1RDm4227.
_)P@L|j3V'I/]4227.ITPUB个人空间LL7]_;x3glr5bF
SYSTEM                         INDEX PARTITION            48
As/[Y-K)D0cc|4227SYSTEM                         TABLE PARTITION            53

已选择42行。

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

SQL> SELECT USERNAME, OBJECT_TYPE, CNITPUB个人空间X y!@)sdf)OD;Yb1Q
  2  FROM (SELECT OWNER, OBJECT_TYPE, COUNT(*) CN FROM T1 GROUP BY OBJECT_TYPE), T2ITPUB个人空间$dzN!rf
  3  WHERE USERNAME = OWNER
4ikL%suUSB4227  4  AND USERNAME LIKE 'SYS%';

执行计划
;U E#z5]4|'[J2v_4227----------------------------------------------------------
^(lB+|Qw:s4227   0      SELECT STATEMENT ptimizer=CHOOSE
{$I5Q p]0{ s c4227   1    0   SORT (GROUP BY)ITPUB个人空间 E7Kh0DJ;s
   2    1     MERGE JOIN
]"U+S@y}i4227   3    2       SORT (JOIN)
JN(?6tp-V-p D(n
Q*U4227   4    3         TABLE ACCESS (FULL) OF 'T2'ITPUB个人空间Ds#|xg L^~
   5    2       SORT (JOIN)
IY7C*h!m4227   6    5         TABLE ACCESS (FULL) OF 'T1'

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

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

SQL> SET AUTOT TRACE EXP
!g`0d;rC!V'[m4227SQL> SELECT /*+ USE_HASH(T2) */ USERNAME, OBJECT_TYPE, CN
|(ZoYp k0C4227  2  FROM (SELECT OWNER, OBJECT_TYPE, COUNT(*) CN FROM T1 GROUP BY OBJECT_TYPE), T2ITPUB个人空间 vS8Y9fX~DBl
  3  WHERE USERNAME = OWNER
$B\R f'iy uxJ4227  4  AND USERNAME LIKE 'SYS%';

执行计划
+H i&B%qSl4227----------------------------------------------------------ITPUB个人空间;p#CL4u }D;n6^5_%Q
   0      SELECT STATEMENT ptimizer=CHOOSE (Cost=54 Card=1467 Bytes=76284)ITPUB个人空间/cg1Jb#_n8e
   1    0   SORT (GROUP BY) (Cost=54 Card=1467 Bytes=76284)ITPUB个人空间x3r6P)Z0o9BDn;bN
   2    1     HASH JOIN (Cost=45 Card=1467 Bytes=76284)ITPUB个人空间/S'd c` g7S4A
   3    2       TABLE ACCESS (FULL) OF 'T2' (Cost=2 Card=2 Bytes=48)ITPUB个人空间V4u]'}4g8VW
   4    2       TABLE ACCESS (FULL) OF 'T1' (Cost=42 Card=5867 Bytes=164276)

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

SQL> SELECT OWNER, OBJECT_TYPE, CN
C-r| V7oB9X4227  2  FROM (SELECT OWNER, OBJECT_TYPE, COUNT(*) CN FROM T1 GROUP BY OBJECT_TYPE), T2ITPUB个人空间:Qo^0G3iE9GT
  3  WHERE USERNAME = OWNERITPUB个人空间-K5mD%G4d!hsR5F
  4  AND USERNAME LIKE 'SYS%';
0ASvA
E)i4227WHERE USERNAME = OWNERITPUB个人空间P1c+j8Z9E Eo'hQM
                 *
4wOK
},Lg'|S4227第3行出现错误:ITPUB个人空间VVW~5~'R~;~Z
ORA-00979:不是GROUP BY表达式

这时即使加上HINT也不行：

SQL> SELECT /*+ USE_HASH(T2) */ OWNER, OBJECT_TYPE, CNITPUB个人空间SNw}h|T
  2  FROM (SELECT OWNER, OBJECT_TYPE, COUNT(*) CN FROM T1 GROUP BY OBJECT_TYPE) T1, T2ITPUB个人空间9\%UIW/F.?7Y
  3  WHERE USERNAME = OWNER
,{4qv1F ],hV)FPD4227  4  AND USERNAME LIKE 'SYS%';
._ O;Wgo:n4227WHERE USERNAME = OWNER
p-Xx'[V9H7D4227                 *
$B []Gl%G'a
yC9KZ4227第3行出现错误:
9\,OJkD4227ORA-00979:不是GROUP BY表达式

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

SQL> CONN TEST/TEST@TESTZJ
'hwEpR&}5O8F D4227已连接。ITPUB个人空间M*q_2uo8U
SQL> SELECT * FROM V$VERSION;

BANNERITPUB个人空间/KKT,k#m*fTS&f^ h
----------------------------------------------------------------ITPUB个人空间'k(hhD5d6^m
Oracle Database10gEnterpriseEdition Release10.2.0.3.0 - 64bi
5L"M]"Gjic4227PL/SQL Release 10.2.0.3.0 - ProductionITPUB个人空间om2?;X,v6~'eR;Yg
CORE    10.2.0.3.0      ProductionITPUB个人空间*_H0iv&E-P
TNS for Linux: Version 10.2.0.3.0 - Production
dHm9m/d4227NLSRTL 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
)M*\A6I:g,p*D]H4227  2  FROM (SELECT OWNER, OBJECT_TYPE, COUNT(*) CN FROM T1 GROUP BY OBJECT_TYPE), T2
c*q-vu+`]%O(Ry2Ssi4227  3  WHERE USERNAME = OWNERITPUB个人空间,\C&Qh\x{Ep
  4  AND USERNAME LIKE 'SYS%';ITPUB个人空间{Q(v;u#mdD$r
FROM (SELECT OWNER, OBJECT_TYPE, COUNT(*) CN FROM T1 GROUP BY OBJECT_TYPE), T2
YY*RWD2H4227             *ITPUB个人空间$Hd)I:n+o2Brn&y3k
第2行出现错误:ITPUB个人空间r"_)}Eb)Um
ORA-00979:不是GROUP BY表达式

SQL> SET AUTOT ON EXPITPUB个人空间M#QL9t-C\\ lg
SQL> SELECT /*+ RULE */ USERNAME, OBJECT_TYPE, CNITPUB个人空间%y2o} O Am4A#G"l
  2  FROM (SELECT OWNER, OBJECT_TYPE, COUNT(*) CN FROM T1 GROUP BY OBJECT_TYPE),ITPUB个人空间__&q.A r
F;N4}S8S
  3  WHERE USERNAME = OWNERITPUB个人空间7Bu(c?C4g7e9@4Fp3x
  4  AND USERNAME LIKE 'SYS%';

USERNAME                       OBJECT_TYPE                 CNITPUB个人空间@2WC
wXW|ke
f7t
------------------------------ ------------------- ----------
;R'S-jc?(@[v7I7l4227SYS                            JOB                          4ITPUB个人空间3wvTiq` b
SYS                            LOB                         95ITPUB个人空间Hq,~f\
SYS                            RULE                         4
}N(i jB8v:\4227SYS                            TYPE                       933
MLbI6}.E4227.ITPUB个人空间6e)rmr#gtNdoQ
.
v+Q*B{8q\6@i4227.ITPUB个人空间\kY/x8Z {_
SYSTEM                         INDEX PARTITION             64ITPUB个人空间;`,FsdmsQaa
SYSTEM                         TABLE PARTITION             53

已选择53行。

Y'o_+\-z4227执行计划
@;R3YDr\Y4227----------------------------------------------------------

-------------------------------------
SYiJ EK4227| Id  | Operation            | Name |ITPUB个人空间@i v$S;Z'cq
-------------------------------------
+x,o?*\P4227|   0 | SELECT STATEMENT     |      |
U:[Y \*a4227|   1 |  SORT GROUP BY       |      |ITPUB个人空间$ZF4t;u l]ckW4D
|   2 |   MERGE JOIN         |      |ITPUB个人空间%d?W,X!k4B,wb
|   3 |    SORT JOIN         |      |
${G`B*um4227|*  4 |     TABLE ACCESS FULL| T2   |
_-K+S/t&h1Rh4227|*  5 |    SORT JOIN         |      |
\ZW6uT!P4227|*  6 |     TABLE ACCESS FULL| T1   |
8|0i-li&u(\3`J7u? P4227-------------------------------------

Predicate Information (identified by operation id):ITPUB个人空间T\!Q{/PB V
---------------------------------------------------

   4 - filter("USERNAME" LIKE 'SYS%')
@OMRv"T-hTZb4227   5 - access("USERNAME"="OWNER")

kP5t+cEbGT,G4227       filter("USERNAME"="OWNER")ITPUB个人空间+a"GL4Mof&}4v
   6 - filter("OWNER" LIKE 'SYS%')

SQL> SELECT /*+ USE_MERGE(T2) */ USERNAME, OBJECT_TYPE, CN
9V.F^-W]pe4227  2  FROM (SELECT OWNER, OBJECT_TYPE, COUNT(*) CN FROM T1 GROUP BY OBJECT_TYPE), T2
$jR1I8XM4227  3  WHERE USERNAME = OWNERITPUB个人空间*nci"sHm.W{
  4  AND USERNAME LIKE 'SYS%';
/XO nyeO}(J4R4227FROM (SELECT OWNER, OBJECT_TYPE, COUNT(*) CN FROM T1 GROUP BY OBJECT_TYPE), T2ITPUB个人空间`Py:`1g'o"Td;S
             *ITPUB个人空间Pvj^Prz2e#d9e
第2行出现错误:ITPUB个人空间JY$}/i's)Xz4l$N
ORA-00979:不是GROUP BY表达式

zR[
FNk4227SQL> SELECT /*+ USE_HASH(T2) */ USERNAME, OBJECT_TYPE, CN
#n'R6l:lPk6f4227  2  FROM (SELECT OWNER, OBJECT_TYPE, COUNT(*) CN FROM T1 GROUP BY OBJECT_TYPE), T2ITPUB个人空间)F7^hZH!M]
  3  WHERE USERNAME = OWNER
)RU4PyV4227  4  AND USERNAME LIKE 'SYS%';
G"V+sU4_AD4227FROM (SELECT OWNER, OBJECT_TYPE, COUNT(*) CN FROM T1 GROUP BY OBJECT_TYPE), T2
|:rhuX#s)nw4227             *
K ]8M4iXj#L'Si
a4227第2行出现错误:
E@'d9cQo4227ORA-00979:不是GROUP BY表达式

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