Opened 16 months ago

Closed 16 months ago

Last modified 16 months ago

#28664 closed Uncategorized (duplicate)

basic subquery generates invalid sql

Reported by: David Szotten Owned by: nobody
Component: Database layer (models, ORM) Version: master
Severity: Normal Keywords:
Cc: Triage Stage: Unreviewed
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no


from django.db import models

class Foo(models.Model):
    a = models.IntegerField()
    b = models.IntegerField()

class Bar(models.Model):
    foo = models.ForeignKey(Foo, on_delete=models.CASCADE)
>>> print(Bar.objects.filter(foo=Foo.objects.filter(a=1)).query)

SELECT "app_bar"."id", "app_bar"."foo_id" FROM "app_bar" WHERE "app_bar"."foo_id" = (SELECT U0."id", U0."a", U0."b" FROM "app_foo" U0 WHERE U0."a" = 1)

The subquery is selecting all columns, causing django.db.utils.ProgrammingError: subquery must return only one column

Change History (4)

comment:2 Changed 16 months ago by Simon Charette

Resolution: duplicate
Status: newclosed

Duplicate of #28497. Using __eq=queryset was never officially supported. You can use __in=queryset or __eq=queryset.values('field') to work around the issue.

comment:3 Changed 16 months ago by David Szotten

ah, my mistake, apologies.

though in that case, i wonder if it would be possible to provide more helpful error message instead of generating bad sql and sending to the db

comment:4 Changed 16 months ago by Simon Charette

The current consensus in #28497 would be to officialize the support of __eq=queryset.

Note that there won't be any way from protecting you from passing a queryset returning more than a single result and ending up with a more than one row returned by a subquery used as an expression or similar message from the database. You'll have to make sure the provided filters don't match more than a single row or that you LIMIT your query.

Last edited 16 months ago by Simon Charette (previous) (diff)
Note: See TracTickets for help on using tickets.
Back to Top