Reorganized text db function tests.

15 files changed, 609 insertions, 0 deletions

diff --git a/tests/db_functions/text/__init__.py b/tests/db_functions/text/__init__.py
new file mode 100644
index 0000000000..e69de29bb2
--- /dev/null
+++ b/tests/db_functions/text/__init__.py
diff --git a/tests/db_functions/text/test_chr.py b/tests/db_functions/text/test_chr.py
new file mode 100644
index 0000000000..d48793457a
--- /dev/null
+++ b/tests/db_functions/text/test_chr.py
@@ -0,0 +1,32 @@
+from django.db.models import IntegerField
+from django.db.models.functions import Chr, Left, Ord
+from django.test import TestCase
+
+from ..models import Author
+
+
+class ChrTests(TestCase):
+    @classmethod
+    def setUpTestData(cls):
+        cls.john = Author.objects.create(name='John Smith', alias='smithj')
+        cls.elena = Author.objects.create(name='Élena Jordan', alias='elena')
+        cls.rhonda = Author.objects.create(name='Rhonda')
+
+    def test_basic(self):
+        authors = Author.objects.annotate(first_initial=Left('name', 1))
+        self.assertCountEqual(authors.filter(first_initial=Chr(ord('J'))), [self.john])
+        self.assertCountEqual(authors.exclude(first_initial=Chr(ord('J'))), [self.elena, self.rhonda])
+
+    def test_non_ascii(self):
+        authors = Author.objects.annotate(first_initial=Left('name', 1))
+        self.assertCountEqual(authors.filter(first_initial=Chr(ord('É'))), [self.elena])
+        self.assertCountEqual(authors.exclude(first_initial=Chr(ord('É'))), [self.john, self.rhonda])
+
+    def test_transform(self):
+        try:
+            IntegerField.register_lookup(Chr)
+            authors = Author.objects.annotate(name_code_point=Ord('name'))
+            self.assertCountEqual(authors.filter(name_code_point__chr=Chr(ord('J'))), [self.john])
+            self.assertCountEqual(authors.exclude(name_code_point__chr=Chr(ord('J'))), [self.elena, self.rhonda])
+        finally:
+            IntegerField._unregister_lookup(Chr)
diff --git a/tests/db_functions/text/test_concat.py b/tests/db_functions/text/test_concat.py
new file mode 100644
index 0000000000..9850b2fd0d
--- /dev/null
+++ b/tests/db_functions/text/test_concat.py
@@ -0,0 +1,81 @@
+from unittest import skipUnless
+
+from django.db import connection
+from django.db.models import CharField, TextField, Value as V
+from django.db.models.functions import Concat, ConcatPair, Upper
+from django.test import TestCase
+from django.utils import timezone
+
+from ..models import Article, Author
+
+lorem_ipsum = """
+    Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod
+    tempor incididunt ut labore et dolore magna aliqua."""
+
+
+class ConcatTests(TestCase):
+
+    def test_basic(self):
+        Author.objects.create(name='Jayden')
+        Author.objects.create(name='John Smith', alias='smithj', goes_by='John')
+        Author.objects.create(name='Margaret', goes_by='Maggie')
+        Author.objects.create(name='Rhonda', alias='adnohR')
+        authors = Author.objects.annotate(joined=Concat('alias', 'goes_by'))
+        self.assertQuerysetEqual(
+            authors.order_by('name'), [
+                '',
+                'smithjJohn',
+                'Maggie',
+                'adnohR',
+            ],
+            lambda a: a.joined
+        )
+
+    def test_gt_two_expressions(self):
+        with self.assertRaisesMessage(ValueError, 'Concat must take at least two expressions'):
+            Author.objects.annotate(joined=Concat('alias'))
+
+    def test_many(self):
+        Author.objects.create(name='Jayden')
+        Author.objects.create(name='John Smith', alias='smithj', goes_by='John')
+        Author.objects.create(name='Margaret', goes_by='Maggie')
+        Author.objects.create(name='Rhonda', alias='adnohR')
+        authors = Author.objects.annotate(
+            joined=Concat('name', V(' ('), 'goes_by', V(')'), output_field=CharField()),
+        )
+        self.assertQuerysetEqual(
+            authors.order_by('name'), [
+                'Jayden ()',
+                'John Smith (John)',
+                'Margaret (Maggie)',
+                'Rhonda ()',
+            ],
+            lambda a: a.joined
+        )
+
+    def test_mixed_char_text(self):
+        Article.objects.create(title='The Title', text=lorem_ipsum, written=timezone.now())
+        article = Article.objects.annotate(
+            title_text=Concat('title', V(' - '), 'text', output_field=TextField()),
+        ).get(title='The Title')
+        self.assertEqual(article.title + ' - ' + article.text, article.title_text)
+        # Wrap the concat in something else to ensure that text is returned
+        # rather than bytes.
+        article = Article.objects.annotate(
+            title_text=Upper(Concat('title', V(' - '), 'text', output_field=TextField())),
+        ).get(title='The Title')
+        expected = article.title + ' - ' + article.text
+        self.assertEqual(expected.upper(), article.title_text)
+
+    @skipUnless(connection.vendor == 'sqlite', "sqlite specific implementation detail.")
+    def test_coalesce_idempotent(self):
+        pair = ConcatPair(V('a'), V('b'))
+        # Check nodes counts
+        self.assertEqual(len(list(pair.flatten())), 3)
+        self.assertEqual(len(list(pair.coalesce().flatten())), 7)  # + 2 Coalesce + 2 Value()
+        self.assertEqual(len(list(pair.flatten())), 3)
+
+    def test_sql_generation_idempotency(self):
+        qs = Article.objects.annotate(description=Concat('title', V(': '), 'summary'))
+        # Multiple compilations should not alter the generated query.
+        self.assertEqual(str(qs.query), str(qs.all().query))
diff --git a/tests/db_functions/text/test_left.py b/tests/db_functions/text/test_left.py
new file mode 100644
index 0000000000..5bb3d6c4fa
--- /dev/null
+++ b/tests/db_functions/text/test_left.py
@@ -0,0 +1,27 @@
+from django.db.models import CharField, Value
+from django.db.models.functions import Left, Lower
+from django.test import TestCase
+
+from ..models import Author
+
+
+class LeftTests(TestCase):
+    @classmethod
+    def setUpTestData(cls):
+        Author.objects.create(name='John Smith', alias='smithj')
+        Author.objects.create(name='Rhonda')
+
+    def test_basic(self):
+        authors = Author.objects.annotate(name_part=Left('name', 5))
+        self.assertQuerysetEqual(authors.order_by('name'), ['John ', 'Rhond'], lambda a: a.name_part)
+        # If alias is null, set it to the first 2 lower characters of the name.
+        Author.objects.filter(alias__isnull=True).update(alias=Lower(Left('name', 2)))
+        self.assertQuerysetEqual(authors.order_by('name'), ['smithj', 'rh'], lambda a: a.alias)
+
+    def test_invalid_length(self):
+        with self.assertRaisesMessage(ValueError, "'length' must be greater than 0"):
+            Author.objects.annotate(raises=Left('name', 0))
+
+    def test_expressions(self):
+        authors = Author.objects.annotate(name_part=Left('name', Value(3), output_field=CharField()))
+        self.assertQuerysetEqual(authors.order_by('name'), ['Joh', 'Rho'], lambda a: a.name_part)
diff --git a/tests/db_functions/text/test_length.py b/tests/db_functions/text/test_length.py
new file mode 100644
index 0000000000..8fbe6887aa
--- /dev/null
+++ b/tests/db_functions/text/test_length.py
@@ -0,0 +1,48 @@
+from django.db.models import CharField
+from django.db.models.functions import Length
+from django.test import TestCase
+
+from ..models import Author
+
+
+class LengthTests(TestCase):
+
+    def test_basic(self):
+        Author.objects.create(name='John Smith', alias='smithj')
+        Author.objects.create(name='Rhonda')
+        authors = Author.objects.annotate(
+            name_length=Length('name'),
+            alias_length=Length('alias'),
+        )
+        self.assertQuerysetEqual(
+            authors.order_by('name'), [(10, 6), (6, None)],
+            lambda a: (a.name_length, a.alias_length)
+        )
+        self.assertEqual(authors.filter(alias_length__lte=Length('name')).count(), 1)
+
+    def test_ordering(self):
+        Author.objects.create(name='John Smith', alias='smithj')
+        Author.objects.create(name='John Smith', alias='smithj1')
+        Author.objects.create(name='Rhonda', alias='ronny')
+        authors = Author.objects.order_by(Length('name'), Length('alias'))
+        self.assertQuerysetEqual(
+            authors, [
+                ('Rhonda', 'ronny'),
+                ('John Smith', 'smithj'),
+                ('John Smith', 'smithj1'),
+            ],
+            lambda a: (a.name, a.alias)
+        )
+
+    def test_transform(self):
+        try:
+            CharField.register_lookup(Length)
+            Author.objects.create(name='John Smith', alias='smithj')
+            Author.objects.create(name='Rhonda')
+            authors = Author.objects.filter(name__length__gt=7)
+            self.assertQuerysetEqual(
+                authors.order_by('name'), ['John Smith'],
+                lambda a: a.name
+            )
+        finally:
+            CharField._unregister_lookup(Length)
diff --git a/tests/db_functions/text/test_lower.py b/tests/db_functions/text/test_lower.py
new file mode 100644
index 0000000000..f438682f1d
--- /dev/null
+++ b/tests/db_functions/text/test_lower.py
@@ -0,0 +1,42 @@
+from django.db.models import CharField
+from django.db.models.functions import Lower
+from django.test import TestCase
+
+from ..models import Author
+
+
+class LowerTests(TestCase):
+
+    def test_basic(self):
+        Author.objects.create(name='John Smith', alias='smithj')
+        Author.objects.create(name='Rhonda')
+        authors = Author.objects.annotate(lower_name=Lower('name'))
+        self.assertQuerysetEqual(
+            authors.order_by('name'), ['john smith', 'rhonda'],
+            lambda a: a.lower_name
+        )
+        Author.objects.update(name=Lower('name'))
+        self.assertQuerysetEqual(
+            authors.order_by('name'), [
+                ('john smith', 'john smith'),
+                ('rhonda', 'rhonda'),
+            ],
+            lambda a: (a.lower_name, a.name)
+        )
+
+    def test_num_args(self):
+        with self.assertRaisesMessage(TypeError, "'Lower' takes exactly 1 argument (2 given)"):
+            Author.objects.update(name=Lower('name', 'name'))
+
+    def test_transform(self):
+        try:
+            CharField.register_lookup(Lower)
+            Author.objects.create(name='John Smith', alias='smithj')
+            Author.objects.create(name='Rhonda')
+            authors = Author.objects.filter(name__lower__exact='john smith')
+            self.assertQuerysetEqual(
+                authors.order_by('name'), ['John Smith'],
+                lambda a: a.name
+            )
+        finally:
+            CharField._unregister_lookup(Lower)
diff --git a/tests/db_functions/text/test_ord.py b/tests/db_functions/text/test_ord.py
new file mode 100644
index 0000000000..c4ad730f6c
--- /dev/null
+++ b/tests/db_functions/text/test_ord.py
@@ -0,0 +1,27 @@
+from django.db.models import CharField, Value
+from django.db.models.functions import Left, Ord
+from django.test import TestCase
+
+from ..models import Author
+
+
+class OrdTests(TestCase):
+    @classmethod
+    def setUpTestData(cls):
+        cls.john = Author.objects.create(name='John Smith', alias='smithj')
+        cls.elena = Author.objects.create(name='Élena Jordan', alias='elena')
+        cls.rhonda = Author.objects.create(name='Rhonda')
+
+    def test_basic(self):
+        authors = Author.objects.annotate(name_part=Ord('name'))
+        self.assertCountEqual(authors.filter(name_part__gt=Ord(Value('John'))), [self.elena, self.rhonda])
+        self.assertCountEqual(authors.exclude(name_part__gt=Ord(Value('John'))), [self.john])
+
+    def test_transform(self):
+        try:
+            CharField.register_lookup(Ord)
+            authors = Author.objects.annotate(first_initial=Left('name', 1))
+            self.assertCountEqual(authors.filter(first_initial__ord=ord('J')), [self.john])
+            self.assertCountEqual(authors.exclude(first_initial__ord=ord('J')), [self.elena, self.rhonda])
+        finally:
+            CharField._unregister_lookup(Ord)
diff --git a/tests/db_functions/text/test_pad.py b/tests/db_functions/text/test_pad.py
new file mode 100644
index 0000000000..2cec280b4d
--- /dev/null
+++ b/tests/db_functions/text/test_pad.py
@@ -0,0 +1,44 @@
+from django.db.models import CharField, Value
+from django.db.models.functions import Length, LPad, RPad
+from django.test import TestCase
+
+from ..models import Author
+
+
+class PadTests(TestCase):
+    def test_pad(self):
+        Author.objects.create(name='John', alias='j')
+        tests = (
+            (LPad('name', 7, Value('xy')), 'xyxJohn'),
+            (RPad('name', 7, Value('xy')), 'Johnxyx'),
+            (LPad('name', 6, Value('x')), 'xxJohn'),
+            (RPad('name', 6, Value('x')), 'Johnxx'),
+            # The default pad string is a space.
+            (LPad('name', 6), '  John'),
+            (RPad('name', 6), 'John  '),
+            # If string is longer than length it is truncated.
+            (LPad('name', 2), 'Jo'),
+            (RPad('name', 2), 'Jo'),
+            (LPad('name', 0), ''),
+            (RPad('name', 0), ''),
+        )
+        for function, padded_name in tests:
+            with self.subTest(function=function):
+                authors = Author.objects.annotate(padded_name=function)
+                self.assertQuerysetEqual(authors, [padded_name], lambda a: a.padded_name, ordered=False)
+
+    def test_pad_negative_length(self):
+        for function in (LPad, RPad):
+            with self.subTest(function=function):
+                with self.assertRaisesMessage(ValueError, "'length' must be greater or equal to 0."):
+                    function('name', -1)
+
+    def test_combined_with_length(self):
+        Author.objects.create(name='Rhonda', alias='john_smith')
+        Author.objects.create(name='♥♣♠', alias='bytes')
+        authors = Author.objects.annotate(filled=LPad('name', Length('alias'), output_field=CharField()))
+        self.assertQuerysetEqual(
+            authors.order_by('alias'),
+            ['  ♥♣♠', '    Rhonda'],
+            lambda a: a.filled,
+        )
diff --git a/tests/db_functions/text/test_repeat.py b/tests/db_functions/text/test_repeat.py
new file mode 100644
index 0000000000..f45544d97e
--- /dev/null
+++ b/tests/db_functions/text/test_repeat.py
@@ -0,0 +1,24 @@
+from django.db.models import CharField, Value
+from django.db.models.functions import Length, Repeat
+from django.test import TestCase
+
+from ..models import Author
+
+
+class RepeatTests(TestCase):
+    def test_basic(self):
+        Author.objects.create(name='John', alias='xyz')
+        tests = (
+            (Repeat('name', 0), ''),
+            (Repeat('name', 2), 'JohnJohn'),
+            (Repeat('name', Length('alias'), output_field=CharField()), 'JohnJohnJohn'),
+            (Repeat(Value('x'), 3, output_field=CharField()), 'xxx'),
+        )
+        for function, repeated_text in tests:
+            with self.subTest(function=function):
+                authors = Author.objects.annotate(repeated_text=function)
+                self.assertQuerysetEqual(authors, [repeated_text], lambda a: a.repeated_text, ordered=False)
+
+    def test_negative_number(self):
+        with self.assertRaisesMessage(ValueError, "'number' must be greater or equal to 0."):
+            Repeat('name', -1)
diff --git a/tests/db_functions/text/test_replace.py b/tests/db_functions/text/test_replace.py
new file mode 100644
index 0000000000..ae87781b8c
--- /dev/null
+++ b/tests/db_functions/text/test_replace.py
@@ -0,0 +1,55 @@
+from django.db.models import F, Value
+from django.db.models.functions import Concat, Replace
+from django.test import TestCase
+
+from ..models import Author
+
+
+class ReplaceTests(TestCase):
+
+    @classmethod
+    def setUpTestData(cls):
+        Author.objects.create(name='George R. R. Martin')
+        Author.objects.create(name='J. R. R. Tolkien')
+
+    def test_replace_with_empty_string(self):
+        qs = Author.objects.annotate(
+            without_middlename=Replace(F('name'), Value('R. R. '), Value('')),
+        )
+        self.assertQuerysetEqual(qs, [
+            ('George R. R. Martin', 'George Martin'),
+            ('J. R. R. Tolkien', 'J. Tolkien'),
+        ], transform=lambda x: (x.name, x.without_middlename), ordered=False)
+
+    def test_case_sensitive(self):
+        qs = Author.objects.annotate(same_name=Replace(F('name'), Value('r. r.'), Value('')))
+        self.assertQuerysetEqual(qs, [
+            ('George R. R. Martin', 'George R. R. Martin'),
+            ('J. R. R. Tolkien', 'J. R. R. Tolkien'),
+        ], transform=lambda x: (x.name, x.same_name), ordered=False)
+
+    def test_replace_expression(self):
+        qs = Author.objects.annotate(same_name=Replace(
+            Concat(Value('Author: '), F('name')), Value('Author: '), Value('')),
+        )
+        self.assertQuerysetEqual(qs, [
+            ('George R. R. Martin', 'George R. R. Martin'),
+            ('J. R. R. Tolkien', 'J. R. R. Tolkien'),
+        ], transform=lambda x: (x.name, x.same_name), ordered=False)
+
+    def test_update(self):
+        Author.objects.update(
+            name=Replace(F('name'), Value('R. R. '), Value('')),
+        )
+        self.assertQuerysetEqual(Author.objects.all(), [
+            ('George Martin'),
+            ('J. Tolkien'),
+        ], transform=lambda x: x.name, ordered=False)
+
+    def test_replace_with_default_arg(self):
+        # The default replacement is an empty string.
+        qs = Author.objects.annotate(same_name=Replace(F('name'), Value('R. R. ')))
+        self.assertQuerysetEqual(qs, [
+            ('George R. R. Martin', 'George Martin'),
+            ('J. R. R. Tolkien', 'J. Tolkien'),
+        ], transform=lambda x: (x.name, x.same_name), ordered=False)
diff --git a/tests/db_functions/text/test_right.py b/tests/db_functions/text/test_right.py
new file mode 100644
index 0000000000..6dcbcc18f5
--- /dev/null
+++ b/tests/db_functions/text/test_right.py
@@ -0,0 +1,27 @@
+from django.db.models import CharField, Value
+from django.db.models.functions import Lower, Right
+from django.test import TestCase
+
+from ..models import Author
+
+
+class RightTests(TestCase):
+    @classmethod
+    def setUpTestData(cls):
+        Author.objects.create(name='John Smith', alias='smithj')
+        Author.objects.create(name='Rhonda')
+
+    def test_basic(self):
+        authors = Author.objects.annotate(name_part=Right('name', 5))
+        self.assertQuerysetEqual(authors.order_by('name'), ['Smith', 'honda'], lambda a: a.name_part)
+        # If alias is null, set it to the first 2 lower characters of the name.
+        Author.objects.filter(alias__isnull=True).update(alias=Lower(Right('name', 2)))
+        self.assertQuerysetEqual(authors.order_by('name'), ['smithj', 'da'], lambda a: a.alias)
+
+    def test_invalid_length(self):
+        with self.assertRaisesMessage(ValueError, "'length' must be greater than 0"):
+            Author.objects.annotate(raises=Right('name', 0))
+
+    def test_expressions(self):
+        authors = Author.objects.annotate(name_part=Right('name', Value(3), output_field=CharField()))
+        self.assertQuerysetEqual(authors.order_by('name'), ['ith', 'nda'], lambda a: a.name_part)
diff --git a/tests/db_functions/text/test_strindex.py b/tests/db_functions/text/test_strindex.py
new file mode 100644
index 0000000000..1670df00fd
--- /dev/null
+++ b/tests/db_functions/text/test_strindex.py
@@ -0,0 +1,66 @@
+from django.db.models import Value
+from django.db.models.functions import StrIndex
+from django.test import TestCase
+from django.utils import timezone
+
+from ..models import Article, Author
+
+
+class StrIndexTests(TestCase):
+    def test_annotate_charfield(self):
+        Author.objects.create(name='George. R. R. Martin')
+        Author.objects.create(name='J. R. R. Tolkien')
+        Author.objects.create(name='Terry Pratchett')
+        authors = Author.objects.annotate(fullstop=StrIndex('name', Value('R.')))
+        self.assertQuerysetEqual(authors.order_by('name'), [9, 4, 0], lambda a: a.fullstop)
+
+    def test_annotate_textfield(self):
+        Article.objects.create(
+            title='How to Django',
+            text='This is about How to Django.',
+            written=timezone.now(),
+        )
+        Article.objects.create(
+            title='How to Tango',
+            text="Won't find anything here.",
+            written=timezone.now(),
+        )
+        articles = Article.objects.annotate(title_pos=StrIndex('text', 'title'))
+        self.assertQuerysetEqual(articles.order_by('title'), [15, 0], lambda a: a.title_pos)
+
+    def test_order_by(self):
+        Author.objects.create(name='Terry Pratchett')
+        Author.objects.create(name='J. R. R. Tolkien')
+        Author.objects.create(name='George. R. R. Martin')
+        self.assertQuerysetEqual(
+            Author.objects.order_by(StrIndex('name', Value('R.')).asc()), [
+                'Terry Pratchett',
+                'J. R. R. Tolkien',
+                'George. R. R. Martin',
+            ],
+            lambda a: a.name
+        )
+        self.assertQuerysetEqual(
+            Author.objects.order_by(StrIndex('name', Value('R.')).desc()), [
+                'George. R. R. Martin',
+                'J. R. R. Tolkien',
+                'Terry Pratchett',
+            ],
+            lambda a: a.name
+        )
+
+    def test_unicode_values(self):
+        Author.objects.create(name='ツリー')
+        Author.objects.create(name='皇帝')
+        Author.objects.create(name='皇帝 ツリー')
+        authors = Author.objects.annotate(sb=StrIndex('name', Value('リ')))
+        self.assertQuerysetEqual(authors.order_by('name'), [2, 0, 5], lambda a: a.sb)
+
+    def test_filtering(self):
+        Author.objects.create(name='George. R. R. Martin')
+        Author.objects.create(name='Terry Pratchett')
+        self.assertQuerysetEqual(
+            Author.objects.annotate(middle_name=StrIndex('name', Value('R.'))).filter(middle_name__gt=0),
+            ['George. R. R. Martin'],
+            lambda a: a.name
+        )
diff --git a/tests/db_functions/text/test_substr.py b/tests/db_functions/text/test_substr.py
new file mode 100644
index 0000000000..5cc12c0288
--- /dev/null
+++ b/tests/db_functions/text/test_substr.py
@@ -0,0 +1,53 @@
+from django.db.models import CharField, Value as V
+from django.db.models.functions import Lower, StrIndex, Substr, Upper
+from django.test import TestCase
+
+from ..models import Author
+
+
+class SubstrTests(TestCase):
+
+    def test_basic(self):
+        Author.objects.create(name='John Smith', alias='smithj')
+        Author.objects.create(name='Rhonda')
+        authors = Author.objects.annotate(name_part=Substr('name', 5, 3))
+        self.assertQuerysetEqual(
+            authors.order_by('name'), [' Sm', 'da'],
+            lambda a: a.name_part
+        )
+        authors = Author.objects.annotate(name_part=Substr('name', 2))
+        self.assertQuerysetEqual(
+            authors.order_by('name'), ['ohn Smith', 'honda'],
+            lambda a: a.name_part
+        )
+        # If alias is null, set to first 5 lower characters of the name.
+        Author.objects.filter(alias__isnull=True).update(
+            alias=Lower(Substr('name', 1, 5)),
+        )
+        self.assertQuerysetEqual(
+            authors.order_by('name'), ['smithj', 'rhond'],
+            lambda a: a.alias
+        )
+
+    def test_start(self):
+        Author.objects.create(name='John Smith', alias='smithj')
+        a = Author.objects.annotate(
+            name_part_1=Substr('name', 1),
+            name_part_2=Substr('name', 2),
+        ).get(alias='smithj')
+
+        self.assertEqual(a.name_part_1[1:], a.name_part_2)
+
+    def test_pos_gt_zero(self):
+        with self.assertRaisesMessage(ValueError, "'pos' must be greater than 0"):
+            Author.objects.annotate(raises=Substr('name', 0))
+
+    def test_expressions(self):
+        Author.objects.create(name='John Smith', alias='smithj')
+        Author.objects.create(name='Rhonda')
+        substr = Substr(Upper('name'), StrIndex('name', V('h')), 5, output_field=CharField())
+        authors = Author.objects.annotate(name_part=substr)
+        self.assertQuerysetEqual(
+            authors.order_by('name'), ['HN SM', 'HONDA'],
+            lambda a: a.name_part
+        )
diff --git a/tests/db_functions/text/test_trim.py b/tests/db_functions/text/test_trim.py
new file mode 100644
index 0000000000..3144aef028
--- /dev/null
+++ b/tests/db_functions/text/test_trim.py
@@ -0,0 +1,40 @@
+from django.db.models import CharField
+from django.db.models.functions import LTrim, RTrim, Trim
+from django.test import TestCase
+
+from ..models import Author
+
+
+class TrimTests(TestCase):
+    def test_trim(self):
+        Author.objects.create(name='  John ', alias='j')
+        Author.objects.create(name='Rhonda', alias='r')
+        authors = Author.objects.annotate(
+            ltrim=LTrim('name'),
+            rtrim=RTrim('name'),
+            trim=Trim('name'),
+        )
+        self.assertQuerysetEqual(
+            authors.order_by('alias'), [
+                ('John ', '  John', 'John'),
+                ('Rhonda', 'Rhonda', 'Rhonda'),
+            ],
+            lambda a: (a.ltrim, a.rtrim, a.trim)
+        )
+
+    def test_trim_transform(self):
+        Author.objects.create(name=' John  ')
+        Author.objects.create(name='Rhonda')
+        tests = (
+            (LTrim, 'John  '),
+            (RTrim, ' John'),
+            (Trim, 'John'),
+        )
+        for transform, trimmed_name in tests:
+            with self.subTest(transform=transform):
+                try:
+                    CharField.register_lookup(transform)
+                    authors = Author.objects.filter(**{'name__%s' % transform.lookup_name: trimmed_name})
+                    self.assertQuerysetEqual(authors, [' John  '], lambda a: a.name)
+                finally:
+                    CharField._unregister_lookup(transform)
diff --git a/tests/db_functions/text/test_upper.py b/tests/db_functions/text/test_upper.py
new file mode 100644
index 0000000000..091e815d6a
--- /dev/null
+++ b/tests/db_functions/text/test_upper.py
@@ -0,0 +1,43 @@
+from django.db.models import CharField
+from django.db.models.functions import Upper
+from django.test import TestCase
+
+from ..models import Author
+
+
+class UpperTests(TestCase):
+
+    def test_basic(self):
+        Author.objects.create(name='John Smith', alias='smithj')
+        Author.objects.create(name='Rhonda')
+        authors = Author.objects.annotate(upper_name=Upper('name'))
+        self.assertQuerysetEqual(
+            authors.order_by('name'), [
+                'JOHN SMITH',
+                'RHONDA',
+            ],
+            lambda a: a.upper_name
+        )
+        Author.objects.update(name=Upper('name'))
+        self.assertQuerysetEqual(
+            authors.order_by('name'), [
+                ('JOHN SMITH', 'JOHN SMITH'),
+                ('RHONDA', 'RHONDA'),
+            ],
+            lambda a: (a.upper_name, a.name)
+        )
+
+    def test_transform(self):
+        try:
+            CharField.register_lookup(Upper)
+            Author.objects.create(name='John Smith', alias='smithj')
+            Author.objects.create(name='Rhonda')
+            authors = Author.objects.filter(name__upper__exact='JOHN SMITH')
+            self.assertQuerysetEqual(
+                authors.order_by('name'), [
+                    'John Smith',
+                ],
+                lambda a: a.name
+            )
+        finally:
+            CharField._unregister_lookup(Upper)