CODEWITHSUNDEEP
sqloracle-databasehibernateoracle11g
Willian
Willian

Reputation: 532

Wrong results in query with ORDER BY and ROWNUM

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:

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 <= 10;

Trying to understand the problem I created the following sandbox:

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;

Executing the select in this example it returns:

id  name        description
1   DDD-1234    (null)
2   DDD-1234    (null)
3   DDD-1234    (null)
4   DDD-1234    (null)

In my understanding it should return:

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

What is wrong? Why is it not returning what I expected it to?

Edited: Removing the ORDER BY clause it returns the expected result. But why?

Edited 2: The execution plan is the following:

---------------------------------------------------------------------------------
| 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<=10)
   3 - filter(ROWNUM<=10)

Note
-----
   - dynamic sampling used for this statement (level=2)

Upvotes: 0

Views: 1246

Answers (2)

ospet
ospet

Reputation: 62

In the subquery, there is something that seems strange to me:

SELECT this_.* FROM plate **this_**
WHERE this_.id IN 
(SELECT DISTINCT this_.id AS y0_ FROM plate **this_** )
ORDER BY this_.name asc

The this_ alias is used twice for 2 different Selects. Maybe this causes something wrong with the results. I am not sure what this_ is pointing to in the order by clause for example. I cannot guarantee this is the reason for your issue but it seems very odd to me.

Upvotes: 1

mauro
mauro

Reputation: 5950

Very interesting... I found it is the combination of these three elements that causes the wrong results:

  1. WHERE this_.id IN (SELECT...)
  2. ORDER BY as you pointed out in your Edit
  3. ROWNUM in the outer select

If you omit one of the three elements listed here above everything is ok. Don't know what confuses Oracle. To maintain the structure of your query I'd use ROW_NUMBER() in the subquery instead of ROWNUM in the outer Select. This way:

SELECT id, name, description
FROM
    (SELECT this_.*, ROW_NUMBER() OVER(ORDER BY this_.name ASC) AS RN
     FROM plate this_
     WHERE this_.id IN 
       (SELECT DISTINCT this_.id AS y0_ FROM plate this_ )
    )
WHERE rn <= 10
ORDER BY rn asc;

Upvotes: 0

Related Questions