Code

Opened 4 years ago

Closed 4 years ago

Last modified 14 months 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: UI/UX:

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 mk 4 years ago.

Download all attachments as: .zip

Change History (6)

comment:1 Changed 4 years ago by lrekucki

  • Needs documentation unset
  • Needs tests unset
  • Patch needs improvement unset

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 Changed 4 years ago by mk

  • Triage Stage changed from Unreviewed to Accepted

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

Changed 4 years ago by mk

comment:3 Changed 4 years ago by Alex

  • Resolution set to fixed
  • Status changed from new to closed

(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 Changed 4 years ago by Alex

(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 Changed 14 months ago by akaariai

  • Component changed from ORM aggregation to Database layer (models, ORM)

Add Comment

Modify Ticket

Change Properties
<Author field>
Action
as closed
as The resolution will be set. Next status will be 'closed'
The resolution will be deleted. Next status will be 'new'
Author


E-mail address and user name can be saved in the Preferences.

 
Note: See TracTickets for help on using tickets.