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

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> > >