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

The following bug has been logged on the website:

Bug reference:      11771
Logged by:          Pavel Chocholous
Email address:      chocholousp@avast.com
PostgreSQL version: 9.3.4
Operating system:   Centos
Description:

wrong behaviour of planner when pushing conditions from outer query to
subselect
(just when joining??)

drop table t;
--create table for varchars
create temporary table t (t varchar(64));
--fill it with two columns
insert into t (t) values ('88652f64-6cca-4ffa-a756-000007406ba6');
insert into t (t) values ('bad guid');
insert into t (t) values ('88652f64-6cca-4ffa-a756');






--this just works
select * from
 (select t::uuid from t
  --filter out all non-uuid varchars
  where t ~
'^[a-z0-9]{8}-[a-z0-9]{4}-[a-z0-9]{4}-[a-z0-9]{4}-[a-z0-9]{12}$'
  ) x
where x.t::uuid = '88652f64-6cca-4ffa-a756-000007406ba6'::uuid



--and then...
--here we go, do you see something wrong?

select * from
 (select t::uuid from t
  --filter out all non-uuid varchars
  where t ~
'^[a-z0-9]{8}-[a-z0-9]{4}-[a-z0-9]{4}-[a-z0-9]{4}-[a-z0-9]{12}$'
  ) x
inner join t on t.t::uuid=x.t::uuid
where x.t::uuid = '88652f64-6cca-4ffa-a756-000007406ba6'::uuid

chocholousp@avast.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