Gorm Gorm MSSQL conditional unique index not working

Description

As per the documentation on https://gorm.io/docs/indexes.html#Index-Tag gorm supports conditional indexing using a where condition. But it seems to be not respected in MSSQL / TSQL

Steps to reproduce

https://github.com/go-gorm/playground/pull/570

type User struct {
    ID string `gorm:"primaryKey"`
    IssuerID string `gorm:"index:unique_issuer,class:UNIQUE,where: issuer_id IS NOT NULL"`
}

the above code results in the following error when creating the columns using the code

mssql: The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.abc' and the index name 'unique_issuer'. The duplicate key value is (<NULL>).
[5.309ms] [rows:0] CREATE UNIQUE INDEX "unique_issuer" ON "abc"("issuer_id")
time="2023-02-17T15:19:23+05:30" level=fatal msg="an error 'mssql: The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.abc' and the index name 'unique_issuer'. The duplicate key value is (<NULL>).' was not expected when opening a database connection"
exit status 1

on MSSQL / TSQL the following code works seamlessly CREATE UNIQUE INDEX unique_issuers ON abc(issuer_id) where issuer_id IS NOT NULL

this means the gorm is unable to translate the go struct format to the native sql commands

Comment From: github-actions[bot]

The issue has been automatically marked as stale as it missing playground pull request link, which is important to help others understand your issue effectively and make sure the issue hasn't been fixed on latest master, checkout https://github.com/go-gorm/playground for details. it will be closed in 30 days if no further activity occurs. if you are asking question, please use the Question template, most likely your question already answered https://github.com/go-gorm/gorm/issues or described in the document https://gorm.io ✨ Search Before Asking ✨

Comment From: black-06

This problem is not due to grom. Because UNIQUE Constraints in MS SQL Server.

Comment From: black-06

Unlike PRIMARY KEY constraints, UNIQUE constraints allow for the value NULL. However, as with any value participating in a UNIQUE constraint, only one null value is allowed per column.

So where: issuer_id IS NOT NULL is invalid actually.

Comment From: jozephp

Thanks @black-06 for looking at this

MSSQL / TSQL supports conditional unique indexes as documented here & here It does accepts WHERE for UNIQUE INDEX

Comment From: black-06

I'm sorry, I'm not familiar with mssql. I'll try it later, and try to fix it if possible.

Comment From: black-06

Yes you are right, where is unimplemented. At present, you can replace it with option.

type User struct {
    ID string `gorm:"primaryKey"`
    IssuerID string `gorm:"index:unique_issuer,class:UNIQUE, option: where issuer_id IS NOT NULL"`
}

CREATE UNIQUE INDEX "unique_issuer" ON "users"("issuer_id") where issuer_id IS NOT NULL

Comment From: jozephp

Thanks @black-06 for suggesting the fix and also raising the PR to fix the where condition

Comment From: black-06

It's my pleasure

Comment From: pradnyoday

Hi @black-06 , We are encountering same issue as this. We have raised https://github.com/go-gorm/gorm/issues/6597 this issue. Can you please look into it?