Opened 14 years ago

Closed 13 years ago

Last modified 11 years ago

#14011 closed (fixed)

QuerySet.none().values('x').query causes "DatabaseError: subquery has too many columns" when used in filters.

Reported by: skatei <satoru.katei@…> Owned by:
Component: Database layer (models, ORM) Version: 1.2
Severity: Keywords: none query DatabaseError
Cc: Triage Stage: Accepted
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

Just came across this error.

class Test(models.Model):
  name = models.CharField(max_length=20)

test = Test(name='bob')
test.save()
pks = Test.objects.none().values('pk').query
print Test.objects.exclude(pk__in=pks)

DatabaseError: subquery has too many columns

The query:

SELECT
  "error_test"."id",
  "error_test"."name",
FROM
  "error_test"
WHERE
  NOT (
    "error_test"."id"
      IN (
        SELECT
          "error_test"."id",
          "error_test"."name",
        FROM
          "error_test"
      )
  )

Fixes?:

  • Substitute with .filter(pk__in=[]) (possibly overriding none).
  • Catch and deal with the exception.
  • Don't let this happen in the code.

This should at least raise a more meaningful exception if deemed incorrect.

Reason i'd like it to work is because I apply filters dynamically, using some complex logic that sometimes applies a none filter.

Attachments (1)

test14011.patch (586 bytes ) - added by Matthias Kestenholz 13 years ago.

Download all attachments as: .zip

Change History (6)

comment:1 by Łukasz Rekucki, 14 years ago

This particular error is caused by how EmptyQuerySet handles values().

>>> print DummyModel.objects.values('pk').none().query
SELECT "simple_dummymodel"."id" FROM "simple_dummymodel"
>>> print DummyModel.objects.none().values('pk').query
SELECT "simple_dummymodel"."id", "simple_dummymodel"."string", "simple_dummymodel"."text" FROM "simple_dummymodel"

The returned QuerySet yields wrong SQL which includes additional columns. This is not a problem, because EmptyQuerySets aren't executed, they just return empty list as their value. The following code should work properly (I'm not entirely sure why this won't execute a query, but it works):

pks = Test.objects.none().values('pk')
print Test.objects.exclude(pk__in=pks)

I guess the first issue could be fixed, but it wouldn't change the fact that .query of the EmptyQuerySet doesn't reflect it's result:

>>> print DummyModel.objects.filter(pk__in=DummyModel.objects.values('pk').none().query)
[<DummyModel: DummyModel object>] # should always be []

comment:2 by Matthias Kestenholz, 13 years ago

Triage Stage: UnreviewedAccepted

Here's a test for this issue. The failure is still present in trunk.

by Matthias Kestenholz, 13 years ago

Attachment: test14011.patch added

comment:3 by Alex Gaynor, 13 years ago

Resolution: fixed
Status: newclosed

(In [14086]) Fixed #14011 -- Doing a subquery with in and an EmptyQuerySet no longer raises an Exception. This is actually just a test for this, it was fixed by [14084]. Thanks to skatei for the report and mk for the patch.

comment:4 by Alex Gaynor, 13 years ago

(In [14087]) [1.2.X] Fixed #14011 -- Doing a subquery with in and an EmptyQuerySet no longer raises an Exception. This is actually just a test for this, it was fixed by [14085]. Thanks to skatei for the report and mk for the patch.

comment:5 by Anssi Kääriäinen, 11 years ago

Component: ORM aggregationDatabase layer (models, ORM)
Note: See TracTickets for help on using tickets.
Back to Top