[jira] [Resolved] (PHOENIX-4116) UPSERT from SELECT of NULL fields does not work as expected

["Xinyi Yan (Jira)" <ji...@apache.org>]

     [ https://issues.apache.org/jira/browse/PHOENIX-4116?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Xinyi Yan resolved PHOENIX-4116.
--------------------------------
    Resolution: Cannot Reproduce

I tried it on the current 4.x branch and didn't repro this issue. Close this bug for now but feel free to reopen if any of you can repro this.

> 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
>  4.7.0-1.clabs_phoenix1.3.0.p0.000
>            Reporter: Roberto Coluccio
>            Priority: Major
>
> 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
(v8.3.4#803005)