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.