If I join two tables and alias both tables, is there still a problem if I restrict on one of the columns of the tables without using the alias? I tried to replicate this and it appears the optimizer now resolves the issue. Is this something that Teradata now handles. In the past it has always treated the unaliased column as a column in another table producing a product join which goes out to lunch.
If I correctly understand you.
You say something like
FROM Table1 A , Table2 B
WHERE AColumn = B.BColumn.
Being AColumn the name of a column of the Table1 A table.
Problems only will appear if the Table2 B got a column with the same name, so him cannot know which one you mean.
If AColumn only exists in Table1 A, the 'A.' Is not necesary.
I hope I understand it correctly.
The Teradata parser/optimizer has never treated an unqualified column name as a reference to the unaliased table in a situation like this. Unqualified column names are alwways resolved within the tables that have been identified in the query.
The concern noted about adding another join to the plan comes when a table is aliased in the from clause and then a column reference is qualified with the table name rather than the alias. In a (potentially confusing) attempt to be user friendly, the Teradata parser does not require all tables to be named in the FROM clause and picks up table names from qualified references within the rest of the query - effectively adding them to the from clause. When an alias and the table name are both used, it results in two instances of a table which the optimizer assumes is a request for a self join - and without a condition that becomes a (potentially very costly) product join.
SELECT * FROM t1 a WHERE c1 = 1; -- will always resolve c1 to be a.c1.
SELECT * FROM t1 a WHERE T1.c1=1; -- will be equivalent to saying:
SELECT * FROM t1 a, T1 WHERE t1.c1=1; -- resulting in a product join between the two instances of T1 in the FROM clause.
Thank you Todd! That is exactly what I was looking for. I had forgotten it was aliasing a table and then qualifying it as a predicate that caused the problems.