Archives of the TeradataForum
Message Posted: Fri, 16 Feb 2001 @ 16:29:19 GMT
A colleague of mine has discovered an issue in some of his coding that I would like to get some feedback from. To replicate the problem in very simple form I have created 2 tables.
create table mbtemp1 (c1 integer);
create table mbtemp2 (c2 integer);
with the following values inserted.
insert into mbtemp2 values (2);
The following sql with subquery against these 2 tables brings back zero rows
This seems wrong to me as 'c1' in the subquery does not exist(it is 'c2' that comes from mbtemp2). If the subquery ran by itself it would fail so why does the whole job succeed.
I can see in the explain that because c1 exists in mbtemp1 the machine is using a product join between the 2 tables but fail to see that this is correct.
The explain is totally different when the subquery is correct (sel c2 from mbtemp2)- ie a merge join is used between the 2 columns as I would expect and 5 rows in the answer set are received.
It is easy enough to get round by using aliases and therefore identifying where columns do/do not exist but would appreciate a more detailed explanation as to why the supposed "incorrect sql" still works.
|Copyright 2016 - All Rights Reserved|
|Last Modified: 28 Jun 2020|