-->
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.  [ 6 posts ] 
Author Message
 Post subject: MySQL syntax error when testing collection size
PostPosted: Wed Feb 11, 2004 3:39 pm 
Newbie

Joined: Wed Feb 11, 2004 3:09 pm
Posts: 3
I have a Hibernate class like this:
public class Resource {

public Set getUsers() {
...
}

}

I want to get all Resources that have no users. I tried two method, but both failed with MySQL syntax error

(1) HQL: "from Resource where users.size=0"
Translated SQL sth like: "select * from Resource where (select count(*) from ResourceUsers where Resource.id=ResourceUsers.resource_id)=0"
Error near the 2nd select

(2) HQL: "from Resource where not exists elements(Resource.users)"
Translated SQL sth like: "select * from Resource where not exists(select ResourceUsers.resource_id from ResourceUsers where Resource.id=ResourceUsers.resource_id)"
Error near exists

I tested manually, both SQL don't work with MySQL 4.1. I wonder if this is a bug or there is anything to work around the mysql limit?

Thanks,

- Howard


Top
 Profile  
 
 Post subject:
PostPosted: Wed Feb 11, 2004 3:46 pm 
Hibernate Team
Hibernate Team

Joined: Tue Sep 09, 2003 2:10 pm
Posts: 3246
Location: Passau, Germany
How does the generated SQL look like, and what is mysql complaining about?


Top
 Profile  
 
 Post subject:
PostPosted: Wed Feb 11, 2004 4:34 pm 
Newbie

Joined: Wed Feb 11, 2004 3:09 pm
Posts: 3
gloeglm wrote:
How does the generated SQL look like, and what is mysql complaining about?


I had the SQL in the original post:

(1) Translated SQL sth like: "select * from Resource where (select count(*) from ResourceUsers where Resource.id=ResourceUsers.resource_id)=0"
MySQL complains: syntax error near "select count(*) from ResourceUsers where..."

(2) Translated SQL sth like: "select * from Resource where not exists(select ResourceUsers.resource_id from ResourceUsers where Resource.id=ResourceUsers.resource_id)"

MySQL complains: syntax error near "exists(select ResourceUsers.resource_id ..."


Top
 Profile  
 
 Post subject:
PostPosted: Wed Feb 11, 2004 4:39 pm 
Hibernate Team
Hibernate Team

Joined: Tue Sep 09, 2003 2:10 pm
Posts: 3246
Location: Passau, Germany
Are you sure you are using a mysql version which supports subselects? Can you get the SQL to work by modifying it manually?


Top
 Profile  
 
 Post subject:
PostPosted: Wed Feb 11, 2004 5:18 pm 
Hibernate Team
Hibernate Team

Joined: Tue Aug 26, 2003 12:50 pm
Posts: 5130
Location: Melbourne, Australia
Please read the documentation fully, regarding the use of size() and subselects.


Top
 Profile  
 
 Post subject:
PostPosted: Wed Feb 11, 2004 6:41 pm 
Newbie

Joined: Wed Feb 11, 2004 3:09 pm
Posts: 3
My mistake... I was using MySQL 4.0.

Just tried MySQL 4.1, the subselect works (by manual query to DB), but the JDBC driver is not working :(

Thanks all for your help.


Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 6 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.