cayenne-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Juan Manuel Diaz Lara <jmdia...@yahoo.com.INVALID>
Subject Bug on DISTINCT handle.
Date Wed, 29 Nov 2017 14:44:56 GMT
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
Mime
  • Unnamed multipart/alternative (inline, None, 0 bytes)
View raw message