phoenix-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Adrian Co <...@exist.com>
Subject Unable to select null values from different column family
Date Wed, 21 Oct 2015 18:32:09 GMT
Hi,

I'm currently using Phoenix 4.4 against Hbase 0.98.4. From sqlline, I 
executed the following operations:

Created a test table:

create table if not exists TEST (
       PK varchar NOT NULL,
       C.F_1A varchar,
       C.F_1B varchar,
       D.F_2A varchar,
       D.F_2B varchar,
       E.F_3A varchar,
       E.F_3B varchar,

       constraint PK primary key (PK)
);

Inserted the following data:

// Complete values
upsert into TEST(PK,F_1A,F_1B,F_2A,F_2B,F_3A,F_3B) values 
('PK1','1A','1B','2A','2B','3A','3B');

// All 'B' values are empty
upsert into TEST(PK,F_1A,F_1B,F_2A,F_2B,F_3A,F_3B) values 
('PK2','1A','','2A','','3A','');


Performed the following queries resulting in the expected correct output:

// Correct output
 > select * from TEST where F_1B is null;
 > select * from TEST where F_2B is null;
 > select * from TEST where F_3B is null;

 > select PK from TEST where F_1B is null;
 > select PK,F_3A from TEST where F_3B is null;
+------------+------------+------------+------------+------------+------------+------------+
|     PK     |    F_2A    |    F_2B    |    F_1A    |    F_1B |    
F_3A    |    F_3B    |
+------------+------------+------------+------------+------------+------------+------------+
| PK2        | 2A         | null       | 1A         | null       | 
3A         | null       |
+------------+------------+------------+------------+------------+------------+------------+

Performed the following queries resulting in incorrect output:

// Incorrect output
 > select PK from TEST where F_2B is null;
 > select PK from TEST where F_3B is null;
+------------+
|     PK     |
+------------+
+------------+

I've observed the following behavior:
1. Only the first column family created in the schema (i.e. C) outputs 
correctly. Selecting columns from succeeding CFs does not output 
correctly for null values. Rearranging the creation of the columns (i.e. 
creating columns for D family, resulted in correct output for select PK 
from TEST where F_2B is null;)

2. If you include in the select statement a column with non-null value 
for the CF the output is as expected (i.e. select * from TEST where F_3B 
is null; or select PK,F_3A from TEST where F_3B is null;)

Is this the expected behavior? Is there a configuration to avoid this issue?

Thanks for the support.

Regards,
Adrian Co













Mime
View raw message