Empty String IS NOT NULL

It is very well known fact, that in Oracle database the empty string equals to NULL. To my surprise I encounter a contra-beispiel recently courtesy to a creative approach in indexing using a this index

create index test_idx on test (REF_ID,'');

See the example below

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.3.0.0.0

SQL> create table test as
  2 select 'x' ref_id from dual union all   3 select null from dual;

Table created.

SQL>
SQL>
SQL> select count(*) from test;

Index created.

SQL> select count(*) from test;

  COUNT(*)

Query Block Name / Object Alias (identified by operation id):

   1 - SEL$1
   2 - SEL$1 / TEST_at_SEL$1

Column Projection Information (identified by operation id):

   1 - (#keys=0) COUNT(*)[22]

This is tested in Oracle 19.0.0 on Windows 10, but same result was observed in 11.2.

We see that Oracle is confused, thinking the index can be safely used for counting all rows, but it is it not – only the not NULL rows are returned. So the question is, is this expected behaviour or is it a bug?

Kind Regards,

Jaromir D.B. Nemec

--
http://www.freelists.org/webpage/oracle-l