[Django] #32616: QuerySet filter() does not preserve the argument order for a WHERE clause

6 views

Skip to first unread message

Django

unread,

Apr 7, 2021, 1:21:00 AM4/7/21

to django-...@googlegroups.com

#32616: QuerySet filter() does not preserve the argument order for a WHERE clause
-------------------------------------+-------------------------------------
Reporter: snio89 | Owner: nobody
Type: Bug | Status: new
Component: Database | Version: 3.1
layer (models, ORM) | Keywords: queryset, filter,
Severity: Normal | sql
Triage Stage: | Has patch: 0
Unreviewed |
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-------------------------------------+-------------------------------------
Tested in Django 3.1.7.
QuerySet filter() method allows multiple keyword arguments. And
[https://docs.djangoproject.com/en/3.1/ref/models/querysets/#filter
multiple parameters are joined via AND in the underlying SQL statement.]

Problem: It seems like the keyword argument order of the filter() method
is not preserved for a WHERE clause.

Django Model:
{{{
#!python
class PvPowerRealDaily(models.Model):
id = models.AutoField(primary_key=True)
id_inv = models.IntegerField()
dt_update = models.DateTimeField(auto_now=True)
date_target = models.DateField()
power00 = models.FloatField(null=True, default=None)
...

class Meta:
managed = False
app_label = 'pv'
db_table = 'tbl_power_pv_real_daily'
constraints = [
models.UniqueConstraint(fields=['id_inv', 'date_target'],
name='pk_pvpowerrealdaily_id_inv_date_target'),
]
indexes = [
models.Index(fields=['id_inv', 'date_target']),
models.Index(fields=['id_inv']),
models.Index(fields=['date_target']),
]
}}}

PostgreSQL Table:
{{{
#!sql
CREATE TABLE "tbl_power_pv_real_daily" (
"id" SERIAL,
"id_inv" INTEGER NOT NULL,
"dt_update" TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
"date_target" DATE NOT NULL,
"power00" DOUBLE PRECISION NULL,
...
PRIMARY KEY("id")
);

CREATE UNIQUE INDEX "tbl_power_pv_real_daily_id_inv_date_target_idx" ON
tbl_power_pv_real_daily ("id_inv", "date_target");
CREATE INDEX "tbl_power_pv_real_daily_id_inv_idx" ON
tbl_power_pv_real_daily ("id_inv");
CREATE INDEX "tbl_power_pv_real_daily_date_target_idx" ON
tbl_power_pv_real_daily ("date_target");
}}}

Result 1. Argument order is not preserved with a single filter.

{{{
#!python
PvPowerRealDaily.objects.filter(id_inv__exact=1,
date_target__lte=date_now).order_by('-date_target')[:31]
}}}
{{{
#!sql
SELECT * FROM "tbl_power_pv_real_daily" WHERE
("tbl_power_pv_real_daily"."date_target" <= '2021-04-07'::date AND
"tbl_power_pv_real_daily"."id_inv" = 1) ORDER BY
"tbl_power_pv_real_daily"."date_target" DESC LIMIT 31;
args=(datetime.date(2021, 4, 7), 1)
}}}

Result 2. Argument order is preserved with multiple filters.

{{{
#!python
PvPowerRealDaily.objects.filter(id_inv__exact=1).filter(date_target__lte=date_now).order_by('-date_target')[:31]
}}}
{{{
#!sql
SELECT * FROM "tbl_power_pv_real_daily" WHERE
("tbl_power_pv_real_daily"."id_inv" = 1 AND
"tbl_power_pv_real_daily"."date_target" <= '2021-04-07'::date) ORDER BY
"tbl_power_pv_real_daily"."date_target" DESC LIMIT 31; args=(1,
datetime.date(2021, 4, 7))
}}}

ISO/IEC 9075-1 SQL standard states as follows:
> Where the precedence is not determined by the Formats or by parentheses,
effective evaluation of expressions is generally performed from left to
right. However, it is implementation-dependent whether expressions are
actually evaluated ...

I know the query optimizer of a DBMS can handle the evaluation order
within the WHERE clause. However, keeping the argument order of the filter
method for a WHERE clause would be a better choice.

--
Ticket URL: <https://code.djangoproject.com/ticket/32616>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.

Django

unread,

Apr 7, 2021, 1:42:12 AM4/7/21

to django-...@googlegroups.com

#32616: QuerySet filter() does not preserve the argument order for a WHERE clause
-------------------------------------+-------------------------------------

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------