#33992: Count subquery issue
-------------------------------------+-------------------------------------
Reporter: Fernando | Owner: nobody
Flores Villaça |
Type: Bug | Status: new
Component: Database | Version: 4.1
layer (models, ORM) | Keywords: database, orm,
Severity: Normal | aggregation
Triage Stage: | Has patch: 0
Unreviewed |
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-------------------------------------+-------------------------------------
I updated one app from 4.0 to 4.1.1 and found a issue with one annotation
using `Count`. I tested with SQLite and PostgreSQL, and both raised
exception. The same app works with 4.0.7.
Exception with SQLite:
{{{
sub-select returns 13 columns - expected 1
Traceback (most recent call last):
File ".../.venv/lib/python3.10/site-
packages/django/db/backends/utils.py", line 89, in _execute
return self.cursor.execute(sql, params)
File ".../.venv/lib/python3.10/site-
packages/django/db/backends/sqlite3/base.py", line 357, in execute
return Database.Cursor.execute(self, query, params)
sqlite3.OperationalError: sub-select returns 13 columns - expected 1
The above exception was the direct cause of the following exception:
Traceback (most recent call last):
File ".../.venv/lib/python3.10/site-packages/django/db/models/query.py",
line 1225, in exists
return self.query.has_results(using=self.db)
File ".../.venv/lib/python3.10/site-
packages/django/db/models/sql/query.py", line 592, in has_results
return compiler.has_results()
File ".../.venv/lib/python3.10/site-
packages/django/db/models/sql/compiler.py", line 1363, in has_results
return bool(self.execute_sql(SINGLE))
File ".../.venv/lib/python3.10/site-
packages/django/db/models/sql/compiler.py", line 1395, in execute_sql
cursor.execute(sql, params)
File ".../.venv/lib/python3.10/site-
packages/django/db/backends/utils.py", line 103, in execute
return super().execute(sql, params)
File ".../.venv/lib/python3.10/site-
packages/django/db/backends/utils.py", line 67, in execute
return self._execute_with_wrappers(
File ".../.venv/lib/python3.10/site-
packages/django/db/backends/utils.py", line 80, in _execute_with_wrappers
return executor(sql, params, many, context)
File ".../.venv/lib/python3.10/site-
packages/django/db/backends/utils.py", line 84, in _execute
with self.db.wrap_database_errors:
File ".../.venv/lib/python3.10/site-packages/django/db/utils.py", line
91, in __exit__
raise dj_exc_value.with_traceback(traceback) from exc_value
File ".../.venv/lib/python3.10/site-
packages/django/db/backends/utils.py", line 89, in _execute
return self.cursor.execute(sql, params)
File ".../.venv/lib/python3.10/site-
packages/django/db/backends/sqlite3/base.py", line 357, in execute
return Database.Cursor.execute(self, query, params)
django.db.utils.OperationalError: sub-select returns 13 columns - expected
1
}}}
Exception with Postgres:
{{{
subquery must return only one column
LINE 1: ...iked_by"."post_id") GROUP BY "network_post"."id", (SELECT U0...
^
Traceback (most recent call last):
File ".../.venv/lib/python3.10/site-
packages/django/db/backends/utils.py", line 89, in _execute
return self.cursor.execute(sql, params)
psycopg2.errors.SyntaxError: subquery must return only one column
LINE 1: ...iked_by"."post_id") GROUP BY "network_post"."id", (SELECT U0...
^
The above exception was the direct cause of the following exception:
Traceback (most recent call last):
File ".../.venv/lib/python3.10/site-packages/django/db/models/query.py",
line 1225, in exists
return self.query.has_results(using=self.db)
File ".../.venv/lib/python3.10/site-
packages/django/db/models/sql/query.py", line 592, in has_results
return compiler.has_results()
File ".../.venv/lib/python3.10/site-
packages/django/db/models/sql/compiler.py", line 1363, in has_results
return bool(self.execute_sql(SINGLE))
File ".../.venv/lib/python3.10/site-
packages/django/db/models/sql/compiler.py", line 1395, in execute_sql
cursor.execute(sql, params)
File ".../.venv/lib/python3.10/site-
packages/django/db/backends/utils.py", line 103, in execute
return super().execute(sql, params)
File ".../.venv/lib/python3.10/site-
packages/django/db/backends/utils.py", line 67, in execute
return self._execute_with_wrappers(
File ".../.venv/lib/python3.10/site-
packages/django/db/backends/utils.py", line 80, in _execute_with_wrappers
return executor(sql, params, many, context)
File ".../.venv/lib/python3.10/site-
packages/django/db/backends/utils.py", line 84, in _execute
with self.db.wrap_database_errors:
File ".../.venv/lib/python3.10/site-packages/django/db/utils.py", line
91, in __exit__
raise dj_exc_value.with_traceback(traceback) from exc_value
File ".../.venv/lib/python3.10/site-
packages/django/db/backends/utils.py", line 89, in _execute
return self.cursor.execute(sql, params)
django.db.utils.ProgrammingError: subquery must return only one column
LINE 1: ...iked_by"."post_id") GROUP BY "network_post"."id", (SELECT U0...
^
}}}
The exception is raised by `annotate(likes=Count("liked_by"))` in method
`fetch_all_posts`.
{{{
class PostManager(models.Manager):
def request_data(self, request_user):
liked_by_user = Value(False)
is_following = Value(False)
is_owner = Case(When(user__id=request_user.id, then=True),
default=False)
if request_user.is_authenticated:
# Check if the user has liked the post in each row of the
query
liked_by_user =
Exists(request_user.liked_posts.filter(id=OuterRef("id")))
is_following = Exists(
request_user.following.filter(id=OuterRef("user__id"))
)
return is_owner, liked_by_user, is_following
def fetch_all_posts(self, request_user) -> QuerySet[Post]:
is_owner, liked_by_user, is_following =
self.request_data(request_user)
return (
self.select_related()
.prefetch_related(
Prefetch(
"comments",
queryset=Comment.objects.select_related().filter(reply=False),
), # filter related "comments" inside the post QuerySet
)
.order_by("-publication_date")
.annotate(is_following=is_following)
.annotate(is_owner=is_owner)
.annotate(likes=Count("liked_by")) # Doesn't work on 4.1
.annotate(liked_by_user=liked_by_user)
)
def fetch_following_posts(self, request_user: User) -> QuerySet[Post]:
return self.fetch_all_posts(request_user).filter(
user__in=request_user.following.all()
)
}}}
Models
{{{
class User(AbstractUser):
id: int
posts: RelatedManager[Post]
liked_posts: RelatedManager[Post]
comments: RelatedManager[Comment]
about = models.CharField(blank=True, max_length=255)
photo = models.ImageField(
blank=True,
null=True,
upload_to=upload_path,
validators=[file_validator],
)
following = models.ManyToManyField(
"self", related_name="followers", symmetrical=False
)
objects: CustomUserManager = CustomUserManager()
# Related fields
# posts = ManyToOne("Post", related_name="user")
# liked_posts = ManyToMany("Post", related_name="liked_by")
# comments = ManyToOne("Comment", related_name="user")
def save(self, *args, **kwargs):
"""
full_clean is not called automatically on save by Django
"""
self.full_clean()
super().save(*args, **kwargs)
def __str__(self):
return f"{self.username}" # type: ignore
class Post(models.Model):
id: int
comments: RelatedManager[Comment]
user_id: int
user = models.ForeignKey(
settings.AUTH_USER_MODEL, on_delete=models.CASCADE,
related_name="posts"
)
text = models.CharField(max_length=200)
publication_date = models.DateTimeField(auto_now_add=True)
edited = models.BooleanField(default=False)
last_modified = models.DateTimeField(auto_now_add=True)
liked_by = models.ManyToManyField(
settings.AUTH_USER_MODEL, related_name="liked_posts", blank=True
)
# Related Fields
# comments = ManyToOne("Comment", related_name="post")
objects: PostManager = PostManager()
class Meta:
ordering = ["-publication_date"]
def __str__(self):
return f"{self.text}"
class Comment(models.Model):
id: int
replies: RelatedManager[Comment]
post_id: int
post = models.ForeignKey(Post, on_delete=models.CASCADE,
related_name="comments")
user_id: int
user = models.ForeignKey(
settings.AUTH_USER_MODEL, on_delete=models.CASCADE,
related_name="comments"
)
text = models.CharField(max_length=200)
publication_date = models.DateTimeField(auto_now_add=True)
reply = models.BooleanField(default=False)
parent_comment_id: int
parent_comment = models.ForeignKey(
"self", on_delete=models.CASCADE, null=True, blank=True,
related_name="replies"
)
class Meta:
ordering = ["-publication_date"]
def save(self, *args, **kwargs):
self.full_clean()
if self.parent_comment is not None:
if self.parent_comment.post.id != self.post.id:
raise ValidationError("Parent comment must be from the
same post.")
self.reply = True
super().save(*args, **kwargs)
def __str__(self):
return f"{self.text} - reply: {self.reply}" # type: ignore
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/33992>
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 on the web visit
https://groups.google.com/d/msgid/django-updates/0107018318d659a2-f39167f4-1c1a-4099-8993-e37a4d581b59-000000%40eu-central-1.amazonses.com.
