-->
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.  [ 3 posts ] 
Author Message
 Post subject: Incorrect SQL from criteria query isNull on one-to-one assoc
PostPosted: Thu Oct 26, 2006 4:31 pm 
Newbie

Joined: Thu Oct 26, 2006 7:45 am
Posts: 2
Hi,

I am having trouble using a criteria query to retrieve objects with a one-to-one relationship to other objects with a restriction that the other side of the relationship is not set.

As a simple example, I have EntityA which has an ID and a name, and EntityB which has an ID and a name. There is a bidirectional one-to-one mapping between the entities, mapped by the entityA property of EntityB.

I have created an Eclipse project that includes source for EntityA and EntityB, and uses the Hibernate 3.2 (GA) Core and Annotations libraries and the MySQL Connector (3.1.13) to demonstrate the above. It can be downloaded from http://home.zip-it.org/~wrose/hibcq.zip.

I have done the configuration using Hibernate annotations. The generated schema looks like (MySQL dialect):

Code:
create table EntityA (
  id integer not null auto_increment,
  name varchar(64) not null,
  primary key (id)
) type=InnoDB;

create table EntityB (
  id integer not null auto_increment,
  name varchar(64) not null,
  entityA_id integer,
  primary key (id)
) type=InnoDB;

alter table EntityB
   add index FK457309F4DADA97B (entityA_id),
   add constraint FK457309F4DADA97B foreign key (entityA_id) references EntityA (id);


If I run a Criteria query looking for instances of EntityB where the associated EntityA is not set, I get a sensible SQL query:

Java:
Code:
List l = s.createCriteria(EntityB.class).add(Restrictions.isNull("entityA")).list();


SQL:
Code:
select this_.id as id1_1_,
       this_.name as name1_1_,
       this_.entityA_id as entityA3_1_1_,
       entitya2_.id as id0_0_,
       entitya2_.name as name0_0_
from EntityB this_ left outer join EntityA entitya2_ on this_.entityA_id=entitya2_.id
where this_.entityA_id is null


But, if I try to run a similar Criteria query from the other end of the association, the where clause is not what I would expect:

Java:
Code:
List l = s.createCriteria(EntityA.class).add(Restrictions.isNull("entityB")).list();


SQL:
Code:
select this_.id as id0_1_,
       this_.name as name0_1_,
       entityb2_.id as id1_0_,
       entityb2_.name as name1_0_,
       entityb2_.entityA_id as entityA3_1_0_
from EntityA this_ left outer join EntityB entityb2_ on this_.id=entityb2_.entityA_id
where this_.id is null


I think this should end with 'where entityb2_.id is null'. I tried using the .createCriteria() method that allows you to specify restrictions on an association, e.g.:

Code:
List l = s.createCriteria(EntityA.class).createCriteria("entityB").add(Restrictions.isNull("id")).list();


This leads to the left join becoming an inner join, however, which means the linked id can never be null.

Does anybody know if the behaviour I am seeing is a bug or the intended effect?


Top
 Profile  
 
 Post subject:
PostPosted: Tue Jun 26, 2007 7:33 pm 
Newbie

Joined: Thu Oct 09, 2003 11:07 pm
Posts: 7
I believe I had the same problem as described above. I found a suitable workaround offered in the "Advanced Problems" FAQ under the heading "The query language IS NULL syntax won't work with a one-to-one association!"

The solution worked for me! Hope this helps. I swore I would post an answer if I found one. Hope this helps.


Top
 Profile  
 
 Post subject:
PostPosted: Wed Jun 27, 2007 5:31 am 
Newbie

Joined: Thu Oct 26, 2006 7:45 am
Posts: 2
I haven't checked whether it fixes the problem (as I worked around it some other way -- not sure where the original problem was!), but thanks for pointing out that bit in the docs. I think it has a very high probability of answering my question.


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