Skip to content

Commit 428262a

Browse files
zzzeekzzzeek
committed
implement multi-element expression constructs
Improved the construction of SQL binary expressions to allow for very long expressions against the same associative operator without special steps needed in order to avoid high memory use and excess recursion depth. A particular binary operation ``A op B`` can now be joined against another element ``op C`` and the resulting structure will be "flattened" so that the representation as well as SQL compilation does not require recursion. To implement this more cleanly, the biggest change here is that column-oriented lists of things are broken away from ClauseList in a new class ExpressionClauseList, that also forms the basis of BooleanClauseList. ClauseList is still used for the generic "comma-separated list" of things such as Tuple and things like ORDER BY, as well as in some API endpoints. Also adds __slots__ to the TypeEngine-bound Comparator classes. Still can't really do __slots__ on ClauseElement. Fixes: #7744 Change-Id: I81a8ceb6f8f3bb0fe52d58f3cb42e4b6c2bc9018
1 parent a45e228 commit 428262a

15 files changed

Lines changed: 528 additions & 79 deletions

File tree

‎doc/build/changelog/unreleased_20/7744.rst‎

Lines changed: 18 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,18 @@
1+
.. change::
2+
:tags: bug, sql
3+
:tickets: 7744
4+
5+
Improved the construction of SQL binary expressions to allow for very long
6+
expressions against the same associative operator without special steps
7+
needed in order to avoid high memory use and excess recursion depth. A
8+
particular binary operation ``A op B`` can now be joined against another
9+
element ``op C`` and the resulting structure will be "flattened" so that
10+
the representation as well as SQL compilation does not require recursion.
11+
12+
One effect of this change is that string concatenation expressions which
13+
use SQL functions come out as "flat", e.g. MySQL will now render
14+
``concat('x', 'y', 'z', ...)``` rather than nesting together two-element
15+
functions like ``concat(concat('x', 'y'), 'z')``. Third-party dialects
16+
which override the string concatenation operator will need to implement
17+
a new method ``def visit_concat_op_expression_clauselist()`` to
18+
accompany the existing ``def visit_concat_op_binary()`` method.

‎lib/sqlalchemy/dialects/mssql/base.py‎

Lines changed: 5 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1833,6 +1833,11 @@ def visit_length_func(self, fn, **kw):
18331833
def visit_char_length_func(self, fn, **kw):
18341834
return "LEN%s" % self.function_argspec(fn, **kw)
18351835

1836+
def visit_concat_op_expression_clauselist(
1837+
self, clauselist, operator, **kw
1838+
):
1839+
return " + ".join(self.process(elem, **kw) for elem in clauselist)
1840+
18361841
def visit_concat_op_binary(self, binary, operator, **kw):
18371842
return "%s + %s" % (
18381843
self.process(binary.left, **kw),

‎lib/sqlalchemy/dialects/mysql/base.py‎

Lines changed: 7 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1322,6 +1322,13 @@ def replace(obj):
13221322

13231323
return "ON DUPLICATE KEY UPDATE " + ", ".join(clauses)
13241324

1325+
def visit_concat_op_expression_clauselist(
1326+
self, clauselist, operator, **kw
1327+
):
1328+
return "concat(%s)" % (
1329+
", ".join(self.process(elem, **kw) for elem in clauselist.clauses)
1330+
)
1331+
13251332
def visit_concat_op_binary(self, binary, operator, **kw):
13261333
return "concat(%s, %s)" % (
13271334
self.process(binary.left, **kw),

‎lib/sqlalchemy/dialects/postgresql/array.py‎

Lines changed: 20 additions & 12 deletions
Original file line numberDiff line numberDiff line change
@@ -5,14 +5,19 @@
55
# This module is part of SQLAlchemy and is released under
66
# the MIT License: https://www.opensource.org/licenses/mit-license.php
77

8+
from __future__ import annotations
9+
810
import re
11+
from typing import Any
12+
from typing import TypeVar
913

1014
from ... import types as sqltypes
1115
from ... import util
12-
from ...sql import coercions
1316
from ...sql import expression
1417
from ...sql import operators
15-
from ...sql import roles
18+
19+
20+
_T = TypeVar("_T", bound=Any)
1621

1722

1823
def Any(other, arrexpr, operator=operators.eq):
@@ -33,7 +38,7 @@ def All(other, arrexpr, operator=operators.eq):
3338
return arrexpr.all(other, operator)
3439

3540

36-
class array(expression.ClauseList, expression.ColumnElement):
41+
class array(expression.ExpressionClauseList[_T]):
3742

3843
"""A PostgreSQL ARRAY literal.
3944
@@ -90,16 +95,19 @@ class array(expression.ClauseList, expression.ColumnElement):
9095
inherit_cache = True
9196

9297
def __init__(self, clauses, **kw):
93-
clauses = [
94-
coercions.expect(roles.ExpressionElementRole, c) for c in clauses
95-
]
96-
97-
self._type_tuple = [arg.type for arg in clauses]
98-
main_type = kw.pop(
99-
"type_",
100-
self._type_tuple[0] if self._type_tuple else sqltypes.NULLTYPE,
98+
99+
type_arg = kw.pop("type_", None)
100+
super(array, self).__init__(operators.comma_op, *clauses, **kw)
101+
102+
self._type_tuple = [arg.type for arg in self.clauses]
103+
104+
main_type = (
105+
type_arg
106+
if type_arg is not None
107+
else self._type_tuple[0]
108+
if self._type_tuple
109+
else sqltypes.NULLTYPE
101110
)
102-
super(array, self).__init__(*clauses, **kw)
103111

104112
if isinstance(main_type, ARRAY):
105113
self.type = ARRAY(

‎lib/sqlalchemy/orm/evaluator.py‎

Lines changed: 3 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -94,6 +94,9 @@ def visit_column(self, clause):
9494
def visit_tuple(self, clause):
9595
return self.visit_clauselist(clause)
9696

97+
def visit_expression_clauselist(self, clause):
98+
return self.visit_clauselist(clause)
99+
97100
def visit_clauselist(self, clause):
98101
evaluators = [self.process(clause) for clause in clause.clauses]
99102

‎lib/sqlalchemy/orm/persistence.py‎

Lines changed: 6 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -884,12 +884,12 @@ def update_stmt():
884884
clauses = BooleanClauseList._construct_raw(operators.and_)
885885

886886
for col in mapper._pks_by_table[table]:
887-
clauses.clauses.append(
887+
clauses._append_inplace(
888888
col == sql.bindparam(col._label, type_=col.type)
889889
)
890890

891891
if needs_version_id:
892-
clauses.clauses.append(
892+
clauses._append_inplace(
893893
mapper.version_id_col
894894
== sql.bindparam(
895895
mapper.version_id_col._label,
@@ -1316,12 +1316,12 @@ def update_stmt():
13161316
clauses = BooleanClauseList._construct_raw(operators.and_)
13171317

13181318
for col in mapper._pks_by_table[table]:
1319-
clauses.clauses.append(
1319+
clauses._append_inplace(
13201320
col == sql.bindparam(col._label, type_=col.type)
13211321
)
13221322

13231323
if needs_version_id:
1324-
clauses.clauses.append(
1324+
clauses._append_inplace(
13251325
mapper.version_id_col
13261326
== sql.bindparam(
13271327
mapper.version_id_col._label,
@@ -1437,12 +1437,12 @@ def delete_stmt():
14371437
clauses = BooleanClauseList._construct_raw(operators.and_)
14381438

14391439
for col in mapper._pks_by_table[table]:
1440-
clauses.clauses.append(
1440+
clauses._append_inplace(
14411441
col == sql.bindparam(col.key, type_=col.type)
14421442
)
14431443

14441444
if need_version_id:
1445-
clauses.clauses.append(
1445+
clauses._append_inplace(
14461446
mapper.version_id_col
14471447
== sql.bindparam(
14481448
mapper.version_id_col.key, type_=mapper.version_id_col.type

‎lib/sqlalchemy/sql/compiler.py‎

Lines changed: 18 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2013,6 +2013,24 @@ def visit_clauselist(self, clauselist, **kw):
20132013

20142014
return self._generate_delimited_list(clauselist.clauses, sep, **kw)
20152015

2016+
def visit_expression_clauselist(self, clauselist, **kw):
2017+
operator_ = clauselist.operator
2018+
2019+
disp = self._get_operator_dispatch(
2020+
operator_, "expression_clauselist", None
2021+
)
2022+
if disp:
2023+
return disp(clauselist, operator_, **kw)
2024+
2025+
try:
2026+
opstring = OPERATORS[operator_]
2027+
except KeyError as err:
2028+
raise exc.UnsupportedCompilationError(self, operator_) from err
2029+
else:
2030+
return self._generate_delimited_list(
2031+
clauselist.clauses, opstring, **kw
2032+
)
2033+
20162034
def visit_case(self, clause, **kwargs):
20172035
x = "CASE "
20182036
if clause.value is not None:

‎lib/sqlalchemy/sql/default_comparator.py‎

Lines changed: 18 additions & 19 deletions
Original file line numberDiff line numberDiff line change
@@ -27,11 +27,12 @@
2727
from .elements import and_
2828
from .elements import BinaryExpression
2929
from .elements import ClauseElement
30-
from .elements import ClauseList
3130
from .elements import CollationClause
3231
from .elements import CollectionAggregate
32+
from .elements import ExpressionClauseList
3333
from .elements import False_
3434
from .elements import Null
35+
from .elements import OperatorExpression
3536
from .elements import or_
3637
from .elements import True_
3738
from .elements import UnaryExpression
@@ -56,11 +57,9 @@ def _boolean_compare(
5657
reverse: bool = False,
5758
_python_is_types: Tuple[Type[Any], ...] = (type(None), bool),
5859
_any_all_expr: bool = False,
59-
result_type: Optional[
60-
Union[Type[TypeEngine[bool]], TypeEngine[bool]]
61-
] = None,
60+
result_type: Optional[TypeEngine[bool]] = None,
6261
**kwargs: Any,
63-
) -> BinaryExpression[bool]:
62+
) -> OperatorExpression[bool]:
6463
if result_type is None:
6564
result_type = type_api.BOOLEANTYPE
6665

@@ -71,7 +70,7 @@ def _boolean_compare(
7170
if op in (operators.eq, operators.ne) and isinstance(
7271
obj, (bool, True_, False_)
7372
):
74-
return BinaryExpression(
73+
return OperatorExpression._construct_for_op(
7574
expr,
7675
coercions.expect(roles.ConstExprRole, obj),
7776
op,
@@ -83,7 +82,7 @@ def _boolean_compare(
8382
operators.is_distinct_from,
8483
operators.is_not_distinct_from,
8584
):
86-
return BinaryExpression(
85+
return OperatorExpression._construct_for_op(
8786
expr,
8887
coercions.expect(roles.ConstExprRole, obj),
8988
op,
@@ -98,15 +97,15 @@ def _boolean_compare(
9897
else:
9998
# all other None uses IS, IS NOT
10099
if op in (operators.eq, operators.is_):
101-
return BinaryExpression(
100+
return OperatorExpression._construct_for_op(
102101
expr,
103102
coercions.expect(roles.ConstExprRole, obj),
104103
operators.is_,
105104
negate=operators.is_not,
106105
type_=result_type,
107106
)
108107
elif op in (operators.ne, operators.is_not):
109-
return BinaryExpression(
108+
return OperatorExpression._construct_for_op(
110109
expr,
111110
coercions.expect(roles.ConstExprRole, obj),
112111
operators.is_not,
@@ -125,7 +124,7 @@ def _boolean_compare(
125124
)
126125

127126
if reverse:
128-
return BinaryExpression(
127+
return OperatorExpression._construct_for_op(
129128
obj,
130129
expr,
131130
op,
@@ -134,7 +133,7 @@ def _boolean_compare(
134133
modifiers=kwargs,
135134
)
136135
else:
137-
return BinaryExpression(
136+
return OperatorExpression._construct_for_op(
138137
expr,
139138
obj,
140139
op,
@@ -169,11 +168,9 @@ def _binary_operate(
169168
obj: roles.BinaryElementRole[Any],
170169
*,
171170
reverse: bool = False,
172-
result_type: Optional[
173-
Union[Type["TypeEngine[_T]"], "TypeEngine[_T]"]
174-
] = None,
171+
result_type: Optional[TypeEngine[_T]] = None,
175172
**kw: Any,
176-
) -> BinaryExpression[_T]:
173+
) -> OperatorExpression[_T]:
177174

178175
coerced_obj = coercions.expect(
179176
roles.BinaryElementRole, obj, expr=expr, operator=op
@@ -189,7 +186,9 @@ def _binary_operate(
189186
op, right.comparator
190187
)
191188

192-
return BinaryExpression(left, right, op, type_=result_type, modifiers=kw)
189+
return OperatorExpression._construct_for_op(
190+
left, right, op, type_=result_type, modifiers=kw
191+
)
193192

194193

195194
def _conjunction_operate(
@@ -311,7 +310,9 @@ def _between_impl(
311310
"""See :meth:`.ColumnOperators.between`."""
312311
return BinaryExpression(
313312
expr,
314-
ClauseList(
313+
ExpressionClauseList._construct_for_list(
314+
operators.and_,
315+
type_api.NULLTYPE,
315316
coercions.expect(
316317
roles.BinaryElementRole,
317318
cleft,
@@ -324,9 +325,7 @@ def _between_impl(
324325
expr=expr,
325326
operator=operators.and_,
326327
),
327-
operator=operators.and_,
328328
group=False,
329-
group_contents=False,
330329
),
331330
op,
332331
negate=operators.not_between_op

0 commit comments

Comments
 (0)