Need help please: Problem with outer join
Hi,
We have a SE 7.10 DB server on SCO unix , and we get a strange
phenomena when using an outer join in a SQL query:
some rows are missing.
Here is the example that I run from dbaccess:
--------------------
set explain on;
create table t5 ( no int, name char(7));
create table t6 ( no2 int, name2 char(7));
load from t5.unl insert into t5; {t5 contains 1308 rows}
load from t6.unl insert into t6; {t6 contains 2956 rows}
{Note: There are no nulls in t5.no nor t6.no2}
{main query
-------------}
select no,name,name2
from t5, outer t6
where no=no2
into temp t7;
select count(*) nb_t5 from t5; { --> returns 1308}
select count(*) nb_t7 from t7; { --> returns 1049}
------------------------It is alarming that the two temp tables t5 and t7 do not
contain the same # of rows. We have also evidence that
specific numbers from t5.no are missing even when we do
not use the "into temp t7". So the problem is not related to
the temp table.
In the sqexplain.out, we see that the DB engine does a
DYNAMIC HASH JOIN to join no and no2.
If I create an index on t6.no2 before running the main query
then the result is good: both tables contain 1308 rows.
I have tried the same example (no index) on our old 4.10
DB server and the problem does not occur; and in
sqexplain.out we see a AUTOINDEX on the join of no and no2
instead of the dynamic hash join.
I would like to know if someone knows of a problem in SE v7.10
with outer joins or dynamic hash join ? And is there a way to force
the engine to use autoindex instead ?
Thank you,
Chris
______________________________________________________
Get Your Private, Free Email at http://www.hotmail.com