ORDER BY unnecessary verbose

In theory, ORDER BY allows unqualified column names, if the column can be uniquely determined.

In practice, this does not work for JOINs with equality conditions between columns.
An example: Assume that both of the tables / views in the following example contain a column "PVN".

WITH s AS (SELECT PVN, ScheibenID FROM 00020_Scheibendaten.2023QA3_Reporting)

SELECT * FROM 005_BerechnungsDaten_Entwicklung.001_Tarif_View AS t

INNER JOIN s

ON t.PVN = s.PVN

ORDER BY PVN

LIMIT 1;

This example should work, since the values of the column PVN are equal for both columns (t.PVN as well as s.PVN), therefore the sort ordering is equal. It does not work, because ORDER BY sees the columns t.PVN and s.PVN as different.

My proposal: If an ORDER-BY-columns seems to be ambiguous (multiple ,,candidate columns" x.column, y.column, ...), check

• if we have only INNER JOINS in the upper level,
• if the columns have equality conditions (of the form x.column = y.column or the other way around) in the combined ON clauses of all INNER JOINS (uses the associativity of inner joins) (technically: equality is a highest-level clause in the conjunction of all ON conditions, which themselves are conjunctions: s.PVN = t.PVN OR xy shall not be considered.)
• and if these equalities form a spanning subtree between all column candidates (using transitivity of equality, undirected graph with columns as vertices and equality conditions as edges).

If all these conditions are fulfilled, it is safe to use anyone of these candidate columns as sort column, such that an unnecessary qualification of the column name within the ORDER-BY-clause is not necessary.