Uploaded image for project: 'Phoenix'
  1. Phoenix
  2. PHOENIX-3498

Query with index failed when query back to data table with desc PK column

    XMLWordPrintableJSON

    Details

    • Type: Bug
    • Status: Resolved
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: 4.9.0
    • Labels:
      None

      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.

        Attachments

        1. PHOENIX-3498.patch
          4 kB
          William Yang

          Activity

            People

            • Assignee:
              yhxx511 William Yang
              Reporter:
              yhxx511 William Yang
            • Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: