2 one-to-many lists to subclasses in table-per-hierarchy

takejiN · **Joined:** Mon Nov 15, 2004 1:52 pm **Posts:** 7

I have extracted the following example from a problem we're having on a software in production. I didnt find another post or an entry in JIRA for it ; if there is one, or if it is an error from our side (we're quite new to Hibernate), my apologize.

To sum it up :
A class (Collec in the example) has 2 lists (subClass1 and subClass2), each of instances of a subclass (resp. SubClass1 and SubClass2) of the same superclass (SuperClass).
The SuperClass/SubClass1/SubClass2 is mapped as table-per-hierarchy.
Note we use the where= attribute for the mapping of the lists.

A data corruption occurs when we remove an element (subClass1[0] in the example) from one of the lists : it also removes the element of the other list having the same index (if there is one ; subClass2[0]).

It's because the sql generated does a :

Code:

update SuperClass set Collec_id=null, index_=null where Collec_id=? and index_=?

which applies to both SubClass1 and SubClass2 entries.
It doesnt use the where= of the list. Or the id of the SuperClass table as in the delete (if we use cascase=all-delete-orphan) which follows that update and is correct :

Code:

delete from SuperClass where SuperClass_id=?

The data corruption is even worse in the following example, since it removes subClass2[0] and subClass2[1].

