[Info-ingres] Wrong results returned from outer join
- From: Piotr.Wisniewski@xxxxxxxxxxxx
- Date: Thu, 7 Sep 2006 13:24:39 +0200
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
~
.
- Follow-Ups:
- Re: Wrong results returned from outer join
- From: ghingres
- Re: Wrong results returned from outer join
- Prev by Date: Re: [Info-ingres] Rollforwardb E_DM1315_RFP_DB_BUSY Database is currently being used. Database lock conflict.
- Next by Date: Re: Rollforwardb E_DM1315_RFP_DB_BUSY
- Previous by thread: Rollforwardb E_DM1315_RFP_DB_BUSY Database is currently being used. Database lock conflict.
- Next by thread: Re: Wrong results returned from outer join
- Index(es):
Relevant Pages
|
|
