-->
These old forums are deprecated now and set to read-only. We are waiting for you on our new forums!
More modern, Discourse-based and with GitHub/Google/Twitter authentication built-in.

All times are UTC - 5 hours [ DST ]



Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 1 post ] 
Author Message
 Post subject: why does hibernate hql distinct cause an sql distinct on lef
PostPosted: Thu Mar 31, 2011 7:05 am 
Beginner
Beginner

Joined: Fri May 12, 2006 9:58 am
Posts: 32
Hello

Note that this is a cross post from http://stackoverflow.com/q/5471819/72478 but I cannot find the solution.

I've got this test HQL:
Code:
select distinct o from Order o left join fetch o.lineItems


and it *does* generate an SQL distinct without an obvious reason:
Code:
select distinct order0_.id as id61_0_, orderline1_.order_id as order1_62_1_...


The SQL resultset is always the same (with and without an SQL distinct):
Code:
order id | order name | orderline id | orderline name
---------+------------+--------------+---------------
       1 | foo        |            1 | foo item
       1 | foo        |            2 | bar item
       1 | foo        |            3 | test item
       2 | empty      |         NULL | NULL
       3 | bar        |            4 | qwerty item
       3 | bar        |            5 | asdfgh item


Why does hibernate generate the SQL distinct? The SQL distinct doesn't make any sense and makes the query slower than needed.
This is contrary to the FAQ http://community.jboss.org/wiki/Hiberna ... ct_keyword which mentions that hql distinct in this case is just a shortcut for the result transformer:
Quote:
session.createQuery("select distinct o from Order o left join fetch o.lineItems").list();

It looks like you are using the SQL DISTINCT keyword here. Of course, this is not SQL, this is HQL. This distinct is just a shortcut for the result transformer, in this case. Yes, in other cases an HQL distinct will translate straight into a SQL DISTINCT. Not in this case: you can not filter out duplicates at the SQL level, the very nature of a product/join forbids this - you want the duplicates or you don't get all the data you need.


thanks


Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 1 post ] 

All times are UTC - 5 hours [ DST ]


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.