Is it a bug, and is there a way to bypass it ? Best would be without having :
- to change the code (like using only 1 list in Collec and doing a filter in the code),
- to change the schema (like using table-per-subclass),
- to use a set : order is important, so we cant use set (and composite-index, if it would help),
since we have that problem in an project already in production (well atm it's rather stuck than in production :p).

The first 2 being the 2 working answers other than the "where=" we use to the situation described in "My class has two one-to-many associations to different subclasses of the same root class, but Hibernate ignores the actual concrete class when loading the associations." of the FAQ http://www.hibernate.org/117.html#A15.
The 2nd answer in the FAQ ("map each association to a different column of the Transaction table") looks like an error to me (cant specify column with one-to-many, right ?).

Follows the example showing the problem.
TIA !

Hibernate version:
2.1.6 (also happens in 2.1.4)

Mapping documents:

Code:

<?xml version="1.0" encoding="utf-8"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 2.0//EN"
        "http://hibernate.sourceforge.net/hibernate-mapping-2.0.dtd">

<hibernate-mapping>
   <class name="test.Collec" table="Collec">
      <id
         column="Collec_id"
         name="id"
         type="int"
      >
         <meta attribute="scope-set">protected</meta>
         <generator class="native" />
      </id>
      
      <list name="SubClass1" cascade="all-delete-orphan"  where="subType = '1'">
           <key column="Collec_id"/>
           <index column="index_"/>
           <one-to-many class="test.SubClass1"/>
      </list>
      
      <list name="SubClass2" cascade="all-delete-orphan" where="subType = '2'">
           <key column="Collec_id"/>
           <index column="index_"/>
           <one-to-many class="test.SubClass2"/>
      </list>
   </class>

   <class name="test.SuperClass" table="SuperClass">
      <id
         column="SuperClass_id"
         name="id"
         type="int"
         >
         <meta attribute="scope-set">protected</meta>
         <generator class="native" />
      </id>
      <discriminator 
         column="subType" 
         type="string"
         length="1"
      />

      <subclass name="test.SubClass1" discriminator-value="1">
       </subclass>
       
      <subclass name="test.SubClass2" discriminator-value="2">
       </subclass>
   </class>
   
</hibernate-mapping>

Code between sessionFactory.openSession() and session.close():

Code:

         s = sf.openSession();
         t = s.beginTransaction();
         
         Collec collec = new Collec();
         collec.setSubClass1(new ArrayList());
         collec.getSubClass1().add(new SubClass1());
         collec.getSubClass1().add(new SubClass1());
         
         collec.setSubClass2(new ArrayList());
         collec.getSubClass2().add(new SubClass2());
         collec.getSubClass2().add(new SubClass2());
         s.saveOrUpdate(collec);
         t.commit();
         
//         database fine :
//         Collec :
//         +-----------+
//         | Collec_id |
//         +-----------+
//         |         1 |
//         +-----------+

//         SuperClass :
//
//         +---------------+---------+-----------+--------+
//         | SuperClass_id | subType | Collec_id | index_ |
//         +---------------+---------+-----------+--------+
//         |             1 | 1       |         1 |      0 |
//         |             2 | 1       |         1 |      1 |
//         |             3 | 2       |         1 |      0 |
//         |             4 | 2       |         1 |      1 |
//         +---------------+---------+-----------+--------+
         
         
         t = s.beginTransaction();
         collec.getSubClass1().remove(0);
         s.saveOrUpdate(collec);
         t.commit();
         
         // table Superclass corrupted :
//
//         +---------------+---------+-----------+--------+
//         | SuperClass_id | subType | Collec_id | index_ |
//         +---------------+---------+-----------+--------+
//         |             2 | 1       |         1 |      0 |
//         |             3 | 2       |    [NULL] | [NULL] |
//         |             4 | 2       |    [NULL] | [NULL] |
//         +---------------+---------+-----------+--------+

Full stack trace of any exception that occurs:
No exception, only data corruption in database.

Name and version of the database you are using:
MySql (4.0.20-standard ?), also happens on Oracle 9i

The generated SQL (show_sql=true):
see debug below

Debug level Hibernate log excerpt:

Code:

52:53,687 DEBUG SQL:226 - insert into Collec values ( )
52:53,718 DEBUG SQL:226 - insert into SuperClass (subType) values ('1')
52:53,734 DEBUG SQL:226 - insert into SuperClass (subType) values ('1')
52:53,734 DEBUG SQL:226 - insert into SuperClass (subType) values ('2')
52:53,734 DEBUG SQL:226 - insert into SuperClass (subType) values ('2')
52:53,750 DEBUG SQL:226 - update SuperClass set Collec_id=?, index_=? where SuperClass_id=?
52:53,750 DEBUG IntegerType:46 - binding '1' to parameter: 1
52:53,750 DEBUG IntegerType:46 - binding '1' to parameter: 3
52:53,765 DEBUG IntegerType:46 - binding '0' to parameter: 2
52:53,765 DEBUG SQL:226 - update SuperClass set Collec_id=?, index_=? where SuperClass_id=?
52:53,765 DEBUG IntegerType:46 - binding '1' to parameter: 1
52:53,765 DEBUG IntegerType:46 - binding '2' to parameter: 3
52:53,765 DEBUG IntegerType:46 - binding '1' to parameter: 2
52:53,765 DEBUG SQL:226 - update SuperClass set Collec_id=?, index_=? where SuperClass_id=?
52:53,765 DEBUG IntegerType:46 - binding '1' to parameter: 1
52:53,765 DEBUG IntegerType:46 - binding '3' to parameter: 3
52:53,765 DEBUG IntegerType:46 - binding '0' to parameter: 2
52:53,765 DEBUG SQL:226 - update SuperClass set Collec_id=?, index_=? where SuperClass_id=?
52:53,765 DEBUG IntegerType:46 - binding '1' to parameter: 1
52:53,765 DEBUG IntegerType:46 - binding '4' to parameter: 3
52:53,781 DEBUG IntegerType:46 - binding '1' to parameter: 2

Begin of sql corrupting

Code:

54:20,140 DEBUG SQL:226 - update SuperClass set Collec_id=null, index_=null where Collec_id=? and index_=?
54:20,140 DEBUG IntegerType:46 - binding '1' to parameter: 1
54:20,140 DEBUG IntegerType:46 - binding '1' to parameter: 2
54:20,140 DEBUG SQL:226 - update SuperClass set Collec_id=null, index_=null where Collec_id=? and index_=?
54:20,156 DEBUG IntegerType:46 - binding '1' to parameter: 1
54:20,156 DEBUG IntegerType:46 - binding '0' to parameter: 2

End of sql corrupting

Code:

54:20,156 DEBUG SQL:226 - update SuperClass set Collec_id=?, index_=? where SuperClass_id=?
54:20,156 DEBUG IntegerType:46 - binding '1' to parameter: 1
54:20,171 DEBUG IntegerType:46 - binding '2' to parameter: 3
54:20,171 DEBUG IntegerType:46 - binding '0' to parameter: 2
54:20,171 DEBUG SQL:226 - delete from SuperClass where SuperClass_id=?
54:20,187 DEBUG IntegerType:46 - binding '1' to parameter: 1

takejiN · **Joined:** Mon Nov 15, 2004 1:52 pm **Posts:** 7

As we didnt get any answer, we've switched to a table-per-subclass mapping for the subclasses and it solves the issue (as expected from seeing the corrupting update).

We still would like to know if what we describded in the former post is worth submitting to JIRA or if it is an error from our side.
In the latter case, a quick explanation is still welcome ; atm we're a tad worried about the level of support on Hibernate (even for a non-commercial product, at least some community support would help, esp. when the documentation is quite complex and not that exhaustive - even with 'Hibernate in action').

Thanks.

christian · **Posted:** Tue Nov 23, 2004 12:57 pm

We offer commercial support and training if 500 pages of documentation and free source code is not enough, e.g. when you absolutely need our personal attention.

takejiN · **Joined:** Mon Nov 15, 2004 1:52 pm **Posts:** 7

Thank you for the offer of commercial support, but we didn't absolutely need your personal attention : as stated we already had solved our problem all by ourselves (even in the 1st post), and were asking if it was worth submitting to JIRA (as explained by the "Using JIRA" page, http://www.hibernate.org/217.html) just in the very slim case (1%) that it could have been a bug and that it could have helped Hibernate making an even better software (no sarcasm on that last part).

From your post we conclude that the answer is no, that the issue explained in this thread is the intended and documented behaviour of Hibernate.

Issue closed, thanks.

anthony · **Posted:** Tue Nov 23, 2004 2:41 pm

Quote:

As we didnt get any answer

i think this can be misunderstood.
Your topic is well described, but be sure to understand "where" clause use, and understand implicit/explicit polymorphism.
After being sure to make good use of inheritance, if this doesn't work, repost here and wait to see if a jira should be necessary.

Thanks for your feedback and everyone has to be very patient here ;))

