Peculiar thing about HQL subquerries - at least in Hibernate 4.1: if you use alias in subquery, it refers to the whole table, not just to its subset that is related with main query. For example - suppose that entity
Bar has integer field
idx and we'd like to get
Bar with minimal
idx for each
Foo.
This query will produce WRONG result:
SELECT Foo FROM Foo f JOIN f.bar b
WHERE b.idx = (SELECT MIN(b.idx) FROM b)
Subquery will refer to globally minimal
idx
To get what we wanted use following query:
SELECT Foo FROM Foo f JOIN f.bar b
WHERE b.idx = (SELECT MIN(fb.idx) FROM f.bar fb)
Here subquery indeed refers to
Bar connected with current
Foo
