背景

有人在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，歡迎大家來討論或拍磚。

• 本文作者： DarkAthena
• 本文鏈接： https://www.darkathena.top/archives/left-join-rownum-equ0-less0-diff
• 版權聲明： 本博客所有文章除特別聲明外，均采用CC BY-NC-SA 3.0 許可協議。轉載請注明出處！