[hibernate-dev] Composite IDs with a null property/field
Gail Badner
gbadner at redhat.com
Mon Dec 16 18:26:55 EST 2019
I've confirmed that the same inserts result in a constraint violation using
Oracle 12c:
insert into bundle (BUNDLE_NAME, KEY, LOCALE) values('bundle1', 'key1',
null);
insert into bundle (BUNDLE_NAME, KEY, LOCALE) values('bundle1', 'key1',
null);
I'm trying to figure out what would be needed to support this.
I tried simply commenting out this line:
https://github.com/hibernate/hibernate-orm/blob/master/hibernate-core/src/main/java/org/hibernate/type/ComponentType.java#L673
When I ran the unit
tests, org.hibernate.test.typedmanytoone.TypedManyToOneTest#testCreateQueryNull
failed because of a lazy many-to-one with a formula for a foreign key
column:
<many-to-one name="billingAddress"
entity-name="BillingAddress"
cascade="persist,save-update,delete"
fetch="join">
<column name="billingAddressId"/>
<formula>'BILLING'</formula>
</many-to-one>
The change results in a Customer with a lazy ShippingAddress proxy
containing an AddressId with #addressId == null and #type == "BILLING').
When the proxy is initialized, ObjectNotFoundException is thrown.
The reason this happens is a bit complicated.
The problematic query is:
"from Customer cust left join fetch cust.billingAddress where
cust.customerId='xyz123'"
The SQL that gets generated is:
select
customer0_.customerId as customer1_1_0_,
billingadd1_.addressId as addressI1_0_1_,
billingadd1_.add_type as add_type2_0_1_,
customer0_.name as name2_1_0_,
customer0_.billingAddressId as billingA3_1_0_,
customer0_.shippingAddressId as shipping4_1_0_,
'BILLING' as formula0_0_,
'SHIPPING' as formula1_0_,
billingadd1_.street as street3_0_1_,
billingadd1_.city as city4_0_1_,
billingadd1_.state as state5_0_1_,
billingadd1_.zip as zip6_0_1_
from
Customer customer0_
left outer join
Address billingadd1_
on customer0_.billingAddressId=billingadd1_.addressId
and 'BILLING'=billingadd1_.add_type
where
customer0_.customerId='xyz123'
In this case, the Customer entity does not have a billingAddress.
Hibernate correctly determines that the join fetched Address is null
because addressI1_0_1_ and add_type2_0_1_ are both null.
The problem happens when the Customer entity gets initialized. Since
Customer#billingAddress is mapped as lazy, it gets resolved as a proxy with
AddressId#addressId == null and #type == "BILLING").
Similarly, Customer#shippingAddress gets resolved as a proxy with
AddressId#addressId == null and #type == "SHIPPING").
Without the change Customer#billingAddress and #shippingAddress are null.
I don't see any way to maintain functionality demonstrated
by TypedManyToOneTest at the same time as allowing a composite ID to have a
null property at the same time.
I suppose we could allow a composite ID to have a null property only when
it has no properties that are formulas.
WDYT?
Thanks,
Gail
On Thu, Dec 12, 2019 at 10:26 AM Gail Badner <gbadner at redhat.com> wrote:
> Thinking about this more, I realized that, depending on the database, the
> use case may be invalid.
>
> For H2, at least, the following is allowed with a unique constraint:
>
> insert into bundle (BUNDLE_NAME, KEY, LOCALE) values('bundle1', 'key1',
> null);
> insert into bundle (BUNDLE_NAME, KEY, LOCALE) values('bundle1', 'key1',
> null);
>
> The reason why the unique constraint is not violated is because null !=
> null.
>
> If we allow a null value for a composite ID property, it should be
> specific to the dialects that would assume null == null in a unique key. I
> believe SQL Server behaves this way. I'm not sure about other databases.
>
> On Wed, Dec 11, 2019 at 3:06 AM Emmanuel Bernard <emmanuel at hibernate.org>
> wrote:
>
>> My answer is that if the code change looks too impactful I'm fine with no
>> supporting such scenario.
>>
>> On 11 Dec 2019, at 11:24, Joerg Baesner wrote:
>>
>> > ... I suppose some means it as default.
>>
>> Yes, exactly.
>>
>> Your reply doesn't answer the question if Hibernate shouldn't support
>> this scenario. Anyhow, what Gail already wrote is that Hibernate returns
>> null for the entity result, leading to a null value in a returned
>> ResultList, which seem to be wrong...
>>
>> On Wed, Dec 11, 2019 at 11:16 AM Emmanuel Bernard <emmanuel at hibernate.org>
>> wrote:
>>
>>> We have been trying to keep a balance of maintainable code base for
>>> Hibernate vs legacy/counter intuitive/plain wrong DB designs. The answer is
>>> never clear cut. In your case I'm not sure what a bundle + key means if it
>>> does not have a locale - I suppose some means it as default.
>>>
>>> On 11 Dec 2019, at 10:49, Joerg Baesner wrote:
>>>
>>> > I think in the past we argued the same for attributes of a composite
>>> id,
>>> > like you said, if one of the element can be nul, why is it in the id
>>> > property in the first place.
>>>
>>> As an example you might Imagine someone wants to put
>>> internationalization properties into a database and having a table
>>> structure like this (this might be an old legacy application that doesn't
>>> have a PK column):
>>>
>>> BUNDLE_NAME (not nullable)
>>> KEY (not nullable)
>>> LOCALE (nullable)
>>> VALUE (not nullable)
>>>
>>> The first 3 (BUNDLE_NAME, KEY, LOCALE) are the CompositeKey and there's
>>> a unique constraint on the database on these columns.
>>>
>>> It is fine to have the LOCALE as <null>, as in this case the systems
>>> default locale would be used, but for each BUNDLE_NAME/KEY combination you
>>> could only have a single composite key with a <null> LOCALE.
>>>
>>> Hibernate should be (must be?) able to handle this scenario, what do you
>>> think?
>>>
>>> Joerg
>>>
>>> On Wed, Dec 11, 2019 at 10:18 AM Emmanuel Bernard <
>>> emmanuel at hibernate.org> wrote:
>>>
>>>> Just talking about simple id, even if we allow the column to be
>>>> nullable
>>>> (if the DB even allows that), I don't think Hibernate allows null to be
>>>> a valid id value. Because null means I don't know or not applicable.
>>>> I think in the past we argued the same for attributes of a composite
>>>> id,
>>>> like you said, if one of the element can be nul, why is it in the id
>>>> property in the first place.
>>>>
>>>> As for whether there is a strong implementation detail reason to not
>>>> allow it, I don't know but I assume the null checking assuming "not an
>>>> id" is pretty much all over the place.
>>>>
>>>> Emmanuel
>>>>
>>>> On 11 Dec 2019, at 3:37, Gail Badner wrote:
>>>>
>>>> > Currently, there is no way to load an entity that exists in the
>>>> > database
>>>> > with a composite ID, if one of the composite ID columns is null.
>>>> >
>>>> > This behavior is due to this code in ComponentType#hydrate:
>>>> >
>>>> https://github.com/hibernate/hibernate-orm/blob/master/hibernate-core/src/main/java/org/hibernate/type/ComponentType.java#L671-L675
>>>> >
>>>> > Basically, if any field/property in a composite ID is null, Hibernate
>>>> > assumes the entire ID is null. An entity cannot have a null ID, so it
>>>> > returns null for the entity result.
>>>> >
>>>> > I believe that Hibernate does allow a primary key column to be
>>>> > nullable.
>>>> >
>>>> > TBH, it seems strange to have a property in a composite ID that can be
>>>> > null. If it can be null, it seems that the property could be removed
>>>> > from
>>>> > the composite key.
>>>> >
>>>> > I don't see anything in the spec about a requirement that all
>>>> > composite ID
>>>> > fields/properties must be non-null. Am I missing something?
>>>> >
>>>> > The code I referenced above is 13 years old. Does anyone have insight
>>>> > into
>>>> > why Hibernate does this?
>>>> >
>>>> > Thanks,
>>>> > Gail
>>>> > _______________________________________________
>>>> > hibernate-dev mailing list
>>>> > hibernate-dev at lists.jboss.org
>>>> > https://lists.jboss.org/mailman/listinfo/hibernate-dev
>>>>
>>>> _______________________________________________
>>>> hibernate-dev mailing list
>>>> hibernate-dev at lists.jboss.org
>>>> https://lists.jboss.org/mailman/listinfo/hibernate-dev
>>>>
>>>>
>>>
>>> --
>>>
>>> JOERG BAESNER
>>>
>>> SENIOR SOFTWARE MAINTENANCE ENGINEER
>>>
>>> Red Hat
>>>
>>> <https://www.redhat.com/>
>>>
>>> jbaesner at redhat.com T: +49-211-95439691
>>> <https://red.ht/sig>
>>> TRIED. TESTED. TRUSTED. <https://redhat.com/trusted>
>>>
>>>
>>
>> --
>>
>> JOERG BAESNER
>>
>> SENIOR SOFTWARE MAINTENANCE ENGINEER
>>
>> Red Hat
>>
>> <https://www.redhat.com/>
>>
>> jbaesner at redhat.com T: +49-211-95439691
>> <https://red.ht/sig>
>> TRIED. TESTED. TRUSTED. <https://redhat.com/trusted>
>>
>>
More information about the hibernate-dev
mailing list