#36181: Composite primary key fields cannot use __in lookup with explicit
Subquery
-------------------------------------+-------------------------------------
Reporter: Jacob Walls | Owner: (none)
Type: Bug | Status: new
Component: Database layer | Version: 5.2
(models, ORM) |
Severity: Release blocker | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Changes (by Simon Charette):
* severity: Normal => Release blocker
* stage: Unreviewed => Accepted
Comment:
Well good news, I think the work on #36149 managed to make
`SubqueryConstraint` completely irrelevant while solving this issue.
{{{#!diff
diff --git a/django/db/backends/mysql/compiler.py
b/django/db/backends/mysql/compiler.py
index 2ec6bea2f1..0291b76c70 100644
--- a/django/db/backends/mysql/compiler.py
+++ b/django/db/backends/mysql/compiler.py
@@ -1,28 +1,20 @@
from django.core.exceptions import FieldError, FullResultSet
from django.db.models.expressions import Col
-from django.db.models.sql import compiler
+from django.db.models.sql.compiler import SQLAggregateCompiler,
SQLCompiler
+from django.db.models.sql.compiler import SQLDeleteCompiler as
BaseSQLDeleteCompiler
+from django.db.models.sql.compiler import SQLInsertCompiler
+from django.db.models.sql.compiler import SQLUpdateCompiler as
BaseSQLUpdateCompiler
+__all__ = [
+ "SQLAggregateCompiler",
+ "SQLCompiler",
+ "SQLDeleteCompiler",
+ "SQLInsertCompiler",
+ "SQLUpdateCompiler",
+]
-class SQLCompiler(compiler.SQLCompiler):
- def as_subquery_condition(self, alias, columns, compiler):
- qn = compiler.quote_name_unless_alias
- qn2 = self.connection.ops.quote_name
- sql, params = self.as_sql()
- return (
- "(%s) IN (%s)"
- % (
- ", ".join("%s.%s" % (qn(alias), qn2(column)) for column
in columns),
- sql,
- ),
- params,
- )
-
-
-class SQLInsertCompiler(compiler.SQLInsertCompiler, SQLCompiler):
- pass
-
-class SQLDeleteCompiler(compiler.SQLDeleteCompiler, SQLCompiler):
+class SQLDeleteCompiler(BaseSQLDeleteCompiler):
def as_sql(self):
# Prefer the non-standard DELETE FROM syntax over the SQL
generated by
# the SQLDeleteCompiler's default implementation when multiple
tables
@@ -52,7 +44,7 @@ def as_sql(self):
return " ".join(result), tuple(params)
-class SQLUpdateCompiler(compiler.SQLUpdateCompiler, SQLCompiler):
+class SQLUpdateCompiler(BaseSQLUpdateCompiler):
def as_sql(self):
update_query, update_params = super().as_sql()
# MySQL and MariaDB support UPDATE ... ORDER BY syntax.
@@ -78,7 +70,3 @@ def as_sql(self):
# removed in .update() and cannot be resolved.
pass
return update_query, update_params
-
-
-class SQLAggregateCompiler(compiler.SQLAggregateCompiler, SQLCompiler):
- pass
diff --git a/django/db/models/fields/related_lookups.py
b/django/db/models/fields/related_lookups.py
index 38d6308f53..9fc7db7c34 100644
--- a/django/db/models/fields/related_lookups.py
+++ b/django/db/models/fields/related_lookups.py
@@ -84,21 +84,12 @@ def get_prep_lookup(self):
def as_sql(self, compiler, connection):
if isinstance(self.lhs, ColPairs):
- from django.db.models.sql.where import SubqueryConstraint
-
if self.rhs_is_direct_value():
values = [get_normalized_value(value, self.lhs) for value
in self.rhs]
lookup = TupleIn(self.lhs, values)
- return compiler.compile(lookup)
else:
- return compiler.compile(
- SubqueryConstraint(
- self.lhs.alias,
- [target.column for target in self.lhs.targets],
- [source.name for source in self.lhs.sources],
- self.rhs,
- ),
- )
+ lookup = TupleIn(self.lhs, self.rhs)
+ return compiler.compile(lookup)
return super().as_sql(compiler, connection)
diff --git a/django/db/models/fields/tuple_lookups.py
b/django/db/models/fields/tuple_lookups.py
index 98959a6161..e701c7d3de 100644
--- a/django/db/models/fields/tuple_lookups.py
+++ b/django/db/models/fields/tuple_lookups.py
@@ -2,7 +2,13 @@
from django.core.exceptions import EmptyResultSet
from django.db.models import Field
-from django.db.models.expressions import ColPairs, Func,
ResolvedOuterRef, Value
+from django.db.models.expressions import (
+ ColPairs,
+ Func,
+ ResolvedOuterRef,
+ Subquery,
+ Value,
+)
from django.db.models.lookups import (
Exact,
GreaterThan,
@@ -301,7 +307,7 @@ def check_rhs_elements_length_equals_lhs_length(self):
)
def check_rhs_is_query(self):
- if not isinstance(self.rhs, Query):
+ if not isinstance(self.rhs, (Query, Subquery)):
lhs_str = self.get_lhs_str()
rhs_cls = self.rhs.__class__.__name__
raise ValueError(
diff --git a/django/db/models/sql/compiler.py
b/django/db/models/sql/compiler.py
index 3bfb3bd631..ef10f00f20 100644
--- a/django/db/models/sql/compiler.py
+++ b/django/db/models/sql/compiler.py
@@ -1661,19 +1661,6 @@ def execute_sql(
return list(result)
return result
- def as_subquery_condition(self, alias, columns, compiler):
- qn = compiler.quote_name_unless_alias
- qn2 = self.connection.ops.quote_name
- query = self.query.clone()
-
- for index, select_col in enumerate(query.select):
- lhs_sql, lhs_params = self.compile(select_col)
- rhs = "%s.%s" % (qn(alias), qn2(columns[index]))
- query.where.add(RawSQL("%s = %s" % (lhs_sql, rhs),
lhs_params), AND)
-
- sql, params = query.as_sql(compiler, self.connection)
- return "EXISTS %s" % sql, params
-
def explain_query(self):
result = list(self.execute_sql())
# Some backends return 1 item tuples with strings, and others
return
diff --git a/django/db/models/sql/where.py b/django/db/models/sql/where.py
index 0fded5cce3..82f96aa6ec 100644
--- a/django/db/models/sql/where.py
+++ b/django/db/models/sql/where.py
@@ -343,23 +343,3 @@ def __init__(self, sqls, params):
def as_sql(self, compiler=None, connection=None):
sqls = ["(%s)" % sql for sql in self.sqls]
return " AND ".join(sqls), list(self.params or ())
-
-
-class SubqueryConstraint:
- # Even if aggregates or windows would be used in a subquery,
- # the outer query isn't interested about those.
- contains_aggregate = False
- contains_over_clause = False
-
- def __init__(self, alias, columns, targets, query_object):
- self.alias = alias
- self.columns = columns
- self.targets = targets
- query_object.clear_ordering(clear_default=True)
- self.query_object = query_object
-
- def as_sql(self, compiler, connection):
- query = self.query_object
- query.set_values(self.targets)
- query_compiler = query.get_compiler(connection=connection)
- return query_compiler.as_subquery_condition(self.alias,
self.columns, compiler)
diff --git a/tests/composite_pk/test_filter.py
b/tests/composite_pk/test_filter.py
index 937dd86652..4b753fb396 100644
--- a/tests/composite_pk/test_filter.py
+++ b/tests/composite_pk/test_filter.py
@@ -442,6 +442,11 @@ def test_cannot_cast_pk(self):
with self.assertRaisesMessage(ValueError, msg):
Comment.objects.filter(text__gt=Cast(F("pk"),
TextField())).count()
+ def test_explicit_subquery(self):
+ subquery = Subquery(User.objects.values("pk"))
+ self.assertEqual(User.objects.filter(pk__in=subquery).count(), 4)
+
self.assertEqual(Comment.objects.filter(user__in=subquery).count(), 5)
+
def test_filter_case_when(self):
msg = "When expression does not support composite primary keys."
with self.assertRaisesMessage(ValueError, msg):
}}}
Getting rid of `SuqueryConstraint` and `as_subquery_condition` was a goal
of #373 so that's a great win!
--
Ticket URL: <https://code.djangoproject.com/ticket/36181#comment:1>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
--
You received this message because you are subscribed to the Google Groups
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To view this discussion visit
https://groups.google.com/d/msgid/django-updates/01070194edbaf469-5d8f65b1-5b2c-4c9e-88f2-589b4087335b-000000%40eu-central-1.amazonses.com.
