Ramiro Aparicio created CAY-1884:
------------------------------------
Summary: 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
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)
