[jira] [Closed] (CAY-1884) setDistinct on SelectQuery generates a wrong MySql query

["Nikita Timofeev (JIRA)" <ji...@apache.org>]

     [ https://issues.apache.org/jira/browse/CAY-1884?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Nikita Timofeev closed CAY-1884.
--------------------------------
    Resolution: Fixed

> setDistinct on SelectQuery generates a wrong MySql query
> --------------------------------------------------------
>
>                 Key: CAY-1884
>                 URL: https://issues.apache.org/jira/browse/CAY-1884
>             Project: Cayenne
>          Issue Type: Bug
>    Affects Versions: 3.1B2
>         Environment: Tomcat 6
>            Reporter: Ramiro Aparicio
>            Priority: Minor
>         Attachments: ProtOnMap.zip
>
>
> I have the following mapping
> User is related with User via a mapping table named Contact, the PK is an id as the User can contact the same target several times.
> I wanted to get all distinct Users contacted by A, so as I wasn't really sure if I needed distinct or not so in the first try I enabled it and the SQL generated makes no sense.
> My code:
>         SelectQuery query =
>                 new SelectQuery(User.class, ExpressionFactory.matchExp(Cayenne.makePath(User.CONTACTED_BY_ARRAY_PROPERTY, Contact.TO_CONTACT_OWNER_PROPERTY), owner));
>         query.setDistinct(true);
>         return performQuery(query);
> The generated SQL query using a non null "owner":
> SELECT DISTINCT t0.idUser, t0.location, t0.PaymentInvoicingData_idInvoicingData, t0.password, t0.ProtOnDomain_idProtOnDomain, t0.username, t0.isPremium, t0.permissionsReadonly, t0.ProtonDomainLDAP_idDomainLdap, t0.lockExternalVisibility, t0.usernameVisibility, t0.locked, t0.Partner_idPartner, t0.CorporateServer_externalIdServer, t0.premium_expiration_notification, t0.completeName, t0.externalIdUser, t0.deleted, t0.isDomainAdmin, t0.accessReadonly, t0.profileReadonly, t0.externalUsername, t0.accountType, t0.premiumExpiration
> FROM user t0
>     JOIN contact t1 ON (t0.idUser = t1.contactTarget)
> WHERE t1.idContact = NULL
> The SQL when distinct is commented:
> SELECT DISTINCT t0.idUser, t0.location, t0.PaymentInvoicingData_idInvoicingData, t0.password, t0.ProtOnDomain_idProtOnDomain, t0.username, t0.isPremium, t0.permissionsReadonly, t0.ProtonDomainLDAP_idDomainLdap, t0.lockExternalVisibility, t0.usernameVisibility, t0.locked, t0.Partner_idPartner, t0.CorporateServer_externalIdServer, t0.premium_expiration_notification, t0.completeName, t0.externalIdUser, t0.deleted, t0.isDomainAdmin, t0.accessReadonly, t0.profileReadonly, t0.externalUsername, t0.accountType, t0.premiumExpiration 
> FROM user t0
>     JOIN contact t1 ON (t0.idUser = t1.contactTarget)
> WHERE t1.contactOwner = ?
> [bind: 1->contactOwner:201]
> As I said owner is not null in both cases but for some reason setting distinct forgets about the real target to match.
>  idContact is an AI PK so that would make sense if I was matching against a Contact object but no when using a User object to match against.



--
This message was sent by Atlassian JIRA
(v7.6.3#76005)