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

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