Possible bug IDS 7.30.UC7 on HPUX
IDS 7.30.UC7
I have a strange occurrence on my instance, SELECT x row1, row2 is giving
incorrect results for values of x > approx. 560
I have demonstrated this on two different machines (same versions) and
different tables.
I have run update statistics, both using Arts dostats utility and just a
simple UPDATE STATISTICS HIGH.
Informix have looked at this, I thought that in conversation with the
engineer he said he had reproduced it on a different version, this morning
however I received an email saying that the problem could not be reproduced
on any version.
Could anyone using this version please try to replicate this, preferably on
existing tables which match the general relationship laid out below.
Table schemas :-
DBSCHEMA Schema Utility INFORMIX-SQL Version 7.30.UC7
Copyright (C) Informix Software, Inc., 1984-1998
{ TABLE "informix".dlcust row size = 436 number of columns = 102 index size
= 171
}
create table "informix".dlcust
(
dlcus_customer char(10),
dlcus_ndcode char(10),
dlcus_limit float,
dlcus_balance float,
dlcus_dateopen date,
dlcus_lastpurch date,
dlcus_lastpay date,
dlcus_terms char(1),
dlcus_area char(4),
dlcus_rep char(4),
dlcus_stop char(1),
dlcus_group char(10),
dlcus_minordec char(1),
dlcus_codestr char(10),
dlcus_srrepname char(9),
dlcus_nodays smallint,
dlcus_condate date,
dlcus_report char(1),
dlcus_vatexempt char(1),
dlcus_sporder char(1),
dlcus_maxlines smallint,
dlcus_rateup char(1),
dlcus_currency char(4),
dlcus_vanarea char(4),
dlcus_loc char(5),
dlcus_class char(4),
dlcus_statement char(1),
dlcus_back char(1),
dlcus_partdel char(1),
dlcus_partinv char(1),
dlcus_acknow char(1),
dlcus_transpcod char(1),
dlcus_sepinvord char(1),
dlcus_comdel char(1),
dlcus_prospect char(1),
dlcus_plist char(1),
dlcus_cdisc char(1),
dlcus_spinvdel char(1),
dlcus_alphacode char(10),
dlcus_letgrp char(1),
dlcus_letlast char(1),
dlcus_letdate date,
dlcus_grpaccind char(1),
dlcus_lastcsh float,
dlcus_pdtran char(10),
dlcus_cdtran char(10),
dlcus_invform char(8),
dlcus_expform char(1),
dlcus_impform char(1),
dlcus_sprcode char(10),
dlcus_stdcode char(10),
dlcus_ordpri char(1),
dlcus_country char(2),
dlcus_txregno char(15),
dlcus_paymeth char(1),
dlcus_payee char(10),
dlcus_cvrate float,
dlcus_credcont char(4),
dlcus_commdates char(1),
dlcus_div char(4),
dlcus_packuse char(1),
dlcus_packdisc float,
dlcus_grptype char(1),
dlcus_mintpsord float,
dlcus_dlvnone char(1),
dlcus_exinv char(1),
dlcus_taxstat char(1),
dlcus_preferred char(10),
dlcus_edipo char(1),
dlcus_editran char(14),
dlcus_appacc char(10),
dlcus_inslim float,
dlcus_uninslim float,
dlcus_repgen char(1),
dlcus_authord char(1),
dlcus_obmaxanal smallint,
dlcus_obperiod char(1),
dlcus_obnumber smallint,
dlcus_obtopprod smallint,
dlcus_obseq char(1),
dlcus_soedibox char(20),
dlcus_code1 char(4),
dlcus_code2 char(4),
dlcus_code3 char(4),
dlcus_code4 char(4),
dlcus_code5 char(4),
dlcus_cidref char(10),
dlcus_cocreq char(1),
dlcus_cocchg char(1),
dlcus_pdate char(1),
dlcus_prodcat char(10),
dlcus_delterm char(3),
dlcus_sreel char(1),
dlcus_refreqd char(1),
dlcus_setnet char(1),
dlcus_prtype char(1),
dlcus_l1format char(9),
dlcus_l2format char(9),
dlcus_l3format char(9),
dlcus_lqformat char(2),
dlcus_overdue integer,
dlcus_pctype char(1)
);
revoke all on "informix".dlcust from "public";
create unique index "informix".i_dlcus_customer on "informix".dlcust
(dlcus_customer);
create index "informix".i_dlcus_ndcode on "informix".dlcust (dlcus_ndcode);
create unique index "informix".i_dlcus_repkey on "informix".dlcust
(dlcus_rep,dlcus_customer);
create unique index "informix".i_dlcus_areakey on "informix".dlcust
(dlcus_area,dlcus_customer);
create index "informix".i_dlcus_group on "informix".dlcust (dlcus_group);
create unique index "informix".i_dlcus_curkey on "informix".dlcust
(dlcus_currency,dlcus_customer);
create index "informix".i_dlcus_alphacode on "informix".dlcust
(dlcus_alphacode);
create unique index "informix".d_dlcus_customer on "informix".dlcust
(dlcus_customer desc);
{ TABLE "informix".ndmas row size = 268 number of columns = 13 index size =
78 }
create table "informix".ndmas
(
ndm_ndcode char(10),
ndm_name char(40),
ndm_addr1 char(30),
ndm_addr2 char(30),
ndm_addr3 char(30),
ndm_addr4 char(30),
ndm_addr5 char(30),
ndm_postcode char(10),
ndm_telephone char(20),
ndm_telex char(20),
ndm_lastdate date,
ndm_addtype char(10),
ndm_lasttime integer
);
revoke all on "informix".ndmas from "public";
create unique index "informix".i_ndm_ndcode on "informix".ndmas
(ndm_ndcode);
create index "informix".i_ndm_postcode on "informix".ndmas (ndm_postcode);
create index "informix".i_ndm_telephone on "informix".ndmas (ndm_telephone);
dlcust has 2298 rows
ndmas has 4246 rows
SQL statement
select first 714 dlcus_customer, ndm_name
from dlcust,
ndmas
where dlcus_ndcode = ndm_ndcode
order by ndm_name
If I order by an indexed column it works fine
If I select than about 560 rows it works fine
However this is the output from the above SQL
dlcus_customer ndm_name
4489 A 1 Insurance
4481 A 1 Insurance Ltd
4483 A 1 Insurance Services
4490 A 1 Insurance Services
4488 A 1 Insurance Services
4546 A 1 Insurance Services
3758 A 2 B Ins (Commercial) Ltd
2173 A A M Insurance Consultants
1885 A B A Insurance
4551 A B Brokers
2504 A B Insurance Centre
4481 A 1 Insurance Ltd
3496 A D M Insurance Cons Ltd
2866 A E A Insurance Services
341 A E Insurance Brokers Ltd
2255 A G Townsend
2756 A G Townsend
1632 A Hampton & Co
3176 A I M (Holdings) Ltd
1253 A I M Insurance Services Ltd
2504 A B Insurance Centre
365 A J Barter
4380 A Joyce & Son
1474 A Letton Percival & Co
2595 A M H Insurance Consultants
1662 A P N Insurance
Note that the data is not ordered corre
Unfortunately, I don't have 7.30 UC7. However, on v7.31.UC2/HP10.20, it works
fine.
All the same, I do recall a problem of a similar nature when I was working with
7.30 UC3 on Sun. We finally determined that it was to do with corrupt indexes.
You have demonstrated this problem on two different machines. This seems to
rule out index corruption, barring an enormous coincidence - unless you used
onunload/onload or backup/restore to create data on the other machine.
Maybe you could disable/enable your indexes and check whether the behaviour
changes.
Rudy
Tony Flaherty wrote:
> IDS 7.30.UC7
>
> I have a strange occurrence on my instance, SELECT x row1, row2 is giving
> incorrect results for values of x > approx. 560
>
> I have demonstrated this on two different machines (same versions) and
> different tables.
>
> ....
The data was restored from one machine to the other, however I have also
reproduced the problem using two different tables in a different database,
same instance.
Oncheck finds no problems......
--
---------------------------------------
Tony Flaherty aef@mfs.misys.co.uk
Analyst Programmer
Misys Financial Systems
All statements and opinions are my own,
Misys don't pay me enough to have opinions
on their behalf
.
Rudy Fernandes wrote in message <3933D25B.AF9A21E9@americasm01.nt.com>...
>Unfortunately, I don't have 7.30 UC7. However, on v7.31.UC2/HP10.20, it
works
>fine.
>
>All the same, I do recall a problem of a similar nature when I was working
with
>7.30 UC3 on Sun. We finally determined that it was to do with corrupt
indexes.
>
>You have demonstrated this problem on two different machines. This seems to
>rule out index corruption, barring an enormous coincidence - unless you
used
>onunload/onload or backup/restore to create data on the other machine.
>
>Maybe you could disable/enable your indexes and check whether the behaviour
>changes.
>
>Rudy
>
>
>Tony Flaherty wrote:
>
>> IDS 7.30.UC7
>>
>> I have a strange occurrence on my instance, SELECT x row1, row2 is giving
>> incorrect results for values of x > approx. 560
>>
>> I have demonstrated this on two different machines (same versions) and
>> different tables.
>>
>> ....
>
Tony, not being familiar with your data, I can't know for sure, but in
your customer table, you've got an index on dlcus_ndcode, but not a
unique index. I can only assume that there are duplicate values in this
column. (I assume you had a reason to not make this a unique index)
Therefore, if you join this column to ndmas, you're gonna get duplicate
rows.
The query :
select dlcus_ndcode, count(*) from dlcust
group by 1 having count(*) > 1 ;
would show you if there are duplicate values in this table.
I could be barking up the wrong tree, however.
select first 714 dlcus_customer, ndm_name
from dlcust,
ndmas
where dlcus_ndcode = ndm_ndcode
order by ndm_name
In article <8h031t$nnp$1@hermes.mfs.misys.co.uk>,
"Tony Flaherty" <aef@mfs.misys.co.uk> wrote:
> IDS 7.30.UC7
>
> I have a strange occurrence on my instance, SELECT x row1, row2 is
giving
> incorrect results for values of x > approx. 560
>
> I have demonstrated this on two different machines (same versions) and
> different tables.
>
> I have run update statistics, both using Arts dostats utility and just
a
> simple UPDATE STATISTICS HIGH.
>
> Informix have looked at this, I thought that in conversation with the
> engineer he said he had reproduced it on a different version, this
morning
> however I received an email saying that the problem could not be
reproduced
> on any version.
>
> Could anyone using this version please try to replicate this,
preferably on
> existing tables which match the general relationship laid out below.
>
> Table schemas :-
>
> DBSCHEMA Schema Utility INFORMIX-SQL Version 7.30.UC7
> Copyright (C) Informix Software, Inc., 1984-1998
> { TABLE "informix".dlcust row size = 436 number of columns = 102 index
size
> = 171
> }
> create table "informix".dlcust
> (
> dlcus_customer char(10),
> dlcus_ndcode char(10),
> dlcus_limit float,
> dlcus_balance float,
> dlcus_dateopen date,
> dlcus_lastpurch date,
> dlcus_lastpay date,
> dlcus_terms char(1),
> dlcus_area char(4),
> dlcus_rep char(4),
> dlcus_stop char(1),
> dlcus_group char(10),
> dlcus_minordec char(1),
> dlcus_codestr char(10),
> dlcus_srrepname char(9),
> dlcus_nodays smallint,
> dlcus_condate date,
> dlcus_report char(1),
> dlcus_vatexempt char(1),
> dlcus_sporder char(1),
> dlcus_maxlines smallint,
> dlcus_rateup char(1),
> dlcus_currency char(4),
> dlcus_vanarea char(4),
> dlcus_loc char(5),
> dlcus_class char(4),
> dlcus_statement char(1),
> dlcus_back char(1),
> dlcus_partdel char(1),
> dlcus_partinv char(1),
> dlcus_acknow char(1),
> dlcus_transpcod char(1),
> dlcus_sepinvord char(1),
> dlcus_comdel char(1),
> dlcus_prospect char(1),
> dlcus_plist char(1),
> dlcus_cdisc char(1),
> dlcus_spinvdel char(1),
> dlcus_alphacode char(10),
> dlcus_letgrp char(1),
> dlcus_letlast char(1),
> dlcus_letdate date,
> dlcus_grpaccind char(1),
> dlcus_lastcsh float,
> dlcus_pdtran char(10),
> dlcus_cdtran char(10),
> dlcus_invform char(8),
> dlcus_expform char(1),
> dlcus_impform char(1),
> dlcus_sprcode char(10),
> dlcus_stdcode char(10),
> dlcus_ordpri char(1),
> dlcus_country char(2),
> dlcus_txregno char(15),
> dlcus_paymeth char(1),
> dlcus_payee char(10),
> dlcus_cvrate float,
> dlcus_credcont char(4),
> dlcus_commdates char(1),
> dlcus_div char(4),
> dlcus_packuse char(1),
> dlcus_packdisc float,
> dlcus_grptype char(1),
> dlcus_mintpsord float,
> dlcus_dlvnone char(1),
> dlcus_exinv char(1),
> dlcus_taxstat char(1),
> dlcus_preferred char(10),
> dlcus_edipo char(1),
> dlcus_editran char(14),
> dlcus_appacc char(10),
> dlcus_inslim float,
> dlcus_uninslim float,
> dlcus_repgen char(1),
> dlcus_authord char(1),
> dlcus_obmaxanal smallint,
> dlcus_obperiod char(1),
> dlcus_obnumber smallint,
> dlcus_obtopprod smallint,
> dlcus_obseq char(1),
> dlcus_soedibox char(20),
> dlcus_code1 char(4),
> dlcus_code2 char(4),
> dlcus_code3 char(4),
> dlcus_code4 char(4),
> dlcus_code5 char(4),
> dlcus_cidref char(10),
> dlcus_cocreq char(1),
> dlcus_cocchg char(1),
> dlcus_pdate char(1),
> dlcus_prodcat char(10),
> dlcus_delterm char(3),
> dlcus_sreel char(1),
> dlcus_refreqd char(1),
> dlcus_setnet char(1),
> dlcus_prtype char(1),
> dlcus_l1format char(9),
> dlcus_l2format char(9),
> dlcus_l3format char(9),
> dlcus_lqformat char(2),
> dlcus_overdue integer,
> dlcus_pctype char(1)
> );
> revoke all on "informix".dlcust from "public";>
> create unique index "informix".i_dlcus_customer on "informix".dlcust
> (dlcus_customer);
> create index "informix".i_dlcus_ndcode on "informix".dlcust
(dlcus_ndcode);
>
> create unique index "informix".i_dlcus_repkey on "informix".dlcust
> (dlcus_rep,dlcus_customer);
> create unique index "informix".i_dlcus_areakey on "informix".dlcust
> (dlcus_area,dlcus_customer);
> create index "informix".i_dlcus_group on "informix".dlcust
(dlcus_group);
>
> create unique index "informix".i_dlcus_curkey on "informix".dlcust
> (dlcus_currency,dlcus_customer);
> create index "informix".i_dlcus_alphacode on "informix".dlcust
> (dlcus_alphacode);
> create unique index "informix".d_dlcus_customer on "informix".dlcust
> (dlcus_customer desc);
>
> { TABLE "informix".ndmas row size = 268 number of columns = 13 index
size =
> 78 }
> create table "informix".ndmas
> (
> ndm_ndcode char(10),
> ndm_name char(40),
> ndm_addr1 char(30),
> ndm_addr2 char(30),
> ndm_addr3 char(30),
> ndm_addr4 char(30),
> ndm_addr5 char(30),
> ndm_postcode char(10),
> ndm_telephone char(20),
> ndm_telex char(20),
> ndm_lastdate date,
> ndm_addtype char(10),
> ndm_lasttime integer
> );
> revoke all on "informix".ndmas from "public";>
> create unique index "informix".i_ndm_ndcode on "informix".ndmas
> (ndm_ndcode);
> create index "informix".i_ndm_postcode on "informix".ndmas
(ndm_postcode);
>
> create index "informix".i_ndm_telephone on "informix".ndmas
(ndm_telephone);
>
> dlcust has 2298 rows
> ndmas has 4246 rows
>
> SQL statement
>
> select first 714 dlcus_customer, ndm_name
> from dlcust,
> ndmas
> where dlcus_ndcode = ndm_ndcode>
The data is unique, it's a 1:1 relationship
between the two tables.
My normal news-server is down so it's time to try
deja :o/
Tony Flaherty
snr A/P
Misys Financial Systems
In article <8h6h8f$eed$1@nnrp1.deja.com>,
Curtis Bennett <die_kluge@hotmail.com> wrote:
> Tony, not being familiar with your data, I
can't know for sure, but in
> your customer table, you've got an index on
dlcus_ndcode, but not a
> unique index. I can only assume that there are
duplicate values in this
> column. (I assume you had a reason to not make
this a unique index)
>
> Therefore, if you join this column to ndmas,
you're gonna get duplicate
> rows.
>
> The query :
> select dlcus_ndcode, count(*) from dlcust
> group by 1 having count(*) > 1 ;>
> would show you if there are duplicate values in
this table.
>
> I could be barking up the wrong tree, however.
>
> select first 714 dlcus_customer, ndm_name
> from dlcust,
> ndmas
> where dlcus_ndcode = ndm_ndcode
> order by ndm_name>
> In article
<8h031t$nnp$1@hermes.mfs.misys.co.uk>,
> "Tony Flaherty" <aef@mfs.misys.co.uk> wrote:
> > IDS 7.30.UC7
> >
> > I have a strange occurrence on my instance,
SELECT x row1, row2 is
> giving
> > incorrect results for values of x > approx.
560
> >
> > I have demonstrated this on two different
machines (same versions) and
> > different tables.
> >
> > I have run update statistics, both using Arts
dostats utility and just
> a
> > simple UPDATE STATISTICS HIGH.
> >
> > Informix have looked at this, I thought that
in conversation with the
> > engineer he said he had reproduced it on a
different version, this
> morning
> > however I received an email saying that the
problem could not be
> reproduced
> > on any version.
> >
> > Could anyone using this version please try to
replicate this,
> preferably on
> > existing tables which match the general
relationship laid out below.
> >
> > Table schemas :-
> >
> > DBSCHEMA Schema Utility INFORMIX-SQL
Version 7.30.UC7
> > Copyright (C) Informix Software, Inc., 1984-
1998
> > { TABLE "informix".dlcust row size = 436
number of columns = 102 index
> size
> > = 171
> > }
> > create table "informix".dlcust
> > (
> > dlcus_customer char(10),
> > dlcus_ndcode char(10),
> > dlcus_limit float,
> > dlcus_balance float,
> > dlcus_dateopen date,
> > dlcus_lastpurch date,
> > dlcus_lastpay date,
> > dlcus_terms char(1),
> > dlcus_area char(4),
> > dlcus_rep char(4),
> > dlcus_stop char(1),
> > dlcus_group char(10),
> > dlcus_minordec char(1),
> > dlcus_codestr char(10),
> > dlcus_srrepname char(9),
> > dlcus_nodays smallint,
> > dlcus_condate date,
> > dlcus_report char(1),
> > dlcus_vatexempt char(1),
> > dlcus_sporder char(1),
> > dlcus_maxlines smallint,
> > dlcus_rateup char(1),
> > dlcus_currency char(4),
> > dlcus_vanarea char(4),
> > dlcus_loc char(5),
> > dlcus_class char(4),
> > dlcus_statement char(1),
> > dlcus_back char(1),
> > dlcus_partdel char(1),
> > dlcus_partinv char(1),
> > dlcus_acknow char(1),
> > dlcus_transpcod char(1),
> > dlcus_sepinvord char(1),
> > dlcus_comdel char(1),
> > dlcus_prospect char(1),
> > dlcus_plist char(1),
> > dlcus_cdisc char(1),
> > dlcus_spinvdel char(1),
> > dlcus_alphacode char(10),
> > dlcus_letgrp char(1),
> > dlcus_letlast char(1),
> > dlcus_letdate date,
> > dlcus_grpaccind char(1),
> > dlcus_lastcsh float,
> > dlcus_pdtran char(10),
> > dlcus_cdtran char(10),
> > dlcus_invform char(8),
> > dlcus_expform char(1),
> > dlcus_impform char(1),
> > dlcus_sprcode char(10),
> > dlcus_stdcode char(10),
> > dlcus_ordpri char(1),
> > dlcus_country char(2),
> > dlcus_txregno char(15),
> > dlcus_paymeth char(1),
> > dlcus_payee char(10),
> > dlcus_cvrate float,
> > dlcus_credcont char(4),
> > dlcus_commdates char(1),
> > dlcus_div char(4),
> > dlcus_packuse char(1),
> > dlcus_packdisc float,
> > dlcus_grptype char(1),
> > dlcus_mintpsord float,
> > dlcus_dlvnone char(1),
> > dlcus_exinv char(1),
> > dlcus_taxstat char(1),
> > dlcus_preferred char(10),
> > dlcus_edipo char(1),
> > dlcus_editran char(14),
> > dlcus_appacc char(10),
> > dlcus_inslim float,
> > dlcus_uninslim float,
> > dlcus_repgen char(1),
> > dlcus_authord char(1),
> > dlcus_obmaxanal smallint,
> > dlcus_obperiod char(1),
> > dlcus_obnumber smallint,
> > dlcus_obtopprod smallint,
> > dlcus_obseq char(1),
> > dlcus_soedibox char(20),
> > dlcus_code1 char(4),
> > dlcus_code2 char(4),
> > dlcus_code3 char(4),
> > dlcus_code4 char(4),
> > dlcus_code5 char(4),
> > dlcus_cidref char(10),
> > dlcus_cocreq char(1),
> > dlcus_cocchg char(1),
> > dlcus_pdate char(1),
> > dlcus_prodcat char(10),
> > dlcus_delterm char(3),
> > dlcus_sreel char(1),
> > dlcus_refreqd char(1),
> > dlcus_setnet char(1),
> > dlcus_prtype char(1),
> > dlcus_l1format char(9),
> > dlcus_l2format char(9),
> > dlcus_l3format char(9),
> > dlcus_lqformat char(2),
> > dlcus_overdue integer,
> > dlcus_pctype char(1)
> > );
> > revoke all on "informix".dlcust from "public";> >
> > create unique index
"informix".i_dlcus_customer on "informix".dlcust
> > (dlcus_customer);
> > create index "informix".i_dlcus_ndcode on
"informix".dlcust
> (dlcus_ndcode);
> >
> > create unique index "informix".i_dlcus_repkey
on "informix".dlcust
> > (dlcus_rep,dlcus_customer);
> > create unique index
"informix".i_dlcus_areakey on "informix".dlcust
> > (dlcus_area,dlcus_customer);
> > create index "informix".i_dlcus_group on
"informix".dlcust
> (dlcus_group);
> >
> > create unique index "informix".i_dlcus_curkey
on "informix".dlcust
> > (dlcus_currency,dlcus_customer);
> > create index "informix".i_dlcus_alphacode on
"informix".dlcust
> > (dlcus_alphacode);
> > create unique index
"informix".d_dlcus_customer on "informix".dlcust
> > (dlcus_customer desc);
> >
> > { TABLE "informix".ndmas row size = 268
number of columns = 13 index
> size =
> > 78 }
> > create table "informix".ndmas
> > (
> > ndm_ndcode char(10),
> > ndm_name char(40),
> > ndm_addr1 char(30),
> > ndm_addr2 char(30),
> > ndm_addr3 char(30),
> > ndm_addr4 char(30),
> > ndm_addr5 char(30),
> >