Surprising limitation of window function frames

Django Internals ORM
1

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.

2

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

1 Like
3

How did I not find that! Thanks! If I find the time, I can look into doing a PR (although it’s not much likely unfortunately).

Since I needed to solve the internal issue urgently I took a bypass by implementing my own expression that just accepts raw SQL fragments like frame=PostgresWindowFrame('ROWS', start='unbounded preceding', end='1 preceding'), implemented as

class PostgresWindowFrame(Expression):
    template = '%(frame_type)s BETWEEN %(start)s AND %(end)s'

    def __init__(self, frame_type=None, start=None, end=None):
        self.frame_type = frame_type
        self.start = Value(start)
        self.end = Value(end)

    def set_source_expressions(self, exprs):
        self.start, self.end = exprs

    def get_source_expressions(self):
        return [self.start, self.end]

    def as_sql(self, compiler, connection):
        return self.template % {
            'frame_type': self.frame_type,
            'start': self.start.value,
            'end': self.end.value,
        }, []

    def __repr__(self):
        return '<%s: %s>' % (self.__class__.__name__, self)

    def get_group_by_cols(self, alias=None):
        return []

    def __str__(self):
        return self.template % {
            'frame_type': self.frame_type,
            'start': self.start.value,
            'end': self.end.value,
        }