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
34840	Django 4.2 casts text fields when testing IS NULL, preventing use of partial indexes	Alex Vandiver	Mariusz Felisiak	"The Zulip project has a model with unique constraints which are expressed as two non-overlapping partial indexes:
{{{
class UserCount(models.Model):
    user = models.ForeignKey(UserProfile, on_delete=models.CASCADE)
    realm = models.ForeignKey(Realm, on_delete=models.CASCADE)
    property = models.CharField(max_length=32)
    subgroup = models.CharField(max_length=16, null=True)
    end_time = models.DateTimeField()
    value = models.BigIntegerField()

    class Meta:
        constraints = [
            UniqueConstraint(
                fields=[""user"", ""property"", ""subgroup"", ""end_time""],
                condition=Q(subgroup__isnull=False),
                name=""unique_user_count"",
            ),
            UniqueConstraint(
                fields=[""user"", ""property"", ""end_time""],
                condition=Q(subgroup__isnull=True),
                name=""unique_user_count_null_subgroup"",
            ),
        ]
}}}

However, since commit 09ffc5c1212d4ced58b708cbbf3dfbfb77b782ca in Django 4.2, a query of the form:
{{{
UserCount.objects.get(
    property=""messages_read::hour"",
    subgroup=None,
    end_time=timezone_now(),
    user_id=user_profile.id,
    realm_id=realm.id,
)
}}}

...generates this SQL:
{{{
SELECT ""analytics_usercount"".""id"", ""analytics_usercount"".""property"", ""analytics_usercount"".""subgroup"", ""analytics_usercount"".""end_time"", ""analytics_usercount"".""value"", ""analytics_usercount"".""user_id"", ""analytics_usercount"".""realm_id"" FROM ""analytics_usercount"" WHERE (""analytics_usercount"".""end_time"" = '2023-09-13T19:16:34.195355+00:00'::timestamptz AND ""analytics_usercount"".""property"" = 'messages_read::hour' AND ""analytics_usercount"".""realm_id"" = 4715 AND ""analytics_usercount"".""subgroup""::text IS NULL AND ""analytics_usercount"".""user_id"" = 428054) LIMIT 21
}}}

The cast of `""analytics_usercount"".""subgroup""::text IS NULL` causes PostgreSQL to not be able to use the unique partial index:
{{{
 Limit  (cost=48.30..49.42 rows=1 width=61)
   ->  Bitmap Heap Scan on analytics_usercount  (cost=48.30..49.42 rows=1 width=61)
         Recheck Cond: (((property)::text = 'messages_read::hour'::text) AND (realm_id = 4715) AND (end_time = '2023-09-13 19:00:00+00'::timestamp with time zone) AND (user_id = 428054))
         Filter: ((subgroup)::text IS NULL)
         ->  BitmapAnd  (cost=48.30..48.30 rows=1 width=0)
               ->  Bitmap Index Scan on analytics_usercount_property_591dbec1_idx  (cost=0.00..4.88 rows=158 width=0)
                     Index Cond: (((property)::text = 'messages_read::hour'::text) AND (realm_id = 4715) AND (end_time = '2023-09-13 19:00:00+00'::timestamp with time zone))
               ->  Bitmap Index Scan on analytics_usercount_e8701ad4  (cost=0.00..43.17 rows=3626 width=0)
                     Index Cond: (user_id = 428054)
}}}

Dropping the explicit cast causes it to use the index:
{{{
 Limit  (cost=0.57..2.80 rows=1 width=61)
   ->  Index Scan using unique_user_count_null_subgroup on analytics_usercount  (cost=0.57..2.80 rows=1 width=61)
         Index Cond: ((user_id = 428054) AND ((property)::text = 'messages_read::hour'::text) AND (end_time = '2023-09-13 19:00:00+00'::timestamp with time zone))
         Filter: (realm_id = 4715)
}}}
...and improving the query runtime significantly.

It's not clear to me from 09ffc5c1212d4ced58b708cbbf3dfbfb77b782ca what about psycopg3 requires this cast. "	Bug	closed	Database layer (models, ORM)	4.2	Release blocker	fixed		Florian Apolloner Mariusz Felisiak Simon Charette	Accepted	1	0	0	0	0	0