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 <h...@karlmenn.is> 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 <h...@karlmenn.is> 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 >
