Bug in SE 7.10 ?: Missing rows from 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.
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