[jboss-user] [EJB 3.0] - Re: InheritanceType.TABLE_PER_CLASS broken in JBoss5 CR2 for

[Wolfgang Knauf]

Ok, after building a small sample, analyzing the sql and doing some google, I can tell that the sql for Hypersonic is wrong.

Here is a small broken sample: http://www.informatik.fh-wiesbaden.de/~knauf/private/InheritanceTablePerClass.ear
It contains all sources and can be run on JBoss 4.2 and JBoss 5 (broken on both, my earlier statement "my other sample worked in 4.2" was wrong, because I did not access the subclass fields ;-) ).

The sample contains a base entity with three childs, and a web client at "http://localhost:8080/InheritanceTablePerClassWeb/index.jsp", which creates three entities and tries to lookup them. 

Now about the sql:
JBoss executes this:
select base0_.id as id56_, base0_.name as name56_, base0_.child3Field as child1_57_, 
  | base0_.child2Field as child1_58_, base0_.child1Field as child1_59_, base0_.clazz_ as clazz_ from  
  |   (select id, null as child2Field, child3Field, null as child1Field, name, 
  |         1 as clazz_ from Child3
  |     union select id, child2Field, null as child3Field, null as child1Field, name, 
  |         2 as clazz_ from Child2 
  |     union select id, null as child2Field, null as child3Field, child1Field, name, 
  |         3 as clazz_ from Child1 )
  | base0_

Executing just the inner subselect brings correct results. But the full statement does not work.

This forum post brought me to a solution that brings correct results in HSQL Database Manager: http://kaigrabfelder.de/en/2008/05/19/1211216700000.html

Here is a reworked SQL statement:
select base0_.id as id56_, base0_.name as name56_, 
  | base0_.child3Field as child1_57_, base0_.child2Field as child1_58_, 
  | base0_.child1Field as child1_59_, base0_.clazz_ as clazz_ from  
  |   (select id, cast (null as VARCHAR) as child2Field, child3Field, 
  |       cast (null as VARCHAR) as child1Field, name, 1 as clazz_ from Child3 
  |    union select id, child2Field, cast (null as VARCHAR) as child3Field, 
  |       cast (null as VARCHAR) as child1Field, name, 2 as clazz_ from Child2 
  |    union select id, cast (null as VARCHAR) as child2Field, cast (null as VARCHAR) as child3Field, 
  |       child1Field, name, 3 as clazz_ from Child1  ) base0_

Is this a JBoss bug, or a Hypersonic bug? Is there some workaround (specifying the statement for the join)?
Should I file a JBoss bug?

Thanks

Wolfgang

View the original post : http://www.jboss.com/index.html?module=bb&op=viewtopic&p=4190641#4190641

Reply to the post : http://www.jboss.com/index.html?module=bb&op=posting&mode=reply&p=4190641