• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Ambiguity in ORDER BY and GROUP BY of JPQL

 
Kaxhif Khan
Ranch Hand
Posts: 50
1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi, i am preparing for JPA exam where i found a few ambiguities ..

1) In Book (Pro JPA ) page 233 it is quoted as "If the SELECT clause of the query uses state field path expressions, the ORDER BY clause is limited
to the same path expressions used in the SELECT clause. For example, the following query is not legal:"



so technically it should give error but when i tried it practically i found the query working without error, here is the query i tried


[e.name = String, e.address.postalCode = int where address is an "embeddable class" inside Employee object].

The same problem i found with GROUP BY clause as well. I may be doing something wrong because specification and book has said the same thing.
Kindly help me clear this ambiguity.

Thanks in advance
 
K. Tsang
Bartender
Posts: 3583
16
Android Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
SELECT e.name FROM Employee e ORDER BY e.salary DESC

From a sql standpoint, your example sql or jpl rather is invalid. Because the salary is not in the select clause.

According to this page:

Only expressions that are derived directly from expressions in the SELECT clause are allowed in the ORDER BY clause


Yet you got no errors is weird. Did you actually get results? Did you check the server log?
 
Tim Holloway
Saloon Keeper
Posts: 18359
56
Android Eclipse IDE Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
You do not need to ORDER BY a selected column. You can not only order by a column not returned by the SELECT, you can order by complex relations.

GROUP BY, however, only works on columns selected for ORDER BY. Otherwise the results would likely contain random "groups" of single records.
 
Kaxhif Khan
Ranch Hand
Posts: 50
1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
K. Tsang wrote:

Only expressions that are derived directly from expressions in the SELECT clause are allowed in the ORDER BY clause


Yet you got no errors is weird. Did you actually get results? Did you check the server log?


Results are correct ordered by postalCode in address(embeddable) object. I was expecting that query will break with "IllegalArgumentException" however i cross validated the server log which is clean).

I ran the following handmade query on My SQL client



and it also ran successfully producing output ordered by postalCode.
 
Kaxhif Khan
Ranch Hand
Posts: 50
1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Tim Holloway wrote:You do not need to ORDER BY a selected column. You can not only order by a column not returned by the SELECT, you can order by complex relations.

GROUP BY, however, only works on columns selected for ORDER BY. Otherwise the results would likely contain random "groups" of single records.


Only having is working as expected so far for me. I tried the following hand made SQL with My SQL client and it ran successfully.

 
Tim Holloway
Saloon Keeper
Posts: 18359
56
Android Eclipse IDE Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
IllegalArgumentException is thrown on method calls, not on invalid JPQL (or SQL).

There's a different Exception that will be thrown by JPA when the query is invalid, but I don't remember what it is at the moment.
 
Frits Walraven
Creator of Enthuware JWS+ V6
Saloon Keeper
Pie
Posts: 2531
112
Android Chrome Eclipse IDE
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Kaxhif Khan wrote: 1) In Book (Pro JPA ) page 233 it is quoted as "If the SELECT clause of the query uses state field path expressions, the ORDER BY clause is limited
to the same path expressions used in the SELECT clause. For example, the following query is not legal:"


That is indeed wrong according to the book but also wrong according to the JPA 2.0 specs. The specs give a couple of such examples:
4.9 ORDER BY Clause
The following two queries are not legal because the orderby_item is not reflected in the SELECT clause of the query.


Kaxhif Khan wrote:so technically it should give error but when i tried it practically i found the query working without error,

Be careful here, none of the JPA implementations I have come across so far have been strict on the JPA specifications. The fact that it works in this JPA implementation is perfectly possible but definitely not portable.
 
Tim Holloway
Saloon Keeper
Posts: 18359
56
Android Eclipse IDE Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Can you refer us to the relevant section of the JPA specs?

Certainly queries like: "SELECT c.first_name, c.last_name, c.street_address, c.last_name, c.city, c.state FROM customer order by c.last_name, c.zip_code" are not only legal, but very common and I don't require JPQL deviating from that capability nor can I think of any reason why the JPQL version should be less powerful than the raw SQL that it should be generating.

Now if the column existed in the database, but it wasn't actually defined in the Entity, that's quite another matter.
 
Frits Walraven
Creator of Enthuware JWS+ V6
Saloon Keeper
Pie
Posts: 2531
112
Android Chrome Eclipse IDE
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Tim Holloway wrote:Can you refer us to the relevant section of the JPA specs?

That is section 4.9 ORDER BY Clause of the JPA 2.0 specs (final release p166).
 
Tim Holloway
Saloon Keeper
Posts: 18359
56
Android Eclipse IDE Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I think you are misreading it. I've got the JSR-317 Final Draft in front of me, and right below the 3 qualifying criteria, there are several examples where the ORDER BY doesn't reference one of the selected items.

For Example:


You are thinking that the column that you are ordering by must be one of the selected properties. But what it actually says is "orderable state field of an entity..."

Meaning that any property of the Entity in question can be used as ORDER BY criteria as long as that property itself can be ordered. Regardless of whether it's returned to the app by the SELECT.

Incidentally, item #3 describes the situation where you can order by something resulting from a JOIN or similar operation.
 
Tim Holloway
Saloon Keeper
Posts: 18359
56
Android Eclipse IDE Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
OK wait, I mis-read the selection set on that one. Never mind.

I could see limiting ORDER BY to selection fields IF the ordering was something that you'd have to be able to do on the returned data instead of internal to the database, since the database server knows about all the fields, but any ordering done client-side would know only about fields transferred to the client.

In the raw SQL example I gave, ordering would obviously be done on the database server, so ordering by a non-selected field is possible. In JPQL, the standard would seem to allow for client-side ordering. Typically I wouldn't notice this, because one of the major things about JPA as opposed to raw SQL is that JPA is focused on retrieving entire entities, not selected columns within the entity. And given that, the order column(s) would naturally be part of the selection set.

Although I suspect that vendors may have been relaxing the standard so that server-side ordering could be used.
 
Kaxhif Khan
Ranch Hand
Posts: 50
1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Frits Walraven wrote:
Be careful here, none of the JPA implementations I have come across so far have been strict on the JPA specifications. The fact that it works in this JPA implementation is perfectly possible but definitely not portable.


So what should we follow if we had any question on it in exam i) the specs/book ? or ii) practical findings ?
 
Winston Gutkowski
Bartender
Pie
Posts: 10571
64
Eclipse IDE Hibernate Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Kaxhif Khan wrote:So what should we follow if we had any question on it in exam i) the specs/book ? or ii) practical findings ?

Simple answer: Dunno.

Tim's answer (from his first post) "feels" right to me - at least as far as SQL is concerned - since the field that qualifies the ORDER BY clause is part of a table being selected.

GROUP BY, on the other hand, relies on detecting "breaks" in a selected item, so it makes sense to me that it must be one of the fields (or columns) actually being selected.

But how JPQL differs from SQL I don't know. My assumption is that they would be as congruent as possible; but since different versions of SQL can't agree how to do lots of things, "congruence" is a relative term.

My advice (if you can): State your reasons for selecting the answer you did if there's any ambiguity, because if results are arbitrated by a human being - as opposed to a computer - you may get "partial" points even if your answer was wrong.

HIH

Winston
 
Frits Walraven
Creator of Enthuware JWS+ V6
Saloon Keeper
Pie
Posts: 2531
112
Android Chrome Eclipse IDE
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Kaxhif Khan wrote:So what should we follow if we had any question on it in exam i) the specs/book ? or ii) practical findings ?

The answer is simple: on java EE-exams it is all about the specifications!
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic