"is not null" queries not working with index


"is not null"-queries are not working correctly for indexed fields.

Following SSCCE creates 1000 entities.
A null value is assigned to an indexed field for every second entity.
When doing count-queries at the end, wrong results are fetched.

import java.io.Serializable;
import javax.jdo.annotations.Index;
import javax.persistence.Entity;
import javax.persistence.EntityManager;
import javax.persistence.EntityManagerFactory;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import javax.persistence.Persistence;

public class Main {
    public static void main(String[] args) {
        EntityManagerFactory emf = Persistence.createEntityManagerFactory("test.odb");
        EntityManager em = emf.createEntityManager();

        for (int i = 0; i < 1000; i++) {
            em.persist(new Foo((i%2)==0 ? i : null));

        System.out.println("total   : " + em.createQuery("SELECT COUNT(p) FROM Foo p").getSingleResult());
        System.out.println("null    : " + em.createQuery("SELECT COUNT(p) FROM Foo p WHERE p.bar IS NULL").getSingleResult());
        System.out.println("not null: " + em.createQuery("SELECT COUNT(p) FROM Foo p WHERE p.bar IS NOT NULL").getSingleResult());

    static public class Foo implements Serializable {
        private static final long serialVersionUID = 1L;

        @Id @GeneratedValue private long id;
        @Index private Integer bar;

        public Foo() {}

        public Foo(Integer bar) {
            this.bar = bar;


total   : 1000
null    : 500
not null: 339

Behaviour detected on 64Bit JDK 1.8.0_144 for ObjectDB version 2.7.6_03 and earlier (tested down to 2.7.4).


Workaround for number fields:

SELECT COUNT(p) FROM Foo p WHERE p.bar >= 0 or p.bar <= 0


Woraround doesn't work in combination with 'min':

System.out.println("min not null: " + em.createQuery("SELECT min(p.bar) FROM Foo p WHERE p.bar >= 0 or p.bar <= 0").getSingleResult());

gives output "322" instead of "0"


Thank you very much for this report. Build 2.7.6_04 should fix the bug.

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

Post Reply

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