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