R: Problems using "EXISTS"

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

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. The correct answer does not change between versions of the software; different answers means one of the versions has a bug in it. Offhand, I don't know which is correct, and whether there is a known defect that accounts for the trouble, and if there is a known defect, whether there is a later version in which it is fixed. It would be helpful if Matthias could let us know which server versions work and which do not. Contact IBM/Informix Tech Support. > -----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 > > -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2005.02 -- http://dbi.perl.org/