Unique Constraint with multiple columns ignores null value and throws validation error

[Fabschu]

Task List

• [x] Steps to reproduce provided
• [x] Stacktrace (if present) provided
• [x] Example that reproduces the problem uploaded to Github
• [x] Full description of the issue provided (see below)

Steps to Reproduce

1. Create Domain Class with unique constraint with multiple columns:

package sec
import org.springframework.http.HttpMethod

class Requestmap {
	String name
	String description
	String url
	String configAttribute
	HttpMethod httpMethod

	static mapping = {
		cache true
	}

	static constraints = {
		url blank: false, unique: 'httpMethod'
		configAttribute blank: false
		name blank:false
		description nullable:true
		httpMethod nullable:true
	}
}

2. Create and Save new Domain Objects

new Requestmap(url: '/test1/**', configAttribute: "IS_AUTHENTICATED_ANONYMOUSLY",name:"Assets", httpMethod:"POST").save(failOnError:true)
new Requestmap(url: '/test1/**', configAttribute: "IS_AUTHENTICATED_ANONYMOUSLY",name:"Assets").save(failOnError:true)

Expected Behaviour

• no validation exceptions
• both objects saved to the database

Actual Behaviour

• validation exception is thrown. Null value of httpMethod seems to be ignored in the created SQL-Query

grails.validation.ValidationException: Validation Error(s) occurred during save():
- Field error in object 'sec.Requestmap' on field 'url': rejected value [/test1/**]; codes [sec.Requestmap.url.unique.error.sec.Requestmap.url,sec.Requestmap.url.unique.error.url,sec.Requestmap.url.unique.error.java.lang.String,sec.Requestmap.url.unique.error,requestmap.url.unique.error.sec.Requestmap.url,requestmap.url.unique.error.url,requestmap.url.unique.error.java.lang.String,requestmap.url.unique.error,sec.Requestmap.url.unique.sec.Requestmap.url,sec.Requestmap.url.unique.url,sec.Requestmap.url.unique.java.lang.String,sec.Requestmap.url.unique,requestmap.url.unique.sec.Requestmap.url,requestmap.url.unique.url,requestmap.url.unique.java.lang.String,requestmap.url.unique,unique.sec.Requestmap.url,unique.url,unique.java.lang.String,unique]; arguments [url,class sec.Requestmap,/test1/**]; default message [Die Eigenschaft [{0}] des Typs [{1}] mit dem Wert [{2}] darf nur einmal vorkommen]

First SQL-Query with POST as httpMethod:

2020-07-21 11:24:32.301 DEBUG --- [  restartedMain] org.hibernate.SQL                        : select this_.id as y0_ from requestmap this_ where this_.url=? and this_.http_method=? limit ?
2020-07-21 11:24:32.301 DEBUG --- [  restartedMain] org.hibernate.SQL                        : select this_.id as y0_ from requestmap this_ where this_.url=? and this_.http_method=? limit ?
2020-07-21 11:24:32.307 TRACE --- [  restartedMain] o.h.type.descriptor.sql.BasicBinder      : binding parameter [1] as [VARCHAR] - [/test1/**]
2020-07-21 11:24:32.307 TRACE --- [  restartedMain] o.h.type.descriptor.sql.BasicBinder      : binding parameter [1] as [VARCHAR] - [/test1/**]
2020-07-21 11:24:32.307 TRACE --- [  restartedMain] o.h.type.descriptor.sql.BasicBinder      : binding parameter [2] as [VARCHAR] - [POST]
2020-07-21 11:24:32.307 TRACE --- [  restartedMain] o.h.type.descriptor.sql.BasicBinder      : binding parameter [2] as [VARCHAR] - [POST]

Second SQL-Query with null as httpMethod:

2020-07-21 11:24:32.344 DEBUG --- [  restartedMain] org.hibernate.SQL                        : select this_.id as y0_ from requestmap this_ where this_.url=? limit ?
2020-07-21 11:24:32.344 DEBUG --- [  restartedMain] org.hibernate.SQL                        : select this_.id as y0_ from requestmap this_ where this_.url=? limit ?
2020-07-21 11:24:32.344 TRACE --- [  restartedMain] o.h.type.descriptor.sql.BasicBinder      : binding parameter [1] as [VARCHAR] - [/test1/**]
2020-07-21 11:24:32.344 TRACE --- [  restartedMain] o.h.type.descriptor.sql.BasicBinder      : binding parameter [1] as [VARCHAR] - [/test1/**]

There is no where clause for the httpMethod column in the second SQL-Query

Environment Information

• Operating System: Windows 10
• **GORM Version:7.0.4.RELEASE
• **Grails Version:4.0.3
• **JDK Version:1.8.0_251

Example Application

https://github.com/Fabschu/multiple-unique-constraint-error

RequestMaps Objects are created in the Bootstrap.

[puneetbehl]

This is happening because when the group property value is null we only check for the constrained property so in the above case it will query to see if there is an existing Requestmap for a given URL. See the following: https://github.com/grails/grails-data-mapping/blob/master/grails-datastore-gorm/src/main/groovy/org/grails/datastore/gorm/validation/constraints/builtin/UniqueConstraint.groovy#L114

However, I am not sure if the check is intended or not or maybe we could make this configurable.

[sbglasius]

@puneetbehl I have run into this issue as well. My domain model can be simplified to

class Language {
   String language
   String country 
   
   static constraints = {
        language unique: 'country'
        country nullable: true
   }
}

I can see that if I do:

new Language(language: 'en', country: 'US').save(flush: true)

I see the sql that searches for language and country

now if I do:

new Language(language: 'en').save(flush: true)

I see the sql that searches only for language and thus retrieves the first value inserted.

I have solved this by using a custom validator instead. It works, but having the correct query for the unique constraint would be great!

For reference, here is my validator:

static constraints = {
        language nullable: false, validator: { value, object ->
            withNewSession {
                def lang = Language.findByLanguageAndCountry(value, object.country)
                if(lang && lang.id != object.id) {
                    return 'unique'
                }
            }
        }
        country nullable: true
}

[JasonTypesCodes]

We hit this on a project recently, and after some review of SQL standards, I think the current behavior is more correct and omitting the null is the right thing to do.

See: https://datacadamia.com/_media/data/type/relation/sql/sql1992.txt

A unique constraint is satisfied if and only if no two rows in a table have the same non-null values in the unique columns.

distinct: Two values are said to be not distinct if either: both are the null value, or they compare equal according to Subclause 8.2, "".

If you consider null to be unknown instead of a single shared value, I think the current behavior makes sense.

If you require logic that treats null differently, I think a custom validator is the right thing to do.