SEO

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/

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.

Thanks you for replying!

So far the problem only occurs on one server with version 9.40.FC3XF.
The query runs ok on another server with the exact same version
9.40.FC3XF and on another one with version 9.21.FC4.

> Clearly, the query can readily be written without an EXISTS clause -
> and this may provide a temporary workaround.

Unfortunately it doesn't, because in our real life application there
may be very many item rows with key="Key1" and value="Value1", and we
only need the distinct child.id_folder values. If we add "DISTINCT" to
the select list, the query runs forever (well, almost).

Upon further investigation we found out that by setting the environment
variable NO_SUBQF, the problem goes away.
Looking at the explain plan of the query without NO_SUBQF we find

4) c0111.item: INDEX PATH (First Row)

Filters: (c0111.item.value = 'Value1' AND c0111.item.key = 'Key1'
)

We think that maybe there lies the problem (bug in the optimizer)

In conclusion the problem is solved somehow by setting NO_SUBQF, but we
have to do this now in all our application environments for all our
customers until the bug is fixed.

Kind regards
Matthias

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
>
>

> 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.

Thanks you, John. I appreciate the info.
I will inform our customers to upgrade to C8.

Matthias

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
>
>