Opened 48 minutes ago
#37316 new New feature
Functions need a way to mark themselves as volatile
| Reported by: | Jacob Walls | Owned by: | |
|---|---|---|---|
| Component: | Database layer (models, ORM) | Version: | dev |
| Severity: | Normal | Keywords: | |
| Cc: | Triage Stage: | Unreviewed | |
| Has patch: | no | Needs documentation: | no |
| Needs tests: | no | Patch needs improvement: | no |
| Easy pickings: | no | UI/UX: | no |
Description
In the ORM, expressions are hashed based on their type and arguments. Because random functions like UUID7() aren't marked as volatile, repeated expressions can hash to the same thing, leading to inappropriate deduplication later when compiling ORDER BY and GROUP BY:
For example, these group by expressions get deduplicated:
from django.contrib.auth.models import User from django.db import models from django.db.models.functions import Floor, Random def roll(faces): return Floor(Random() * faces) + 1 def run(): User.objects.bulk_create([User(username=str(i)) for i in range(1, 13)]) qs = User.objects.values( first_roll=roll(6), tie_break_roll=roll(6), ).annotate(total=models.Count("pk")) print(qs)
SELECT (FLOOR((RANDOM() * 6)) + 1) AS "first_roll", (FLOOR((RANDOM() * 6)) + 1) AS "tie_break_roll", COUNT("auth_user"."id") AS "total" FROM "auth_user" GROUP BY 2 LIMIT 21
I would have expected GROUP BY 1, 2.
In addition to GROUP BY, some other areas to check are deduplication in ORDER BY, get_extra_select() for distinct(), and get_qualify_sql() for Window() functions.
Noticed while reviewing #37222.