Re: [hibernate-dev] Composite IDs with a null property/field

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(a)redhat.com&gt; 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... > > 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(a)redhat.com&gt; 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(a)hibernate.org&gt; >> 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(a)hibernate.org&gt; >>> 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(a)hibernate.org&gt; 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... >>>>>> >>>>>> 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(a)lists.jboss.org >>>>>> https://lists.jboss.org/mailman/listinfo/hibernate-dev >>>>> >>>>> _______________________________________________ >>>>> hibernate-dev mailing list >>>>> hibernate-dev(a)lists.jboss.org >>>>> https://lists.jboss.org/mailman/listinfo/hibernate-dev >>>>> >>>>> >>>> >>>> -- >>>> >>>> JOERG BAESNER >>>> >>>> SENIOR SOFTWARE MAINTENANCE ENGINEER >>>> >>>> Red Hat >>>> >>>> <https://www.redhat.com/> >>>> >>>> jbaesner(a)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(a)redhat.com T: +49-211-95439691 >>> <https://red.ht/sig> >>> TRIED. TESTED. TRUSTED. <https://redhat.com/trusted> >>> >>> > _______________________________________________ > hibernate-dev mailing list > hibernate-dev(a)lists.jboss.org > https://lists.jboss.org/mailman/listinfo/hibernate-dev