borinquenkid opened a new issue, #16748:
URL: https://github.com/apache/grails-core/issues/16748
## Problem
When two unidirectional `hasMany` associations to the same class share one
join table **and the same key column**, with an element column each, `is empty`
and `is not empty` on one association also see the rows of the other
association.
```groovy
class Account {
static hasMany = [roles: Role, denyRoles: Role]
static mapping = {
roles joinTable: [name: 'account_roles', key: 'account_id',
column: 'role_id']
denyRoles joinTable: [name: 'account_roles', key: 'account_id',
column: 'deny_role_id']
}
}
// an account with one role and no denied roles: its only row is
(account_id, role_id, deny_role_id = null)
Account.executeQuery('select a from Account a where a.denyRoles is empty')
// expected: the account, found: nothing
```
The SQL for `denyRoles is empty` is
```
not exists(select 1 from account_roles dr1_0 where a1_0.id=dr1_0.account_id)
```
with no restriction on `deny_role_id`, so the row of `roles` counts.
`denyRoles is not empty` is wrong in the same way.
## What was checked
Found in the semantic review of #16701, with a throwaway spec on H2. In the
same spec, `size(a.denyRoles)`, `left join` and `left join fetch` of the
association, and loading the collections directly all answered correctly:
#16701 gives each collection of a shared table a `where` fragment (its element
columns are not null), and Hibernate applies it to those statements but not to
the `exists` subquery of `is empty`. The default mapping with a key column per
association is not affected, because the key column tells the associations
apart. I did not run the GORM criteria `isEmpty` / `isNotEmpty`, which probably
compile to the same HQL.
Not a regression: before #16701 the rows of the second association were not
written at all.
## Direction
The `Collection.setWhere` restriction does not reach `is empty`. A fix needs
another mechanism for the test of emptiness, or the documented limit that these
two predicates are not supported on associations that share a key column.
## Tests
A pending feature in `SharedJoinTableBulkDeleteSpec`.
--
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]
