I agree with Steve. I don't think that we should add a feature to explicitly support this.
That said, there is a workaround that avoids the issue, by using a UserType to convert null to an empty string for Java processing, and convert the empty string to null for inserting, update, and deleting. I've created 2 tests that illustrate how this can be done with a UserType and a custom @Loader, @SQLUpdate, and @SQLDelete: Using an embedded ID: https://github.com/gbadner/hibernate-core/blob/composite-id-with-null-property/hibernate-core/src/test/java/org/hibernate/test/cid/EmbeddedIdUserTypeNullableColumnTest.java Using an IdClass: https://github.com/gbadner/hibernate-core/blob/composite-id-with-null-property/hibernate-core/src/test/java/org/hibernate/test/cid/IdClassUserTypeNullableColumnTest.java It will not be possible to execute an HQL/JPL query like: CompositeKeyEntity compositeKeyEntity = session.createQuery( "from CKE where compositeKey = ?1", CompositeKeyEntity.class ).setParameter( 1, new CompositeKey( "abc", LocaleType.DEFAULT ) ).uniqueResult(); because Hibernate generates the SQL: select embeddable0_.id1 as id1_0_ embeddable0_.id2 as id2_0_, embeddable0_.name as name3_0_ from CKE embeddable0_ where embeddable0_.id1=? and embeddable0_.id2=? If an entity has id2 == null, then it will not be possible to load it using this query. The following will work though: CompositeKeyEntity compositeKeyEntity = session.createQuery( "from CKE where compositeKey.id1 = ?1 and (compositeKey.id2 = ?2 or compositeKey.id2 is null )" , CompositeKeyEntity.class ).setParameter( 1, "abc" ).setParameter( 2, LocaleType.DEFAULT ).uniqueResult(); Regards, Gail On Wed, Dec 18, 2019 at 1:48 PM Steve Ebersole <st...@hibernate.org> wrote: > 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 <gbad...@redhat.com> 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/main/java/org/hibernate/type/ComponentType.java#L671-L675 >> >> On Tue, Dec 17, 2019 at 10:41 AM Gail Badner <gbad...@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-wil...@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 <gbad...@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 <gbad...@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 < >>>> emman...@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 < >>>> emman...@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 < >>>> >>>> emman...@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@lists.jboss.org >>>> >>>>>> https://lists.jboss.org/mailman/listinfo/hibernate-dev >>>> >>>>> >>>> >>>>> _______________________________________________ >>>> >>>>> hibernate-dev mailing list >>>> >>>>> hibernate-dev@lists.jboss.org >>>> >>>>> https://lists.jboss.org/mailman/listinfo/hibernate-dev >>>> >>>>> >>>> >>>>> >>>> >>>> >>>> >>>> -- >>>> >>>> >>>> >>>> JOERG BAESNER >>>> >>>> >>>> >>>> SENIOR SOFTWARE MAINTENANCE ENGINEER >>>> >>>> >>>> >>>> Red Hat >>>> >>>> >>>> >>>> <https://www.redhat.com/> >>>> >>>> >>>> >>>> jbaes...@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/> >>>> >>> >>>> >>> jbaes...@redhat.com T: +49-211-95439691 >>>> >>> <https://red.ht/sig> >>>> >>> TRIED. TESTED. TRUSTED. <https://redhat.com/trusted> >>>> >>> >>>> >>> >>>> > _______________________________________________ >>>> > hibernate-dev mailing list >>>> > hibernate-dev@lists.jboss.org >>>> > https://lists.jboss.org/mailman/listinfo/hibernate-dev >>>> >>>> _______________________________________________ hibernate-dev mailing list hibernate-dev@lists.jboss.org https://lists.jboss.org/mailman/listinfo/hibernate-dev