• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

iBatis groupBy spoils SQL order by

 
Ranch Hand
Posts: 182
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 182
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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 ?
 
Marshal
Posts: 28193
95
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 182
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator


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:


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
Marshal
Posts: 28193
95
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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
Ranch Hand
Posts: 182
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Marshal
Posts: 28193
95
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 182
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator


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
Marshal
Posts: 28193
95
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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 ]
 
Ranch Hand
Posts: 354
Eclipse IDE Oracle Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I guess whats happening here is your grouping is applied after ordering, while what you seek is the reverse.
 
Ranch Hand
Posts: 245
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator


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
Ranch Hand
Posts: 354
Eclipse IDE Oracle Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
If you don't have any other way, can't you specify both order by and group by in your query.
 
aditee sharma
Ranch Hand
Posts: 182
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
Greenhorn
Posts: 3
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Greenhorn
Posts: 3
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Greenhorn
Posts: 3
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
The government thinks you are too stupid to make your own lightbulb choices. But this tiny ad thinks you are smart:
a bit of art, as a gift, the permaculture playing cards
https://gardener-gift.com
reply
    Bookmark Topic Watch Topic
  • New Topic