#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.