为什么mySQL能够在通常无法重用别名时解析这些列别名？

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.

大多数SQL专家都会说,不能在同一级别的select中重用别名;经常解决这个问题,使用CTE;或者将查询包装为子查询,以便可以引用别名。但是,如果在select本身的子查询中引用别名,mySQL似乎允许这样的情况;所以它在技术上并不是同一水平。

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:

所以这里的问题有两个方面:

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

这是一个缺乏文档的mySQL“功能”还是有人可以解释编译器如何解析别名?
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

这个问题源于一个已经问过的问题:在MySQL中按别名选择列

2 个解决方案

#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.

我可以猜到发生了什么。 MySQL正在解析子查询(选择c1)。它在子查询中找不到c1,因此它开始在外部查询中查找引用。

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.

根据SQL的规则,它应该只查看from子句中的列。但是,MySQL似乎也在寻找列别名。

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:

虽然我称之为bug,但MySQL似乎认为它是一个功能。 (MySQL的另一个例子是将“bug”视为“特征”。)考虑这个查询:

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)

不支持引用'c1'(项目列表中的前向引用)

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

该错误表明MySQL故意解析“向后”引用。

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:

为了记录,我永远不会依赖这个功能。它不仅违反了ANSI标准和数据库之间的独特行为。这令人困惑。考虑这个小修改:

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.

查询解决了哪个c1?我会告诉你的。而且这甚至没有考虑到c1可能是一个变量。

#2

Investigation has lead me to this statement: As an extension MySQL also allows them in GROUP BY and HAVING. However, they are invalid in the WHERE clause and in other parts of the SELECT list. For more information see http://dev.mysql.com/doc/refman/5.7/en/problems-with-alias.html. However the section doesn't specifically talk about reuse of alias in select and subquery. The bug does seem to indicate this was a feature in prior versions (5.6.23) but not in 5.7.9..

调查引导我发表这样的声明:作为扩展,MySQL还允许他们使用GROUP BY和HAVING。但是,它们在WHERE子句和SELECT列表的其他部分中无效。有关更多信息,请参阅http://dev.mysql.com/doc/refman/5.7/en/problems-with-alias.html。但是,该部分没有具体讨论在select和子查询中重用别名。该错误似乎表明这是以前版本(5.6.23)中的一个功能,但不是5.7.9中的功能。

However further in the above link the below information is provided:

但是,在上述链接中,还提供了以下信息:

[9 Dec 2015 15:35] Roy Lyseng ...

[2015年12月9日15:35] Roy Lyseng ......

I read about Aliased expressions (that they can't be used in WHERE clause), but then the question arises:

我读到了有关别名的表达式(它们不能在WHERE子句中使用),但随后出现了问题:

1. Why is it stopped working? It stopped working because we were screening the MySQL extensions to the SQL standard. And when examining some crashing bugs due to corner cases that were using references to aliased expressions, it was decided that this extension was poorly defined and could be handled by other means. But the crashing bug was using a subquery in the WHERE clause, not in the SELECT list.

1.为什么停止工作?它停止工作,因为我们正在筛选SQL标准的MySQL扩展。并且当检查由于使用对别名表达式的引用的极端情况导致的一些崩溃错误时,确定该扩展定义不明确并且可以通过其他方式处理。但崩溃的错误是在WHERE子句中使用子查询,而不是在SELECT列表中。

2. Why standard SQL was changed? (or it did not previously been given to the standard?) This construction has never been part of standard SQL, it was a MySQL extension to the standard. The standard has never allowed references to aliases, except within the ORDER BY clause.

2.为什么标准SQL被更改了? (或者它以前没有被赋予标准?)这种结构从未成为标准SQL的一部分,它是标准的MySQL扩展。除了在ORDER BY子句中之外,该标准从未允许引用别名。

3. Does exists an option of the configuration that query will working again OR how to find all such requests that doesn't working? We have reconsidered how to handle this problem, and will try to revert the decision on not supporting aliases in subqueries in SELECT list. Thus, we are reopening the bug.

3.是否存在查询将再次起作用的配置选项或如何查找不起作用的所有此类请求?我们已经重新考虑了如何处理这个问题,并将尝试恢复不支持SELECT列表中子查询中的别名的决定。因此,我们正在重新开放这个bug。

Here is a longer background for the original decision:

以下是原始决定的较长背景:

Contrary to references to aliases in subqueries in the WHERE clause (and in GROUP BY, for that matter), there is no reason (except standard compliance) that we should not allow references to aliases in the SELECT list, since they should be available in the same phase of query execution. But the support in 5.6 was quite arbitrary:

与WHERE子句中的子查询中的别名(以及GROUP BY中的别名)的引用相反,没有理由(标准合规除外)我们不应该允许在SELECT列表中引用别名,因为它们应该在查询执行的同一阶段。但5.6中的支持是相当随意的:

Given this: create table t1(a int, b int),

鉴于此:创建表t1(一个int,b int),

Alias in SELECT list is not valid:

SELECT列表中的别名无效:

  select a+b as c,c+1 from t1;

ERROR 1054 (42S22): Unknown column 'c' in 'field list'

ERROR 1054(42S22):'字段列表'中的未知列'c'

But within a subquery, reference to c is valid:

但是在子查询中,对c的引用是有效的:

  select a+b as c,(select c+1) from t1;

And subquery must be after definition of alias:

子查询必须在别名定义之后:

  select (select c+1),a+b as c from t1;

ERROR 1247 (42S22): Reference 'c' not supported (forward reference in item list)

ERROR 1247(42S22):不支持参考'c'(项目列表中的前向参考)

So, it is easy to say that support for references to aliases in SELECT list was rather ad-hoc. Nevertheless, we will try to reimplement the old solution, but with no attempt at cleaning up the obvious holes in the support for this feature. But referencing aliases in subqueries in the WHERE clause will not be reimplemented.

因此,很容易说在SELECT列表中对别名的引用的支持是相当临时的。尽管如此,我们将尝试重新实现旧的解决方案,但是没有尝试清理支持此功能的明显漏洞。但是,不会重新实现在WHERE子句中引用子查询中的别名。

Conclusions:

While it is contrary to common knowledge of referencing an alias in the same select, it does appear mySQL will allow this. It is not common place in any other RDBMS to my knowledge. One needs to consider the order in which the alias is identified (Gordon's answer addresses 3 places one needs to considers).

虽然它与在同一个select中引用别名的常识相悖,但似乎mySQL会允许这样做。据我所知,它在任何其他RDBMS中都不常见。人们需要考虑别名被识别的顺序(戈登的答案解决了需要考虑的3个地方)。

Personally I would gravitate away from this functionality unless additional documentation can be found explaining how it works or is to work. Most developers would stick with the use of a CTE or a subquery to handle the alias reuse and from a maintenance stand point this may be more confusing to others than the standard approaches.

我个人会倾向于远离这个功能,除非可以找到解释它是如何工作或工作的附加文档。大多数开发人员会坚持使用CTE或子查询来处理别名重用,从维护的角度来看,这可能比标准方法更容易混淆。

"Aliased Expressions" or "references to aliases in the SELECT list" seems to be what this is called. but I can't find much more in documentation of this "feature" (unintended consequence?)

“Aliased Expressions”或“SELECT列表中对别名的引用”似乎就是这个名称。但我在这个“功能”的文档中找不到更多(意想不到的后果?)

#1