#23622: Subquery doesn't respect order when not bound to a __pk__ field.
----------------------------------------------+----------------------------
Reporter: ris | Owner: nobody
Type: Bug | Status: new
Component: Database layer (models, ORM) | Version: 1.7
Severity: Normal | Keywords: subquery order
Triage Stage: Unreviewed | distinct
Easy pickings: 0 | Has patch: 0
| UI/UX: 0
----------------------------------------------+----------------------------
Fix for bug #20600 doesn't seem to have covered all cases.
Using django git fa4b6482df08d308fe88044b8c8bf981c6225fb8 (stable/1.7.x),
postgres backend.
Example models.py:
{{{
class ModelA ( models.Model ):
pass
class ModelB ( models.Model ):
modela_fk = models.ForeignKey ( ModelA )
field_b0 = models.IntegerField ( null = True )
field_b1 = models.DateField ()
}}}
Enter some data:
{{{
import datetime
a1 = ModelA ()
a1.save ()
a2 = ModelA ()
a2.save ()
ModelB ( modela_fk = a1 , field_b0 = 123 , field_b1 = datetime.date ( 2013
, 1 , 6 ) ).save ()
ModelB ( modela_fk = a1 , field_b0 = 23 , field_b1 = datetime.date ( 2011
, 6 , 6 ) ).save ()
ModelB ( modela_fk = a1 , field_b0 = 234 , field_b1 = datetime.date ( 2011
, 9 , 2 ) ).save ()
ModelB ( modela_fk = a1 , field_b0 = 12 , field_b1 = datetime.date ( 2012
, 9 , 15 ) ).save ()
ModelB ( modela_fk = a2 , field_b0 = 567 , field_b1 = datetime.date ( 2014
, 3 , 1 ) ).save ()
ModelB ( modela_fk = a2 , field_b0 = 76 , field_b1 = datetime.date ( 2011
, 3 , 3 ) ).save ()
ModelB ( modela_fk = a2 , field_b0 = 7 , field_b1 = datetime.date ( 2012 ,
10 , 20 ) ).save ()
ModelB ( modela_fk = a2 , field_b0 = 56 , field_b1 = datetime.date ( 2011
, 1 , 27 ) ).save ()
}}}
Some queries:
{{{
qx = (
Q ( modelb__pk__in = ModelB.objects.order_by ( "modela_fk" ,
"-field_b1" ).distinct ( "modela_fk" ) )
& Q ( modelb__field_b0__gte = 50 )
)
qy = (
Q ( modelb__in = ModelB.objects.order_by ( "modela_fk" ,
"-field_b1" ).distinct ( "modela_fk" ) )
& Q ( modelb__field_b0__gte = 50 )
)
}}}
(only difference being {{{modelb__pk__in}}} vs {{{modelb__in}}})
and...
{{{
>>> frozenset ( ModelA.objects.filter ( qx ).values_list ( "pk" , flat =
True ) ) == frozenset ( ModelA.objects.filter ( qy ).values_list ( "pk" ,
flat = True ) )
False
}}}
We see this is because
{{{
>>> str ( ModelA.objects.filter ( qx ).query )
'SELECT "dummy_modela"."id" FROM "dummy_modela" INNER JOIN "dummy_modelb"
ON ( "dummy_modela"."id" = "dummy_modelb"."modela_fk_id" ) WHERE
("dummy_modelb"."id" IN (SELECT DISTINCT ON
("dummy_modelb"."modela_fk_id") "dummy_modelb"."id" FROM "dummy_modelb"
ORDER BY "dummy_modelb"."modela_fk_id" ASC, "dummy_modelb"."field_b1"
DESC) AND "dummy_modelb"."field_b0" >= 50)'
>>> str ( ModelA.objects.filter ( qy ).query )
'SELECT "dummy_modela"."id" FROM "dummy_modela" INNER JOIN "dummy_modelb"
ON ( "dummy_modela"."id" = "dummy_modelb"."modela_fk_id" ) WHERE
("dummy_modelb"."id" IN (SELECT DISTINCT ON
("dummy_modelb"."modela_fk_id") "dummy_modelb"."id" FROM "dummy_modelb")
AND "dummy_modelb"."field_b0" >= 50)'
}}}
{{{qy}}}'s SQL is missing the ORDER BY clause. This is a bit of an
unexpected gotcha.
--
Ticket URL: <https://code.djangoproject.com/ticket/23622>
Django <https://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 unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/046.f3a01d7f990d50a8a5fd282d1051f143%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
