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}
{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
On Fri, 23 Apr 1999 17:05:13 -0400, Christian Allaire <callaire@ergonet.com>
wrote:
>
>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}>
>{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}
How many of t5.no are null? I don't think they would be
selected into t7.
Hope that helps,
Douglas Wilson