Re: [BUGS] Mapping Hibernate boolean to smallint(Postgresql)

On 29/09/2010 9:02 PM, Oliver Jowett wrote:
> stagirus wrote:
>> Oliver:
>>>> * use a real boolean column in their schema (I mean, the data you're
>> representing IS a boolean value in this case, right?);
>> --- No, we already tried this path. Unfortunately boolean is not portable
>> column type among DB vendors. So, we ruled out this option.
>
> You can't ship different DDL for different vendors?
>
> I thought part of the point of using a mapping layer like Hibernate was
> so Hibernate could handle the details of the mapping to different DBs
> (which might be done in different ways) without having to know about the
> different mappings in your application. So a Java boolean might be
> mapped to either a Postgresql boolean or an Oracle SMALLINT (since
> oracle doesn't have a real boolean type?), but your app code doesn't
> care about that detail - it's just dealing with a Java boolean either way.

That is indeed a significant part of Hibernate's feature set. If you
don't use it, you have to expect to have to maintain different DDL for
different databases yourself, or be prepared to customise Hibernate's
behaviour where necessary.

That said, I do think the OP ("stagirus") has a point. Pg's JDBC driver
is passing boolean literals through to Pg, which tries to insert them
into a smallint column and fails due to a type error. This is IMO overly
strict, and appears to be outside the JDBC spec too.

The underlying problem is that Pg won't permit implicit casts between
smallint and boolean. I see the rationale behind that, but it's a pain.
It's not a JDBC driver bug as such, because it's Pg not JDBC that's
rejecting the query, and JDBC is sending a boolean when asked to send a
boolean by Hibernate, as instructed by the OP's mappings. The JDBC
driver has no way to know the column is really a smallint, so a casting
error will occur.

It looks like "stagirus" may be right about the JDBC spec requiring
conversion between all numeric types, and considering "boolean" to be a
numeric special case. Page 217 of the JDBC4 spec (see
http://java.sun.com/products/jdbc/download.html#corespec40) contains a
table that suggests that setting a Java 'boolean' to a database
'smallint' (and vice versa) should be valid and permitted.

So I guess the question is: what do we do about it? Does the JDBC driver
have to find out what the database's expectations of parameter types are
and perform appropriate casts Java side? Or might it be possible to
offer a more permissive casting mode server-side that the JDBC driver
can set for the session?

--
Craig Ringer

Tech-related writing at http://soapyfrogs.blogspot.com/