ORDER BY on a source column fails when a select item is aliased to that column's name #26462

Describe the bug

A select item aliased to the same name as a source column, plus an ORDER BY on that qualified source column, fails planning.

Postgres plans it. Its SELECT reference says each ORDER BY expression "can be the name or ordinal number of an output column (SELECT list item), or it can be an arbitrary expression formed from input-column values" (SELECT, ORDER BY clause). "a simple name that matches both an output column name and an input column name" is read as the output column. e.email is qualified, not a simple name, and Postgres sorts on the input column (run below).

CREATE TABLE employees (id INT, email VARCHAR);
INSERT INTO employees VALUES (1, 'B@x'), (2, 'a@y');
SELECT lower(e.email) AS email FROM employees e ORDER BY e.email;

Schema error: Schema contains qualified field name e.email and unqualified field name email which would be ambiguous

Reproduced on 53.0.0, 54.0.0, 54.1.0 and 55.0.0 (datafusion-python). Postgres 16.13, with C collation, returns b@x, then a@y: sorted by the original e.email. ORDER BY email there returns a@y, then b@x.

ORDER BY lower(e.email), the aliased expression, plans from 54.0. ORDER BY email, the output name, plans on every version. Only the source column fails.

Why it fails

The ORDER BY key is not in the select list, so the planner carries e.email into the projection to sort on it. The projection then holds e.email and the alias email. DFSchema::check_names refuses a qualified and an unqualified field with the same name, even though the extra column is internal and is projected away after the sort.

Expected behavior

Plan it. Return 1 column, email, sorted by the original e.email.

Describe the solution you'd like

Carry the missing sort key under an internal, collision-free alias, and strip it in the projection above the sort. Then the user's alias never meets the carried column in 1 schema. This sits in the ORDER BY missing-expression path that #10326 proposes to unify. If that refactor lands, it should cover this case; please link it there.

Additional context

Related: #6543 (duplicate aliases), #10326 (ORDER BY planning), #13558 (CAST(c AS text) … ORDER BY c, the validate_unique_names sibling of this), #17294 (AmbiguousReference after a join, builder API).