433 words

Strange behaviour with ORDER BY and IN

#1
2016-01-10 08:00

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

bklink
bklink's picture
Joined on 2016-01-10
User Post #1
#2
2016-01-10 16:48

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

ObjectDB Support
ObjectDB - Fast Object Database for Java (JPA/JDO)
support
support's picture
Joined on 2010-05-03
User Post #2,375
#3
2016-01-10 18:43

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

bklink
bklink's picture
Joined on 2016-01-10
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