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 23622,Subquery doesn't respect order when not bound to a __pk__ field.,ris,,"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.",Bug,closed,"Database layer (models, ORM)",1.7,Normal,fixed,subquery order distinct,,Accepted,0,0,0,0,0,0