[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