Wrong result from statement with rownum

Question

I have some trouble with a generated SQL statement from Hibernate. I want to get all user with their birthday within a given time range or at a specific day. While the statement for a specific day work fine, the other one just returns the first n users, where n is the number of correct users.

Considering this data:

ID     name      birthdate
1      firstUser 12.07.1990
2      user1     25.04.2007
3      user2     15.05.1992
4      user3     01.04.1988

First the generated statement for a specific day:

select
    * 
from
    ( select
        distinct *
    from
        USERS user0_ 
    where
        calculateNextBirthday(user0_.birthDate) = TO_DATE('25.04.2018', 'dd.MM.yyyy')
    order by
        user0_.id asc ) 
where
    rownum <= ?; // This will be the row count of this statement with the same where clause, i.e. 1 if there is only 1 matching user

As I said this statement returns the correct user (user1 with ID 2).

The same statement with a between:

select
    * 
from
    ( select
        distinct *
    from
        USERS user0_ 
    where
        calculateNextBirthday(user0_.birthDate) between TO_DATE('01.04.2018', 'dd.MM.yyyy') and TO_DATE('30.04.2018', 'dd.MM.yyyy')
    order by
        user0_.id asc ) 
where
    rownum <= ?; // This will be the row count of this statement with the same where clause, i.e. 1 if there is only 1 matching user

This statment has a strange behavior. If I execute the inner select statement, the result contains the correct users (user1). But executing the whole statement it will return the first user (firstUser with ID 1). Changing the date like this:

select
    * 
from
    ( select
        distinct *
    from
        USERS user0_ 
    where
        calculateNextBirthday(user0_.birthDate) between TO_DATE('01.04.2018', 'dd.MM.yyyy') and TO_DATE('30.06.2018', 'dd.MM.yyyy')
    order by
        user0_.id asc ) 
where
    rownum <= ?; // This will be the row count of this statement with the same where clause, i.e. 1 if there is only 1 matching user

will return the first two users (firstUser and user1), because there two matching users (user1 and user2)

And if I remove the rownum from where outer statement it returns only the correct users (example below returns user1).

select
    * 
from
    ( select
        distinct *
    from
        USERS user0_ 
    where
        calculateNextBirthday(user0_.birthDate) between TO_DATE('01.04.2018', 'dd.MM.yyyy') and TO_DATE('30.04.2018', 'dd.MM.yyyy')
    order by
        user0_.id asc ) 

So I have no idea where Oracle gets the first user entry.

Am I doing something wrong here or is there a bug in Oracle Oracle Database 10g Express Edition Release 10.2.0.1.0?

Answer 1

Seems to work for me (11g). I don't use your calculateNextBirthday function though:

SQL> drop table t_users
Table dropped.
SQL> create table t_users
(
id number,
name varchar2(100),
birthdate date
)
Table created.
SQL> insert into t_users (id,name,birthdate) values (1,'firstUser',to_date('12.07.1990','DD.MM.YYYY'))
1 row created.
SQL> insert into t_users (id,name,birthdate) values (2,'user1',to_date('25.04.2007','DD.MM.YYYY'))
1 row created.
SQL> insert into t_users (id,name,birthdate) values (3,'user2',to_date('15.05.1992','DD.MM.YYYY'))
1 row created.
SQL> insert into t_users (id,name,birthdate) values (4,'user3',to_date('01.04.1988','DD.MM.YYYY'))
1 row created.
SQL> commit
Commit complete.
SQL> select
    * 
from
    ( select
        distinct *
    from
        t_users user0_ 
    where
        to_date( to_char(birthdate, 'DD.MM') || '.' || to_char(sysdate, 'YYYY'), 'DD.MM.YYYY' ) between TO_DATE('01.04.2018', 'dd.MM.yyyy') and TO_DATE('30.04.2018', 'dd.MM.yyyy')
    order by
        user0_.id asc ) 
where
    rownum <= 2

        ID
----------
NAME                                                                            
--------------------------------------------------------------------------------
BIRTHDATE
---------
         2
user1                                                                           
25-APR-07

         4
user3                                                                           
01-APR-88


2 rows selected.

Answer 2

You can't rely on rownum in this situation because the order by is in the inner select. Rownum is being determined before, and therefore overriding, the order by. It's not a great practice to rely on rownum in general.

With the caveat that I know Oracle SQL and am not inherently familiar with Hibernate, my suggestion is the following:

I would consider creating a view with the information you need and seeing if Hibernate handles that better, otherwise I would try to override the Hibernate generated SQL statement with one that just uses the inner select, because there isn't an Oracle reason to wrap that select with the outer statement.