Hi all, I've reported the following bug to the hsqldb community. According to the answer they gave me I should understand that the problem is at hibernate side, because the sql generated by the hsql dialect isn't strictly ansi conformant. The same example worked fine using Postgres as my dbms. What do you think? Should I report an issue about this?

Best regards,
Carlos

Carlos
---------

Hi all,

I found this bug while playing with hibernate
table-per-concrete-class-with-union mapping strategy. To put you in
context, a Person has a Document, which may be DNI or LU. There is one
shared property for documents international:bool, and each of the
document types has its own specific property also, number:int for DNI
and code:string for LU. As each document type is stored in a different
table, a sequence is used to generate disjoint ids for documents. To
retrieve the document for a user a join is done against the union of
both document tables. See below how this join-against-union always
return null for the DNI.number column.

The schema:

create table DNI (id integer not null, international bit, number
integer, primary key (id))
create table LU (id integer not null, international bit, code
varchar(255), primary key (id))
create table Person (id integer generated by default as identity
(start with 1), name varchar(255), document integer, primary key (id))

The data:

INSERT INTO DNI VALUES(1,FALSE,123456)
INSERT INTO LU VALUES(2,FALSE,'123456')
INSERT INTO PERSON VALUES(1,'Andres',1)
INSERT INTO PERSON VALUES(2,'Luis',2)

The union works fine:

select id, international, null as number, code, 1 as clazz_ from LU union
select id, international, number, null as code, 2 as clazz_ from DNI

ID INTERNATIONAL NUMBER CODE
1 false 123456 <null>
2 false <null> 123456

But when you join with the union, NUMBER gets lost.

select * from Person person inner join (
select id, international, null as number, code, 1 as clazz_ from LU
union
select id, international, number, null as code, 2 as clazz_ from DNI
) document
on person.document=document.id

ID NAME DOCUMENT ID INTERNATIONAL NUMBER CODE CLAZZ_
1 Andres 1 1 false <null>
<null> 2
2 Luis 2 2 false
<null> 123456 1

I'm testing against hsqldb-1.8.0.9.

Fred
------

Try this:

select * from Person person inner join (
select id, international, cast (null as integer) as number, code, 1 as
clazz_ from LU union
select id, international, number, cast (null as integer) as code, 2 as
clazz_ from DNI
) document
on person.document=document.id

The casts are absolutely necessary according to the SQL standard, although
HSQLDB relaxes this requirement.

Also, it is better to use BOOLEAN instead of BIT for type definition. In
1.8.0 and older versions, BIT is a synonym for boolean, but in the
forthcoming 1.9.0, BIT is a bit-map data type as defined in SQL:1999 and
cannot be used for BOOLEAN values.