【ORACLE】什么时候ROWNUM等于0和ROWNUM小于0，两个条件不等价？

背景

有人在Dave的群里问了个问题，大概是这样的：
有一段sql，如何理解其中的rownum<=2

select a.id, b.id
  from test_tb a
  left join test_tb b
    on a.id = b.id
   and rownum <= 2;

因为“rownum<=2”这个条件，并不是在where后，而是作为left join的条件，那么a.id肯定是输出了全部值，但b.id会是什么情况？

实际查询结果是，两列全部值都输出了，没有空值，仿佛“rownum <= 2”这个条件并不存在。

分析

先从执行计划上入手

create table test_tb(id number);
insert into test_tb
  select rownum from dual connect by rownum <= 10;
commit;

EXPLAIN PLAN FOR
select a.id, b.id
  from test_tb a
  left join test_tb b
    on a.id = b.id
   and rownum <= 2;
   
select * from table(dbms_xplan.display);   

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3673628547

--------------------------------------------------------------------------------
| Id  | Operation            | Name            | Rows  | Bytes | Cost (%CPU)| Ti
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |                 |    10 |   260 |     4   (0)| 00
|   1 |  COUNT               |                 |       |       |            |
|*  2 |   HASH JOIN OUTER    |                 |    10 |   260 |     4   (0)| 00
|   3 |    TABLE ACCESS FULL | TEST_TB         |    10 |   130 |     2   (0)| 00
|   4 |    VIEW              | VW_DCL_2E38C6CE |     2 |    26 |     2   (0)| 00
|   5 |     TABLE ACCESS FULL| TEST_TB         |    10 |   130 |     2   (0)| 00
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("A"."ID"="ITEM_1"(+))

Note
-----

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
   - dynamic statistics used: dynamic sampling (level=2)

21 rows selected

产生了一个2行的view，但解释不了为什么右表的数据全部查出来了

然后我做了个尝试，把条件改成rownum<=0 ，结果竟然和rownum<=2一样。
改成rownum<0,结果也没变；只有当条件为rownum=0时，右侧数据没了.

问题来了：
我们知道，rownum永远都是大于等于1的，rownum<0和rownum=0 都明显是false，理论上应该等价，但此处却出现了差异！

原有认知被打破了!

再尝试rownum=1

select a.id, b.id
  from test_tb a
  left join test_tb b
    on a.id = b.id
   and rownum=1;

两列数据都出来了；如果改成rownum=2，那么左边数据是全的，右边没有数据。
所以，总结现象:

条件	左侧是否有数据	右侧是否有数据
rownum<1	有	有
rownum<0	有	有
rownum>0	有	有
rownum=1	有	有
rownum=0	有	无
rownum=2	有	无

这个规律是，首先rownum大于等于一个“大于1的值”，肯定没数据，可以排除；
然后rownum等于0，肯定也没数据，也可以排除；
剩下的就是分两类

• 等于1和大于0是一类，是可以有数据的；
• 小于1和小于0是一类，不应该有数据，但实际上查出了数据；

矛盾点就在于最后一点。

对于等于1和大于0，可以猜测右侧数据存在一个隐藏字段，每一行的值都为1。

于是查看一下 “rownum=0”时的执行计划

SQL> explain plan for select a.id, b.id
  2    from test_tb a
  3    left join test_tb b
  4      on a.id = b.id
  5     and rownum=0;

Explained


SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 404971544

--------------------------------------------------------------------------------
| Id  | Operation             | Name            | Rows  | Bytes | Cost (%CPU)| T
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |                 |    10 |   160 |     2   (0)| 0
|   1 |  COUNT                |                 |       |       |            |
|*  2 |   HASH JOIN OUTER     |                 |    10 |   160 |     2   (0)| 0
|   3 |    TABLE ACCESS FULL  | TEST_TB         |    10 |    30 |     2   (0)| 0
|   4 |    VIEW               | VW_DCL_2E38C6CE |     1 |    13 |     5 (100)| 0
|*  5 |     FILTER            |                 |       |       |            |
|   6 |      TABLE ACCESS FULL| TEST_TB         |    10 |    30 |     2   (0)| 0
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("A"."ID"="ITEM_1"(+))
   5 - filter(0=1)

19 rows selected

