Re: BUG #11771: wrong behaviour of planner when pushing conditions

chocholousp(at)avast(dot)com writes:
> wrong behaviour of planner when pushing conditions from outer query to
> subselect

There's nothing wrong with what the planner did here. There is no
constraint on reordering the application of WHERE clauses with an
inner join --- if there were, it'd be catastrophic to performance
in many real queries.

In the particular case at hand, what's actually happening is that
the two equalities

t.t::uuid = x.t::uuid
x.t::uuid = '88652f64-6cca-4ffa-a756-000007406ba6'::uuid

get reassociated into

t.t::uuid = '88652f64-6cca-4ffa-a756-000007406ba6'::uuid
x.t::uuid = '88652f64-6cca-4ffa-a756-000007406ba6'::uuid

so that the condition on t.t isn't a join condition at all and
can get applied to the (unprotected) scan of t. So the condition
in the subselect has nothing to do with whether a failure occurs.
However, even without that, you would have had failures when the
join condition was applied, because the fact that x.t can validly
be cast to a uuid doesn't imply that every t.t value it could be
compared to can be cast to uuid.

regards, tom lane