Having multiple domain classes with map properties will result in bad hibernate sql generation

David Hay added a comment - 06/Dec/12 3:01 PM We ran into the same problem. After debugging for quite a while, I discovered the root issue lies in the way that Hibernate creates aliases for key columns for a Map property, which can result in an alias which is identical to the Map's key/value column aliases. This is VERY bad and results in the parent table's row id appearing as the Map's key or value when a row is retrieved. In general, Hibernate creates aliases by stripping the column name down so that it only contains letters, and then appending a unique integer and an '_'. For non key columns, the uniqueInteger used is that from the Column object, which is it's column number. However for key columns, it uses the uniqueInteger from the root (parent) table. The Table object's uniqueInteger is based on the order in which it was created when everything is initialized (secondPassCompile() in GrailsAnnotationConfiguration iterates through the HashSet of known GrailsDomainClass objects, so the order varies) If all this comes together just right (or wrong!) this bug manifests itself - if the column names all begin with the same set of letters and the parent table is created as the 3rd or 4th table, the first (key) column will have an alias identical to the 2nd or 3rd column, and when toFragmentString() is called on the SelectFragment, the 2nd or 3rd column will be ignored. This is exactly what happens with Grails, sometimes, as the default column naming convention happens to start all column names with the same substring of letters. If we have a domain object 'Object1' with a Map property 'options', Grails creates a table 'options' with the following columns: 'options' contains the id of the associated row in the Object1 table 'options_idx' contains the Map key 'options_elt' contains the Map value Now, if the Object1 table is created as the 3rd table, Hibernate assigns its Table object a uniqueInteger property of 2. When the BasicCollectionPersister creates an alias for the keyColumn (line 299),it passes the dialect and root table 'Object1' to the following Column method: public String getAlias(Dialect dialect, Table table) { return getAlias(dialect) + table.getUniqueInteger() + '_'; } The getAlias(dialect) returns the column name 'options' unchanged as it is all letters, and then "2_" is appended, with the resulting alias 'options2_'. When the BasicCollectionPersister proceeds to create the next column (line 387), it calls the getAlias(dialect) method directly on the Column object. The column name i'options_idx' is stripped down to it's initial string of just letters ('options') and, as this is different from the column name, it appends this column's uniqueInteger (2) and the ' ' - resulting in 'options2 '. As you can see, this is identical to the alias for the first column. To note, if the Object1 table is created as the 4th table, it will then have an alias identical to the 3rd column - 'options3_' When the BasicCollectionPersister's selectFragment() is called, it creates a SelectFragment that has all 3 aliases (the alias for the 3rd column will be 'options3_'). When toFragmentString() is called on it, these aliases are added to a HashSet, 'columnsUnique', which causes the 2nd column to be ignored as it already exists in the Set (there is no 'else' clause to match line 150). As Eric notes in his thread, a workaround is to override the name of the key column: static mapping = { options joinTable: [key: "owner_id"] } Technically, this doesn't seem to be a Grails bug, but Grail's naming convention for Map properties exposing a Hibernate bug. I'll try to find out if this is fixed in a later Hibernate release (I debugged on Grails 2.0.3 which uses Hibernate Core 3.6.10). Interested in comments from the Grails team... cheers, David

David Hay added a comment - 07/Dec/12 2:00 PM Turns out this is a known Hibernate bug: https://hibernate.onjira.com/browse/HHH-7243