Home » Support » Forum » Strange behaviour with ORDER BY and IN

Strange behaviour with ORDER BY and IN

#1

Hi,

after trying out objectdb for a while now and being quite impressed by its speed and overall performance I noticed a strange behaviour where an ORDER BY - directive isn't executed as it should be (and usually is, except in this special combination-case).

Ordering seems to be ignored if we order by a simple member (e.g. a String) being the Entity-ID and, at the same time, filter on this member with a simple IN-clause. Filtering with IN-clause or ordering on another member (which is not the Entity-ID) actually work well, this behaviour is only observable when filtering and ordering both are done on the ID member.

 

Assuming this simple Entity class:

@Entity
public class Location implements Serializable {

    @Id
    private String id;
   
    private String town;

    public Location() {
    }

    public Location(String id, String town) {
        this.id = id;
        this.town = town;
    }
   
    public String getId() {
        return id;
    }

    public void setId(String id) {
        this.id = id;
    }

    public String getTown() {
        return town;
    }

    public void setTown(String town) {
        this.town = town;
    }

    @Override
    public String toString() {
        return getId() + " " + getTown();
    }
   
}

This main class demonstrates the described behaviour:

package de.solvit.objectdbtest;

import de.solvit.objectdbtest.entity.Location;
import java.util.Arrays;
import java.util.List;
import java.util.stream.Collectors;
import javax.persistence.EntityManager;
import javax.persistence.EntityManagerFactory;
import javax.persistence.Persistence;

public class Main {

    private static final List<Location> locations = Arrays.asList(
        new Location("1", "A"),
        new Location("2", "B"),
        new Location("3", "C")
    );
   
    public static void main(String[] args) {
        EntityManagerFactory emf =
            Persistence.createEntityManagerFactory("$objectdb/db/points.odb");
        EntityManager em = emf.createEntityManager();

        em.getTransaction().begin();
       
        em.getMetamodel().entity(Location.class);
        //clean up...
        em.createQuery("delete from Location l").executeUpdate();
       
        locations.forEach(l -> em.persist(l));
        em.getTransaction().commit();
       
        System.out.println("Locations without selection criterion in descending order (by id): ");
        em.createQuery("select l from Location l order by l.id desc", Location.class)
            .getResultList().forEach(l -> System.out.println("Location: " + l));
       
        System.out.println("\nLocations with IN selection criterion ON ID-FIELD in descending order (by id): ");
        em.createQuery("select l from Location l where l.id IN :ids order by l.id desc", Location.class)
            .setParameter("ids", locations.stream().map(l -> l.getId()).collect(Collectors.toList()))
            .getResultList().forEach(l -> System.out.println("Location: " + l));
       
        System.out.println("\nLocations with IN selection criterion ON NON-ID-FIELD in descending order (by town): ");
        em.createQuery("select l from Location l where l.town IN :towns order by l.town desc", Location.class)
            .setParameter("towns", locations.stream().map(l -> l.getTown()).collect(Collectors.toList()))
            .getResultList().forEach(l -> System.out.println("Location: " + l));
       
        System.out.println("\nLocations with IN selection criterion ON ID-FIELD in descending order (by town): ");
        em.createQuery("select l from Location l where l.town IN :towns order by l.town desc", Location.class)
            .setParameter("towns", locations.stream().map(l -> l.getTown()).collect(Collectors.toList()))
            .getResultList().forEach(l -> System.out.println("Location: " + l));
              
        em.close();
        emf.close();
    }
   
}

Do you have an explanation for this behaviour or could it be a simple bug?

 

Thanks and regards

Benjamin Klink

#2

Thank you for this report. Build 2.6.5_03 fixes the bug.

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

Downloaded latest release and can confirm this fixes it, many thanks for this rapid support!


Post Reply

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