[hibernate-dev] Composite IDs with a null property/field
Steve Ebersole
steve at hibernate.org
Wed Dec 18 16:47:47 EST 2019
I've already chimed in ;)
I don't think this is something we should do.
On Wed, Dec 18, 2019, 3:44 PM Gail Badner <gbadner at redhat.com> wrote:
> The main obstacle I see is that Hibernate assumes that ID columns are
> non-nullable, even if ID columns are explicitly mapped as nullable.
>
> This is consistent with the JPA spec, which says:
>
> "Every entity must have a primary key."
>
> ANSI SQL 92 says:
>
> "In addition, if the unique constraint was defined with PRIMARY KEY, then
> it requires that none of the values in the specified column or columns be
> the null value."
>
> javax.persistence.Column#nullable has a default of true.
>
> Since Hibernate ignores the default (as appropriate for a primary key), I
> think that it is also appropriate to ignore an explicit mapping to make it
> nullable (i.e., @Column(nullable="true")).
>
> I don't think it's a good idea to add a new property that would change
> this behavior for ID columns.
>
> Aside from the issue I mentioned above about ComponentType#hydrate [1],
> SQL Hibernate generates for loading an entity by ID would have to change.
> Currently, Hibernate generates SQL like the following:
>
> select ... from ... where bundle = ? and key = ? and locale = ?
>
> If the locale column is null, then Session#get will return null.
>
> It is possible to create a custom (@Loader) to change the query used by
> Session#get.
>
> Unfortunately, that won't work when loading entities with a Query like
> "from ... where id = ?".
>
> IMO, if we want to support this use case, we should use Hibernate-specific
> annotations to indicate that a composite ID column should be forced to be
> nullable.
>
> I haven't thought too much about this yet, but something like the
> following comes to mind:
>
> @EnbeddedId
> @UniqueKeyIdColumns( uniqueKeyIdColumns = {
> @UniqueKeyIdColumn( name="BUNDLE_NAME" ),
> @UniqueKeyIdColumn( name="KEY" ),
> @UniqueKeyIdColumn( name="LOCALE", nullable="true" )
> }
>
> The default for UniqueKeyIdColumn#nullable would be false.
>
> This would likely affect SPIs.
>
> Honestly, I am on the fence about whether this use case should be
> supported.
>
> Steve, Emmanuel, please chime in with your opinion?
>
> Thanks,
> Gail
>
> [1]
> https://github.com/hibernate/hibernate-orm/blob/master/hibernate-core/src/main/java/org/hibernate/type/ComponentType.java#L671-L675
>
> On Tue, Dec 17, 2019 at 10:41 AM Gail Badner <gbadner at redhat.com> wrote:
>
>> Jan-Willem, thanks very much for your feedback!
>>
>> You may be right about Hibernate not allowing a formula for a composite
>> ID property. It appears it's not allowed when hbm mapping is used. I'll
>> have to check to see if a formula is allowed when mapped with annotations..
>>
>> Looking a the mapping for Address again, I see the composite ID does not
>> contain a formula. Instead Address is associated with 2 different entity
>> names, each with a specified "where" attribute that indicates which entity
>> name the specific mapping is for:
>>
>> <class name="Address"
>> table="Address"
>> entity-name="BillingAddress"
>> where="add_type='BILLING'"
>> check="add_type in ('BILLING', 'SHIPPING')"
>> select-before-update="true"
>> dynamic-update="true">
>>
>> <composite-id name="addressId">
>> <key-property name="addressId"/>
>> <key-property name="type" column="add_type"/>
>> </composite-id>
>> ...
>>
>> </class>
>>
>> <class name="Address"
>> table="Address"
>> entity-name="ShippingAddress"
>> where="add_type='SHIPPING'"
>> select-before-update="true"
>> dynamic-update="true">
>>
>> <composite-id name="addressId">
>> <key-property name="addressId"/>
>> <key-property name="type" column="add_type"/>
>> </composite-id>
>> ...
>> </class>
>>
>> We would need to check the entity's "where" attribute or @Where clause
>> attribute value to see if the fragment references a composite ID column.
>>
>> Regards,
>> Gail
>>
>> On Tue, Dec 17, 2019 at 1:29 AM Jan-Willem Gmelig Meyling <
>> jan-willem at youngmediaexperts.nl> wrote:
>>
>>> 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