Archives of the TeradataForum

Message Posted: Fri, 16 Feb 2001 @ 16:29:19 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

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 mbtemp1 values (1);
insert into mbtemp1 values (2);
insert into mbtemp1 values (3);
insert into mbtemp1 values (4);
insert into mbtemp1 values (5);
insert into mbtemp1 values (6);
insert into mbtemp1 values (7);
insert into mbtemp1 values (8);
insert into mbtemp1 values (9);
insert into mbtemp1 values (10);

insert into mbtemp2 values (2);
insert into mbtemp2 values (4);
insert into mbtemp2 values (6);
insert into mbtemp2 values (8);
insert into mbtemp2 values (10);

The following sql with subquery against these 2 tables brings back zero rows

select *
from
mbtemp1
where
c1 not in
(select
c1
from
mbtemp2
)
;

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.

Cheers

Mike.

	<Prev	Next>		<<First	<Prev	Next>	Last>>