Jonathan Leffler wrote:
> 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.
I've not been able to reproduce the problem on Solaris (9.30.UC6,
9.40.UC7, 10.00.UC3), but it is clear from the information given that
it is rather version-specific and I've not got the exact versions
referenced.
> > -----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"
> > ) ;
Clearly, the query can readily be written without an EXISTS clause -
and this may provide a temporary workaround. Equally, I am in no sense
claiming that the deviant behaviour is not a bug.
select child.id_folder
from folder child,
folder parent,
relation, item
where parent.id_folder = 1
and relation.id_parent = parent.id_folder
and relation.id_child = child.id_folder
and item.id_folder = child.id_folder
and item.key = "Key1"
and item.value = "Value1"
;
This produces the correct answer (2, 3) on all the platforms I tested
(but then, so did the original, so it doesn't help much).
Matthias - thank you for taking the time to put together the test case.
It really helps.