Opened 5 years ago

Closed 5 years ago

#31058 closed Bug (duplicate)

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

Reported by: wilhelmhb Owned by: nobody
Component: Database layer (models, ORM) Version: dev
Severity: Normal Keywords: RawSQL, ordering, expression
Cc: Triage Stage: Unreviewed
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

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

Change History (2)

comment:1 by Simon Charette, 5 years ago

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

comment:2 by Mariusz Felisiak, 5 years ago

Resolution: duplicate
Status: newclosed
Summary: Ordering by the result of RawSQL with F expression and distinctOrdering by the result of RawSQL with F expression and distinct.
Version: 2.2master

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.

Note: See TracTickets for help on using tickets.
Back to Top