Обсуждение: 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