Re: [Django] #24386: Querysets with filters and exclusions based on deep relations build invalid queries. (was: Querysets with filters and exclusions based on deep relations build invalid queries)

[Django]

#24386: Querysets with filters and exclusions based on deep relations build 
invalid
queries.
-+-
 Reporter:  Raphael Gaschignard  |Owner:  (none)
 Type:  Bug  |   Status:  closed
Component:  Database layer   |  Version:  master
  (models, ORM)  |
 Severity:  Normal   |   Resolution:  duplicate
 Keywords:   | Triage Stage:  Accepted
Has patch:  0|  Needs documentation:  0
  Needs tests:  0|  Patch needs improvement:  0
Easy pickings:  0|UI/UX:  0
-+-
Changes (by felixxm):

 * status:  new => closed
 * resolution:   => duplicate
 * needs_tests:  1 => 0


Comment:

 Test added in 9ab1d5559681d3adde4fcfc98a19a7396ed6a42e.
 Fixed in f19a4945e1191e1696f1ad8e6cdc6f939c702728.

 Duplicate of #18726.

-- 
Ticket URL: 
Django 
The Web framework for perfectionists with deadlines.

-- 
You received this message because you are subscribed to the Google Groups 
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to django-updates+unsubscr...@googlegroups.com.
To post to this group, send email to django-updates@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-updates/062.39597649e0bab2166bd6a7a859420596%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.

Re: [Django] #24386: Querysets with filters and exclusions based on deep relations build invalid queries

[Django]

#24386: Querysets with filters and exclusions based on deep relations build 
invalid
queries
-+-
 Reporter:  Raphael Gaschignard  |Owner:  (none)
 Type:  Bug  |   Status:  new
Component:  Database layer   |  Version:  master
  (models, ORM)  |
 Severity:  Normal   |   Resolution:
 Keywords:   | Triage Stage:  Accepted
Has patch:  0|  Needs documentation:  0
  Needs tests:  1|  Patch needs improvement:  0
Easy pickings:  0|UI/UX:  0
-+-

