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] [Updated] (PHOENIX-4116) UPSERT from SELECT of NULL fields does not work as expected
Date Thu, 24 Aug 2017 10:37:00 GMT

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

Roberto Coluccio updated PHOENIX-4116:
--------------------------------------
    Environment: 
HBase 1.2
CDH 5.8.4
 4.7.0-1.clabs_phoenix1.3.0.p0.000

  was:
HBase 1.2
CDH 5.8.4


> 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
>
> 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