Mapping column aliases should work in clauses

[machow]

If I understand the spec correctly people should be able to specify that these two options are both correct even when testing ASTs...

SELECT id FROM sometable ORDER BY id ASC

and

SELECT id as id2 FROM sometable ORDER BY id2 ASC

However, if the order by clause is incorrect in the second give feedback:

-- "incorrect ORDER BY clause"
SELECT id as id2 FROM sometable ORDER BY id2 DESC

implementation

This will require mapping identifiers from the solution onto the submission AST. An analogous case is mapping imports in pythonwhat. One solution is, like pythonwhat, keep a dictionary that maps each identifier and alias to the table it is referring to. E.g.

# where these are encountered in order
id as id2 -> sometable.id
id -> sometable.id
id2 -> sometable.id
sometable.id -> sometable.id

Note that this doesn't account for cases where an alias is given to, say, the result of COUNT or a CASE expression. For those situations it would just map to the corresponding COUNT or CASE ASTs...

# for CASE ... as id2
id2 -> CASE AST
# for COUNT(*) as count
count -> COUNT(*)

[machow]

Note that aliasing it a bit tricky, in that aliases occur in namespaces? see this SO post

[machow]

Also that alias can't be used in where clause, since it is run before the target list clause. But note that you can by using a subquery...

[machow]

Alias can't be reused as field in query.

Note that people get around this using subqueries

[machow]

The behavior of alias is pretty complex, and possibly differs between dialects. Let's hold off on all "execute this sql code" related issues until if/when we can devote a block of time to them.

The workaround for this is simply being more flexible in what names to accept.