Fantastic, thanks for the pointer this parameter. I would argue that
the expected behavior would be that these two cases are semantically
equivalent. The fact that this is configurable is great, but my gripe
would be that the default setting results in 'wrong' answers for a very
unobvious reason.
Again thanks,
Matt
Kris Jurka wrote:
>
>
> 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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend