Re: Always getting distinct results in fetch?

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