summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--doc/build/changelog/changelog_11.rst19
-rw-r--r--doc/build/changelog/migration_11.rst39
-rw-r--r--doc/build/core/sqlelement.rst5
-rw-r--r--lib/sqlalchemy/sql/compiler.py8
-rw-r--r--lib/sqlalchemy/sql/elements.py141
-rw-r--r--lib/sqlalchemy/sql/expression.py5
-rw-r--r--lib/sqlalchemy/sql/functions.py159
-rw-r--r--lib/sqlalchemy/sql/util.py7
-rw-r--r--test/dialect/postgresql/test_compiler.py2
-rw-r--r--test/dialect/postgresql/test_types.py12
-rw-r--r--test/sql/test_functions.py125
-rw-r--r--test/sql/test_selectable.py4
12 files changed, 477 insertions, 49 deletions
diff --git a/doc/build/changelog/changelog_11.rst b/doc/build/changelog/changelog_11.rst
index 0a6543575..350a7c4d2 100644
--- a/doc/build/changelog/changelog_11.rst
+++ b/doc/build/changelog/changelog_11.rst
@@ -22,6 +22,21 @@
:version: 1.1.0b1
.. change::
+ :tags: feature, sql
+ :tickets: 1370
+
+ Added support for "set-aggregate" functions of the form
+ ``<function> WITHIN GROUP (ORDER BY <criteria>)``, using the
+ method :meth:`.FunctionElement.within_group`. A series of common
+ set-aggregate functions with return types derived from the set have
+ been added. This includes functions like :class:`.percentile_cont`,
+ :class:`.dense_rank` and others.
+
+ .. seealso::
+
+ :ref:`change_3132`
+
+ .. change::
:tags: feature, sql, postgresql
:tickets: 3132
@@ -31,6 +46,10 @@
supported on Postgresql at the moment, only actually works on
Postgresql.
+ .. seealso::
+
+ :ref:`change_3132`
+
.. change::
:tags: feature, sql
:tickets: 3516
diff --git a/doc/build/changelog/migration_11.rst b/doc/build/changelog/migration_11.rst
index f4cadeea5..c146e2443 100644
--- a/doc/build/changelog/migration_11.rst
+++ b/doc/build/changelog/migration_11.rst
@@ -16,7 +16,7 @@ What's New in SQLAlchemy 1.1?
some issues may be moved to later milestones in order to allow
for a timely release.
- Document last updated: July 24, 2015.
+ Document last updated: August 26, 2015
Introduction
============
@@ -263,6 +263,43 @@ such as::
:ticket:`3516`
+.. _change_3132:
+
+New Function features, "WITHIN GROUP", array_agg and set aggregate functions
+----------------------------------------------------------------------------
+
+With the new :class:`.Array` type we can also implement a pre-typed
+function for the ``array_agg()`` SQL function that returns an array,
+which is now available using :class:`.array_agg`::
+
+ from sqlalchemy import func
+ stmt = select([func.array_agg(table.c.value)])
+
+Additionally, functions like ``percentile_cont()``, ``percentile_disc()``,
+``rank()``, ``dense_rank()`` and others that require an ordering via
+``WITHIN GROUP (ORDER BY <expr>)`` are now available via the
+:meth:`.FunctionElement.within_group` modifier::
+
+ from sqlalchemy import func
+ stmt = select([
+ department.c.id,
+ func.percentile_cont(0.5).within_group(
+ department.c.salary.desc()
+ )
+ ])
+
+The above statement would produce SQL similar to::
+
+ SELECT department.id, percentile_cont(0.5)
+ WITHIN GROUP (ORDER BY department.salary DESC)
+
+Placeholders with correct return types are now provided for these functions,
+and include :class:`.percentile_cont`, :class:`.percentile_disc`,
+:class:`.rank`, :class:`.dense_rank`, :class:`.mode`, :class:`.percent_rank`,
+and :class:`.cume_dist`.
+
+:ticket:`3132` :ticket:`1370`
+
Key Behavioral Changes - ORM
============================
diff --git a/doc/build/core/sqlelement.rst b/doc/build/core/sqlelement.rst
index d2019f71e..30a6ed568 100644
--- a/doc/build/core/sqlelement.rst
+++ b/doc/build/core/sqlelement.rst
@@ -69,6 +69,8 @@ used to construct any kind of typed SQL expression.
.. autofunction:: type_coerce
+.. autofunction:: within_group
+
.. autoclass:: BinaryExpression
:members:
@@ -133,6 +135,9 @@ used to construct any kind of typed SQL expression.
.. autoclass:: Tuple
:members:
+.. autoclass:: WithinGroup
+ :members:
+
.. autoclass:: sqlalchemy.sql.elements.True_
:members:
diff --git a/lib/sqlalchemy/sql/compiler.py b/lib/sqlalchemy/sql/compiler.py
index a5a3975b1..52116a231 100644
--- a/lib/sqlalchemy/sql/compiler.py
+++ b/lib/sqlalchemy/sql/compiler.py
@@ -772,7 +772,7 @@ class SQLCompiler(Compiled):
def visit_over(self, over, **kwargs):
return "%s OVER (%s)" % (
- over.func._compiler_dispatch(self, **kwargs),
+ over.element._compiler_dispatch(self, **kwargs),
' '.join(
'%s BY %s' % (word, clause._compiler_dispatch(self, **kwargs))
for word, clause in (
@@ -783,6 +783,12 @@ class SQLCompiler(Compiled):
)
)
+ def visit_withingroup(self, withingroup, **kwargs):
+ return "%s WITHIN GROUP (ORDER BY %s)" % (
+ withingroup.element._compiler_dispatch(self, **kwargs),
+ withingroup.order_by._compiler_dispatch(self, **kwargs)
+ )
+
def visit_funcfilter(self, funcfilter, **kwargs):
return "%s FILTER (WHERE %s)" % (
funcfilter.func._compiler_dispatch(self, **kwargs),
diff --git a/lib/sqlalchemy/sql/elements.py b/lib/sqlalchemy/sql/elements.py
index d5d364c77..618b987e1 100644
--- a/lib/sqlalchemy/sql/elements.py
+++ b/lib/sqlalchemy/sql/elements.py
@@ -2970,21 +2970,21 @@ class Over(ColumnElement):
order_by = None
partition_by = None
- def __init__(self, func, partition_by=None, order_by=None):
+ def __init__(self, element, partition_by=None, order_by=None):
"""Produce an :class:`.Over` object against a function.
Used against aggregate or so-called "window" functions,
for database backends that support window functions.
- E.g.::
+ :func:`~.expression.over` is usually called using
+ the :meth:`.FunctionElement.over` method, e.g.::
- from sqlalchemy import over
- over(func.row_number(), order_by='x')
+ func.row_number().over(order_by='x')
- Would produce "ROW_NUMBER() OVER(ORDER BY x)".
+ Would produce ``ROW_NUMBER() OVER(ORDER BY x)``.
- :param func: a :class:`.FunctionElement` construct, typically
- generated by :data:`~.expression.func`.
+ :param element: a :class:`.FunctionElement`, :class:`.WithinGroup`,
+ or other compatible construct.
:param partition_by: a column element or string, or a list
of such, that will be used as the PARTITION BY clause
of the OVER construct.
@@ -2997,8 +2997,14 @@ class Over(ColumnElement):
.. versionadded:: 0.7
+ .. seealso::
+
+ :data:`.expression.func`
+
+ :func:`.expression.within_group`
+
"""
- self.func = func
+ self.element = element
if order_by is not None:
self.order_by = ClauseList(
*util.to_list(order_by),
@@ -3008,17 +3014,29 @@ class Over(ColumnElement):
*util.to_list(partition_by),
_literal_as_text=_literal_as_label_reference)
+ @property
+ def func(self):
+ """the element referred to by this :class:`.Over`
+ clause.
+
+ .. deprecated:: 1.1 the ``func`` element has been renamed to
+ ``.element``. The two attributes are synonymous though
+ ``.func`` is read-only.
+
+ """
+ return self.element
+
@util.memoized_property
def type(self):
- return self.func.type
+ return self.element.type
def get_children(self, **kwargs):
return [c for c in
- (self.func, self.partition_by, self.order_by)
+ (self.element, self.partition_by, self.order_by)
if c is not None]
def _copy_internals(self, clone=_clone, **kw):
- self.func = clone(self.func, **kw)
+ self.element = clone(self.element, **kw)
if self.partition_by is not None:
self.partition_by = clone(self.partition_by, **kw)
if self.order_by is not None:
@@ -3028,7 +3046,106 @@ class Over(ColumnElement):
def _from_objects(self):
return list(itertools.chain(
*[c._from_objects for c in
- (self.func, self.partition_by, self.order_by)
+ (self.element, self.partition_by, self.order_by)
+ if c is not None]
+ ))
+
+
+class WithinGroup(ColumnElement):
+ """Represent a WITHIN GROUP (ORDER BY) clause.
+
+ This is a special operator against so-called
+ so-called "ordered set aggregate" and "hypothetical
+ set aggregate" functions, including ``percentile_cont()``,
+ ``rank()``, ``dense_rank()``, etc.
+
+ It's supported only by certain database backends, such as PostgreSQL,
+ Oracle and MS SQL Server.
+
+ The :class:`.WithinGroup` consturct extracts its type from the
+ method :meth:`.FunctionElement.within_group_type`. If this returns
+ ``None``, the function's ``.type`` is used.
+
+ """
+ __visit_name__ = 'withingroup'
+
+ order_by = None
+
+ def __init__(self, element, *order_by):
+ """Produce a :class:`.WithinGroup` object against a function.
+
+ Used against so-called "ordered set aggregate" and "hypothetical
+ set aggregate" functions, including :class:`.percentile_cont`,
+ :class:`.rank`, :class:`.dense_rank`, etc.
+
+ :func:`~.expression.within_group` is usually called using
+ the :meth:`.FunctionElement.within_group` method, e.g.::
+
+ from sqlalchemy import within_group
+ stmt = select([
+ department.c.id,
+ func.percentile_cont(0.5).within_group(
+ department.c.salary.desc()
+ )
+ ])
+
+ The above statement would produce SQL similar to
+ ``SELECT department.id, percentile_cont(0.5)
+ WITHIN GROUP (ORDER BY department.salary DESC)``.
+
+ :param element: a :class:`.FunctionElement` construct, typically
+ generated by :data:`~.expression.func`.
+ :param \*order_by: one or more column elements that will be used
+ as the ORDER BY clause of the WITHIN GROUP construct.
+
+ .. versionadded:: 1.1
+
+ .. seealso::
+
+ :data:`.expression.func`
+
+ :func:`.expression.over`
+
+ """
+ self.element = element
+ if order_by is not None:
+ self.order_by = ClauseList(
+ *util.to_list(order_by),
+ _literal_as_text=_literal_as_label_reference)
+
+ def over(self, partition_by=None, order_by=None):
+ """Produce an OVER clause against this :class:`.WithinGroup`
+ construct.
+
+ This function has the same signature as that of
+ :meth:`.FunctionElement.over`.
+
+ """
+ return Over(self, partition_by=partition_by, order_by=order_by)
+
+ @util.memoized_property
+ def type(self):
+ wgt = self.element.within_group_type(self)
+ if wgt is not None:
+ return wgt
+ else:
+ return self.element.type
+
+ def get_children(self, **kwargs):
+ return [c for c in
+ (self.func, self.order_by)
+ if c is not None]
+
+ def _copy_internals(self, clone=_clone, **kw):
+ self.element = clone(self.element, **kw)
+ if self.order_by is not None:
+ self.order_by = clone(self.order_by, **kw)
+
+ @property
+ def _from_objects(self):
+ return list(itertools.chain(
+ *[c._from_objects for c in
+ (self.element, self.order_by)
if c is not None]
))
diff --git a/lib/sqlalchemy/sql/expression.py b/lib/sqlalchemy/sql/expression.py
index 9bd424e21..79d25a39e 100644
--- a/lib/sqlalchemy/sql/expression.py
+++ b/lib/sqlalchemy/sql/expression.py
@@ -24,7 +24,7 @@ __all__ = [
'literal', 'literal_column', 'not_', 'null', 'nullsfirst', 'nullslast',
'or_', 'outparam', 'outerjoin', 'over', 'select', 'subquery',
'table', 'text',
- 'tuple_', 'type_coerce', 'union', 'union_all', 'update']
+ 'tuple_', 'type_coerce', 'union', 'union_all', 'update', 'within_group']
from .visitors import Visitable
@@ -34,7 +34,7 @@ from .elements import ClauseElement, ColumnElement,\
BindParameter, CollectionAggregate, UnaryExpression, BooleanClauseList, \
Label, Cast, Case, ColumnClause, TextClause, Over, Null, \
True_, False_, BinaryExpression, Tuple, TypeClause, Extract, \
- Grouping, not_, \
+ Grouping, WithinGroup, not_, \
collate, literal_column, between,\
literal, outparam, type_coerce, ClauseList, FunctionFilter
@@ -67,6 +67,7 @@ text = public_factory(TextClause._create_text, ".expression.text")
table = public_factory(TableClause, ".expression.table")
column = public_factory(ColumnClause, ".expression.column")
over = public_factory(Over, ".expression.over")
+within_group = public_factory(WithinGroup, ".expression.within_group")
label = public_factory(Label, ".expression.label")
case = public_factory(Case, ".expression.case")
cast = public_factory(Cast, ".expression.cast")
diff --git a/lib/sqlalchemy/sql/functions.py b/lib/sqlalchemy/sql/functions.py
index d536c3008..d5d0eb7f2 100644
--- a/lib/sqlalchemy/sql/functions.py
+++ b/lib/sqlalchemy/sql/functions.py
@@ -12,9 +12,9 @@ from . import sqltypes, schema
from .base import Executable, ColumnCollection
from .elements import ClauseList, Cast, Extract, _literal_as_binds, \
literal_column, _type_from_args, ColumnElement, _clone,\
- Over, BindParameter, FunctionFilter, Grouping
+ Over, BindParameter, FunctionFilter, Grouping, WithinGroup
from .selectable import FromClause, Select, Alias
-
+from . import util as sqlutil
from . import operators
from .visitors import VisitableType
from .. import util
@@ -116,6 +116,21 @@ class FunctionElement(Executable, ColumnElement, FromClause):
"""
return Over(self, partition_by=partition_by, order_by=order_by)
+ def within_group(self, *order_by):
+ """Produce a WITHIN GROUP (ORDER BY expr) clause against this function.
+
+ Used against so-called "ordered set aggregate" and "hypothetical
+ set aggregate" functions, including :class:`.percentile_cont`,
+ :class:`.rank`, :class:`.dense_rank`, etc.
+
+ See :func:`~.expression.within_group` for a full description.
+
+ .. versionadded:: 1.1
+
+
+ """
+ return WithinGroup(self, *order_by)
+
def filter(self, *criterion):
"""Produce a FILTER clause against this function.
@@ -157,6 +172,18 @@ class FunctionElement(Executable, ColumnElement, FromClause):
self._reset_exported()
FunctionElement.clauses._reset(self)
+ def within_group_type(self, within_group):
+ """For types that define their return type as based on the criteria
+ within a WITHIN GROUP (ORDER BY) expression, called by the
+ :class:`.WithinGroup` construct.
+
+ Returns None by default, in which case the function's normal ``.type``
+ is used.
+
+ """
+
+ return None
+
def alias(self, name=None, flat=False):
"""Produce a :class:`.Alias` construct against this
:class:`.FunctionElement`.
@@ -493,7 +520,7 @@ class GenericFunction(util.with_metaclass(_GenericMeta, Function)):
def __init__(self, *args, **kwargs):
parsed_args = kwargs.pop('_parsed_args', None)
if parsed_args is None:
- parsed_args = [_literal_as_binds(c) for c in args]
+ parsed_args = [_literal_as_binds(c, self.name) for c in args]
self.packagenames = []
self._bind = kwargs.get('bind', None)
self.clause_expr = ClauseList(
@@ -538,10 +565,10 @@ class ReturnTypeFromArgs(GenericFunction):
"""Define a function whose return type is the same as its arguments."""
def __init__(self, *args, **kwargs):
- args = [_literal_as_binds(c) for c in args]
+ args = [_literal_as_binds(c, self.name) for c in args]
kwargs.setdefault('type_', _type_from_args(args))
kwargs['_parsed_args'] = args
- GenericFunction.__init__(self, *args, **kwargs)
+ super(ReturnTypeFromArgs, self).__init__(*args, **kwargs)
class coalesce(ReturnTypeFromArgs):
@@ -589,7 +616,7 @@ class count(GenericFunction):
def __init__(self, expression=None, **kwargs):
if expression is None:
expression = literal_column('*')
- GenericFunction.__init__(self, expression, **kwargs)
+ super(count, self).__init__(expression, **kwargs)
class current_date(AnsiFunction):
@@ -646,5 +673,123 @@ class array_agg(GenericFunction):
args = [_literal_as_binds(c) for c in args]
kwargs.setdefault('type_', sqltypes.Array(_type_from_args(args)))
kwargs['_parsed_args'] = args
- GenericFunction.__init__(self, *args, **kwargs)
+ super(array_agg, self).__init__(*args, **kwargs)
+
+
+class OrderedSetAgg(GenericFunction):
+ """Define a function where the return type is based on the sort
+ expression type as defined by the expression passed to the
+ :meth:`.FunctionElement.within_group` method."""
+
+ array_for_multi_clause = False
+
+ def within_group_type(self, within_group):
+ func_clauses = self.clause_expr.element
+ order_by = sqlutil.unwrap_order_by(within_group.order_by)
+ if self.array_for_multi_clause and len(func_clauses.clauses) > 1:
+ return sqltypes.Array(order_by[0].type)
+ else:
+ return order_by[0].type
+
+
+class mode(OrderedSetAgg):
+ """implement the ``mode`` ordered-set aggregate function.
+
+ This function must be used with the :meth:`.FunctionElement.within_group`
+ modifier to supply a sort expression to operate upon.
+
+ The return type of this function is the same as the sort expression.
+
+ .. versionadded:: 1.1
+
+ """
+
+
+class percentile_cont(OrderedSetAgg):
+ """implement the ``percentile_cont`` ordered-set aggregate function.
+
+ This function must be used with the :meth:`.FunctionElement.within_group`
+ modifier to supply a sort expression to operate upon.
+
+ The return type of this function is the same as the sort expression,
+ or if the arguments are an array, an :class:`.Array` of the sort
+ expression's type.
+
+ .. versionadded:: 1.1
+
+ """
+
+ array_for_multi_clause = True
+
+
+class percentile_disc(OrderedSetAgg):
+ """implement the ``percentile_disc`` ordered-set aggregate function.
+
+ This function must be used with the :meth:`.FunctionElement.within_group`
+ modifier to supply a sort expression to operate upon.
+ The return type of this function is the same as the sort expression,
+ or if the arguments are an array, an :class:`.Array` of the sort
+ expression's type.
+
+ .. versionadded:: 1.1
+
+ """
+
+ array_for_multi_clause = True
+
+
+class rank(GenericFunction):
+ """Implement the ``rank`` hypothetical-set aggregate function.
+
+ This function must be used with the :meth:`.FunctionElement.within_group`
+ modifier to supply a sort expression to operate upon.
+
+ The return type of this function is :class:`.Integer`.
+
+ .. versionadded:: 1.1
+
+ """
+ type = sqltypes.Integer()
+
+
+class dense_rank(GenericFunction):
+ """Implement the ``dense_rank`` hypothetical-set aggregate function.
+
+ This function must be used with the :meth:`.FunctionElement.within_group`
+ modifier to supply a sort expression to operate upon.
+
+ The return type of this function is :class:`.Integer`.
+
+ .. versionadded:: 1.1
+
+ """
+ type = sqltypes.Integer()
+
+
+class percent_rank(GenericFunction):
+ """Implement the ``percent_rank`` hypothetical-set aggregate function.
+
+ This function must be used with the :meth:`.FunctionElement.within_group`
+ modifier to supply a sort expression to operate upon.
+
+ The return type of this function is :class:`.Numeric`.
+
+ .. versionadded:: 1.1
+
+ """
+ type = sqltypes.Numeric()
+
+
+class cume_dist(GenericFunction):
+ """Implement the ``cume_dist`` hypothetical-set aggregate function.
+
+ This function must be used with the :meth:`.FunctionElement.within_group`
+ modifier to supply a sort expression to operate upon.
+
+ The return type of this function is :class:`.Numeric`.
+
+ .. versionadded:: 1.1
+
+ """
+ type = sqltypes.Numeric()
diff --git a/lib/sqlalchemy/sql/util.py b/lib/sqlalchemy/sql/util.py
index 8f502fc86..cbd74faac 100644
--- a/lib/sqlalchemy/sql/util.py
+++ b/lib/sqlalchemy/sql/util.py
@@ -154,6 +154,7 @@ def unwrap_order_by(clause):
without DESC/ASC/NULLS FIRST/NULLS LAST"""
cols = util.column_set()
+ result = []
stack = deque([clause])
while stack:
t = stack.popleft()
@@ -166,11 +167,13 @@ def unwrap_order_by(clause):
t = t.element
if isinstance(t, (_textual_label_reference)):
continue
- cols.add(t)
+ if t not in cols:
+ cols.add(t)
+ result.append(t)
else:
for c in t.get_children():
stack.append(c)
- return cols
+ return result
def clause_is_present(clause, search):
diff --git a/test/dialect/postgresql/test_compiler.py b/test/dialect/postgresql/test_compiler.py
index 1489fe24c..0407dcb81 100644
--- a/test/dialect/postgresql/test_compiler.py
+++ b/test/dialect/postgresql/test_compiler.py
@@ -693,7 +693,7 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL):
self._test_array_zero_indexes(False)
def test_array_literal_type(self):
- is_(postgresql.array([1, 2]).type._type_affinity, postgresql.ARRAY)
+ isinstance(postgresql.array([1, 2]).type, postgresql.ARRAY)
is_(postgresql.array([1, 2]).type.item_type._type_affinity, Integer)
is_(postgresql.array([1, 2], type_=String).
diff --git a/test/dialect/postgresql/test_types.py b/test/dialect/postgresql/test_types.py
index da45c2f2a..a625e1cee 100644
--- a/test/dialect/postgresql/test_types.py
+++ b/test/dialect/postgresql/test_types.py
@@ -1710,7 +1710,7 @@ class HStoreTest(AssertsCompiledSQL, fixtures.TestBase):
def test_where_defined(self):
self._test_where(
self.hashcol.defined('foo'),
- "defined(test_table.hash, %(param_1)s)"
+ "defined(test_table.hash, %(defined_1)s)"
)
def test_where_contains(self):
@@ -1741,7 +1741,7 @@ class HStoreTest(AssertsCompiledSQL, fixtures.TestBase):
def test_cols_delete_single_key(self):
self._test_cols(
self.hashcol.delete('foo'),
- "delete(test_table.hash, %(param_1)s) AS delete_1",
+ "delete(test_table.hash, %(delete_2)s) AS delete_1",
True
)
@@ -1756,7 +1756,7 @@ class HStoreTest(AssertsCompiledSQL, fixtures.TestBase):
def test_cols_delete_matching_pairs(self):
self._test_cols(
self.hashcol.delete(hstore('1', '2')),
- ("delete(test_table.hash, hstore(%(param_1)s, %(param_2)s)) "
+ ("delete(test_table.hash, hstore(%(hstore_1)s, %(hstore_2)s)) "
"AS delete_1"),
True
)
@@ -1772,7 +1772,7 @@ class HStoreTest(AssertsCompiledSQL, fixtures.TestBase):
def test_cols_hstore_pair_text(self):
self._test_cols(
hstore('foo', '3')['foo'],
- "hstore(%(param_1)s, %(param_2)s) -> %(hstore_1)s AS anon_1",
+ "hstore(%(hstore_1)s, %(hstore_2)s) -> %(hstore_3)s AS anon_1",
False
)
@@ -1797,14 +1797,14 @@ class HStoreTest(AssertsCompiledSQL, fixtures.TestBase):
self._test_cols(
self.hashcol.concat(hstore(cast(self.test_table.c.id, Text), '3')),
("test_table.hash || hstore(CAST(test_table.id AS TEXT), "
- "%(param_1)s) AS anon_1"),
+ "%(hstore_1)s) AS anon_1"),
True
)
def test_cols_concat_op(self):
self._test_cols(
hstore('foo', 'bar') + self.hashcol,
- "hstore(%(param_1)s, %(param_2)s) || test_table.hash AS anon_1",
+ "hstore(%(hstore_1)s, %(hstore_2)s) || test_table.hash AS anon_1",
True
)
diff --git a/test/sql/test_functions.py b/test/sql/test_functions.py
index f080046ff..51cfcb919 100644
--- a/test/sql/test_functions.py
+++ b/test/sql/test_functions.py
@@ -2,7 +2,7 @@ from sqlalchemy.testing import eq_, is_
import datetime
from sqlalchemy import func, select, Integer, literal, DateTime, Table, \
Column, Sequence, MetaData, extract, Date, String, bindparam, \
- literal_column, Array
+ literal_column, Array, Numeric
from sqlalchemy.sql import table, column
from sqlalchemy import sql, util
from sqlalchemy.sql.compiler import BIND_TEMPLATES
@@ -52,7 +52,7 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL):
self.assert_compile(
fake_func('foo'),
"fake_func(%s)" %
- bindtemplate % {'name': 'param_1', 'position': 1},
+ bindtemplate % {'name': 'fake_func_1', 'position': 1},
dialect=dialect)
def test_use_labels(self):
@@ -89,7 +89,7 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL):
def test_generic_annotation(self):
fn = func.coalesce('x', 'y')._annotate({"foo": "bar"})
self.assert_compile(
- fn, "coalesce(:param_1, :param_2)"
+ fn, "coalesce(:coalesce_1, :coalesce_2)"
)
def test_custom_default_namespace(self):
@@ -140,7 +140,7 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL):
self.assert_compile(
func.my_func(1, 2),
- "my_func(:param_1, :param_2, :param_3)"
+ "my_func(:my_func_1, :my_func_2, :my_func_3)"
)
def test_custom_registered_identifier(self):
@@ -178,7 +178,7 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL):
self.assert_compile(
myfunc(1, 2, 3),
- "myfunc(:param_1, :param_2, :param_3)"
+ "myfunc(:myfunc_1, :myfunc_2, :myfunc_3)"
)
def test_namespacing_conflicts(self):
@@ -188,7 +188,7 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL):
assert isinstance(func.count().type, sqltypes.Integer)
self.assert_compile(func.count(), 'count(*)')
- self.assert_compile(func.count(1), 'count(:param_1)')
+ self.assert_compile(func.count(1), 'count(:count_1)')
c = column('abc')
self.assert_compile(func.count(c), 'count(abc)')
@@ -378,7 +378,7 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL):
def test_funcfilter_empty(self):
self.assert_compile(
func.count(1).filter(),
- "count(:param_1)"
+ "count(:count_1)"
)
def test_funcfilter_criterion(self):
@@ -386,7 +386,7 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL):
func.count(1).filter(
table1.c.name != None
),
- "count(:param_1) FILTER (WHERE mytable.name IS NOT NULL)"
+ "count(:count_1) FILTER (WHERE mytable.name IS NOT NULL)"
)
def test_funcfilter_compound_criterion(self):
@@ -395,7 +395,7 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL):
table1.c.name == None,
table1.c.myid > 0
),
- "count(:param_1) FILTER (WHERE mytable.name IS NULL AND "
+ "count(:count_1) FILTER (WHERE mytable.name IS NULL AND "
"mytable.myid > :myid_1)"
)
@@ -404,7 +404,7 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL):
select([func.count(1).filter(
table1.c.description != None
).label('foo')]),
- "SELECT count(:param_1) FILTER (WHERE mytable.description "
+ "SELECT count(:count_1) FILTER (WHERE mytable.description "
"IS NOT NULL) AS foo FROM mytable"
)
@@ -429,7 +429,7 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL):
table1.c.name == 'name'
)
]),
- "SELECT count(:param_1) FILTER (WHERE mytable.name = :name_1) "
+ "SELECT count(:count_1) FILTER (WHERE mytable.name = :name_1) "
"AS anon_1 FROM mytable"
)
@@ -443,7 +443,7 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL):
table1.c.description == 'description'
)
]),
- "SELECT count(:param_1) FILTER (WHERE "
+ "SELECT count(:count_1) FILTER (WHERE "
"mytable.name = :name_1 AND mytable.description = :description_1) "
"AS anon_1 FROM mytable"
)
@@ -477,6 +477,70 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL):
"AS anon_1 FROM mytable"
)
+ def test_funcfilter_within_group(self):
+ stmt = select([
+ table1.c.myid,
+ func.percentile_cont(0.5).within_group(
+ table1.c.name
+ )
+ ])
+ self.assert_compile(
+ stmt,
+ "SELECT mytable.myid, percentile_cont(:percentile_cont_1) "
+ "WITHIN GROUP (ORDER BY mytable.name) "
+ "AS anon_1 "
+ "FROM mytable",
+ {'percentile_cont_1': 0.5}
+ )
+
+ def test_funcfilter_within_group_multi(self):
+ stmt = select([
+ table1.c.myid,
+ func.percentile_cont(0.5).within_group(
+ table1.c.name, table1.c.description
+ )
+ ])
+ self.assert_compile(
+ stmt,
+ "SELECT mytable.myid, percentile_cont(:percentile_cont_1) "
+ "WITHIN GROUP (ORDER BY mytable.name, mytable.description) "
+ "AS anon_1 "
+ "FROM mytable",
+ {'percentile_cont_1': 0.5}
+ )
+
+ def test_funcfilter_within_group_desc(self):
+ stmt = select([
+ table1.c.myid,
+ func.percentile_cont(0.5).within_group(
+ table1.c.name.desc()
+ )
+ ])
+ self.assert_compile(
+ stmt,
+ "SELECT mytable.myid, percentile_cont(:percentile_cont_1) "
+ "WITHIN GROUP (ORDER BY mytable.name DESC) "
+ "AS anon_1 "
+ "FROM mytable",
+ {'percentile_cont_1': 0.5}
+ )
+
+ def test_funcfilter_within_group_w_over(self):
+ stmt = select([
+ table1.c.myid,
+ func.percentile_cont(0.5).within_group(
+ table1.c.name.desc()
+ ).over(partition_by=table1.c.description)
+ ])
+ self.assert_compile(
+ stmt,
+ "SELECT mytable.myid, percentile_cont(:percentile_cont_1) "
+ "WITHIN GROUP (ORDER BY mytable.name DESC) "
+ "OVER (PARTITION BY mytable.description) AS anon_1 "
+ "FROM mytable",
+ {'percentile_cont_1': 0.5}
+ )
+
def test_incorrect_none_type(self):
class MissingType(FunctionElement):
name = 'mt'
@@ -489,13 +553,44 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL):
MissingType().compile
)
+
+class ReturnTypeTest(fixtures.TestBase):
+
def test_array_agg(self):
- m = MetaData()
- t = Table('t', m, Column('data', Integer))
- expr = func.array_agg(t.c.data)
+ expr = func.array_agg(column('data', Integer))
is_(expr.type._type_affinity, Array)
is_(expr.type.item_type._type_affinity, Integer)
+ def test_mode(self):
+ expr = func.mode(0.5).within_group(
+ column('data', Integer).desc())
+ is_(expr.type._type_affinity, Integer)
+
+ def test_percentile_cont(self):
+ expr = func.percentile_cont(0.5).within_group(column('data', Integer))
+ is_(expr.type._type_affinity, Integer)
+
+ def test_percentile_cont_array(self):
+ expr = func.percentile_cont(0.5, 0.7).within_group(
+ column('data', Integer))
+ is_(expr.type._type_affinity, Array)
+ is_(expr.type.item_type._type_affinity, Integer)
+
+ def test_percentile_cont_array_desc(self):
+ expr = func.percentile_cont(0.5, 0.7).within_group(
+ column('data', Integer).desc())
+ is_(expr.type._type_affinity, Array)
+ is_(expr.type.item_type._type_affinity, Integer)
+
+ def test_cume_dist(self):
+ expr = func.cume_dist(0.5).within_group(
+ column('data', Integer).desc())
+ is_(expr.type._type_affinity, Numeric)
+
+ def test_percent_rank(self):
+ expr = func.percent_rank(0.5).within_group(
+ column('data', Integer))
+ is_(expr.type._type_affinity, Numeric)
class ExecuteTest(fixtures.TestBase):
diff --git a/test/sql/test_selectable.py b/test/sql/test_selectable.py
index 4a332a4d1..b9cbbf480 100644
--- a/test/sql/test_selectable.py
+++ b/test/sql/test_selectable.py
@@ -932,10 +932,10 @@ class AnonLabelTest(fixtures.TestBase):
c1 = func.count('*')
assert c1.label(None) is not c1
- eq_(str(select([c1])), "SELECT count(:param_1) AS count_1")
+ eq_(str(select([c1])), "SELECT count(:count_2) AS count_1")
c2 = select([c1]).compile()
- eq_(str(select([c1.label(None)])), "SELECT count(:param_1) AS count_1")
+ eq_(str(select([c1.label(None)])), "SELECT count(:count_2) AS count_1")
def test_named_labels_named_column(self):
c1 = column('x')
View Lorry Controller Status