takejiN · **Joined:** Mon Nov 15, 2004 1:52 pm **Posts:** 7

anthony wrote:

Quote:

As we didnt get any answer

i think this can be misunderstood.

I am sorry if it sounded rude, it wasnt my intention.
I mainly meant that we bypassed the problem for our users so the question remained out of personal curiosity and for the community.

Thank you for the informative feedback.
After checking "where" and implicit/polymorphism :
The "where" clause, as we understand it, only applies to queries, while the problem is with the update.

About the polymorphism="explicit", we refered to the reference manual 2.1.6, section 5.1.3 :

Quote:

Implicit polymorphism means that instances of the class will be returned by a query that names any superclass or implemented interface or the class and that instances of any subclass of the class will be returned by a query that names the class itself. Explicit polymorphism means that class instances will be returned only be queries that explicitly name that class and that queries that name the class will return only instances of subclasses mapped inside this <class> declaration as a <subclass> or <joined-subclass>.

We understand it as :
- It applies to queries, while the issue is about update.
- It affects retrieval behaviour of sub/super classes of a given class, while the issue is about 2 lists of "sister" classes and modification on one affecting the other (removing an element of the list of "SubClass1" and "SubClass2".
Still we tried it (<class name="test.SuperClass" table="SuperClass" polymorphism="explicit"> ...) and it didnt solve the issue.

What we really find strange is that
- the "corrupting" update uses a "where Collec_id=? and index_=?"
- while the following "correct" delete (when using cascade="all-delete-orphan") uses a "where SuperClass_id=?".
We definitely dont have a good understanding of Hibernate, so we guess there is a reason why 2 different where strategies are used.

In fact, the problem comes from the one-to-many associations : we missed the part that the association is a TABLE and not a OBJECT or CLASS association. So there cannot be 2 <one-to-many> refering to the same table, even if it's using 2 different classes like in a table-per-hierarchy.
It is quite obvious from a relational point of view, but the syntax of <one-to-many class=...> hides that, using class and not table (esp. that the class doesnt seem to have much use since we have to use a where=...).

The question looks really answered this time.
Thanks.