Re: Inconsistent casting with literal vs parameter

On Tue, 7 Feb 2006, Matthew Bellew wrote:

> 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..

I don't think these queries are the same, consider
SELECT '12' < '2', 12 < 2;

In the first case the server considers it text and the second it considers
it numeric.  Now when considering the mixed case the server has to decide
what to do.  When you say 12 < '2' you aren't conclusively stating what
type '2' is and it gets coerced to numeric, but when you say setString you
are explicitly telling it that it is a text data type and a text
comparison should be done.  When you say setObject with a String object
you also say that it is text data.  If you want it interpreted as numeric
data use setFloat or similar.

The 8.2 driver has an option to allow setString data to passed to the
server without a type and you will get the result you desire.  See the
stringtype parameter here:

http://jdbc.postgresql.org/documentation/head/connect.html#connection-parameters

Kris Jurka