Outer Joins: stange behavior
Hi,
I ran a query across instances using an OUTER join using the old way of
doing OUTER joins (the informix way).
When the query is run locally, it works, when you run it from a
different instance, it gives different results.
It was tried using 11.50 and 11.70.
--------------------------------------------------
CREATE TABLE t1test ( libelle CHAR(20), code INTEGER ) LOCK MODE ROW;
CREATE TABLE t2test ( cle INTEGER, increment INTEGER, valeur CHAR(20) )
LOCK MODE ROW;
INSERT INTO t1test VALUES("Libelle 1",1);
INSERT INTO t1test VALUES("Libelle 2",2);
INSERT INTO t1test VALUES("Libelle 3",3);
INSERT INTO t2test VALUES(1,1,"1-1 VAL");
INSERT INTO t2test VALUES(1,2,"1-2 VAL");
INSERT INTO t2test VALUES(1,3,"1-3 VAL");
INSERT INTO t2test VALUES(3,1,"3-1 VAL");
-------------------------------------------------
SELECT A.libelle, B.valeur
FROM stores:t1test A, outer stores:t2test B
WHERE B.increment = ( SELECT MAX(increment) FROM stores:t2test WHERE cle= A.code ) AND A.code = B.cle
--------------------------------------------------
When run locally, you get the following result.
Libelle 1 1-3 VAL
Libelle 2
Libelle 3 3-1 VAL
When run from another instance, you get a wrong result; see below:
SELECT A.libelle, B.valeur
FROM stores@ol_informix1170:t1test A, outerstores@ol_informix1170:t2test B
WHERE B.increment = ( SELECT MAX(increment) FROMstores@ol_informix1170:t2test WHERE cle = A.code ) AND A.code = B.cle
libelle valeur
Libelle 1
Libelle 2
Libelle 3
Did anybody run across this problem?
Khaled Bentebal
Email: khaled.bentebal@consult-ix.fr
Site Web: www.consult-ix.fr
On Fri, May 27, 2011 at 05:25, Khaled Bentebal <
khaled.bentebal@consult-ix.fr> wrote:
> I ran a query across instances using an OUTER join using the old way of
> doing OUTER joins (the informix way).
>
> When the query is run locally, it works, when you run it from a
> different instance, it gives different results.
>
> It was tried using 11.50 and 11.70.
> --------------------------------------------------
>
> CREATE TABLE t1test ( libelle CHAR(20), code INTEGER ) LOCK MODE ROW;
> CREATE TABLE t2test ( cle INTEGER, increment INTEGER, valeur CHAR(20) )
> LOCK MODE ROW;
> INSERT INTO t1test VALUES("Libelle 1",1);
> INSERT INTO t1test VALUES("Libelle 2",2);
> INSERT INTO t1test VALUES("Libelle 3",3);
> INSERT INTO t2test VALUES(1,1,"1-1 VAL");
> INSERT INTO t2test VALUES(1,2,"1-2 VAL");
> INSERT INTO t2test VALUES(1,3,"1-3 VAL");
> INSERT INTO t2test VALUES(3,1,"3-1 VAL");>
> -------------------------------------------------
> SELECT A.libelle, B.valeur
> FROM stores:t1test A, outer stores:t2test B
> WHERE B.increment = ( SELECT MAX(increment) FROM stores:t2test WHERE cle> = A.code ) AND A.code = B.cle
> --------------------------------------------------
> When run locally, you get the following result.
>
> Libelle 1 1-3 VAL
> Libelle 2
> Libelle 3 3-1 VAL
>
> When run from another instance, you get a wrong result; see below:
>
[...typo fixed 'outerstores' --> 'outer stores'...]
> SELECT A.libelle, B.valeur
> FROM stores@ol_informix1170:t1test A, outer stores@ol_informix1170:t2test> B
> WHERE B.increment = ( SELECT MAX(increment) FROM
> stores@ol_informix1170:t2test WHERE cle = A.code ) AND A.code = B.cle
>
> libelle valeur
> Libelle 1
> Libelle 2
> Libelle 3
>
> Did anybody run across this problem?
>
Please report the issue to IBM/Informix Tech Support.
I don't see any reason why simply changing the location where data is stored
should affect the results returned. The speed at which they are returned
can be affected; without an ORDER BY, the order in which they're returned
can be affected; but the set of values returned should not be affected.
--
Jonathan Leffler <jonathan.leffler@gmail.com> #include <disclaimer.h>
Guardian of DBD::Informix - v2008.0513 - http://dbi.perl.org
"Blessed are we who can laugh at ourselves, for we shall never cease to be
amused."
--20cf30564455f4d12c04a4428982
That was just a typo.
This is just a small example, since the main SELECT is more complicated
than that. The main point is not the performance in this example but to
understand why is not working where it did work in V7.31/
We are going to report it to support. Our client is having problems
migrating this application to using IDS 11.50
Khaled Bentebal
Email: khaled.bentebal@consult-ix.fr
Site Web: www.consult-ix.fr
Le 27/05/11 16:13, Jonathan Leffler a écrit :
> On Fri, May 27, 2011 at 05:25, Khaled Bentebal<
> khaled.bentebal@consult-ix.fr> wrote:
>
>> I ran a query across instances using an OUTER join using the old way of
>> doing OUTER joins (the informix way).
>>
>> When the query is run locally, it works, when you run it from a
>> different instance, it gives different results.
>>
>> It was tried using 11.50 and 11.70.
>> --------------------------------------------------
>>
>> CREATE TABLE t1test ( libelle CHAR(20), code INTEGER ) LOCK MODE ROW;
>> CREATE TABLE t2test ( cle INTEGER, increment INTEGER, valeur CHAR(20) )
>> LOCK MODE ROW;
>> INSERT INTO t1test VALUES("Libelle 1",1);
>> INSERT INTO t1test VALUES("Libelle 2",2);
>> INSERT INTO t1test VALUES("Libelle 3",3);
>> INSERT INTO t2test VALUES(1,1,"1-1 VAL");
>> INSERT INTO t2test VALUES(1,2,"1-2 VAL");
>> INSERT INTO t2test VALUES(1,3,"1-3 VAL");
>> INSERT INTO t2test VALUES(3,1,"3-1 VAL");>>
>> -------------------------------------------------
>> SELECT A.libelle, B.valeur
>> FROM stores:t1test A, outer stores:t2test B
>> WHERE B.increment = ( SELECT MAX(increment) FROM stores:t2test WHERE cle>> = A.code ) AND A.code = B.cle
>> --------------------------------------------------
>> When run locally, you get the following result.
>>
>> Libelle 1 1-3 VAL
>> Libelle 2
>> Libelle 3 3-1 VAL
>>
>> When run from another instance, you get a wrong result; see below:
>>
> [...typo fixed 'outerstores' --> 'outer stores'...]
>
>> SELECT A.libelle, B.valeur
>> FROM stores@ol_informix1170:t1test A, outer stores@ol_informix1170:t2test>> B
>> WHERE B.increment = ( SELECT MAX(increment) FROM
>> stores@ol_informix1170:t2test WHERE cle = A.code ) AND A.code = B.cle
>>
>> libelle valeur
>> Libelle 1
>> Libelle 2
>> Libelle 3
>>
>> Did anybody run across this problem?
>>
> Please report the issue to IBM/Informix Tech Support.
>
> I don't see any reason why simply changing the location where data is stored
> should affect the results returned. The speed at which they are returned
> can be affected; without an ORDER BY, the order in which they're returned
> can be affected; but the set of values returned should not be affected.
>
I believe you have run into a known defect which is fixed. Please cont=
act
support and ask about IC69815.
John F. Miller III
STSM, Embedability Architect
miller3@us.ibm.com
503-578-5645
IBM Informix Dynamic Server (IDS)
ids-bounces@iiug.org wrote on 05/27/2011 09:43:58 AM:
> [image removed]
>
> Re: Outer Joins: stange behavior [23855]
>
> Khaled Bentebal
>
> to:
>
> ids
>
> 05/27/2011 09:47 AM
>
> Sent by:
>
> ids-bounces@iiug.org
>
> Please respond to ids
>
> That was just a typo.
>
> This is just a small example, since the main SELECT is more complicat=
ed
> than that. The main point is not the performance in this example but =
to
> understand why is not working where it did work in V7.31/
>
> We are going to report it to support. Our client is having problems
> migrating this application to using IDS 11.50
>
> Khaled Bentebal
>
> Email: khaled.bentebal@consult-ix.fr
> Site Web: www.consult-ix.fr
>
> Le 27/05/11 16:13, Jonathan Leffler a =E9crit :
> > On Fri, May 27, 2011 at 05:25, Khaled Bentebal<
> > khaled.bentebal@consult-ix.fr> wrote:
> >
> >> I ran a query across instances using an OUTER join using the old w=
ay
of
> >> doing OUTER joins (the informix way).
> >>
> >> When the query is run locally, it works, when you run it from a
> >> different instance, it gives different results.
> >>
> >> It was tried using 11.50 and 11.70.
> >> --------------------------------------------------
> >>
> >> CREATE TABLE t1test ( libelle CHAR(20), code INTEGER ) LOCK MODE R=OW;
> >> CREATE TABLE t2test ( cle INTEGER, increment INTEGER, valeur CHAR
(20) )
> >> LOCK MODE ROW;
> >> INSERT INTO t1test VALUES("Libelle 1",1);
> >> INSERT INTO t1test VALUES("Libelle 2",2);
> >> INSERT INTO t1test VALUES("Libelle 3",3);
> >> INSERT INTO t2test VALUES(1,1,"1-1 VAL");
> >> INSERT INTO t2test VALUES(1,2,"1-2 VAL");
> >> INSERT INTO t2test VALUES(1,3,"1-3 VAL");
> >> INSERT INTO t2test VALUES(3,1,"3-1 VAL");> >>
> >> -------------------------------------------------
> >> SELECT A.libelle, B.valeur
> >> FROM stores:t1test A, outer stores:t2test B
> >> WHERE B.increment =3D ( SELECT MAX(increment) FROM stores:t2test W=HERE
cle
> >> =3D A.code ) AND A.code =3D B.cle
> >> --------------------------------------------------
> >> When run locally, you get the following result.
> >>
> >> Libelle 1 1-3 VAL
> >> Libelle 2
> >> Libelle 3 3-1 VAL
> >>
> >> When run from another instance, you get a wrong result; see below:=
> >>
> > [...typo fixed 'outerstores' --> 'outer stores'...]
> >
> >> SELECT A.libelle, B.valeur
> >> FROM stores@ol_informix1170:t1test A, outerstores@ol_informix1170:t2test
> >> B
> >> WHERE B.increment =3D ( SELECT MAX(increment) FROM
> >> stores@ol_informix1170:t2test WHERE cle =3D A.code ) AND A.code =3D=
B.cle
> >>
> >> libelle valeur
> >> Libelle 1
> >> Libelle 2
> >> Libelle 3
> >>
> >> Did anybody run across this problem?
> >>
> > Please report the issue to IBM/Informix Tech Support.
> >
> > I don't see any reason why simply changing the location where datai=
s
stored
> > should affect the results returned. The speed at which they are
returned
> > can be affected; without an ORDER BY, the order in which they're
returned
> > can be affected; but the set of values returned should not be affec=
ted.
> >
>
>
>
***********************************************************************=
********
> Forum Note: Use "Reply" to post a response in the discussion forum.=
>=