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 > 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 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 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 wrote: Hi all, let's say I'm performing a simple single column fetch. Example: -- List 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
Re: Always getting distinct results in fetch?
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 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 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 wrote: > >> > >> Hi all, > >> let's say I'm performing a simple single column fetch. Example: > >> > >> -- > >> List 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
Re: Always getting distinct results in fetch?
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 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 wrote: >> >> Hi all, >> let's say I'm performing a simple single column fetch. Example: >> >> -- >> List 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 >
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 wrote: > > Hi all, > let's say I'm performing a simple single column fetch. Example: > > -- > List 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
Always getting distinct results in fetch?
Hi all, let's say I'm performing a simple single column fetch. Example: -- List 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
