Inconsistent casting with literal vs parameter

From: Matthew Bellew <matthew(at)bellew(dot)net>
To: pgsql-jdbc(at)postgresql(dot)org
Subject: Inconsistent casting with literal vs parameter
Date: 2006-02-07 22:28:10
Message-ID: 43E91EFA.4080106@bellew.net
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-jdbc

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

Responses

Browse pgsql-jdbc by date

  From Date Subject
Next Message Kris Jurka 2006-02-07 22:44:29 Re: Inconsistent casting with literal vs parameter
Previous Message Ralph Moser 2006-02-07 09:27:42 Re: Bug Report