/* This query used to work in older versions tested in 4.04 but produces
zero rows in latest version*/
select ssd.scenario_code,*
from clive
inner join category c on c.category_code = clive.category_code
inner join scenario_detail s on s.period = clive.period
inner join scenario_stream_detail ssd on ssd.category_code = c.category_code
and ssd.period = clive.period and ssd.scenario_code = s.scenario_code
where s.scenario_code = 335;


/*If I change the join on scenario_stream_detail to ssd.scenario_code = 335
(See Below) then the query works again in the latest version*/
select ssd.scenario_code,*
from clive
inner join category c on c.category_code = clive.category_code
inner join scenario_detail s on s.period = clive.period
inner join scenario_stream_detail ssd on ssd.category_code = c.category_code
and ssd.period = clive.period and ssd.scenario_code = 335
where s.scenario_code = 335;

I have posted incident report 2182 for this..

Thanks
Clive.

I have tracked this down to query optomisation, the query returns the
correct result set if I add the keyword NOJOINOPTIMIZE at the end of the
query.

And appears to occur with tables that do not have an index, the table clive
was a memory table created with Select Into and hence had no secondary
indexes.

However the same behaviour is shown with disk tables also.

"Clive" <dd@dddd.com> wrote in message
news:EAD9054A-C9F7-40CC-91D2-D5C134EF2F2C@news.elevatesoft.com...
> /* This query used to work in older versions tested in 4.04 but produces
> zero rows in latest version*/
> select ssd.scenario_code,*
> from clive
> inner join category c on c.category_code = clive.category_code
> inner join scenario_detail s on s.period = clive.period
> inner join scenario_stream_detail ssd on ssd.category_code =
> c.category_code and ssd.period = clive.period and ssd.scenario_code =
> s.scenario_code
> where s.scenario_code = 335;
>
>
> /*If I change the join on scenario_stream_detail to ssd.scenario_code =
> 335 (See Below) then the query works again in the latest version*/
> select ssd.scenario_code,*
> from clive
> inner join category c on c.category_code = clive.category_code
> inner join scenario_detail s on s.period = clive.period
> inner join scenario_stream_detail ssd on ssd.category_code =
> c.category_code and ssd.period = clive.period and ssd.scenario_code = 335
> where s.scenario_code = 335;
>
> I have posted incident report 2182 for this..
>
> Thanks
> Clive.
>

Clive,

<< I have tracked this down to query optomisation, the query returns the
correct result set if I add the keyword NOJOINOPTIMIZE at the end of the
query. >>

You are correct.  It's a dependency issue that is overlooked when the query
optimizer is trying different join orderings.  One of your join expressions:

inner join scenario_stream_detail ssd on ssd.category_code = c.category_code
and ssd.period = clive.period and ssd.scenario_code = s.scenario_code

relies on another join expression:

inner join category c on c.category_code = clive.category_code

and DBISAM isn't detecting this properly because there is an intermediate
join between the two.  Therefore, when it goes to put the joins back into
the correct order after trying the various join orderings, it doesn't do it
correctly.

--
Tim Young
Elevate Software
www.elevatesoft.com

Any idea how  I identify other queries that would have this problem? .

We have hundreds of SQL statements, I think all is OK as this one stuck out,
but I need to be sure , short of putting NOJOINOPTIMIZE at the end of all
queries

Thanks
Clive.

"Tim Young [Elevate Software]" <timyoung@elevatesoft.com> wrote in message
news:A82323FF-3705-497C-84CE-9D68BAD56456@news.elevatesoft.com...
> Clive,
>
> << I have tracked this down to query optomisation, the query returns the
> correct result set if I add the keyword NOJOINOPTIMIZE at the end of the
> query. >>
>
> You are correct.  It's a dependency issue that is overlooked when the
> query optimizer is trying different join orderings.  One of your join
> expressions:
>
> inner join scenario_stream_detail ssd on ssd.category_code =
> c.category_code
> and ssd.period = clive.period and ssd.scenario_code = s.scenario_code
>
> relies on another join expression:
>
> inner join category c on c.category_code = clive.category_code
>
> and DBISAM isn't detecting this properly because there is an intermediate
> join between the two.  Therefore, when it goes to put the joins back into
> the correct order after trying the various join orderings, it doesn't do
> it correctly.
>
> --
> Tim Young
> Elevate Software
> www.elevatesoft.com
>
>

Clive,

<< Any idea how  I identify other queries that would have this problem? . >>

It's an eyeball issue - you have to look at the join expressions themselves
to determine whether you have joins that depend upon other joins for correct
execution.   The bottom line is that any time you have an expression in join
C that references a column that is part join A's expression, and there is
one or more joins separating the two joins, then you'll see this issue.
It's very, very, very specific in nature.  In fact, I would say that it
would have been very easy for DBISAM to go its entire lifespan without
someone encountering this problem.

--
Tim Young
Elevate Software
www.elevatesoft.com

Thanks, Is a fix going in for this?..

Cheers
Clive.

"Tim Young [Elevate Software]" <timyoung@elevatesoft.com> wrote in message
news:2FBC2E93-90F0-4520-A7F0-D5F4386EB9DE@news.elevatesoft.com...
> Clive,
>
> << Any idea how  I identify other queries that would have this problem? .
>  >>
>
> It's an eyeball issue - you have to look at the join expressions
> themselves to determine whether you have joins that depend upon other
> joins for correct execution.   The bottom line is that any time you have
> an expression in join C that references a column that is part join A's
> expression, and there is one or more joins separating the two joins, then
> you'll see this issue. It's very, very, very specific in nature.  In fact,
> I would say that it would have been very easy for DBISAM to go its entire
> lifespan without someone encountering this problem.
>
> --
> Tim Young
> Elevate Software
> www.elevatesoft.com
>
>

Clive,

<< Thanks, Is a fix going in for this?.. >>

Absolutely, we always fix bugs.  I thought I indicated this in one of my
emails to you, but I could be wrong.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim

>Absolutely, we always fix bugs. I thought I indicated this in one of my
>emails to you, but I could be wrong.

In that case there's this loverly little one in V2.12 <vbg>

Roy Lambert

So you did!.

"Tim Young [Elevate Software]" <timyoung@elevatesoft.com> wrote in message
news:21108070-BD73-4059-A203-E89445A55495@news.elevatesoft.com...
> Clive,
>
> << Thanks, Is a fix going in for this?.. >>
>
> Absolutely, we always fix bugs.  I thought I indicated this in one of
> my emails to you, but I could be wrong.
>
> --
> Tim Young
> Elevate Software
> www.elevatesoft.com
>
>

Roy,

<< In that case there's this loverly little one in V2.12 <vbg> >>

Ahh, let me clarify - we don't retroactively fix bugs in old releases, we
fix bugs in current releases.

--
Tim Young
Elevate Software
www.elevatesoft.com