title: Why does these two sqls give different result?
body: <p>I execute these sqls in PL/SQL.</p>
  
  <p>The original sql is:</p>
  
  <pre><code>select  * from (
    select RecID,TaskNum from umstat.tostatinfo
    where RecID in (select recid from umstat.tostatinfo where StreetID = 101 and checkNum &gt; 0)
    order by recID desc  
  ) where rownum &gt;= 1 and rownum &lt;= 50
  </code></pre>
  
  <p><code>RecID</code> is the primary key of umstat.tostatinfo. This sql will empty the column <code>TaskNum</code> and give the result below:</p>
  
  <p><a href="https://i.stack.imgur.com/JC0OH.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/JC0OH.png" alt="enter image description here"></a></p>
  
  <p>The weird thing is that If I delete the fourth line(<code>order by recID desc</code>) or remove the outermost restriction imposed by where(<code>where rownum &gt;= 1 and rownum &lt;= 50</code>) then the column <code>TaskNum</code> will come alive:</p>
  
  <p><a href="https://i.stack.imgur.com/spywD.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/spywD.png" alt="enter image description here"></a></p>
  
  <p>In order to use the function provided by <code>order by</code> and <code>rownum</code> I simplify original sql to a more concise one:</p>
  
  <pre><code>select  * from (
    select RecID,TaskNum from umstat.tostatinfo
    where StreetID = 101 and checkNum &gt; 0
    order by recID desc 
  ) where rownum &gt;= 1 and rownum &lt;= 50
  </code></pre>
  
  <p>Result of this sql looks normal:</p>
  
  <p><a href="https://i.stack.imgur.com/kR4Kj.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/kR4Kj.png" alt="enter image description here"></a></p>
  
  <p><strong>More tests:</strong></p>
  
  <p>Replace subquery to plain ids <code>(447914,447912,447905)</code>:</p>
  
  <pre><code>select * from (
    select RecID,TaskNum from umstat.tostatinfo
    where RecID in (447914,447912,447905)
    order by recID desc  
  ) where rownum &gt;= 1 and rownum &lt;= 50
  </code></pre>
  
  <p>Result is:</p>
  
  <p><a href="https://i.stack.imgur.com/kR4Kj.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/kR4Kj.png" alt="enter image description here"></a></p>
  
  <p>Change <code>recid</code> to <code>TaskNum</code> in subquery:</p>
  
  <pre><code>select  * from (
    select RecID,TaskNum from umstat.tostatinfo
    where TaskNum in (select TaskNum from umstat.tostatinfo where StreetID = 101 and checkNum &gt; 0)
    order by recID desc  
  ) where rownum &gt;= 1 and rownum &lt;= 50
  </code></pre>
  
  <p>Result is:</p>
  
  <p><a href="https://i.stack.imgur.com/kR4Kj.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/kR4Kj.png" alt="enter image description here"></a></p>
  
  <p>I want to know what makes all these happen underground, thanks for your time.</p>
  
  <hr>