1# sql/default_comparator.py
2# Copyright (C) 2005-2024 the SQLAlchemy authors and contributors
3# <see AUTHORS file>
4#
5# This module is part of SQLAlchemy and is released under
6# the MIT License: https://www.opensource.org/licenses/mit-license.php
7
8"""Default implementation of SQL comparison operations.
9"""
10
11
12from . import coercions
13from . import operators
14from . import roles
15from . import type_api
16from .elements import and_
17from .elements import BinaryExpression
18from .elements import ClauseList
19from .elements import collate
20from .elements import CollectionAggregate
21from .elements import False_
22from .elements import Null
23from .elements import or_
24from .elements import True_
25from .elements import UnaryExpression
26from .. import exc
27from .. import util
28
29
30def _boolean_compare(
31 expr,
32 op,
33 obj,
34 negate=None,
35 reverse=False,
36 _python_is_types=(util.NoneType, bool),
37 _any_all_expr=False,
38 result_type=None,
39 **kwargs
40):
41
42 if result_type is None:
43 result_type = type_api.BOOLEANTYPE
44
45 if isinstance(obj, _python_is_types + (Null, True_, False_)):
46 # allow x ==/!= True/False to be treated as a literal.
47 # this comes out to "== / != true/false" or "1/0" if those
48 # constants aren't supported and works on all platforms
49 if op in (operators.eq, operators.ne) and isinstance(
50 obj, (bool, True_, False_)
51 ):
52 return BinaryExpression(
53 expr,
54 coercions.expect(roles.ConstExprRole, obj),
55 op,
56 type_=result_type,
57 negate=negate,
58 modifiers=kwargs,
59 )
60 elif op in (
61 operators.is_distinct_from,
62 operators.is_not_distinct_from,
63 ):
64 return BinaryExpression(
65 expr,
66 coercions.expect(roles.ConstExprRole, obj),
67 op,
68 type_=result_type,
69 negate=negate,
70 modifiers=kwargs,
71 )
72 elif _any_all_expr:
73 obj = coercions.expect(
74 roles.ConstExprRole, element=obj, operator=op, expr=expr
75 )
76 else:
77 # all other None uses IS, IS NOT
78 if op in (operators.eq, operators.is_):
79 return BinaryExpression(
80 expr,
81 coercions.expect(roles.ConstExprRole, obj),
82 operators.is_,
83 negate=operators.is_not,
84 type_=result_type,
85 )
86 elif op in (operators.ne, operators.is_not):
87 return BinaryExpression(
88 expr,
89 coercions.expect(roles.ConstExprRole, obj),
90 operators.is_not,
91 negate=operators.is_,
92 type_=result_type,
93 )
94 else:
95 raise exc.ArgumentError(
96 "Only '=', '!=', 'is_()', 'is_not()', "
97 "'is_distinct_from()', 'is_not_distinct_from()' "
98 "operators can be used with None/True/False"
99 )
100 else:
101 obj = coercions.expect(
102 roles.BinaryElementRole, element=obj, operator=op, expr=expr
103 )
104
105 if reverse:
106 return BinaryExpression(
107 obj, expr, op, type_=result_type, negate=negate, modifiers=kwargs
108 )
109 else:
110 return BinaryExpression(
111 expr, obj, op, type_=result_type, negate=negate, modifiers=kwargs
112 )
113
114
115def _custom_op_operate(expr, op, obj, reverse=False, result_type=None, **kw):
116 if result_type is None:
117 if op.return_type:
118 result_type = op.return_type
119 elif op.is_comparison:
120 result_type = type_api.BOOLEANTYPE
121
122 return _binary_operate(
123 expr, op, obj, reverse=reverse, result_type=result_type, **kw
124 )
125
126
127def _binary_operate(expr, op, obj, reverse=False, result_type=None, **kw):
128 obj = coercions.expect(
129 roles.BinaryElementRole, obj, expr=expr, operator=op
130 )
131
132 if reverse:
133 left, right = obj, expr
134 else:
135 left, right = expr, obj
136
137 if result_type is None:
138 op, result_type = left.comparator._adapt_expression(
139 op, right.comparator
140 )
141
142 return BinaryExpression(left, right, op, type_=result_type, modifiers=kw)
143
144
145def _conjunction_operate(expr, op, other, **kw):
146 if op is operators.and_:
147 return and_(expr, other)
148 elif op is operators.or_:
149 return or_(expr, other)
150 else:
151 raise NotImplementedError()
152
153
154def _scalar(expr, op, fn, **kw):
155 return fn(expr)
156
157
158def _in_impl(expr, op, seq_or_selectable, negate_op, **kw):
159 seq_or_selectable = coercions.expect(
160 roles.InElementRole, seq_or_selectable, expr=expr, operator=op
161 )
162 if "in_ops" in seq_or_selectable._annotations:
163 op, negate_op = seq_or_selectable._annotations["in_ops"]
164
165 return _boolean_compare(
166 expr, op, seq_or_selectable, negate=negate_op, **kw
167 )
168
169
170def _getitem_impl(expr, op, other, **kw):
171 if (
172 isinstance(expr.type, type_api.INDEXABLE)
173 or isinstance(expr.type, type_api.TypeDecorator)
174 and isinstance(expr.type.impl, type_api.INDEXABLE)
175 ):
176 other = coercions.expect(
177 roles.BinaryElementRole, other, expr=expr, operator=op
178 )
179 return _binary_operate(expr, op, other, **kw)
180 else:
181 _unsupported_impl(expr, op, other, **kw)
182
183
184def _unsupported_impl(expr, op, *arg, **kw):
185 raise NotImplementedError(
186 "Operator '%s' is not supported on " "this expression" % op.__name__
187 )
188
189
190def _inv_impl(expr, op, **kw):
191 """See :meth:`.ColumnOperators.__inv__`."""
192
193 # undocumented element currently used by the ORM for
194 # relationship.contains()
195 if hasattr(expr, "negation_clause"):
196 return expr.negation_clause
197 else:
198 return expr._negate()
199
200
201def _neg_impl(expr, op, **kw):
202 """See :meth:`.ColumnOperators.__neg__`."""
203 return UnaryExpression(expr, operator=operators.neg, type_=expr.type)
204
205
206def _match_impl(expr, op, other, **kw):
207 """See :meth:`.ColumnOperators.match`."""
208
209 return _boolean_compare(
210 expr,
211 operators.match_op,
212 coercions.expect(
213 roles.BinaryElementRole,
214 other,
215 expr=expr,
216 operator=operators.match_op,
217 ),
218 result_type=type_api.MATCHTYPE,
219 negate=operators.not_match_op
220 if op is operators.match_op
221 else operators.match_op,
222 **kw
223 )
224
225
226def _distinct_impl(expr, op, **kw):
227 """See :meth:`.ColumnOperators.distinct`."""
228 return UnaryExpression(
229 expr, operator=operators.distinct_op, type_=expr.type
230 )
231
232
233def _between_impl(expr, op, cleft, cright, **kw):
234 """See :meth:`.ColumnOperators.between`."""
235 return BinaryExpression(
236 expr,
237 ClauseList(
238 coercions.expect(
239 roles.BinaryElementRole,
240 cleft,
241 expr=expr,
242 operator=operators.and_,
243 ),
244 coercions.expect(
245 roles.BinaryElementRole,
246 cright,
247 expr=expr,
248 operator=operators.and_,
249 ),
250 operator=operators.and_,
251 group=False,
252 group_contents=False,
253 ),
254 op,
255 negate=operators.not_between_op
256 if op is operators.between_op
257 else operators.between_op,
258 modifiers=kw,
259 )
260
261
262def _collate_impl(expr, op, other, **kw):
263 return collate(expr, other)
264
265
266def _regexp_match_impl(expr, op, pattern, flags, **kw):
267 return BinaryExpression(
268 expr,
269 coercions.expect(
270 roles.BinaryElementRole,
271 pattern,
272 expr=expr,
273 operator=operators.comma_op,
274 ),
275 op,
276 negate=operators.not_regexp_match_op,
277 modifiers={"flags": flags},
278 )
279
280
281def _regexp_replace_impl(expr, op, pattern, replacement, flags, **kw):
282 return BinaryExpression(
283 expr,
284 ClauseList(
285 coercions.expect(
286 roles.BinaryElementRole,
287 pattern,
288 expr=expr,
289 operator=operators.comma_op,
290 ),
291 coercions.expect(
292 roles.BinaryElementRole,
293 replacement,
294 expr=expr,
295 operator=operators.comma_op,
296 ),
297 operator=operators.comma_op,
298 group=False,
299 ),
300 op,
301 modifiers={"flags": flags},
302 )
303
304
305# a mapping of operators with the method they use, along with
306# their negated operator for comparison operators
307operator_lookup = {
308 "and_": (_conjunction_operate,),
309 "or_": (_conjunction_operate,),
310 "inv": (_inv_impl,),
311 "add": (_binary_operate,),
312 "mul": (_binary_operate,),
313 "sub": (_binary_operate,),
314 "div": (_binary_operate,),
315 "mod": (_binary_operate,),
316 "truediv": (_binary_operate,),
317 "custom_op": (_custom_op_operate,),
318 "json_path_getitem_op": (_binary_operate,),
319 "json_getitem_op": (_binary_operate,),
320 "concat_op": (_binary_operate,),
321 "any_op": (_scalar, CollectionAggregate._create_any),
322 "all_op": (_scalar, CollectionAggregate._create_all),
323 "lt": (_boolean_compare, operators.ge),
324 "le": (_boolean_compare, operators.gt),
325 "ne": (_boolean_compare, operators.eq),
326 "gt": (_boolean_compare, operators.le),
327 "ge": (_boolean_compare, operators.lt),
328 "eq": (_boolean_compare, operators.ne),
329 "is_distinct_from": (_boolean_compare, operators.is_not_distinct_from),
330 "is_not_distinct_from": (_boolean_compare, operators.is_distinct_from),
331 "like_op": (_boolean_compare, operators.not_like_op),
332 "ilike_op": (_boolean_compare, operators.not_ilike_op),
333 "not_like_op": (_boolean_compare, operators.like_op),
334 "not_ilike_op": (_boolean_compare, operators.ilike_op),
335 "contains_op": (_boolean_compare, operators.not_contains_op),
336 "startswith_op": (_boolean_compare, operators.not_startswith_op),
337 "endswith_op": (_boolean_compare, operators.not_endswith_op),
338 "desc_op": (_scalar, UnaryExpression._create_desc),
339 "asc_op": (_scalar, UnaryExpression._create_asc),
340 "nulls_first_op": (_scalar, UnaryExpression._create_nulls_first),
341 "nulls_last_op": (_scalar, UnaryExpression._create_nulls_last),
342 "in_op": (_in_impl, operators.not_in_op),
343 "not_in_op": (_in_impl, operators.in_op),
344 "is_": (_boolean_compare, operators.is_),
345 "is_not": (_boolean_compare, operators.is_not),
346 "collate": (_collate_impl,),
347 "match_op": (_match_impl,),
348 "not_match_op": (_match_impl,),
349 "distinct_op": (_distinct_impl,),
350 "between_op": (_between_impl,),
351 "not_between_op": (_between_impl,),
352 "neg": (_neg_impl,),
353 "getitem": (_getitem_impl,),
354 "lshift": (_unsupported_impl,),
355 "rshift": (_unsupported_impl,),
356 "contains": (_unsupported_impl,),
357 "regexp_match_op": (_regexp_match_impl,),
358 "not_regexp_match_op": (_regexp_match_impl,),
359 "regexp_replace_op": (_regexp_replace_impl,),
360}