#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.

Change History (0)

Note: See TracTickets for help on using tickets.
Back to Top