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"
) ;