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

Fix erroneous query result when RVC is clipped with desc column

    XMLWordPrintableJSON

Details

    • Bug
    • Status: Resolved
    • Major
    • Resolution: Fixed
    • 5.0.0, 4.15.0
    • 5.1.0, 4.16.0
    • None

    Description

      Given following table and data:

             CREATE TABLE  test
             (
                  pk1 INTEGER NOT NULL ,  
                  pk2 INTEGER NOT NULL, 
                  pk3 INTEGER NOT NULL, 
                  pk4 INTEGER NOT NULL, 
                  v INTEGER, CONSTRAINT PK PRIMARY KEY(pk1,pk2,pk3 desc,pk4))
             )
      

      Noticed pk3 is DESC.

             UPSERT INTO test (pk1, pk2, pk3, pk4, v) VALUES (1,3,4,10,1)
      

      If we execute the following sql:

           select * from test
           where (pk1 >=1 and pk1<=2) and (pk2>=3 and pk2<=4) and (pk3,pk4) < (5,7)
      

      the returned result is empty, but obviously, the above inserted row (1,3,4,10,1) should be returned.

      I think this problem is introduced by PHOENIX-3383 and PHOENIX-4841, when we clip the (pk3,pk4) < (5,7) because pk3 is DESC by following line 260 in WhereOptimizer.pushKeyExpressionsToScan , (pk3,pk4) < (5,7) is clipped to pk3 <= 5 and pk4 < 7 .

      257                        List<KeyRange> leftRanges = clipLeft(schema, slot.getPKPosition()
      258                            + slotOffset - clipLeftSpan, clipLeftSpan, keyRanges, ptr);
      259                    keyRanges =
      260                            clipRight(schema, slot.getPKPosition() + slotOffset - 1, keyRanges,
      261                                    leftRanges, ptr);
      262                    if (prevSortOrder == SortOrder.DESC) {
      263                        leftRanges = invertKeyRanges(leftRanges);
      264                    }
      265                    slotSpanArray[cnf.size()] = clipLeftSpan-1;
      266                    cnf.add(leftRanges);
      267                    clipLeftSpan = 0;
      268                    prevSortOrder = sortOrder;
      269                    // since we have to clip the portion with the same sort order, we can no longer
      270                    // extract the nodes from the where clause
      271                    // for eg. for the schema A VARCHAR DESC, B VARCHAR ASC and query
      272                    //   WHERE (A,B) < ('a','b')
      273                    // the range (* - a\xFFb) is converted to (~a-*)(*-b)
      274                    // so we still need to filter on A,B
      275                    stopExtracting = true;
      276                }
      

      Eventually after we completed the WhereOptimizer.pushKeyExpressionsToScan, the result
      ScanRanges.ranges is [[[1 - 2]], [[3 - 4]], [5 - *), [(* - 7)]], ScanRanges.useSkipScanFilter is true and SkipScanFilter is also [[[1 - 2]], [[3 - 4]], [5 - *), [(* - 7)]], so the the above inserted row (1,3,4,10,1) could not be retrieved.

      But as we know, (pk3,pk4) < (5,7) is not semantically equals to pk3 <= 5 and pk4 < 7 , we could only have
      pk3 <= 5 but not pk4 < 7, so when we clipped (pk3,pk4) < (5,7) to pk3 <= 5 , we could simply skip remaining columns of this RVC.

      Attachments

        1. PHOENIX-5753_v2-4.x-HBase-1.4.patch
          42 kB
          chenglei
        2. PHOENIX-5753_v5-4.x.patch
          41 kB
          chenglei
        3. PHOENIX-5753_v5-master.patch
          41 kB
          chenglei
        4. PHOENIX-5753_v6-master.patch
          41 kB
          chenglei

        Issue Links

          Activity

            People

              comnetwork chenglei
              comnetwork chenglei
              Votes:
              0 Vote for this issue
              Watchers:
              5 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Time Tracking

                  Estimated:
                  Original Estimate - Not Specified
                  Not Specified
                  Remaining:
                  Remaining Estimate - 0h
                  0h
                  Logged:
                  Time Spent - 20m
                  20m