Major Description
Summary:
Upsert include three phases, and if failed after phase1, unverified index rows will leave in the index table. This will cause wrong result when do aggregate queries.
Steps for reproduce
1. create table and index
create table students(id integer primary key, name varchar, status integer); create index students_name_index on students(name, id) include (status);
2. upsert data using phoenix
upsert into students values(1, 'tom', 1); upsert into students values(2, 'jerry', 2);
3. do phase1 by hbase shell, change status column value to '2' and verified column value to '2'
put 'STUDENTS_NAME_INDEX', "tom\x00\x80\x00\x00\x01", '0:0:STATUS', "\x80\x00\x00\x02" put 'STUDENTS_NAME_INDEX', "tom\x00\x80\x00\x00\x01", '0:_0', "\x02"
notice: hbase shell can't parse colon in column, like '0:0:STATUS', you may need comment the line in hbase/lib/ruby/hbase/table.rb, see https://issues.apache.org/jira/browse/HBASE-13788
# Returns family and (when has it) qualifier for a column name
def parse_column_name(column)
split = org.apache.hadoop.hbase.KeyValue.parseColumn(column.to_java_bytes)
-> comment this line out #set_converter(split) if split.length > 1
return split[0], (split.length > 1) ? split[1] : nil
end
4. do query without aggregate, the result is right
0: jdbc:phoenix:> select status from students where name = 'tom';
+--------+
| STATUS |
+--------+
| 1 |
+--------+
5. do query with aggregate, get wrong result
0: jdbc:phoenix:> select count(*) from students where name = 'tom' and status = 1;
+----------+
| COUNT(1) |
+----------+
| 0 |
+----------+
6. using NO_INDEX hint
0: jdbc:phoenix:> select /*+ NO_INDEX */ count(*) from students where name = 'tom' and status = 1; +----------+ | COUNT(1) | +----------+ | 1 | +----------+