When a custom function wraps a subquery that uses order by, the sql for the order by is messed up.
This is the HQL:
SELECT d.name
FROM Document d
WHERE d.id IN (LIMIT((
SELECT subDoc.id
FROM Document subDoc
ORDER BY subDoc.name ASC NULLS LAST
),1))
The custom function LIMIT as can be found in the attached testcase in class MyMySQLDialect applies a limit(second argument) to the subquery(first argument) and returns the result. The problem is, that the function gets a messed up subquery for MySQL. The same function works for other databases in the sense that the order by is not messed up.
By messed up I mean that the following is produced:
select document1_.id from Document document1_ order by document1_.namecase when is null then 1 else 0 end, ASC
instead of the expected:
select document1_.id from Document document1_ order by case when document1_.name is null then 1 else 0 end, document1_.name ASC
|