It looks like you have encountered bug 161362 (see description below).
This bug appears to be fixed in the C8 version of 9.40. I would
suggest upgrading to this version.


161362 QUERY WITH EXISTS SUBQUERY RETURNS INCORRECT NUMBER OF ROWS WHEN
SUBQUERY FLATTENING IS TURNED ON



John Miller



Badii Alessandro wrote:
> I try this query on Solaris 2.8 with IDS 9.40.FC4 and this works correctly.
> When I try this on Linux Red Hat Linux 3.2.2-5 with IDS 9.40.UC6 this doesn't work.
>
>
> -----Messaggio originale-----
> Da: informix-list-bounces@iiug.org
> [mailto:informix-list-bounces@iiug.org]Per conto di
> matthias.butterweck@comline.de
> Inviato: martedì 28 febbraio 2006 10.09
> A: informix-list@iiug.org
> Oggetto: Problems using "EXISTS"
>
>
> Hello!
>
> We have a problem with one of our queries using the "EXISTS subquery"
> clause.
> On most servers the query returns the correct result, on other servers
> it doesn't.
> Below is a testcase.
> The query returns rows with id_folder = 2 and 3 on most servers, but on
> one server it only returns the row with id_folder =2.
> (This server's version is IBM Informix Dynamic Server Version
> 9.40.FC3XF)
> Anyone knows what might be going on here?
>
> Regards
> Matthias
>
> --
>
> Testcase:
>
> drop table folder ;
> drop table relation ;
> drop table item ;
>
> create table folder (
> id_folder integer
> ) ;
> create unique index folder_i1 on folder ( id_folder ) ;
>
> create table relation (
> id_parent integer,
> id_child integer
> ) ;
> create unique index relation_i1 on relation ( id_parent, id_child ) ;
>
> create table item (
> id_item integer,
> id_folder integer,
> key char(32),
> value varchar(128)
> ) ;
> create unique index item_i1 on item ( id_item ) ;
> create index item_i2 on item ( id_folder ) ;
> create index item_i3 on item ( key, value ) ;
>
> insert into folder( id_folder )
> values ( 1 ) ;
> insert into folder( id_folder )
> values ( 2 ) ;
> insert into folder( id_folder )
> values ( 3 ) ;
> insert into folder( id_folder )
> values ( 4 ) ;
> insert into folder( id_folder )
> values ( 5 ) ;
>
> insert into relation( id_parent, id_child )
> values ( 1, 2 ) ;
> insert into relation( id_parent, id_child )
> values ( 1, 3 ) ;
> insert into relation( id_parent, id_child )
> values ( 1, 4 ) ;
> insert into relation( id_parent, id_child )
> values ( 1, 5 ) ;
>
> insert into item( id_item, id_folder, key, value )
> values ( 100, 2, "Key1", "Value1" ) ;
> insert into item( id_item, id_folder, key, value )
> values ( 101, 3, "Key1", "Value1" ) ;
> insert into item( id_item, id_folder, key, value )
> values ( 102, 4, "Key2", "Value2" ) ;
> insert into item( id_item, id_folder, key, value )
> values ( 103, 5, "Key2", "Value2" ) ;
>
> select child.id_folder
> from folder child,
> folder parent,
> relation
> where parent.id_folder = 1
> and relation.id_parent = parent.id_folder
> and relation.id_child = child.id_folder
> and exists (
> select item.id_folder
> from item
> where item.id_folder = child.id_folder
> and item.key = "Key1"
> and item.value = "Value1"
> ) ;
>
> _______________________________________________
> Informix-list mailing list
> Informix-list@iiug.org
> http://www.iiug.org/mailman/listinfo/informix-list
>
>