QuerySet filter() does not preserve the argument order for a WHERE clause

[J. Choi]

Tested in Django 3.1.7.
QuerySet filter() method allows multiple keyword arguments. And ​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:

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:

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.

PvPowerRealDaily.objects.filter(id_inv__exact=1, date_target__lte=date_now).order_by('-date_target')[:31]

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.

PvPowerRealDaily.objects.filter(id_inv__exact=1).filter(date_target__lte=date_now).order_by('-date_target')[:31]

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.

[Mariusz Felisiak]

Duplicate of #31940.