[PHOENIX-3498] Query with index failed when query back to data table with desc PK column

• Type: Bug
• Status: Resolved
• Priority: Major
• Resolution: Fixed
• Fix Version/s: 4.9.0
• Assignee: William Yang
• Reporter: William Yang
• Created: 18/Nov/16 03:52
• Updated: 18/Nov/16 21:00
• Resolved: 18/Nov/16 19:14

Description

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:

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 

if we declare ether p1/p2 as DESC, then the same SELECT will return nothing.

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

if p1 is not DESC, but p2 is, SELECT will fail too.