发表于: 2006.10.27 19:58
分类: ORACLE , Bug
出处: http://yangtingkun.itpub.net/post/468/224408
---------------------------------------------------------------
今天又碰到一个奇怪的错误，ROWNUM伪列的值居然返回空。
首先重现一下错误：
SQL> CREATE TABLE T AS SELECT * FROM DBA_OBJECTS;
表已创建。
SQL> SELECT ROWNUM RN, ROW_NUMBER() OVER(ORDER BY OBJECT_NAME) RN_OVER
2 FROM (SELECT DISTINCT OBJECT_NAME FROM T)
3 WHERE ROWNUM < 10;
RN RN_OVER
---------- ----------
1
2
3
4
5
6
7
8
9
已选择9行。
只要将ROWNUM放到查询的外层，就没有错误了：
SQL> SELECT ROWNUM RN, A.* FROM
2 (
3 SELECT ROW_NUMBER() OVER(ORDER BY OBJECT_NAME) RN_OVER
4 FROM (SELECT DISTINCT OBJECT_NAME FROM T)
5 WHERE ROWNUM < 10
6 ) A;
RN RN_OVER
---------- ----------
1 1
2 2
3 3
4 4
5 5
6 6
7 7
8 8
9 9
已选择9行。
进一步观察发现，问题不仅和ROWNUM的位置有关，还与分析函数ROW_NUMBER() OVER()有关。换成其他的分析函数，则不会出现这个问题：
SQL> SELECT ROWNUM RN, COUNT(*) OVER(ORDER BY OBJECT_NAME) CNT
2 FROM (SELECT DISTINCT OBJECT_NAME FROM T)
3 WHERE ROWNUM < 10;
RN CNT
---------- ----------
1 1
2 2
3 3
4 4
5 5
6 6
7 7
8 8
9 9
已选择9行。
SQL> COL MAX FORMAT A45
SQL> SELECT ROWNUM RN, MAX(OBJECT_NAME) OVER(ORDER BY OBJECT_NAME) MAX
2 FROM (SELECT DISTINCT OBJECT_NAME FROM T)
3 WHERE ROWNUM < 10;
RN MAX
---------- ---------------------------------------------
1 /1005bd30_LnkdConstant
2 /10076b23_OraCustomDatumClosur
3 /1025308f_SunTileScheduler
4 /10297c91_SAXAttrList
5 /103a2e73_DefaultEditorKitEndP
6 /1048734f_DefaultFolder
7 /10501902_BasicFileChooserUINe
8 /105072e7_HttpSessionBindingEv
9 /106ba0a5_ArrayEnumeration
已选择9行。
而且，问题还与内层查询有关，当内层查询包括GROUP BY、DISTINCT/UNIQUE或ORDER BY等操作时，会出现这个问题：
SQL> SELECT ROWNUM RN, ROW_NUMBER() OVER(ORDER BY OBJECT_NAME) RN_OVER
2 FROM (SELECT OBJECT_NAME FROM T GROUP BY OBJECT_NAME)
3 WHERE ROWNUM < 10;
RN RN_OVER
---------- ----------
1
2
3
4
5
6
7
8
9
已选择9行。
SQL> SELECT ROWNUM RN, ROW_NUMBER() OVER(ORDER BY OBJECT_NAME) RN_OVER
2 FROM (SELECT OBJECT_NAME FROM T ORDER BY OBJECT_NAME)
3 WHERE ROWNUM < 10;
RN RN_OVER
---------- ----------
1
2
3
4
5
6
7
8
9
已选择9行。
SQL> SELECT ROWNUM RN, ROW_NUMBER() OVER(ORDER BY OBJECT_NAME) RN_OVER
2 FROM (SELECT OBJECT_NAME FROM T)
3 WHERE ROWNUM < 10;
RN RN_OVER
---------- ----------
1 1
2 2
3 3
4 4
5 5
6 6
7 7
8 8
9 9
已选择9行。
而且，问题还与分析函数ROW_NUMBER() OVER()中的排序字段有关：
SQL> SELECT ROWNUM RN,
2 ROW_NUMBER() OVER(ORDER BY OBJECT_TYPE) RN_OVER
3 FROM
4 (
5 SELECT OBJECT_TYPE, CREATED, COUNT(*) CN
6 FROM T
7 GROUP BY OBJECT_TYPE, CREATED
8 )
9 WHERE ROWNUM < 10;
RN RN_OVER
---------- ----------
1
2
3
4
5
6
7
8
9
已选择9行。
SQL> SELECT ROWNUM RN,
2 ROW_NUMBER() OVER(ORDER BY CREATED) RN_OVER
3 FROM
4 (
5 SELECT OBJECT_TYPE, CREATED, COUNT(*) CN
6 FROM T
7 GROUP BY OBJECT_TYPE, CREATED
8 )
9 WHERE ROWNUM < 10;
RN RN_OVER
---------- ----------
1 1
2 2
3 3
4 4
5 5
6 6
7 7
8 8
9 9
已选择9行。
SQL> SELECT ROWNUM RN,
2 ROW_NUMBER() OVER(ORDER BY CREATED) RN_OVER
3 FROM
4 (
5 SELECT OBJECT_TYPE, CREATED, COUNT(*) CN
6 FROM T
7 GROUP BY CREATED, OBJECT_TYPE
8 )
9 WHERE ROWNUM < 10;
RN RN_OVER
---------- ----------
1
2
3
4
5
6
7
8
9
已选择9行。
最后，这个错误在9204中出现，在10g中已经不再出现：
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 Linux: Version 9.2.0.4.0 - Production
NLSRTL Version 9.2.0.4.0 - Production
SQL> SELECT ROWNUM RN, ROW_NUMBER() OVER(ORDER BY OBJECT_NAME) RN_OVER
2 FROM (SELECT DISTINCT OBJECT_NAME FROM T)
3 WHERE ROWNUM < 10;
RN RN_OVER
---------- ----------
1
2
3
4
5
6
7
8
9
已选择9行。
SQL> CONN YANGTK/YANGTK@YTK已连接。
SQL> CREATE TABLE T AS SELECT * FROM DBA_OBJECTS;
表已创建。
SQL> SELECT ROWNUM RN, ROW_NUMBER() OVER(ORDER BY OBJECT_NAME) RN_OVER
2 FROM (SELECT DISTINCT OBJECT_NAME FROM T)
3 WHERE ROWNUM < 10;
RN RN_OVER
---------- ----------
1 1
2 2
3 3
4 4
5 5
6 6
7 7
8 8
9 9
已选择9行。
根据上面的测试，基本上可以判断，子查询中首先对某个字段进行了排序，而外层的ROW_NUMBER() OVER()中也对这个字段进行排序的话，那么与ROW_NUMBER() OVER()分析函数同层的ROWNUM会返回NULL。
查询了一下METALINK，发现果然是Oracle的bug，bug号：3760380。Oracle在9208、10.1.0.4和10.2.0.1中解决了这个BUG。不过METALINK上没有给出详细的错误原因，以及何时可能导致错误出现，甚至没有给出解决办法。
re: ROWNUM伪列返回空 
