RE: Empty String IS NOT NULL

From: Clay Jackson (cjackson) <"Clay>
Date: Tue, 26 May 2020 15:11:59 +0000
Message-ID: <MWHPR19MB0141F79B991F545F070E30419BB00_at_MWHPR19MB0141.namprd19.prod.outlook.com>

OK – looks like this is an “edge case”, IMHO, a “bug”; but……

Note carefully the create index:
create index test_idx on test(ref_id,'');

                                                                ^

That’s a NULL being added to the end of the index.

As the joke goes:
Patient – “It hurts when I do this” Doctor – “OK, don’t do that. Next”

Here are my test results on 19.3.0.0

SQL> select count(*) from test;

  COUNT(*)

---

                2

SQL> create index test_idx on test(ref_id);

Index created.

SQL> select count(*) from test;

  COUNT(*)

---

                2

SQL> drop index test_idx;

Index dropped.

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

Index created.

SQL> select count(*) from test;

  COUNT(*)

---

                1

SQL> drop index test_idx;

Index dropped.

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

Index created.

SQL> select count(*) from test;

  COUNT(*)

---

                2

SQL> ^
Clay Jackson
Database Solutions Sales Engineer clay.jackson_at_quest.com<mailto:clay.jackson_at_quest.com> office 949-754-1203 mobile 425-802-9603 [cid:image001.jpg_at_01D63335.4B0A0740]

From: oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> On Behalf Of Luis Claudio Dias dos Santos Sent: Tuesday, May 26, 2020 6:51 AM
To: Norman Dunbar <oracle_at_dunbar-it.co.uk> Cc: Luis Santos <lsantos_at_pobox.com>; rogel_at_web.de; jaromir_at_db-nemec.com; ORACLE-L <oracle-l_at_freelists.org> Subject: Re: Empty String IS NOT NULL

CAUTION: This email originated from outside of the organization. Do not follow guidance, click links, or open attachments unless you recognize the sender and know the content is safe.

Yes, it's a joke. As bitmap indexes stores NULL values (instead of B+ indexes, which don't) they not suffer this bug.

Em ter., 26 de mai. de 2020 às 09:25, Norman Dunbar <oracle_at_dunbar-it.co.uk<mailto:oracle_at_dunbar-it.co.uk>> escreveu: On 26/05/2020 12:40, Luis Claudio Dias dos Santos wrote:
> Use bitmap indexes! :-)

No, please don't! At least, not in an OLTP or frequently updated system. Deadlocks will occur! (Ask me how I know!)

(I did notice the smiley by the way Luis, but some may not have.)

Cheers,
Norm.

--
Norman Dunbar
Dunbar IT Consultants Ltd

Registered address:
27a Lidget Hill
Pudsey
West Yorkshire
United Kingdom
LS28 7LG

Company Number: 05132767

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






(image/jpeg attachment: image001.jpg)

Received on Tue May 26 2020 - 17:11:59 CEST