where.py 13 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365
  1. """
  2. Code to manage the creation and SQL rendering of 'where' constraints.
  3. """
  4. import operator
  5. from functools import reduce
  6. from django.core.exceptions import EmptyResultSet, FullResultSet
  7. from django.db.models.expressions import Case, When
  8. from django.db.models.functions import Mod
  9. from django.db.models.lookups import Exact
  10. from django.utils import tree
  11. from django.utils.functional import cached_property
  12. # Connection types
  13. AND = "AND"
  14. OR = "OR"
  15. XOR = "XOR"
  16. class WhereNode(tree.Node):
  17. """
  18. An SQL WHERE clause.
  19. The class is tied to the Query class that created it (in order to create
  20. the correct SQL).
  21. A child is usually an expression producing boolean values. Most likely the
  22. expression is a Lookup instance.
  23. However, a child could also be any class with as_sql() and either
  24. relabeled_clone() method or relabel_aliases() and clone() methods and
  25. contains_aggregate attribute.
  26. """
  27. default = AND
  28. resolved = False
  29. conditional = True
  30. def split_having_qualify(self, negated=False, must_group_by=False):
  31. """
  32. Return three possibly None nodes: one for those parts of self that
  33. should be included in the WHERE clause, one for those parts of self
  34. that must be included in the HAVING clause, and one for those parts
  35. that refer to window functions.
  36. """
  37. if not self.contains_aggregate and not self.contains_over_clause:
  38. return self, None, None
  39. in_negated = negated ^ self.negated
  40. # Whether or not children must be connected in the same filtering
  41. # clause (WHERE > HAVING > QUALIFY) to maintain logical semantic.
  42. must_remain_connected = (
  43. (in_negated and self.connector == AND)
  44. or (not in_negated and self.connector == OR)
  45. or self.connector == XOR
  46. )
  47. if (
  48. must_remain_connected
  49. and self.contains_aggregate
  50. and not self.contains_over_clause
  51. ):
  52. # It's must cheaper to short-circuit and stash everything in the
  53. # HAVING clause than split children if possible.
  54. return None, self, None
  55. where_parts = []
  56. having_parts = []
  57. qualify_parts = []
  58. for c in self.children:
  59. if hasattr(c, "split_having_qualify"):
  60. where_part, having_part, qualify_part = c.split_having_qualify(
  61. in_negated, must_group_by
  62. )
  63. if where_part is not None:
  64. where_parts.append(where_part)
  65. if having_part is not None:
  66. having_parts.append(having_part)
  67. if qualify_part is not None:
  68. qualify_parts.append(qualify_part)
  69. elif c.contains_over_clause:
  70. qualify_parts.append(c)
  71. elif c.contains_aggregate:
  72. having_parts.append(c)
  73. else:
  74. where_parts.append(c)
  75. if must_remain_connected and qualify_parts:
  76. # Disjunctive heterogeneous predicates can be pushed down to
  77. # qualify as long as no conditional aggregation is involved.
  78. if not where_parts or (where_parts and not must_group_by):
  79. return None, None, self
  80. elif where_parts:
  81. # In theory this should only be enforced when dealing with
  82. # where_parts containing predicates against multi-valued
  83. # relationships that could affect aggregation results but this
  84. # is complex to infer properly.
  85. raise NotImplementedError(
  86. "Heterogeneous disjunctive predicates against window functions are "
  87. "not implemented when performing conditional aggregation."
  88. )
  89. where_node = (
  90. self.create(where_parts, self.connector, self.negated)
  91. if where_parts
  92. else None
  93. )
  94. having_node = (
  95. self.create(having_parts, self.connector, self.negated)
  96. if having_parts
  97. else None
  98. )
  99. qualify_node = (
  100. self.create(qualify_parts, self.connector, self.negated)
  101. if qualify_parts
  102. else None
  103. )
  104. return where_node, having_node, qualify_node
  105. def as_sql(self, compiler, connection):
  106. """
  107. Return the SQL version of the where clause and the value to be
  108. substituted in. Return '', [] if this node matches everything,
  109. None, [] if this node is empty, and raise EmptyResultSet if this
  110. node can't match anything.
  111. """
  112. result = []
  113. result_params = []
  114. if self.connector == AND:
  115. full_needed, empty_needed = len(self.children), 1
  116. else:
  117. full_needed, empty_needed = 1, len(self.children)
  118. if self.connector == XOR and not connection.features.supports_logical_xor:
  119. # Convert if the database doesn't support XOR:
  120. # a XOR b XOR c XOR ...
  121. # to:
  122. # (a OR b OR c OR ...) AND MOD(a + b + c + ..., 2) == 1
  123. # The result of an n-ary XOR is true when an odd number of operands
  124. # are true.
  125. lhs = self.__class__(self.children, OR)
  126. rhs_sum = reduce(
  127. operator.add,
  128. (Case(When(c, then=1), default=0) for c in self.children),
  129. )
  130. if len(self.children) > 2:
  131. rhs_sum = Mod(rhs_sum, 2)
  132. rhs = Exact(1, rhs_sum)
  133. return self.__class__([lhs, rhs], AND, self.negated).as_sql(
  134. compiler, connection
  135. )
  136. for child in self.children:
  137. try:
  138. sql, params = compiler.compile(child)
  139. except EmptyResultSet:
  140. empty_needed -= 1
  141. except FullResultSet:
  142. full_needed -= 1
  143. else:
  144. if sql:
  145. result.append(sql)
  146. result_params.extend(params)
  147. else:
  148. full_needed -= 1
  149. # Check if this node matches nothing or everything.
  150. # First check the amount of full nodes and empty nodes
  151. # to make this node empty/full.
  152. # Now, check if this node is full/empty using the
  153. # counts.
  154. if empty_needed == 0:
  155. if self.negated:
  156. raise FullResultSet
  157. else:
  158. raise EmptyResultSet
  159. if full_needed == 0:
  160. if self.negated:
  161. raise EmptyResultSet
  162. else:
  163. raise FullResultSet
  164. conn = " %s " % self.connector
  165. sql_string = conn.join(result)
  166. if not sql_string:
  167. raise FullResultSet
  168. if self.negated:
  169. # Some backends (Oracle at least) need parentheses around the inner
  170. # SQL in the negated case, even if the inner SQL contains just a
  171. # single expression.
  172. sql_string = "NOT (%s)" % sql_string
  173. elif len(result) > 1 or self.resolved:
  174. sql_string = "(%s)" % sql_string
  175. return sql_string, result_params
  176. def get_group_by_cols(self):
  177. cols = []
  178. for child in self.children:
  179. cols.extend(child.get_group_by_cols())
  180. return cols
  181. def get_source_expressions(self):
  182. return self.children[:]
  183. def set_source_expressions(self, children):
  184. assert len(children) == len(self.children)
  185. self.children = children
  186. def relabel_aliases(self, change_map):
  187. """
  188. Relabel the alias values of any children. 'change_map' is a dictionary
  189. mapping old (current) alias values to the new values.
  190. """
  191. if not change_map:
  192. return self
  193. for pos, child in enumerate(self.children):
  194. if hasattr(child, "relabel_aliases"):
  195. # For example another WhereNode
  196. child.relabel_aliases(change_map)
  197. elif hasattr(child, "relabeled_clone"):
  198. self.children[pos] = child.relabeled_clone(change_map)
  199. def clone(self):
  200. clone = self.create(connector=self.connector, negated=self.negated)
  201. for child in self.children:
  202. if hasattr(child, "clone"):
  203. child = child.clone()
  204. clone.children.append(child)
  205. return clone
  206. def relabeled_clone(self, change_map):
  207. clone = self.clone()
  208. clone.relabel_aliases(change_map)
  209. return clone
  210. def replace_expressions(self, replacements):
  211. if not replacements:
  212. return self
  213. if replacement := replacements.get(self):
  214. return replacement
  215. clone = self.create(connector=self.connector, negated=self.negated)
  216. for child in self.children:
  217. clone.children.append(child.replace_expressions(replacements))
  218. return clone
  219. def get_refs(self):
  220. refs = set()
  221. for child in self.children:
  222. refs |= child.get_refs()
  223. return refs
  224. @classmethod
  225. def _contains_aggregate(cls, obj):
  226. if isinstance(obj, tree.Node):
  227. return any(cls._contains_aggregate(c) for c in obj.children)
  228. return obj.contains_aggregate
  229. @cached_property
  230. def contains_aggregate(self):
  231. return self._contains_aggregate(self)
  232. @classmethod
  233. def _contains_over_clause(cls, obj):
  234. if isinstance(obj, tree.Node):
  235. return any(cls._contains_over_clause(c) for c in obj.children)
  236. return obj.contains_over_clause
  237. @cached_property
  238. def contains_over_clause(self):
  239. return self._contains_over_clause(self)
  240. @property
  241. def is_summary(self):
  242. return any(child.is_summary for child in self.children)
  243. @staticmethod
  244. def _resolve_leaf(expr, query, *args, **kwargs):
  245. if hasattr(expr, "resolve_expression"):
  246. expr = expr.resolve_expression(query, *args, **kwargs)
  247. return expr
  248. @classmethod
  249. def _resolve_node(cls, node, query, *args, **kwargs):
  250. if hasattr(node, "children"):
  251. for child in node.children:
  252. cls._resolve_node(child, query, *args, **kwargs)
  253. if hasattr(node, "lhs"):
  254. node.lhs = cls._resolve_leaf(node.lhs, query, *args, **kwargs)
  255. if hasattr(node, "rhs"):
  256. node.rhs = cls._resolve_leaf(node.rhs, query, *args, **kwargs)
  257. def resolve_expression(self, *args, **kwargs):
  258. clone = self.clone()
  259. clone._resolve_node(clone, *args, **kwargs)
  260. clone.resolved = True
  261. return clone
  262. @cached_property
  263. def output_field(self):
  264. from django.db.models import BooleanField
  265. return BooleanField()
  266. @property
  267. def _output_field_or_none(self):
  268. return self.output_field
  269. def select_format(self, compiler, sql, params):
  270. # Wrap filters with a CASE WHEN expression if a database backend
  271. # (e.g. Oracle) doesn't support boolean expression in SELECT or GROUP
  272. # BY list.
  273. if not compiler.connection.features.supports_boolean_expr_in_select_clause:
  274. sql = f"CASE WHEN {sql} THEN 1 ELSE 0 END"
  275. return sql, params
  276. def get_db_converters(self, connection):
  277. return self.output_field.get_db_converters(connection)
  278. def get_lookup(self, lookup):
  279. return self.output_field.get_lookup(lookup)
  280. def leaves(self):
  281. for child in self.children:
  282. if isinstance(child, WhereNode):
  283. yield from child.leaves()
  284. else:
  285. yield child
  286. class NothingNode:
  287. """A node that matches nothing."""
  288. contains_aggregate = False
  289. contains_over_clause = False
  290. def as_sql(self, compiler=None, connection=None):
  291. raise EmptyResultSet
  292. class ExtraWhere:
  293. # The contents are a black box - assume no aggregates or windows are used.
  294. contains_aggregate = False
  295. contains_over_clause = False
  296. def __init__(self, sqls, params):
  297. self.sqls = sqls
  298. self.params = params
  299. def as_sql(self, compiler=None, connection=None):
  300. sqls = ["(%s)" % sql for sql in self.sqls]
  301. return " AND ".join(sqls), list(self.params or ())
  302. class SubqueryConstraint:
  303. # Even if aggregates or windows would be used in a subquery,
  304. # the outer query isn't interested about those.
  305. contains_aggregate = False
  306. contains_over_clause = False
  307. def __init__(self, alias, columns, targets, query_object):
  308. self.alias = alias
  309. self.columns = columns
  310. self.targets = targets
  311. query_object.clear_ordering(clear_default=True)
  312. self.query_object = query_object
  313. def as_sql(self, compiler, connection):
  314. query = self.query_object
  315. query.set_values(self.targets)
  316. query_compiler = query.get_compiler(connection=connection)
  317. return query_compiler.as_subquery_condition(self.alias, self.columns, compiler)