Aw: Empty String IS NOT NULL

Gesendet: Montag, 25. Mai 2020 um 23:06 Uhr
Von: "jaromir nemec" <jaromir_at_db-nemec.com>
An: oracle-l_at_freelists.org
Betreff: 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;

COUNT(*)
----------
2

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

Index created.

SQL> select count(*) from test;

COUNT(*)
----------
1

The execution plan provides the explanation, apparently the INDEX FULL
SCAN gets both of the rows, but somehow in the SORT AGGREGATE the NULL key
row is lost

Plan hash value: 2284640995

---------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
---------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 1 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | INDEX FULL SCAN| TEST_IDX | 2 | 1 (0)| 00:00:01 |
---------------------------------------------------------------------

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