Notifications
Clear all

Surprising limitation of window function frames


john077taylor
Posts: 1
Topic starter
(@john077taylor)
New Member
Joined: 2 weeks ago

Hi,

I was just writing my first more complex window function with the ORM and to achieve my goal, I basically need the following SQL:

count(*) filter (…) over (partition by … order by … rows
                          between unbounded preceding and 1 preceding)

For which the Django representation could be

ct=Window(
    expression=Count('position_id', filter=Q(type=Value('exit'))),
    partition_by=[F('position_id'), F('list_id')],
    order_by=F('datetime').asc(),
    frame=RowRange(start=None, end=-1),
)

This fails:

ValueError: end argument must be a positive integer, zero, or None, but got '-1'.

Apparently, for the end argument, only X following is supported by Django, not X preceeding.

The docs somehow confirm this:

The accepted values for the start and end arguments are None , an integer, or zero. A negative integer for start results in N preceding , while None yields UNBOUNDED PRECEDING . For both start and end , zero will return CURRENT ROW . Positive integers are accepted for end .

But I can’t see any reason why. I only tested on Postgres, but it seems to be allowed by SQL. Does anyone know about a database backend that does not support this? Or is this just an oversight? A quick look into Django’s git history indicates that this was in there in the initial window function patch by
@atombrella 4 years ago and hasn’t been touched since.

Happy for any feedback on whether this is a bug and we should be looking for a fix or an intended limitation.

Topic Tags
1 Reply
gabrielyoung077
Posts: 1
(@gabrielyoung077)
New Member
Joined: 1 week ago

We have an accepted ticket for this feature, see #29850 (Add window support for RowRange frames) – Django 7.

Reply