Skip site navigation (1) Skip section navigation (2)

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

From: "Matthew Bellew" <postgres(at)bellew(dot)net>
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #2242: Inconsistent casting in query with literal vs query with parameter
Date: 2006-02-06 18:48:41
Message-ID: 20060206184841.1A903F0AC7@svr2.postgresql.org (view raw or flat)
Thread:
Lists: pgsql-bugs
The following bug has been logged online:

Bug reference:      2242
Logged by:          Matthew Bellew
Email address:      postgres(at)bellew(dot)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');

Responses

pgsql-bugs by date

Next:From: Márcio A. SeppDate: 2006-02-06 20:15:32
Subject: Problems with createlang - windows
Previous:From: Tom LaneDate: 2006-02-06 18:10:42
Subject: Re: BUG #2239: "vacuumdb -a" remove freeze

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group