Bug on DISTINCT handle.

classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

Bug on DISTINCT handle.

Juan Manuel Diaz Lara
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
Reply | Threaded
Open this post in threaded view
|

Re: Bug on DISTINCT handle.

Nikita Timofeev
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
<[hidden email]> 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
Reply | Threaded
Open this post in threaded view
|

Re: Bug on DISTINCT handle.

Juan Manuel Diaz Lara
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 <[hidden email]> 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
<[hidden email]> 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