Possible bug IDS 7.30.UC7 on HPUX (Repost with typos removed :()

My previous post was a little unclear, so here goes again. HPUX 10.20 IDS 7.30.UC7 I have a strange occurrence on my instance, SELECT FIRST x .... is giving incorrect results for values of x > approx. 560 See sample schema, SQL and output below. 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. The tables have a 1:1 relationship. This may or maynot be significant. Could you try to replicate this, preferably on existing tables which match the general schema ;- create table tab1(col1 char(10), col2 char(10)) create unique index itab1 on tab1(col1) create table tab2(col1 char(10), col2 char(40)) create unique index itab2 on tab2(col1) Populate with approx. 2000 rows in each table. update statistics select first 700 tab1.col1, tab2.col2 from tab1, tab2 where tab1.col2 = tab2.col1 order by tab2.col2 Actual 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_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 less 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 correctly and that the row for dlcus_customer = 4481 is duplicated as is dlcus_customer = 2504 dlcus_customer is a unique column. I have checked and there are no duplicates. EXPLAIN OUTPUT QUERY: (FIRST_ROWS OPTIMIZATION) ------ select first 714 dlcus_customer, ndm_name from dlcust, ndmas where dlcus_ndcode = ndm_ndcode order by ndm_name Estimated Cost: 3894 Estimated # of Rows Returned: 2299 Temporary Files Required For: Order By 1) informix.dlcust: SEQUENTIAL SCAN 2) informix.ndmas: INDEX PATH (1) Index Keys: ndm_ndcode Lower Index Filter: informix.ndmas.ndm_ndcode = informix.dlcust.dlcus_ndcode NESTED LOOP JOIN It would be really usefull if someone could reproduce this on any platform/version. -- --------------------------------------- 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 .

A simple way to try and duplicate this;- choose an existing table which has a unique index, a char column with >= 30 characters and > 1000 rows select * from your_table into temp fred select first 800 your_table.unique_column, fred.char_column where your_table.unique_column = fred.unique_column order by fred.char_column This gives the duplicated and mis-ordered rows error on my machine :o( -- --------------------------------------- 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 . Tony Flaherty wrote in message <8h2sh1$69q$1@hermes.mfs.misys.co.uk>... >My previous post was a little unclear, so here goes again. > >HPUX 10.20 >IDS 7.30.UC7 > >I have a strange occurrence on my instance, SELECT FIRST x .... >is giving incorrect results for values of x > approx. 560 > >See sample schema, SQL and output below. > >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. > >The tables have a 1:1 relationship. This may or maynot be significant. > >Could you try to replicate this, preferably on existing tables which match >the general schema ;- > >create table tab1(col1 char(10), col2 char(10))> >create unique index itab1 on tab1(col1)> >create table tab2(col1 char(10), col2 char(40))> >create unique index itab2 on tab2(col1)> > >Populate with approx. 2000 rows in each table. > >update statistics> >select first 700 tab1.col1, tab2.col2 > from tab1, tab2 >where tab1.col2 = tab2.col1 >order by tab2.col2> > > > >Actual 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_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 less 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 correctly and that the row for >dlcus_customer = 4481 is duplicated as is dlcus_customer = 2504 > >dlcus_customer is a unique column. I have checked and there are no >duplicates. > >EXPLAIN OUTPUT > >QUERY: (FIRST_ROWS OPTIMIZATION) > >------ >select first 714 dlcus_customer, ndm_name > from dlcust, > ndmas > where dlcus_ndcode = ndm_ndcode > order by ndm_name> >Estimated Cost: 3894 >Estimated # of Rows Returned: 2299 >Temporary Files Required For: Order By > >1) informix.dlcust: SEQUENTIAL SCAN > >2) informix.ndmas: INDEX PATH > > (1) Index Keys: ndm_ndcode > Lower Index Filter: informix.ndmas.ndm_ndcode = >informix.dlcust.dlcus_ndcode >NESTED LOOP JOIN > > > > >It would be really usefull if someone could reproduce this on any >platform/version. > >-- >--------------------------------------- >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 > >. > >

OK I just did this test and I do no get your results. It comes up clean in 7.31UC2. Have you run an oncheck on the two tables? Did you try beating two clean temp tables against each other? Have you considered upgrading? Art S. Kagel Tony Flaherty wrote: > > A simple way to try and duplicate this;- > > choose an existing table which has a unique index, a char column with >= 30 > characters and > 1000 rows > > select * > from your_table into temp fred> > select first 800 your_table.unique_column, fred.char_column > where your_table.unique_column = fred.unique_column > order by fred.char_column> > This gives the duplicated and mis-ordered rows error on my machine :o( > > -- > --------------------------------------- > 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 > > . > Tony Flaherty wrote in message <8h2sh1$69q$1@hermes.mfs.misys.co.uk>... > >My previous post was a little unclear, so here goes again. > > > >HPUX 10.20 > >IDS 7.30.UC7 > > > >I have a strange occurrence on my instance, SELECT FIRST x .... > >is giving incorrect results for values of x > approx. 560 > > > >See sample schema, SQL and output below. > > > >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. > > > >The tables have a 1:1 relationship. This may or maynot be significant. > > > >Could you try to replicate this, preferably on existing tables which match > >the general schema ;- > > > >create table tab1(col1 char(10), col2 char(10))> > > >create unique index itab1 on tab1(col1)> > > >create table tab2(col1 char(10), col2 char(40))> > > >create unique index itab2 on tab2(col1)> > > > > >Populate with approx. 2000 rows in each table. > > > >update statistics> > > >select first 700 tab1.col1, tab2.col2 > > from tab1, tab2 > >where tab1.col2 = tab2.col1 > >order by tab2.col2> > > > > > > > > >Actual 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_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 less 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 correctly and that the row for > >dlcus_customer = 4481 is duplicated as is dlcus_customer = 2504 > > > >dlcus_customer is a unique column. I have checked and there are no > >duplicates. > > > >EXPLAIN OUTPUT > > > >QUERY: (FIRST_ROWS OPTIMIZATION) > > > >------ > >select first 714 dlcus_customer, ndm_name > > from dlcust, > > ndmas > > where dlcus_ndcode = ndm_ndcode > > order by ndm_name> > > >Estimated Cost: 3894 > >Estimated # of Rows Returned: 2299 > >Temporary Files Required For: Order By > > >

I can easily reproduce this in both following two cases: Environment: Informix Dynamic Server Version 7.30.UC8 SunOS confidential_node_name 5.6 Generic_105181-16 sun4u sparc SUNW,UltraAX-MP Case 1: Permanent table t1 (real life table) with index on c1, no index on c2. Temp table t2 same schema as t1 no index at all. SQL: SELECT first 700 t1.c1, t1.c2 FROM t1, t2 WHERE t1.c1 = t2.c1 ORDER BY t1.c2 Case 2: Two temp tables without any index. I can easily notice that the result rows is in wrong order, but haven't got time to check duplicates. Tony Flaherty wrote in message <8h2sh1$69q$1@hermes.mfs.misys.co.uk>... >My previous post was a little unclear, so here goes again. > >HPUX 10.20 >IDS 7.30.UC7 > >I have a strange occurrence on my instance, SELECT FIRST x .... >is giving incorrect results for values of x > approx. 560 > >See sample schema, SQL and output below. > >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. > >The tables have a 1:1 relationship. This may or maynot be significant. > >Could you try to replicate this, preferably on existing tables which match >the general schema ;- > >create table tab1(col1 char(10), col2 char(10))> >create unique index itab1 on tab1(col1)> >create table tab2(col1 char(10), col2 char(40))> >create unique index itab2 on tab2(col1)> > >Populate with approx. 2000 rows in each table. > >update statistics> >select first 700 tab1.col1, tab2.col2 > from tab1, tab2 >where tab1.col2 = tab2.col1 >order by tab2.col2> > > > >Actual 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_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 less 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 correctly and that the row for >dlcus_customer = 4481 is duplicated as is dlcus_customer = 2504 > >dlcus_customer is a unique column. I have checked and there are no >duplicates. > >EXPLAIN OUTPUT > >QUERY: (FIRST_ROWS OPTIMIZATION) > >------ >select first 714 dlcus_customer, ndm_name > from dlcust, > ndmas > where dlcus_ndcode = ndm_ndcode > order by ndm_name> >Estimated Cost: 3894 >Estimated # of Rows Returned: 2299 >Temporary Files Required For: Order By > >1) informix.dlcust: SEQUENTIAL SCAN > >2) informix.ndmas: INDEX PATH > > (1) Index Keys: ndm_ndcode > Lower Index Filter: informix.ndmas.ndm_ndcode = >informix.dlcust.dlcus_ndcode >NESTED LOOP JOIN > > > > >It would be really usefull if someone could reproduce this on any >platform/version. > >-- >--------------------------------------- >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 > >. > >

I've checked for data corruption. I've run this on two new clean tables. I've had one response so far confirming the problem in 7.30.UD8 on SunOS. I have recently received 7.31.UC6 and will be loading this on my test box shortly... -- --------------------------------------- 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 . Art S. Kagel wrote in message <3935406C.1183FC33@bloomberg.net>... >OK I just did this test and I do no get your results. It comes up clean >in 7.31UC2. Have you run an oncheck on the two tables? Did you try beating >two clean temp tables against each other? Have you considered upgrading? > >Art S. Kagel > >Tony Flaherty wrote: >> >> A simple way to try and duplicate this;- >> >> choose an existing table which has a unique index, a char column with >= 30 >> characters and > 1000 rows >> >> select * >> from your_table into temp fred>> >> select first 800 your_table.unique_column, fred.char_column >> where your_table.unique_column = fred.unique_column >> order by fred.char_column>> >> This gives the duplicated and mis-ordered rows error on my machine :o( >> >> -- >> --------------------------------------- >> 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 >> >> . >> Tony Flaherty wrote in message <8h2sh1$69q$1@hermes.mfs.misys.co.uk>... >> >My previous post was a little unclear, so here goes again. >> > >> >HPUX 10.20 >> >IDS 7.30.UC7 >> > >> >I have a strange occurrence on my instance, SELECT FIRST x .... >> >is giving incorrect results for values of x > approx. 560 >> > >> >See sample schema, SQL and output below. >> > >> >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. >> > >> >The tables have a 1:1 relationship. This may or maynot be significant. >> > >> >Could you try to replicate this, preferably on existing tables which match >> >the general schema ;- >> > >> >create table tab1(col1 char(10), col2 char(10))>> > >> >create unique index itab1 on tab1(col1)>> > >> >create table tab2(col1 char(10), col2 char(40))>> > >> >create unique index itab2 on tab2(col1)>> > >> > >> >Populate with approx. 2000 rows in each table. >> > >> >update statistics>> > >> >select first 700 tab1.col1, tab2.col2 >> > from tab1, tab2 >> >where tab1.col2 = tab2.col1 >> >order by tab2.col2>> > >> > >> > >> > >> >Actual 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_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 less 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

This sounds like PTS 102340 (also reported independently as bug numbers 72163 and 72163). This is marked as fixed in version 7.32.UC1N8. Looks like there might also have been a patch for this in 7.31. I see a label "21105.hp.7.31.FC2" at the version where the fix first appears. - Kevin Tony Flaherty <aef@mfs.misys.co.uk> wrote in message news:8h2sh1$69q$1@hermes.mfs.misys.co.uk... > My previous post was a little unclear, so here goes again. > > HPUX 10.20 > IDS 7.30.UC7 > > I have a strange occurrence on my instance, SELECT FIRST x .... > is giving incorrect results for values of x > approx. 560 > > See sample schema, SQL and output below. > > 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. > > The tables have a 1:1 relationship. This may or maynot be significant. > > Could you try to replicate this, preferably on existing tables which match > the general schema ;- > > create table tab1(col1 char(10), col2 char(10))> > create unique index itab1 on tab1(col1)> > create table tab2(col1 char(10), col2 char(40))> > create unique index itab2 on tab2(col1)> > > Populate with approx. 2000 rows in each table. > > update statistics> > select first 700 tab1.col1, tab2.col2 > from tab1, tab2 > where tab1.col2 = tab2.col1 > order by tab2.col2> > > > > Actual 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_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 less 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 correctly and that the row for > dlcus_customer = 4481 is duplicated as is dlcus_customer = 2504 > > dlcus_customer is a unique column. I have checked and there are no > duplicates. > > EXPLAIN OUTPUT > > QUERY: (FIRST_ROWS OPTIMIZATION) > > ------ > select first 714 dlcus_customer, ndm_name > from dlcust, > ndmas > where dlcus_ndcode = ndm_ndcode > order by ndm_name> > Estimated Cost: 3894 > Estimated # of Rows Returned: 2299 > Temporary Files Required For: Order By > > 1) informix.dlcust: SEQUENTIAL SCAN > > 2) informix.ndmas: INDEX PATH > > (1) Index Keys: ndm_ndcode > Lower Index Filter: informix.ndmas.ndm_ndcode = > informix.dlcust.dlcus_ndcode > NESTED LOOP JOIN > > > > > It would be really usefull if someone could reproduce this on any > platform/version. > > -- > --------------------------------------- > 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 > > . > >

In article <MgHZ4.83269$55.684629@news1.sttls1.wa.home.com>, Kevin Beck <beckkl@home.com> writes >This sounds like PTS 102340 (also reported independently as bug numbers >72163 and 72163). > >This is marked as fixed in version 7.32.UC1N8. Looks like there might also >have been a patch for this in 7.31. I see a label "21105.hp.7.31.FC2" at >the version where the fix first appears. > > - Kevin > I tried this script to reproduce the problem:- echo ' drop database abc; create database abc; database abc; create table bob ( a char(30));' > /tmp/1 for i in 1 2 do for j in a b c d e f g h i j k l m n o p q r s t u v w x y z do for k in a b c d e f g h i j k l m n o p q r s t u v w x y z do echo "insert into bob values ('$i$j$k');" done done done >> /tmp/1 echo 'create unique index bob1 on bob(a); UPDATE STATISTICS MEDIUM FOR TABLE bob DISTRIBUTIONS ONLY; UPDATE STATISTICS HIGH FOR TABLE bob (a);' >> /tmp/1 echo ' database abc; select * from bob into temp fred; select first 800 bob.a, fred.a from bob,fred where bob.a = fred.a order by fred.a;' > /tmp/2 dbaccess < /tmp/1 dbaccess < /tmp/2 On 7.31.UC6 I get:- ... 1352 row(s) retrieved into temp table. a a 1aa 1aa 1ab 1ab 1ac 1ac 1ad 1ad 1ae 1ae 1af 1af 1ag 1ag 1ah 1ah 1ai 1ai 1aj 1aj 1ak 1ak 1al 1al 1am 1am 1an 1an 1ao 1ao 1ap 1ap 1aq 1aq 1ar 1ar 1as 1as 1at 1at 1au 1au 1av 1av 1aw 1aw 1ax 1ax 1ay 1ay 1az 1az 1ba 1ba 1bb 1bb 1bc 1bc 1bd 1bd 1be 1be 1bf 1bf 1bg 1bg 1bh 1bh 1bi 1bi 1bj 1bj 1bk 1bk 1bl 1bl 1bm 1bm 1bn 1bn ... Seems ok to me! -- David Williams

Ok, I tried this and it does not return line 1ap for some reason, everything else appears correct! This does not really replicate the conditions of my test/problem though, The column used to join the data should not be the same column that the SQL is ordered by. Anyone else had chance to test this? Tony Flaherty Snr. A/P Misys Financial Systems In article <tXEV6BAYI$N5EwAV@smooth1.demon.co.uk>, David Williams <djw@smooth1.demon.co.uk> wrote: > In article <MgHZ4.83269$55.684629@news1.sttls1.wa.home.com>, Kevin Beck > <beckkl@home.com> writes > >This sounds like PTS 102340 (also reported independently as bug numbers > >72163 and 72163). > > > >This is marked as fixed in version 7.32.UC1N8. Looks like there might also > >have been a patch for this in 7.31. I see a label "21105.hp.7.31.FC2" at > >the version where the fix first appears. > > > > - Kevin > > > > I tried this script to reproduce the problem:- > > echo ' > drop database abc; > create database abc; > database abc; > create table bob ( a char(30));' > /tmp/1> > for i in 1 2 > do > for j in a b c d e f g h i j k l m n o p q r s t u v w x y z > do > for k in a b c d e f g h i j k l m n o p q r s t u v w x y z > do > echo "insert into bob values ('$i$j$k');" > done > done > done >> /tmp/1 > > echo 'create unique index bob1 on bob(a); > UPDATE STATISTICS MEDIUM FOR TABLE bob DISTRIBUTIONS ONLY; > UPDATE STATISTICS HIGH FOR TABLE bob (a);' >> /tmp/1> > echo ' database abc; > select * from bob into temp fred; > select first 800 bob.a, fred.a > from bob,fred > where bob.a = fred.a > order by fred.a;' > /tmp/2> > dbaccess < /tmp/1 > dbaccess < /tmp/2 > > On 7.31.UC6 I get:- > > ... > > 1352 row(s) retrieved into temp table. > > a a > > 1aa 1aa > 1ab 1ab > 1ac 1ac > 1ad 1ad > 1ae 1ae > 1af 1af > 1ag 1ag > 1ah 1ah > 1ai 1ai > 1aj 1aj > 1ak 1ak > 1al 1al > 1am 1am > 1an 1an > 1ao 1ao > 1ap 1ap > 1aq 1aq > 1ar 1ar > 1as 1as > 1at 1at > 1au 1au > 1av 1av > 1aw 1aw > 1ax 1ax > 1ay 1ay > 1az 1az > 1ba 1ba > 1bb 1bb > 1bc 1bc > 1bd 1bd > 1be 1be > 1bf 1bf > 1bg 1bg > 1bh 1bh > 1bi 1bi > 1bj 1bj > 1bk 1bk > 1bl 1bl > 1bm 1bm > 1bn 1bn > ... > > Seems ok to me! > > -- > David Williams > Sent via Deja.com http://www.deja.com/ Before you buy.

In article <8hfpdf$k7s$1@nnrp1.deja.com>, brother58@my-deja.com writes > > >Ok, > >I tried this and it does not return line 1ap for some reason, >everything else appears correct! > >This does not really replicate the conditions of my test/problem >though, The column used to join the data should not be the same column >that the SQL is ordered by. > >Anyone else had chance to test this? > >Tony Flaherty >Snr. A/P >Misys Financial Systems > Can you change my test to show me what is required?? An extra column somewhere?? -- David Williams

Tony Flaherty wrote: > > My previous post was a little unclear, so here goes again. > > HPUX 10.20 > IDS 7.30.UC7 > > I have a strange occurrence on my instance, SELECT FIRST x .... > is giving incorrect results for values of x > approx. 560 > > See sample schema, SQL and output below. > > 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. > > The tables have a 1:1 relationship. This may or maynot be significant. > > Could you try to replicate this, preferably on existing tables which match > the general schema ;- > > create table tab1(col1 char(10), col2 char(10))> > create unique index itab1 on tab1(col1)> > create table tab2(col1 char(10), col2 char(40))> > create unique index itab2 on tab2(col1)> > Populate with approx. 2000 rows in each table. > > update statistics> > select first 700 tab1.col1, tab2.col2 > from tab1, tab2 > where tab1.col2 = tab2.col1 > order by tab2.col2> > Actual 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_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 less 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 correctly and that the row for > dlcus_customer = 4481 is duplicated as is dlcus_customer = 2504 > > dlcus_customer is a unique column. I have checked and there are no > duplicates. > > EXPLAIN OUTPUT > > QUERY: (FIRST_ROWS OPTIMIZATION) > > ------ > select first 714 dlcus_customer, ndm_name > from dlcust, > ndmas > where dlcus_ndcode = ndm_ndcode > order by ndm_name> > Estimated Cost: 3894 > Estimated # of Rows Returned: 2299 > Temporary Files Required For: Order By > > 1) informix.dlcust: SEQUENTIAL SCAN > > 2) informix.ndmas: INDEX PATH > > (1) Index Keys: ndm_ndcode > Lower Index Filter: informix.ndmas.ndm_ndcode = > informix.dlcust.dlcus_ndcode > NESTED LOOP JOIN > > It would be really usefull if someone could reproduce this on any > platform/version. > > -- > --------------------------------------- > 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 > > . Has anyone come to a conclusion concerning this issue? I've run a basic test and found nothing. . . . could be my test is wrong. -- John Carlson Informix DBA WHSmith USA #include std_disclaimer.h /* These are my opinions, not my company's opinion */