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
32616	QuerySet filter() does not preserve the argument order for a WHERE clause	J. Choi	nobody	"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."	Bug	closed	Database layer (models, ORM)	3.1	Normal	duplicate	queryset, filter, sql		Unreviewed	0	0	0	0	0	0