Problem with distinct select, order by and equivalent alias/attribute path

Hi,

while working with a (standard-)JPA-query generating framework we run into an exception when ordering the results of some projection when the order by expression is different (though logically equivalent) to the projection expression.

Here is the example:

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

@Entity
public class Street implements Serializable {

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Long id;
   
    private String name;
   
    @ManyToOne
    private Location location;

    public Street() {
    }

    public Street(Long id, String name, Location location) {
        this.id = id;
        this.name = name;
        this.location = location;
    }

    public Long getId() {
        return id;
    }

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

    public Location getLocation() {
        return location;
    }

    public void setLocation(Location location) {
        this.location = location;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }
   
}

package de.solvit.objectdbtest;

import de.solvit.objectdbtest.entity.Location;
import de.solvit.objectdbtest.entity.Street;
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;

/**
*
* @author Benjamin Klink, SOLVIT GmbH
*/
public class Main {

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

        em.getTransaction().begin();
       
        em.getMetamodel().entity(Street.class);
        em.getMetamodel().entity(Location.class);
        //clean up...
        em.createQuery("delete from Street s").executeUpdate();
        em.createQuery("delete from Location l").executeUpdate();
       
        locations.forEach(l -> em.persist(l));
        streets.forEach(s -> em.persist(s));
       
        em.getTransaction().commit();
       
        
        String q1 = "select distinct s_location "
            + "from Street s "
            + "left join s.location as s_location "
            + "order by s_location.id asc";
        System.out.println("Working query: ");
        System.out.println(q1);
       
        em.createQuery(q1, Location.class).getResultList();
       
        q1 = "select distinct s.location "
            + "from Street s "
            + "left join s.location as s_location "
            + "order by s.location.id asc";
        System.out.println("Also working: ");
        System.out.println(q1);
       
        em.createQuery(q1, Location.class).getResultList();
       
        String q2 = "select distinct s.location "
            + "from Street s "
            + "left join s.location as s_location "
            + "order by s_location.id asc";
        System.out.println("\nNOT working: ");
        System.out.println(q2);
       
        em.createQuery(q2, Location.class).getResultList();
       
        em.close();
        emf.close();
    }
   
}

Exception thrown:

Invalid order expression 's_location' for distinct results (error 745)
(position 85) at com.objectdb.jpa.JpaQuery.getResultList(JpaQuery.java:728)
at de.solvit.objectdbtest.Main.main(Main.java:105)
Caused by: com.objectdb.o.UserException: Invalid order expression 's_location' for distinct results
at com.objectdb.o.MSG.d(MSG.java:62)
at com.objectdb.o.QNF.l(QNF.java:339)
at com.objectdb.o.QNF.n(QNF.java:399)
at com.objectdb.o.QNF.k(QNF.java:170)
at com.objectdb.o.QNF.z(QNF.java:788)
at com.objectdb.o.QNF.k(QNF.java:258)
at com.objectdb.o.QNF.j(QNF.java:135)
at com.objectdb.o.QRC.H(QRC.java:582)
at com.objectdb.o.QRC.y(QRC.java:239)
at com.objectdb.o.QRC.x(QRC.java:188)
at com.objectdb.o.QRM.Vm(QRM.java:272)
at com.objectdb.o.MST.Vm(MST.java:988)
at com.objectdb.o.WRA.Vm(WRA.java:311)
at com.objectdb.o.WSM.Vm(WSM.java:115)
at com.objectdb.o.QRR.g(QRR.java:247)
at com.objectdb.o.QRR.f(QRR.java:153)
at com.objectdb.jpa.JpaQuery.getResultList(JpaQuery.java:719)
... 1 more

While I wouldn't write the query with different expressions as shown above manually (and actually have no idea why the framework is doing so...) - shouldn't the expressions (alias and access path) theoretically be equivalent and therefore also the last query should work?

The error does mean some pain in the neck since it's quite uncomfortable to write these queries by hand in our application which makes intensive use of the dsl library generating the problematic query. Influencing the generation is as well very difficult since we even can't modify the query while passing the "generation process" before throwing this exception.

