RE: Empty String IS NOT NULL

From: Mark W. Farnham <mwf_at_rsiz.com>
Date: Tue, 26 May 2020 16:24:18 -0400
Message-ID: <580801d6339b$a2ad1d50$e80757f0$_at_rsiz.com>

You wrote:

" in Oracle database the empty string equals to NULL"

This is NOT CORRECT.

In Oracle the empty string is considered to be NULL.

A NULL is not equal to an empty string.
One NULL is not even equal to another NULL.

This does NOT stop your example from being a correct illustration of a bug.

But I consider it my duty to point out when anyone transgresses on there being a value to NULL.

(not to be confused with the nomenclature of the C programming language, which is literally the reason why Oracle ended up considering empty strings to be NULL.)

-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of jaromir nemec
Sent: Monday, May 25, 2020 5:07 PM
To: oracle-l_at_freelists.org
Subject: 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




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

Received on Tue May 26 2020 - 22:24:18 CEST