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

Question

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

Answer 1

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.