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

[Sigh.  Moving to the PostgreSQL JDBC list; I accidentally picked up
the Sun JDBC address in a previous attempt to move this.  :-( ]

I wrote:
> stagirus  wrote:
>
>> I guess it would be set/getBoolean methods, right?
>
> Maybe.  I'd rather not guess at what it is you need though.
>
>> If you need, I will get the exception report from my developer.
>
> Please do.  That would allow us to make sure that if we try to
> help, we're working on the right part of the code.  It's so much
> harder to hit a target when shooting in the dark.  ;-)

Any progress on getting exception reports?

-Kevin

My developer sent me the following error report. It does not include stack
trace. But I can get it shortly.

 ERROR: column "deleted" is of type smallint but expression is of type
boolean

It appears that the queries generated by Hibernate contains boolean
expressions that are not being accepted as valid values by Postgresql DB.
Does anybody have experience with the following Hibernate's query
substitutions will work (Dean's suggestion)?
<property name="hibernate.query.substitutions">true 1, false 0</property>

It seems to be promising but I want to be sure it works before I recommend
to my team. They are already burnt with this boolean related issues in
Postgresql.

Ideally DB Engine/JDBC Drivers should be flexible(smart?) enough to
recognize integer values for boolean values. It is unfortunate that RDMBS
vendors nor ANSI SQL standardized boolean values. Am I making sense?

Thanks.
--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Re-BUGS-Mapping-Hibernate-boolean-to-smallint-Postgresql-tp2855367p2855475.html
Sent from the PostgreSQL - jdbc mailing list archive at Nabble.com.

stagirus <mamasa@stagirus.com> wrote:

> Ideally DB Engine/JDBC Drivers should be flexible(smart?) enough
> to recognize integer values for boolean values. It is unfortunate
> that RDMBS vendors nor ANSI SQL standardized boolean values. Am I
> making sense?

Sure.  I finally tracked down a current copy of the JDBC spec.  For
some reason, this seems to have become more difficult to find in
recent months; if anyone else is looking, I found it here:

http://jcp.org/aboutJava/communityprocess/final/jsr221/index.html

The relevant portions are TABLE B-5 and TABLE B-6.  What matters for
purposes of this discussion is that, when the driver is specifically
requested to convert between any of these Java classes:

String
java.math.BigDecimal
Boolean
Byte
Short
Integer
Long
Float
Double

and any of these database types:

TINYINT
SMALLINT
INTEGER
BIGINT
REAL
FLOAT
DOUBLE
DECIMAL
NUMERIC
BIT
BOOLEAN
CHAR
VARCHAR
LONGVARCHAR

it should attempt to do so.  Obviously that can sometimes fail due
to size or format issues.  But getBoolean against a SMALLINT is
clearly supposed to be attempted on demand.  I didn't happen across
the specification of how values map there, but in the absence of
evidence to the contrary I'd assume zero is false and anything else
is true;  Likewise, setObject using a Boolean against a SMALLINT
target is supposed to work.

What kinds of values do you see in the SMALLINT column when you set
a TRUE into a SMALLINT column?  1?  -1?

-Kevin

Kevin's response above seems to be very promising.
From your comments/observations, are you saying that there could be some
coding issues regarding these conversions between boolean and integers?

Reg your question of "What kinds of values do you see in the SMALLINT column
when you set a TRUE into a SMALLINT column?  1?  -1?"
: Nothing. Because it fails before any inserts or updates happen. We
discussed the error above.

Because I have not worked with the internals of Hibernate/JDBC drivers, I
cannot definitively state if Hibernate is calling setters and getters
(setBoolean) using prepared statements or directly executes INSERT/UPDATE
statements. Anything is possible.

Thanks.
--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Re-BUGS-Mapping-Hibernate-boolean-to-smallint-Postgresql-tp2855367p2855614.html
Sent from the PostgreSQL - jdbc mailing list archive at Nabble.com.

stagirus <mamasa@stagirus.com> wrote:

> Reg your question of "What kinds of values do you see in the
> SMALLINT column when you set a TRUE into a SMALLINT column?  1?
> -1?" : Nothing. Because it fails before any inserts or updates
> happen. We discussed the error above.

I meant with other database products.  Are they consistent?

-Kevin

Yes. It works without any issues on Oracle. We have not yet tested with other
databases except Postgresql. For the past 40 days, we have been trying to
get the product work with Postgresql. Ran into too many issues just related
to "Boolean" fields. We were hoping to deploy our Cloud System (SaaS) on
Postgreql -if we can get it to work.
--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Re-BUGS-Mapping-Hibernate-boolean-to-smallint-Postgresql-tp2855367p2855630.html
Sent from the PostgreSQL - jdbc mailing list archive at Nabble.com.

stagirus <mamasa@stagirus.com> wrote:

> From your comments/observations, are you saying that there could
> be some coding issues regarding these conversions between boolean
> and integers?

I'm saying that while the code is probably behaving as the person
who contributed it to PostgreSQL intended, it appears not to comply
with the current standard.  The PostgreSQL community generally makes
a pretty good effort toward conforming to standards.  If it's
relatively easy to make it conform to the standard, someone will
probably just do it.  (Heck, it could even be me.)  If it's a lot of
work, someone who needs the feature may have to take on the burden
of the change, either by offering a patch to implement it or paying
someone to do so.

> Because I have not worked with the internals of Hibernate/JDBC
> drivers, I cannot definitively state if Hibernate is calling
> setters and getters (setBoolean) using prepared statements or
> directly executes INSERT/UPDATE statements. Anything is possible.

Hence the request for the stack trace.  Most people have a pretty
full plate, and aren't eager to volunteer their time to work on
something which might not actually help anyone.

-Kevin

stagirus <mamasa@stagirus.com> wrote:

> It works without any issues on Oracle.

The question was -- when you set a SMALLINT column with a Boolean
which is TRUE, what value is stored in the database column.  You've
mentioned doing this on multiple databases; do they all set the same
value, and if so, what is it?

-Kevin

Point is well-taken. I will supply the stack trace. Thanks.

We are inserting 0 for false and 1 for true. Oracle and Hibernate has been
pretty happy so far. We just have to make Postgresql happy too.

I am impressed with your comment that Postgresql tries to comply with the
standards. If so do you have sufficient test cases for converting between
the data types you mentioned above. Any test cases for
Hibernate-JDBC-Postgresql scenarios? If I were (Postgres team), I would
include them to ensure the compatibility.

Thanks.
--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Re-BUGS-Mapping-Hibernate-boolean-to-smallint-Postgresql-tp2855367p2855656.html
Sent from the PostgreSQL - jdbc mailing list archive at Nabble.com.

stagirus <mamasa@stagirus.com> wrote:

> If I were (Postgres team), I would include them to ensure the
> compatibility.

Feel free to contribute a patch.  The "PostgreSQL Team" is a pretty
loose and varied lot.  I've read that the changes for the 9.0
release were contributed by over 100 different people, although some
people are obviously a lot more active than others.  One great thing
about free, open source software is that "you can scratch your own
itch." Many contributions come from people who use the product in a
way others don't, and run into an issue which isn't bothering anyone
else -- much like you.

Welcome to the team!  ;-)

