436 words

Handling "is null" in where clause

#1
2010-09-24 10:23

Greetings,

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

@Entity
public class EntityA {
    @Id
    Integer id;
    Integer simpleField;
 
    // (getters and setters here)
 
    // - populating database
 
    for (int x = 1; x < 100; x++) {
        EntityA a = new EntityA();
        a.setId(x);
 
        if (Math.random() < 0.5) {
            a.setSimpleField(new Double(Math.random() * 1000).intValue());
        }
        em.persist(a);
    }
 
    // - 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
lwalkowski's picture
Joined on 2010-08-25
User Post #2
#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
support's picture
Joined on 2010-05-03
User Post #51
#3
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
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.
Attachments:
Maximum file size: 32 MB
Cancel