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.

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.

... I suppose some means it as default.

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>

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>

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

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(a)youngmediaexperts.nl&gt; 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(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 > >

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(a)redhat.com&gt; 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... > > On Tue, Dec 17, 2019 at 10:41 AM Gail Badner <gbadner(a)redhat.com&gt; 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(a)youngmediaexperts.nl&gt; 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(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 >>> >>>