problem with select count(distinct ✗ Answered: red (solid confidence) — SELECT COUNT(DISTINCT ...) gives a different, wrong answer once a plain index exists on the column; a known IBM knowledge-base defect (fixed in UC5) is raised as a candidate explanation but is walked back as not quite matching (non-unique, non-composite index), and the thread ends without a working fix.

Advisory only.

Running 10.00.UC5I1 on linux With the following schema and static data i.e. I am the only user create table 'informix'.phone ( id SERIAL not null, phone CHAR(11) not null, batch_id INT not null ) fragment by round robin in data1,data2,data3 extent size 350000 next size 20000 lock mode row; The following code echo "select count(distinct phone) from phone" | dbaccess data produces Database selected. (count) 3862276 However - if I add the following create index 'informix'.ix101_2 on 'informix'.phone ( phone ); I get Database selected. (count) 4374605 FWIW 1) the first is the 'correct' answer i.e. 3862276 I unloaded the column from the table and ran through sort -u | wc -l 2) before you ask OTC, I HAVE tried UPDATE STATISTICS 3) This is entirely reproducible - drop index - answer 1 re-add index - answer 2 I'm going to try a dbexport and dbimport and will report back. Any comments gratefully received -- Clive

Clive Eisen wrote: > > I'm going to try a dbexport and dbimport and will report back. No change -- Clive

Clive Eisen wrote: > Clive Eisen wrote: > >> I'm going to try a dbexport and dbimport and will report back. > No change > Sorry to re-comment on my own comment but actually it's WORSE echo "select count(distinct phone) from phone" | dbaccess data_test Database selected. (count) 4348249 Ho hum -- Clive

Update statistics??? -----Mensaje original----- De: informix-list-bounces@iiug.org [mailto:informix-list-bounces@iiug.org] En nombre de Clive Eisen Enviado el: lunes, 27 de noviembre de 2006 14:24 Para: informix-list@iiug.org Asunto: Re: problem with select count(distinct Clive Eisen wrote: > Clive Eisen wrote: > >> I'm going to try a dbexport and dbimport and will report back. > No change > Sorry to re-comment on my own comment but actually it's WORSE echo "select count(distinct phone) from phone" | dbaccess data_test Database selected. (count) 4348249 Ho hum -- Clive _______________________________________________ Informix-list mailing list Informix-list@iiug.org http://www.iiug.org/mailman/listinfo/informix-list

what is 10.00.UC5I1 and where did it come from? Is this a fully supported version? Eval is usually E1, PID drops would be W1 and special builds are normally X1 so I am unsure what I1 is Does it reproduce on 10.00.UC5 If it does, or if 10.00.UC5I1 is a fully supported version then contact your support provider

Diego Morales wrote: > Update statistics???As I said in my first post - I have tried that -- Clive

scottishpoet wrote: > what is > > 10.00.UC5I1 > > and where did it come from? Is this a fully supported version? Eval is > usually E1, PID drops would be W1 and special builds are normally X1 so > I am unsure what I1 is > > Does it reproduce on 10.00.UC5 > > If it does, or if 10.00.UC5I1 is a fully supported version then > contact your support provider > It's the download from the IIUG :-) Just trying on 10.00.UC3R1TL - 90 day time bombed version from informix.com -- Clive

Searching the IDS online knowledge base (http://www-306.ibm.com/software/data/informix/ids/support/) finds there was a problem in UC4 which it says is fixed in UC5 http://www-1.ibm.com/support/docview.wss?rs=630&context=SSGU8G&context=SSHPYE&dc=DB520&dc=D600&dc=DB530&dc=D700&dc=DB500&dc=DB540&dc=DB510&dc=DB550&q1=COUNT&uid=swg21246388&loc=en_US&cs=utf-8&lang=en So I guess it depends what base UC5I1 came from There is a workaround in the article, does that resolve your problem?

maybe not the same problem as your index is not a composite

scottishpoet wrote: > maybe not the same problem as your index is not a composite > Quite - and in addition the data is not unique -- Clive

the index in that article was not unique either does the workaround suggested resolve your problem?

scottishpoet wrote: > the index in that article was not unique either > > does the workaround suggested resolve your problem? > Quote-- WORKAROUND Make the distinct column an index by itself. Example create index idx2 on table1(col1) --EndQuote My column is NOT distinct Also - way back in my first post I said 1) With such an index I get the WRONG answer 2) Without the index I get the correct answer Looks like the fix for the composite index problem broke my example I have no other index on the table -- Clive

Clive Eisen wrote: > scottishpoet wrote: >> the index in that article was not unique either >> >> does the workaround suggested resolve your problem? > Quote-- > WORKAROUND > > Make the distinct column an index by itself. > > Example > create index idx2 on table1(col1) > --EndQuote> > My column is NOT distinct I've just had another cup of coffee and re-read this Please ignore me regarding unique etc etc The rest of the post however is correct > > Also - way back in my first post I said > 1) With such an index I get the WRONG answer > 2) Without the index I get the correct answer > > Looks like the fix for the composite index problem broke my example > > I have no other index on the table > -- Clive