Re: Always getting distinct results in fetch?

Need to experiment with this one. Looks like it may need some adjustment.

> On Oct 20, 2021, at 9:29 PM, Nikita Timofeev <[email protected]> wrote:
> 
> Hi all,
> 
> This behavior is still in 4.2. To make it odder, selecting a single
> column would return all rows like with suppressDistinct() call.
> This is caused by the logic deep inside mapping result rows to objects
> [1], that came, I believe, from the EJBQL.
> Not sure I know why it's there, though.
> 
> [1] https://github.com/apache/cayenne/blob/master/cayenne-server/src/main/java/org/apache/cayenne/access/DataDomainQueryAction.java#L816
> 
> On Wed, Oct 20, 2021 at 8:28 AM Andrus Adamchik <[email protected]> wrote:
>> 
>> Yeah, this seems wrong. "Historically" distinct would be implicitly added (either to SQL or to the processed result) when a query condition contained to-many relationships. The goal was to preserve the abstraction of an "object" query, and return the same consistent result regardless of the SQL specifics.
>> 
>> This is not the case here, as the query is not object-based, neither there is a to-many relationship involved.
>> 
>> I recall we discussed it with Nikita long time ago, just don't remember what the outcome of that discussion was :) Maybe Nikita can shed some light?
>> 
>> Andrus
>> 
>>> On Oct 19, 2021, at 4:57 PM, Hugi Thordarson <[email protected]> wrote:
>>> 
>>> Followup—I added .suppressDistinct() to the ObjectSelect and that fixed my problem, i.e. the list contained all 462.653 rows.
>>> 
>>> However, I'm still a bit flummoxed… I didn't realize the fetch was distinct because the "distinct" keyword did not appear in the generated SQL. However, the keyword *is* added to the SQL if I explicitly specify distinct(). Somewhat odd.
>>> 
>>> Note that all of this is on Cayenne 4.1 and I haven't tried this on 4.2 (where everything is changed behind the scenes). Perhaps all of this is different there and I should just keep silent for now? :)
>>> 
>>> Cheers,
>>> - hugi
>>> 
>>> 
>>> 
>>>> On 19 Oct 2021, at 13:45, Hugi Thordarson <[email protected]> wrote:
>>>> 
>>>> Hi all,
>>>> let's say I'm performing a simple single column fetch. Example:
>>>> 
>>>> --------------------------------------------------
>>>> List<Object[]> customers = ObjectSelect
>>>>     .query( Customer.class )
>>>>     .columns( Customer.CPRNR )
>>>>     .select( oc );
>>>> 
>>>> System.out.println( "Customers: " + customers.size() );
>>>> --------------------------------------------------
>>>> 
>>>> This generates the following output:
>>>> 
>>>> --------------------------------------------------
>>>> - --- transaction started.
>>>> - SELECT "t0"."cprnr" FROM "nb_customer" "t0"
>>>> - === returned 462653 rows. - took 210 ms.
>>>> - +++ transaction committed.
>>>> Customers: 169770
>>>> --------------------------------------------------
>>>> 
>>>> As you can see, the fetch returns 462.653 rows which is the number of customers in the DB, and the number I expected.
>>>> However, the resulting list only contains 169.770 objects—the number of distinct values of CPRNR.
>>>> 
>>>> Why? It's not a distinct fetch. I'm feeling particularly stupid today, and I'm definitely missing something here, I just don't understand what :).
>>>> 
>>>> Cheers,
>>>> - hugi
>>> 
>> 
> 
> 
> -- 
> Best regards,
> Nikita Timofeev