Re: Follow up to Conditional SELECT returns incorrect results.

"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