Ordering by the result of RawSQL with F expression and distinct.

[wilhelmhb]

Hi,
I run into an exception when combining RawSQL, distinct and order_by with an expression:

ids = [10]
params = (tuple(ids), )
queryset = Item.objects.annotate(
      score=RawSQL(""" 
            SELECT SUM(id) AS score 
            FROM app_item 
            WHERE id IN %s""", 
            params=params, 
            output_field=FloatField()), 
       ).order_by(F("score").desc(nulls_last=True)).distinct() 
 print(queryset.query)

where Item is any model you want, as I'm only using its id.

The exact exception is: TypeError: not all arguments converted during string formatting

The exception seems due to the sql_with_params method of django.db.models.sql.Query duplicating the params one time too many : a print(params) at line 257 in django/db/models/sql/query.py outputs (('10',), ('10',), ('10',)), while the query has only two placeholders.

I'm running Django 2.2.6

[Simon Charette]

Could you try reproducing against the recently released 3.0 version, I vaguely remember a similar issue that was addressed in the past weeks.

[Mariusz Felisiak]

Executing this query in Django 2.2 fails with

django.db.utils.ProgrammingError: syntax error at or near "WHERE"
LINE 4: ...    WHERE id IN (10)) AS "score",                 WHERE id I...

but it was fixed in 567b9928a3ad37e95b9ae17ec41342daa6968739.

I cannot reproduce TypeError when calling print(queryset.query). I think we can mark this as a duplicate of #29692.