[
https://issues.apache.org/jira/browse/PHOENIX-3498?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15675675#comment-15675675
]
William Yang commented on PHOENIX-3498:
---------------------------------------
This should be a very old bug, I think the reason this bug had been hidden for
so long is: in most cases, we will use covered index so that 'query back to
data table' will never happen. In this case, even if DESC pk column is very
common, it will be hard to meet both of the conditions that will trigger this
problem.
> Query with index failed when query back to data table with desc PK column
> -------------------------------------------------------------------------
>
> Key: PHOENIX-3498
> URL: https://issues.apache.org/jira/browse/PHOENIX-3498
> Project: Phoenix
> Issue Type: Bug
> Reporter: William Yang
> Assignee: William Yang
> Attachments: PHOENIX-3498.patch
>
>
> This bug can be re-pro against two conditions:
> 1. select must hit index table and have to query back to data table
> 2. at least one of the data table's PK column is in DESC order
> see the following SQLs:
> {code}
> create table tt (p1 integer not null, p2 integer not null, a integer, b
> integer constraint pk primary key (p1,p2));
> create index tti on tt (a);
> upsert into tt values (0, 1, 2, 3);
> select /*+index(tt tti)*/ b from tt where a = 2; // will query back
> to data table
> //this SELECT works fine, will return b=3
> {code}
> if we declare ether p1/p2 as DESC, then the same SELECT will return nothing.
> {code}
> create table tt (p1 integer not null, p2 integer not null, a integer, b
> integer constraint pk primary key (p1 desc, p2));
> create index tti on tt (a);
> upsert into tt values (0, 1, 2, 3);
> select /*+index(tt tti)*/ b from tt where a = 2; // return nothing
> {code}
> if p1 is not DESC, but p2 is, SELECT will fail too.
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
