Issue #635: Issue with DISTINCT Select

Type: Bug ReoprtPriority: NormalStatus: ClosedReplies: 12

It is getting more and more frustrating :( We've introduced distinct select instead of regular selection in our code (no changes else) and suddenly receive invalid identifiers in queries exceptions in various queries. Here's the stacktrace:

Caused by: com.objectdb.o.UserException: Invalid order expression '$3' for distinct results
at com.objectdb.o.MSG.d(
at com.objectdb.o.QNF.l(
at com.objectdb.o.QNF.n(
at com.objectdb.o.QNF.k(
at com.objectdb.o.QNF.z(
at com.objectdb.o.QNF.k(
at com.objectdb.o.QNF.B(
at com.objectdb.o.QNF.k(
at com.objectdb.o.QNF.j(
at com.objectdb.o.QRC.E(
at com.objectdb.o.QRC.v(
at com.objectdb.o.QRC.u(
at com.objectdb.o.QRM.U5(
at com.objectdb.o.MST.U5(
at com.objectdb.o.WRA.U5(
at com.objectdb.o.WSM.U5(
at com.objectdb.o.STC.r(
at com.objectdb.o.SHN.aj(
at com.objectdb.o.SHN.K(

Sorry but I've tried one hour to replicate it in a simple demo without success. Furthermore, doing a simple .toString() of the CriteriaQuery doesn't contain any variable named $3!!! And even worse.. when debugging in eclipse which calls the .toString() of the CriteriaQuery at different points in time, it happens that we get the same identifier (namely, $1) multiple times for different selections in the same query!! so after debugging the query returns useless.. but anyway, I can ignore this however the issue mentioned before is again a full show stopper for us we cannot work around :((

thanks for fast help!!



$n variables are used for both:

  • Explicit variables in criteria queries.
  • Implicit variables that are generated as a result of path navigation (e.g. x.a.b).

Only explicit variables are shown in toString of a criteria query, so probably $3 in this case is an implicit variable.

Invalid order expressions is a common error (not only in ObjectDB and also in SQL), since there are strict rules regarding what is allowed in the ORDER BY expression. You didn't provide a query so it is impossible to say if the problem in this specific case is in the query or in ObjectDB.

It is clear, however, that $3 should be replaced in the error message with the original path that it represents, to make the error message more meaningful.

ObjectDB Support


Well, the query works perfect without using distinct select so I doubt its an issue in our query. Here it goes:

SELECT DISTINCT $1 FROM $1 LEFT JOIN $1.profile $2 WHERE ((TYPE($1)=class ORDER BY UPPER($2.lastName)

And attached you can find the according database for it. Try the query (without the TYPE($1)..() check as this causes more issues in explorer because the generated code from criteriaquery is NOT valid JPQL) -> you get the same issue.


thanks for any help,



Funny enough, this works:

SELECT DISTINCT $1 FROM $1 LEFT JOIN $1.profile $2 ORDER BY UPPER($1.profile.lastName)

But doesn't help us because we can't change our code.. and as said, without the distinct it works!??




Note: We're using the left join to select all entities, also those which have profile = null


The use of DISTINCT does the difference. I will try to explain this issue.

The following query is invalid:

FROM IndividualContact $1 LEFT JOIN $1.profile $2
ORDER BY UPPER($2.lastName)

By the way, why are you using fully qualified names? if you remove entity names from your @Entity tags you will be able to use unqualified short class names as in the query above.

Anyway, the query is invalid because you are trying to order the results based on a field of $2 - after eliminating $2 from the results. Notice that if profile is a collection then the same $1 value may represent more than on tuple, e.g. if (1,2a) and (1,2b) are both result tuples - only 1 is returned. How should it be ordered as 2a.lastName or as 2b.lastName?

This is why ORDER BY is usually limited to expressions that are part of the results.
Not just in ObjectDB but in SQL in general.

ObjectDB is a little more flexible and supports the following query with no DISTINCT:

FROM IndividualContact $1 LEFT JOIN $1.profile $2
ORDER BY UPPER($2.lastName)

In this case (1,2a) and (1,2b) are represented in the results by two 1 results, one is associated with 2a and the other with 2b (even though they are not returned to the user) and each is ordered accordingly.

The following query is also supported:

FROM IndividualContact $1 LEFT JOIN $1.profile $2
ORDER BY UPPER($1.profile.lastName)

because the order here is derived from $1, not from $2 that is removed from the results.

ObjectDB Support


Now you've lost me.. why is $2 removed from the result? I mean, it is not a collection though, it is just an embedded object which should regularily returned with $1 no? If not, what is the desired, JPA-Conform way of NOT having a result with doubled $1 and stilll use something like our code?




This should be valid:

FROM IndividualContact $1 LEFT JOIN $1.profile $2
ORDER BY UPPER($2.lastName)

as well as this:

SELECT DISTINCT $1, $2.lastName
FROM IndividualContact $1 LEFT JOIN $1.profile $2
ORDER BY UPPER($2.lastName)

except that UPPER in ORDER BY is an extension to JPA 2.

ObjectDB Support


Great that you are still online as I continued working on this issue :)

Well I was playing around with SELECT DISTINCT $1, $2.. however, it returns either Object[] or Tuple element result lists and I need only a clean Collection<$1> being returned as otherwise I will have yet another overhead manually iterating over the resultset and extracting the first part of each tuple element and putting it into my own list.. Isn't there any other, more effective way? this is really confusing, I so much often wish to have a better interface than JPQL :(




Why not:

FROM IndividualContact $1 LEFT JOIN $1.profile $2
ORDER BY UPPER($1.profile.lastName)

(which I think is not valid in JPQL but works with ObjectDB).

You can also use:

SELECT DISTINCT NEW IndividualContact($1, $2.lastName)
FROM IndividualContact $1 LEFT JOIN $1.profile $2
ORDER BY UPPER($2.lastName)

If IndividualContact has a constructor that gets IndividualContact and a String.

ObjectDB Support

Sorry but still not working it looks like I am too dumb!?

Using this query on attached DB:

SELECT DISTINCT $1, UPPER($2.lastName) AS TEST FROM com.lexware.vereinsverwaltung.verein.mitglied.Mitglied $1 LEFT JOIN $1.mitgliedschaft $3 LEFT JOIN $1.profile $2 ORDER BY TEST

results in the "TEST" column containing always "Mitglied 99" string eventhough if you look into the first column->profile->lastName it is definitely different!? What am I doing wrong??



works using

SELECT DISTINCT $1, UPPER($1.profile.lastName) AS TEST FROM com.lexware.vereinsverwaltung.verein.mitglied.Mitglied $1 ORDER BY TEST

but that doesn't help me as I need the LEFT JOIN on profile because profile might be null yet I want to select everything...




Oh and I need to use the LEFT JOIN as well because this code might apply to collections and maps as well which is supposed to work fine.. (i.e. sorting by the property of an embedded entity within a map etc.)

Hrm not sure whether this is a bug or would be better posted into the forum?