23.5 What changed in expressions, lookups and the compiler
What changed from Django 5.0 to this commit in the layer that writes a queryset’s SQL: parameters as a tuple, aliases quoted, the aggregates that came into django.db.models, None and JSONNull on a JSON field, and smaller changes to expressions, lookups and the compiler, with the code that now does each.
Each part of a statement, an expression, a lookup, a join, compiles to a pair, SQL with placeholders and the parameters that fill them, and the compiler joins the pairs (From QuerySet to SQL). The deprecations below are listed with the others in What still warns: the deprecations and the releases that end them.
Parameters as a tuple
Until 6.0 BaseExpression, and Lookup and Lookup, could return their parameters as a list or a tuple. Since 6.0 the second element is to be a tuple. The release notes give as the reason the interoperability problems of the mixture. At this commit Func.as_sql gathers its arguments’ parameters in a list and returns a tuple of them, and BuiltinLookup unpacks both halves into one tuple, so that a half of either kind still joins. Nothing checks the type of what an as_sql returns.
Aliases quoted, and the percent sign
The release notes of 6.1 say that the aliases of annotations in the select list, and those of tables and joins, are now quoted systematically, so that a special character in one cannot collide with the SQL around it; and that since a quoted alias is case-sensitive, raw SQL, a RawSQL’s, that names an alias of mixed case may have to quote it too. At this commit SQLCompiler writes each alias of the select list through BaseDatabaseOperations, and BaseTable.as_sql and Join.as_sql write a table’s alias through SQLCompiler. SQLCompiler is deprecated since 6.1 in favour of it: it warns with RemovedInDjango2028Warning and calls quote_name (The compiler: as_sql, execute_sql and results_iter). Here the annotation’s alias and the joined table’s are quoted:
# sql_page.py at this commit, on SQLite
str(Ship.objects.filter(home__ships__name='Gannet').annotate(Tons=F('tonnage')).values('Tons').query) -> 'SELECT "fleet_ship"."tonnage" AS "Tons" FROM "fleet_ship" INNER JOIN "fleet_port" ON ("fleet_ship"."home_id" = "fleet_port"."id") INNER JOIN "fleet_ship" "T3" ON ("fleet_port"."id" = "T3"."home_id") WHERE "T3"."name" = Gannet ORDER BY "fleet_ship"."name" ASC'
The aliases a queryset is given are checked by Query against a pattern of what is forbidden (The SELECT clause: get_select, the select mask and klass_info). Since 6.0 a percent sign in an alias warns with RemovedInDjango2028Warning and is otherwise accepted; a comment above FORBIDDEN_ALIAS_PATTERN gives the pattern that will refuse it. The aliases security releases since 5.0 closed to injection are in Security fixes: SQL injection.
Aggregates: ordering, the new classes and arity
Since 6.0 an Aggregate takes an ordering, Aggregate, and refuses it with TypeError unless the class sets Aggregate (Aggregate; Aggregates: Aggregate, Count and get_aggregation). With it two classes came to django.db.models: StringAgg, which joins values with a delimiter and was the PostgreSQL application’s alone, and AnyValue, which takes any one value of the group and is refused with NotSupportedError where BaseDatabaseFeatures is off, as on MariaDB. In 6.1 BitAnd, BitOr and BitXor followed, refused where BaseDatabaseFeatures is off, as on Oracle before 21; and StringAgg took distinct=True on SQLite with the delimiter Value(","). The PostgreSQL application’s classes of those names are now subclasses that warn with RemovedInDjango2028Warning (django/; What changed in the other contrib apps).
Since 5.2 Avg, Max, Min and Sum declare Func.arity as 1, and Func.__init__ refuses any other number of expressions with TypeError; Count, StdDev and Variance declare it too, but their own __init__ passes on one expression and takes a second argument as filter or sample:
# sql_page.py at this commit
Sum('tonnage', 'name') -> raises TypeError: 'Sum' takes exactly 1 argument (2 given)
The JSON field: strings, None and JSONNull
A string given to a JSONField, or compared with it by "exact" or "in", is a JSON string at this commit, encoded like any value by BaseDatabaseOperations, json.dumps unless the backend has its own. Until 5.1 a string that decoded as JSON was taken as already encoded, with a warning since 4.2.
A None means two things on a JSONField: saved, SQL’s "NULL"; compared with the whole value in an exact lookup, JSON’s null. 6.1 added JSONNull, a Value of None whose output is a JSONField, which means JSON’s null in both places. The comparison with a top-level None is deprecated: JSONExact warns with RemovedInDjango2028Warning and compiles it as before, to the parameter JSONNull gives. A comment marks the line that sets JSONExact for removal when the deprecation ends; without it Query turns the comparison into "isnull", "IS NULL" (From a name to a column: names_to_path, setup_joins and build_lookup), while a commented line in KeyTransformExact is to set it there, so that a key compared with None still means JSON’s null. Since 6.1 iexact=None on a key is compiled as exact=None on it, where before it became "isnull".
# sql_page.py at this commit, on SQLite; plans is Ship.objects.alias(plan=Value({"crates": 12}, JSONField())).values("name")
JSONField().get_db_prep_value('[]', connection) -> '"[]"'
plans.filter(plan=None).query.sql_with_params()[1] -> ('{"crates": 12}', 'null')
warns RemovedInDjango2028Warning: Using None as the right-hand side of an exact lookup on JSONField to mean JSON scalar 'null' is deprecated. Use JSONNull() instead (or use the __isnull lookup if you meant SQL NULL).
plans.filter(plan=JSONNull()).query.sql_with_params()[1] -> ('{"crates": 12}', 'null')
plans.filter(plan__crates__iexact=None).query.sql_with_params() == plans.filter(plan__crates=None).query.sql_with_params() -> True
A negative index in a key’s path counts from the end of an array, on SQLite since 6.0 and on Oracle 21 and later since 6.1; BaseDatabaseOperations refuses one where BaseDatabaseFeatures is off, as on MySQL (BaseDatabaseOperations; The SQL a backend writes: BaseDatabaseOperations).
The smaller changes
| what | the change | where now |
|---|---|---|
Now on Oracle |
the change "LOCALTIMESTAMP", where it was "CURRENT_ (5.0) |
where now Now.as_oracle django/ |
"XOR" of several operands |
the change on the backends without "XOR", all but MySQL’s, true when an odd number of operands are; before, exactly one (5.0) |
where now WhereNode.as_sql django/ |
| An integer outside the column’s range | the change decided without the database: "exact", and a comparison no value in range passes, match nothing; one every value passes is dropped (5.0) |
where now Integer django/ |
Query |
the change the 5.0 notes made it an ordered list; at this commit it is a set, and the select’s order is Query.selected’s or Query’ (5.0) |
where now Query django/ |
nulls_, nulls_last=False |
the change refused with ValueError (5.0) |
where now OrderBy django/ |
| get_joining_columns, get_reverse_joining_columns | the change gone; Join asks only for Foreign or Foreign (6.0) |
where now Join django/ |
RowRange, ValueRange |
the change a row frame may start after the current row, or end before it; both take an exclusion, a Window (5.1) |
where now Base django/WindowFrame django/ |
| Ordering by a transform of an annotation | the change Query takes an annotation’s alias followed by transforms, a key of a JSONObject for one (5.1) |
where now SQLCompiler django/ |
Slicing an F |
the change makes a Sliced, written by the output field’s Field: a substring for a text field, refused where not overridden (5.1) |
where now Sliced django/ |
Base |
the change where false, a unique or exclusion constraint’s validation uses the expression’s one source instead (5.1) | where now Base django/ |
Base |
the change a selected annotation that sets it makes an aggregation run over a subquery (5.2) | where now Query django/ |
Base |
the change unless set, resolve_ refuses a composite key’s ColPairs among the sources with ValueError (5.2) |
where now Base django/ |
JSONArray |
the change new: a JSON array of its arguments (5.2) | where now JSONArray django/ |
| A subquery of a composite key | the change may stand on the right of lookups other than "in", "exact" among them (6.0) |
where now Lookups on relations and composite keys: RelatedIn and the tuple lookups |
UUID4, UUID7 |
the change new functions with a UUIDField output; UUID7 takes an optional shift (6.1) |
where now uuid.py django/ |
Extract and Trunc without a time zone |
the change deconstructed while USE_TZ is on, as in a migration’s Field.db_default, warn with Removed; Django 2029 will capture the current zone (6.2) |
where now Timezone django/ |