Using limit() and offset() in subqueries not working for offset > 0

[Sven Panko]

Hey all,

I discovered a problem when using limit() and offset() in subqueries in the SQL component (QueryDSL 4.0.6). I am basically doing this:

subQuery = new SQLQuery();
subQuery.from(YYY).where(...).select(YYY.ID).distinct();
subQuery.limit(10);
subQuery.offset(20);


query.from(XXX).where(XXX.id.in(subQuery));

This yields the following SQL snippet (only WHERE clause is shown):

WHERE
    a.ID IN
    (
        SELECT
            *
        FROM
            (
                SELECT
                    a.*,
                    rownum rn
                FROM
                    (
                        SELECT DISTINCT
                            a1.ID
                        FROM
                            FOO.BAR a1
                        WHERE
                            a1.X_ID = 123 ) a)
        WHERE
            rn > 20
        AND rownum <= 30)

This leads to an exception in my case (Oracle 11g), because the subquery now return more than one value. If I omit the offset, this SQL is generated:

WHERE
    a.ID IN
    (
        SELECT
            *
        FROM
            (
                SELECT DISTINCT
                    a1.ID
                FROM
                            FOO.BAR a1
                        WHERE
                            a1.X_ID = 123 )
        WHERE
            rownum <= 10)        

which is correct and yields the expected result.

Is it possible to explicitly return only the columns of the original subquery in the generated container query like this:

WHERE
    a.ID IN
    (
        SELECT
            a.ID
        FROM
            (
                SELECT
                    a.*,
                    rownum rn
                FROM
                    (
                        SELECT DISTINCT
                            a1.ID
                        FROM
                            FOO.BAR a1
                        WHERE
                            a1.X_ID = 123 ) a)
        WHERE
            rn > 20
        AND rownum <= 30)

Or is there some other way to do what I do?

Thanks in advance

Sven

[timowest]

Hi.

Could you create an issue for this on GitHub?

Timo

[Sven Panko]

Hi Timo,

I created issue 1667 for this:

https://github.com/querydsl/querydsl/issues/1667

Regards

Sven