Issue #947: Strange Error with Criteria API and Sorting

We're creating a simple selection (distinct) including an order clause.

When watching the created criteria in debugger we get the correct expression:

SELECT DISTINCT $1 FROM MyEntity $1 WHERE ((TYPE($1)=class test.MyEntity)) ORDER BY UPPER($1.name) DESC

You can see that in the order by there's correctly a "$1" var. Running this (without the type thing before) in the explorer correctly returns the sorted results.

However, running this with the criteria API produces this error:

Caused by: com.objectdb.o.UserException: Invalid order expression '$2' for distinct results
at com.objectdb.o.MSG.d(MSG.java:61) [objectdb-2.4.4_05.jar:]
at com.objectdb.o.QNF.l(QNF.java:333) [objectdb-2.4.4_05.jar:]
at com.objectdb.o.QNF.n(QNF.java:394) [objectdb-2.4.4_05.jar:]
at com.objectdb.o.QNF.k(QNF.java:169) [objectdb-2.4.4_05.jar:]
at com.objectdb.o.QNF.z(QNF.java:783) [objectdb-2.4.4_05.jar:]
at com.objectdb.o.QNF.k(QNF.java:257) [objectdb-2.4.4_05.jar:]
at com.objectdb.o.QNF.B(QNF.java:886) [objectdb-2.4.4_05.jar:]
at com.objectdb.o.QNF.k(QNF.java:277) [objectdb-2.4.4_05.jar:]
at com.objectdb.o.QNF.j(QNF.java:134) [objectdb-2.4.4_05.jar:]
at com.objectdb.o.QRC.E(QRC.java:550) [objectdb-2.4.4_05.jar:]
at com.objectdb.o.QRC.v(QRC.java:213) [objectdb-2.4.4_05.jar:]
at com.objectdb.o.QRC.u(QRC.java:165) [objectdb-2.4.4_05.jar:]
at com.objectdb.o.QRM.U5(QRM.java:248) [objectdb-2.4.4_05.jar:]
at com.objectdb.o.MST.U5(MST.java:959) [objectdb-2.4.4_05.jar:]
at com.objectdb.o.WRA.U5(WRA.java:291) [objectdb-2.4.4_05.jar:]
at com.objectdb.o.WSM.U5(WSM.java:113) [objectdb-2.4.4_05.jar:]
at com.objectdb.o.STC.r(STC.java:449) [objectdb-2.4.4_05.jar:]
at com.objectdb.o.SHN.aj(SHN.java:489) [objectdb-2.4.4_05.jar:]
at com.objectdb.o.SHN.K(SHN.java:156) [objectdb-2.4.4_05.jar:]
at com.objectdb.o.HND.run(HND.java:132) [objectdb-2.4.4_05.jar:]
... 1 more

This doesn't make any sense, there might not be any "$2" variable. More strangely, when debugging through the error doesn't error but if you run without debugging through then it happens.

Please advise / fix, this is more than a show stopper, we cannot even sort right now!!

A test case that demonstrates the problem is needed.

Here's the testcase:

import java.util.List;

import javax.persistence.Entity;
import javax.persistence.EntityManager;
import javax.persistence.EntityManagerFactory;
import javax.persistence.Persistence;
import javax.persistence.criteria.CriteriaBuilder;
import javax.persistence.criteria.CriteriaQuery;
import javax.persistence.criteria.Expression;
import javax.persistence.criteria.Join;
import javax.persistence.criteria.Root;

public class Test_OrderBy
{
public static void main(String[] args)
{
  EntityManagerFactory emf = Persistence.createEntityManagerFactory("objectdb:db/test.tmp;drop");
  EntityManager em = emf.createEntityManager();

  // Add some data
  em.getTransaction().begin();

  OtherEntity o1 = new OtherEntity("Name C");
  OtherEntity o2 = new OtherEntity("Name B");
  OtherEntity o3 = new OtherEntity("Name A");

  em.persist(o1);
  em.persist(o2);
  em.persist(o3);

  em.persist(new TestEntity(o1));
  em.persist(new TestEntity(o2));
  em.persist(new TestEntity(o3));

  em.getTransaction().commit();

  CriteriaBuilder cb = em.getCriteriaBuilder();
  CriteriaQuery<TestEntity> query = cb.createQuery(TestEntity.class);
  Root<TestEntity> root = query.from(TestEntity.class);

  final Join<?, ?> otherJoin = root.join("other");

  query.orderBy(cb.asc(cb.upper((Expression<String>) (Expression<?>) otherJoin.get("name"))));
  query.select(root).distinct(true);
  List<TestEntity> result = em.createQuery(query).getResultList();

  for (TestEntity e : result)
  {
   System.out.println("Name: " + e.getOther().getName());
  }

  emf.close();
}

@Entity
public static class OtherEntity
{
  private String name;

  public OtherEntity()
  {
  }

  public OtherEntity(String name)
  {
   this.name = name;
  }

  public String getName()
  {
   return name;
  }

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

@Entity
public static class TestEntity
{
  private OtherEntity other;

  public TestEntity()
  {
  }

  public TestEntity(OtherEntity other)
  {
   this.other = other;
  }

  public OtherEntity getOther()
  {
   return other;
  }

  public void setOther(OtherEntity other)
  {
   this.other = other;
  }
}
}

And here's the output:

Exception in thread "main" [ObjectDB 2.4.4_08] SELECT DISTINCT $1 FROM TestEntity $1 JOIN $1.other $2 ORDER BY UPPER( ==> $2 <== .name)
javax.persistence.PersistenceException
Invalid order expression '$2' for distinct results (error 745)
(position 70) at com.objectdb.jpa.JpaQuery.getResultList(JpaQuery.java:695)
at Test_OrderBy.main(Test_OrderBy.java:45)
Caused by: com.objectdb.o.UserException: Invalid order expression '$2' for distinct results
at com.objectdb.o.MSG.d(MSG.java:61)
at com.objectdb.o.QNF.l(QNF.java:333)
at com.objectdb.o.QNF.n(QNF.java:394)
at com.objectdb.o.QNF.k(QNF.java:169)
at com.objectdb.o.QNF.z(QNF.java:783)
at com.objectdb.o.QNF.k(QNF.java:257)
at com.objectdb.o.QNF.B(QNF.java:886)
at com.objectdb.o.QNF.k(QNF.java:277)
at com.objectdb.o.QNF.j(QNF.java:134)
at com.objectdb.o.QRC.E(QRC.java:555)
at com.objectdb.o.QRC.v(QRC.java:214)
at com.objectdb.o.QRC.u(QRC.java:166)
at com.objectdb.o.QRM.U5(QRM.java:248)
at com.objectdb.o.MST.U5(MST.java:959)
at com.objectdb.o.WRA.U5(WRA.java:291)
at com.objectdb.o.WSM.U5(WSM.java:113)
at com.objectdb.o.QRR.g(QRR.java:242)
at com.objectdb.o.QRR.b(QRR.java:153)
at com.objectdb.jpa.JpaQuery.getResultList(JpaQuery.java:686)
... 1 more

Looks like the issues occurs when using a join...

Apparently this error is justified.

As explained in the manual: "Only expressions that are derived directly from expressions in the SELECT clause are allowed in the ORDER BY clause". This is not just an ObjectDB limitation but a known JPQL and also SQL limitation.

Your query includes only $1 in the SELECT expression.
Therefore the ORDER BY cannot include $2, which is another variable.

You can fix this specific query by using Path (which is not a separate variable) instead of Join:

//final Join<?, ?> otherJoin = root.join("other");
final Path<Object> otherJoin = root.get("other");