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 <aadamc...@gmail.com> 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 <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 > > > -- Best regards, Nikita Timofeev
