Hello,

Hibernate version: 3.0.5

Mapping documents:

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping>
<class name="com.ex.Person" table="PERSON">
<id name="id" column="PERSON_ID">
<generator class="native">
<param name="sequence">PERSON_ID_SEQUENCE</param>
</generator>
</id>
<version name="version" column="VERSION"/>
<property name="name">
<column name="NAME"/>
</property>
<one-to-one name="address" cascade="all"/>
</class>
</hibernate-mapping>

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping>
<class name="com.ex.Address" table="ADDRESS">
<id name="id">
<column name="ADDRESS_ID"/>
<generator class="foreign">
<param name="property">person</param>
</generator>
</id>
<version name="version" column="VERSION"/>
<one-to-one name="person" constrained="true"/>
<property name="city">
<column name="CITY"/>
</property>
<property name="address">
<column name="ADDRESS"/>
</property>
</class>
</hibernate-mapping>

Code between sessionFactory.openSession() and session.close():

Criteria criteria = session.createCriteria(Person.class);

criteria.add(Restrictions.isNull("address");

OR

criteria.add(Restrictions.isNotNull("address");

criteria.list();

Name and version of the database you are using:

PostgreSQL 8

The generated SQL (show_sql=true):

for Restrictions.isNull("address") the join and where statements are:

from PERSON this_ left outer join ADDRESS address1_ on this_.PERSON_ID=address1_.ADDRESS_ID where this_.PERSON_ID is null

and for Restrictions.isNotNull("address"):

from PERSON this_ left outer join ADDRESS address2_ on this_.PERSON_ID=address2_.ADDRESS_ID where this_.PERSON_ID is not null


The problem is:

If I use Restrictions.isNull("address") empty list is returned and when I use Restrictions.isNotNull("address") all persons are returned.

What do I do wrong?

Best regards,
Piotrek

Nobody knows what I do wrong?

Best regards,
Piotrek

I'm in the same situation as pbzdyl, I'm trying to create a criteria query with Restrictions.isNull() passing as a parameter an attribute that is an AssociationType. What's the correct way to do this?

Best regards,

Nobody knows? Is this the normal behavior?

Best regards,

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.

I've also just run into this issue, using Hibernate 3.2.4sp1. In my case, I have classes set up via annotations, but it's the same situation, putting a restriction on a one-to-one mapping field isn't behaving correctly. Here is an outline of what my code is doing:

@Entity
public class A {
...
@OneToOne(optional = true, mappedBy = "a")
public void B getB() { ... }
}

@Entity
public class B {
...
@OneToOne(optional=false)
@JoinColumn(name = "A", unique = true, nullable = false)
public void A getA() { ... }
}

Then I build the query the same way Piotrek did.

The resulting SQL statement has the same problem that Piotrek's did - specifically, it says "where this_.id is null" when it should say "where b_.id is null" (or in Piotrek's case, "where address_.id is null"). The problem is that Hibernate is building the IS NULL clause using the field from the wrong table; it should be using the table from the right side of the left outer join, not the left side. It would be even cooler if Hibernate were smart enough to figure out that it should just be doing an inner join in this case, but that's probably asking too much. :)

I see a bug report on JIRA for this (HHH-1057) which has mention of the FAQ entry, but that report was for Hibernate 3.0.1 and hasn't been touched in over a year. Is it ever going to be addressed, or just left as a known issue? I haven't dug into the Hibernate code enough to figure out how hard it would be to get it to pick the right column/alias for the IS NULL check...

well i have the same problem but its not a @OneToOne relation, is a @ManyToMany, and instead of WHERE A.b.id=null is making an WHERE A.id=null.

it sux coz i cant make it generic without that functionality.