Opened 6 years ago

Closed 6 years ago

Last modified 3 years ago

#12625 closed (invalid)

.filter(foo__in= duplicates

Reported by: CarlFK Owned by:
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

http://docs.djangoproject.com/en/dev/ref/models/querysets/#in

SELECT ... WHERE id IN (1, 3, 4);

There is no JOIN, and yet:

eps all have the same parent: ITA.

>>> from main.models import Client, Show, Location, Episode
>>>  Location.objects.all()
[<Location: Holladay>, <Location: Multnomah>, <Location: ITA>]

>>> show = Show.objects.get(slug='January_2010_Meeting')
>>> eps = Episode.objects.filter(show=show)
>>> Location.objects.filter(episode__in=eps)
[<Location: ITA>, <Location: ITA>, <Location: ITA>]

I expected

>>> Location.objects.filter(episode__in=eps)
[<Location: ITA>]

I am pretty sure this used to work as described.

hmmm...

In [15]: Location.objects.filter(id__in=[9,9,9])
Out[15]: [<Location: ITA>]

Change History (6)

comment:1 Changed 6 years ago by CarlFK

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

same in 1.0.4:

In [1]: from main.models import Client, Show, Location, Episode
In [2]: show = Show.objects.get(slug='January_2010_Meeting')
In [3]: eps = Episode.objects.filter(show=show)
In [4]: Location.objects.filter(episode__in=eps)
Out[4]: [<Location: ITA>, <Location: ITA>, <Location: ITA>]

In [5]: import django
In [7]: django.get_version()
Out[7]: '1.0.4'

comment:2 Changed 6 years ago by russellm

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

It's very hard to evaluate whether your outputs are incorrect if you don't give us the inputs.

Regarding the duplicate results - Django doesn't guarantee uniqueness in the return set unless you specify .distinct(). However - without more detail, its impossible to know whether you have found a bug in Django, or if your expectations are incorrect.

Marking invalid because the bug report doesn't contain enough detail to be helpful. Please reopen if you care to provide more details and you're not satisified that this is just Django returning duplicate rows because you aren't telling it not to.

comment:3 follow-up: Changed 6 years ago by CarlFK

  • Resolution invalid deleted
  • Status changed from closed to reopened

"Django doesn't guarantee uniqueness"

It does in http://docs.djangoproject.com/en/dev/ref/models/querysets/#in

Example:
Entry.objects.filter(id__in=[1, 3, 4])
SQL equivalent:
SELECT ... WHERE id IN (1, 3, 4);

That will not duplicate rows.

I have a feeling what is happening:
.filter(idin=X)
If X is a list: WHERE id IN X
else: JOIN X.table ON FK=X.PK

Which breaks

As for inputs, do you want to see the values in my db, or a fully functional code example?

Or, I bet if you run the example code in the docs:

inner_qs = Blog.objects.filter(name__contains='Cheddar')
entries = Entry.objects.filter(blog__in=inner_qs)

It will not do what it says:

This queryset will be evaluated as subselect statement:
SELECT ... WHERE blog.id IN (SELECT id FROM ... WHERE NAME LIKE '%Cheddar%')

Does the setup code for this example exist anywhere?

comment:4 in reply to: ↑ 3 Changed 6 years ago by russellm

Replying to CarlFK:

"Django doesn't guarantee uniqueness"

It does in http://docs.djangoproject.com/en/dev/ref/models/querysets/#in

Example:
Entry.objects.filter(id__in=[1, 3, 4])
SQL equivalent:
SELECT ... WHERE id IN (1, 3, 4);

That will not duplicate rows.

Granted. But that isn't a guarantee of uniqueness. It isn't the same as the sample query, either.

As for inputs, do you want to see the values in my db, or a fully functional code example?

We need enough detail to be able to reproduce the problem. It makes a difference if you have an m2m relationship or a FK relationship, for example. Without that detail, we can't reproduce the problem you are having. A fully functional code example would be helpful. A fully functional code example that is integrated into the Django test suite would be extremely helpful, because that will form the first part of a fix for the problem.

Or, I bet if you run the example code in the docs:

inner_qs = Blog.objects.filter(name__contains='Cheddar')
entries = Entry.objects.filter(blog__in=inner_qs)

It will not do what it says:

This queryset will be evaluated as subselect statement:
SELECT ... WHERE blog.id IN (SELECT id FROM ... WHERE NAME LIKE '%Cheddar%')

Yes - because that's exactly what you are asking it to do. Django lazy-evaluates queries. If the argument to a filter clause is a queryset, it will be evaluated as a subselect.

Does the setup code for this example exist anywhere?

I'm not sure what you're asking for. The sample models you reference are defined in the docs. There isn't any sample data in the docs.

comment:5 Changed 6 years ago by russellm

  • Resolution set to invalid
  • Status changed from reopened to closed

Closing due to a lack of feedback. Please reopen if you can provide a clear, reproducible test case (i.e., a complete working example of models and code that produces invalid results).

comment:6 Changed 3 years ago by akaariai

  • Component changed from ORM aggregation to Database layer (models, ORM)
Note: See TracTickets for help on using tickets.
Back to Top