Re: Empty String IS NOT NULL

Every wrong result is a bug.
Open an sr and let them fix it.
Regards

On Mon, May 25, 2020, 23:08 jaromir nemec <jaromir_at_db-nemec.com> 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
>
>
>

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