I tried 4.02.B2 with same behavior.
Atte. Juan Manuel Díaz Lara
On Wednesday, November 29, 2017, 9:02:34 AM CST, Nikita Timofeev
<[email protected]> wrote:
Hi,
Thanks for such a detailed information, we are investigating this
behavior. Looks really like a bug to me.
Meanwhile could you try latest 4.0.B2 release?
On Wed, Nov 29, 2017 at 5:44 PM, Juan Manuel Diaz Lara
<[email protected]> wrote:
> I have an entity Products with longvarbinary columns, and I was getting the
> same entity duplicated on results when using queries that include conditions
> on to-many relationships:
> @Test
> public void test() {
> SelectQuery<Products> query = SelectQuery.query(Products.class);
> query.setQualifier(ExpressionFactory.exp("code = 'USBVGA' and
>existencias_corporativas.units > 0"));
> query.setDistinct(true);
> @SuppressWarnings("unchecked")
> List<Products> lp = (List<Products>) dao.context.performQuery(query);
> Assert.assertTrue(lp.size() == 1);
> }
>
> existencias_corporativas is a toMany relationship from products (Products
> -toMany->existencias_corporativas).
> This test fails because the result list have many items, but all items are
> the same product entity (in db code has a unique constraint). The
> setDistinct() call did not help. I tried ObjectSelect with same result.
>
> Tracing code, I arrived to DefaultSelectTranslator#isUnsupportedForDistinct,
> this is the cause for cayenne not including a DISTINCT in generated SQL,
> because I have columns mapping to types "not supported for distinct clause".
> But I found a note on DefaultSelectTranslator#isSuppressingDistinct that
> tells that cayenne should should do an in-memory distinct operation when
> DISTINCT clause was suppressed, so this is not working well (a bug ?).
> I conclude that:
> 1. Cayenne is deciding with types of columns can be used for DISTINCT, but
> this is not generally true, some db's ignore not sortable columns when used
> in DISTINCT queries. Maybe this decision should be delegated to the specific
> db translator.
>
> 2. There is a bug because cayenne must perform the in-memory distinct when
> suppressing DISTINCT in generated SQL.
>
> To prove, I removed my longvarbinary columns and the problem gone, a DISTINCT
> SQL was generated and queries results does not have duplicates, this
> confirming the previous conclusions.
> Excuse me for reporting here, I do not know where or how to do.
>
> Thanks.
>
> Atte. Juan Manuel Díaz Lara
--
Best regards,
Nikita Timofeev
