【oracle】什么时候rownum等于0和rownum小于0，两个条件不等价？ -m6米乐安卓版下载

背景

有人在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，肯定也没数据，也可以排除；
剩下的就是分两类

• 等于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，欢迎大家来讨论或拍砖。

• 本文作者：
• 本文链接：
• 米乐app官网下载的版权声明： 本博客所有文章除特别声明外，均采用 许可协议。转载请注明出处！