From f9acbeb778a8716a1e49151fe0d0bd23e4f03464 Mon Sep 17 00:00:00 2001 From: Violet Folino Gallo <48537601+galloviolet@users.noreply.github.com> Date: Thu, 31 Jul 2025 04:05:36 -0400 Subject: [PATCH] Allow non-integer RANGE in OVER Updated the :func:`_sql.over` clause to allow non-integer values in :paramref:`_sql.over.range_` clause. Previously, only integer values were allowed and any other values would lead to a failure. To specify a non-integer value, use the new :class:`_sql.FrameClause` construct along with the new :class:`_sql.FrameClauseType` enum to specify the frame boundaries. Fixes #12596. Closes: #12695 Pull-request: https://github.com/sqlalchemy/sqlalchemy/pull/12695 Pull-request-sha: 8063cec65d1e7ad5d640bf689c01079e2d8c89ae Change-Id: I248a938f6502d72555c005d86791c992822117d4 --- doc/build/changelog/unreleased_21/12596.rst | 23 ++ doc/build/core/sqlelement.rst | 6 + lib/sqlalchemy/__init__.py | 2 + lib/sqlalchemy/sql/_elements_constructors.py | 48 +++- lib/sqlalchemy/sql/compiler.py | 22 +- lib/sqlalchemy/sql/elements.py | 236 ++++++++++++------- lib/sqlalchemy/sql/expression.py | 2 + lib/sqlalchemy/sql/functions.py | 12 +- lib/sqlalchemy/testing/requirements.py | 15 ++ lib/sqlalchemy/testing/suite/test_select.py | 87 ++++++- test/requirements.py | 15 ++ test/sql/test_compare.py | 36 ++- test/sql/test_compiler.py | 153 +++++++++--- test/sql/test_functions.py | 24 ++ 14 files changed, 529 insertions(+), 152 deletions(-) create mode 100644 doc/build/changelog/unreleased_21/12596.rst diff --git a/doc/build/changelog/unreleased_21/12596.rst b/doc/build/changelog/unreleased_21/12596.rst new file mode 100644 index 0000000000..f46d7d2e8e --- /dev/null +++ b/doc/build/changelog/unreleased_21/12596.rst @@ -0,0 +1,23 @@ +.. change:: + :tags: bug, sql + :tickets: 12596 + + Updated the :func:`_sql.over` clause to allow non integer values in + :paramref:`_sql.over.range_` clause. Previously, only integer values + were allowed and any other values would lead to a failure. + To specify a non-integer value, use the new :class:`_sql.FrameClause` + construct along with the new :class:`_sql.FrameClauseType` enum to specify + the frame boundaries. For example:: + + from sqlalchemy import FrameClause, FrameClauseType + + select( + func.sum(table.c.value).over( + range_=FrameClause( + 3.14, + 2.71, + FrameClauseType.PRECEDING, + FrameClauseType.FOLLOWING, + ) + ) + ) diff --git a/doc/build/core/sqlelement.rst b/doc/build/core/sqlelement.rst index 79c41f7d23..88dc810efa 100644 --- a/doc/build/core/sqlelement.rst +++ b/doc/build/core/sqlelement.rst @@ -211,6 +211,12 @@ The classes here are generated using the constructors listed at .. autoclass:: Over :members: +.. autoclass:: FrameClause + :members: + +.. autoclass:: FrameClauseType + :members: + .. autoclass:: SQLColumnExpression .. autoclass:: TextClause diff --git a/lib/sqlalchemy/__init__.py b/lib/sqlalchemy/__init__.py index 7a70450e05..80fbd0d247 100644 --- a/lib/sqlalchemy/__init__.py +++ b/lib/sqlalchemy/__init__.py @@ -127,6 +127,8 @@ from .sql.expression import Extract as Extract from .sql.expression import extract as extract from .sql.expression import false as false from .sql.expression import False_ as False_ +from .sql.expression import FrameClause as FrameClause +from .sql.expression import FrameClauseType as FrameClauseType from .sql.expression import from_dml_column as from_dml_column from .sql.expression import FromClause as FromClause from .sql.expression import FromGrouping as FromGrouping diff --git a/lib/sqlalchemy/sql/_elements_constructors.py b/lib/sqlalchemy/sql/_elements_constructors.py index 2b37c12d27..c8d76d150a 100644 --- a/lib/sqlalchemy/sql/_elements_constructors.py +++ b/lib/sqlalchemy/sql/_elements_constructors.py @@ -58,7 +58,9 @@ if typing.TYPE_CHECKING: from ._typing import _ColumnExpressionOrStrLabelArgument from ._typing import _DMLOnlyColumnArgument from ._typing import _TypeEngineArgument + from .elements import _FrameIntTuple from .elements import BinaryExpression + from .elements import FrameClause from .selectable import FromClause from .type_api import TypeEngine @@ -1611,11 +1613,11 @@ if not TYPE_CHECKING: def over( element: FunctionElement[_T], - partition_by: Optional[_ByArgument] = None, - order_by: Optional[_ByArgument] = None, - range_: Optional[typing_Tuple[Optional[int], Optional[int]]] = None, - rows: Optional[typing_Tuple[Optional[int], Optional[int]]] = None, - groups: Optional[typing_Tuple[Optional[int], Optional[int]]] = None, + partition_by: _ByArgument | None = None, + order_by: _ByArgument | None = None, + range_: _FrameIntTuple | FrameClause | None = None, + rows: _FrameIntTuple | FrameClause | None = None, + groups: _FrameIntTuple | FrameClause | None = None, ) -> Over[_T]: r"""Produce an :class:`.Over` object against a function. @@ -1672,6 +1674,26 @@ def over( func.row_number().over(order_by="x", groups=(1, 3)) + Depending on the type of the order column, the 'RANGE' value may not be + an integer. In this case use a :class:`_expression.FrameClause` directly + to specify the frame boundaries. E.g.:: + + from datetime import timedelta + from sqlalchemy import FrameClause, FrameClauseType + + func.sum(my_table.c.amount).over( + order_by=my_table.c.date, + range_=FrameClause( + start=timedelta(days=7), + end=None, + start_frame_type=FrameClauseType.PRECEDING, + end_frame_type=FrameClauseType.UNBOUNDED, + ), + ) + + .. versionchanged:: 2.1 Added support for range types that are not + integer-based, via the :class:`_expression.FrameClause` construct. + :param element: a :class:`.FunctionElement`, :class:`.WithinGroup`, or other compatible construct. :param partition_by: a column element or string, or a list @@ -1681,14 +1703,22 @@ def over( of such, that will be used as the ORDER BY clause of the OVER construct. :param range\_: optional range clause for the window. This is a - tuple value which can contain integer values or ``None``, + two-tuple value which can contain integer values or ``None``, and will render a RANGE BETWEEN PRECEDING / FOLLOWING clause. - :param rows: optional rows clause for the window. This is a tuple + Can also be a :class:`_expression.FrameClause` instance to + specify non-integer values. + + .. versionchanged:: 2.1 Added support for range types that are not + integer-based, via the :class:`_expression.FrameClause` construct. + + :param rows: optional rows clause for the window. This is a two-tuple value which can contain integer values or None, and will render - a ROWS BETWEEN PRECEDING / FOLLOWING clause. + a ROWS BETWEEN PRECEDING / FOLLOWING clause. Can also be a + :class:`_expression.FrameClause` instance. :param groups: optional groups clause for the window. This is a - tuple value which can contain integer values or ``None``, + two-tuple value which can contain integer values or ``None``, and will render a GROUPS BETWEEN PRECEDING / FOLLOWING clause. + Can also be a :class:`_expression.FrameClause` instance. .. versionadded:: 2.0.40 diff --git a/lib/sqlalchemy/sql/compiler.py b/lib/sqlalchemy/sql/compiler.py index e95eaa5918..cbf7c3d10f 100644 --- a/lib/sqlalchemy/sql/compiler.py +++ b/lib/sqlalchemy/sql/compiler.py @@ -2898,30 +2898,24 @@ class SQLCompiler(Compiled): def visit_frame_clause(self, frameclause, **kw): - if frameclause.lower_type is elements._FrameClauseType.RANGE_UNBOUNDED: + if frameclause.lower_type is elements.FrameClauseType.UNBOUNDED: left = "UNBOUNDED PRECEDING" - elif frameclause.lower_type is elements._FrameClauseType.RANGE_CURRENT: + elif frameclause.lower_type is elements.FrameClauseType.CURRENT: left = "CURRENT ROW" else: - val = self.process(frameclause.lower_integer_bind, **kw) - if ( - frameclause.lower_type - is elements._FrameClauseType.RANGE_PRECEDING - ): + val = self.process(frameclause.lower_bind, **kw) + if frameclause.lower_type is elements.FrameClauseType.PRECEDING: left = f"{val} PRECEDING" else: left = f"{val} FOLLOWING" - if frameclause.upper_type is elements._FrameClauseType.RANGE_UNBOUNDED: + if frameclause.upper_type is elements.FrameClauseType.UNBOUNDED: right = "UNBOUNDED FOLLOWING" - elif frameclause.upper_type is elements._FrameClauseType.RANGE_CURRENT: + elif frameclause.upper_type is elements.FrameClauseType.CURRENT: right = "CURRENT ROW" else: - val = self.process(frameclause.upper_integer_bind, **kw) - if ( - frameclause.upper_type - is elements._FrameClauseType.RANGE_PRECEDING - ): + val = self.process(frameclause.upper_bind, **kw) + if frameclause.upper_type is elements.FrameClauseType.PRECEDING: right = f"{val} PRECEDING" else: right = f"{val} FOLLOWING" diff --git a/lib/sqlalchemy/sql/elements.py b/lib/sqlalchemy/sql/elements.py index 39614b9178..c0ffefb596 100644 --- a/lib/sqlalchemy/sql/elements.py +++ b/lib/sqlalchemy/sql/elements.py @@ -4371,6 +4371,9 @@ class _OverrideBinds(Grouping[_T]): return ck +_FrameIntTuple = tuple[int | None, int | None] + + class Over(ColumnElement[_T]): """Represent an OVER clause. @@ -4399,18 +4402,18 @@ class Over(ColumnElement[_T]): """The underlying expression object to which this :class:`.Over` object refers.""" - range_: Optional[_FrameClause] - rows: Optional[_FrameClause] - groups: Optional[_FrameClause] + range_: FrameClause | None + rows: FrameClause | None + groups: FrameClause | None def __init__( self, element: ColumnElement[_T], partition_by: Optional[_ByArgument] = None, order_by: Optional[_ByArgument] = None, - range_: Optional[typing_Tuple[Optional[int], Optional[int]]] = None, - rows: Optional[typing_Tuple[Optional[int], Optional[int]]] = None, - groups: Optional[typing_Tuple[Optional[int], Optional[int]]] = None, + range_: _FrameIntTuple | FrameClause | None = None, + rows: _FrameIntTuple | FrameClause | None = None, + groups: _FrameIntTuple | FrameClause | None = None, ): self.element = element if order_by is not None: @@ -4423,14 +4426,14 @@ class Over(ColumnElement[_T]): _literal_as_text_role=roles.ByOfRole, ) - if sum(bool(item) for item in (range_, rows, groups)) > 1: + if sum(item is not None for item in (range_, rows, groups)) > 1: raise exc.ArgumentError( "only one of 'rows', 'range_', or 'groups' may be provided" ) else: - self.range_ = _FrameClause(range_) if range_ else None - self.rows = _FrameClause(rows) if rows else None - self.groups = _FrameClause(groups) if groups else None + self.range_ = FrameClause._parse(range_, coerce_int=False) + self.rows = FrameClause._parse(rows, coerce_int=True) + self.groups = FrameClause._parse(groups, coerce_int=True) if not TYPE_CHECKING: @@ -4451,16 +4454,37 @@ class Over(ColumnElement[_T]): ) -class _FrameClauseType(Enum): - RANGE_UNBOUNDED = 0 - RANGE_CURRENT = 1 - RANGE_PRECEDING = 2 - RANGE_FOLLOWING = 3 +class FrameClauseType(Enum): + """Frame clause type enum for FrameClause lower_type and upper_type. + + .. versionadded:: 2.1 + + """ + UNBOUNDED = 0 + """Produces an "UNBOUNDED PRECEDING" or "UNBOUNDED FOLLOWING" frame + clause depending on the position. + Requires a ``None`` value for the corresponding bound value. + """ + CURRENT = 1 + """Produces a "CURRENT ROW" frame clause. + Requires a ``None`` value for the corresponding bound value. + """ + PRECEDING = 2 + """Produces a "PRECEDING" frame clause.""" + FOLLOWING = 3 + """Produces a "FOLLOWING" frame clause.""" -class _FrameClause(ClauseElement): - """indicate the 'rows' or 'range' field of a window function, e.g. using - :class:`.Over`. + +_require_none = ( + FrameClauseType.CURRENT, + FrameClauseType.UNBOUNDED, +) + + +class FrameClause(ClauseElement): + """Indicate the 'rows' 'range' or 'group' field of a window function, + e.g. using :class:`.Over`. .. versionadded:: 2.1 @@ -4469,70 +4493,116 @@ class _FrameClause(ClauseElement): __visit_name__ = "frame_clause" _traverse_internals: _TraverseInternalsType = [ - ("lower_integer_bind", InternalTraversal.dp_clauseelement), - ("upper_integer_bind", InternalTraversal.dp_clauseelement), + ("lower_bind", InternalTraversal.dp_clauseelement), + ("upper_bind", InternalTraversal.dp_clauseelement), ("lower_type", InternalTraversal.dp_plain_obj), ("upper_type", InternalTraversal.dp_plain_obj), ] def __init__( self, - range_: typing_Tuple[Optional[int], Optional[int]], - ): - try: - r0, r1 = range_ - except (ValueError, TypeError) as ve: - raise exc.ArgumentError("2-tuple expected for range/rows") from ve + start: Any, + end: Any, + start_frame_type: FrameClauseType, + end_frame_type: FrameClauseType, + _validate: bool = True, + ) -> None: + """Creates a new FrameClause specifying the bounds of a window frame. - if r0 is None: - self.lower_type = _FrameClauseType.RANGE_UNBOUNDED - self.lower_integer_bind = None - else: - try: - lower_integer = int(r0) - except ValueError as err: + :param start: The start value. + :param end: The end value. + :param start_frame_type: The :class:`FrameClauseType` for the + start value. + :param end_frame_type: The :class:`FrameClauseType` for the end value. + """ + self.lower_bind = self._as_literal(start) + self.upper_bind = self._as_literal(end) + self.lower_type = FrameClauseType(start_frame_type) + self.upper_type = FrameClauseType(end_frame_type) + if _validate: + if ( + self.lower_type in _require_none + and self.lower_bind is not None + ): raise exc.ArgumentError( - "Integer or None expected for range value" - ) from err - else: - if lower_integer == 0: - self.lower_type = _FrameClauseType.RANGE_CURRENT - self.lower_integer_bind = None - elif lower_integer < 0: - self.lower_type = _FrameClauseType.RANGE_PRECEDING - self.lower_integer_bind = literal( - abs(lower_integer), type_api.INTEGERTYPE - ) - else: - self.lower_type = _FrameClauseType.RANGE_FOLLOWING - self.lower_integer_bind = literal( - lower_integer, type_api.INTEGERTYPE - ) + "Cannot specify a value for start with frame type " + f"{self.lower_type.name}" + ) + if ( + self.upper_type in _require_none + and self.upper_bind is not None + ): + raise exc.ArgumentError( + "Cannot specify a value for end with frame type " + f"{self.upper_type.name}" + ) - if r1 is None: - self.upper_type = _FrameClauseType.RANGE_UNBOUNDED - self.upper_integer_bind = None + @classmethod + def _as_literal(cls, value: Any) -> BindParameter[Any] | None: + if value is None: + return None + elif isinstance(value, int): + return literal(value, type_api.INTEGERTYPE) + elif isinstance(value, BindParameter): + return value else: + return literal(value) # let the default type resolution occur + + @classmethod + def _handle_int( + cls, value: Any | None, coerce_int: bool + ) -> tuple[int | None, FrameClauseType]: + if value is None: + return None, FrameClauseType.UNBOUNDED + + if coerce_int: try: - upper_integer = int(r1) + integer = int(value) except ValueError as err: raise exc.ArgumentError( - "Integer or None expected for range value" + "Integer or None expected for values in rows/groups frame" ) from err - else: - if upper_integer == 0: - self.upper_type = _FrameClauseType.RANGE_CURRENT - self.upper_integer_bind = None - elif upper_integer < 0: - self.upper_type = _FrameClauseType.RANGE_PRECEDING - self.upper_integer_bind = literal( - abs(upper_integer), type_api.INTEGERTYPE - ) - else: - self.upper_type = _FrameClauseType.RANGE_FOLLOWING - self.upper_integer_bind = literal( - upper_integer, type_api.INTEGERTYPE - ) + elif not isinstance(value, int): + raise exc.ArgumentError( + "When using a tuple to specify a range only integer or none " + "values are allowed in the range frame. To specify a " + "different type use the FrameClause directly." + ) + else: + integer = value + if integer == 0: + return None, FrameClauseType.CURRENT + elif integer < 0: + return abs(integer), FrameClauseType.PRECEDING + else: + return integer, FrameClauseType.FOLLOWING + + @classmethod + def _parse( + cls, + range_: _FrameIntTuple | FrameClause | None, + coerce_int: bool, + ) -> FrameClause | None: + if range_ is None or isinstance(range_, FrameClause): + return range_ + + try: + r0, r1 = range_ + except (ValueError, TypeError) as ve: + raise exc.ArgumentError( + "2-tuple expected for range/rows/groups" + ) from ve + + l_b, l_t = cls._handle_int(r0, coerce_int) + u_b, u_t = cls._handle_int(r1, coerce_int) + + return FrameClause( + start=l_b, + end=u_b, + start_frame_type=l_t, + end_frame_type=u_t, + _validate=False, + ) class AggregateOrderBy(WrapsColumnExpression[_T]): @@ -4594,11 +4664,11 @@ class AggregateOrderBy(WrapsColumnExpression[_T]): def over( self, *, - partition_by: Optional[_ByArgument] = None, - order_by: Optional[_ByArgument] = None, - rows: Optional[typing_Tuple[Optional[int], Optional[int]]] = None, - range_: Optional[typing_Tuple[Optional[int], Optional[int]]] = None, - groups: Optional[typing_Tuple[Optional[int], Optional[int]]] = None, + partition_by: _ByArgument | None = None, + order_by: _ByArgument | None = None, + rows: _FrameIntTuple | FrameClause | None = None, + range_: _FrameIntTuple | FrameClause | None = None, + groups: _FrameIntTuple | FrameClause | None = None, ) -> Over[_T]: """Produce an OVER clause against this :class:`.WithinGroup` construct. @@ -4738,21 +4808,11 @@ class FunctionFilter(Generative, ColumnElement[_T]): def over( self, - partition_by: Optional[ - Union[ - Iterable[_ColumnExpressionArgument[Any]], - _ColumnExpressionArgument[Any], - ] - ] = None, - order_by: Optional[ - Union[ - Iterable[_ColumnExpressionArgument[Any]], - _ColumnExpressionArgument[Any], - ] - ] = None, - range_: Optional[typing_Tuple[Optional[int], Optional[int]]] = None, - rows: Optional[typing_Tuple[Optional[int], Optional[int]]] = None, - groups: Optional[typing_Tuple[Optional[int], Optional[int]]] = None, + partition_by: _ByArgument | None = None, + order_by: _ByArgument | None = None, + range_: _FrameIntTuple | FrameClause | None = None, + rows: _FrameIntTuple | FrameClause | None = None, + groups: _FrameIntTuple | FrameClause | None = None, ) -> Over[_T]: """Produce an OVER clause against this filtered function. diff --git a/lib/sqlalchemy/sql/expression.py b/lib/sqlalchemy/sql/expression.py index 267a572a5b..d85142ed90 100644 --- a/lib/sqlalchemy/sql/expression.py +++ b/lib/sqlalchemy/sql/expression.py @@ -88,6 +88,8 @@ from .elements import DMLTargetCopy as DMLTargetCopy from .elements import ExpressionClauseList as ExpressionClauseList from .elements import Extract as Extract from .elements import False_ as False_ +from .elements import FrameClause as FrameClause +from .elements import FrameClauseType as FrameClauseType from .elements import FunctionFilter as FunctionFilter from .elements import Grouping as Grouping from .elements import Label as Label diff --git a/lib/sqlalchemy/sql/functions.py b/lib/sqlalchemy/sql/functions.py index dda890c0b5..4674967b0a 100644 --- a/lib/sqlalchemy/sql/functions.py +++ b/lib/sqlalchemy/sql/functions.py @@ -70,7 +70,9 @@ if TYPE_CHECKING: from ._typing import _StarOrOne from ._typing import _TypeEngineArgument from .base import _EntityNamespace + from .elements import _FrameIntTuple from .elements import ClauseElement + from .elements import FrameClause from .elements import KeyedColumnElement from .elements import TableValuedColumn from .operators import OperatorType @@ -431,11 +433,11 @@ class FunctionElement(Executable, ColumnElement[_T], FromClause, Generative): def over( self, *, - partition_by: Optional[_ByArgument] = None, - order_by: Optional[_ByArgument] = None, - rows: Optional[Tuple[Optional[int], Optional[int]]] = None, - range_: Optional[Tuple[Optional[int], Optional[int]]] = None, - groups: Optional[Tuple[Optional[int], Optional[int]]] = None, + partition_by: _ByArgument | None = None, + order_by: _ByArgument | None = None, + rows: _FrameIntTuple | FrameClause | None = None, + range_: _FrameIntTuple | FrameClause | None = None, + groups: _FrameIntTuple | FrameClause | None = None, ) -> Over[_T]: """Produce an OVER clause against this function. diff --git a/lib/sqlalchemy/testing/requirements.py b/lib/sqlalchemy/testing/requirements.py index d22e37a2a5..efcd0d9de0 100644 --- a/lib/sqlalchemy/testing/requirements.py +++ b/lib/sqlalchemy/testing/requirements.py @@ -300,6 +300,21 @@ class SuiteRequirements(Requirements): """Target database must support window functions.""" return exclusions.closed() + @property + def window_range(self): + """Target backend supports RANGE in window functions with int frames""" + return exclusions.closed() + + @property + def window_range_numeric(self): + """Target backend supports numeric values in RANGE""" + return exclusions.closed() + + @property + def window_range_non_numeric(self): + """Target backend supports non-numeric values in RANGE""" + return exclusions.closed() + @property def ctes(self): """Target database supports CTEs""" diff --git a/lib/sqlalchemy/testing/suite/test_select.py b/lib/sqlalchemy/testing/suite/test_select.py index 6b21bb67fe..b3c909634c 100644 --- a/lib/sqlalchemy/testing/suite/test_select.py +++ b/lib/sqlalchemy/testing/suite/test_select.py @@ -7,6 +7,8 @@ # mypy: ignore-errors import collections.abc as collections_abc +from datetime import date +from datetime import timedelta import itertools from .. import AssertsCompiledSQL @@ -23,9 +25,13 @@ from ... import bindparam from ... import case from ... import column from ... import Computed +from ... import Date from ... import exists from ... import false +from ... import Float from ... import ForeignKey +from ... import FrameClause +from ... import FrameClauseType from ... import func from ... import Identity from ... import Integer @@ -1913,13 +1919,22 @@ class WindowFunctionTest(fixtures.TablesTest): Column("id", Integer, primary_key=True), Column("col1", Integer), Column("col2", Integer), + Column("col3", Float), ) @classmethod def insert_data(cls, connection): + def row_factory(i): + return { + "id": i, + "col1": i, + "col2": i * 5, + "col3": i + 0.5, + } + connection.execute( cls.tables.some_table.insert(), - [{"id": i, "col1": i, "col2": i * 5} for i in range(1, 50)], + [row_factory(i) for i in range(1, 50)], ) def test_window(self, connection): @@ -1934,6 +1949,76 @@ class WindowFunctionTest(fixtures.TablesTest): eq_(rows, [(95,) for i in range(19)]) + @testing.requires.window_range + def test_window_range(self, connection): + some_table = self.tables.some_table + rows = connection.execute( + select( + func.max(some_table.c.col1).over( + partition_by=[some_table.c.col2], + order_by=[some_table.c.col2.asc()], + range_=(0, 1), + ) + ).where(some_table.c.col1 < 20) + ).all() + + eq_(rows, [(i,) for i in range(1, 20)]) + + @testing.requires.window_range_numeric + def test_window_range_numeric(self, connection): + some_table = self.tables.some_table + rows = connection.execute( + select( + func.max(some_table.c.col3).over( + partition_by=[some_table.c.col3], + order_by=[some_table.c.col3.asc()], + range_=FrameClause( + 1.25, + 1.25, + FrameClauseType.PRECEDING, + FrameClauseType.FOLLOWING, + ), + ) + ).where(some_table.c.col1 < 20) + ).all() + + eq_(rows, [(i + 0.5,) for i in range(1, 20)]) + + @testing.requires.window_range_non_numeric + def test_window_range_dates(self, connection, metadata): + t = Table( + "range_string", + metadata, + Column("value", Integer), + Column("oder", Date), + ) + t.create(connection) + connection.execute( + t.insert(), + [ + {"value": 1, "oder": date(2025, 10, 1)}, + {"value": 2, "oder": date(2025, 10, 2)}, + {"value": 3, "oder": date(2025, 10, 10)}, + {"value": 4, "oder": date(2025, 10, 13)}, + {"value": 5, "oder": date(2025, 10, 16)}, + ], + ) + rows = connection.execute( + select( + func.sum(t.c.value).over( + order_by=t.c.oder, + range_=FrameClause( + timedelta(days=7), + None, + FrameClauseType.PRECEDING, + FrameClauseType.CURRENT, + ), + ) + ).order_by(t.c.oder) + ).all() + + eq_(rows, [(1,), (3,), (3,), (7,), (12,)]) + def test_window_rows_between_w_caching(self, connection): some_table = self.tables.some_table diff --git a/test/requirements.py b/test/requirements.py index adecb31158..b08b30e340 100644 --- a/test/requirements.py +++ b/test/requirements.py @@ -913,6 +913,21 @@ class DefaultRequirements(SuiteRequirements): "Backend does not support window functions", ) + @property + def window_range(self): + """Target backend supports RANGE in window functions with int frames""" + return skip_if(["mssql"]) + + @property + def window_range_numeric(self): + """Target backend supports non-integer values in RANGE""" + return skip_if(["mssql"]) + + @property + def window_range_non_numeric(self): + """Target backend supports non-numeric values in RANGE""" + return only_if(["postgresql"]) + @property def two_phase_transactions(self): """Target database must support two-phase transactions.""" diff --git a/test/sql/test_compare.py b/test/sql/test_compare.py index 774f9792f4..0138228ac8 100644 --- a/test/sql/test_compare.py +++ b/test/sql/test_compare.py @@ -58,6 +58,8 @@ from sqlalchemy.sql.elements import CollationClause from sqlalchemy.sql.elements import DMLTargetCopy from sqlalchemy.sql.elements import DQLDMLClauseElement from sqlalchemy.sql.elements import ElementList +from sqlalchemy.sql.elements import FrameClause +from sqlalchemy.sql.elements import FrameClauseType from sqlalchemy.sql.elements import Immutable from sqlalchemy.sql.elements import Null from sqlalchemy.sql.elements import OrderByList @@ -487,6 +489,33 @@ class CoreFixtures: func.row_number().over(order_by=table_a.c.a, range_=(None, 10)), func.row_number().over(order_by=table_a.c.a, rows=(None, 20)), func.row_number().over(order_by=table_a.c.a, groups=(None, 20)), + func.row_number().over( + order_by=table_a.c.a, + range_=FrameClause( + 2, + 3, + FrameClauseType.FOLLOWING, + FrameClauseType.PRECEDING, + ), + ), + func.row_number().over( + order_by=table_a.c.a, + rows=FrameClause( + 2, + 3, + FrameClauseType.FOLLOWING, + FrameClauseType.PRECEDING, + ), + ), + func.row_number().over( + order_by=table_a.c.a, + groups=FrameClause( + 2, + 3, + FrameClauseType.FOLLOWING, + FrameClauseType.PRECEDING, + ), + ), func.row_number().over(order_by=table_a.c.b), func.row_number().over( order_by=table_a.c.a, partition_by=table_a.c.b @@ -1700,10 +1729,9 @@ class HasCacheKeySubclass(fixtures.TestBase): }, {"columns", "name", "literal_binds"}, ), - "_FrameClause": ( - {"upper_integer_bind", "upper_type"} - | {"lower_type", "lower_integer_bind"}, - {"range_"}, + "FrameClause": ( + {"upper_bind", "upper_type", "lower_type", "lower_bind"}, + {"start", "end", "start_frame_type", "end_frame_type"}, ), "_MemoizedSelectEntities": ( {"_with_options", "_raw_columns", "_setup_joins"}, diff --git a/test/sql/test_compiler.py b/test/sql/test_compiler.py index c1feee694c..377f05b24f 100644 --- a/test/sql/test_compiler.py +++ b/test/sql/test_compiler.py @@ -92,6 +92,8 @@ from sqlalchemy.sql import util as sql_util from sqlalchemy.sql.elements import BooleanClauseList from sqlalchemy.sql.elements import ColumnElement from sqlalchemy.sql.elements import CompilerColumnElement +from sqlalchemy.sql.elements import FrameClause +from sqlalchemy.sql.elements import FrameClauseType from sqlalchemy.sql.elements import Grouping from sqlalchemy.sql.expression import ClauseElement from sqlalchemy.sql.expression import ClauseList @@ -3175,6 +3177,32 @@ class SelectTest(fixtures.TestBase, AssertsCompiledSQL): " AS anon_1 FROM mytable", ) + self.assert_compile( + select(func.row_number().over(order_by=expr, rows=(-10, 1))), + "SELECT row_number() OVER " + "(ORDER BY mytable.myid ROWS BETWEEN " + ":param_1 PRECEDING AND :param_2 FOLLOWING)" + " AS anon_1 FROM mytable", + checkparams={"param_1": 10, "param_2": 1}, + ) + + RF = FrameClauseType.FOLLOWING + RP = FrameClauseType.PRECEDING + + self.assert_compile( + select( + func.row_number().over( + order_by=expr, + rows=FrameClause(3, 2, RF, RP), + ) + ), + "SELECT row_number() OVER " + "(ORDER BY mytable.myid ROWS BETWEEN " + ":param_1 FOLLOWING AND :param_2 PRECEDING)" + " AS anon_1 FROM mytable", + checkparams={"param_1": 3, "param_2": 2}, + ) + self.assert_compile( select(func.row_number().over(order_by=expr, range_=(None, 0))), "SELECT row_number() OVER " @@ -3210,6 +3238,19 @@ class SelectTest(fixtures.TestBase, AssertsCompiledSQL): checkparams={"param_1": 10, "param_2": 1}, ) + self.assert_compile( + select( + func.row_number().over( + order_by=expr, range_=FrameClause("a", "x", RP, RF) + ) + ), + "SELECT row_number() OVER " + "(ORDER BY mytable.myid RANGE BETWEEN " + ":param_1 PRECEDING AND :param_2 FOLLOWING)" + " AS anon_1 FROM mytable", + checkparams={"param_1": "a", "param_2": "x"}, + ) + self.assert_compile( select(func.row_number().over(order_by=expr, groups=(None, 0))), "SELECT row_number() OVER " @@ -3245,53 +3286,103 @@ class SelectTest(fixtures.TestBase, AssertsCompiledSQL): checkparams={"param_1": 10, "param_2": 1}, ) + self.assert_compile( + select( + func.row_number().over( + order_by=expr, + groups=FrameClause(1, 3, RP, RF), + ) + ), + "SELECT row_number() OVER " + "(ORDER BY mytable.myid GROUPS BETWEEN " + ":param_1 PRECEDING AND :param_2 FOLLOWING)" + " AS anon_1 FROM mytable", + checkparams={"param_1": 1, "param_2": 3}, + ) + def test_over_invalid_framespecs(self): - assert_raises_message( + with expect_raises_message( exc.ArgumentError, - "Integer or None expected for range value", - func.row_number().over, - range_=("foo", 8), - ) + "Integer or None expected for values in rows/groups frame", + ): + func.row_number().over(rows=("foo", 8)) - assert_raises_message( + with expect_raises_message( exc.ArgumentError, - "Integer or None expected for range value", - func.row_number().over, - range_=(-5, "foo"), - ) + "Integer or None expected for values in rows/groups frame", + ): + func.row_number().over(groups=(-5, "foo")) - assert_raises_message( + with expect_raises_message( + exc.ArgumentError, + "When using a tuple to specify a range only integer or none " + "values are allowed in the range frame. To specify a " + "different type use the FrameClause directly.", + ): + func.row_number().over(range_=(-5, "foo")) + with expect_raises_message( + exc.ArgumentError, + "2-tuple expected for range/rows/groups", + ): + func.row_number().over(rows=("foo",)) + + with expect_raises_message( + exc.ArgumentError, + "2-tuple expected for range/rows/groups", + ): + func.row_number().over(groups=(-5, "foo", 1)) + + with expect_raises_message( + exc.ArgumentError, "2-tuple expected for range/rows/groups" + ): + func.row_number().over(range_=(-5,)) + + with expect_raises_message( exc.ArgumentError, "only one of 'rows', 'range_', or 'groups' may be provided", - func.row_number().over, - range_=(-5, 8), - rows=(-2, 5), - ) + ): + func.row_number().over(range_=(-5, 8), rows=(-2, 5)) - assert_raises_message( + with expect_raises_message( exc.ArgumentError, "only one of 'rows', 'range_', or 'groups' may be provided", - func.row_number().over, - range_=(-5, 8), - groups=(None, None), - ) + ): + func.row_number().over(range_=(-5, 8), groups=(None, None)) - assert_raises_message( + with expect_raises_message( exc.ArgumentError, "only one of 'rows', 'range_', or 'groups' may be provided", - func.row_number().over, - rows=(-2, 5), - groups=(None, None), - ) + ): + func.row_number().over(rows=(-2, 5), groups=(None, None)) - assert_raises_message( + with expect_raises_message( exc.ArgumentError, "only one of 'rows', 'range_', or 'groups' may be provided", - func.row_number().over, - range_=(-5, 8), - rows=(-2, 5), - groups=(None, None), - ) + ): + func.row_number().over( + range_=(-5, 8), rows=(-2, 5), groups=(None, None) + ) + + with expect_raises_message( + exc.ArgumentError, + "Cannot specify a value for start with frame type " "CURRENT", + ): + FrameClause( + 5, + None, + FrameClauseType.CURRENT, + FrameClauseType.UNBOUNDED, + ) + with expect_raises_message( + exc.ArgumentError, + "Cannot specify a value for end with frame type " "UNBOUNDED", + ): + FrameClause( + None, + 5, + FrameClauseType.CURRENT, + FrameClauseType.UNBOUNDED, + ) def test_over_within_group(self): from sqlalchemy import within_group diff --git a/test/sql/test_functions.py b/test/sql/test_functions.py index 19c8930731..c64aec7064 100644 --- a/test/sql/test_functions.py +++ b/test/sql/test_functions.py @@ -48,6 +48,8 @@ from sqlalchemy.sql import table from sqlalchemy.sql import util from sqlalchemy.sql.compiler import AggregateOrderByStyle from sqlalchemy.sql.compiler import BIND_TEMPLATES +from sqlalchemy.sql.elements import FrameClause +from sqlalchemy.sql.elements import FrameClauseType from sqlalchemy.sql.functions import FunctionElement from sqlalchemy.sql.functions import GenericFunction from sqlalchemy.testing import assert_raises @@ -68,6 +70,7 @@ table1 = table( column("myid", Integer), column("name", String), column("description", String), + column("myfloat", Float), ) @@ -866,6 +869,27 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL): checkparams={"name_1": "foo", "param_1": 1, "param_2": 5}, ) + self.assert_compile( + select( + func.rank() + .filter(table1.c.name > "foo") + .over( + range_=FrameClause( + 3.14, + 2.71, + FrameClauseType.PRECEDING, + FrameClauseType.FOLLOWING, + ), + partition_by=["myfloat"], + ) + ), + "SELECT rank() FILTER (WHERE mytable.name > :name_1) " + "OVER (PARTITION BY mytable.myfloat RANGE BETWEEN :param_1 " + "PRECEDING AND :param_2 FOLLOWING) " + "AS anon_1 FROM mytable", + checkparams={"name_1": "foo", "param_1": 3.14, "param_2": 2.71}, + ) + def test_funcfilter_windowing_range_positional(self): self.assert_compile( select( -- 2.47.3