Strange execution between instances using an OUTER
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, *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*
--
Khaled Bentebal
Email: khaled.bentebal@consult-ix.fr
Site Web: www.consult-ix.fr
More info using LEFT OUTER JOIN:
SELECT A.libelle, B.valeur
FROM stores@ol_informix1170:t1test A
LEFT OUTER JOIN stores@ol_informix1170:t2test B ON A.code = B.cle
--FROM stores@ol_informix1170:t1test A, outerstores@ol_informix1170:t2test B
WHERE B.increment =
(
SELECT MAX(increment) FROM stores@ol_informix1170:t2test
WHERE cle = A.code
)
Then you get:
libelle valeur
Libelle 1 1-3 VAL
Libelle 3 3-1 VAL
Which is NOT totally correct.
Has anybody come across this.
Khaled Bentebal
Email: khaled.bentebal@consult-ix.fr
Site Web: www.consult-ix.fr
Le 25/05/11 01:05, Khaled Bentebal a écrit :
> 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, *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*
>