Skip site navigation (1) Skip section navigation (2)

Difference between 7.2 and 7.3, possible bug?

From: Barry Lind <barry(at)xythos(dot)com>
To: pgsql-hackers(at)postgresql(dot)org
Cc: pgsql-jdbc <pgsql-jdbc(at)postgresql(dot)org>
Subject: Difference between 7.2 and 7.3, possible bug?
Date: 2002-10-13 04:19:52
Message-ID: 3DA8F468.106@xythos.com (view raw or flat)
Thread:
Lists: pgsql-hackerspgsql-jdbc
create table test (col_a bigint);
update test set col_a = nullif('200', -1);

The above works fine on 7.2 but the update fails on 7.3b2 with the 
following error:

ERROR:  column "col_a" is of type bigint but expression is of type text
	You will need to rewrite or cast the expression

Is this change in behavior intentional or is it a bug?


This situation is occuring because of two changes.  The first being the 
difference in how the server is handling the above update in 7.2 vs. 
7.3.  The second is a change in the jdbc driver in 7.3.  The actual 
update in jdbc looks like:
update test set col_a = nullif(?, -1);
and a "setLong(1, 200)" call is being done.  In 7.2 the jdbc driver 
bound the long/bigint value as a plain number, but in 7.3 it binds it 
with quotes making it type text and exposing the change in server 
behavior.  This change was made in the jdbc driver to work around the 
fact that indexes are not used for int2 or int8 columns unless the value 
is enclosed in quotes (or an explicit cast is used).  I am not sure if 
the recent changes for implicit casts fixes this index usage problem in 
the server or not.

So I have three options here:

1) if this is a server bug wait for a fix for 7.3
2) revert the jdbc driver back to not quoting int2 and int8 values
      - If the server now handles using indexes on int2/int8 columns then
        this should be done anyway
      - It the server still has problems with using indexes without the
        quotes then this removes an often requested bugfix/workaround
        for the index usage problem
3) Just have people rework their sql to avoid the change in behavior

Any suggestions?


thanks,
--Barry



Responses

pgsql-hackers by date

Next:From: Tom LaneDate: 2002-10-13 05:05:24
Subject: Re: [HACKERS] Difference between 7.2 and 7.3, possible bug?
Previous:From: Masaru SugawaraDate: 2002-10-13 02:23:36
Subject: Transactions through dblink_exec()

pgsql-jdbc by date

Next:From: Tom LaneDate: 2002-10-13 05:05:24
Subject: Re: [HACKERS] Difference between 7.2 and 7.3, possible bug?
Previous:From: Aaron MulderDate: 2002-10-13 04:13:56
Subject: Re: NullPointer exception in ResultSet.getString()

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group