[Django] #11104: HAVING filter screws with extra() SQL parameter ordering

[Django-3]

#11104: HAVING filter screws with extra() SQL parameter ordering
-----------------------------+----------------------------------------------
 Reporter:  miracle2k        |       Owner:            
   Status:  new              |   Milestone:            
Component:  ORM aggregation  |     Version:  SVN      
 Keywords:                   |       Stage:  Unreviewed
Has_patch:  0                |  
-----------------------------+----------------------------------------------
 {{{
 >>> qs = Model.objects.extra(where=['foo>%s'],
 params=[2]).annotate(count=Count('bar')).filter(bar=99)
 >>> qs.query.as_sql()
 ('SELECT `site_publication`.`id`, ..., FROM ... WHERE foo > %s GROUP BY
 ... HAVING bar > %s',
  (99, 2))
 }}}

 As you can see, the parameters (99 and 2) are in the wrong order. "foo",
 the first filter, should be evaluated against 2, and bar, the second one,
 against 99.

 If "bar" is not an aggregate, it works, because apparently the filter
 would be placed in the WHERE clause *before* the "extra()" where.

 Using rev. 10743.

--
Ticket URL: <http://code.djangoproject.com/ticket/11104>
Django <http://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups "Django updates" group.
To post to this group, send email to django-updates@...
To unsubscribe from this group, send email to django-updates+unsubscribe@...
For more options, visit this group at http://groups.google.com/group/django-updates?hl=en
-~----------~----~----~----~------~----~------~--~---

[Django-3]

#11104: HAVING filter screws with extra() SQL parameter ordering
--------------------------------------+-------------------------------------
          Reporter:  miracle2k        |         Owner:    
            Status:  new              |     Milestone:    
         Component:  ORM aggregation  |       Version:  SVN
        Resolution:                   |      Keywords:    
             Stage:  Accepted         |     Has_patch:  0  
        Needs_docs:  0                |   Needs_tests:  0  
Needs_better_patch:  0                |  
--------------------------------------+-------------------------------------
Changes (by russellm):

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

Comment:

 A related use case, reported in #11117:

 {{{
 Book.objects.extra(
     select={'date': "DATE_FORMAT(timestamp, '%s')"},
     select_params=('%%H',),
     where=["DATE_FORMAT(timestamp, '%%H:%%i') > '%s'"],
     params=['22:00']).values('date').annotate(cnt=Count('id'))
 }}}

 the resulting sql query is:

 {{{
 SELECT DATE_FORMAT(timestamp, '%H') AS `date`, COUNT(id) AS `cnt`
 FROM `books`
 WHERE DATE_FORMAT(timestamp, '%H:%i') > '%H'
 GROUP BY DATE_FORMAT(timestamp, '22:00')
 ORDER BY timestamp ASC
 }}}

 The placeholder values '%H' and '22:00' are interchanged, which is
 obviously a result of using the GROUP BY here on a user-defined variable
 with placeholder (--> 'date')

--
Ticket URL: <http://code.djangoproject.com/ticket/11104#comment:1>
Django <http://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups "Django updates" group.
To post to this group, send email to django-updates@...
To unsubscribe from this group, send email to django-updates+unsubscribe@...
For more options, visit this group at http://groups.google.com/group/django-updates?hl=en
-~----------~----~----~----~------~----~------~--~---

[Django-3]

#11104: HAVING filter screws with extra() SQL parameter ordering
--------------------------------------+-------------------------------------
          Reporter:  miracle2k        |         Owner:    
            Status:  new              |     Milestone:    
         Component:  ORM aggregation  |       Version:  SVN
        Resolution:                   |      Keywords:    
             Stage:  Accepted         |     Has_patch:  0  
        Needs_docs:  0                |   Needs_tests:  0  
Needs_better_patch:  0                |  
--------------------------------------+-------------------------------------
Comment (by mpaolini):

 I think all filters on annotated queryset should end up in HAVING clause,
 not in WHERE

 imagine you want to do something like:

 {{{
 SELECT * FROM blog group by location, rating having count(id) > 10 OR
 rating >1
 }}}

 right now (r11199) you can ony add HAVING clauses using if you filter
 using  an aggregate. Does this make sense to you?

