[hibernate-dev] Composite IDs with a null property/field
Gail Badner
gbadner at redhat.com
Wed Dec 18 16:44:18 EST 2019
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