-->
These old forums are deprecated now and set to read-only. We are waiting for you on our new forums!
More modern, Discourse-based and with GitHub/Google/Twitter authentication built-in.

All times are UTC - 5 hours [ DST ]



Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 4 posts ] 
Author Message
 Post subject: Error using keyword ALL in query with WHERE NOT
PostPosted: Tue Apr 04, 2017 5:27 am 
Newbie

Joined: Tue Apr 04, 2017 4:53 am
Posts: 2
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.


Top
 Profile  
 
 Post subject: Re: Error using keyword ALL in query with WHERE NOT
PostPosted: Tue Apr 04, 2017 6:37 am 
Hibernate Team
Hibernate Team

Joined: Thu Sep 11, 2014 2:50 am
Posts: 1638
Location: Romania
Sounds like a bug. Please provide a replicating test case and open a new Jira issue.

_________________
If you liked my answer, you are going to love my High-Performance Java Persistence book and my blog as well.


Top
 Profile  
 
 Post subject: Re: Error using keyword ALL in query with WHERE NOT
PostPosted: Tue Apr 04, 2017 9:33 am 
Newbie

Joined: Tue Apr 04, 2017 4:53 am
Posts: 2
I opened an issue :https://hibernate.atlassian.net/browse/HHH-11613?filter=-2 and I provided a replicating test case.


Top
 Profile  
 
 Post subject: Re: Error using keyword ALL in query with WHERE NOT
PostPosted: Tue Apr 04, 2017 10:06 am 
Hibernate Team
Hibernate Team

Joined: Thu Sep 11, 2014 2:50 am
Posts: 1638
Location: Romania
Ok, thanks.

_________________
If you liked my answer, you are going to love my High-Performance Java Persistence book and my blog as well.


Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 4 posts ] 

All times are UTC - 5 hours [ DST ]


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.