[hibernate-dev] Composite IDs with a null property/field
Jan-Willem Gmelig Meyling
jan-willem at youngmediaexperts.nl
Tue Dec 17 04:29:13 EST 2019
PostgreSQL doesn’t allow nullable columns in a compound primary key. It allows a unique constraint on a nullable column, but then it happily inserts the two values below - without constraint violation error.
I too have stumbled upon the need for nullable identifiers in Hibernate however. Mostly when I want to map native query results without an actual PK to an entity class. Another use case is mapping a @Subselect entity. These may not have an actual identifier, for example when creating a join product, so in order for all results to appear at all, you specify a compound key based on your requirements. I have experienced that this compound key may contain null values (in case of a LEFT JOIN for example).
As far as I am aware, it is currently not allowed to use Formula’s as @Id - I’ve stumbled upon this exception recently and I think correctly so . Maybe it is allowed for compound keys however. I think its safe to only allow null properties in an compound identifier if none of the identifier properties is a formula.
Kind regards,
Jan-Willem
> On 17 Dec 2019, at 00:26, Gail Badner <gbadner at redhat.com> wrote:
>
> 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>
>>>
>>>
> _______________________________________________
> hibernate-dev mailing list
> hibernate-dev at lists.jboss.org
> https://lists.jboss.org/mailman/listinfo/hibernate-dev
More information about the hibernate-dev
mailing list