JpaSpecificationExecutor distinct find by with limit

[frostedpenguin]

I am having this findBy call where I am using distinct through the specification like so

repo.findBy(specification, q -> q.limit(limit).as(GeneralSearchProjection.class).all());

I am adding distinct through the specification

return ((root, query, cb) ->
        {
            query.distinct(true);
            return ...;
        })

the query generated by hibernate looks like this

select
        * 
    from
        (select
            distinct ck1_0.seq c0,
             ck1_0.desc c1
            rownum rn 
        from
            table ck1_0 
       ) r_0_ 
    where
        r_0_.rn<=? 
    order by
        r_0_.rn

the distinct won't work of course because of rownum column! Is this actually possible to be done?

[mp911de]

Care to add the SQL output and what you're expecting?

[quaff]

It's handled by Hibernate, I think you should upgrade your oracle database to at least 12c, then hibernate will use ANSI SQL standard syntax FETCH FIRST m ROWS ONLY and OFFSET n ROWS FETCH NEXT m ROWS ONLY instead of ROWNUM.

[mp911de]

Thanks @quaff. As. @quaff mentioned, SQL is generated by Hibernate and there's nothing Spring Data can do about.

[frostedpenguin]

@quaff yep you are right it is a legacy db issue, there is no valid SQL that could make this work either way! Anyway, thanks for the response!