DISTINCT with ORDER BY issue

Some parts of this message have been removed. Learn more about Nabble's security policy.

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@...
https://dev.eclipse.org/mailman/listinfo/eclipselink-users
  

Some parts of this message have been removed. Learn more about Nabble's security policy.

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@...
https://dev.eclipse.org/mailman/listinfo/eclipselink-users
  

Some parts of this message have been removed. Learn more about Nabble's security policy.

Derek Knapp
Software Engineer
itracMEDIA, Inc.
1560 Queen Street East, Toronto, ON M4L 1E9  |   Tel: 416.364.9444   |   Fax:416.364.9589

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@...
https://dev.eclipse.org/mailman/listinfo/eclipselink-users
 

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

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

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

Some parts of this message have been removed. Learn more about Nabble's security policy.

Derek Knapp
Software Engineer
itracMEDIA, Inc.
1560 Queen Street East, Toronto, ON M4L 1E9  |   Tel: 416.364.9444   |   Fax:416.364.9589

Hi Derek,

  The one word answer is....  Yes, it is intentional.

  MaxResults is being applied to the SQL executed by EclipseLink.  Doing it that way is really the only way maxResults can be designed to optimize your query.  To return 10 entities from the query below, we would have to execute some other queries to determine how many DB rows to select and essentially remove any optimization setMaxResults provides.

-Tom

Derek Knapp wrote:

is how it deals with the *query.setMaxResults(10); *also intentional?

for example, if the top 10 results are the same contact, it will only return 1 result, not the same contact 10 times... if it is smart enough to do this, shouldn't it return the top 10 unique contacts instead of the top x unique contacts in the top 10 results...

for example,

1, bob barkey, bob, barkey, mr, director, fake@..., 1
1, bob barkey, bob, barkey, mr, director, fake@..., 2
1, bob barkey, bob, barkey, mr, director, fake@..., 3
2, bob1 barkey1, bob1, barkey1, mr, director, fake@..., 1
2, bob1 barkey1, bob1, barkey1, mr, director, fake@..., 2
2, bob1 barkey1, bob1, barkey1, mr, director, fake@..., 3
2, bob1 barkey1, bob1, barkey1, mr, director, fake@..., 4
3, bob2 barkey2, bob2, barkey2, mr, director, fake@..., 1
3, bob2 barkey2, bob2, barkey2, mr, director, fake@..., 2
3, bob2 barkey2, bob2, barkey2, mr, director, fake@..., 3

will return

1, bob barkey, bob, barkey, mr, director, fake@...
2, bob1 barkey1, bob1, barkey1, mr, director, fake@...
3, bob2 barkey2, bob2, barkey2, mr, director, fake@...



Derek Knapp
/Software Engineer/
*itrac**MEDIA, Inc.*
1560 Queen Street East, Toronto, ON M4L 1E9  |   Tel: 416.364.9444   |   Fax:416.364.9589

itrac_logo


Tom Ware wrote:

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@...
https://dev.eclipse.org/mailman/listinfo/eclipselink-users
 

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

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

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

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

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

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