Code

Opened 5 years ago

Closed 5 years ago

Last modified 5 years ago

#9985 closed (fixed)

r9701 breaks for combinations of values_list and unique - it inserts duplicate references in SQL

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

Description

r9701 introduces a change that leads to malformed SQL in queries that include values_list and distinct queries, at least in SQLite. A simple example:

class Database(models.Model):
    name = models.TextField()

class Process(models.Model):
    database = models.ForeignKey(Database)
    name = models.TextField()

The query

Database.objects.filter(id__in=Process.objects.filter(id__in=(1,2)).values_list(
'database', flat=True).distinct()) 

produces the following SQL:

SELECT "basic_database"."id", "basic_database"."name" FROM "basic_database" WHERE 
"basic_database"."id" IN (SELECT DISTINCT U0."database_id", U0."database_id" FROM 
"basic_process" U0 WHERE U0."id" IN (1, 2)) LIMIT 21

where U0."database_id" is repeated, and produces an error:

OperationalError: only a single result allowed for a SELECT that is part of an expression

Unfortunately, this is the limit of my Python knowledge, so I don't have a patch to correct this problem.

The query functions perfectly in r9700 and earlier.

Attachments (0)

Change History (3)

comment:1 Changed 5 years ago by mtredinnick

  • Needs documentation unset
  • Needs tests unset
  • Owner changed from nobody to mtredinnick
  • Patch needs improvement unset
  • Status changed from new to assigned

That query cannot possibly come from the queryset you specify, since that queryset will never generate a limit. It's close, though, and does demonstrate a problem.

The root issue is that qs.values_list(...).values(....) doesn't work properly, which needs fixing.

comment:2 Changed 5 years ago by mtredinnick

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

(In [9717]) Fixed #9985 -- qs.values_list(...).values(...) was constructing incorrect SQL.

comment:3 Changed 5 years ago by anonymous

  • milestone post-1.0 deleted

Milestone post-1.0 deleted

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.