[PHOENIX-5753] Fix erroneous query result when RVC is clipped with desc column

• Type: Bug
• Status: Resolved
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 5.0.0, 4.15.0
• Fix Version/s: 5.1.0, 4.16.0
• Labels: DESC
• Assignee: chenglei
• Reporter: chenglei
• Created: 29/Feb/20 03:47
• Updated: 01/Aug/23 15:00
• Resolved: 18/Mar/20 04:36

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.

Issue Links

• is caused by: PHOENIX-3383 Comparison between descending row keys used in RVC is reverse
• is related to: PHOENIX-4841 Filters that uses RVC with pk columns where with DESC sort order don't work correctly
• links to: GitHub Pull Request #719