Issue #635: Issue with DISTINCT Select

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(MSG.java:61)
at com.objectdb.o.QNF.l(QNF.java:333)
at com.objectdb.o.QNF.n(QNF.java:389)
at com.objectdb.o.QNF.k(QNF.java:169)
at com.objectdb.o.QNF.z(QNF.java:774)
at com.objectdb.o.QNF.k(QNF.java:257)
at com.objectdb.o.QNF.B(QNF.java:878)
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:540)
at com.objectdb.o.QRC.v(QRC.java:211)
at com.objectdb.o.QRC.u(QRC.java:166)
at com.objectdb.o.QRM.U5(QRM.java:245)
at com.objectdb.o.MST.U5(MST.java:947)
at com.objectdb.o.WRA.U5(WRA.java:290)
at com.objectdb.o.WSM.U5(WSM.java:113)
at com.objectdb.o.STC.r(STC.java:447)
at com.objectdb.o.SHN.aj(SHN.java:489)
at com.objectdb.o.SHN.K(SHN.java:156)
at com.objectdb.o.HND.run(HND.java:133)
at java.lang.Thread.run(Thread.java:680)

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!!

Alex

$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.

hi,

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 com.quasado.foundation.contact.individual.IndividualContact $1 LEFT JOIN $1.profile $2 WHERE ((TYPE($1)=class com.quasado.foundation.contact.api.model.individual.IndividualContact)) 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,

Alex

Funny enough, this works:

SELECT DISTINCT $1 FROM com.quasado.foundation.contact.individual.IndividualContact $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!??

Alex

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:

SELECT DISTINCT $1
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:

SELECT $1
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:

SELECT DISTINCT $1
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.

Hi,

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?

thanks,

Alex

This should be valid:

SELECT DISTINCT $1, $2
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.

hi!

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 :(

Thanks,

Alex

Why not:

SELECT DISTINCT $1
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.

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??

Alex

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...

Alex

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?