Re: Empty String IS NOT NULL

Use bitmap indexes! :-)

SQL> create table t(n) as select 0 from dual;

Tabela criada.

SQL> select count(*) from t;

  COUNT(*)

         1

SQL> create bitmap index ti on t('');

Índice criado.

SQL> select count(*) from t;

  COUNT(*)

         1

Em ter., 26 de mai. de 2020 às 02:25, <rogel_at_web.de> escreveu:

> good one.
>
> It goes even simpler:
>
> *SQL> create table t(n) as select 0 from dual;*
> *Table created.*
> *SQL> select count(*) from t;*
>
>
> * COUNT(*) ---------- 1*
> *SQL> create index ti on t('');*
> *Index created.*
> *SQL> select count(*) from t;*
>
>
> * COUNT(*) ---------- 0*
>
>
> *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
>
>
> -- http://www.freelists.org/webpage/oracle-l

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