iBatis groupBy spoils SQL order by

[aditee sharma]

Hi,
for some requirement and for better performance, I am fetching parent and child records in the same query.
This query has an order by on one key and the when checked through toad, the results come in perfect order.
However, to filter out redundant rows, when I use groupby on another set of keys, the order is disturbed, even though the results are otherwise as per expectations.
Any idea ?

[aditee sharma]

Let me re-phrase the question a little bit:
For some requirement and for better performance, I am fetching parent and child records in the same SQL query.
This query has an order by on one key and the when checked through toad, the results come in perfect order.
However, to filter out redundant rows, when I use groupby on another set of keys in iBATIS resultMap, the order is disturbed, even though the results are otherwise as per expectations.
Any idea ?

[Paul Clapham]

Are you saying that the records are not returned in the order which the ORDER BY clause specifies? I doubt that you are.

If you're not saying that, then you don't have a valid complaint. The database can return the records in any order it likes as long as it satisfies the order you requested in your ORDER BY clause.

[aditee sharma]

Are you saying that the records are not returned in the order which the ORDER BY clause specifies? I doubt that you are.

Ok, let me try to explain again:
The database returns the results perfectly in order.
When these results are fed to the iBatis framework, and iBatis resultMap groupBy is applied, then the order is disturbed in the results.And by disturbed I mean a random order.
below is the resultMap definition to help you understand what iBatis has been asked to do:

<resultMap id="itemShipment" class="com.xxx.ordertrack.pojo.ItemShipment" groupBy="invoiceNo,shpDt,trackNo,trackLnk,shpMethod,carrier">
.....
......
</resultMap>

thanks,
Aditee
P.S: The groupBy is applied on keys none of which is primary...can that be a reason for iBatis to behave in this way?

[Paul Clapham]

Originally posted by aditee sharma:
[QB]The database returns the results perfectly in order.

This is the part I don't understand. You seem to be inferring some "order" in the database which isn't specified in your ORDER BY clauses. The database isn't required to order the rows in any particular way except what the ORDER BY clause specifies. In particular it isn't required to order rows the same way in two different queries -- unless, of course, the ORDER BY clauses of those queries say to do that.

[aditee sharma]

nice to find you again!

You seem to be inferring some "order" in the database which isn't specified in your ORDER BY clauses.

Hardly.In my very 1st post I've stated that the SQL query is using order by clause:
My own quote:

This query has an order by on one key and the when checked through toad, the results come in perfect order.

So, I don't understand where the confusion is coming from.
Again, the SQL query gives accurate results. It is iBATIS or my own handling afterwards that is messing up the order.I need your help to pinpoint what is it that I am doing wrong or if there is a bug in iBATIS.

[Paul Clapham]

But you're asking about the second query. I don't see where you specify the ordering for it.

"Group by" does not specify the ordering for a query, if that's what you thought. You still have to specify "order by" or the database can return the groups in any order.

[aditee sharma]

But you're asking about the second query.

There is no second query. The iBATIS resultMap groupBy does not fire a 2nd query...or does it?
To my knowledge it just provides uniqueness to the results fetched from SQL.
So,going by the given code, if we has 2 records that have same invoiceNo,shpDt,trackNo,trackLnk,shpMethod,carrier combination, iBatis groupBy will give out only one.
Please let me know if you know any better than what I've said.

[Paul Clapham]

Originally posted by aditee sharma:
There is no second query. The iBATIS resultMap groupBy does not fire a 2nd query...or does it?

I don't know. It's possible that iBatis is running your query, loading a ton of records into memory, and then grouping them. But the much more sensible thing for it to do would be to generate a second query, with a GROUP BY clause, and ask the database to execute that. I do know that iBatis was written by people who know what they are doing. I would expect them to fire a second query at the database rather than trying to replicate what the database would do.

Later thought: And even if iBatis were simulating the database's work, it still wouldn't be required to use the same ORDER BY criteria as the underlying query. Because the database isn't required to do that anyway.
[ September 29, 2008: Message edited by: Paul Clapham ]

[Abhinav Srivastava]

I guess whats happening here is your grouping is applied after ordering, while what you seek is the reverse.

[tapeshwar sharma]

I guess whats happening here is your grouping is applied after ordering, while what you seek is the reverse.

Absolutely right, but then how do we specify order by in iBATIS?

[Abhinav Srivastava]

If you don't have any other way, can't you specify both order by and group by in your query.

[aditee sharma]

I could, but it will be like another select-groupBy on top of the current select query's results, and therefore possibly introducing a performance bottleneck.
I thought iBATIS does the groupBy in Java and is therefore faster.
So, I am looking for the iBATIS solution for orderBy.

[Corrado Alesso]

I'm facing the very same problem.

I have a query (on Postgres) with an order by clause, and the query results are OK when browsed with the console.

But when the query is executed by iBats, and the result map applies the groupBy clause, the ordering is not the same.

Please, post here the solution for this problem if you find it. I'll do the same.

Thank you
[ December 14, 2008: Message edited by: Corrado Alesso ]

[Corrado Alesso]

Are you using Postgres?

I had the query running on a Derby embedded database, and the problem couldn't be reproduced. Maybe an issue in the postgres jdbc driver?

[Corrado Alesso]

I solved my problem, and it was a comment in the query.

The pgAdmin can digest comment in between the query, and return the correct result, while JDBC will comment out everythin after the comment (in my case the order clause).

Check for comments in your query.