[
https://issues.apache.org/jira/browse/CAY-1884?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Ramiro Aparicio updated CAY-1884:
---------------------------------
Attachment: ProtOnMap.zip
Full map of our project.
> 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
(v6.1#6144)
