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

Yesterday I ran into an ambiguity issue in SQL for the first time. I got used to SQL errors in case of table ambiguity and yesterday did not even try to think about the problem in that 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 two tables here have the same alias and WHERE id = t.id is always 1.

The query is quite valid in MySQL and MS SQL. But the question is whether it is a bug in terms of SQL.

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

Reading the SQL-92 Standard, section 6.3 (table reference), it appears to me that the ambiguity is legal. What you refer to as an 'alias' the Standard knows to be a correlation name. The table reference in your FROM clause is covered by case 2a and your other table reference with the same correlation name is covered by case 2b and they are of distinct scope.

Something to bear in mind is that one of aims of the Standard was to consolidate established functionality found in SQL products in the late 1980s and the major vendors had representatives on the Standard's committee. I guess the ambiguity was already present in vendor's products.

You were missing some of the elements you were trying to filter by in the subqueries.

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;

I edited this so that the subqueries have the same alias, as per your question. From a data perspective, you val2_sum would always return 20 because you didn't have anything that differentiated the id value. It would always be the sum of the entire table. Whether that was a side effect of your simplification or it is the actual code, I'm not sure.

If you can use a join with the subquery, you will be able to get the specific values. I would avoid the subquery in the select if you could.