Re: [Info-ingres] Wrong results returned from outer join

I agree it should return 0 rows. When I tested it against a random
sample of versions (2.6 SP2, 3.0.3/211, 2006 9.0.4/105) it returned 0
rows. I suspect you have a bug here.

On 9/7/06, Piotr.Wisniewski@xxxxxxxxxxxx <Piotr.Wisniewski@xxxxxxxxxxxx> wrote:
My ingres version is 11273 1 II 2.6/0305 (su4.us5/00.
I think the script below should produce 0 rows according to what is written
in the SQL manual:
" Outer joins specified in the from clause are not the same as joins
specified in the where clause: the from clause specifies sources of data,
while the where clause specifies restrictions to be applied to the sources
of data to produce the
results table."

The last condition '(i_type >=90 or i_t > 0)' is not satisfied for any row.
Still the script returns all 2 rows.
Am I right ?


DECLARE GLOBAL TEMPORARY TABLE session.a

(i_id i4 not null, i_type i1 not null, i_date date not null)

ON COMMIT PRESERVE ROWS WITH NORECOVERY

;

insert into session.a values (21, 11, 'now');

insert into session.a values (221, 41, 'now');



DECLARE GLOBAL TEMPORARY TABLE session.b (i_id i4 not null, i_t i1 not null)

ON COMMIT PRESERVE ROWS WITH NORECOVERY

;

insert into session.b values (21, 0);

insert into session.b values (221, 0);



select a.*,b.i_t

from session.a a left join session.b b

on a.i_id=b.i_id

where i_date > 'today'

and (i_type >=90 or i_t > 0);

\g

~

_______________________________________________
Info-ingres mailing list
Info-ingres@xxxxxxxxxxxxxxx
http://mailman.cariboulake.com/mailman/listinfo.py/info-ingres



--
Paul Mason
.

Relevant Pages