Suppose you have

SELECT a+b AS some_sum, a-b AS some_diff
FROM some_table

It would be syntactically invalid, as far as I understand it, to have

SELECT a+b AS some_sum, a-b AS some_diff,
some_sum + some_diff AS something_else
FROM some_table

, because `some_sum` and `some_diff` aren’t names that yet exist in the scope of that `SELECT` statement. To use `some_sum` and `some_diff`, you need an outer scope that has access to all the names created in the inner scope — so you need either

SELECT some_sum + some_diff AS something_else
FROM
(
SELECT a+b AS some_sum, a-b AS some_diff
FROM some_table
)

or, if you don’t need to attach a name to `some_sum + some_diff`, you can just do

SELECT a+b AS some_sum, a-b AS some_diff
FROM some_table
HAVING some_sum + some_diff >= 0

(or whatever it is that you might want to do with `some_sum + some_diff`)

So … are there some nightmare scenarios that would result from allowing the names ‘`some_sum`’ and ‘`some_diff`’ to be visible in the inner scope? Does anyone know the SQL spec well enough to understand the problem?

__P.S.__: Oh, I think I can answer the question a moment after asking it: the problem is that you can create a name in the inner scope that’s the same as the name of a field in the base table. That is, you can do something like

SELECT 2*a AS a, 3*a AS triple_a
FROM some_table

, and that `3*a` statement is ambiguous: are you referring to triple the *derived* `a`, or triple the *base* `a`?

Even still, I can imagine allowing one scope to refer to a name created within that scope, but then making it a syntax error to introduce a name in a given scope that’s the same as a name in a base table. The way to resolve the ambiguity in this case would be to do

SELECT 2*a AS double_a, 3*(double_a) AS sextuple_a
FROM some_table

I wonder if a Sufficiently Smart Compiler (“a classic argument often pulled out in a LanguagePissingMatch”) could find all ambiguous uses of names, or whether there’s some logical reason why it just couldn’t.

Then again, forbidding the creation of a name in a given scope that is the same as the name of a name in an inner scope or base table would make a lot of common usage impossible. I routinely do

SELECT one_thing, sum(something) something_else
FROM some_table
GROUP BY one_thing

I.e., I’m reusing `something_else` in this scope, even though it also exists in the base table. If I had to constantly use new names for derived fields, just because the derived fields might be referred to in the outer scope, I think I’d find that annoying.

Then, of course, there’s the ever-available argument that it’s just simpler to implement a compiler which imposes certain mild aesthetic constraints on its users.