--
Ticket URL: <http://code.djangoproject.com/ticket/11104#comment:2>
Django <http://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups "Django updates" group.
To post to this group, send email to django-updates@...
To unsubscribe from this group, send email to django-updates+unsubscribe@...
For more options, visit this group at http://groups.google.com/group/django-updates?hl=en
-~----------~----~----~----~------~----~------~--~---

[Django-3]

#11104: HAVING filter screws with extra() SQL parameter ordering
--------------------------------------+-------------------------------------
          Reporter:  miracle2k        |         Owner:    
            Status:  new              |     Milestone:    
         Component:  ORM aggregation  |       Version:  SVN
        Resolution:                   |      Keywords:    
             Stage:  Accepted         |     Has_patch:  0  
        Needs_docs:  0                |   Needs_tests:  0  
Needs_better_patch:  0                |  
--------------------------------------+-------------------------------------
Comment (by Alex):

 #11916 was closed as a dupe of this, it has a patch.

--
Ticket URL: <http://code.djangoproject.com/ticket/11104#comment:3>
Django <http://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups "Django updates" group.
To post to this group, send email to django-updates@...
To unsubscribe from this group, send email to django-updates+unsubscribe@...
For more options, visit this group at http://groups.google.com/group/django-updates?hl=en
-~----------~----~----~----~------~----~------~--~---

[Django-3]

#11104: HAVING filter screws with extra() SQL parameter ordering
--------------------------------------+-------------------------------------
          Reporter:  miracle2k        |         Owner:    
            Status:  new              |     Milestone:    
         Component:  ORM aggregation  |       Version:  SVN
        Resolution:                   |      Keywords:    
             Stage:  Accepted         |     Has_patch:  0  
        Needs_docs:  0                |   Needs_tests:  0  
Needs_better_patch:  0                |  
--------------------------------------+-------------------------------------
Comment (by paluh):

 I think that I've found bug related to this ticket. When generating list
 for grouping values (not keys) are taken from extra selects.[[BR]]
 [[BR]]

 {{{
 --- django/db/models/sql/query.py       (revision 11729)
 +++ django/db/models/sql/query.py       (working copy)
 @@ -885,9 +885,9 @@
              group_by = self.group_by or []

              extra_selects = []
 -            for extra_select, extra_params in
 self.extra_select.itervalues():
 -                extra_selects.append(extra_select)
 -                params.extend(extra_params)
 +            for extra_select_key in self.extra_select.iterkeys():
 +                extra_selects.append(extra_select_key)
 +
              for col in group_by + self.related_select_cols +
 extra_selects:
                  if isinstance(col, (list, tuple)):
                      result.append('%s.%s' % (qn(col[0]), qn(col[1])))

 }}}

--
Ticket URL: <http://code.djangoproject.com/ticket/11104#comment:4>
Django <http://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups "Django updates" group.
To post to this group, send email to django-updates@...
To unsubscribe from this group, send email to django-updates+unsubscribe@...
For more options, visit this group at http://groups.google.com/group/django-updates?hl=en
-~----------~----~----~----~------~----~------~--~---

[Django-3]

#11104: HAVING filter screws with extra() SQL parameter ordering
--------------------------------------+-------------------------------------
          Reporter:  miracle2k        |         Owner:    
            Status:  new              |     Milestone:    
         Component:  ORM aggregation  |       Version:  SVN
        Resolution:                   |      Keywords:    
             Stage:  Accepted         |     Has_patch:  0  
        Needs_docs:  0                |   Needs_tests:  0  
Needs_better_patch:  0                |  
--------------------------------------+-------------------------------------
Comment (by paluh):

 sorry for my previous post and attachment - it's related to #11916 which
 I've reopened.

--
Ticket URL: <http://code.djangoproject.com/ticket/11104#comment:5>
Django <http://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups "Django updates" group.
To post to this group, send email to django-updates@...
To unsubscribe from this group, send email to django-updates+unsubscribe@...
For more options, visit this group at http://groups.google.com/group/django-updates?hl=en
-~----------~----~----~----~------~----~------~--~---