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
some_diff aren’t names that yet exist in the scope of that
SELECT statement. To use
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
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.