Refs #28643 -- Added NullIf database function.

Thanks Nick Pope, Mariusz Felisiak, and Tim Graham for reviews.

6 files changed, 78 insertions, 3 deletions

diff --git a/django/db/models/functions/__init__.py b/django/db/models/functions/__init__.py
index f005546eb0..2d58821653 100644
--- a/django/db/models/functions/__init__.py
+++ b/django/db/models/functions/__init__.py
@@ -1,4 +1,4 @@
-from .comparison import Cast, Coalesce, Greatest, Least
+from .comparison import Cast, Coalesce, Greatest, Least, NullIf
 from .datetime import (
     Extract, ExtractDay, ExtractHour, ExtractIsoYear, ExtractMinute,
     ExtractMonth, ExtractQuarter, ExtractSecond, ExtractWeek, ExtractWeekDay,
@@ -20,7 +20,7 @@ from .window import (
 
 __all__ = [
     # comparison and conversion
-    'Cast', 'Coalesce', 'Greatest', 'Least',
+    'Cast', 'Coalesce', 'Greatest', 'Least', 'NullIf',
     # datetime
     'Extract', 'ExtractDay', 'ExtractHour', 'ExtractMinute', 'ExtractMonth',
     'ExtractQuarter', 'ExtractSecond', 'ExtractWeek', 'ExtractWeekDay',
diff --git a/django/db/models/functions/comparison.py b/django/db/models/functions/comparison.py
index ff0f0e09d0..6c0b33bdcc 100644
--- a/django/db/models/functions/comparison.py
+++ b/django/db/models/functions/comparison.py
@@ -1,5 +1,5 @@
 """Database functions that do comparisons or type conversions."""
-from django.db.models.expressions import Func
+from django.db.models.expressions import Func, Value
 
 
 class Cast(Func):
@@ -103,3 +103,14 @@ class Least(Func):
     def as_sqlite(self, compiler, connection, **extra_context):
         """Use the MIN function on SQLite."""
         return super().as_sqlite(compiler, connection, function='MIN', **extra_context)
+
+
+class NullIf(Func):
+    function = 'NULLIF'
+    arity = 2
+
+    def as_oracle(self, compiler, connection, **extra_context):
+        expression1 = self.get_source_expressions()[0]
+        if isinstance(expression1, Value) and expression1.value is None:
+            raise ValueError('Oracle does not allow Value(None) for expression1.')
+        return super().as_sql(compiler, connection, **extra_context)
diff --git a/docs/ref/databases.txt b/docs/ref/databases.txt
index 04214a6560..0c584f7110 100644
--- a/docs/ref/databases.txt
+++ b/docs/ref/databases.txt
@@ -899,6 +899,8 @@ occur when an Oracle datatype is used as a column name.  In
 particular, take care to avoid using the names ``date``,
 ``timestamp``, ``number`` or ``float`` as a field name.
 
+.. _oracle-null-empty-strings:
+
 NULL and empty strings
 ----------------------
 
diff --git a/docs/ref/models/database-functions.txt b/docs/ref/models/database-functions.txt
index f1affb2b6f..2661b65c78 100644
--- a/docs/ref/models/database-functions.txt
+++ b/docs/ref/models/database-functions.txt
@@ -149,6 +149,25 @@ will result in a database error.
     The PostgreSQL behavior can be emulated using ``Coalesce`` if you know
     a sensible maximum value to provide as a default.
 
+``NullIf``
+----------
+
+.. class:: NullIf(expression1, expression2)
+
+.. versionadded:: 2.2
+
+Accepts two expressions and returns ``None`` if they are equal, otherwise
+returns ``expression1``.
+
+.. admonition:: Caveats on Oracle
+
+    Due to an :ref:`Oracle convention<oracle-null-empty-strings>`, this
+    function returns the empty string instead of ``None`` when the expressions
+    are of type :class:`~django.db.models.CharField`.
+
+    Passing ``Value(None)`` to ``expression1`` is prohibited on Oracle since
+    Oracle doesn't accept ``NULL`` as the first argument.
+
 .. _date-functions:
 
 Date functions
diff --git a/docs/releases/2.2.txt b/docs/releases/2.2.txt
index 843aadd24f..bc950eb88e 100644
--- a/docs/releases/2.2.txt
+++ b/docs/releases/2.2.txt
@@ -220,6 +220,9 @@ Models
 
 * Added many :ref:`math database functions <math-functions>`.
 
+* The new :class:`~django.db.models.functions.NullIf` database function
+  returns ``None`` if the two expressions are equal.
+
 * Setting the new ``ignore_conflicts`` parameter of
   :meth:`.QuerySet.bulk_create` to ``True`` tells the database to ignore
   failure to insert rows that fail uniqueness constraints or other checks.
diff --git a/tests/db_functions/comparison/test_nullif.py b/tests/db_functions/comparison/test_nullif.py
new file mode 100644
index 0000000000..36f881ed57
--- /dev/null
+++ b/tests/db_functions/comparison/test_nullif.py
@@ -0,0 +1,40 @@
+from unittest import skipUnless
+
+from django.db import connection
+from django.db.models import Value
+from django.db.models.functions import NullIf
+from django.test import TestCase
+
+from ..models import Author
+
+
+class NullIfTests(TestCase):
+
+    @classmethod
+    def setUpTestData(cls):
+        Author.objects.create(name='John Smith', alias='smithj')
+        Author.objects.create(name='Rhonda', alias='Rhonda')
+
+    def test_basic(self):
+        authors = Author.objects.annotate(nullif=NullIf('alias', 'name')).values_list('nullif')
+        self.assertSequenceEqual(
+            authors, [
+                ('smithj',),
+                ('' if connection.features.interprets_empty_strings_as_nulls else None,)
+            ]
+        )
+
+    def test_null_argument(self):
+        authors = Author.objects.annotate(nullif=NullIf('name', Value(None))).values_list('nullif')
+        self.assertSequenceEqual(authors, [('John Smith',), ('Rhonda',)])
+
+    def test_too_few_args(self):
+        msg = "'NullIf' takes exactly 2 arguments (1 given)"
+        with self.assertRaisesMessage(TypeError, msg):
+            NullIf('name')
+
+    @skipUnless(connection.vendor == 'oracle', 'Oracle specific test for NULL-literal')
+    def test_null_literal(self):
+        msg = 'Oracle does not allow Value(None) for expression1.'
+        with self.assertRaisesMessage(ValueError, msg):
+            list(Author.objects.annotate(nullif=NullIf(Value(None), 'name')).values_list('nullif'))