Pig supports this, though using $1, $2 syntax, which is useful and convenient in some sense. However, I didn't find it's in standard SQL. One downside of supporting this is that ordering starts to matter now in the select list. If I do "select a, b, c from T", the output is deterministic regardless T's schema (as long it has a, b, and c). On the other hand, if I do "select $1, $2, $3 from T" and if later on the table's schema is changed as (a, b, d, c), then my query will return a different data set.
So, projecting by numbers is different from "they just get translated into numbers anyway".
Adding columns is quite common in hadoop data. Of course, one can argue that columns should always add at the end, which doesn't happen that way always.