[PHOENIX-6583] Inserting explicit Null into a (fixed length) binary field is stored as an array of zeroes

• Type: Bug
• Status: Resolved
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 5.1.2
• Fix Version/s: 4.17.0, 5.2.0, 4.16.2, 5.1.3
• Component/s: core
• Assignee: Alejandro Anadon
• Reporter: Alejandro Anadon
• Created: 27/Oct/21 20:20
• Updated: 10/Nov/21 10:22
• Resolved: 10/Nov/21 10:06

Description

In binary fields, if I use upsert with null value, it "insert" the field with 0x00 0x00 bytes vaules.

Example:

create table DUMMYTABLE (id integer not null, text varchar(255), testbin binary(16), CONSTRAINT pk primary key (id));

1)

upsert into DUMMYTABLE (id,text) values (1,'null in testBinary');

select id,text,LENGTH(TEXT),TESTBIN,OCTET_LENGTH(TESTBIN) from DUMMYTABLE;

result--> TESTBIN => null, OCTET_LENGTH(TESTBIN) => null  . that's ok.

2)

upsert into DUMMYTABLE (id,text,testbin) values (2,'IT DO NOT null in testBinary',null);

select id,text,LENGTH(TEXT),TESTBIN,OCTET_LENGTH(TESTBIN) from DUMMYTABLE;

result in seconf row-> TESTBIN => NOT NULL, OCTET_LENGTH(TESTBIN) => 16  . that's an error.

 

I verified it in hbase and actually reserves empty data (it should not):

hbase(main):001:0> scan 'DUMMYTABLE'
ROW COLUMN+CELL
\x80\x00\x00\x01 column=0:\x00\x00\x00\x00, timestamp=1635365632081, value=x
\x80\x00\x00\x01 column=0:\x80\x0B, timestamp=1635365632081, value=null in testBinary
\x80\x00\x00\x02 column=0:\x00\x00\x00\x00, timestamp=1635365696139, value=x
\x80\x00\x00\x02 column=0:\x80\x0B, timestamp=1635365696139, value=IT DO NOT null in testBinary
\x80\x00\x00\x02 column=0:\x80\x0C, timestamp=1635365696139, value=\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00
2 row(s)
Took 0.5030 seconds

 

And now, it is imposible to delete that files:

upsert into DUMMYTABLE (id,testbin) values (2,null);

it does not removes the data.

Issue Links

• links to: GitHub Pull Request #1342