Re: ANSI full outer join - B175078 (9.40 only; not in
Dear Peter,
I've confirmed that this bug exists in IDS 9.40.UC7 on Solaris 8 as well
as you recording it for RedHat Linux; I also confirmed that it is not
present in IDS 10.00.UC3 - I've not verified UC1 or UC2 either way.
B175078: FULL OUTER JOIN RETURNS INCORRECT RESULTS WHEN LHS TABLE IS
EMPTY.
--
Jonathan Leffler (jleffler@us.ibm.com)
STSM, Informix Database Engineering, IBM Information Management Division
4100 Bohannon Drive, Menlo Park, CA 94025
Tel: +1 650-926-6921 Tie-Line: 630-6921
"I don't suffer from insanity; I enjoy every minute of it!"
---------- Forwarded message ----------
From: Jonathan Le.... <jleffler.iiug@gmail.com>
Date: Nov 15, 2005 10:48 PM
Subject: Re: ASNI full outer join [6006]
To: ids@iiug.org, forum.subscriber@iiug.org
On 11/15/05, Peter J Dia.... < pdiazdeleon@infinityhealthcare.com> wrote:
>
> I am playing with the ANSI Full out join syntax using IDS 9.40.UC7 on RH
> linux.
> However, I don't know if I am reading the manual correctly. The manual
> states:
>
> From the IBM Informix manuals
>
> ANSI FULL OUTER Joins
> The FULL keyword specifies a join in which the result set includes all
> the rows from the Cartesian product for which the join condition is
> true, plus all the rows from each table that do not match the join
> condition.
>
>
> So I created my test example:
>
> create temp table odd
> (
> invoice integer,
> value char(1)
> ) with no log;>
> create temp table even
> (
> invoice integer,
> value char(1)
> ) with no log;>
> insert into odd values (1, "A");
> insert into odd values (3, "B");
> insert into odd values (5, "C");
> insert into odd values (7, "D");>
> insert into even values (2, "A");
> insert into even values (4, "B");
> insert into even values (6, "C");
> insert into even values (8, "D");>
> I execute the following query:
> select e.*, o.* from even e full outer join odd o on e.invoice =
o.invoice
> ;
>
> The result is:
> invoice value invoice value
>
> 2 A
> 4 B
> 6 C
> 8 D
> 1 A
> 3 B
> 5 C
> 7 D
>
> Which is correct.
Agreed.
However, if I delete all rows in table "even", I don't get the rows from
> table odd?
> Is correct functionality?
No. You should get all the rows from 'odd'.
From the above definition I would say no
> since the definition says "all rows that do not match the join
condition"
>
> Any clarification would be great.
Please contact IBM/Informix Tech Support.
--
Jonathan Leffler #include <disclaimer.h>
Email: jleffler@earthlink.net, jleffler@us.ibm.com
Guardian of DBD::Informix v2005.02 -- http://dbi.perl.org/
--
Jonathan Leffler #include <disclaimer.h>
Email: jleffler@earthlink.net, jleffler@us.ibm.com
Guardian of DBD::Informix v2005.02 -- http://dbi.perl.org/