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