id	summary	reporter	owner	description	type	status	component	version	severity	resolution	keywords	cc	stage	has_patch	needs_docs	needs_tests	needs_better_patch	easy	ui_ux
9985	r9701 breaks for combinations of values_list and unique - it inserts duplicate references in SQL	cmutel@…	Malcolm Tredinnick	"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."		closed	Database layer (models, ORM)	dev		fixed			Unreviewed	0	0	0	0	0	0