Re: Empty String IS NOT NULL

From: Mladen Gogala <gogala.mladen_at_gmail.com>
Date: Mon, 25 May 2020 19:01:27 -0400
Message-ID: <f6e4b96a-b383-bcfc-06aa-e3b09936c276_at_gmail.com>

I can verify that the same "beispiel" works identically on 19.7:

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

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

Table created.

Elapsed: 00:00:00.323
SQL> select count(*) from test;

   COUNT(*)

---

          2

Elapsed: 00:00:00.235
SQL> create index test_idx on test (REF_ID,'');

Index created.

Elapsed: 00:00:00.031
SQL> select   count(*) from test;

   COUNT(*)

---

          1

Elapsed: 00:00:00.005
SQL> set autotrace on explain;
Autotrace Enabled
Displays the execution plan only.

SQL> select count(*) from test;

   COUNT(*)

---

          1

Explain Plan

---

PLAN_TABLE_OUTPUT

---

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 |
---------------------------------------------------------------------

Elapsed: 00:00:00.218

Vielen Dank for keeping us in the loop.

Regards

On 5/25/20 5:06 PM, jaromir nemec wrote:
> 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
>
>

-- 
Mladen Gogala
Database Consultant
Tel: (347) 321-1217


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

Received on Tue May 26 2020 - 01:01:27 CEST