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