ObjectDB ObjectDB

"is not null" queries not working with index

#1

"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();

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

        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());

        em.close();
        emf.close();
    }
    
    @Entity
    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;
        }
    } 
}

Output:

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).

edit
delete
#2

Workaround for number fields:

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

edit
delete
#3

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"

edit
delete
#4

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

ObjectDB Support
edit
delete

Reply

To post on this website please sign in.