Обсуждение: Inconsistent casting with literal vs parameter

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

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

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

I wrote the following code

        Properties props = new Properties();
        props.put("user", "postgres");
        props.put("password", "****");
        props.put("stringtype", "unspecified");
        Connection conn =
DriverManager.getConnection("jdbc:postgresql://localhost/cpas", props);
        DatabaseMetaData md = conn.getMetaData();
        System.out.println("Server URL:               " + md.getURL());
        System.out.println("Database Product Name:    " +
md.getDatabaseProductName());
        System.out.println("Database Product Version: " +
md.getDatabaseProductVersion());
        System.out.println("JDBC Driver Name:         " +
md.getDriverName());
        System.out.println("JDBC Driver Version:      " +
md.getDriverVersion());

Unfortunately, it gives the same result.

    Server URL:               jdbc:postgresql://localhost/cpas
    Database Product Name:    PostgreSQL
    Database Product Version: 8.1.0
    JDBC Driver Name:         PostgreSQL Native Driver
    JDBC Driver Version:      PostgreSQL 8.1 JDBC3 with SSL (build 404)

So, does this mean this parameter is not properly respected in the driver?

Matt

Matthew Bellew wrote:

> 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
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend

On Tue, 7 Feb 2006, Matthew Bellew wrote:

>   JDBC Driver Version:      PostgreSQL 8.1 JDBC3 with SSL (build 404)
>
> So, does this mean this parameter is not properly respected in the driver?
>

Yes, this parameter is only available in the 8.2 driver.

Kris Jurka

Right, I missed that.

My last 2 cents on the whole topic.  There is a fundamental design issue here with deciding to handle the expression column < (value/literal/parameter typed or not) by converting the column to a string.  Obviously, with INSERT INTO the data values must be coerced to the destination column type.  In this case, while there is a parsing/optimizing decision to be made, it should be made with a strong bias to convert to the column type (and then handling any tricky coercion failures or optimization issues).

Again thanks for your help,
Matt

Kris Jurka wrote:

On Tue, 7 Feb 2006, Matthew Bellew wrote:

  JDBC Driver Version:      PostgreSQL 8.1 JDBC3 with SSL (build 404)

So, does this mean this parameter is not properly respected in the driver?

Yes, this parameter is only available in the 8.2 driver.

Kris Jurka

Hi, Matthew,

Matthew Bellew wrote:

> My last 2 cents on the whole topic.  There is a fundamental design issue
> here with deciding to handle the expression /column <
> (value/literal/parameter typed or not)/ by converting the column to a
> string.  Obviously, with INSERT INTO the data values must be coerced to
> the destination column type.  In this case, while there is a
> parsing/optimizing decision to be made, it should be made with a strong
> bias to convert to the column type (and then handling any tricky
> coercion failures or optimization issues).

The problem is that you explicitly send the parameter as a String to the
server, so it gets the bias to coerce the column to the string.

However, you can always make shure the parameters are compared set
correctly by explicityl casting them:

SELECT * FROM floats WHERE x<(?::double precision);

or

SELECT * FROM floats WHERE x<CAST(? as double precision);

AFAIR, the latter one is standards conforming, and such more likely to
work on other databases.

HTH,
Markus
--
Markus Schaber | Logical Tracking&Tracing International AG
Dipl. Inf.     | Software Development GIS

Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org