Wrong results in query with ORDER BY and ROWNUM
<p>I have a problem with a query generated by an ORM framework to limit and order the results in the Oracle Database 11.2.0.1.0    64bit Production. The generated select looks like this:</p>

<pre><code>SELECT *
FROM
  (SELECT this_.*
   FROM plate this_
   WHERE this_.id IN 
      (SELECT DISTINCT this_.id AS y0_ FROM plate this_ )
   ORDER BY this_.name asc)
WHERE rownum &lt;= 10;
</code></pre>

<p>Trying to understand the problem I created the following sandbox:</p>

<pre><code>create table plate ( id integer primary key, 
                    name varchar2(30), 
                    description varchar2(255) );

insert into plate values ( 1, 'AAA-1234', 'test1' );
insert into plate values ( 2, 'BBB-1234', 'test2' );
insert into plate values ( 3, 'CCC-1234', 'test3' );
insert into plate values ( 4, 'DDD-1234', 'test4' );
commit;
</code></pre>

<p>Executing the select in this example it returns:</p>

<pre>id  name        description
1   DDD-1234    (null)
2   DDD-1234    (null)
3   DDD-1234    (null)
4   DDD-1234    (null)
</pre>

<p>In my understanding it should return:</p>

<pre>id  name        description
1   AAA-1234    test1
2   BBB-1234    test2
3   CCC-1234    test3
4   DDD-1234    test4
</pre>

<p>What is wrong? Why is it not returning what I expected it to?</p>

<p><strong>Edited:</strong>
Removing the ORDER BY clause it returns the expected result. But why?</p>

<p><strong>Edited 2:</strong>
The execution plan is the following:</p>

<pre><code>---------------------------------------------------------------------------------
| Id  | Operation               | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |       |       |       |     4 (100)|          |
|*  1 |  COUNT STOPKEY          |       |       |       |            |          |
|   2 |   VIEW                  |       |     4 |   636 |     4  (25)| 00:00:01 |
|*  3 |    SORT ORDER BY STOPKEY|       |     4 |   636 |     4  (25)| 00:00:01 |
|   4 |     TABLE ACCESS FULL   | PLATE |     4 |   636 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------------

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

   1 - filter(ROWNUM&lt;=10)
   3 - filter(ROWNUM&lt;=10)

Note
-----
   - dynamic sampling used for this statement (level=2)
</code></pre>