[hibernate-issues] [Hibernate-JIRA] Created: (ANN-757) Multiple secondary tables give uncontrollable ordering of sql inserts and of joins in select statements.

[Pete Geraghty (JIRA)]

Multiple secondary tables give uncontrollable ordering of sql inserts and of joins in select statements.
--------------------------------------------------------------------------------------------------------

                 Key: ANN-757
                 URL: http://opensource.atlassian.com/projects/hibernate/browse/ANN-757
             Project: Hibernate Annotations
          Issue Type: Bug
    Affects Versions: 3.3.1.GA
         Environment: Hibernate Annotations 3.3.1.GA
Hibernate 3.2.6.ga
Oracle 10g express edition
            Reporter: Pete Geraghty
            Priority: Minor


When an entity has multiple secondary tables, the order in which they are considered when constructing and executing sql is important in at least two cases: 
(a) selection with LEFT OUTER JOIN
(b) the order of insertion of rows into the database when persisting

However the order in which Hibernate actually processes them is effectively random as it is using the values() collection of a HashMap.

I am using secondary tables predominantly to achieve a mixed inheritance strategy, as suggested in Gavin King's "Java Persistence With Hiberate" book, section 5.1.5.  However there are also some situations where I need secondary tables for other reasons, and consequently I have two secondary tables declared on some entities.

Consider the following situation (table names for clarity of illustration):
@Entity
@Table(name = "a_main")
@Inheritance(strategy = InheritanceType.SINGLE_TABLE)
public abstract class A { ... }

@Entity
@SecondaryTables({
@SecondaryTable(name = "b_main"),
@SecondaryTable(name = "b_secondary")
})
public class B extends A { ...}

At a database level, there must be a row in b_main for every instance of B, and there may or may not be a row in b_secondary.  I want foreign key constraints from b_main to a_main, and from b_secondary to b_main.

In fact the order in which inserts are processed depend on the hashed value of the actual table names corresponding to b_main and b_secondary, i.e.,  is  effectively indeterminate, and in some cases I must ask the dba to drop the foreign key constraint which ought to be there. 

Also, when an instance of B is being hydrated from the result set of a query on A, the select issued specifies a left outer join involving b_main and b_secondary. Which of these is considered left depends on the hash of the actual table names, and so for some table names the select is the wrong way round and will not retrieve the necessary values, i.e., it takes the following form:

  select ... from b_secondary ....  left outer join b_main ....	

Whereas I want:

  select ... from b_main ....  left outer join b_secondary ....	

The basic problem is that although EntityBinder processes SecondaryTable annotations in a defined order (the order in which they appear), it uses two HashMaps to store these , secondaryTables and secondaryTableJoins.  Then the values collection iterator is used (for example) when adding joins to the PersistentClass object.  HashMap offers no contract as to the order of the values collection.  Strictly it doesn't even guarantee that the values collections of the two HashMaps in question will have corresponding entries in the same order, although in practice they will, as they have the same keys and same capacity.

When deciding on the tables relevant to a select, the table with the lowest "table number" is deemed to be the "driving table" e.g., in the AbstractEntityPersister renderSelect() method at line 2891. However, these table numbers are indirectly derived from the iterator on the values() collection of the secondaryTables map.  

The map variables are assigned on lines 89 and 90 of EntityBinder
	private java.util.Map<String, Join> secondaryTables = new HashMap<String, Join>();
	private java.util.Map<String, Object> secondaryTableJoins = new HashMap<String, Object>();

If these are instead assigned to new LinkedHashMaps, the values collections reflect the order of insertion, and therefore the order in which the annotations appear determines the order in which left joins and inserts are processed, which gives more predictable and controllable behaviour.

	private java.util.Map<String, Join> secondaryTables = new LinkedHashMap<String, Join>();
	private java.util.Map<String, Object> secondaryTableJoins = new LinkedHashMap<String, Object>();

I think this represents a definite improvement, and it solves my problem. 

I can imagine other situations where both secondary tables are "optional", and the use of a LEFT OUTER JOIN is wrong either way round  I haven't looked further at what a solution to that would look like.


-- 
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