jdaugherty commented on code in PR #16583: URL: https://github.com/apache/grails-core/pull/16583#discussion_r4392715108
########## grails-data-hibernate5/core/src/main/groovy/org/grails/orm/hibernate/dialect/UniqueNullsPerDialect.java: ########## @@ -0,0 +1,68 @@ +/* + * Licensed to the Apache Software Foundation (ASF) under one + * or more contributor license agreements. See the NOTICE file + * distributed with this work for additional information + * regarding copyright ownership. The ASF licenses this file + * to you under the Apache License, Version 2.0 (the + * "License"); you may not use this file except in compliance + * with the License. You may obtain a copy of the License at + * + * https://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, + * software distributed under the License is distributed on an + * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY + * KIND, either express or implied. See the License for the + * specific language governing permissions and limitations + * under the License. + */ + +package org.grails.orm.hibernate.dialect; + +import java.util.Arrays; + +import org.hibernate.dialect.Dialect; +import org.hibernate.dialect.Oracle8iDialect; + +import org.grails.datastore.mapping.config.UniqueNulls; + +/** + * How the unique index Hibernate creates for a {@code unique} constraint with a group treats a {@code null} in the key, + * on each database, so that the validation can read {@code null}s the way the index will. + * <ul> + * <li>{@link #ORACLE}: Oracle indexes a row that has a {@code null} in some of the key columns and compares its + * other columns, so {@code ('en', null)} twice is a violation, and only a key that is {@code null} in every column is + * left out. A {@code null} is a value: {@link UniqueNulls#VALUE}.</li> + * <li>{@link #DEFAULT}: H2, PostgreSQL, MySQL and MariaDB treat {@code null}s as distinct, so such a row never + * conflicts. SQL Server and DB2 compare them, but Hibernate renders the index for a nullable key as a filtered unique + * index that leaves the rows with a {@code null} out, which comes to the same. {@link UniqueNulls#SKIP}.</li> + * </ul> + * + * @since 9.0 + */ +public enum UniqueNullsPerDialect { + + ORACLE(UniqueNulls.VALUE, Oracle8iDialect.class), + + DEFAULT(UniqueNulls.SKIP, Dialect.class); Review Comment: Verified at cd413415c8 against `hibernate-core-jakarta` 5.6.15: `SQLServerDialect`, `SybaseDialect` and their subclasses get `DefaultUniqueDelegate`, and `UniqueNullsPerDialectSpec` pins all eleven dialects. This round's comment on the Hibernate 7 enum covers the all-null key on `ORACLE`. ########## grails-doc/src/en/ref/Constraints/unique.adoc: ########## @@ -64,6 +64,60 @@ username(unique: ['group', 'department']) In this example `username` must be unique in `group` and `department`. There might be same logins in different groups or different departments. +==== Null values in a composite key + +A property of the key can be `null`: a `country` that is optional in `language unique: 'country'`, a `parent` that is `null` for the top level nodes of a tree, or the constrained property itself when it is `nullable`. Whether such a row can be a duplicate of another is a question databases answer in two ways for their unique index, so the constraint has four readings of a `null`, selected per constraint or for the whole application: + +[cols="1,4"] +|=== +|Reading |A `null` in the key + +|`database` (default) +|Is read the way the unique index of the database does: `skip` where the index treats `null` values as distinct, `value` where it compares them. The validation and the index then agree. + +|`skip` +|Is never a duplicate, as in a SQL unique predicate: `('en', null)` twice is valid, and the lookup is not made. + +|`value` +|Is a value that matches only another `null`: `('en', null)` conflicts with another `('en', null)` and with no row that has a country. This holds for the constrained property as well. + +|`wildcard` +|In the group, leaves its property out of the lookup, so it matches every row: `('en', null)` conflicts with `('en', 'US')`. A `null` constrained property is not checked. +|=== + +With rows `('en', 'US')` and `('en', null)`, a new `('en', null)` is a duplicate under `value` and `wildcard` and is valid under `skip`; a new `(null, 'US')` is a duplicate only under `value`. + +The unique index Hibernate creates for the group treats `null` values as follows, which is what `database` reads: + +[cols="2,1,3"] +|=== +|Database |Reading |Why + +|H2, PostgreSQL, MySQL, MariaDB +|`skip` +|`null` values are distinct in a unique index. + +|SQL Server, DB2 Review Comment: Verified: the table has a Hibernate column, and the Db2 rows from d7079ef36f match the 5.6 and 7.4 delegates. ########## grails-doc/src/en/ref/Constraints/unique.adoc: ########## @@ -64,6 +64,60 @@ username(unique: ['group', 'department']) In this example `username` must be unique in `group` and `department`. There might be same logins in different groups or different departments. +==== Null values in a composite key + +A property of the key can be `null`: a `country` that is optional in `language unique: 'country'`, a `parent` that is `null` for the top level nodes of a tree, or the constrained property itself when it is `nullable`. Whether such a row can be a duplicate of another is a question databases answer in two ways for their unique index, so the constraint has four readings of a `null`, selected per constraint or for the whole application: + +[cols="1,4"] +|=== +|Reading |A `null` in the key + +|`database` (default) +|Is read the way the unique index of the database does: `skip` where the index treats `null` values as distinct, `value` where it compares them. The validation and the index then agree. + +|`skip` +|Is never a duplicate, as in a SQL unique predicate: `('en', null)` twice is valid, and the lookup is not made. + +|`value` +|Is a value that matches only another `null`: `('en', null)` conflicts with another `('en', null)` and with no row that has a country. This holds for the constrained property as well. + +|`wildcard` +|In the group, leaves its property out of the lookup, so it matches every row: `('en', null)` conflicts with `('en', 'US')`. A `null` constrained property is not checked. +|=== + +With rows `('en', 'US')` and `('en', null)`, a new `('en', null)` is a duplicate under `value` and `wildcard` and is valid under `skip`; a new `(null, 'US')` is a duplicate only under `value`. Review Comment: Verified: the example lists the four rows, and the `(null, 'US')` sentence holds for them. ########## grails-doc/src/en/guide/upgrading/upgrading90x.adoc: ########## @@ -1151,3 +1151,34 @@ When upgrading an affected application: ---- See xref:theWebLayer#commandObjects[Command Objects] for a trait-based alternative to sharing a non-domain superclass with a domain class. + +==== 17. A Null in a Unique Constraint's Composite Key Follows the Database + +A `unique` constraint that names other properties, such as `url unique: 'httpMethod'`, validates the constrained property together with the properties of its group. +Before Grails 9, a group property whose value was `null` was left out of the query that looks for a conflicting row. +The constraint then checked the constrained property alone, so `new Language(language: 'en')` was rejected when a `Language` with `language: 'en'` and `country: 'US'` existed, although the two rows differ in `country`. +That was stricter than any reading of `null`: in SQL a `null` never conflicts, and where `null` is a value it conflicts only with another `null`. + +In Grails 9 the constraint reads a `null` in its key, in the group and in the constrained property, as the unique index of the database does, so that the validation and the index agree: + +* `skip` on H2, PostgreSQL, MySQL, MariaDB, SQL Server and DB2, whose index leaves a row with a `null` out: `('en', null)` is never a duplicate. Review Comment: SQL Server and Sybase are right now. The Db2 part went stale with d7079ef36f; see the new comment on the bullet. ########## grails-doc/src/en/guide/upgrading/upgrading90x.adoc: ########## @@ -1151,3 +1151,34 @@ When upgrading an affected application: ---- See xref:theWebLayer#commandObjects[Command Objects] for a trait-based alternative to sharing a non-domain superclass with a domain class. + +==== 17. A Null in a Unique Constraint's Composite Key Follows the Database Review Comment: Verified: it is section 20 now, after 18 and 19. -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: [email protected] For queries about this service, please contact Infrastructure at: [email protected]
