#35788: Order By using column number with Annotated fields
-------------------------------------+-------------------------------------
Reporter: Adrian Garcia | Owner: (none)
Type: | Status: closed
Cleanup/optimization |
Component: Database layer | Version: 5.1
(models, ORM) |
Severity: Normal | Resolution: needsinfo
Keywords: order_by, annotate, | Triage Stage:
column number, | Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Adrian Garcia):
The
[https://dev.mysql.com/doc/refman/8.4/en/select.html#:~:text=use%20of%20column%20positions%20is%20deprecated%20because%20the%20syntax%20has%20been%20removed%20from%20the%20sql%20standard.%20
MySQL documentation] references its removal from the standard.
Additionally, [https://www.red-gate.com/hub/product-learning/sql-prompt
/avoid-using-constants-in-an-order-by-clause this article] specifically
references that the use of constants was defined in the ANSI SQL-92
standard, and subsequently removed in ANSI SQL-99, but goes on to say that
most RDBMS vendors still support this practice.
All DBs that Django currently supports (and many that are supported via
third party libraries) appear to honor the deprecated column numbers, and
given how long ago this change was made it's likely they will continue to
support this. It's mostly the inconsistency of `order_by` using an integer
constant _only_ with annotated fields that bothers me, rather than any
risk of the feature suddenly breaking, which is why I offered to make the
change if that's acceptable.
----
Since these older versions of the SQL spec can be found online for free, I
was able to find the actual definitions.
From page pages 371 and 372 of
[https://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt ANSI SQL-92]:
{{{
<order by clause> ::=
ORDER BY <sort specification list>
<sort specification list> ::=
<sort specification> [ { <comma> <sort specification> }... ]
<sort specification> ::=
<sort key> [ <collate clause > ] [ <ordering specification> ]
<sort key> ::=
<column name>
| <unsigned integer>
<ordering specification> ::= ASC | DESC
...
10)If ORDER BY is specified, then each <sort specification> in the <order
by clause> shall identify a column of T.
Case:
a) If a <sort specification> contains a <column name>, then T shall
contain exactly one column with that <column name> and the <sort
specification> identifies that column.
b) If a <sort specification> contains an <unsigned integer>, then the
<unsigned integer> shall be greater than 0 and not greater than the degree
of T. The <sort specification> identifies the column of T with the ordinal
position specified by the <unsigned integer>.
}}}
From page 651 of [[https://web.cecs.pdx.edu/~len/sql1999.pdf ANSI SQL-99]]
{{{
<order by clause> ::=
ORDER BY <sort specification list>
<sort specification list> ::=
<sort specification> [ { <comma> <sort specification> }... ]
<sort specification> ::=
<sort key> [ <collate clause> ] [ <ordering specification> ]
<sort key> ::=
<value expression>
<ordering specification> ::= ASC | DESC
...
NOTE 287 – A previous version of ISO/IEC 9075 allows <sort specification>
to be a <signed in-
teger> to denote a column reference of a column of T. That facility no
longer exists. See Annex E,
‘‘Incompatibilities with ISO/IEC 9075:1992 and ISO/IEC 9075-4:1996’’.
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/35788#comment:2>
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/0107019234983004-63a53695-20aa-48ae-8345-f3209b9f3e83-000000%40eu-central-1.amazonses.com.
