Re: Cayenne 4.2/Postgres - difference in generated SQL based on nesting in expressions

I see. It’s a good question, but I would just stick with .andExp in that
case. I prefer using that one anyway due to historical usage and also to
keep things consistent if I have Expressions defined apart from queries.

On Tue, Aug 18, 2020 at 4:30 PM Hugi Thordarson <[email protected]> wrote:

> Hi John,
>
> the grouping of the expressions (the locations of the parentheses) only
> relate to the Cayenne expression. The main issue is the different SQL
> generated by Cayenne 4.2 from those expressions due to the grouping.
>
> If you look at the SQL generated for (2), the last condition in the where
> clause (that uses date_time) is applied with a "=" rather than "AND". It's
> something I honestly just haven't seen or used before (and I'm embarrassed
> not knowing how or why it works, reading up right now). But I assume it
> means it first applies the first condition and then applies the second
> condition to the result of that to return a final result. In my case, that
> would make sense performance wise—I have indexes on "date_time" so if
> date_time is not included in the first query, we're going to have a bad
> time.
>
> What I don't get is why that "=" is generated instead of and "AND". If
> there's an "AND" the index gets used, regardless of the order of the
> expressions.
>
> - hugi
>
>
>
>
> > On 18 Aug 2020, at 18:10, John Huss <[email protected]> wrote:
> >
> > I would tend to think the postgres query planner is smart enough to
> handle
> > the differences in the placement of parentheses if that is the only
> > difference. But in any case, try adding "explain analyze" to the
> beginning
> > of the query and then compare the query plans between the fast version
> and
> > the slow version. You can post them here too. That would be the best
> place
> > to start I think.
> >
> > On Tue, Aug 18, 2020 at 11:57 AM Hugi Thordarson <[email protected]>
> wrote:
> >
> >> Hi all,
> >> I'm currently migrating a large project to Cayenne 4.2. Code wise the
> >> transition was easy enough, apart from one thing.
> >>
> >> Consider the following two expressions. Both are generated from the same
> >> three conditions.
> >>
> >> In the first case they're combined using exp1.andExp( exp2 ).andExp(
> exp3
> >> ).
> >>
> >> In the second case, they're combined using:
> >>
> >> ObjectSelect
> >>        .query( SomeClass.class )
> >>        .where( exp1 )
> >>        .and( exp2 )
> >>        .and( exp3 )
> >>
> >> The two different methods yield:
> >> 1) (customer = <ObjectId:Customer, company=0100007294, customer_no=40>)
> >> and (key.number = "161-400") and (date > 2015-08-18)
> >> 2) ((customer = <ObjectId:Customer, company=0100007294, customer_no=40>)
> >> and (key.number = "161-400")) and (date > 2015-08-18)
> >>
> >> Note the different nesting. In Cayenne 4.1 this was not a problem since
> >> the two expressions generated more or less the same SQL.
> >>
> >> However in 4.2.M2-SNAPSHOT, the generated SQL is quite different,
> >> resulting in a large performance loss in the latter case (as in, queries
> >> that previously took a few of milliseconds now take minutes). Here's the
> >> SQL generated — note the comparison that happens with the last
> (date_time)
> >> condition in (2):
> >>
> >> SQL generated by 1:
> >> - SELECT DISTINCT "t0"."amount" FROM "nb_movement" "t0" JOIN "nb_key"
> "t1"
> >> ON ( "t0"."company" = "t1"."company" ) AND ( "t0"."gl_number" =
> >> "t1"."gl_number" ) WHERE ( ( ( "t0"."company" = ? ) AND (
> >> "t0"."customer_no" = ? ) AND ( "t1"."gl_number" = ? ) AND (
> >> "t0"."date_time" > ? ) ) ) LIMIT 10 [bind: 1:'0100007294', 2:40,
> >> 3->gl_number:'161-400', 4->date_time:2015-08-18]
> >>
> >> SQL generated by 2:
> >> - SELECT DISTINCT "t0"."amount" FROM "nb_movement" "t0" JOIN "nb_key"
> "t1"
> >> ON ( "t0"."company" = "t1"."company" ) AND ( "t0"."gl_number" =
> >> "t1"."gl_number" ) WHERE ( ( ( ( "t0"."company" = ? ) AND (
> >> "t0"."customer_no" = ? ) AND ( "t1"."gl_number" = ? ) ) = (
> >> "t0"."date_time" > ? ) ) ) LIMIT 10 [bind: 1:'0100007294', 2:40,
> >> 3->gl_number:'161-400', 4->date_time:2015-08-18]
> >>
> >> Any ideas?
> >>
> >> I'd like to add the disclaimer that I have not been able to identify the
> >> exact conditions that result in the generation of the slower SQL, but it
> >> certainly does not happen in all cases (i.e. will only happen with some
> >> combinations of conditions).
> >>
> >> But I'd be happy to try to create a reproducible standalone test-case if
> >> required, just give me a shout. I just wanted to start out by throwing
> this
> >> out there.
> >>
> >> Cheers,
> >> - hugi
>
>