Re: BUG #3012: Wrong JOIN order when a JOIN depends on result from a LEFT JOIN.

"Pelle Johansson" <morth(at)morth(dot)org> writes:
> We have a join where we select which row to join on a subquery with a
> coalesce on a column from a left join, which is not working as expected.

Hm, this is a fun one. The problem basically is that

(1) The join qual "appear.tb_id = (SELECT ..." gets marked as
is_pushed_down = false, because it uses all three relations of the
outer query (ta, last_delete, tb) and so it's not possible to
evaluate it at any lower syntactic level.

(2) For whatever reason, the planner decides it can swap the order of
the two joins and do the innerjoin first. (This wasn't possible before
8.2, hence no bug before.)

(3) The join qual is correctly placed at the left join, since it's now
the top join ... but because the qual's not marked is_pushed_down, the
createplan.c code thinks it's a join qual of the outer join, and hence
stores it as a "Join Filter" instead of just "Filter". This makes the
wrong things happen --- the executor emits null-extended rows wherever
the qual fails, instead of emitting no row as it should.

I think we can band-aid this by forcing is_pushed_down = true for all
innerjoin quals, but that suggests that the whole concept may need a
bit of a rethink...

regards, tom lane