[hibernate-issues] [Hibernate-JIRA] Commented: (JPA-6) @ManyToOne target using Single Table inheritance type

[Michal Jastak (JIRA)]

    [ http://opensource.atlassian.com/projects/hibernate/browse/JPA-6?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=38442#action_38442 ] 

Michal Jastak commented on JPA-6:
---------------------------------

I've tested the same mapping on OpenJPA and Eclipselink, both are working correctly, generating SQL:

1) OpenJPA:

SELECT t1.id, t1.D_TYPE, t1.description FROM TABLES t0 INNER JOIN DICTIONARIES t1 ON t0.LEG_MATERIAL = t1.id WHERE t0.id = ? AND t1.D_TYPE = ?

(parameters: 1 and 'MaterialType')

2) Eclipselink:

SELECT ID, LEG_MATERIAL FROM TABLES WHERE (ID = ?)
SELECT ID, D_TYPE, DESCRIPTION FROM DICTIONARIES WHERE ((ID = ?) AND (D_TYPE = ?))

(parameters: 1 - for first query, and 1, 'MaterialType' for second one)


> @ManyToOne target using Single Table inheritance type
> -----------------------------------------------------
>
>                 Key: JPA-6
>                 URL: http://opensource.atlassian.com/projects/hibernate/browse/JPA-6
>             Project: Java Persistence API
>          Issue Type: Bug
>    Affects Versions: 1.0.0
>         Environment: hibernate annotations 3.4.0 GA, hibernate entity manager 3.4.0 GA, spring framework 3.0.4, database: Mysql (tested on Oracle as well)
>            Reporter: Michal Jastak
>
> It looks like the wrong SQL is generated when you use the @ManyToOne which target entity is using Single Table inheritance type, see the example below:
> @Entity
> @Inheritance(strategy = InheritanceType.SINGLE_TABLE)
> @DiscriminatorColumn(name = "D_TYPE")
> @Table(name = "DICTIONARIES")
> public abstract class DictionaryItem implements Serializable {
>     private String description;
>     private Long id;
>     @Basic
>     public String getDescription() {
>         return description;
>     }
>     @Id
>     public Long getId() {
>         return id;
>     }
> ...
> }
> @Entity
> @DiscriminatorValue("MaterialType")
> public class MaterialType extends DictionaryItem {
>     // Empty by design ...
> }
> @Entity
> @javax.persistence.Table(name = "TABLES")
> public class Table implements Serializable {
>     private Long id;
>     private MaterialType legsMaterial;
>     @Id
>     public Long getId() {
>         return id;
>     }
>     @ManyToOne
>     @JoinColumn(name = "LEG_MATERIAL")
>     public MaterialType getLegsMaterial() {
>         return legsMaterial;
>     }
> ...
> }
> @Repository("persistence.TableDAO")
> public class DefaultTableDAO implements TableDAO {
>     @PersistenceContext
>     private EntityManager entityManager;
>     public Table get(Long id) {
>         return entityManager.find(Table.class, id);
>     }
> ...
> }
> DB tables:
> CREATE TABLE DICTIONARIES (
>     ID            NUMERIC NOT NULL,
>     D_TYPE        VARCHAR(32),    
>     DESCRIPTION   VARCHAR(64),
>     CONSTRAINT DICTIONARIES_PK PRIMARY KEY (ID, D_TYPE)
> );
> INSERT INTO DICTIONARIES(ID, D_TYPE, DESCRIPTION) VALUES (1, 'MaterialType', 'Wood');
> INSERT INTO DICTIONARIES(ID, D_TYPE, DESCRIPTION) VALUES (2, 'MaterialType', 'Glass');
> INSERT INTO DICTIONARIES(ID, D_TYPE, DESCRIPTION) VALUES (1, 'SomeOtherType', 'SOT1');
> INSERT INTO DICTIONARIES(ID, D_TYPE, DESCRIPTION) VALUES (2, 'SomeOtherType', 'SOT2');
> CREATE TABLE TABLES (
>     ID           NUMERIC NOT NULL, 
>     LEG_MATERIAL NUMERIC, 
>     CONSTRAINT TABLES_PK PRIMARY KEY (ID)
> );
> INSERT INTO TABLES(ID, LEG_MATERIAL) VALUES (1, 1);
> Now let's try to fetch the table having id 1 with following code:
> Table table = tableDAO.get(1L);
> this will cause hibernate to execute the SQL query:
> select table0_.id as id0_1_, table0_.LEG_MATERIAL as LEG2_0_1_, materialty1_.id as id1_0_, materialty1_.description as descript3_1_0_ from TABLES table0_ left outer join DICTIONARIES materialty1_ on table0_.LEG_MATERIAL=materialty1_.id where table0_.id=?
> with parameter 1 (table id)
> which leads to the following result set:
> +--------+-----------+--------+----------------+
> | id0_1_ | LEG2_0_1_ | id1_0_ | descript3_1_0_ |
> +--------+-----------+--------+----------------+
> |      1 |         1 |      1 | Wood           |
> |      1 |         1 |      1 | SOT1           |
> +--------+-----------+--------+----------------+
> as you see - there is missing discriminator part of where clause for the material type

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://opensource.atlassian.com/projects/hibernate/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira