> I prepared a simplified example where the optimizer does not choose
> the optimal plan.
> The requested result is the same for each query, as far as I can
> see, written down in other words only. As far as I can see, the
> optimizer is not able to transform a parenthesized left join so that
> no natural term in the plan is used for the left table if the set of
> cadidate rows of this table is restricted by conditions outside the
> perenthesis.
>
> SELECT 1:
> select
> a.*, c.*
> from
> t_a a left join t_c c on a.id = c.ta_id
> inner join t_b b on a.id = b.member_id
> inner join t_a a2 on a2.id = b.owner_id
> where
> a2.uid = 100
>
> PLAN 1: (non-optimal)
> PLAN JOIN (JOIN (JOIN (A NATURAL, C INDEX (PK_TC)), B INDEX
> (IX_TB_MEMBER)), A2 INDEX (PK_TA))

There are no conditions to filter outmost table t_a therefore it is natural scanned

...

> SELECT 4:
> select
> a.*, c.*
> from
> (t_b b inner join t_a a2 on a2.id = b.owner_id)
> inner join t_a a on a.id = b.member_id
> left join t_c c on a.id = c.ta_id
> where
> a2.uid = 100
>
> PLAN 4: (optimal)
> PLAN JOIN (JOIN (A2 INDEX (IX_TA_UID), B INDEX (IX_TB_OWNER), A
> INDEX (PK_TA)), C INDEX (PK_TC))

This is different queries therefore they plans also different.
"a inner join b left join c" is NOT equal to "b left join c inner join a"

Regards,
Vlad