Is SQL table alias ambiguity prohibited by standard or blocked by implementations?

Question

Yesterday I ran into the ambiguity problem in SQL for the first time. I got used to SQL errors in case of ambiguity of the table, and yesterday I didn’t even try to think of the problem in this way.

Here is a simplified query:

SELECT
  val1,
  (
    SELECT SUM(val2) as val2_sum
    FROM (
      SELECT 1 as id, 10 as val2
      UNION ALL
      SELECT 2 as id, 10 as val2
    ) t
    WHERE id = t.id
  ) as val2_sum
FROM (
  SELECT 1 as id, 'a' as val1
  UNION ALL
  SELECT 2 as id, 'b' as val1
) as t
WHERE id = 1;

Result:

+------+----------+
| val1 | val2_sum |
+------+----------+
| a    |       20 |
+------+----------+

Expected Result:

+------+----------+
| val1 | val2_sum |
+------+----------+
| a    |       10 |
+------+----------+

The problem is that the two tables here have the same alias, and WHERE id = t.id is always 1.

The query is fully applicable in MySQL and MS SQL. But the question is whether this is an error in terms of SQL.

UPD : As @Phil Sandler notes, and as I noted in the comments, the only problem is that both tables with UNION have the same alias t. Renaming the table from val2 to t2 will fix the problem.

Answer 1

SQL-92, 6.3 ( ), , . , "", , . FROM 2a, 2b, .

-, , , , SQL 1980- , . , .

Answer 2

, .

SELECT
  val1
, val2_sum
FROM (
  SELECT 1 as id, 'a' as val1
  UNION ALL
  SELECT 2 as id, 'b' as val1
) as t
join (SELECT id, SUM(val2) as val2_sum
        FROM (
      SELECT 1 as id, 10 as val2
      UNION ALL
      SELECT 2 as id, 10 as val2)
    as t GROUP BY id) t2 on t2.id = t.id
WHERE t.id = 1;

, , . val2_sum 20, , id. . , , .

, . , .