-Kevin

Kevin Grittner wrote:

> The relevant portions are TABLE B-5 and TABLE B-6. [...]

> But getBoolean against a SMALLINT is
> clearly supposed to be attempted on demand.  I didn't happen across
> the specification of how values map there, but in the absence of
> evidence to the contrary I'd assume zero is false and anything else
> is true;  Likewise, setObject using a Boolean against a SMALLINT
> target is supposed to work.

If I read the original report right, it's about setBoolean(), not
getBoolean() (the error is a type mismatch while executing the query,
not a problem processing the results)

Note that table B-5 is specifically about "What combinations of
java.sql.Types value and actual instance type are valid for
setObject()?". So, for example, if you called "setObject(column,
Boolean.TRUE, Types.SMALLINT)", that should work - you're explicitly
asking the driver to represent a Boolean as a SMALLINT. (As noted, the
mapping's not standard, so you may not get exactly the same as other
databases).

However, if you just call setBoolean() in a context where the database
is expecting a SMALLINT, then it's not going to work - how does the
driver know, in the general case, that it should apply a conversion
there? (Consider fun cases where the parameter isn't just directly
mapped to a column, it's part of an expression, etc).

The driver could in theory ask the backend to always infer a type for
positional parameters, then apply its own conversions, but this requires
an extra round trip per query and would actually break other cases where
valid queries with correct types would suddenly start complaining about
being unable to infer a type. And anyway, it's not the right place for
it - the backend already has a large set of type conversion
functionality, why are we suddenly trying to duplicate that in the driver?

You can think of setBoolean(column, boolValue) as essentially the same
as setObject(column, Boolean.valueOf(boolValue), Types.BOOLEAN) - if you
look at it that way, there's obviously no conversion required, because
the type you passed is already a BOOLEAN as you requested ..

I would suggest that the OP either:

* use a real boolean column in their schema (I mean, the data you're
representing IS a boolean value in this case, right?); or
* use setObject(column, value, Types.SMALLINT) instead of the implied
setBoolean() call when setting the column (this may be tricky to do
since you have a layer between you and the driver, but that's not really
the driver's fault..); or
* add a suitable implicit cast on the backend side from boolean ->
smallint (not sure if this works, haven't looked at the details)

-O

On 28/09/10 07:01, Samuel Gendler wrote:

> I would actually suggest running your problem past the hibernate
> community.  It seems unlikely that you're the first team to encounter
> this problem.  How do you have the property mapped in the hibernate
> mapping?  Is it possible that explicitly setting the hibernate type of
> the property in the mapping will solve your problem?

There's way too much guesswork happening here.

OP ("stagirus"), please supply *code*, *stack traces* and *exact error
messages*.

Please show at least one mapping that you are having problems with - the
Java class with annotations, or if you're using XML mapping the
associated XML configuration. Show the stack trace that results from the
failure, and what appears in the PostgreSQL error logs.

Even better, produce buildable source code that demonstrates your
problem without depending on other parts of your application. If you
don't use Hibernate's DDL generation to populate your database, include
a SQL script containing you would use to populate the database too. This
should be a twenty minute job for any competent developer on your team.

That way, instead of lots of hand-waving and guesswork, it'll be
possible to discuss concrete problems and possible solutions.

You should also specify:

- Your Hibernate version
- How you're mapping your entity classes
- Your PostgreSQL JDBC driver versions
- What dialect, if any, is configured for Hibernate
- Your JDK version
- Which version of PostgreSQL you are targeting

See:
  http://wiki.postgresql.org/wiki/Guide_to_reporting_problems


Personally, I suspect you're overriding Hibernate's DDL generation but
not its type mappings, so the PostgreSQL dialect can't do it's job. But
without actual code, it's nigh-on impossible to tell.

--
Craig Ringer

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

I got sick of the guesswork and hand-waving, so here's a test case
demonstrating that Hibernate works just fine with PostgreSQL when
mapping booleans as shorts or as booleans.

Executable:

http://www.postnewspapers.com.au/~craig/weblinks/pghiberboolean-1.0-SNAPSHOT-jar-with-dependencies.jar

Sources including build README and Maven pom:

http://www.postnewspapers.com.au/~craig/weblinks/pghiberboolean-1.0-SNAPSHOT-project.zip

Run the test case as:

java -jar target/pghiberboolean-1.0-SNAPSHOT-jar-with-dependencies.jar \
      jdbc:postgresql://localhost/DBNAME DBUSER DBPASSWORD

(all on one line, replace DBNAME, DBUSER, DBPASSWORD with settings for a
test database on your machine).



Now: please show your code so we can see what's actualling going wrong,
as this case demonstrates that Hibernate gets it right when left to its
own devices.

Are you using your own DDL to define your tables, so you're mapping
types as shorts in hibernate but using booleans in the database? Because
that's not how Hibernate expects it to be, it expects a short if you
tell it you're mapping a short.

I'm going to guess that what you're doing is equivalent to running the
attached test program to create the tables, then connecting to the
database and running:

  ALTER TABLE test_entity DROP COLUMN not_boolean;
  ALTER TABLE test_entity ADD COLUMN not_boolean boolean;

thus changing the "shortint" typed column to "boolean". This will cause
the test program to fail with:


SEVERE: ERROR: column "not_boolean" is of type boolean but expression is
of type smallint
  Hint: You will need to rewrite or cast the expression.
  Position: 67



If this is the case, you can:

- Let hibernate generate the DDL in a database-independent manner;

- Fix your SQL DDL so it declares booleans where Hibernate expects
  booleans;

- Fix your mappings so they're Boolean in java if they're boolean in
  the database, and let Hibernate take care of mapping them to shorts
  when the database doesn't understand booleans natively;

- Write a UserType to hack around this by using the short-typed get and
  set methods in JDBC rather than the boolean ones, overriding
  Hibernate's defaults.

Basically, I think you're throwing a spanner in the works by trying to
override Hibernate, but only doing it half-way.



--
Craig Ringer

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

Thank you for the information provided above. It appears that we are all not
on the same page regarding the issue we are discussing. My apologies for my
share of communication gaps. Let me try again.

1. Create the table (DDL): CREATE TABLE st_employee (e_name varchar(30),
active SMALLINT);
2. Define a POJO: class Employee{ name String; active boolean};
3. Hibernate mapping:
    <class name="Employee" table="st_employee">
        <property name="name" type="string">
            <column name="e_name" />
        </property>
        <property name="active" type="boolean">
            <column name="active" />
        </property>
4. Use Hibernate templates (DAO) to store and read the DB.

Observation: this works for Oracle and other DBs. Postgresql (9.0 and latest
JDBC drivers) complain about boolean cannot be cast to SMALLINT. I would
expect Postgresql work like other RDBMS like Oracle.

Thanks.




--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Re-BUGS-Mapping-Hibernate-boolean-to-smallint-Postgresql-tp2855367p2865634.html
Sent from the PostgreSQL - jdbc mailing list archive at Nabble.com.

stagirus <mamasa@stagirus.com> wrote:

> Observation: this works for Oracle and other DBs.

That doesn't carry that much weight in this community, unless it is
a barrier to a lot of people converting.  If you can demonstrate
that we're out of compliance with a standard, or that it is a
problem in some common usage, that would carry some weight.  So far
you haven't provided evidence of anything.  A complete stranger took
the time to post an example of how Hibernate works fine doing what
you appeared to him to be describing.  Code and instructions how to
run it and everything.  The ball is in your court to actually show
enough information about the failure case for us to understand what
you're doing and where it fails.

If you haven't already done so, you should probably read this page:

http://wiki.postgresql.org/wiki/Guide_to_reporting_problems

-Kevin

Kevin:
This discussion is getting overly political, legal and religious. I have no
interest in any of these.

You have already noted above that boolean values and integer values are
interchangeable as per the JDBC 4.0 Spec. Why are you now asking me to prove
it? (I am not married to Oracle either!)

I posted a common sense question. I requested for an advice why Postgresql
is not working in a seemingly obvious scenario. My hope was that Postgresql
community appreciates this sort of feedback.

Otherwise, when we can dedicate resources, we will troubleshoot these issues
and provide a patch, if that helps. Until then, fortunately life is full of
options.

Please advise so that we can put this discussion to rest.

Thanks.


--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Re-BUGS-Mapping-Hibernate-boolean-to-smallint-Postgresql-tp2855367p2916249.html
Sent from the PostgreSQL - jdbc mailing list archive at Nabble.com.

>>Are you using your own DDL to define your tables, so you're mapping
types as shorts in hibernate but using booleans in the database? Because
that's not how Hibernate expects it to be, it expects a short if you
tell it you're mapping a short.<<<

Craig: the scenario you have described is different from the one we are
facing.

1. Yes, we define and manage our own DDL, not generate from Hibernate. We
prefer to keep it that way.
2. We define the column in the database table as SMALLINT not boolean.
Please refer to my post below.
3. As described below, in the Hibernate we map Java boolean to DB SMALLINT.
Below I showed the mapping.
Obviously we assume java boolean values and DB integer values (columns) are
interchangeable. Poor "boolean" values have no real shape among DB vendors
and ANSI SQL.

I hope this makes sense.

Thanks.


--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Re-BUGS-Mapping-Hibernate-boolean-to-smallint-Postgresql-tp2855367p2918544.html
Sent from the PostgreSQL - jdbc mailing list archive at Nabble.com.

stagirus wrote:

> You have already noted above that boolean values and integer values are
> interchangeable as per the JDBC 4.0 Spec.

They're not actually - see my post. You can ask the driver to convert
between types for you, but Hibernate does not appear to be doing that
(presumably because you've told it that the column is a boolean, which
doesn't match your DDL!)

To repeat my suggestions:

* use a real boolean column in their schema (I mean, the data you're
representing IS a boolean value in this case, right?); or
* use setObject(column, value, Types.SMALLINT) instead of the implied
setBoolean() call when setting the column (this may be tricky to do
since you have a layer between you and the driver, but that's not really
the driver's fault..); or
* add a suitable implicit cast on the backend side from boolean ->
smallint (not sure if this works, haven't looked at the details)

Do any of these work for you? The first suggestion seems to be the
simplest to me, since you really are storing a boolean, not a smallint,
and you have control over the DDL.

-O

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.

or
* use setObject(column, value, Types.SMALLINT) instead of the implied
setBoolean() call when setting the column (this may be tricky to do
since you have a layer between you and the driver, but that's not really
the driver's fault..); or
---I am not sure if our application developers have this option. Hibernate
handles this for us. I am not pointing fingers. I am curious how other DBs
or JDBC drivers handle it so transparently.

* add a suitable implicit cast on the backend side from boolean ->
smallint (not sure if this works, haven't looked at the details)
-- we will look into it.

Do any of these work for you? The first suggestion seems to be the
simplest to me, since you really are storing a boolean, not a smallint,
and you have control over the DDL.
--Not yet, please.

--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Re-BUGS-Mapping-Hibernate-boolean-to-smallint-Postgresql-tp2855367p3005405.html
Sent from the PostgreSQL - jdbc mailing list archive at Nabble.com.

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.

-O

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/

Craig:
Finally, you made my day SIR! We reached to the bottom of the issue. (My
original post tried to convey this very fact.) Anyway, thank you for
referring to the JDBC specs and reaching this very powerful conclusion. You
sound very grown up person! My cudos!

I cannot wait to hear your group's consensus on how you all like to resolve
this issue. As far as how we resolve this issue or what decisions stagirus
makes should our internal headache. We are competent in that side of the
house. When we do need other help from you all, we will surely contact you.

Regarding other comments on Cloud. No, we are not looking to use Postgresql
on the Cloud. Postgresql DB will be on our own servers/hardware locally.
Just our software (SaaS) like Salesforce.com would be on the Cloud. Sorry
for the confusion.

Best wishes.


--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Re-BUGS-Mapping-Hibernate-boolean-to-smallint-Postgresql-tp2855367p3047370.html
Sent from the PostgreSQL - jdbc mailing list archive at Nabble.com.

On Thu, Sep 30, 2010 at 1:31 PM, stagirus <mamasa@stagirus.com> wrote:
>
> Craig:
> Finally, you made my day SIR! We reached to the bottom of the issue. (My
> original post tried to convey this very fact.) Anyway, thank you for
> referring to the JDBC specs and reaching this very powerful conclusion. You
> sound very grown up person! My cudos!

Actually that table B-5 refers to setObject conversions, table B-6
refers to resultset gettter methods. There is no explicit conversion
specified for settters. I think Oliver already pointed this out in a
previous post.

Dave

Craig Ringer wrote:

> 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.

This is not correct. I covered this in a previous mail. This table
(table B-5) is specifically about the conversions that setObject() will
do on require. See page 192 at the start of appendix B:

> TABLE B-5 Conversions by setObject and setNull from Java Object Types to JDBC
> Types
> This table shows which JDBC types may be specified as the target JDBC type to the
> methods PreparedStatement.setObject, PreparedStatement.setNull, RowSet.setNull
> and RowSet.setObject.

Section 13.2.2.1, page 100:

> The data type specified in a PreparedStatement setter method is a data type in
> the Java programming language. The JDBC driver is responsible for mapping this to
> the corresponding JDBC type (one of the SQL types defined in java.sql.Types) so
> that it is the appropriate type to be sent to the data source. The default mapping is
> specified in Appendix B TABLE B-2.

(Table B-2 specifies that a Java boolean is mapped to JDBC datatypes BIT
or BOOLEAN)

And section 13.2.2.3, page 101:

> The method setObject can be used to convert an object in the Java programming
> language to a JDBC type.
> The conversion is explicit when setObject is passed a Java Object and a JDBC
> data type. [...]
> If setObject is called without a type parameter, the Java Object is implicitly
> mapped using the default mapping for that object type. [...]
> The default mapping is described in Appendix B TABLE B-4

(Table B-4 specifies that a Java boolean is mapped to JDBC datatypes BIT
or BOOLEAN)

So, in summary: If you explicitly ask for a Java boolean to be mapped to
a JDBC SMALLINT, by passing Types.SMALLINT to setObject(), then that
should be supported and the driver will do the conversion for you; if
that doesn't work, that's a bug! However, if you don't specify a type
when calling setObject() or setBoolean(), it is mapped to BOOLEAN. If
BOOLEAN is then not a suitable type for the actual query you're trying
to run, then you'll get a SQL error, but that's not the driver's fault,
it just did what you asked!

So the driver is working just fine here - the problem is that the
application (via Hibernate) is expecting that a BOOLEAN value should be
implicitly cast to a SMALLINT, which is really nonportable behaviour
which only happens to work with Oracle because Oracle doesn't have a SQL
boolean type in the first place and so the app is using SMALLINT in its
DDL ..

-O

Oliver should be a lawyer by nature! In my opinion Software is about solving
problems! I carefully read many of your writings on this/related topics.

I respectfully disagree with your interpretations and conclusions. You seem
to be more interested in proving somebody is WRONG rather than being open to
truly understand the issues at hand and discovering helpful solutions.
Anybody can prove anything is wrong. For one to understand and admit the
limitations/weaknesses requires one to be very brave and mature.

Anyway, I got to go. Take care.

Best wishes.




--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Re-BUGS-Mapping-Hibernate-boolean-to-smallint-Postgresql-tp2855367p3047671.html
Sent from the PostgreSQL - jdbc mailing list archive at Nabble.com.

stagirus wrote:
> Oliver should be a lawyer by nature! In my opinion Software is about solving
> problems! I carefully read many of your writings on this/related topics.
>
> I respectfully disagree with your interpretations and conclusions. You seem
> to be more interested in proving somebody is WRONG rather than being open to
> truly understand the issues at hand and discovering helpful solutions.
> Anybody can prove anything is wrong. For one to understand and admit the
> limitations/weaknesses requires one to be very brave and mature.

Well - I've tried to explain why the driver is behaving as it is, why I
believe that is the correct behavior according to the spec, and why it's
difficult for the driver to behave in any other way. I've even suggested
ways you can modify your software to do what you want. I'm not sure what
more I can do here.

Since it seems that you're not prepared to change your application, and
you're not prepared to listen to feedback that disagrees with you, I
think your only real option is to modify the driver to behave as you
think it should, and post that patch for review. The source is available
- go and prove that you're right!

(And I'm certainly not getting into passive-aggressive games about how I
am a terrible person because I disagreed with you)

-O

Oliver: Thank you for your generous response. The options you suggested were
not yet viable. Disagreeing is part of human nature. No worries there.

But if you review Craig's remarks carefully, this issue is likely outside
the scope of JDBC drivers. It is more to do with the Postgresql DB Engine
that is not happy to cast or convert boolean values to SMALLINT columns.

Craig.. is my interpretation of your comments right?

Thanks


--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Re-BUGS-Mapping-Hibernate-boolean-to-smallint-Postgresql-tp2855367p3047721.html
Sent from the PostgreSQL - jdbc mailing list archive at Nabble.com.

stagirus wrote:

> But if you review Craig's remarks carefully, this issue is likely outside
> the scope of JDBC drivers. It is more to do with the Postgresql DB Engine
> that is not happy to cast or convert boolean values to SMALLINT columns.

I refer you to my previous emails that discuss exactly this, and a
possible workaround.

(Nice backpedalling)

-O

On 01/10/10 10:50, stagirus wrote:
> Oliver: Thank you for your generous response. The options you suggested were
> not yet viable. Disagreeing is part of human nature. No worries there.
>
> But if you review Craig's remarks carefully, this issue is likely outside
> the scope of JDBC drivers. It is more to do with the Postgresql DB Engine
> that is not happy to cast or convert boolean values to SMALLINT columns.
>
> Craig.. is my interpretation of your comments right?
>
>

This could be an interesting related factor (using 9.0 here):

test=# create table smallinttest(val smallint);
CREATE TABLE

test=# create table booltest(val bool);
CREATE TABLE

test=# insert into booltest values (1);
ERROR:  column "val" is of type boolean but expression is of type integer
LINE 1: insert into booltest values (1);

test=# insert into booltest values ((1::smallint)::bool);
ERROR:  cannot cast type smallint to boolean
LINE 1: insert into booltest values ((1::smallint)::bool);
                                                   ^
                                   ^
HINT:  You will need to rewrite or cast the expression.
test=# insert into booltest values (1::bool);
INSERT 0 1

test=# insert into smallinttest values (true);
ERROR:  column "val" is of type smallint but expression is of type boolean
LINE 1: insert into smallinttest values (true);
                                          ^
HINT:  You will need to rewrite or cast the expression.
test=# insert into smallinttest values (true::smallint);
ERROR:  cannot cast type boolean to smallint
LINE 1: insert into smallinttest values (true::smallint);
                                              ^
test=# insert into smallinttest values (true::int);
INSERT 0 1


So looks like we are missing type casts BOOLEAN <-> SMALLINT, given that
we can happily cast to and from INTEGER.

Before getting too excited, note that the server is clearly not doing
automated casts under any circumstances, I had to coerce it - so might
not help you even if we added such casts.

Cheers

Mark

Yes. we will try the following two options.
1. Operator overloading - add a suitable implicit cast on the backend side
from boolean -> smallint (not sure if this works, haven't looked at the
details)
2. Hibernate query subs: <property name="hibernate.query.substitutions">true
1, false 0</property>

I already indicated earlier these two are viable options for us.  It might
take sometime before I can post the update. Thank you for your help. See
you.

Best wishes.

--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Re-BUGS-Mapping-Hibernate-boolean-to-smallint-Postgresql-tp2855367p3047745.html
Sent from the PostgreSQL - jdbc mailing list archive at Nabble.com.

On 10/01/2010 04:38 AM, Oliver Jowett wrote:

> This is not correct. I covered this in a previous mail.

[snip]

> So, in summary: If you explicitly ask for a Java boolean to be mapped to
> a JDBC SMALLINT, by passing Types.SMALLINT to setObject(), then that
> should be supported and the driver will do the conversion for you; if
> that doesn't work, that's a bug! However, if you don't specify a type
> when calling setObject() or setBoolean(), it is mapped to BOOLEAN. If
> BOOLEAN is then not a suitable type for the actual query you're trying
> to run, then you'll get a SQL error, but that's not the driver's fault,
> it just did what you asked!

Aah, thanks for the clarification. I didn't notice your earlier message
on the topic.

Given that, it's pretty clear that the OP has to fix their app to:

- Trust Hibernate to generate their DDL, taking care of their db
portability issues;

- Use per-database DDL with the data types Hibernate expects;

- Use a short in Java if they want a short in the database, and convert
it in their accessor methods; or

- Use a Hibernate UserType to call the appropriate set() and get() JDBC
methods, overriding the default type-based options in the dialect.


--
Craig Ringer

On 10/01/2010 05:50 AM, stagirus wrote:
>
> Oliver: Thank you for your generous response. The options you suggested were
> not yet viable. Disagreeing is part of human nature. No worries there.
>
> But if you review Craig's remarks carefully, this issue is likely outside
> the scope of JDBC drivers. It is more to do with the Postgresql DB Engine
> that is not happy to cast or convert boolean values to SMALLINT columns.
>
> Craig.. is my interpretation of your comments right?

Yes, but that's not inherently a problem. It'd only be an issue if the
JDBC driver was required to ensure that those conversions occurred
correctly. As it seems it isn't require to do so, the server isn't
obliged to handle these conversions and the JDBC driver isn't obliged to
work around the server's restriction.

I have no strong opinion on whether Pg should accept 'short' inputs for
'boolean' columns. I see good arguments in both directions. All I was
saying earlier was that my casual reading of the JDBC spec (incorrectly,
it turns out) said that jdbc drivers had to support this.

Since the JDBC spec doesn't require drivers to support this, you should
stop relying on behaviour that isn't guaranteed by the spec.

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
database dialect and automatic type selection features (runtime SQL),
but not the other half (DDL), so it's breaking. If you'll let Hibernate
do it's job and produce your DDL, or stop lying to Hibernate about how
you've defined the database by mapping the smallint fields as shorts,
your problem will go away.

How hard is this?

@Entity
class Something {

     @Basic
     @Column
     private boolean fakeBoolean;

     public boolean getFakeBoolean() {
         return fakeBoolean != 0;
     }

     public void setFakeBoolean(boolean fakeBoolean) {
         this.fakeBoolean = (short)(fakeBoolean ? 1 : 0);
     }

}



?

Note, by the way, that Java won't let you implicitly cast between
boolean and short. Just like Pg.



It's also easy to use a UserType to get what you want, like the one I
wrote recently because PostgreSQL won't implicitly cast 'text' to 'xml':

http://wiki.postgresql.org/wiki/Hibernate_XML_Type



For what it's worth, I think this is partly a Hibernate/JPA issue. At
least in JPA, there's no way to specify the SQLTYPE associated with a
given column, which is mildly annoying, as it'd let the JDBC driver
handle this issue.

--
Craig Ringer

>>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. Per our architecture, Hibernate is
simply an ORM tool. Period. Not any sort of database administration tool.
There are better tools for that job.

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.

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!

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? I like the way other RDBMS have resolved this difference.

Why wouldn't you (Postgresql)?

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). PLEASE
LISTEN TO THE CRIES OF THE WORLD!





--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Re-BUGS-Mapping-Hibernate-boolean-to-smallint-Postgresql-tp2855367p3073409.html
Sent from the PostgreSQL - jdbc mailing list archive at Nabble.com.

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/

Of course my very first post on this topic indicated that we were looking for
a solution for a problem with booleans. It took this long for all of us to
reach a conclusion that the business of type mappings are handled by
Hibernate. The following dialect class/API clearly states it.
http://ajava.org/online/hibernate3api/org/hibernate/dialect/PostgreSQLDialect.html#toBooleanValueString(boolean)

Oliver did refer to Hibernate Dialect. Not very clear though. So it is
becoming evident that the types can be managed within Hibernate API or
configurations. Overriding just the above API might suffice. I wish you guys
have pointed me to this earlier.

I am good. thanks.




--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Re-BUGS-Mapping-Hibernate-boolean-to-smallint-Postgresql-tp2855367p3073605.html
Sent from the PostgreSQL - jdbc mailing list archive at Nabble.com.

Thank you all. I have heard and looked at all the options including the
UserType. This indeed has been an insightful discussion. I enjoyed speaking
to you. It is my honor to getting to know you all.

Now I am in a much better position to advise my architects and developers on
this issue. I would be happy to update you with the solution we would
implement and our rationale, if that helps. I am sure we will talk in the
near future.

Thank you very much.

--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Re-BUGS-Mapping-Hibernate-boolean-to-smallint-Postgresql-tp2855367p3074149.html
Sent from the PostgreSQL - jdbc mailing list archive at Nabble.com.

Good morning. Please find the attached log file regarding our problem. The
main issue is with "DELETED" column as SMALLINT. Postgres is not accepting
'0' in the values(..) for DELETED column. (This was the original issue that
I posted.)

http://postgresql.1045698.n5.nabble.com/file/n3199236/PostgresForum.log
PostgresForum.log

--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Re-BUGS-Mapping-Hibernate-boolean-to-smallint-Postgresql-tp2855367p3199236.html
Sent from the PostgreSQL - jdbc mailing list archive at Nabble.com.

stagirus wrote:
> Good morning. Please find the attached log file regarding our problem. The
> main issue is with "DELETED" column as SMALLINT. Postgres is not accepting
> '0' in the values(..) for DELETED column. (This was the original issue that
> I posted.)
>
> http://postgresql.1045698.n5.nabble.com/file/n3199236/PostgresForum.log
> PostgresForum.log

> ERROR 04-10 15:26:32,937 (JDBCExceptionReporter.java:logExceptions:101)  -Batch entry 0 /* insert
com.stagirus.subsystem.suppliermaster.domain.Provider*/ insert into ST_PROVIDER (GEOGRAPHY_ID, ADDRESS_ID,
PROVIDER_NAME,DESCRIPTION, WEBSITE, EMAIL, PHONE, FAXNO, PRIMARY_ORG_ID, DELETED, CREATED_ON, CREATED_BY,
LAST_MODIFIED_ON,LAST_MODIFIED_BY, PROVIDER_ID) values ('1', '2', 'supplier1', NULL, NULL, NULL, '1245874123', '', '1',
'0','2010-10-04 15:26:32.734000 +05:30:00', '1', '2010-10-04 15:26:32.734000 +05:30:00', '1', '1') was aborted.  Call
getNextExceptionto see the cause. 

Not shown in your logs is the type which Hibernate is binding the
parameters as. I expect that the '0' is actually bound with Oid.BOOL
because Hibernate is using setBoolean() or setObject(...,Types.BOOLEAN).

For example, the implementation of setBoolean does this:

>     public void setBoolean(int parameterIndex, boolean x) throws SQLException
>     {
>         checkClosed();
>         bindString(parameterIndex, x ? "1" : "0", Oid.BOOL);
>     }

which means that the value parameter is indeed "0" or "1" - but its type
is BOOL. (I'm guessing that the query logged by Hibernate above is the
result of PreparedStatement.toString(), which just substitutes the raw
parameter values into the query string without respect for their bound
types)

You could check this by setting loglevel=2 and looking at the messages
sent to the backend, or by looking at a packet capture with wireshark
(IIRC, it has a decent protocol analyzer for the postgresql FE/BE protocol)

Oliver

I found this post in Hibernate's forums:

https://forum.hibernate.org/viewtopic.php?f=1&t=944165&p=2247802&hilit=postgres+boolean+smallint#p2247802

In summary, Hibernate will handle converting a char(1) in the database to/from boolean in java, and from that post, it
worksin DB2 and PostgreSQL. 

So here's another option. Change your smallint in the database to a char(1). The only thing it affects is the DDL for
eachdatabase, any existing data you have in the database, and the Hibernate mapping files. Your java code remains
untouched.

-- Jeff Hubbach

Jeff:
Your research and the recommendation is viable. The conclusion was Java
boolean and DB char(1) are interoperable. By the same token, why wasn't Java
boolean and DB smallint interoperable? Is it the Hibernate issue (Dialect?),
JDBC driver issue, or the DB Engine issue? In our case, it works with Oracle
DB indicates to me that we are missing something. Or I am just missing
something.

I am afraid the boolean to char(1) might run into similar issues with other
databases. Just because it worked with DB2 and Postgres does not mean it
would work with other DBs. We are already noticing such incompatibilities
though our solution worked with Oracle.

Thanks.

--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Re-BUGS-Mapping-Hibernate-boolean-to-smallint-Postgresql-tp2855367p3200613.html
Sent from the PostgreSQL - jdbc mailing list archive at Nabble.com.

>>Not shown in your logs is the type which Hibernate is binding the
>>parameters as. I expect that the '0' is actually bound with Oid.BOOL
>>because Hibernate is using setBoolean() or setObject(...,Types.BOOLEAN)...

Oliver - your observations could possibly be right.

One simple question though. Is the reported exception generated by the
Postgresql JDBC driver or Postgresql DB Engine?

Thanks.

--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Re-BUGS-Mapping-Hibernate-boolean-to-smallint-Postgresql-tp2855367p3200618.html
Sent from the PostgreSQL - jdbc mailing list archive at Nabble.com.

stagirus wrote:

> One simple question though. Is the reported exception generated by the
> Postgresql JDBC driver or Postgresql DB Engine?

The BatchUpdateException in this case is built by the driver using error
details reported by the backend.

Oliver

stagirus,

How did you solve this issue ? Did you use UserType ?

Could you please send the details ?


--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Re-BUGS-Mapping-Hibernate-boolean-to-smallint-Postgresql-tp2855367p5582702.html
Sent from the PostgreSQL - jdbc mailing list archive at Nabble.com.

Dnia środa, 21 marca 2012 o 05:07:43 c_h_thakar napisał(a):
> stagirus,
>
> How did you solve this issue ? Did you use UserType ?
>
> Could you please send the details ?
>
>
> --
> View this message in context:
> http://postgresql.1045698.n5.nabble.com/Re-BUGS-Mapping-Hibernate-boolean-t
> o-smallint-Postgresql-tp2855367p5582702.html Sent from the PostgreSQL - jdbc
> mailing list archive at Nabble.com.
I may give you alternative solution, I use it for different situations, but
currently I write it "by hand". It's JPA aware, not bound to hibernate. Try
this:
public class entity {
    private int intAsBool;
    private boolean boolValue;

    @Column()
    protected int getIntAsBool() {...}
    protected void setIntAsBool(int val) {...}

    @Transient
    public boolean getBoolValue() {...}
    public boolean setBoolValue() {...}

    @PostLoad
    protected void postLoad() {
        setBoolValue(getIntAsBool() == 1);
    }

    @PrePersist
    @PreUpdate
    protected void preStore() {
        setIntAsBool(getBoolValue() ? 1 : 0);
    }
}

Bear in mind you need to choose only one way to mark JPA annotations field or
ancestors, 2nd one is better for many reasons.

Hope this help. I use this construct to store and map T/N char(1) values to
boolean.

Regards,
Radosław Smogura
http://softperience.eu/