Product Join in Teradata 15

Database
Enthusiast

Product Join in Teradata 15

Hi Dieter ,

I am running below query 

SELECT ABC.COL1,

ABC.COL2,

ABC.COL3

FROM 

DATABASENAME.ABCD ;

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 .

 

Thanks,

RS

Tags (1)
6 REPLIES
Enthusiast

Re: Product Join in Teradata 15

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. 

BR

Roland

Roland Wenzlofsky
Teradata Employee

Re: Product Join in Teradata 15

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.

Enthusiast

Re: Product Join in Teradata 15

Hi Roland/Folks,
Do you think is the right behaviour as database perspective? I am running same query through Informatica .I was expecting error but it did not get failed .Is there any way or setting we can set for failing of this query ?

Thanks,
RS
Teradata Employee

Re: Product Join in Teradata 15

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.

Enthusiast

Re: Product Join in Teradata 15

Hi ,

I am facing peformance issue whle running query .Below logic is implemented in view .

SELECT 

*

FROM

table A

WHERE

(A.c, A.d) in (

SELECT  x, y

FROM

table b

where  user1 = USER

group by 1,2

)

OR (A.c, A.e) IN (

select x, y

from c

where user1= user

group by 1,2

);

could you please help out for same how we can rewrite same in better 

Enthusiast

Re: Product Join in Teradata 15

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 )

UNION ALL

SELECT * FROM  table A WHERE (A.c, A.e) IN

 ( select x, y from cwhere user1= user group by 1,2);