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