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
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
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
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs