#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.
--
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 django-updates+unsubscr...@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/049.bf8ba924ac5e753df08cbc614bb84a0e%40djangoproject.com.
