phoenix-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Roberto Coluccio (JIRA)" <j...@apache.org>
Subject [jira] [Created] (PHOENIX-4116) UPSERT from SELECT of NULL fields does not work as expected
Date Wed, 23 Aug 2017 14:49:00 GMT
Roberto Coluccio created PHOENIX-4116:
-----------------------------------------

             Summary: UPSERT from SELECT of NULL fields does not work as expected
                 Key: PHOENIX-4116
                 URL: https://issues.apache.org/jira/browse/PHOENIX-4116
             Project: Phoenix
          Issue Type: Bug
    Affects Versions: 4.7.0
         Environment: HBase 1.2
CDH 5.8.4
            Reporter: Roberto Coluccio


When performing an UPSERT from SELECT statement, if a column value returned from the SELECT
one is NULL then the update is not performed on the target column:

{code:sql}
sqlline version 1.1.8
0: jdbc:phoenix:myhost> create table if not exists my_temp_table (my_key char(4) not null,
value_1 varchar, value_2 varchar, constraint pk_my_temp_table primary key (my_key));
No rows affected (1,602 seconds)
0: jdbc:phoenix:myhost> select * from my_temp_table;
+---------+----------+----------+
| MY_KEY  | VALUE_1  | VALUE_2  |
+---------+----------+----------+
+---------+----------+----------+
No rows selected (0,657 seconds)
{code}

Now let's insert a record with a null and a not null values:

{code:sql}
0: jdbc:phoenix:myhost> upsert into my_temp_table (my_key,value_1, value_2) values ('AAAA','abc',
null);
1 row affected (0,138 seconds)
0: jdbc:phoenix:myhost> select * from my_temp_table where my_key = 'AAAA';
+---------+----------+----------+
| MY_KEY  | VALUE_1  | VALUE_2  |
+---------+----------+----------+
| AAAA    | abc      |          |
+---------+----------+----------+
1 row selected (1,109 seconds)
{code}

Now I want to put VALUE_2 value (which is currently NULL) into VALUE_1:

{code:sql}
0: jdbc:phoenix:myhost> upsert into my_temp_table (my_key,value_1) select my_key,value_2
from my_temp_table where my_key = 'AAAA';
1 row affected (0,047 seconds)
0: jdbc:phoenix:myhost> select * from my_temp_table where my_key = 'AAAA';
+---------+----------+----------+
| MY_KEY  | VALUE_1  | VALUE_2  |
+---------+----------+----------+
| AAAA    | abc      |          |
+---------+----------+----------+
1 row selected (0,04 seconds)
{code}

I'd have expected VALUE_1 to be NULL, instead it still has the value it had before the UPSERT
statement.

Obviously, updating VALUE_2 with the VALUE_1 content (not null) works as expected:

{code:sql}
0: jdbc:phoenix:myhost> upsert into my_temp_table (my_key,value_2) select my_key,value_1
from my_temp_table where my_key = 'AAAA';
1 row affected (0,048 seconds)
0: jdbc:phoenix:myhost> select * from my_temp_table where my_key = 'AAAA';
+---------+----------+----------+
| MY_KEY  | VALUE_1  | VALUE_2  |
+---------+----------+----------+
| AAAA    | abc      | abc      |
+---------+----------+----------+
1 row selected (0,06 seconds)
{code}

*NOTE*: The problem occurs both with varchar and numeric (e.g. float) column types.



--
This message was sent by Atlassian JIRA
(v6.4.14#64029)

Mime
View raw message