[jira] [Commented] (PHOENIX-6897) Filters on unverified index rows return wrong result

["ASF GitHub Bot (Jira)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/PHOENIX-6897?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17720618#comment-17720618 ] 

ASF GitHub Bot commented on PHOENIX-6897:
-----------------------------------------

tkhurana commented on PR #1597:
URL: https://github.com/apache/phoenix/pull/1597#issuecomment-1538760736

   2 failures in AlterTableIT.testSetPropertyDoesntUpdateDDLTimestamp are fixed in #1595 




> Filters on unverified index rows return wrong result
> ----------------------------------------------------
>
>                 Key: PHOENIX-6897
>                 URL: https://issues.apache.org/jira/browse/PHOENIX-6897
>             Project: Phoenix
>          Issue Type: Bug
>    Affects Versions: 5.1.2
>            Reporter: Yunbo Fan
>            Assignee: Tanuj Khurana
>            Priority: Major
>
> h4. 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.
> h4. Steps for reproduce
> 1. create table and index
> {code}
> create table students(id integer primary key, name varchar, status integer);
> create index students_name_index on students(name, id) include (status);
> {code}
> 2. upsert data using phoenix
> {code}
> upsert into students values(1, 'tom', 1);
> upsert into students values(2, 'jerry', 2);
> {code}
> 3. do phase1 by hbase shell, change status column value to '2' and verified column value to '2'
> {code}
> 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"
> {code}
> 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
> {code}
>     # 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
> {code}
> 4. do query without aggregate, the result is right
> {code}
> 0: jdbc:phoenix:> select status from students where name = 'tom';
> +--------+
> | STATUS |
> +--------+
> | 1      |
> +--------+
> {code}
> 5. do query with aggregate, get wrong result
> {code}
> 0: jdbc:phoenix:> select count(*) from students where name = 'tom' and status = 1;
> +----------+
> | COUNT(1) |
> +----------+
> | 0        |
> +----------+
> {code}
> 6. using NO_INDEX hint
> {code}
> 0: jdbc:phoenix:> select /*+ NO_INDEX */ count(*) from students where name = 'tom' and status = 1;
> +----------+
> | COUNT(1) |
> +----------+
> | 1        |
> +----------+
> {code}



--
This message was sent by Atlassian Jira
(v8.20.10#820010)