Fixed #29251 -- Added bytes to str conversion in LPad/RPad database functions on MySQL.

Thanks Tim Graham for the review.

4 files changed, 33 insertions, 3 deletions

diff --git a/django/db/backends/base/features.py b/django/db/backends/base/features.py
index b86f0be35b..74f7829e3f 100644
--- a/django/db/backends/base/features.py
+++ b/django/db/backends/base/features.py
@@ -247,6 +247,9 @@ class BaseDatabaseFeatures:
     # Does the backend support keyword parameters for cursor.callproc()?
     supports_callproc_kwargs = False
 
+    # Convert CharField results from bytes to str in database functions.
+    db_functions_convert_bytes_to_str = False
+
     def __init__(self, connection):
         self.connection = connection
 
diff --git a/django/db/backends/mysql/features.py b/django/db/backends/mysql/features.py
index 9c1500e692..691fba49ad 100644
--- a/django/db/backends/mysql/features.py
+++ b/django/db/backends/mysql/features.py
@@ -48,6 +48,7 @@ class DatabaseFeatures(BaseDatabaseFeatures):
             SET V_I = P_I;
         END;
     """
+    db_functions_convert_bytes_to_str = True
 
     @cached_property
     def _mysql_storage_engine(self):
diff --git a/django/db/models/functions/text.py b/django/db/models/functions/text.py
index c57545bc6a..aecf64e28e 100644
--- a/django/db/models/functions/text.py
+++ b/django/db/models/functions/text.py
@@ -2,6 +2,22 @@ from django.db.models import Func, IntegerField, Transform, Value, fields
 from django.db.models.functions import Coalesce
 
 
+class BytesToCharFieldConversionMixin:
+    """
+    Convert CharField results from bytes to str.
+
+    MySQL returns long data types (bytes) instead of chars when it can't
+    determine the length of the result string. For example:
+        LPAD(column1, CHAR_LENGTH(column2), ' ')
+    returns the LONGTEXT (bytes) instead of VARCHAR.
+    """
+    def convert_value(self, value, expression, connection):
+        if connection.features.db_functions_convert_bytes_to_str:
+            if self.output_field.get_internal_type() == 'CharField' and isinstance(value, bytes):
+                return value.decode()
+        return super().convert_value(value, expression, connection)
+
+
 class Chr(Transform):
     function = 'CHR'
     lookup_name = 'chr'
@@ -110,7 +126,7 @@ class Lower(Transform):
     lookup_name = 'lower'
 
 
-class LPad(Func):
+class LPad(BytesToCharFieldConversionMixin, Func):
     function = 'LPAD'
 
     def __init__(self, expression, length, fill_text=Value(' '), **extra):
diff --git a/tests/db_functions/test_pad.py b/tests/db_functions/test_pad.py
index d873345fc4..1c2caf06b5 100644
--- a/tests/db_functions/test_pad.py
+++ b/tests/db_functions/test_pad.py
@@ -1,5 +1,5 @@
-from django.db.models import Value
-from django.db.models.functions import LPad, RPad
+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
@@ -32,3 +32,13 @@ class PadTests(TestCase):
             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,
+        )