Do you have an idea for any workaround or could this be fixed? Is the last (problematic) query invalid from your point of view?

Thanks and regards

Benjamin Klink

JPQL restricts ORDER BY expressions to expressions in the SELECT clause.

Section 4.9 in the JPA 2.1 specification demonstrates it:

The following two queries are not legal because the orderby_item is not reflected in the SELECT
clause of the query.

SELECT p.product_name
FROM Order o JOIN o.lineItems l JOIN l.product p JOIN o.customer c
WHERE c.lastname = ‘Smith’ AND c.firstname = ‘John’
ORDER BY p.price

ObjectDB is less restrictive, and will except the query above. However, in DISTINCT and GROUP BY queries it must follow these restrictions, otherwise the same result may have multiple different order values.

Your third query, although logically valid, is synthatically invalid:

SELECT DISTINCT s.location
FROM Street s LEFT JOIN s.location as s_location
ORDER BY s_location.id ASC

As you wrote correctly replacing the projection form s.location to s_location or the order from s_location.id to s.location.id makes the query valid in ObjectDB.

Since you have no control on the library that creates these queries, you may consider using a new option of build 2.6.5_03 for disabling the ORDER BY restriction checks:

    System.setProperty(
        "objectdb.temp.no-distinct-order-restrictions", "true");

Set the property before using ObjectDB (possibly also as a JVM -D argument).

When these checks are disabled, it will be your responsibility to avoid ORDER BY expressions that are also logically wrong.

Confirmed this fix/workaround in the test scenario, still trying to establish it in the "real" project. There I get the same error still.

It is a Java EE 7 project running in wildfly 9 using JPA and JTA (EJB 3.1 etc.). Where would you recommend to set this property? I tried to set it in persistence.xml, directly in the code (before loading an EntityManager) and as a run argument for the wildfly server instance. Nothing really worked so far but I'll try it again the next days and hope to get rid of the problem in this project...

Again, thanks for the quick support here!

An update on this topic:

1. It only seems to work in embedded mode?! After trying a lot of things I finally tested it out in the example project but pointing from there to the (client-server-based) database that is used by the "real" project. In client-server-mode I still get the exception about the invalid expression, even when executing the same code with the same Entity classes as in my post before, in embedded mode I do not.

2. Even if the exception is NOT thrown (as happening in embedded mode) the order by clause seems to be ignored, results are not ordered as they should be. The ordering works, on the other hand, if I use the same expression in projection as in order by-clause, as the "correct" solution would suggest.

Could you check these issues again?

The first point is probably simple. The system property is checked on the server side, so in client server mode it has to be specified on the server, i.e. as a JVM -D argument.

The second point is more difficult. Apparently just removing the error is not enough and some analysis is needed in order to identify the order expression as equivalent to the projection. Unfortunately this is more complex.

Which library generates these queries? It would probably be better to repair that library to generate valid JPQL queries.

1. Even if maybe obsolete because of point 2. I'm pretty sure I did configure this property both on server as well as on client side even at different stages/positions. However, the bigger problem is...

2. It's QueryDSL that we use in our projects especially for query abstraction and type-safety/refactoring-safety of the persistence layer. As I already stated, I don't really have an idea why it forms the query that strange when using projections (that actually differ from the queried root entity). I will post a thread there to see if someone can fix it. For so long I will probably work around the issue by writing these queries by hand - although this is not really nice architecture then. 

Regards

B. Klink

Note that the server is the ObjectDB Server (not the application server, which is actually a client of ObjectDB).

Is it that QueryDSL: https://github.com/querydsl?

It seems similar to the JPA Criteria Queries, where Order is specified by the library user (the developer). Maybe you can change your query building to produce a different query?

Yes, it's that project. Unfortunately it seems to make no difference in the resulting query if I change the construction of the QueryDSL-expressions and the order by clause is always defined in the same way, allowing no different way to construct it.

However, maybe they'll fix this kind of query building in future releases since it still doesn't make any real sense to me switching from alias to attribute path between projection and order by part...