Barry Lind <barry@xythos.com> writes:
> 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 is an intentional tightening of implicit-cast behavior.
> 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.
I would say that that is a very bad decision in the JDBC driver and
should be reverted ... especially if the driver is not bright enough
to notice the context in which the parameter is being used. Consider
for example
regression=# select 12 + 34;
?column?
----------
46
(1 row)
regression=# select '12' + '34';
?column?
----------
d
(1 row)
Not exactly the expected result ...
> 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
You are trying to mask a server problem in the driver. This is not a
good idea. The server problem is short-term (yes, we've finally agreed
how to fix it, and it will happen in 7.4), but a client-library hack to
mask it will cause problems indefinitely.
regards, tom lane