cayenne-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Ramiro Aparicio (JIRA)" <j...@apache.org>
Subject [jira] [Created] (CAY-1884) setDistinct on SelectQuery generates a wrong MySql query
Date Fri, 15 Nov 2013 17:17:21 GMT
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)

Mime
View raw message