[jira] [Created] (PHOENIX-6585) strange behavior when using default values ​​and upsert with nulls in not pk fields

["Alejandro Anadon (Jira)" <ji...@apache.org>]

Alejandro Anadon created PHOENIX-6585:
-----------------------------------------

             Summary: strange behavior when using default values ​​and upsert with nulls in not pk fields
                 Key: PHOENIX-6585
                 URL: https://issues.apache.org/jira/browse/PHOENIX-6585
             Project: Phoenix
          Issue Type: Bug
          Components: core
            Reporter: Alejandro Anadon


While I was doing the test case for the fix in  PHOENIX-6583 , I discovered a strange behavior when using default values and upsert with nulls in not pk fields.

Steps:

CREATE TABLE IF NOT EXISTS DUMMYTABLE  (pk INTEGER PRIMARY KEY, int INTEGER DEFAULT -100);

UPSERT INTO DUMMYTABLE (pk,int) VALUES (1,null);

SELECT * FROM DUMMYTABLE;

 now you can see that there is a null in the field.

SELECT * FROM DUMMYTABLE WHERE INT IS NULL;

Hey!! No result!! they were? is it null or not null?

(It doesn't happen if we create the table  witout 'DEFAULT -100' . In taht case, the results are correct).

 

After a investigation, that is because after 'UPSERT INTO DUMMYTABLE (pk,int) VALUES (1,null);' it insert in hbase a record without a value:

hbase(main):003:0> scan 'DUMMYTABLE'
ROW COLUMN+CELL
 \x80\x00\x00\x01 column=0:\x00\x00\x00\x00, timestamp=1635879976908, value=x
 \x80\x00\x00\x01 column=0:\x80\x0B, timestamp=1635879976908, value=
1 row(s)

 

It is hard to solve it, because that is the way that phoenix can difer nulls from default value -100.

But , how can be solve the problem when setting null? From client side point of view, if whe put a null, we want to be null, not default value.

So if phoenix removes from hbase the record, it has no way to see if it is null or default value -100. And if it insert a value like "value=" to represent the null (as it is doing actualy), then the select "where int is null" does not works. I don't know if I can make myself understood.

I tested it with other types and the behavior is similar.



--
This message was sent by Atlassian Jira
(v8.3.4#803005)