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
