[Django] #33992: Count subquery issue

53 views

Skip to first unread message

Django

unread,

Sep 7, 2022, 12:44:09 PM9/7/22

to django-...@googlegroups.com

#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.

Django

unread,

Sep 7, 2022, 1:50:17 PM9/7/22

to django-...@googlegroups.com

#33992: Count subquery issue
-------------------------------------+-------------------------------------

Reporter: Fernando Flores | Owner: nobody

Villaça |
Type: Bug | Status: new

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Mariusz Felisiak):

* cc: Simon Charette (added)
* severity: Normal => Release blocker
* stage: Unreviewed => Accepted