What does ORDER BY resolve to when a SELECT alias reuses an existing column's name?

## The shape of the trap ```sql SELECT -amount AS amount FROM ledger WHERE amount > 0 ORDER BY amount; ``` This statement runs. It filters rows whose **stored** `amount` is positive, and sorts by the **negated** value. One identifier, two meanings, no error. That is the shadowing hazard this leaf is about, and it is why reviewers flag aliases that reuse a base column's name. ## The resolution rules, clause by clause - `WHERE`, `GROUP BY`, `HAVING` are resolved before the select list produces output names, so an identifier there can only be an **input column** — a column of a table in `FROM`. - `ORDER BY` is resolved last, over the finished result. A **bare** identifier that matches an output column name resolves to that output column. PostgreSQL documents this explicitly: when a simple name matches both an output column and an input column, `ORDER BY` interprets it as the output column. MySQL documents the same search order — select-list expressions first, then columns of the tables in `FROM`. - A **compound expression** in `ORDER BY` — anything more than a bare name, such as `UPPER(amount)` or `amount + 0` — is built from input columns, because output names are not values you can compute over. So `ORDER BY amount` and `ORDER BY amount + 0` can sort by different things in the same query. That is not a bug in any engine; it falls out of what an output name is. ## Qualification disambiguates, in one direction An output column name has no table to belong to — it exists only in the result — so it can never be qualified. That gives a precise tool: ```sql SELECT -amount AS amount FROM ledger AS l ORDER BY l.amount; -- unambiguously the table's column ``` `l.amount` must be the input column. There is no corresponding syntax to force the alias, other than writing the bare name (or, in engines that support it, the select-list ordinal). So: qualify when you mean the column; leave it bare when you mean the alias; and prefer never to need the distinction. ## Why the language allows the shadowing at all Because aliases are how you *rename* things, and renaming to the same name is a legitimate operation. `SELECT COALESCE(nickname, name) AS name` is idiomatic — the caller wants a column called `name`, and the query author is providing one. Nothing is wrong with the alias; the hazard is only that a later clause in the *same* statement may then refer to the identifier and get a different answer than the author expected. ## A related restriction worth knowing When a query uses `SELECT DISTINCT`, `ORDER BY` may reference only expressions that appear in the select list, because sorting by something not present in the deduplicated result is not well defined. In such a query the shadowing question disappears: only output columns are addressable. ## Diagnosing it in the wild The symptom is a report that filters correctly but sorts wrongly, or a top-N list whose ordering does not match the column it appears to be sorted on. Nothing errors and nothing logs. Three checks find it quickly: 1. Look for an `AS` in the select list whose name also exists in one of the tables in `FROM`. 2. Rewrite the `ORDER BY` reference with the table alias and see whether the ordering changes; if it does, the query was sorting by the alias. 3. Rename the alias to something unique and re-run — if the sort changes, the alias was shadowing. ## The rule to state in review "An alias may reuse a column's name only when the statement never references that name again." In practice, that means: shadowing a name in a final projection is fine; shadowing it in a query that also sorts, groups or filters on the name is a defect, because a maintainer will read the identifier once and assume it means one thing in every clause. Where the shadow is genuinely wanted — the `COALESCE(nickname, name) AS name` case — compute it in a CTE and let the outer query sort on the unambiguous result. ## What interviewers listen for They want to hear that resolution depends on the clause, that `ORDER BY` prefers output names for bare identifiers while `WHERE` and `GROUP BY` see only input columns, and that a qualified reference is always an input column. The senior signal is the conclusion drawn from it: the rule exists, you should know it, and you should still write queries that never require the reader to know it.