436 words

Handling "is null" in where clause

2010-09-24 10:23


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.

lwalkowski's picture
Joined on 2010-08-25
User Post #2
2010-09-24 14:58

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)
support's picture
Joined on 2010-05-03
User Post #51
2010-09-24 20:03

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

lwalkowski's picture
Joined on 2010-08-25
User Post #3

Post Reply

Please read carefully the posting instructions - before posting to the ObjectDB website.

  • You may have to disable pop up blocking in order to use the toolbar (e.g. in Chrome).
  • Use ctrl + right click to open the browser context menu in the editing area (e.g. for using a browser spell checker).
  • To insert formatted lines (e.g. Java code, stack trace) - select a style in the toolbar and then insert the text in the new created block.
  • Avoid overflow of published source code examples by breaking long lines.
  • You may mark in paragraph code words (e.g. class names) with the code style (can be applied by ctrl + D).
  • Long stack traces (> 50 lines) and complex source examples (> 100 lines) should be posted as attachments.
Maximum file size: 32 MB