Home » Support » Forum » Handling "is null" in where clause

Handling "is null" in where clause



I was playing aroung with JPQL and have found that there is a problem with "is null" queries. When you use "is null" in where clause, query runs correctly, but results are incorrect. Let me show you an example:

- entity

public class EntityA {
    Integer id;
    Integer simpleField;

    // (getters and setters here)

    // - populating database

    for (int x = 1; x < 100; x++) {
        EntityA a = new EntityA();

        if (Math.random() < 0.5) {
            a.setSimpleField(new Double(Math.random() * 1000).intValue());

    // - selecting records, where simpleField is null

    Query query = em.createQuery(
        "SELECT a FROM EntityA a WHERE a.simpleField IS NULL");
    List<EntityA> resultList = query.getResultList();
    System.out.println("ResultList size = " + resultList.size());

    if (resultList.size() == 0) {
        throw new Exception("There is no EntityA with simpleField == null ???");

ResultList.size() is always 0. It should be somwhere near 50. Is it a bug? Or I missed something?

I have attached full eclipse project with test case for this problem.


Thank you for this bug report and for the useful test program.

There was a bug in a query optimization that is applied to String and numeric fields, so null values in an Integer field (as defined in EntityA) were affected.

Please try the new build (rc4_05) that should fix the bug.

ObjectDB Support
ObjectDB - Fast Object Database for Java (JPA/JDO)

I just get new build and re-run test program. It's working perfectly well now. Thanks for very quick fix.

Post Reply

To post a reply and/or subscribe to update notifications - please login