ianroberts commented on issue #16665:
URL: https://github.com/apache/grails-core/issues/16665#issuecomment-6249426034
If I change the `Item` class to have two associations to the same other side
and no further mapping:
```groovy
class Item {
String name
static hasMany = [roles: Role, denyRoles: Role]
}
```
then both 5 and 7 create a single join table for both associations, albeit
with slightly different constraints:
```sql
--- Hibernate 5
create table item_role (item_roles_id bigint not null, role_id bigint,
item_deny_roles_id bigint not null)
alter table item_role add constraint FKmr27g8c794t0765o17bpi1exh foreign key
(role_id) references role
alter table item_role add constraint FKskq0ajk4g23s1rdrrao5er4qj foreign key
(item_roles_id) references item
alter table item_role add constraint FKay92y9xyiu5yaoyk89plm3h9v foreign key
(item_deny_roles_id) references item
--- Hibernate 7
create table item_role (item_deny_roles_id bigint, item_roles_id bigint,
role_id bigint, unique (item_roles_id, role_id))
alter table if exists item_role add constraint FKmr27g8c794t0765o17bpi1exh
foreign key (role_id) references role
alter table if exists item_role add constraint FKskq0ajk4g23s1rdrrao5er4qj
foreign key (item_roles_id) references item
alter table if exists item_role add constraint FKay92y9xyiu5yaoyk89plm3h9v
foreign key (item_deny_roles_id) references item
```
In H5 the `Item` side columns are both `not null`. In H7 they are nullable,
and there's a `unique` constraint on `item_roles_id` (the first declared
`hasMany`) but not on the `item_deny_roles_id` (the second one).
Running the test app on Hibernate 5, any attempt to save an `Item` after
`addToRoles` or `addToDenyRoles` throws an exception because of the `not null`
on the foreign key column representing the other association
```
insert into item_role (item_roles_id, role_id) values (?, ?)
SQL Error: 23502, SQLState: 23502
NULL not allowed for column "ITEM_DENY_ROLES_ID"; SQL statement:
insert into item_role (item_roles_id, role_id) values (?, ?) [23502-240]
```
On Hibernate 7 there is no attempt to write to the join table at all (since
there are two unidirectional `hasMany` associations on the same class) so it
fails silently rather than with an exception.
---
Adding explicit join table mappings:
```groovy
class Item {
String name
static hasMany = [roles: Role, denyRoles: Role]
static mapping = {
roles joinTable: 'item_allow_roles'
denyRoles joinTable: 'item_deny_roles'
}
}
```
both versions create two separate join tables:
```sql
-- Hibernate 5
create table item_allow_roles (item_roles_id bigint not null, role_id bigint)
create table item_deny_roles (item_deny_roles_id bigint not null, role_id
bigint)
-- Hibernate 7
create table item_allow_roles (item_roles_id bigint, role_id bigint, unique
(item_roles_id, role_id))
create table item_deny_roles (item_deny_roles_id bigint, role_id bigint,
unique (item_deny_roles_id, role_id))
```
and now Hibernate 5 succeeds in creating an item with one allowed role and
no denied, but Hibernate 7 silently fails as before (because there are two
unidirectional `hasMany` associations).
---
## Analysis
The whole check in `CollectionKeyColumnUpdater` for "does the containing
class have more than one unidirectional `hasMany`" only seems to protect
against a condition that would fail at runtime anyway under Hibernate 5. The
original issue #10811 was to do with an app upgraded from Grails version 2 to
version 3 nine years ago, so maybe it's time to stop trying for backwards
compatibility that far back. If the user needs compatibility with an existing
database that models both associations in a single table then they can
introduce an explicit class to represent the join table with many-to-one
associations on the three (or more) sides.
For v8.0.x we could just drop the whole "more than one `hasMany`" test
altogether and accept that it's a bit of a foot-gun if you declare two
`hasMany` links to the same far side class _without_ explicitly naming the join
tables. And document this more clearly in the GORM documentation that if you
want to `hasMany` twice to the same class then you _need_ the explicit mapping.
Then for v8.1 or v9 we could consider making it a hard error to have two
`hasMany` links that use the same join table, so such cases fail up front at
startup time.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
