The following bug has been logged online:
Bug reference: 2242
Logged by: Matthew Bellew
Email address: postgres@bellew.net
PostgreSQL version: 8.1.0
Operating system: XP SP2
Description: Inconsistent casting in query with literal vs query with
parameter
Details:
In the script below, I'd expect all four queries to return 10 rows
(1,2,3,4,5,10,20,30,40,50). However, function bystr() returns two rows
(1,10). Clearly, in this one case the query processor is casting the column
to the parameter type, rather than the other way around. The optimizer
should always preferentially cast the parameter to the type of the column.
--drop table Floats;
create table Floats (x float);
insert into Floats values (1);
insert into Floats values (2);
insert into Floats values (3);
insert into Floats values (4);
insert into Floats values (5);
insert into Floats values (10);
insert into Floats values (20);
insert into Floats values (30);
insert into Floats values (40);
insert into Floats values (50);
insert into Floats values (100);
insert into Floats values (200);
insert into Floats values (300);
insert into Floats values (400);
insert into Floats values (500);
select 'QUERY 1', *
from Floats
where x < 100;
create or replace function byint(int) returns setof float as $$
select *
from Floats
where x < $1
$$ LANGUAGE SQL;
select byint(100);
select 'QUERY 2', *
from Floats
where x < '100';
create or replace function bystr(text) returns setof float as $$
select *
from Floats
where x < $1
$$ LANGUAGE SQL;
select bystr('100');
