You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@cayenne.apache.org by Ramiro Aparicio <ra...@prot-on.com> on 2013/11/12 17:59:45 UTC
Issue with distinct 3.1
I found some strange behaviour trying to use Distinct for the first time.
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 I first 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]
I am really not sure if current behaviour without distinct is ok, but at
least it works for me.
Best regards.
Ramiro Aparicio
Re: Issue with distinct 3.1
Posted by Andrus Adamchik <an...@objectstyle.org>.
> So really there is little to no use for setDistinct in a SelectQuery.
I think so too. Other than some messed up DB with de-facto non-unique PK, I can’t think why that would be needed.
> Andrus if you want I can open a Jira Issue
Yes please. And I wonder if we should just deprecate ‘distinct’ as a solution.
Andrus
On Nov 13, 2013, at 1:05 PM, Ramiro Aparicio <ra...@prot-on.com> wrote:
> So really there is little to no use for setDistinct in a SelectQuery.
>
> Anyway I was more concerned about the Where clause, I can assure that I am using a non null owner but even then owner is a User object so there is no reason why Cayenne should try to match agains a Contact and distinct should not change the where clause in this query.
>
> For reference Contact entity has 3 attributes:
> + contactOwner : relationship with User
> + contactTarget: relationship with User
> + idcontact: AI PK
>
> Andrus if you want I can open a Jira Issue
>
> Ramiro
>
> El 13/11/2013 8:00, Andrus Adamchik escribió:
>> Cayenne forces DISTINCT in the generated SQL whether you specify it or not because it detects a match on to-many relationship and realizes that it needs to get rid of duplicates in the cartesian product. So I guess explicit DISTINCT shouldn’t normally be needed.
>>
>> As to why NULL is bound in the DISTINCT case, this could either be a yet unknown bug in the framework, or something in the app code.
>>
>> Andrus
>>
>> On Nov 13, 2013, at 1:15 AM, Aristedes Maniatis <ar...@maniatis.org> wrote:
>>
>>> On 13/11/2013 3:59am, Ramiro Aparicio wrote:
>>>
>>>> I am really not sure if current behaviour without distinct is ok, but at least it works for me.
>>> Cayenne returns a collection of objects that will never have duplicates. In fact you can choose whether the results return as a Set, List or Map.
>>>
>>> Ari
>>>
>>>
>>>
>>>
>>>
>>> --
>>> -------------------------->
>>> Aristedes Maniatis
>>> GPG fingerprint CBFB 84B4 738D 4E87 5E5C 5EFA EF6A 7D2E 3E49 102A
>>>
>
>
Re: Issue with distinct 3.1
Posted by Ramiro Aparicio <ra...@prot-on.com>.
So really there is little to no use for setDistinct in a SelectQuery.
Anyway I was more concerned about the Where clause, I can assure that I
am using a non null owner but even then owner is a User object so there
is no reason why Cayenne should try to match agains a Contact and
distinct should not change the where clause in this query.
For reference Contact entity has 3 attributes:
+ contactOwner : relationship with User
+ contactTarget: relationship with User
+ idcontact: AI PK
Andrus if you want I can open a Jira Issue
Ramiro
El 13/11/2013 8:00, Andrus Adamchik escribió:
> Cayenne forces DISTINCT in the generated SQL whether you specify it or not because it detects a match on to-many relationship and realizes that it needs to get rid of duplicates in the cartesian product. So I guess explicit DISTINCT shouldn’t normally be needed.
>
> As to why NULL is bound in the DISTINCT case, this could either be a yet unknown bug in the framework, or something in the app code.
>
> Andrus
>
> On Nov 13, 2013, at 1:15 AM, Aristedes Maniatis <ar...@maniatis.org> wrote:
>
>> On 13/11/2013 3:59am, Ramiro Aparicio wrote:
>>
>>> I am really not sure if current behaviour without distinct is ok, but at least it works for me.
>> Cayenne returns a collection of objects that will never have duplicates. In fact you can choose whether the results return as a Set, List or Map.
>>
>> Ari
>>
>>
>>
>>
>>
>> --
>> -------------------------->
>> Aristedes Maniatis
>> GPG fingerprint CBFB 84B4 738D 4E87 5E5C 5EFA EF6A 7D2E 3E49 102A
>>
Re: Issue with distinct 3.1
Posted by Andrus Adamchik <an...@objectstyle.org>.
Cayenne forces DISTINCT in the generated SQL whether you specify it or not because it detects a match on to-many relationship and realizes that it needs to get rid of duplicates in the cartesian product. So I guess explicit DISTINCT shouldn’t normally be needed.
As to why NULL is bound in the DISTINCT case, this could either be a yet unknown bug in the framework, or something in the app code.
Andrus
On Nov 13, 2013, at 1:15 AM, Aristedes Maniatis <ar...@maniatis.org> wrote:
> On 13/11/2013 3:59am, Ramiro Aparicio wrote:
>
>> I am really not sure if current behaviour without distinct is ok, but at least it works for me.
>
> Cayenne returns a collection of objects that will never have duplicates. In fact you can choose whether the results return as a Set, List or Map.
>
> Ari
>
>
>
>
>
> --
> -------------------------->
> Aristedes Maniatis
> GPG fingerprint CBFB 84B4 738D 4E87 5E5C 5EFA EF6A 7D2E 3E49 102A
>
Re: Issue with distinct 3.1
Posted by Aristedes Maniatis <ar...@maniatis.org>.
On 13/11/2013 3:59am, Ramiro Aparicio wrote:
> I am really not sure if current behaviour without distinct is ok, but at least it works for me.
Cayenne returns a collection of objects that will never have duplicates. In fact you can choose whether the results return as a Set, List or Map.
Ari
--
-------------------------->
Aristedes Maniatis
GPG fingerprint CBFB 84B4 738D 4E87 5E5C 5EFA EF6A 7D2E 3E49 102A