Skip to main content
[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index] [List Home]
Re: [eclipselink-users] DISTINCT with ORDER BY issue 
Hi Derek,
The behavior you are seeing is actually some behavior that EclipseLink takes beyond the JPA specification.
The spec defines what can be used in an Order by in section 4.9. For a query that selects individual fields, only those fields that are selected can be used in the Order by clause. (A query that selects a whole Entity can use any of the fields from that Entity)
I believe the reason for this restriction is that some databases do not allow SQL that orders by a non-selected field (but I am not certain).
EclipseLink is allowing you to Order by something that is not selected, but in order to do that, it is transparently adding the item that is in the selected list. 

-Tom

Derek Knapp wrote:

any ideas on this one??
is this the intended behavior of the ORDER BY? or should I be submitting a bug report? 


Derek Knapp


Derek Knapp wrote:


public List<Contacts> recentTouches(Subuser subuser)

{
Query query = em.createQuery("SELECT DISTINCT h.contact FROM History h WHERE h.subuser = :subuser ORDER BY h.id DESC"); 

    query.setParameter("subuser", subuser);

    query.setMaxResults(10);

    return query.getResultList();

}


*Generates the following sql*
SELECT DISTINCT t0.contactid, t0.fullname, t0.firstname, t0.lastname, t0.salutation, t0.title, t0.email, t0.mailingaddressline1, t0.mailingaddressline2, t0.mailingaddressline3, t0.city, t0.province, t0.postalcode, t0.country, t0.company, t0.businessphone, t0.businessfax, t0.mobilephone, t0.homephone, t0.businessphoneext, t0.smsphone, t0.leadsource, t0.birthdate, t0.emailoptout, t0.create_date, t0.last_modified, *t1.id* FROM history t1 LEFT OUTER JOIN contacts t0 ON (t0.contactid = t1.contactid) WHERE (t1.subuserid = ?) ORDER BY t1.id DESC
*For whatever reason, it has added in t1.id in to the list of items selected…. So the number of results from the above function varies depending if there are any duplicate contacts in the top 10 results returned from the query… why is it adding t1.id to the select list? I don’t care about its value….* 

* *

* *

*Derek Knapp*
__________ Information from ESET NOD32 Antivirus, version of virus signature database 4378 (20090828) __________ 

The message was checked by ESET NOD32 Antivirus.

http://www.eset.com
------------------------------------------------------------------------

_______________________________________________
eclipselink-users mailing list
eclipselink-users@xxxxxxxxxxx
https://dev.eclipse.org/mailman/listinfo/eclipselink-users


------------------------------------------------------------------------

_______________________________________________
eclipselink-users mailing list
eclipselink-users@xxxxxxxxxxx
https://dev.eclipse.org/mailman/listinfo/eclipselink-users

Back to the top