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

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

From: Craig Ringer <craig(at)postnewspapers(dot)com(dot)au>
To: Oliver Jowett <oliver(at)opencloud(dot)com>
Cc: stagirus <mamasa(at)stagirus(dot)com>, pgsql-jdbc(at)postgresql(dot)org
Subject: Re: [BUGS] Mapping Hibernate boolean to smallint(Postgresql)
Date: 2010-09-30 14:08:27
Message-ID: 4CA499DB.8000807@postnewspapers.com.au (view raw or flat)
Thread:
Lists: pgsql-bugspgsql-jdbc
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/

In response to

Responses

pgsql-bugs by date

Next:From: Alan DeKokDate: 2010-09-30 16:07:51
Subject: BUG #5687: RADIUS Authentication issues
Previous:From: Craig RingerDate: 2010-09-30 13:39:21
Subject: Re: BUG #5683: Service path incorrectly configured

pgsql-jdbc by date

Next:From: Craig RingerDate: 2010-09-30 14:26:44
Subject: Re: [BUGS] Mapping Hibernate boolean to smallint(Postgresql)
Previous:From: Mark KirkwoodDate: 2010-09-29 21:30:51
Subject: Re: [BUGS] Mapping Hibernate boolean to smallint(Postgresql)

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