Comment (by Can Sarıgöl):

 I think, [https://github.com/django/django/pull/11142 this test] covers
 this.

-- 
Ticket URL: 
Django 
The Web framework for perfectionists with deadlines.

-- 
You received this message because you are subscribed to the Google Groups 
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to django-updates+unsubscr...@googlegroups.com.
To post to this group, send email to django-updates@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-updates/062.ae54f754dfb1fd81fdf547e06836cecd%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.

Re: [Django] #24386: Querysets with filters and exclusions based on deep relations build invalid queries

[Django]

#24386: Querysets with filters and exclusions based on deep relations build 
invalid
queries
-+-
 Reporter:  Raphael Gaschignard  |Owner:  (none)
 Type:  Bug  |   Status:  new
Component:  Database layer   |  Version:  master
  (models, ORM)  |
 Severity:  Normal   |   Resolution:
 Keywords:   | Triage Stage:  Accepted
Has patch:  0|  Needs documentation:  0
  Needs tests:  1|  Patch needs improvement:  0
Easy pickings:  0|UI/UX:  0
-+-
Changes (by Can Sarıgöl):

 * status:  assigned => new
 * cc: Can Sarıgöl (added)
 * owner:  Can Sarıgöl => (none)


-- 
Ticket URL: 
Django 
The Web framework for perfectionists with deadlines.

-- 
You received this message because you are subscribed to the Google Groups 
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to django-updates+unsubscr...@googlegroups.com.
To post to this group, send email to django-updates@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-updates/062.7fb136558aae35537dac306c42824196%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.

Re: [Django] #24386: Querysets with filters and exclusions based on deep relations build invalid queries

[Django]

#24386: Querysets with filters and exclusions based on deep relations build 
invalid
queries
-+-
 Reporter:  Raphael Gaschignard  |Owner:  Can
 |  Sarıgöl
 Type:  Bug  |   Status:  assigned
Component:  Database layer   |  Version:  master
  (models, ORM)  |
 Severity:  Normal   |   Resolution:
 Keywords:   | Triage Stage:  Accepted
Has patch:  0|  Needs documentation:  0
  Needs tests:  1|  Patch needs improvement:  0
Easy pickings:  0|UI/UX:  0
-+-
Changes (by Can Sarıgöl):

 * status:  new => assigned
 * owner:  nobody => Can Sarıgöl


Comment:

 this issue has been solved by
 
[https://github.com/django/django/blob/fc2536fe66c519b306f673672b795d16f87ed57d/django/db/models/sql/query.py#L1664
 this lines] of Simon's [https://github.com/django/django/pull/11062/files
 #diff-0edd853580d56db07e4020728d59e193R1659 PR]

-- 
Ticket URL: 
Django 
The Web framework for perfectionists with deadlines.

-- 
You received this message because you are subscribed to the Google Groups 
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to django-updates+unsubscr...@googlegroups.com.
To post to this group, send email to django-updates@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-updates/062.0937dd36472924e4b7be02a144d62dd4%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.

Re: [Django] #24386: Querysets with filters and exclusions based on deep relations build invalid queries

[Django]

#24386: Querysets with filters and exclusions based on deep relations build 
invalid
queries
-+-
 Reporter:  rtpg |Owner:  nobody
 Type:  Bug  |   Status:  new
Component:  Database layer   |  Version:  master
  (models, ORM)  |
 Severity:  Normal   |   Resolution:
 Keywords:   | Triage Stage:  Accepted
Has patch:  0|  Needs documentation:  0
  Needs tests:  1|  Patch needs improvement:  0
Easy pickings:  0|UI/UX:  0
-+-

Comment (by dgouldin):

 It looks like this is as a result of using the combination of an exclude
 filter with multiple joins and an F object. The multi-join exclude causes
 the ORM to represent the filter as a subquery, but since that Query object
 doesn't know about its parent, it has to try to make sense of the F object
 in the context of the subquery.

--
Ticket URL: 
Django 
The Web framework for perfectionists with deadlines.

-- 
You received this message because you are subscribed to the Google Groups 
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to django-updates+unsubscr...@googlegroups.com.
To post to this group, send email to django-updates@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-updates/062.19fa6e0bee5bd9e11d2f21655b68848c%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.

Re: [Django] #24386: Querysets with filters and exclusions based on deep relations build invalid queries

[Django]

#24386: Querysets with filters and exclusions based on deep relations build 
invalid
queries
-+-
 Reporter:  rtpg |Owner:  nobody
 Type:  Bug  |   Status:  new
Component:  Database layer   |  Version:  master
  (models, ORM)  |
 Severity:  Normal   |   Resolution:
 Keywords:   | Triage Stage:  Accepted
Has patch:  0|  Needs documentation:  0
  Needs tests:  1|  Patch needs improvement:  0
Easy pickings:  0|UI/UX:  0
-+-

Comment (by rtpg):

 Seemed I lacked a bit of imagination on that one, thanks for the
 workaround for my specific use case charettes. Though I guess that
 wouldn't work on a non-Postgres DB.

--
Ticket URL: 
Django 
The Web framework for perfectionists with deadlines.

-- 
You received this message because you are subscribed to the Google Groups 
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to django-updates+unsubscr...@googlegroups.com.
To post to this group, send email to django-updates@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-updates/062.e9fd6c90b6eca5ae89676692c23b6258%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.

Re: [Django] #24386: Querysets with filters and exclusions based on deep relations build invalid queries

[Django]

#24386: Querysets with filters and exclusions based on deep relations build 
invalid
queries
-+-
 Reporter:  rtpg |Owner:  nobody
 Type:  Bug  |   Status:  new
Component:  Database layer   |  Version:  master
  (models, ORM)  |
 Severity:  Normal   |   Resolution:
 Keywords:   | Triage Stage:  Accepted
Has patch:  0|  Needs documentation:  0
  Needs tests:  1|  Patch needs improvement:  0
Easy pickings:  0|UI/UX:  0
-+-

Comment (by charettes):

 To find the oldest active employees for each owner you could use a
 combination of `DISTINCT ON` and `ORDER BY`:

 {{{#!python
 Employee.objects.filter(status='active').order_by('owner',
 'start_time').distinct('owner')
 }}}

--
Ticket URL: 
Django 
The Web framework for perfectionists with deadlines.

-- 
You received this message because you are subscribed to the Google Groups 
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to django-updates+unsubscr...@googlegroups.com.
To post to this group, send email to django-updates@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-updates/062.d074c2116b0b5da78adffecbdf0a60fc%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.

Re: [Django] #24386: Querysets with filters and exclusions based on deep relations build invalid queries

[Django]

#24386: Querysets with filters and exclusions based on deep relations build 
invalid
queries
-+-
 Reporter:  rtpg |Owner:  nobody
 Type:  Bug  |   Status:  new
Component:  Database layer   |  Version:  master
  (models, ORM)  |
 Severity:  Normal   |   Resolution:
 Keywords:   | Triage Stage:  Accepted
Has patch:  0|  Needs documentation:  0
  Needs tests:  1|  Patch needs improvement:  0
Easy pickings:  0|UI/UX:  0
-+-

Comment (by rtpg):

 I don't know if annotations allow me to work around this. I need to find
 the minimum of only active employees, but to my knowledge the aggregates
 don't really work in combination with filters:

 I added a {{{number}}} field to my Employee model:

 {{{
 In [5]: Employee.objects.annotate(Sum('owner__employee__number')).values()
 Out[5]: [{'status': u'not_active', 'number': 3, u'id': 2,
 'owner__employee__number__sum': 7, 'start_date': datetime.date(2015, 2,
 23), u'owner_id': 1}, {'status': u'active', 'number': 4, u'id': 1,
 'owner__employee__number__sum': 7, 'start_date': datetime.date(2015, 2,
 23), u'owner_id': 1}]

 In [6]:
 
Employee.objects.filter(number=4).annotate(Sum('owner__employee__number')).values()
 Out[6]: [{'status': u'active', 'number': 4, u'id': 1,
 'owner__employee__number__sum': 7, 'start_date': datetime.date(2015, 2,
 23), u'owner_id': 1}]
 }}}

--
Ticket URL: 
Django 
The Web framework for perfectionists with deadlines.

-- 
You received this message because you are subscribed to the Google Groups 
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to django-updates+unsubscr...@googlegroups.com.
To post to this group, send email to django-updates@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-updates/062.23d25828da432fccff467a64168a44ce%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.

Re: [Django] #24386: Querysets with filters and exclusions based on deep relations build invalid queries

[Django]

#24386: Querysets with filters and exclusions based on deep relations build 
invalid
queries
-+-
 Reporter:  rtpg |Owner:  nobody
 Type:  Bug  |   Status:  new
Component:  Database layer   |  Version:  master
  (models, ORM)  |
 Severity:  Normal   |   Resolution:
 Keywords:   | Triage Stage:  Accepted
Has patch:  0|  Needs documentation:  0
  Needs tests:  1|  Patch needs improvement:  0
Easy pickings:  0|UI/UX:  0
-+-
Changes (by shaib):

 * needs_better_patch:   => 0
 * stage:  Unreviewed => Accepted
 * needs_tests:   => 1
 * needs_docs:   => 0


Comment:

 Accepting based on details in report, didn't reproduce myself.

 Just wanted to note for the submitter that even without the bug, the query
 you wrote would not achieve the stated goals -- it would return no
 employees. To do what you intended, you need to replace your `lte` with
 `lt`. Also, another approach for this relies on annotations (annotate
 employee with `Min(owner__employee__startdate)` and filter on that).

--
Ticket URL: 
Django 
The Web framework for perfectionists with deadlines.

-- 
You received this message because you are subscribed to the Google Groups 
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to django-updates+unsubscr...@googlegroups.com.
To post to this group, send email to django-updates@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-updates/062.20374addadd8c3490f4ab212207843a3%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.

[Django] #24386: Querysets with filters and exclusions based on deep relations build invalid queries

[Django]

#24386: Querysets with filters and exclusions based on deep relations build 
invalid
queries
--+
 Reporter:  rtpg  |  Owner:  nobody
 Type:  Bug   | Status:  new
Component:  Database layer (models, ORM)  |Version:  master
 Severity:  Normal|   Keywords:
 Triage Stage:  Unreviewed|  Has patch:  0
Easy pickings:  0 |  UI/UX:  0
--+
 I've hit this bug in 1.6, and reproduced it on master (django
 v1.9.dev20150221182749)

 I have the following models:

 {{{
 class Owner(models.Model):
 pass

 class Employee(models.Model):
 owner = models.ForeignKey(Owner)
 status = models.CharField(max_length=100)
 start_date = models.DateField()

 }}}

 When trying to find the oldest active employees for each owner, I tried
 the following query:

 {{{
 >>> query =
 
Employee.objects.filter(status='active').exclude(owner__employee__start_date__lte=F('start_date'),owner__employee__status='active').distinct()
 >>> query
   [...]
   File "/Users/rtpg/proj/test_django/django-
 trunk/django/db/backends/utils.py", line 64, in execute
 return self.cursor.execute(sql, params)
   File "/Users/rtpg/proj/test_django/django-trunk/django/db/utils.py",
 line 95, in __exit__
 six.reraise(dj_exc_type, dj_exc_value, traceback)
   File "/Users/rtpg/proj/test_django/django-
 trunk/django/db/backends/utils.py", line 64, in execute
 return self.cursor.execute(sql, params)
 ProgrammingError: missing FROM-clause entry for table "u1"
 LINE 1: ...e_employee" U0 INNER JOIN "core_employee" U2 ON ( U1."id" = ...
  ^
 }}}

 The SQL query generated by the queryset seems to be at fault
 {{{
 >>> print query.query
 SELECT DISTINCT "core_employee"."id", "core_employee"."owner_id",
 "core_employee"."status", "core_employee"."start_date" FROM
 "core_employee" WHERE ("core_employee"."status" = active AND NOT
 ("core_employee"."owner_id" IN (SELECT U2."owner_id" AS Col1 FROM
 "core_employee" U2 WHERE U2."status" = active) AND
 "core_employee"."owner_id" IN (SELECT U2."owner_id" AS Col1 FROM
 "core_employee" U0 INNER JOIN "core_employee" U2 ON ( U1."id" =
 U2."owner_id" ) WHERE U2."start_date" <= (U0."start_date"
 }}}

 I'm not very good at reading complex SQL queries, so I'm not sure what
 this is supposed to look like but in any case this query is refused by
 PostgreSQL

--
Ticket URL: 
Django 
The Web framework for perfectionists with deadlines.

-- 
You received this message because you are subscribed to the Google Groups 
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to django-updates+unsubscr...@googlegroups.com.
To post to this group, send email to django-updates@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-updates/047.a863db610e9a63c09a95598558556d6e%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.