Why is mySQL able to resolve these column aliases when normally one can't reuse an alias?

Question

Most SQL experts would say one can't re-use an alias in a select at the same level; often to work around this a CTE is used; or one wraps the query as a subquery so the alias can be referenced. However, mySQL seems to allow such a situation provided the alias is referenced in a subquery within the select itself; so it's not technically at the same level.

DEMO:

SELECT CONCAT(a, b) AS c1, CONCAT((SELECT c1), 2)
FROM (SELECT 'a' a, 'b' b, 'c' c UNION ALL 
      SELECT '1', '2', '3') t1;

SELECT 1 a, 2 b, (SELECT A+B) c
     , concat((SELECT a),(SELECT b)) d 
     , greatest((SELECT a),(SELECT b), (SELECT c))

Both of the above queries work.. yes; they work. (or do a really good job of making it seem like they work)

While this does not: as one would expect.

SELECT CONCAT(a, b) AS c1, CONCAT(c1, 2)
FROM (SELECT 'a' a, 'b' b, 'c' c UNION ALL 
      SELECT '1', '2', '3') t1;

So the question here is two fold:

1. Is this a mySQL "feature" lacking documentation or can someone explain how the compiler is able to resolve the aliases?

2. Is this a documented feature that one could rely upon; if so where is this documented so one could understand the implications of using this method?

This question stems from one already asked: Select column by alias in MySQL

Answer 1

This looks like a parsing bug to me (in other databases, you would get an error on the first two queries).

I can guess at what is happening. MySQL is parsing the subquery (select c1). It doesn't find c1 in the subquery, so it starts looking for references in outer queries.

According to the rules of SQL, it should only be looking at columns in the from clause. However, MySQL also seems to be looking at column aliases.

Although I would call this a bug, MySQL seems to consider it a feature. (Yet another example of MySQL considering a "bug" a "feature".) Consider this query:

SELECT CONCAT((SELECT c1), 2), CONCAT(a, b) AS c1
FROM (SELECT 'a' a, 'b' b, 'c' c UNION ALL 
      SELECT '1', '2', '3') t1;

And the error it generates:

Reference 'c1' not supported (forward reference in item list)

The error suggests that MySQL is intentionally parsing "backwards" references.

For the record, I would never depend on this functionality. It is not only a violation of the ANSI standard and unique behavior among databases. It is confusing. Consider this little modification:

SELECT CONCAT(a, b) AS c1, CONCAT((SELECT c1), 2)
FROM (SELECT 'a' a, 'b' b, 'c' c UNION ALL 
      SELECT '1', '2', '3') t1 CROSS JOIN
     (SELECT 'abcdef' as c1) x;

Which c1 does the query resolve to? I'll let you figure that out. And this doesn't even take into account that c1 could be a variable.