ExpressionWrapper output_field no longer works

[Siburg]

It seems that output_field for an ExpressionWrapper no longer works in Django 5.1 as in previous versions. Example code and test is below.

class PartnerQuerySet(models.QuerySet):

    def order_by_nick_name(self):
        qs = self.annotate(
            nick_name_for_ordering=Case(
                When(
                    nick_name='',
                    # The starting 'zzzzzzzz' is intended to ensure they
                    # will be after records with nicknames. As ugly
                    # as it is, this should work on all databases.
                    # Subtracting the pk from a large number provides a
                    # reverse ordering by pk. Let's assume we never
                    # reach 900,000 records.
                    then=Concat(
                        Value('zzzzzzzz'),
                        ExpressionWrapper(999_999 - F('pk'), output_field=models.CharField()),
                        # This is probably a bug in Django 5.1, but it no longer
                        # worked when using `CharField` as output_field for the
                        # ExpressionWrapper. Amazingly, the 2-stage casting
                        # does work.
                        # Cast(
                        #     ExpressionWrapper(999_999 - F('pk'), output_field=models.BigAutoField()),
                        #     output_field=models.CharField()
                        # ),
                    ),
                ),
                default=Lower('nick_name'),
            )
        )
        return qs.order_by('nick_name_for_ordering')


class Partner(models.Model):
    name = models.CharField(max_length=128, unique=True)
    nick_name = models.CharField(max_length=64, default='', blank=True)

    objects = PartnerQuerySet().as_manager()


class PartnerQuerySetTests(TestCase):

    def test_ordering_by_nick_name(self):
        # Given
        aaron = Partner.objects.create(name='aaron', nick_name='Zorro')
        bert = Partner.objects.create(name='bert')
        zelda = Partner.objects.create(name='Zelda', nick_name='ace')
        ernie = Partner.objects.create(name='Ernie')
        # When
        qs = Partner.objects.order_by_nick_name()
        # Then
        self.assertEqual(list(qs), [zelda, aaron, ernie, bert])
        # And
        self.assertEqual(qs[2].nick_name_for_ordering, 'zzzzzzzz999995')

I'm not especially proud of that code, but it worked in Django 4.2 and 5.0 and passed the test. It fails in 5.1. My workaround for it, wrapping the ExpressionWrapper itself in a Cast solves the problem; as in the commented out snippet above. However, I don't see why that should have become necessary.

I don't know what causes this change in behaviour. I think this ticket may be related to https://code.djangoproject.com/ticket/26650

[Simon Charette]

In order to help with triaging could you please provide the exception you are encountering and the database backend you as using.

[Siburg]

In order to help with triaging could you please provide the exception you are encountering and the database backend you as using.

Thank you for fast response. I'm running it on PostgreSQL. Exception from the test is

psycopg.errors.InvalidTextRepresentation: invalid input syntax for type bigint: ""
LINE 1: ... '') || COALESCE((999999 - "calls_partner"."id"), '')) ELSE ...

From various trials and errors that I undertook I learned that the problem is not actually with the COALESCE though. The problem is caused by the ExpressionWrapper.

[Simon Charette]

Well ExpressionWrapper should ​not change the SQL generated at all so unless ​you are getting a Django level crash about issues resolving output_field, which doesn't seem to be case here, there is likely something else at play.

Could you provide the SQL that was previously generated, your Postgres version (if it changed between Django upgrades), and which version of psycopg2 or psycopg you are using?