和rownum不是等于0的时候，多了一个 “filter(0=1)”
也就是说，“rownum=0” 被oracle 自动优化成了 “1=0” ,这是作为一个过滤条件，并且此处rownum始终是等于1的，印证了我之前的猜想。

为了排除版本特性的问题，我测试了11g/12c/19c/21c，表现均一致。

尝试等价改写

先不考虑小于0和小于1的情况，那么sql可以这么改

--改写前
 select a.id, b.id
  from test_tb a
  left join test_tb b
    on a.id = b.id
   and rownum <0;
--改写后   
    select a.id, b.id
  from test_tb a
  left join 
  (select rn,b.* from  (select rownum rn from dual),test_tb b )  b
    on a.id = b.id
   and rn <0;

但是这个肯定不满足小于0 和小于1的情况，于是再改一下

select a.id, b.id
    from test_tb a
    left join (select nvl(rn, -1E125) rn, b.*
                 from test_tb b
                 left join (select rownum rn from dual where rownum < 0)
                   on 1 = 1) b
      on a.id = b.id
     and rn < 0;

同时改“rownum < 0”、“rn < 0” 两处条件即可一一对应几乎所有场景，除了 “rownum=-1E125”的场景。当然再加个判断就可以完全对应了，不过意义不大。其实重点在于，处理这个逻辑时，期待引入一个无法用number类型表示的“最小的负数”。

以下附完整测试验证代码，标准组和对照组的输出完全一致

--测试数据准备 
   create table test_tb(id number);
insert into test_tb
  select rownum from dual connect by rownum <= 10;
commit;

-- 小于0  标准组
 select a.id, b.id
  from test_tb a
  left join test_tb b
    on a.id = b.id
   and rownum <0;
-- 小于0 对照组
  select a.id, b.id
    from test_tb a
    left join (select nvl(rn, -1E125) rn, b.*
                 from test_tb b
                 left join (select rownum rn from dual where rownum < 0)
                   on 1 = 1) b
      on a.id = b.id
     and rn < 0;

-- 等于0  标准组
 select a.id, b.id
  from test_tb a
  left join test_tb b
    on a.id = b.id
   and rownum =0;
-- 等于0 对照组
  select a.id, b.id
    from test_tb a
    left join (select nvl(rn, -1E125) rn, b.*
                 from test_tb b
                 left join (select rownum rn from dual where rownum = 0)
                   on 1 = 1) b
      on a.id = b.id
     and rn = 0;

-- 等于1  标准组
 select a.id, b.id
  from test_tb a
  left join test_tb b
    on a.id = b.id
   and rownum <0;
-- 等于1 对照组
  select a.id, b.id
    from test_tb a
    left join (select nvl(rn, -1E125) rn, b.*
                 from test_tb b
                 left join (select rownum rn from dual where rownum =1)
                   on 1 = 1) b
      on a.id = b.id
     and rn =1;

-- 小于1  标准组
 select a.id, b.id
  from test_tb a
  left join test_tb b
    on a.id = b.id
   and rownum <1;
-- 小于1 对照组
  select a.id, b.id
    from test_tb a
    left join (select nvl(rn, -1E125) rn, b.*
                 from test_tb b
                 left join (select rownum rn from dual where rownum < 1)
                   on 1 = 1) b
      on a.id = b.id
     and rn < 1;

-- 大于1  标准组
 select a.id, b.id
  from test_tb a
  left join test_tb b
    on a.id = b.id
   and rownum >1;
-- 大于1 对照组
  select a.id, b.id
    from test_tb a
    left join (select nvl(rn, -1E125) rn, b.*
                 from test_tb b
                 left join (select rownum rn from dual where rownum > 1)
                   on 1 = 1) b
      on a.id = b.id
     and rn > 1;

后记

rownum，在不同数据库上实现的情况不一样，或者说，就连join语法的执行逻辑也可能存在差异，因为我测了下其他几个支持rownum的数据库：

• 在openGauss 3.0.0中， rownum<=2,输出的结果是左边全部数据,右边数据2行;
• 在达梦8中，输出结果和openGauss 3.0.0一致；
• 在EDB14.4中，输出结果也和openGauss 3.0.0一致
• 在kinbase8中，rownum不能用于join…

也就是说，在大部分数据库中，都把rownum的限制条件简单认为是对右表限制条件,当然这更容易理解。

但是查询结果和oracle不一致，我无法判断这是ORACLE的特性还是BUG，欢迎大家来讨论或拍砖。