ORACLE 8.1.5.i : Strange problem with SELECT COUNT(*) WHERE ... IS NULL

From: Genoud <tgenoud_at_cisel.ch>
Date: Wed, 22 Sep 1999 09:05:53 +0200
Message-ID: <7s9v4i$ouf$1@pollux.ip-plus.net>

Hello,

on Oracle 8.1.5i, the query :
Select count(*) from t_transfert where etat is null; gives 0.
It's not correct... there's a lot of rows where etat is null !!!!!!!!!

The query :
Select etat,id_pm from t_transfert where etat is null; gives the correct result...

There an index on this table:
CREATE INDEX IX_ETAT_PM ON T_TRANSFERT (ETAT, ID_PM) TABLESPACE TS_IX_TRANSFERT; If I drop the index, then the 'select count(*)' gives the correct result !! Why the creation of this simple index affects the queries ??

Can someone help me ?
Is it a bug in Oracle 8.1.5i ?
Thanks..

Here's a paste of a SQL*plus session showing that ...

SQL*Plus: Release 3.3.4.0.0 - Production on Wed Sep 22 08:52:37 1999

Connected to:
Oracle8i Enterprise Edition Release 8.1.5.0.0 - Production With the Partitioning and Java options
PL/SQL Release 8.1.5.0.0 - Production

SQL> alter session set nls_date_format = 'dd.mm.yyyy hh24:mi:ss';

Session altered.

SQL> desc t_transfert

 Name                            Null?    Type
 ------------------------------- -------- ----
 ETAT                                     VARCHAR2(10)
 ID_PM                           NOT NULL VARCHAR2(40)
 DATE_VAL                        NOT NULL DATE
 DATE_SEQ                        NOT NULL NUMBER(38)
 CD_SAISON                                VARCHAR2(2)
 DATE_VAL_FIN                             DATE
 CD_SAISON_FIN                            VARCHAR2(2)
 GENRE_VAL                                CHAR(1)
 TYPE_VAL                                 CHAR(1)
 TYPE_INTRO                               CHAR(1)
 VALIDITE                                 NUMBER
 VAL                                      NUMBER
 VAL_ALPHA                                VARCHAR2(120)
 STATUS                                   VARCHAR2(10)
 SYST_ORIG                       NOT NULL VARCHAR2(20)
 TEXT_ERREUR                              VARCHAR2(200)
 DATE_DERN_ETAT                  NOT NULL DATE

SQL> select count(*) from t_transfert where etat is null;

COUNT(*)

SQL> select etat,date_dern_etat from t_transfert where etat is null;

ETAT DATE_DERN_ETAT
---------- -------------------

           21.09.1999 15:20:20
           21.09.1999 15:19:51
           21.09.1999 15:19:28
           21.09.1999 15:19:09
           21.09.1999 15:18:33
           21.09.1999 15:18:10
           21.09.1999 15:17:36
           21.09.1999 15:17:16
           21.09.1999 15:16:47
           21.09.1999 15:16:32
           21.09.1999 15:15:50
           21.09.1999 15:15:28
           21.09.1999 15:14:59
           21.09.1999 15:14:30
           21.09.1999 15:14:04
           21.09.1999 15:13:39
           21.09.1999 15:13:07
           21.09.1999 15:12:37
           21.09.1999 15:12:00
           21.09.1999 15:11:20
           21.09.1999 15:10:39

ETAT       DATE_DERN_ETAT


---------- -------------------


           21.09.1999 15:10:07
           21.09.1999 15:09:35
           21.09.1999 15:09:04
           21.09.1999 15:08:46
           21.09.1999 15:07:31
           21.09.1999 15:07:04
           21.09.1999 15:06:39
           21.09.1999 15:06:05
           21.09.1999 15:05:38
           21.09.1999 15:05:16
           21.09.1999 15:04:45

etc..... Received on Wed Sep 22 1999 - 02:05:53 CDT