I have an example here where replacing a string literal with a string parameter in the same query yields different results. See Java code below, and note that this example works with int as well as float. In one case the comparisions are done as numbers in the other they are done as strings.
The explanation I received from Tom Lane is that in stmtA with (x < '100'), '100' is an untyped literal, in stmtB and stmtC (x < ?), the parameter is treated as typed, and thus the coercion occurs differently. I'm afraid someone is going to answer "it works this way because..." I'm more interested to know if anyone else thinks it is a problem that these two statements return different results.
I seems to me that these queries reasonably have to be considered the same, and should return the same answer..
Thanks,
Matt
-- SQL
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');
-- JAVA
PreparedStatement stmtA = conn.prepareStatement("SELECT * FROM Floats WHERE x < '100'");
ResultSet rsltA = stmtA.executeQuery();
System.out.println("A\n----------");
while (rsltA.next())
System.out.println(rsltA.getFloat(1));
PreparedStatement stmtB = conn.prepareStatement("SELECT * FROM Floats WHERE x < ?");
stmtB.setObject(1, "100");
ResultSet rsltB = stmtB.executeQuery();
System.out.println("B\n----------");
while (rsltB.next())
System.out.println(rsltB.getFloat(1));
PreparedStatement stmtC = conn.prepareStatement("SELECT * FROM Floats WHERE x < ?");
stmtC.setString(1, "100");
ResultSet rsltC = stmtB.executeQuery();
System.out.println("C\n----------");
while (rsltC.next())
System.out.println(rsltC.getFloat(1));
-- OUTPUT
A
----------
1.0
2.0
3.0
4.0
5.0
10.0
20.0
30.0
40.0
50.0
B
----------
1.0
10.0
C
----------
1.0
10.0