Hello,
I tried to find an open issue about this in
https://hibernate.atlassian.net but I didn't, neither a post about it in this forum.
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:
| id | field | field2 |
| 1 | 50 | 30 |
| 2 | 20 | 40 |
| 3 | 110 | 70 |
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.
Do you know if there is an open issue, I didn't manage to find, about this. Or should I create a new issue ?
Thank you for your time.
Login
FAQ
Search
