Hi Dieter ,
I am running below query
ABC is also tablename which exists in same database where ABCD resides.I am running above query in SQLA 15.0 .First of all , it is not getting failed .I am totally got surprised to see this behaviour and other thing if i am looking at explain plan then it is making product join between the tables ABC and ABCD .
I am not sure about this functionality at all .
As far as I remember this behaviour is the same like in previous releases and not related to Teradata 15. Actually, one of the "features" which can lead to errors whcih are very difficult to detect.
Since the early days of Teradata, the grammar has not required the FROM clause. early on this was viewed as an ease of use feature. The parser looks through the query finding all database and table references and adding them to the internal version of the FROM clause. It then satisfies column references from that list and uses that list as the list of tables to be joined. While it is now recognized that the ease of use is outweighed by the issues caused by surprises like you describe above, it is hard to change it at this point because there are a lot of queries out there that would fail because they count on this legacy behavior.
There is not a way to cause this behavior to change and issue an error instead.
If the product join is large, it is possible to catch it with a TASM filter and error it because of the combination of product join and very large row/cost estimate.
The below approach will improve the performance ...
SELECT * FROM table A WHERE (A.c, A.d) in
(SELECT x, y FROM table b where user1 = USER group by 1,2 )
SELECT * FROM table A WHERE (A.c, A.e) IN
( select x, y from cwhere user1= user group by 1,2);