JDBC driver setNull(_,Types.OTHER) bug

From: Tazhkenov Denis <imaskar(at)live(dot)ru>
To: "pgsql-bugs(at)postgresql(dot)org" <pgsql-bugs(at)postgresql(dot)org>
Subject: JDBC driver setNull(_,Types.OTHER) bug
Date: 2015-09-16 10:54:50
Message-ID: DUB128-W530EEFFF5B2450BD29CD9EAB5B0@phx.gbl
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

tl/dr:
select 'ok' where ?=? or (? is null)
stp.setObject(1, UUID.randomUUID(), Types.OTHER);
stp.setNull(2, Types.OTHER); // works
stp.setNull(3, Types.OTHER); // doesn't work
tl/dr end

Suppose, you use uuid columns in your table and need to compare a cloumn value with a parameter and alose to compare this parameter with null. In this case you have to use Types.OTHER to compare parameter with your column, but you can't use Types.OTHER whith (? is null) clause, because it issues error "can't detect parameter datatype for parameter$_" (not actual message, mine is localized).

Tested with versions:

driver
postgresql-9.4-1202.jdbc42

postgresql-9.4-1202.jdbc41

postgresql-9.3-1101.jdbc4db
postgres (PostgreSQL) 9.3.5

Test case:
http://ideone.com/W3sfhI

It does have some workarounds:
*if you know that parameter will be of uuid type, add cast to your clause;
-inconvinient, if your statements are auto-generated or if you can't change them (responsibility scope!);
-may slow down queries if used too broad;
*if at runtime you are setting uuid parameter to null, use Types.VARCHAR;
-didn't test, if it would correctly work when comparing with actual column, or writing there.

Maybe there is some pg-specific typeId for the uuid type?

Regards,
Denis Tazhkenov

Browse pgsql-bugs by date

  From Date Subject
Next Message Userob 2015-09-16 11:11:02 Re: BUG #13621: Program paths are registered incorrectly.
Previous Message 林俊彥 2015-09-16 10:27:46 [Bug / Question ] " authentication_timeout " is invalid.