Found an interesting feature in MySQL, I wanted to know more in detail.

There is a query like:

select t.id, (select sum(something) from table t1 where t1.id = t.id) as amt, amt * 10, /* тут будет ошибка */ (select id from table t3 where t3.id = amt) as amt_t3 /* тут не будет ошибки */ from table t

It turns out that MySQL allows you to use the amt value from a subquery in another amt_t3 subquery, but does not allow you to use it as amt * 10 (that is, outside of another subquery). Why?

  • At least because the order of calculation of the fields is not defined - vp_arth
  • @vp_arth and how does it fit in that you can use in a subquery? - teran
  • 3
    on stackoverflow.com/a/18020948/1216425 - teran
  • one
    @vp_arth but 10*(select amt) will be :) - teran
  • one
    and if we loop them, use one another and vice versa: D - teran

1 answer 1

It seems you are wondering why the second subquery is executed, but not multiplying. Although it should be the opposite.

The fact that multiplication cannot be done follows from the fact that aliases declared in a SELECT can be used in GROUP BY , ORDER BY and HAVING expressions. About the fact that the alias can be used in the select itself again is not said anywhere else, this is not in the standard, and it is not implemented in the mysql extensions. What actually can be seen in the description of the SELECT syntax in the documentation .

A select_expr can be given an alias using AS alias_name . The alias is used in the HAVING clauses.

As for the use of aliases in subqueries, the following bug was published for version 5.7.9. It concerns just the use of the external query aliases in the where conditions of the subquery. This topic contains the following developer comment:

1. Why is it stopped working?

A. It stopped working because we were expanding to the SQL standard. And when examining some of the crashes, it was decided. But the crash bug was used in the WHERE clause, not in the SELECT list.

2. Why standard SQL was changed? (or it didn’t previously been given to the standard?)

A: This is a construction of the SQL interface to the standard. The standard has never allowed references to aliases, except within the ORDER BY clause.

3. How does it work again?

It is not necessary to clarify the issue. Thus, we are reopening the bug.

Here is the background for the original decision:

Contrary to the law in which it is a clause (there is no reason for compliance with the regulations). the same phase of query execution. But the support in 5.6 was quite arbitrary:

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

Alias ​​in SELECT list is not valid:

 select a+b as c,c+1 from t1; ERROR 1054 (42S22): Unknown column '`c`' in 'field list'

Reference to c is valid:

 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)

It is easy to say that it’s a list of advertisements. However, we’ll try to make it clear. But referencing aliases in subqueries on the WHERE clause will not be reimplemented.

This shows that:

  • the use of aliases in subqueries is an extension of Mysql (although it’s not really written about the documentation anywhere, there are, however, references to HAVING in the subqueries).
  • the use of aliases was prohibited in the where clause of subqueries due to some errors in their use.
  • the use of the alias must be after the alias has been defined
  • Well, as an alternative to your original error , it is suggested (select amt*10) or 10*(select amt) both of these options should work properly.

Regarding this bug and references to aliases in the HAVING subquery, the documentation:

The SQL standard requires that you use the aggregate functions. However, MySQL supports the subsections as well.

as well as from changelog version 5.7.11

There are no rules for SQL SQL 5.7.8. However, this is a frequently used extension.

In general, I do not know how this having corresponds to the general use of external aliases in subqueries, but it is worth checking whether all the functionality in recent versions has earned or not. I have nothing to check on.

  • thank! The theme is really interesting - Denis