Troublesome bug with polymorphic query using TABLE_PER_CLASS

hallmit · **Posted:** Mon Feb 15, 2010 7:10 am

Hi folks,
I found this little bug while playing with Hibernate 3.3.2 and PostgreSQL 8.3:

Very simple model to understand:

Code:

@Entity
@Inheritance(strategy = InheritanceType.TABLE_PER_CLASS)
//this entity must be queryable i.e do not use @MappedSuperclass
public class Foo
{
   @Id
   private Integer id
   //other attributes.
}

Code:

@Entity
public class SonOfFoo extends Foo
{
   private String toys;
}

Code:

@Entity
public class DaughterOfFoo extends Foo
{
   private Boolean toys;
}

when I have been executed a polymorphic query ("SELECT f FROM Foo as f"), I get this error :

Code:

Caused by: org.postgresql.util.PSQLException: ERROR: UNION types boolean and character varying cannot be matched
   at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:1608)

In fact, the Hibernate uses the same alias name in union clauses for columns with different SQL type. I found these similar issues for DB2 [1] and Oracle [2].

Code:

em.createQuery("SELECT f FROM Foo as f")
Hibernate: 
    select
        foo0_.id as id0_,
        foo0_.toys as toys1_,
        foo0_.toys as toys3_,
        foo0_.clazz_ as clazz_ 
    from
        ( select
            null::bool as toys,
            id,
            0 as clazz_ 
        from
            Foo 
        union
        all select
            toys, <-- not good. Boolean type
            id,
            1 as clazz_ 
        from
            DaughterOfFoo 
        union
        all select
            toys, <-- not good. Varying char type
            id,
            2 as clazz_ 
        from
            SonOfFoo 
    ) foo0_

Apparently, the problem can circumvented by setting the column name on offending attributes (@Column(name = "son_toys")) to disambiguate them... but if you have a lot of classes to customize or must map a legacy database this problem get blocker.

What do you think? Should I report an issue about this?

--cited links
[1] viewtopic.php?f=1&t=974326
[2] viewtopic.php?f=1&t=949281