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

I get the expected zero rows when using:

II 2.6/0305 (rs4.us5/00) + p9695
II 3.0.2 (a64.lnx/111)

Leandro Fava
DBA-SINF-UNISC http://www.unisc.br
Presidente IUG-BR http://www.iug-br.org
Fone: +55 51 3717 7636


-----Mensagem original-----
De: info-ingres-admin@xxxxxxxxxxxxxxx
[mailto:info-ingres-admin@xxxxxxxxxxxxxxx] Em nome de Paul White
Enviada em: Friday, September 08, 2006 12:40 AM
Para: info-ingres@xxxxxxxxxxxxxxx
Assunto: RE: [Info-ingres] Wrong results returned from outer join

Two rows are incorrectly returned for
II 2.6/0305 (int.w32/00)
Patch 11126

Paul
-----Original Message-----
From: info-ingres-admin@xxxxxxxxxxxxxxx
[mailto:info-ingres-admin@xxxxxxxxxxxxxxx]On Behalf Of Andy X Keadell
Sent: Friday, 8 September 2006 2:19 AM
To: William Avery
Cc: info-ingres@xxxxxxxxxxxxxxx; info-ingres-admin@xxxxxxxxxxxxxxx
Subject: Re: [Info-ingres] Wrong results returned from outer join






I get two rows returned. Ingres II 2.0 axm/vms patch 7643

Andy Keadell
Ingres Database Administrator
CSC at Reynolds & Reynolds
937-485-9468


--------------------------------------------------------------
--------------
--------------------------------------------------------------
--------------
------------------------

This is a PRIVATE message. If you are not the intended
recipient, please
delete without copying and kindly advise us by e-mail of the
mistake in
delivery. NOTE: Regardless of content, this e-mail shall not
operate to
bind CSC to any order or other contract unless pursuant to
explicit written
agreement or government initiative expressly permitting the
use of e-mail
for such purpose.
--------------------------------------------------------------
--------------
--------------------------------------------------------------
--------------
------------------------





William Avery
<william.avery@ed
is.co.uk>
To
Sent by: info-ingres@xxxxxxxxxxxxxxx
info-ingres-admin
cc
@cariboulake.com

Subject
Re: [Info-ingres] Wrong results
09/07/2006 11:39 returned from outer join
AM









I get the expected zero rows when using the following versions

II 2.6/0305 (su9.us5/00) with patch 10934
II 3.0.2 (a64.sol/109)
II 9.0.4 (int.w32/105)

Regards,

Will Avery

Piotr.Wisniewski@xxxxxxxxxxxx said the following on 07/09/06 12:24:

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






--
Best wishes,

William Avery
Senior Associate


Tel. 0870 8308510
Fax. 0870 8308599
Email. William.Avery@xxxxxxxxxx
--------------------------------------------------------------
----------

This e-mail is confidential. If you are not the intended recipient
please notify us immediately. Any dissemination,
distribution, copying
or use of this communication without prior permission of the sender is
strictly prohibited.

Every reasonable precaution to minimise the risk of computer
viruses has
been taken, however we recommend that recipients undertake their own
additional measures.

Edis Partnerships Limited, The Talbot, 2 Victoria Street,
Bristol BS1 6BB
http://www.edis.co.uk

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


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

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


.

Relevant Pages

  • Re: CROSS JOIN
    ... > was that depreciating the original FROM .. ... WHERE syntax would never ... > The problem is that the WHERE clause is done after the FROM clause. ... but thats just outer join stuff. ...
    (comp.databases)
  • Re: Performance degradation with view and "left outer join" vs. "from x, outer y"
    ... Turned Every Which Way But Loose", I reminded the attendees that the ANSI SQL '92 parsing rules REQUIRE that filters in the WHERE clause of a query MUST be processed POST-JOIN in order for an RDBMS to be compliant. ... That means that your filter, c.custid = "AB1234", is applied after all rows of the customer table have been LEFT OUTER JOINed to the my_view VIEW with the results stored into a temp table. ... Now if I use the informix extended outer join syntax: ...
    (comp.databases.informix)
  • Re: Better "Join" vs "Where" clause?
    ... running the SQL directly, in the SQL Server tools, rather than ... WHERE clause has been deprecated, ... AFAIK one cannot perform an outer join in Access without using the explicit ...
    (microsoft.public.access.queries)
  • Re: exclude lines after outer join, like left joins in 7.24
    ... an EXISTS clause for the NOT EXISTS clause if you don't need any ... OUTER join using ANSI syntax and join in the ON clause and filter out ...
    (comp.databases.informix)
Loading