Support for IS DISTINCT FROM and IS NOT DISTINCT FROM operators

[gabegorelick]

Feature Description

IS DISTINCT FROM and the related IS NOT DISTINCT FROM operators are defined in SQL:2003 as a null-safe way to compare two values. Their advantage over <> and =, respectively, is that x IS DISTINCT FROM NULL evaluates to FALSE for all non-null x while x <> NULL evaluates to NULL for all x (including NULL).

Supported in Postgres since at least 8.0: https://www.postgresql.org/docs/8.0/functions-comparison.html

MySQL has a related "null-safe equals operator" <=>: https://dev.mysql.com/doc/refman/5.7/en/comparison-operators.html#operator_equal-to

SQLite has an IS operator that behaves identically: https://www.sqlite.org/lang_expr.html#isisnot. Note that this is more general than the IS NULL/TRUE/FALSE/UNKNOWN operators that Postgres and other database systems implement.

To my knowledge, SQL Server has nothing comparable: https://docs.microsoft.com/en-us/sql/t-sql/language-elements/comparison-operators-transact-sql?view=sql-server-ver15

Is your feature request related to a problem? Please describe.

Finding all rows that match a predicate, without worrying about the predicate being NULL

Describe the solution you'd like

I see 2 potential solutions. The simplest would be to add a distinctFrom operator. The alternative would be to amend the existing is operator to evaluate to IS DISTINCT FROM on Postgres and <=> on MySQL.

On Postgres, IS must be followed by NULL, TRUE, FALSE, or UNKNOWN (or a NOT and then one of NULL, TRUE, FALSE, or UNKNOWN). In my testing, a clause like {[Op.is]: 'foo'} will output IS 'foo', which is a syntax error. To match the user's intent, Sequelize should be outputting IS DISTINCT FROM 'foo'.

I assume Sequelize currently has similar behavior on MySQL (outputting foo IS 'foo' instead of foo <=> 'foo'), but I haven't tested it.

Why should this be in Sequelize

Adds a construct implemented in one form or another by most supported database systems.

Describe alternatives/workarounds you've considered

This can be worked around by using equality comparison coupled with IS NULL. For example, to find all values not equal to x:

Model.findAll({
    where: {
        foo: {
            [Op.or]: {
                [Op.ne]: x,
                [Op.not]: null // Sequelize converts this to IS NOT NULL
            }
        }
    }
});

Additional context

https://wiki.postgresql.org/wiki/Is_distinct_from
https://www.postgresql.org/docs/current/functions-comparison.html
https://www.sqlite.org/lang_expr.html#:~:text=In%20other%20words%2C%20the%20operator,it%20does%20in%20ANSI%2DC.&text=The%20IS%20and%20IS%20NOT,evaluates%20to%200%20(false).
https://dev.mysql.com/doc/refman/5.7/en/comparison-operators.html#operator_equal-to
https://mariadb.com/kb/en/null-safe-equal

Feature Request Checklist

Is this feature dialect-specific?

• No. This issue is relevant to Sequelize as a whole.
• Yes. This issue only applies to the following dialect(s):Postgres, MySQL, MariaDB

Would you be willing to implement this feature by submitting a Pull Request?

• Yes, I have the time and I know how to start.
• Yes, I have the time but I don't know how to start, I would need guidance.
• No, I don't have the time, although I believe I could do it if I had the time...
• No, I don't have the time and I wouldn't even know how to start.

[aufula]

@gabegorelick same question . I solve this with literal() function

{
   where: {
       ftype: {
        [Op.is]: literal(`distinct from 'html'`)
      }
   }
}

[github-actions]

This issue has been automatically marked as stale because it has been open for 7 days without activity. It will be closed if no further activity occurs. If this is still an issue, just leave a comment or remove the "stale" label. 🙂

[Betree]

Would love to see this! Something like:

Model.findAll({
  where: {
    foo: { [Op.df]: 'Hello World' }
  }
});

[celsowm]

SQL SERVER 2022 (16) now supports too: https://learn.microsoft.com/en-us/sql/t-sql/queries/is-distinct-from-transact-sql?view=sql-server-ver16

[max-marcus]

Reporting from mid 2024...

Found this thread since I was running into this issue of [Op.ne]: true not including null values in the result
[Op.not]: true works - it includes null and false values in the result