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

On 2/10/2010 6:17 AM, stagirus wrote:
>
>>> I still don't understand why you won't fix your application code to use
> Hibernate how it's designed to be used. You're using half of Hibernate's..
>
> If it was a simple thing on our application side, why would I be wasting my
> time and your time this much!
>
> Our product is developed based on certain architectural principles. It was
> all developed for the past four years. Unfortunately, your suggestions are
> too invasive and not even feasible.

OK, then let's add another:

- Subclass the PostgreSQL dialect for Hibernate to map boolean to
tinyint instead of to boolean

It's like using a UserType, but applies globally across your application.

Hibernate (specifically Hibernate's PostgreSQL dialect) knows about
PostgreSQL's boolean type, and assumes you want to use it. If you don't,
you have to tell Hibernate so.

If the Hibernate designers agreeed with you about booleans, they
wouldn't have mapped java boolean to postgresql boolean in Hibernate.
Because it's hibernate that's doing it, via
org.hibernate.dialect.PostgreSQLDialect. Go argue with them if you want
them to change how it works to suit your code.

> Your argument of boolean is not same as short in Java. Agreed. Do you
> believe programing languages are same as RDBMS engines? I do not think so.

No, I'm merely making the point that implicitly casting between boolean
and integer isn't universally supported and isn't considered so obvious
as to be a no-brainer.

> We all know there is no universal definition or support for boolean columns
> (storage class as it is called) in RDBMS except in Postgresql. Check ANSI
> SQL. Sorry no boolean!

AFAIK, boolean was added in SQL:1999 . More than 10 years ago. It's also
fully supported by JDBC. Note, for example, the presence of
java.sql.Types.BOOLEAN .

> So the real question to the JDBC experts as you all - how is this difference
> between boolean in programming language(Java) and RDBMS/JDBC should be
> resolved?

There is no difference between boolean in Java and boolean in
PostgreSQL. Both have distinct true/false values, and neither are
convertable to/from integer. Nor is there at the JDBC level, as
java.sql.Types.BOOLEAN maps perfectly to Java's boolean.

The difference you are experiencing is because you're *lying* to the
JDBC driver, via Hibernate, while you enforce your fixed view of how
relational databases should store boolean values. By mapping boolean
fields as boolean, you're effectively calling:

s.setObject(n, false, java.sql.Types.BOOLEAN);

which tells the JDBC driver the column type is boolean. It isn't,
because while you've defined it as a boolean in Java and mapped it that
way with Hibernate, you've defined it in the database as a smallint.

Hibernate knows about PostgreSQL's boolean type, and will use it for
boolean java fields, because it expects the database field to be defined
as boolean. If you don't want Hibernate to behave that way, you need to
change how it behaves so it instead sends the SQL type you want:

s.setObject(n, false, java.sql.Types.SMALLINT);

You can do this with a UserType, by subclassing
org.hibernate.dialect.PostgreSQLDialect, or by writing your own dialect
subclass.

I know you want PostgreSQL to implicitly cast between boolean and
smallint, so this works transparently. PostgreSQL doesn't. Personally, I
have no strong opinon on whether it should or not am not in a position
to change how it works anyway. So, unless you're going to develop a
patch to PostgreSQL that enables that and prove that it won't break
existing code, you should probably be focusing on how to make your code
work how you want with PostgreSQL.

> I like the way other RDBMS have resolved this difference.
>
> Why wouldn't you (Postgresql)?

Because nobody seems to have wanted it/needed it until now? It used to
be handled via implicit casts to text, and now that those are gone you
seem to be the first one who misses the lack of the cast.

Because there are also solid reasons NOT to permit implicit casts from
boolean to integer, catching unintended errors being only one of them?

If you like you can always add a cast from boolean to smallint yourself.
See "CREATE CAST" in the documentation and the contents of the
pg_catalog.pg_cast table.

> If you observe the discussions on this topic, unfortunately Postgresql has
> created the biggest incompatibility for boolean fields. You know the reasons
> - you treat boolean as chars unlike everybody else (0, 1 or -1).

Nonsense. PostgreSQL booleans are not char. The literal representation
looks like a char, but so do dates, NUMERIC literals, and all sorts of
other things - and you're not telling me PostgreSQL represents dates as
char are you?

I think you need to understand how your tools work - PostgreSQL, PgJDBC,
Hibernate, and your mapping code. Because right now, I don't think
you're properly aware of how Hibernate maps types and how its dialects
influence its behaviour.

Anyway, I'm done wasting my time here; I've spent about four hours of my
own time trying to be patient and help, and that's four hours too many.
If you're not satisfied with the answers, it's time to pay a
professional consultant to help you, 'cos I'm done.

--
Craig Ringer

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