Thread: Follow up to Conditional SELECT returns incorrect results.

Hello,



This is a follow up to the bug report filed earlier today.



As reported earlier:



I am receiving incorrect result from a query that is issued via the JDBC
driver. I am running Windows XP, Java version 1.6.0_02, PostgresSQL version
8.2 and using the postgresql-8.2-507-jdbc4.jar. I have also used the
postgresql-8.2-506-jdbc4.jar with the same results.



Java code with try/catch./finally and resource cleanup all left out for
brevity,



String sel = "SELECT testing_table.rec_no, testing_table.test_number FROM
testing_table WHERE test_number >= ? AND test_number <= ? ORDER BY rec_no";

Connection conn = xxx.getConnection();

PreparedStatement ps1 = conn.prepareStatement(sel);



ps1,setString(1, "10.0");

ps1,setString(2, "50.0");



ResultSet  rs = ps1.executeQuery();



while (rs.hasNext())

{

// process a row.

}





After further testing I have determined that if the ps1.setString(1, "10.0")
and ps1.setString(2, "50.0") are replaced by ps1.setDouble(1, 10.0) and
ps1.setDouble(2, 50.0) the query executes correctly. I still believe this is
a bug as the database column definition should determine the comparison type
NOT the PreparedStatement parameter type. It would appear that the parameter
type of String is causing a String comparison to be used. Also, using the
same SELECT statement, with or without quotes around the parameters, in the
PostgresSQL query tool returns the correct results.



Regards,



Gary Gesmundo

ggesmundo@tds.net

"Gary Gesmundo" <ggesmundo@tds.net> writes:
> After further testing I have determined that if the ps1.setString(1, "10.0")
> and ps1.setString(2, "50.0") are replaced by ps1.setDouble(1, 10.0) and
> ps1.setDouble(2, 50.0) the query executes correctly. I still believe this is
> a bug as the database column definition should determine the comparison type
> NOT the PreparedStatement parameter type. It would appear that the parameter
> type of String is causing a String comparison to be used.

So what you are complaining about is this well-known behavior:

regression=# select 9::int < '10'::text;
 ?column?
----------
 f
(1 row)

Try searching the PG archives for previous discussions of implicit casts
to text :-(

FWIW, 8.3 will throw an error instead:

regression=# select 9::int < '10'::text;
ERROR:  operator does not exist: integer < text
LINE 1: select 9::int < '10'::text;
                      ^
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.

which may or may not make you happier.  But the short answer is that
setString is the wrong thing to be using in this context, because it
implies a presumption that the datatype is indeed string.

> Also, using the
> same SELECT statement, with or without quotes around the parameters, in the
> PostgresSQL query tool returns the correct results.

I suspect that what you are trying by hand is '10.0', not '10.0'::text,
which is the real equivalent of what setString does.  The parser's
type resolution rules treat these cases quite a lot differently.

            regards, tom lane