See : https://forum.hibernate.org/viewtopic.php?f=1&t=1044175

I have the following query in JPQL :

Code:
SELECT x.* FROM AGETable x WHERE ( NOT (x.field > ALL ( SELECT t.field2 FROM AGETable t ) ) )

For the following Table 'AGETable' :
Quote:

I expect to retrieve the records with the id 1 and 2 because fields 50 and 20 are not > than All field2 in the Table.
The above query return those 2 records if executed directly in database.

The problem is only the record n°2 is returned because the above query is transformed by Hibernate into this query :

Code:
select
table0_.id as id1_0_,
table0_.field as field2_0_,
table0_.field2 as field3_0_
from
AGEtable table0_
where
table0_.field<=all (
select
table1_.field2
from
AGEtable table1_
)

And yes, only the record n°2 has a field value <= to all field2 values.

The problem here is that : "WHERE NOT x.field > ALL t.field2" is not equivalent to "WHERE x.field <= ALL t.field2".

I suppose the keyword "ALL" should become an "ANY" with the removing of the "NOT" and the changing of ">" into "<=", for the query to be OK.

I have the exact problem using ANY instead of ALL and expecting only the record n°2 but the hibernate query returns me the records 1 and 2.