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)
