Thread: BUG #2242: Inconsistent casting in query with literal vs query with parameter

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');

"Matthew Bellew" <postgres@bellew.net> writes:
> 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.

I see no bug here.  You are confused about the difference between an
unknown literal ('100') and a value that is actually declared to be of
type text.  See
http://www.postgresql.org/docs/8.1/static/typeconv.html

            regards, tom lane