id	summary	reporter	owner	description	type	status	component	version	severity	resolution	keywords	cc	stage	has_patch	needs_docs	needs_tests	needs_better_patch	easy	ui_ux
36875	Avoid unnecessary Coalesce in Concat/ConcatPair	David		"Currently `ConcatPair` (which is used by `Concat`) when invoking the `.coalesce` method it will force the `COALESCE` on every argument provided to the expression.

https://github.com/django/django/blob/3851601b2e080df34fb9227fe5d2fd43af604263/django/db/models/functions/text.py#L112-L122

This does not take into account:

- if any involved expression have nullable output (which I understand can be hard to check)
- if any involved expression is a fixed value (which should easier to detect)

The case in which I am more interested is the latter, because it is completely useless to invoke `COALESCE` on  a fixed value (unless it was provided `NULL` from the beginning, which should be easy to check).


I came along this while writing a query to detect custom permissions on the database:

{{{
from django.contrib.auth.models import Permission
from django.db.models import Value, F
from django.db.models.functions import Concat

custom_permissions = Permission.objects.exclude(
    codename__regex=Concat(
        Value(""^(add|change|delete|view)_""),
        F(""content_type__model""),
        Value(""$""),
    )
)
}}}

I was expecting a WHERE clause like:

{{{
""auth_permission"".""codename""::text ~ ('^(add|change|delete|view)_' || ""django_content_type"".""model"" || '$')
}}}

And I was stunned when I saw the following (which is hard to read):

{{{
""auth_permission"".""codename""::text ~ (COALESCE('^(add|change|delete|view)_', '') || COALESCE((COALESCE(""django_content_type"".""model"", '') ||  COALESCE('$', '')), ''))
}}}

My proposal is to change how  `ConcatPair.coalesce` behaves to detect if `COALESCE` can be skipped (ie: fixed value, already enforced coalesce on previous expression, etc) before putting that function in the output.
This could improve readability for generated queries and may reduce the overhad of building the `Coalesce` expression."	Cleanup/optimization	new	Database layer (models, ORM)	5.2	Normal		concat,coalese		Unreviewed	0	0	0	0	0	0