MAX Function !
I have a fragmented table. Here are the SQLs I tried on that table: SQL result ------------------------------------------------------------------ select max(f2) returns something. OK from table1 ------------------------------------------------------------------ select max(f2) from table1 returns nothing to the screen where f1 = 123 but says '1 row(s) retrieved' ------------------------------------------------------------------ select min(f2) from table1 returns something. OK where f1 = 123 ------------------------------------------------------------------ select min(f2),max(f2) from table1 returns something. OK where f1 = 123 ------------------------------------------------------------------ Table is fragmented on column f2 to 2 disks. f1 is part of the primary key. What is wrong? MAX functions works fine on non-fragmented tables. DB-Access Version 7.30.UC5 Informix Dynamic Server Version 7.30.UC5 IBM RISC/6000 model S/70 running AIX 4.3 (64 bit) == Olcay Sarioglu
In article <79cdj0$li1$1@news.xmission.com>, Olcay Sarioglu <olcay@knidos.cc.metu.edu.tr> writes > > > I have a fragmented table. Here are the SQLs I tried on that table: > >SQL result >------------------------------------------------------------------ >select max(f2) returns something. OK >from table1 >------------------------------------------------------------------ >select max(f2) from table1 returns nothing to the screen >where f1 = 123 but says '1 row(s) retrieved' >------------------------------------------------------------------ >select min(f2) from table1 returns something. OK >where f1 = 123 >------------------------------------------------------------------ >select min(f2),max(f2) from table1 returns something. OK >where f1 = 123 >------------------------------------------------------------------ > >Table is fragmented on column f2 to 2 disks. f1 is part of the primary >key. What is wrong? MAX functions works fine on non-fragmented tables. > Sounds like a bug, bugs still show up on fragmented tables.. >DB-Access Version 7.30.UC5 >Informix Dynamic Server Version 7.30.UC5 >IBM RISC/6000 model S/70 running AIX 4.3 (64 bit) > >== Olcay Sarioglu > > > > -- David Williams
Olcay Sarioglu wrote: > select max(f2) from table1 returns nothing to the screen > where f1 = 123 but says '1 row(s) retrieved' > ------------------------------------------------------------------ > select min(f2) from table1 returns something. OK > where f1 = 123 Olcay, I described exactly the same problem about 2 weeks ago in a posting "Incorrect results from aggregates in ESQL/C?". However, I didn't get any solution from this group. What actually happens is that the query for MAX returns a NULL when it definitely shouldn't. By the time I made the posting, I couldn't reproduce the problem in DBACCESS, it only showed up every once in a while in an ESQL/C program, but usually went away before I could get my hands on it. Yesterday, for the first time, I could catch it in DBACCESS and immediately made an urgent support call, but by the time they called me back it was gone again. I tried several different aggregates, and only MAX showed the problem for a certain WHERE condition. I made sure I was the only one using the database at that time, so a locking problem couldn't be the reason. Even if it was, there should be an error message and not just NULL returned. The WHERE conditions where the problem occurs are different every time, and without any apparent reason the query works ok again after a while. The table in question is NOT fragmented and only contains about 40,000 rows. Nothing that Informix should get excited about. My version is 7.30UC6. The support case is still open, and I will forward your posting to them. Regards, Richard -- +--------------------------+------------------------------------------+ | Dr. Richard Spitz | INTERNET: spitz@ana.med.uni-muenchen.de | | EDV-Gruppe Anaesthesie | Tel : +49-89-7095-6110 <-- NEW! | | Klinikum Grosshadern | FAX : +49-89-7095-8886 | | 81366 Munich, Germany | GSM : +49-172-8933578 | +--------------------------+------------------------------------------+
In article <79cdj0$li1$1@news.xmission.com>, Olcay Sarioglu <olcay@knidos.cc.metu.edu.tr> writes > > > I have a fragmented table. Here are the SQLs I tried on that table: > >SQL result >------------------------------------------------------------------ >select max(f2) returns something. OK >from table1 >------------------------------------------------------------------ >select max(f2) from table1 returns nothing to the screen >where f1 = 123 but says '1 row(s) retrieved' >------------------------------------------------------------------ >select min(f2) from table1 returns something. OK >where f1 = 123 >------------------------------------------------------------------ >select min(f2),max(f2) from table1 returns something. OK >where f1 = 123 >------------------------------------------------------------------ > Looks like an Online bug >Table is fragmented on column f2 to 2 disks. f1 is part of the primary >key. What is wrong? MAX functions works fine on non-fragmented tables. > >DB-Access Version 7.30.UC5 >Informix Dynamic Server Version 7.30.UC5 >IBM RISC/6000 model S/70 running AIX 4.3 (64 bit) > >== Olcay Sarioglu > > > > -- David Williams
Olcay Sarioglu wrote: > What is wrong? MAX functions works fine on non-fragmented tables. > > DB-Access Version 7.30.UC5 > Informix Dynamic Server Version 7.30.UC5 > IBM RISC/6000 model S/70 running AIX 4.3 (64 bit) I have the same problem. It is definitely a bug in Informix. Informix Norway claims the problem is fixed in version 7.30.UC7, but I haven't been able to check this yet. Karl A. Pedersen -- ___________________________________________________________ Karl A. Pedersen Consulent Compagniet AS - Et selskap i Antares Gruppen Storgt. 1, N-0155 Oslo, Norway, Dir. phone: +47 - 22 40 45 30 Mobile: +47 - 911 32 180, Fax : +47 - 22 42 97 77, http://www.antares.no ___________________________________________________________